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
Function CELLHASFORMULA(cell As Range) As Boolean
‘ Returns TRUE if cell has a formula
CELLHASFORMULA = cell.Range(“A1”).HasFormula
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:
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
CELLFORMULA = “”
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: