Microsoft Office Tutorials and References

In Depth Information

**Simple Functions**

Simple Functions

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

End Function

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

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.