Need to know when calculated value has changed

[chcculle]

I'd like to be able to tell which cell changed (As in WorksheetChange).
WorksheetCalculate doesn't tell me which
cell has changed.
Usually I use InitializeLinks because my
data is coming from an outside source and
this works perfectly. In this case however,
it's being calculated from within the worksheet. Can I fake out excel into thinking it's coming in from an external source and use initializeLinks?
Or is there an easy way to do this?

Many thanks in advance.

 

[Damon Ostrander]

Hi chcculle,

The reason why the worksheet's Calculate event doesn't tell which cell has changed (like the Change event does) is that, typically, hundreds or even thousands or millions of cells can change on a Calculate event. If Volatile is True, then all formulas re-calculate, and if iteration is allowed and there are circular references, the number of cell value changes can get VERY large. If you really want to know all the cells that change when a calculation is done, you can trap the Change event to determine which cell change triggered the re-calculate event, then look at all the dependents of the cell using the Dependents property that provides a Range object that contains all the dependent cells. These cells will all have changed. But then you must keep going down the Dependents tree because other cells might be dependent on these dependents. So you really need a recursive algorithm to search the entire tree structure of dependents to determine all of the cells that change as a result of a calculation.

I realize that this has gotten a bit complicated, but unfortunately that is because there is a lot more to this question than first meets the eye.

 

[chcculle]

Damon,
Thanks for your response. I'm still trying to figure out the best way to do this.
I'd like to explore further your suggestion
of trapping the event. I don't think it
will be quite as difficult as what you were mentioning because I don't care about the
dependants who changed, I only care about the
calculated value.
For example Cell1 and Cell2 are DDE inputs.
Lets say Cell3 = Cell2 - Cell1, I only care that Cell3 has changed.
Could you or someone explain how I can trap this event?
Thanks.