Microsoft Office Tutorials and References
In Depth Information
The functions in this section are relatively simple, but they can be very useful. Most of them are
based on the fact that VBA can obtain useful information that’s not normally available for use in a
formula. For example, your VBA code can access a cell’s HasFormula property to determine
whether a cell contains a formula. Oddly, Excel does not have a built-in worksheet function that
tells you this.
The companion CD-ROM 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
If a multicell range argument is passed to the function, the function works with the upper-left cell
in the range.
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.