How to properly set calculation mode in Excel VBA

Most people set calculation mode to 'manual' at the beginning of the code, then set it to 'automatic' at the end. This assumes that the setting was 'automatic' in the first place and the user wants the calculation mode to be 'automatic' after the code finishes. While I believe that in 99.9% of cases, calculation should always be set to automatic (see Spreadsheet Speeding up Re-calculations for explanation), your code should never make unrequested changes without notifying the user. Here is how to properly set the calculation mode so it is restored to whatever it was previously.

Dim CalcMode As Long
' get current calculation state, save for later
CalcMode = Application.Calculation
' set to manual for the duration of the code
Application.Calculation = xlCalculationManual

'your code here

' set it back to whatever it was before the code was run.
Application.Calculation = CalcMode

Optimize your macros

This can be cut and pasted into your routines to make them run faster. It will stop events, disable automatic calculation and screen updating and, if any dialog boxes pop up, it gives the default answer silently. Keep in mind that in some cases, you may actually want some of these things to happen (i.e. your code may depend on calculations made in the workbook during processing). In that case, simply remove the parts you don't want. In keeping with the spirit of storing and restoring settings to avoid disrupting the user, the code uses variables to store the current states of each property, turns them off for the duration of the code, then restores them to whatever they were previously. That being said, sometimes you want calculation, events, etc to occur. I recommend leaving calculation at 'automatic' under most circumstances, otherwise you are asking for trouble.

Dim bEvents As Boolean
Dim bAlerts As Boolean
Dim CalcMode As Long
Dim bScreen As Boolean

' save current settings
bEvents = Application.EnableEvents
bAlerts = Application.DisplayAlerts
CalcMode = Application.Calculation
bScreen = Application.ScreenUpdating

' disable events, alerts, automatic calculation & screen updating
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

' your code here

Then right before the End Sub, paste this:

' restore previous settings
With Application
    .EnableEvents = bEvents
    .DisplayAlerts = bAlerts
    .Calculation = CalcMode
    .ScreenUpdating = bScreen
End With

LAST UPDATED: February 8, 2008