XL97: Custom Function Is Not Recalculated with Intersection

ID: Q186685

The information in this article applies to:

• Microsoft Excel 97 for Windows

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:

   ARTICLE-ID: Q151261
   TITLE     : OFF97: How to Obtain and Install MS Office 97 SR-2

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" (without the quotation marks), and then
     click OK.

  8. Select cells A2:B2, point to Name on the Insert menu, and then
     click Define. Type "Name2" (without the quotation marks), and then
     click OK.

  9. Click Sheet2. Type the value "5" (without the quotation marks) 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" (without the quotation marks) into
     cell A1, and then press ENTER.

 12. Click Sheet1.

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

Keywords          : kbdta xlvbainfo xlformula 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbfix

Last Reviewed: November 10, 1998