XL97: Custom Function Is Not Recalculated with Intersection Operator

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