Excel contains many built-in worksheet functions that check the type of content in a cell (a number, a string of text, an error value, etc.). But Excel does not provide a function that checks to see if a cell contains a formula. If the cell contains a constant value, these information functions return the type of the value, but if the cell contains a formula, the information functions return the type of the calculated value of the formula.

A custom function: IsFormula()

The following custom worksheet function, IsFormula(), is similar to the other built-in IS functions in Excel. When you enter this function into a cell, the one argument the function accepts, the Ref argument, is a reference to either a single cell or a range of cells. If you enter a reference to a single cell, the function returns TRUE if the cell contains a formula. If the cell does not contain a formula (either it is blank or it contains a value of some kind such as a number or a date), then IsFormula() returns FALSE.

To check to see if a cell or range of cells contains formulas, the custom function uses the HasFormula property of the range that is passed to the function. The following table lists the possible return values for the property.

Since the property can return three different results, the function uses an IF–THEN–ELSE conditional statement to force only two possible results: TRUE when the cell contains a formula or a range contains at least one formula, and FALSE when the cell does not contain a formula or all cells in the range do not contain any formulas.

Function IsFormula(ByVal Ref As Range) As Boolean
      'This function returns FALSE if no cells contain
      'formulas, or TRUE if all or some cells contain
      'formulas. Note that this function only works
      'with cells in adjacent ranges.
    If Ref.HasFormula = False Then
        IsFormula = False
    Else
        IsFormula = True
    EndIf
End Function

How do I use this function?

This function has the form:

ISFORMULA(Ref)

Where Ref is a reference to either a cell or a range of cells.

For example, if cell A3 contains the formula =SUM(A1:A2) then =ISFORMULA(A3) equals TRUE.

More information

To learn more about custom functions in Excel, read Create your own worksheet functions and see Excel Help. If you are new to Microsoft Visual Basic® for Applications programming, the Microsoft Office 2000/Visual Basic Programmer's Guide has information to help you get started. See Excel Help for more information about how to obtain this guide.