Wednesday, September 22, 2010

Excel VBA: Does the Cell or Range Have a Formula

Need to quicky check if a cell contains a formula? Use Range's HasFormula property:

Property definition
expression.HasFormula
  • Expression = Range object
  • Returns:
    • True if all cells in range contain a formulss
    • False if none contain formulas
    • Null if some cells contain a formula 
    • Read-only Variant
Use IsNull to check if the result is Null.

Example 1

Scenario
You have a column, where you want the user to have two options:
  1. By default include a formula
  2. Let the user enter a value manually
The formula is updated on Worksheet_Open or a button.

Solution 
' Update only cells with formulas.
'
If ActiveSheet.Cells(Row, Column).HasFormula  Then
    ' Update or refresh your formula here.
End If

Example 2

See MSDN's HasFormula Property page for an example which recalculates the active worksheet if any cell in the currently selected range contains a formula.

1 comment:

  1. I really appreciate the kind of topics you post here. Thanks for sharing us a great information that is actually helpful. Good day!
    excel vba courses london

    ReplyDelete