Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accuracy of Using Calculate - IMPORTANT QUESTION PLEASE
I have a large Excel 2003 workbook with multiple sheets and formula
interdependencies. When my code runs I change the calc status to manual and then use 'Calculate' (not ActiveSheet.Calculate or ActiveSheet.UsedRange.Calculate for example, just 'Calculate') at various points during the process. However, after coming across Microsoft's KB919127 (see below for partial excerpt), am now concerned if this can give me inaccurate results. Is 'Calculate' accurate or must I use UsedRange.Calculate; if so, I thought there are issues with applying the UsedRange method also (in terms of correctly selecting the used range). Can someone advise me please? KB919127: 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. 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 met: o You use the Calculate method in a Microsoft Visual Basic for Applications (VBA) macro to calculate formulas. o 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. This issue may occur when either of the following conditions is true: 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("Sheet1").Calculate Instead, use the UsedRange property as follows:  ActiveSheet.UsedRange.Calculate  Worksheets("Sheet1").UsedRange.Calculate |
Thread Tools | Search this Thread |
Display Modes | |
|
|