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

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.