Need to know when calculated value has changed

chcculle

New Member
Joined
Feb 24, 2002
Messages
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top