Microsoft KB Archive/898920

= If you use the SUBTOTAL function to calculate the subtotal of a list in a worksheet that contains a user-defined function, the user-defined function may be calculated more times than expected in Excel =

Article ID: 898920

Article Last Modified on 2/6/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003

-



SYMPTOMS
You are using a worksheet that contains a user-defined function in Microsoft Office Excel 2003 or in Microsoft Office Excel 2007. If you use the SUBTOTAL function to calculate the subtotal of a list of data in the worksheet, the user-defined function may be calculated more times than expected.



CAUSE
This issue may occur if the user-defined function is called more than one time. The user-defined function is called more than one time when the following conditions are true:
 * You used the following procedure to remove the subtotals from the worksheet:
 * You selected the list that contains the subtotals.
 * In Excel 2003, you clicked Subtotals on the Data menu, and then you clicked Remove all.

In Excel 2007, you clickedSubtotal in the Outline group on the Data, and then you clickedRemove all.
 * The list of data contains the user-defined function.



WORKAROUND
To work around this issue in Excel 2003, follow these steps:
 * 1) Select the list that contains the user-defined function.
 * 2) On the Edit menu, click Copy.
 * 3) On the Insert menu, click Worksheet.
 * 4) On the Edit menu, click Paste Special.
 * 5) In the Paste Special dialog box, click Paste Link.

To work around this issue in Excel 2007, follow these steps:
 * 1) Select the list that contains the user-defined function.
 * 2) Click Copy in the Clipboard group on the Home tab.
 * 3) Click the Insert Worksheet tab.
 * 4) Click the arrow on Paste, and then click Paste Special.
 * 5) In the Paste Special dialog box, click Paste Link.

Additional query words: excel2003 xl2003 xl2k3 UDF XL2007

Keywords: kbexpertisebeginner kbfunctions kbtshoot kbprb KB898920

-

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

© Microsoft Corporation. All rights reserved.