ThisCalculator

Everyday

Excel VBA Run Macro When Cell Formula Result Changes

Updated · Standard formula, shown below

Excel VBA Run Macro When Cell Formula Result Changes

Yes, you can run a VBA macro automatically when a cell’s formula result changes by using the Worksheet_Change event combined with a check of the Target range, or by using the Worksheet_Calculate event which fires after any recalculation. The exact approach depends on whether you need to react to the formula’s output value or to the fact that a recalculation happened.

Understanding Excel Events

Excel VBA provides event procedures that run automatically when certain actions occur. Two are relevant here:

  • Worksheet_Change – fires when a cell’s value is changed by the user or by a link, but not when a formula recalculates. It gives you the Target range so you can check which cells were affected.
  • Worksheet_Calculate – fires after the worksheet recalculates, which includes formula result changes. It does not tell you which cell changed, so you must compare old and new values yourself if you need that detail.

For a formula result change, the typical solution is to use Worksheet_Calculate and store the previous result in a module-level variable or a hidden cell, then compare it to the new result.

How to Detect a Formula Result Change

Because Worksheet_Change doesn’t fire when a formula recalculates, you need a different strategy. The most common pattern:

  1. Open the VBA editor (Alt+F11) and double-click the worksheet in the Project Explorer.
  2. In the code window, select “Worksheet” from the left dropdown and “Calculate” from the right dropdown.
  3. Write code that compares the current value of your target cell to a stored previous value.

Example: Suppose cell A1 contains a formula. You want to run a macro when its result changes.

Private Sub Worksheet_Calculate()
    Static prevValue As Variant
    Dim newValue As Variant
    newValue = Range("A1").Value
    If IsEmpty(prevValue) Then
        prevValue = newValue
    ElseIf newValue <> prevValue Then
        ' Run your macro here
        Call MyMacro
        prevValue = newValue
    End If
End Sub

The Static variable retains its value between calls, so it remembers the last result. The first time it runs, it just stores the initial value. On subsequent recalculations, if the value differs, your macro runs.

Using Worksheet_Change with Formula Cells

If your formula is recalculated only when the worksheet calculates (e.g., volatile functions like NOW(), RAND(), or when a dependency changes), Worksheet_Change won’t catch it. However, if the formula result changes because a precedent cell was edited, you can use Worksheet_Change on the precedent cell and then check the formula cell’s value.

Example: Suppose B1 contains =A1*2. When you change A1, Worksheet_Change fires with Target being A1. You can then check B1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        ' B1 will have recalculated by now
        Call MyMacro
    End If
End Sub

This works only if the formula’s result changes due to a manual entry or paste in a precedent cell. It won’t catch changes from external data refreshes or volatile functions.

How to Choose the Right Event

Consider these factors:

  • What triggers the change? If it’s a user editing a cell, Worksheet_Change is simpler. If it’s a recalculation (volatile functions, data connections, or formulas referencing other sheets), use Worksheet_Calculate.
  • Do you need to know which cell changed? Worksheet_Change gives you the Target range. Worksheet_Calculate does not, so you must check all relevant cells yourself.
  • Performance: Worksheet_Calculate fires on every recalculation, which can be frequent. If your macro is heavy, you might want to disable events temporarily or use a more targeted approach.

Common Problems and Solutions

  • Macro doesn’t run when formula changes via calculation. This is expected with Worksheet_Change. Switch to Worksheet_Calculate.
  • Macro runs too often. If your worksheet has many volatile functions, Worksheet_Calculate fires repeatedly. You can add a check to see if the specific cell’s value actually changed before running the macro.
  • Static variable loses its value. If you use Dim prevValue outside the event, it resets when the code is recompiled or the workbook is opened. Using Static inside the procedure keeps the value for the session, but it resets when the workbook closes. For a persistent value, store it in a hidden cell or a named range.
  • Events not firing because macros are disabled. Ensure that macros are enabled in the Trust Center settings.

Alternative Approaches

  • Use a hidden cell to store the last value. Put the formula in a hidden cell, and in the Calculate event compare the visible cell to the hidden one. This avoids Static variables.
  • Use Application.Volatile with a custom function. You can write a UDF that triggers a macro when it recalculates, but this is less reliable and not recommended.
  • Use a class module to monitor multiple cells. For complex scenarios, you can create a class that handles events for multiple worksheets.

Performance and Safety Considerations

Event-driven macros can slow down your workbook if they run frequently. To mitigate:

  • Disable events at the start of your macro and re-enable at the end using Application.EnableEvents = False and True.
  • Keep the event code short; do heavy processing in a separate procedure.
  • Avoid using volatile functions unnecessarily.

Also, be aware that Worksheet_Calculate fires even when no formula result changes (e.g., when you enter data in an unrelated cell that triggers recalculation). Always compare old and new values to avoid unnecessary macro runs.

Frequently Asked Questions

Can I use Worksheet_Change to detect formula result changes?

No, Worksheet_Change only fires when a cell is manually edited or changed via a link, not when a formula recalculates. Use Worksheet_Calculate instead.

How do I know which cell’s formula changed in Worksheet_Calculate?

Worksheet_Calculate doesn’t provide a Target. You need to compare the current values of all relevant cells to stored previous values (e.g., in a Static variable or hidden cells).

Why does my macro run every time I change any cell?

Because Worksheet_Calculate fires on any recalculation. If you only want to react to a specific cell, add a check comparing the cell’s new value to its previous value.

Will the macro run if the formula result changes due to a data connection refresh?

Yes, if the refresh causes a recalculation, Worksheet_Calculate will fire. However, if the connection is set to not recalculate, it won’t.

Can I make the macro run only once per change?

Yes, by comparing the new value to the stored previous value and only running the macro when they differ. The example above does this.

Is there a way to trigger a macro when a formula result changes without VBA?

No, Excel’s built-in conditional formatting or data validation cannot run macros. You need VBA for that.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.