R
roybrew
I'm looking at a particular problem and have observered the following
behavior, which I didn't expect. It has to do with the order of when
the App_SheetCalculate and App_SheetChange events are fired. If you
have a sheet with some numbers and a column or row that has a =Sum(...)
formula in it, totaling a row or column, this is the easiest way to
demonstrate this. You can also trap this behavior with the
Worksheet_Calculate and Worksheeet_Change events.
Table might look like the following:
a b c d Totals
x 5 10 15 20 50
y 10 15 20 25 70
z 15 20 25 30 90
Totals 30 45 60 75 210
The order of the respective Calculate or Change event is different,
depending on how you exit a cell after you modify it.
If you change the number 15 in the column labeled c, above and use some
keystroke (tab, enter, cursor key) to exit the cell and finish the edit
you get the events in the following order:
1. Calculate
2. Change
If you change the number 15 in the column labeled c, above and use the
mouse to exit the cell and finish the edit you get the events in the
following order:
1. Change
2. Calculate
At first I thought it was something we were doing in our XLA/XLL code,
but I have observed this in plain old vanilla Excel.
My question is as follows:
For both of these cases (keystroke and mouse cell exit), why would we
not see these events come in the same order?
Any help or insight to this would be greatly appreciated. Thanks in
advance.
Roy
behavior, which I didn't expect. It has to do with the order of when
the App_SheetCalculate and App_SheetChange events are fired. If you
have a sheet with some numbers and a column or row that has a =Sum(...)
formula in it, totaling a row or column, this is the easiest way to
demonstrate this. You can also trap this behavior with the
Worksheet_Calculate and Worksheeet_Change events.
Table might look like the following:
a b c d Totals
x 5 10 15 20 50
y 10 15 20 25 70
z 15 20 25 30 90
Totals 30 45 60 75 210
The order of the respective Calculate or Change event is different,
depending on how you exit a cell after you modify it.
If you change the number 15 in the column labeled c, above and use some
keystroke (tab, enter, cursor key) to exit the cell and finish the edit
you get the events in the following order:
1. Calculate
2. Change
If you change the number 15 in the column labeled c, above and use the
mouse to exit the cell and finish the edit you get the events in the
following order:
1. Change
2. Calculate
At first I thought it was something we were doing in our XLA/XLL code,
but I have observed this in plain old vanilla Excel.
My question is as follows:
For both of these cases (keystroke and mouse cell exit), why would we
not see these events come in the same order?
Any help or insight to this would be greatly appreciated. Thanks in
advance.
Roy