-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
VB code to turn off automatic calculations
- Thread starter Cossie
- Start date
Jay Petrulis
MrExcel MVP
- Joined
- Mar 17, 2002
- Messages
- 2,040
Hi,
It is a good idea to determine the calculation setting *before* you change it and then turn it back to its pre-routine state at the end of the code.
This finds the calc setting, sets it to manual, and returns it at the end of the routine.
HTH,
Jay
It is a good idea to determine the calculation setting *before* you change it and then turn it back to its pre-routine state at the end of the code.
Code:
Sub test()
Dim CalcSetting As Integer
With Application
CalcSetting = .Calculation
.Calculation = xlCalculationManual
End With
'
' your code here
'
Application.Calculation = CalcSetting
End SubThis finds the calc setting, sets it to manual, and returns it at the end of the routine.
HTH,
Jay
Upvote
0
Jay Petrulis
MrExcel MVP
- Joined
- Mar 17, 2002
- Messages
- 2,040
Where does it fail? Which line? It is quite strange that it would bomb like that, as this is the basic way to change the calculation setting.
Try removing the 'As Integer' from the CalcSetting declaration. Any success then?
Try removing the 'As Integer' from the CalcSetting declaration. Any success then?
Upvote
0
Here's some commands that may help.
First command stops excel from doing autoCalculate... I usually include this in "ON Open Events".
Second command forces the active sheet to be calculated while the AutoCalculate is disabled
Third Command I usually put in "Before Close Event" of the workbooks that I have used the first command on.
First command stops excel from doing autoCalculate... I usually include this in "ON Open Events".
Second command forces the active sheet to be calculated while the AutoCalculate is disabled
Third Command I usually put in "Before Close Event" of the workbooks that I have used the first command on.
Code:
Sub CalcOnDemand()
' Set APP to manual Calc Only
Application.Calculation = xlManual
' Calculate the Active sheet
ActiveSheet.Calculate
' Set APP to Automatic calc
Application.Calculation = xlAutomatic
End Sub
Upvote
0
Similar threads
- Question
