Sheet calculation target cell

[Otto Moehrbach]

Excel 2002, WinXP
I have an OP who is getting stock updates through a Dynamic Data Exchange
(DDE). He has a column of formulas that pull down the data.
He wants something to happen when the contents of each of these cells
change and that "something" needs to be tied to the particular cell that
changed. Therein lies the problem.
Obviously the content of the cell (a formula) has not changed so I can't
use a Worksheet_Change macro to home in on the cell that changed. The only
event that fires that I know of is the "sheet calculate" event, and that
event does not identify the cell that triggered the calculation.
I feel that I'm not the first one to come up against this problem, but I
can't find anything on it. Does anyone know of some methodology by which to
identify the cell that caused the calculation to fire? Thanks for your
help. Otto

 

[Charles Williams]

Otto,

I do not think there is a good solution to this, but here are some
suggestions that might be practical for small numbers of formulae:

- use a tracker function (similar to CalcSeqCountRef from my downloads page)
to track each cell and signal to the "something" that the cell has
changed/been recalculated. The problem is that this is probably only
practical for a few hundred cells before the overhead gets too large.

- use a shadow sheet that contains the values at the end of the last
calculation. Use the Sheet Calculate event to compare the values looking for
differences to trigger the "something" and then rewrite any changed values
into the shadow sheet.

The tracker function approach would work best if only a small number of
cells change at each calculation, or the number of changes per minute is not
large. The shadow sheet works best for batches of updates.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Otto Moehrbach]

Charles
Thanks for that. I'll try both and see which fits him best. Otto