Microsoft KB Archive/186685

From BetaArchive Wiki

Article ID: 186685

Article Last Modified on 10/21/2000



APPLIES TO

  • Microsoft Excel 97 Standard Edition



This article was previously published under Q186685


SYMPTOMS

When you calculate values on a worksheet, some formulas may not be automatically updated.

CAUSE

This problem may occur when the following conditions are true:

  • The formula calls a custom function. -and-


  • The formula refers to a cell an another worksheet.


-and-

  • You use the Intersection operator in one of the arguments in the custom function.


RESOLUTION

To correct this problem, obtain Microsoft Excel 97 Service Release 2 (SR-2).

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

151261 OFF97: How to Obtain and Install MS Office 97 SR-2




To work around the problem temporarily, press CTRL+ALT+F9 to recalculate the values in the entire workbook.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Office 97 Service Release 2 (SR-2).

MORE INFORMATION

Example of the Problem

To see an example of this problem, follow these steps:

  1. Save and close any open workbooks, and then create a new workbook.
  2. Start the Visual Basic Editor (press ALT+F11).
  3. On the Insert menu, click Module to insert a Visual Basic for Applications module sheet.
  4. Type the following code into the module sheet:

            Function Test(a As Single, b As Single) As Single
    
               Test = a + b
    
            End Function
                            
  5. On the File menu, click "Close and Return to Microsoft Excel."
  6. Enter the following values into Sheet1:

            A1: 1   B1:
            A2: 2   B2: 3
                            
  7. Select cells A1:A2, point to Name on the Insert menu, and then click Define. Type Name1, and then click OK.
  8. Select cells A2:B2, point to Name on the Insert menu, and then click Define. Type Name2, and then click OK.
  9. Click Sheet2. Type the value 5 into cell A1, and then press ENTER.
  10. Click Sheet1. Type the following formula into cell A5, and then press ENTER:

            =Test(Sheet2!A1,Name1 Name2)
                            

    The formula returns a value of 7, which is a correct answer.

  11. Click Sheet2. Type the value 10 into cell A1, and then press ENTER.
  12. Click Sheet1.

    Cell A5 displays a value of 10, but the correct value is 12.



Additional query words: XL97 recalc

Keywords: kbbug kbfix KB186685