finding which cells changed after a calculate?

[mrmack]

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?

[Tom Ogilvy]

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?

[mrmack]

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?

[Tom Ogilvy]

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?