Excel Calculation mode

[eti3nne]

Hi,

I needed to ensure that excel 'calculation mode' was set to Automatic when a particular spreadsheet was activated, so I included this code:

Application.Calculation = xlCalculationAutomatic.

On de-activate, I set:

Application.Calculation = xlCalculationManual.

So far so good..., but it occurred to me that the user may have been in 'Automatic mode' prior to loading my spreadsheet.

So I thought it would be useful if I could test the current (ie. original) setting (at activation), store it and reset the excel 'Application.Calculation' to the original state on exit.

So, at last ... my question is ...

How do I determine the current state of Calcualation mode?

Thanks for looking.

 

[shetlandbob]

(I think) xlAutomatic, xlManual is stored as integers, so you just need to store the Application.Calculation value on opening the spreadsheet. Then restore this when you close the spreadsheet.

You'll need to store the variable as global, or similar so you can still access it when the spreadsheet closes.

Robert Cumming

http://www.consultantengineer.co.uk

 

[HarleyQuinn]

You could always use a case statement e.g.

Select Case Application.Calculation
Case xlCalculationAutomatic
'code if it is Automatic
Case xlCalculationManual
'code if it is Manual
End Select

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[xlbo]

public bln_ChangeItBack as boolean

Private Sub Workbook_Open()
[COLOR=green]'check calculation status[/color]
bln_ChangeItBack = application.calculation = xlcalculationmanual
End sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
[COLOR=green]'change status back if it was manual before[/color]
If bln_ChangeItBack = true then application.calculation = xlcalculationmanual
end sub

End Sub

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

You don't need to care what the calculation mode was - and it is a Long with three possible values - you just want to set it back.

If the user changes it while the workbook is open, of course (or opens another workbook which forces it to manual in the same way), you won't pick up the change and you also won't get the effect you desire.

All in all I don't approve of this type of manipulation of global user settings - if you really must do it, you should do it at the WorkSheet level, on Activation and Deactivation, ...

[blue]Public UserCalculationMode as Long

Private Sub Worksheet_Activate()
    UserCalculationMode = Application.Calculation
    Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Worksheet_Deactivate()
    Application.Calculation = UserCalculationMode 
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[