Subj.: Microsoft Excel 97 Recalc Issue
Maurice Henry
Subj.: Microsoft Excel 97 Recalc Issue
In January 1998, I found a serious bug in Microsoft Excel 97: SOME
CELLS DO NOT RECALCULATE.
This bug occurs if you have a LABEL in a CHART, and the label contains a
numerical calculation form a CELL in the spreadsheet. The label value
WILL NOT recalculate when the values change, even if you push F9 (CALC).
While I have not tested the conditions exhaustively, it appears to be
ANY label on ANY type of chart that contains a NUMERICAL calculation
from ANY cell in the spreadsheet.
Microsoft has published a patch that fixes a Recalc bug that occurs on
Excel 97 if:
a- You have a range of 18 or more cells in a row AND
b- Each cell has a formula referring it to the cell in the left AND
c- These cells are in a row which is a multiple of 16 +1 row
(17,33,49 ....) AND
d- A formula in a different cell refers to one of the above cells AND
e- A formula in any cell refers to any cell above the range of 18
If the above conditions happen, the cells will not recalculate when you
push F9 or when new values are entered.
A patch is available (Service release 1 patch, XLP81.EXE) at the
Microsoft Web site.
I have verified this patch works for the above conditions.
This patch DOES NOT fix the label recalc bug.
I do not know if there are other conditions that cause a MISSED
RECALCULATION.
The net effect is that most, if not all our production tools would read
a value that bears no relationship to the numbers entered, either
manually or through computer input.
I spent two weeks trying to communicate this to Microsoft. Yesterday, I
finally reached their people. They were able to reproduce the bug, and
acknowledged that they know what the problem is.
They were not able to tell me what the outcome will be (A new patch,
wait for Office 98, do nothing...) I urged them to let me know what
their plans are. I have not heard anything from anybody.
Microsoft offers a work around: Hit CTRL-ALT-F9 (Grand recalculation),
either manually or through Visual Basic macro.
We cannot guarantee that CTRL-ALT-F9 will be always hit in production.
Since the values previously existing in the spreadsheet may be similar
to those of the product being tested, a FALSE ACCEPTANCE OF ANY PRODUCT
IS POSSIBLE, with the potential of an expensive field return.
This is a pretty basic problem. We expect Excel to calculate
correctly. For us to re-do the software to ensure that the calculations
are correct may be beyond our budget.
Does anybody have any suggestions?
Luis Navarro
Luis.n...@precisionint.com
David Hager
You might want to try using the Worksheet_Change event to trigger
Ctrl-Alt-F9 with the SendKeys method when data is entered in the target
cell. Still, even if this works for you, I consider this to be a drastic
workaround for something that should work. There are other recalculation
problems as well
(see www.j-walk.com).
David Hager
Maurice Henry <mauric...@precisionint.com> wrote in article
<3509B0...@precisionint.com>...