VBA: Improve Speed & Other Best Practices

Associated Files Download Links

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.

vba speed up code

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:

Is slower than this For Each Loop:

Declare Variables / Use Option Explicit

VBA does not require that you declare your variables, unless you add Option Explicit to the top of your module:
Adding Option Explicit is a coding best practice as it decreases the probability of errors. It also forces you to declare your variables, which slightly increases the speed of your code (benefits are more noticeable the more a variable is used).
How does Option Explicit prevent errors?
The biggest benefit to Option Explicit is it will help you catch variable name spelling errors. For example, in the following example we’ve set a variable named ‘var1’, but later we reference variable named ‘varl’. Variable ‘varl’ hasn’t been defined so it’s blank, causing unexpected results.

Use With – End With Statements

If you reference the same objects multiple times (ex. Ranges, Worksheets, Workbooks), consider using the With Statement.  It is faster to process, can make your code easier to read, and simplifies your code.
With statement example:
Is faster than:

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:

vba best practices

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.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

 

Learn More!


<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues:

You may also like some of this related content...

Advertisements