Microsoft KB Archive/919127

= Calculations may not occur in an Excel workbook that has many formulas when you use the Calculate method to calculate formulas or after you press SHIFT+F9 to calculate formulas =

Article ID: 919127

Article Last Modified on 1/12/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-





SYMPTOMS
When you calculate formulas in a workbook that contains many formulas in Microsoft Excel 2002 or in Microsoft Office Excel 2003, the calculations may not occur.

This issue may occur when either of the following conditions is true:
 * You use the Calculate method in a Microsoft Visual Basic for Applications (VBA) macro to calculate formulas.
 * You press F9 to calculate formulas in all open workbooks that have changed since the last calculation. Then, you press SHIFT+F9 to calculate formulas in the active worksheet that have changed since the last calculation. When you press F9 again to calculate formulas in all open workbooks, the formulas in all open workbooks are not calculated.



WORKAROUND
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. To work around this problem, use the UsedRange property when you calculate formulas. To do this, use one of the following methods depending on how you calculate formulas.

You use the Calculate method in a VBA macro to calculate formulas
If you use the Calculate method in a VBA macro to calculate the formulas in a workbook, use the UsedRange property.

For example, you use one of the following Calculate methods:
 * ActiveSheet.Calculate
 * Worksheets(&quot;Sheet1&quot;).Calculate

Instead, use the UsedRange property as follows:
 * ActiveSheet.UsedRange.Calculate
 * Worksheets(&quot;Sheet1&quot;).UsedRange.Calculate

You press SHIFT+F9 to manually calculate formulas
If you press SHIFT+F9 to manually calculate formulas in the active worksheet, create a Microsoft Visual Basic for Applications (VBA) macro to capture the keystrokes and to programmatically use the UsedRange property.

To do this, follow these steps:  Open a new workbook in Excel. On the Tools menu, point to Macro and then click Visual Basic Editor. Or, press ALT+F11 to open the Visual Basic Editor. In the Project window, double-click ThisWorkbook.  Paste or type the following VBA code in the module code sheet: Private Sub Workbook_Open Application.OnKey key:=&quot;+{F9}&quot;, procedure:=&quot;Calcit&quot; End Sub Sub Calcit ActiveSheet.UsedRange.Calculate End Sub  Press ALT+TAB to switch back to the worksheet. On the File menu, click Properties. On the Summary tab, type the title of the add-in that you want to appear in the Add-Ins dialog box, and then click OK.</li> On the File menu, click Save As.</li> In the Save as type box of the Save As dialog box, click Microsoft Office Excel Add-in (*.xla), type a name for the add-in in the File name box, and then click Save.

Note Save the add-in in the following folder:

C:\Program Files\Microsoft Office\Office11\Library

</li> To load the new add-in, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Tools menu, click Add-Ins.</li> Click to select the check box for the new add-in, and then click OK to close the Add-Ins dialog box.</li></ol> </li></ol>

<div class="moreinformation_section">

MORE INFORMATION
This issue does not occur in Excel 2000. If you use this workaround in Excel 2000, the workaround will not cause any problems. When you use the UsedRange property, you may experience the same issues that are experienced with the Range property. For more information about the issues with the Range property, click the following article number to view the article in the Microsoft Knowledge Base:

292476 Run-time error when you use the Range.Calculate method in Excel

For more information about Excel add-ins, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/library/aa140936(office.10).aspx

Additional query words: excel2003 excel2k3 excel11 xl2003 xl2k3 xl11 excel2002 excel2k2 excel10 excelxp xl2002 xl2k2 xl10 xlxp

Keywords: kbprogramming kbformula kbautomation kbvba kbtshoot kbexpertisebeginner kbprb KB919127

-

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

© Microsoft Corporation. All rights reserved.