Excel 97 Display Bug
There is a bug in Excel 97 that impacts some spreadsheets in Strategic Decision Making: Multiobjective Decision Analysis with Spreadsheets. If a worksheet uses both custom Visual Basic functions in cell formulas and Data Tables, then some cells will display incorrect values if changes are made
to the worksheet. This bug is in all versions of Excel 97, including Service
Release 2. However, it is relatively easy to work around the bug. (The bug is
not in Excel 95 or Excel 2000.)
To observe the bug, download the Section 4.7 workbook (Sec4-7.xls, 49,664 bytes). Open the workbook and go to tab "Figure 4.6."
Change the entry in cell E28 to 0.5 (or anything else that you want). The
entries in cells D22:D25 and F22:F25 will change to all zeros. This is not
supposed to happen, and if you tab to worksheet "Figure 4.3" and then tab back
to "Figure 4.6," the entries in these cells will correct themselves.
A worksheet must contain both Data Tables and Visual Basic functions for the
bug to occur. Therefore, it will only show up in examples or exercises from Strategic Decision Making that involve sensitivity analysis or the use of Data Tables to address
continuous decision variables. Examples involving these are in Sections 4.7,
4.8 and 7.7. Also, the information displayed in the Data Table will be correct.
It is other information in the worksheet that is displayed incorrectly
Work AroundThis is a display bug rather than a recalculation bug. Manually recalculating the worksheet does not correct the problem. You can correct the screen display by taking some action that requires the
worksheet to be redisplayed. For example, switch to another worksheet and then
switch back. The bug does not necessarily show up as cell entries with zeros in
them, as in the example above. (The specific incorrect quantities that are
displayed depend on the values in the Data Table.)
Alternate Work AroundYou can also work around the bug by inserting Application.Volatileas the first statement in each custom Visual Basic function. This approach is
demonstrated by the alternate Section 4.7 workbook (Sec4-7v.xls, 56,320 bytes). The Application.Volatile statement forces the
recalculation of each function every time that any cell changes in the
worksheet. The display of all cell values will always be correct, but this
approach substantially slows recalculation of a worksheet. However, the longer
recalculation times should not be objectionable for the examples and exercises
in the book. (The modified functions will work correctly in Excel 95, Excel 97,
and Excel 2000.)
Return to ASU decision analysis resources page.
Last updated August 1, 2001.