Microsoft Office Tutorials and References

In Depth Information

This topic's website contains the workbook simple functions.xlsm that includes all the

functions in this section.

Does a cell contain a formula?

The following CELLHASFORMULA function accepts a single-cell argument and returns TRUE if the cell has

a formula:

Function CELLHASFORMULA(cell As Range) As Boolean

‘ Returns TRUE if cell has a formula

CELLHASFORMULA = cell.Range(“A1”).HasFormula

End Function

If a multicell range argument is passed to the function, the function works with the upper-left cell in the range.

Excel 2013 includes a new function, FORMULATEXT, that can be used to indicate

whether a cell has a formula. This function returns the formula in a cell (as text). If the

referenced cell does not contain a formula, it returns #N/A. To emulate the

CELLHASFORMULA function, use a formula like this:

=NOT(ISNA(FORMULATEXT(A1)))

Returning a cell's formula

The following CELLFORMULA function returns the formula for a cell as a string. If the cell does not have a

formula, it returns an empty string.

Function CELLFORMULA(cell As Range) As String

‘

Returns the formula in cell, or an

‘

empty string if cell has no formula

Dim UpperLeft As Range

Set UpperLeft = cell.Range(“A1”)

If UpperLeft.HasFormula Then

CELLFORMULA = UpperLeft.Formula

Else

CELLFORMULA = “”

End If

End Function

This function creates a Range object variable named UpperLeft. This variable represents the upper-left cell in

the argument that is passed to the function. This ensures that the code works with only one cell. Alternatively,

the function can be written like this: