XL5: Array Formula Doesn't Recalculate Using Calculate Method

The information in this article applies to:

SYMPTOMS

In Microsoft Excel, when you use the Calculate method in a Visual Basic procedure to recalculate a range of cells, if the range contains an array formula, one of the following occurs:


CAUSE

This problem occurs when you calculate any array formula using the Calculate method, whether the formula contains a custom function or a built-in function.

Note that this problem does not occur in Microsoft Excel for Windows version 5.0.


WORKAROUNDS

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
To recalculate an array formula, use any of the following methods.

Method 1

Calculate the entire sheet by using the Calculate method with the Worksheets object, as in the following example: 

   Worksheets(1).Calculate

Method 2

Set the Calculation option to Automatic by following these steps:

  1. On the Tools menu, click Options.


  2. Click the Calculation tab.


  3. Under Calculations, click Automatic, and then click OK.


Note that to recalculate a custom function that is entered as an array using this method, you must then calculate the worksheet. To do this, you can press F9 to calculate the entire workbook, or press SHIFT+F9 to calculate only the selected worksheet.

Method 3

Calculate the selected range by using the Replace method with the Selection property, as in the following example: 

   Selection.replace What:="=", Replacement:="=", _
   LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
This macro simulates re-entering the formula and does not damage the array formula.

NOTE: If you use this method, and you later find errors on your sheet that are related to array formulas, close the sheet without saving your changes and use either method 1 or 2.


STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was fixed in Microsoft Excel for Windows 95, version 7.0, Microsoft Excel 97, and Microsoft Excel 98 Macintosh Edition.


MORE INFORMATION

For additional information, please see the following article in the Microsoft Knowledge Base:

Q118655 XL5: Custom Function Doesn't Recalculate with Calculate Method


REFERENCES

For more information about the "Calculate Method", choose the Search button in Help and type:

calculate method

Additional query words: 5.00a 5.00c broken breaks #value! error XL5

Keywords :
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0c
Platform : MACINTOSH WINDOWS
Issue type :
Technology :


Last Reviewed: April 12, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.