XL5: Array Formula Doesn't Recalculate Using Calculate Method

ID: Q130708

The information in this article applies to:

Microsoft Excel for Windows, version 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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:

If the array formula is in a single cell, the formula is no longer an array after you run the procedure

If the array formula is in a range of cells, the range is not recalculated.

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:

On the Tools menu, click Options.

Click the Calculation tab.

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 :