Microsoft KB Archive/186686

= XL98: Custom Function Is Not Recalculated with Intersection Operator =

Article ID: 186686

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q186686





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 that this is a problem in the Microsoft products that are listed at the beginning of this article.



Example of the Problem
To see an example of this problem, follow these steps:  Save and close any open workbooks, and then create a new workbook. Start the Visual Basic Editor (press OPTION+F11). On the Insert menu, click Module to insert a Visual Basic for Applications module sheet.  Type the following code into the module sheet: Function Test(a As Single, b As Single) As Single Test = a + b End Function  On the File menu, click Close and Return to Microsoft Excel.</li>  Type the following values into Sheet1: <pre class="fixed_text">  A1: 1   B1: A2: 2  B2: 3 </li> Select cells A1:A2, point to Name on the Insert menu, and then click Define. Type Name1, and then click OK.</li> Select cells A2:B2, point to Name on the Insert menu, and then click Define. Type Name2, and then click OK.</li> Click Sheet2. Type the value 5 into cell A1, and then press RETURN.</li>  Click Sheet1. Type the following formula into cell A5, and then press RETURN: <pre class="fixed_text">  =Test(Sheet2!A1,Name1 Name2) The formula returns a value of 7, which is a correct answer. </li> Click Sheet2. Type the value 10 into cell A1, and then press ENTER.</li> Click Sheet1.

Cell A5 displays a value of 10, but the correct value is 12.</li></ol>

Additional query words: XL98

Keywords: kbbug kbpending KB186686

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.