Need to know when calculated value has changed

chcculle · Feb 25th, 2002, 02:01 PM

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 · Feb 25th, 2002, 05:49 PM

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 · Feb 28th, 2002, 12:49 PM

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.

Feb 25th, 2002, 05:49 PM	#2
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	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. __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 02:01 PM	#1
chcculle New Member Join Date: Feb 2002 Posts: 2	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.

Feb 28th, 2002, 12:49 PM	#3
chcculle New Member Join Date: Feb 2002 Posts: 2	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.