VBA: Improve Speed & Other Best Practices
In this Article
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:
1 |
Application.ScreenUpdating = False |
At the end of your macro, you should turn back on Screen Updating:
1 |
Application.ScreenUpdating = True |
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:
1 |
Application.Calculation = xlManual |
To manually re-calculate the entire workbook:
1 |
Calculate |
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):
1 |
Application.Calculation = xlAutomatic |
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:
1 |
Application.EnableEvents = False |
To turn events back on:
1 |
Application.EnableEvents = True |
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:
1 |
ActiveSheet.DisplayPageBreaks = False |
To re-enable PageBreaks:
1 |
ActiveSheet.DisplayPageBreaks = True |
Best Practices to Improve VBA Speed
Avoid Activating and Selecting
When you record a Macro, you’ll see many Activate and Select methods:
1 2 3 4 5 6 7 8 |
Sub Slow_Example() Sheets("Sheet2").Select Range("D9").Select ActiveCell.FormulaR1C1 = "example" Range("D12").Select ActiveCell.FormulaR1C1 = "demo" Range("D13").Select End Sub |
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:
1 2 3 4 |
Sub Fast_Example() Sheets("Sheet2").Range("D9").FormulaR1C1 = "example" Sheets("Sheet2").Range("D12").FormulaR1C1 = "demo" End Sub |
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.
1 2 3 4 5 6 7 8 9 |
Sub CopyPaste() 'Slower Range("a1:a1000").Copy Range("b1:b1000") 'Faster Range("b1:b1000").Value = Range("a1:a1000").Value End Sub |
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:
1 2 3 4 5 6 |
Sub Loop1() dim i as Range For i = 1 To 100 Cells(i, 1).Value = 1 Next i End Sub |
1 2 3 4 5 6 |
Sub Loop2() Dim cell As Range For Each cell In Range("a1:a100") cell.Value = 1 Next cell End Sub |
Declare Variables / Use Option Explicit
1 |
Option Explicit |
1 2 3 4 |
Sub OptionExplicit() var1 = 10 MsgBox varl End Sub |
Use With – End With Statements
1 2 3 4 5 6 7 8 |
Sub Faster_Example() With Sheets("Sheet2") .Range("D9").FormulaR1C1 = "example" .Range("D12").FormulaR1C1 = "demo" .Range("D9").Font.Bold = True .Range("D12").Font.Bold = True End With End Sub |
1 2 3 4 5 6 |
Sub Slow_Example() Sheets("Sheet2").Range("D9").FormulaR1C1 = "example" Sheets("Sheet2").Range("D12").FormulaR1C1 = "demo" Sheets("Sheet2").Range("D9").Font.Bold = True Sheets("Sheet2").Range("D12").Font.Bold = True End Sub |
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.
1 2 3 4 5 |
Sub UnProtectSheet() Sheets(“sheet1”).Unprotect ”password” 'Edit Sheet1 Sheets(“sheet1”).Protect ”password” End Sub |
Instead, you can protect sheets with setting UserInterfaceOnly:=True. This allows VBA to make changes to sheets, while still protecting them from the user.
1 |
Sheets(“sheet1”).Protect Password:="password", UserInterFaceOnly:=True |
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:
1 2 3 4 5 6 |
Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="password", UserInterFaceOnly:=True Next ws End Sub |
or this code in any regular module:
1 2 3 4 5 6 |
Private Sub Auto_Open() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Password:="password", UserInterFaceOnly:=True Next ws End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Sub LoopRange() Dim cell As Range Dim tStart As Double tStart = Timer For Each cell In Range("A1:A100000") cell.Value = cell.Value * 100 Next cell Debug.Print (Timer - tStart) & " seconds" End Sub Sub LoopArray() Dim arr As Variant Dim item As Variant Dim tStart As Double tStart = Timer arr = Range("A1:A100000").Value For Each item In arr item = item * 100 Next item Range("A1:A100000").Value = arr Debug.Print (Timer - tStart) & " seconds" End Sub |
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!