SheetChange for formulas

[ARHangel]

I've noticed that if a change the value of a cell that affects a formula for
that cell the Worksheet.Change event is triggered but for the cell with the
formula is not.

Is there a way to determine if the value of a formula has changed?

 

[Peter T]

Normally a formula cell will contain a refer to one or more precedents, of
which at least one is likely to be a cell you can look for changes in a
change event.

Alternatively, start by storing the old formula value, then comparing at the
end of a Calculate event, eg

Make B4 a formula cell, eg =Sum("B2:B3")

in the worksheet module (rt-click sheet tab, view code)

Dim mbStored As Boolean
Dim mV As Variant

Private Sub Worksheet_Calculate()
Debug.Print Range("B4").Value
With Range("B4")

If Not mbStored Then
mV = "last value stored"
End If
Range("C4").Value = mV

mV = .Value
End With
mbStored = True
End Sub

Initialize (ie store the old value) by causing a recalc, or manually
Ctrl-Alt-F9
Look at cell C4

Regards,
Peter T

 

[ARHangel]

Is there a way without knowing the cells with formulas?

 

[Peter T]

In theory maybe yes but difficult to comment without any details as to what
you have and what you want. At the very least it would need to be tailored
to your set-up and requirements.

Regards,
Peter T

 

[ARHangel]

If anyone interested I've found a solution.

use Range.Dependents - gives you all the cells that are formulas and the
formulas "contains" the Range
or
Range.SpecialCells(xlCellFormulas) - gives you all the cells in the Range
that are formulas