Guide to Making Your Macro Run Faster and Better in Excel

This guide will show you 4 different ways to make your macros run faster and more efficiently in Excel.

There are 4 main things that you can do to each macro in order to have it run faster.

Sections:

ScreenUpdating

Calculation

Events

Alerts

All Together

Notes

ScreenUpdating

This is the number one way to speed up a macro.

ScreenUpdating controls if Excel will update the worksheet each time something is done within the worksheet.

If you leave this setting on, which is the default value, and you do something like run a loop on every row of data and do something with that data, then Excel will update the screen after every change; this really slows things down.

Turn this off:

Application.ScreenUpdating = False

Turn this on:

Application.ScreenUpdating = True

Read more about this in our tutorial on turning-off ScreenUpdating in Excel.

Calculation

If you have a large spreadsheet with complex formulas or formulas linked to separate workbooks, this can really slow down a macro.

Calculation in Excel refers to Excel recalculating all formulas and functions in a worksheet after each time that something is changed in that worksheet. With small worksheets and simple formulas, you wouldn't notice anything; however, large spreadsheets with complex formulas linked to other workbooks can take a long time to update and really slow down a macro.

Turn this off:

Application.Calculation = xlCalculationManual

Turn this on:

Application.Calculation = xlCalculationAutomatic

Read more about this in our tutorial on disabling Calculation in Excel.

Events

Events are things in Excel that can be set to trigger a macro to run. You can set an event to run a macro with a user enters data into a cell, selects a cell, moves between worksheets, or even when they close the workbook.

Events are triggered when a user performs a specific action and also when a macro performs that action. As such, when you run a macro, Events can lead to unpredictable results or just slow macros. You can, however, turn-off these Events for the duration of the macro.

Turn this off:

Application.EnableEvents = False

Turn this on:

Application.EnableEvents = True

Read more about this in our tutorial on turning off Events in Excel.

Alerts

Alerts are Excel windows that pop-up to "alert" you to something. This happens when, for instance, you try to close a workbook without saving and Excel stops you and shows you a window that asks if you would like to save it or not.

This kind of behavior is cumbersome when doing things like running a macro that needs to open and close a workbook. To get around this, you can turn these messages and alerts off so they won't appear.

Turn this off:

Application.DisplayAlerts = False

Turn this on:

Application.DisplayAlerts = True

Read more about this in our tutorial on turning off Alerts in Excel.

All Together

Many people simply combine all of the above lines of code in their macros so they don't have to worry about these speed and usability issues.

Here is an example of this:

Sub your_macro()

'Turn everything off
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False

'Code to run here

'Turn everything back on
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Notes

The links in each section above provide more information and examples and can help you better understand what is happening in the macro.

Download the sample file for this workbook to get the last example in Excel.