How To Speed Up Excel VBA Macro Execution Time – Optimize?

Excel VBA Optimized – Reduce Run Time

You can use this code in almost all of your macro code. This will improvise your code run time drastically.

Macro that run for minutes will get completed in seconds.

Before getting the code for this, lets see What are the options we touch upon safely?

1. Switch off updates that is shown on screen: This is the major Time taking step. For every action or calculations done on Worksheets through Macro, Excel keeps displaying the changes. If we switch it off & display only the final result, it will reduce a lot of time.
2. Stop Events triggers: Every time we change any cell value on a worksheet, it triggers corresponding function related to Worksheet.Change or conditional formatting etc., Switch it off.
3. Stop Formula Calculations: For every change in worksheet cell value, Excel refresh all the formula calculations. if a workbook has many lookup formulas, then it will cost a lot of time. Even hours sometimes.

The above 3 are the basic & major options that is enough to optimize most of the code run time.

Here is the code snippet that is ready to use in your modules.

'Increase Speed
Sub Optimize_ON()
    'Switch of All options that takes most of the time
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub

'Switch off Speed improvement options
Sub Optimize_OFF()
    Dim sh As Worksheet
    
    'Enable Graphics, Events & Autocalculations
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    'Refresh Formula in each Sheet
    For Each sh In ThisWorkbook.Sheets
        sh.Calculate
    Next
End Sub

There is a small point to be noted. If the program execution stops in middle of the code due to any run time error, then the Optimize_Off will not be called. It will result in your Excel workbook behaving weird.

In that case, add a shortcut key or a command button that invokes the Optimize_Off function. so that Excel switches back to its normal operation mode.