XL98: Custom Function Is Not Recalculated with Intersection Operator

• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

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

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.

WORKAROUND

To work around the problem, press COMMAND+SHIFT+F9 to recalculate the values in the entire workbook.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Example of the Problem

1. Save and close any open workbooks, and then create a new workbook.



2. Start the Visual Basic Editor (press OPTION+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. Type 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 RETURN.



10. Click Sheet1. Type the following formula into cell A5, and then press RETURN:

    
       =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: XL98

Keywords : kbdta xlvbainfo xlformula
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbbug