Microsoft KB Archive/906310

= Calculation takes longer than expected when a formula depends on a custom array function in Excel 2003 and in Excel 2002 =

Article ID: 906310

Article Last Modified on 12/29/2005

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-





SYMPTOMS
When you calculate a formula that depends on a custom array function in a Microsoft Office Excel 2003 worksheet or in Microsoft Excel 2002 worksheet, the calculation takes longer than expected.



CAUSE
When you use an array function in a formula, the array function is called one time for each cell in the array. The formula calculates a cell every time that a cell in the array is passed to the array function. The calculation process is repeated until the end of the array. The formula calculates the whole array only after the formula reaches the end of the array. The calculation may take longer than expected when you have multiple formulas that refer to large arrays in the worksheet.



WORKAROUND
In the cells that call the custom function, create a built-in function that does not change the value that is returned by the custom function. For example, create a built-in function that is similar to the following:

Original formula

=CUSTOMFUNCTION(A1:A50)

Updated formula

=IF(COUNT(A1:A50)<0,NA,CUSTOMFUNCTION(A1:A50))

Note In this function, the COUNT(A1:A150) function is not evaluated until all the precedent cells in the array are calculated. The custom function is called only after all the precedent cells in the array range of the COUNT(A1:A50) function are calculated. The COUNT will always be 0 or greater. Therefore, the IF condition will always result in a FALSE value. When you receive a FALSE value, your custom function will be called.

Additional query words: arrays UDF User Defined Function

Keywords: kbtshoot kbformat kbformula KB906310

-

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

© Microsoft Corporation. All rights reserved.