VBA: Improve Speed & Other Best Practices

This tutorial will discuss how to speed up VBA Macros and other VBA best practices.

Settings to Speed Up VBA Code

Below you will find several tips to speed up your VBA code. The tips are loosely organized by importance.

The easiest way to improve the speed of your VBA code is by disabling ScreenUpdating and disabling Automatic Calculations. These settings should be disabled in all large procedures.

Disable Screen Updating

By default, Excel will display changes to workbook(s) in real-time as VBA code runs. This causes a massive slowdown in processing speed as Excel most interpret and display changes for each line of code.

To turn off Screen Updating:

At the end of your macro, you should turn back on Screen Updating:

While your code is running, you may need to “refresh” the screen. There is no “refresh” command. Instead, you will need to turn Screen Updating back on and disable it again.

Set Calculations to Manual

Whenever a cell value is changed, Excel must follow the “calculation tree” to recalculate all dependent cells. Additionally, whenever a formula is changed, Excel will need to update the “calculation tree” in addition to re-calculating all dependent cells.  Depending on your workbook size, these recalculations can cause your macros to run unreasonably slow.

To set Calculations to Manual:

To manually re-calculate the entire workbook:

Note you can also calculate only a sheet, range, or individual cell, if necessary for improved speed.

To restore Automatic Calculations (at the end of your procedure):

Important! This is an Excel setting. If you don’t re-set calculations to automatic your workbook will not re-calculate until you tell it to.

You’ll see the biggest improvements from the above settings, but there are several other settings that can make a difference:

Disable Events

Events are “triggers” that cause special event procedures to run. Examples include: when any cell on a worksheet changes, when a worksheet is activated, when a workbook is opened, before a workbook is saved, etc.

Disabling events can cause minor speed improvements when any macros run, but the speed improvement can be much greater if your workbook uses events.  And in some cases disabling events is necessary to avoid creating endless loops.

To disable events:

To turn events back on:

Disable PageBreaks

Disabling PageBreaks can help in certain situations:

• You’ve previously set a PageSetup property for the relevant worksheet and your VBA procedure modifies the properties of many rows or columns
• OR Your VBA procedure forces Excel to calculate pagebreaks (displaying Print Preview or modifying any properties of PageSetup).

To disable PageBreaks:

To re-enable PageBreaks:

Best Practices to Improve VBA Speed

Avoid Activating and Selecting

When you record a Macro, you’ll see many Activate and Select methods:

Activating and selecting objects is usually unnecessary, they add clutter to your code, and they are very time-consuming.  You should avoid this methods when possible.

Improved Example:

Avoid Copying and Pasting

Copying requires significant memory. Unfortunately, you can’t tell VBA to clear internal memory. Instead Excel will clear it’s internal memory at (seemingly) specific intervals. So if you perform many copy and paste operations you run the risk of hogging too much memory, which can drastically slow down your code or even crash Excel.

Instead of copying and pasting, consider setting the value properties of cells.

Use the For Each loops instead of For Loops

When looping through objects, the For Each loop is faster than the For Loop. Example:

This For Loop:

Declare Variables / Use Option Explicit

Use With – End With Statements

Advanced Best Practice Tips

Protect UserInterfaceOnly

It’s good practice to protect your worksheets from editing unprotected cells to prevent the end-user (or you!) from accidentally corrupting the workbook.  However, this will also protect the worksheet(s) from allowing VBA to make changes. So you must unprotect and re-protect worksheets, which is very time consuming when done on many sheets.

Instead, you can protect sheets with setting UserInterfaceOnly:=True. This allows VBA to make changes to sheets, while still protecting them from the user.

Important! UserInterFaceOnly resets to False every time the workbook opens. So to use this awesome feature, you will need to use the Workbook_Open or Auto_Open events to set the setting each time the workbook is opened.

Place this code in the Thisworkbook module:

or this code in any regular module:

Use Arrays to Edit Large Ranges

It can be very time consuming to manipulate large ranges of cells (Ex. 100,000+).  Instead of looping through ranges of cells, manipulating each cell, you can load the cells into an array, process each item in the array, and then output the array back into their original cells.  Loading the cells into arrays for manipulation can be much faster.

<<Return to VBA Examples