Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding which cells changed after a calculate?
I would like to know when formula cells have their calculated values changed
(to update my client program). Is there an obvious event handler I'm missing? There's the AppEvents_SheetCalculateEventHandler, but it's signature would be simply: private void Calculating(object Sh){...} (providing only the worksheet the calculate event occurred in). And although the DocEvents_ChangeEventHandler provides a range, e.g. private void Changing(Excel.Range target){...} it does not fire for updates as a result of formula recalculation. So, presuming that there is no event handler that deals with formula updates, I can only envisage a solution that requires keeping an internal collection of all those cells which contain formulae, coupled with their calculated values and then checking these to see if any changed upon the AppEvents_SheetCalculateEventHandler event. Can anyone see a better solution? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding which cells changed after a calculate?
What you have stated is pretty much the case.
There is no event/internal information that reveals what cells calculated to a new value. -- Regards, Tom Ogilvy "mrmack" wrote: I would like to know when formula cells have their calculated values changed (to update my client program). Is there an obvious event handler I'm missing? There's the AppEvents_SheetCalculateEventHandler, but it's signature would be simply: private void Calculating(object Sh){...} (providing only the worksheet the calculate event occurred in). And although the DocEvents_ChangeEventHandler provides a range, e.g. private void Changing(Excel.Range target){...} it does not fire for updates as a result of formula recalculation. So, presuming that there is no event handler that deals with formula updates, I can only envisage a solution that requires keeping an internal collection of all those cells which contain formulae, coupled with their calculated values and then checking these to see if any changed upon the AppEvents_SheetCalculateEventHandler event. Can anyone see a better solution? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding which cells changed after a calculate?
Good to know!
Now I am wondering if the Calculating event is always fired before the Changing event, as if I have a range that contains both a formula cell and the source data for that cell then when I update the source data cell that will fire both the Changing event AND the Calculating event, when I only really want one. Just mentioning these things in case anyone comes across the same situation.. "Tom Ogilvy" wrote: What you have stated is pretty much the case. There is no event/internal information that reveals what cells calculated to a new value. -- Regards, Tom Ogilvy "mrmack" wrote: I would like to know when formula cells have their calculated values changed (to update my client program). Is there an obvious event handler I'm missing? There's the AppEvents_SheetCalculateEventHandler, but it's signature would be simply: private void Calculating(object Sh){...} (providing only the worksheet the calculate event occurred in). And although the DocEvents_ChangeEventHandler provides a range, e.g. private void Changing(Excel.Range target){...} it does not fire for updates as a result of formula recalculation. So, presuming that there is no event handler that deals with formula updates, I can only envisage a solution that requires keeping an internal collection of all those cells which contain formulae, coupled with their calculated values and then checking these to see if any changed upon the AppEvents_SheetCalculateEventHandler event. Can anyone see a better solution? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding which cells changed after a calculate?
My brief test showed calculate firing before Change. You could set
calculation to manual and then have your code do the calculate when appropriate. (disabling events before doing it if appropriate). -- Regards, Tom Ogilvy "mrmack" wrote: Good to know! Now I am wondering if the Calculating event is always fired before the Changing event, as if I have a range that contains both a formula cell and the source data for that cell then when I update the source data cell that will fire both the Changing event AND the Calculating event, when I only really want one. Just mentioning these things in case anyone comes across the same situation.. "Tom Ogilvy" wrote: What you have stated is pretty much the case. There is no event/internal information that reveals what cells calculated to a new value. -- Regards, Tom Ogilvy "mrmack" wrote: I would like to know when formula cells have their calculated values changed (to update my client program). Is there an obvious event handler I'm missing? There's the AppEvents_SheetCalculateEventHandler, but it's signature would be simply: private void Calculating(object Sh){...} (providing only the worksheet the calculate event occurred in). And although the DocEvents_ChangeEventHandler provides a range, e.g. private void Changing(Excel.Range target){...} it does not fire for updates as a result of formula recalculation. So, presuming that there is no event handler that deals with formula updates, I can only envisage a solution that requires keeping an internal collection of all those cells which contain formulae, coupled with their calculated values and then checking these to see if any changed upon the AppEvents_SheetCalculateEventHandler event. Can anyone see a better solution? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding values in a table using 2 criteria (changed) | Excel Worksheet Functions | |||
How to found changed cells? | Excel Discussion (Misc queries) | |||
Horizontal Cells changed to Vertical cells | Excel Programming | |||
keep specific cells from being changed. | Excel Discussion (Misc queries) | |||
Get information on changed cells | Excel Programming |