The HasFormula property (which is read-only) returns True if the single-cell Range contains a formula. It returns False if the cell does not have a formula. If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don't have a formula. The property returns a Null if there is a mixture of formulas and nonformulas.
Be careful with the type of variables you use to maintain the results returned by the HasFormula property. When working with any property that returns a Null, it is easy to generate errors by using the wrong data types.
For example, assume that cell A1 contains a value and cell A2 contains a formula. The following statements generate an error because the range doesn't consist of all formulas or all nonformulas:
Dim FormulaTest As Boolean FormulaTest = Range("A1:A2").HasFormula
To fix this type of situation, the best thing to do is simply make sure the FormulaTest variable is declared as a variant rather than as a Boolean. The following example demonstrates.
Dim FormulaTest As Variant
FormulaTest = Range("A1:A2").HasFormula
If TypeName(FormulaTest) = "Null" Then MsgBox "Mixed!"
Was this article helpful?