excel-vba Excel-VBA Optimization Disabling Worksheet Updating


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