Microsoft KB Archive/890053

= Recalculation of a workbook that contains a user-defined function takes longer than expected in Excel 2003 and Excel 2002 =

Article ID: 890053

Article Last Modified on 1/12/2005

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



SYMPTOMS
The recalculation of a workbook that contains a user-defined function takes longer than expected in Microsoft Office Excel 2003 and Microsoft Excel 2002. Excel calculates the user-defined function many times, and the overall calculation performance decreases significantly.

This issue may occur if the following conditions are true:
 * The user-defined function is entered as an array formula.
 * A number of the precedent cells have not yet calculated.



CAUSE
When Excel starts to calculate the user-defined function, it finds that the precedent cells have not yet calculated. Therefore, Excel stops the calculation on the user-defined function to calculate the first uncalculated precedent cell. Then, Excel returns to the user-defined function. At that point, Excel discovers another uncalculated precedent cell, stops the calculation on the user-defined function to calculate the uncalculated precedent cell, and then returns to the user-defined function. The result is that Excel tries to calculate the user-defined function one time for each uncalculated precedent cell.



WORKAROUND
To work around this issue, change your formula to use a built-in function that does not return a value until all precedent cells have calculated.

For example, your formula may be similar to the following: =myudf(arrayRange) Change your formula to use the COUNT function as in the following example: IF(COUNT(ArrayRange)<0,&quot;&quot;,myudf(ArrayRange)) Note The IF statement always evaluates to FALSE and does not change the result in the cell. However, the IF statement makes sure that Excel uses the COUNT function to calculate all precedent cells before calculating the user-defined function.

Additional query words: excel2003 excel2002 xl2003 xl2002 xl2k3 xl2k2 udf

Keywords: kbtshoot kbprb KB890053

-

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

© Microsoft Corporation. All rights reserved.