Excel’s Missing ISFORMULA function

(This is one of an irregular series of posts entitled “Stupid Geek Tricks”. The complete collection of Stupid Geek Tricks posts is available on a single page, or via a special RSS feed.)

In an Excel-based tool I maintain for some of my underwriters, I use conditional formatting to help highlight missing information.  I also have some default values based on formulas, and for various reasons I’ve been wanting to highlight them too.

Excel has a nice collection of “is” functions to help identify the contents of particular cells — ISLOGICAL, ISTEXT, ISNUMBER, ISBLANK…   However, there is no ISFORMULA.

The answer is to add a VBA function:

Function IsFormula(Cell As Range) As Boolean

    If Len(Cell.Formula) = 0 Then

        IsFormula = False

    Else

        IsFormula = (Left(Cell.Formula, 1) = "=")

    End If

End Function

Once coded, usage is =ISFORMULA(cell) ; it returns TRUE if cell contains a formula, FALSE otherwise.

It’s nothing fancy, and I’m embarrassed that I didn’t track down the requisite code sooner.  However, I thought I’d share in case others have need of something similar.