excel-vba VBA Best Practices Switch off properties during macro execution
Example
It is best practice in any programming language to avoid premature optimization. However, if testing reveals that your code is running too slowly, you may gain some speed by switching off some of the application’s properties while it runs. Add this code to a standard module:
Public Sub SpeedUp( _
SpeedUpOn As Boolean, _
Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
With Application
If SpeedUpOn Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayStatusBar = False 'in case you are not showing any messages
ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
Else
.ScreenUpdating = True
.Calculation = xlCalc
.EnableEvents = True
.DisplayStatusBar = True
ActiveSheet.DisplayPageBreaks = True
End If
End With
End Sub
More info on Office Blog - Excel VBA Performance Coding Best Practices
And just call it at beginning and end of macros:
Public Sub SomeMacro
'store the initial "calculation" state
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
SpeedUp True
'code here ...
'by giving the second argument the initial "calculation" state is restored
'otherwise it is set to 'xlCalculationAutomatic'
SpeedUp False, xlCalc
End Sub
While these can largely be considered "enhancements" for regular Public Sub procedures, disabling event handling with Application.EnableEvents = False should be considered mandatory for Worksheet_Change and Workbook_SheetChange private event macros that change values on one or more worksheets. Failure to disable event triggers will cause the event macro to recursively run on top of itself when a value changes and can lead to a "frozen" workbook. Remember to turn events back on before leaving the event macro, possibly through a "safe exit" error handler.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
'code that may change a value on the worksheet goes here
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
One caveat: While disabling these settings will improve run time, they may make debugging your application much more difficult. If your code is not functioning correctly, comment out the SpeedUp True call until you figure out the problem.
This is particularly important if you are writing to cells in a worksheet and then reading back in calculated results from worksheet functions since the xlCalculationManual prevents the workbook from calculating. To get around this without disabling SpeedUp, you may want to include Application.Calculate to run a calculation at specific points.
NOTE: Since these are properties of the Application itself, you need to ensure that they are enabled again before your macro exits. This makes it particularly important to use error handlers and to avoid multiple exit points (i.e. End or Unload Me).
With error handling:
Public Sub SomeMacro()
'store the initial "calculation" state
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
On Error GoTo Handler
SpeedUp True
'code here ...
i = 1 / 0
CleanExit:
SpeedUp False, xlCalc
Exit Sub
Handler:
'handle error
Resume CleanExit
End Sub
