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 Around

This 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 Around

You 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.