excel-vba Excel-VBA Optimization Disabling Worksheet Updating
Example
Disabling calculation of the worksheet can decrease running time of the macro significantly. Moreover, disabling events, screen updating and page breaks would be beneficial. Following Sub can be used in any macro for this purpose.
Sub OptimizeVBA(isOn As Boolean)
Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
Application.EnableEvents = Not(isOn)
Application.ScreenUpdating = Not(isOn)
ActiveSheet.DisplayPageBreaks = Not(isOn)
End Sub
For optimization follow the below pseudo-code:
Sub MyCode()
OptimizeVBA True
'Your code goes here
OptimizeVBA False
End Sub
