Detecting Calculation State

[Chris Gorham]

firstly, thanks for the invisible range names advice -
thats solved.
is it possible to detect the calculation state of a
workbook such that the macro will only make it recalculate
if it requires it. I run Excel 2000. Thanks

 

[John Wilson]

Chris,

The following code is from Myrna Larson:

Application.ScreenUpdating = False
SaveCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = SaveCalcMode
Application.ScreenUpdating = True

When you get to the "your code here" line, calculation will
be set to manual, regardless of what it was before.
Within your code, you can calculate the entire workbook
Application.Calculation = xlCalculationAutomatic
or a specific sheet
Worksheets("Sheet1").Calculate
With the coding above, Excel will return to whatever calculation
state it was in before you ran the code.

John

 

[Chris Gorham]

Sorry,

nice try - but sometimes I have a very large model and I
only want to recalculate it if it needs to be - I need to
detect the calculation status (assuming that the model is
already set to manual recalc)

Chris

 

[Charles Williams]

Hi Chris,

In Excel 2002 you can check:
if application.calculationstate=xlpending then
application.calculate
endif

before xl2002 there is no good method: you can check if the workbook has
been changed by checking the Workbook.Saved property, but that is a bit
oversensitive.


hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com