Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Application.Calculate" does not always update/recalculate the for
In writing VBA code for an Excel Application, the following problem has occurred: The command, "Application.Calculate", does not always update or recalculate the formulas in the cells of the workbook. To compound this problem the users of the Excel Application are using several different versions of Excel and VBA. VBA code will need to check the version of Excel and VBA before executing a command (i.e. using Application.Version and Application.VBE.Version) Present documentation shows the following: * F9 - recalculates all of the data in the open workbooks (Application.Calculate) * Shift+F9 - only calculates data in the specified worksheet (ActiveSheet.Calculate) * Ctrl+Alt+F9 - Forces a full calculation of the data in all of the open workbooks (Application.CalculateFull) * Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of the open workbooks after checking the dependencies between formulas (Application.CalculateFullRebuild) The following information is needed: * What is the best way to handle this issue over the various versions of Excel (backward compatibility)? * What version of Excel and VBA correspond to the previous commands (i.e. Application.CalculateFullRebuild was introduced in Excel 2003 or Excel Version 11, VBA Version ??). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Application.Calculate" does not always update/recalculate the
The following code was used for the trouble worksheet:
'--EnableCalculation must be unset and then set ThisWorkbook.Worksheets("MySheet").EnableCalculati on = False ThisWorkbook.Worksheets("MySheet").EnableCalculati on = True ThisWorkbook.Worksheets("MySheet").Calculate This was similar to the loop you describe in the code: Dim oSht as worksheet Application.Calculation=xlCalculationManual for each oSht in Worksheets oSht.enablecalculation=false osht.enablecalculation=true next osht Application.calculate Unfortunately, this method did not work. Any other ideas? Question: Is there a document on the Microsoft site that will link the version of Excel and the VBE with the following VBA commands: Application.Calculate Application.CalculateFull Application.CalculateFullRebuild Thank you. "Fredrik Wahlgren" wrote: "John K" wrote in message ... In writing VBA code for an Excel Application, the following problem has occurred: The command, "Application.Calculate", does not always update or recalculate the formulas in the cells of the workbook. To compound this problem the users of the Excel Application are using several different versions of Excel and VBA. VBA code will need to check the version of Excel and VBA before executing a command (i.e. using Application.Version and Application.VBE.Version) Present documentation shows the following: * F9 - recalculates all of the data in the open workbooks (Application.Calculate) * Shift+F9 - only calculates data in the specified worksheet (ActiveSheet.Calculate) * Ctrl+Alt+F9 - Forces a full calculation of the data in all of the open workbooks (Application.CalculateFull) * Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of the open workbooks after checking the dependencies between formulas (Application.CalculateFullRebuild) The following information is needed: * What is the best way to handle this issue over the various versions of Excel (backward compatibility)? * What version of Excel and VBA correspond to the previous commands (i.e. Application.CalculateFullRebuild was introduced in Excel 2003 or Excel Version 11, VBA Version ??). You have to check the version prior to deciding what method to call. There are may ways to force Excel top recalculate a worksheet. The best options are of course Application.CalculateFull and Application.CalculateFullrebuild. If you use Excel 2000 there's another way Dim oSht as worksheet Application.Calculation=xlCalculationManual for each oSht in Worksheets oSht.enablecalculation=false osht.enablecalculation=true next osht Application.calculate Read more he http://www.decisionmodels.com/calcsecretsh.htm /Fredrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn off "CALCULATE" on bottom of Excel worksheet. near "Ready" | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
"WinForm Application" to act as "RTD Server" using .Net | Excel Discussion (Misc queries) | |||
Follow up to "Recalculate" problem | Excel Worksheet Functions |