The Has Formula property

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?

0 0

Post a comment