More Than 10 Ways to Speed Up Macros

"All roads lead to Rome", the same task can also be accomplished in different ways in VBA. When starting to learn programming, it is often enough to achieve the goal, and not to think more about the running speed and resource consumption of the code.

As the level of programming increases and the complexity of the project increases, you will increasingly feel the importance of code optimization.

You can take steps to improve the performance of your macros. In this article, you will find ten ways to help you keep your Excel macros running at optimal performance levels.

Turn Off Automatic Calculation

When automatic calculation is enabled, Excel recalculates the workbook automatically each time a value affecting a formula changes. In worksheets with a large number of formulas, this behavior can slow down your macros considerably.

If your workbook is formula-intensive, you may not want Excel to trigger a recalculation every time a macro changes a cell value.

Calculation Properties to tell Excel to switch to manual calculation mode. When a workbook is in manual calculation mode, the workbook does not recalculate until you explicitly trigger the calculation by pressing the F9 key on the keyboard.

Turning off Excel's automatic calculation behavior can dramatically speed up your macros. The idea is to put Excel in Manual Calculation mode, run your code, and then switch back to Automatic Calculation mode.

'------------- mdlCalculationMode -------------
Sub CalculationMode()
    Application.Calculation = xlCalculationManual
    'Place your macro code here
    Application.Calculation = xlCalculationAutomatic
End Sub

Please note that setting the calculation mode back to xlCalculationAutomatic will automatically trigger a recalculation of the worksheet. Therefore, you do not need to press F9 after your macro has run.

Depending on your needs, you may actually need to perform calculations during the macro run, in which case you do not want to invoke the manual calculation mode. Be sure to consider your application scenario and determine what happens when you turn off automatic calculation while the macro is running.

Disable Screen Updating

You may notice that your screen flickers quite a bit when your macros are running. This flickering is Excel's attempt to redraw the screen to show the current state of the worksheet. Unfortunately, each time Excel redraws the screen, it takes time. In most cases, you don't need Excel to run out of resources to redraw the screen every time a macro performs some action.

ScreenUpdating property to disable any screen updates until your macro has finished running. This saves time and resources, and allows your macro to run a little faster. You can turn ScreenUpdating back on after the macro code has finished running.

'------------- mdlDisableScreenUpdating -------------
Sub DisableScreenUpdating()
    Application.ScreenUpdating = False
    'Place your macro code here
    Application.ScreenUpdating = True
End Sub

After setting the ScreenUpdating property back to True, Excel will not only resume redrawing, but will also trigger the first redraw to show all changes from the macro.

Turn Off Status Bar Updates

At the bottom of the Excel window, you will see the Excel Status Bar. The status bar usually shows the progress of certain actions in Excel. For example, if you copy/paste a range, Excel will show the progress of that action in the status bar. Usually, the operation is performed so quickly that you can't see the progress in the status bar. However, if your macro is processing a lot of data, the status bar will take up some resources.

Note that turning off screen updates is separate from turning off the status bar display. This means that the status bar will continue to update even if you disable screen updates. You can further improve the macro's performance by temporarily disabling any status bar updates using the Application.

'------------- mdlStatusBarUpdates -------------
Sub StatusBarUpdates()
    Application.DisplayStatusBar = False
    'Place your macro code here
    Application.DisplayStatusBar = True
End Sub

Ignore Events

You can implement macros as event procedures that tell Excel to run certain code when a worksheet or workbook changes.

Sometimes, the standard macros make changes that actually trigger the event procedure. For example, suppose you implement the Worksheet_Change event for Sheet1 of a workbook. Whenever a cell or range is changed, the Worksheet_Change event is triggered.

So if you have a standard macro to manipulate multiple cells on Sheet1, each time you change a cell on that sheet, your macro must pause while the Worksheet_Change event runs. You can imagine how this behavior would slow down your macro.

Another performance improvement at this point is to use the EnableEvents property to tell Excel to ignore events while the macro is running.

Simply set the EnableEvents property to False before running the macro. After the macro code has run, you can set the EnableEvents property back to True.

'------------- mdlIgnoreEvents -------------
Sub IgnoreEvents()
    Application.EnableEvents = False
    'Place your macro code here
    Application.EnableEvents = True
End Sub

While it is true that disabling events can speed up your macros, you may actually need to trigger some events while the macro is running. Be sure to consider your particular scenario and determine what happens if you turn off worksheet or workbook events while the macro is running.

Hide Page Breaks

If you display Page Breaks on a worksheet, Excel is forced to spend time recalculating where to display page breaks on the worksheet every time your macro modifies the number of rows, columns, or changes the sheet's page settings.

You can avoid this by simply hiding the page breaks before starting the macro. Set the DisplayPageBreaks worksheet property to False to hide the page breaks. If you want to continue to display page breaks after the macro has run, you can set the DisplayPageBreaks worksheet property back to True.

'------------- mdlDisplayPageBreaks -------------
Sub DisplayPageBreaks()
    ActiveSheet.DisplayPageBreaks = False
    'Place your macro code here
    ActiveSheet.DisplayPageBreaks = True
End Sub

Suspend PivotTable Updates

If your macro operates on PivotTables that contain large data sources, you may experience poor performance when performing operations such as dynamically adding or moving pivot fields. This is because each change you make to the PivotTable structure requires Excel to recalculate the values in the PivotTable for each pivot field that your macro touches.

You can improve the macro's performance by pausing the recalculation of the PivotTable until all pivot field changes are completed. Simply set the PivotTable.ManualUpdate property to True to delay the recalculation, run the macro code, and then set the PivotTable.ManualUpdate property back to False to trigger the recalculation.

'------------- mdlPivotTablesManualUpdate -------------
Sub PivotTablesManualUpdate()
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
    'Place your macro code here
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
End Sub

Avoid Copy and Paste

It is important to remember that while the Macro Recorder saves time by writing VBA code for you, it does not always write the most efficient code. A typical example is how the Macro Recorder captures any copy and paste operations that you perform while recording.

If you were to copy cell A1 and paste it into cell B1 while recording a macro, the Macro Recorder would capture the following.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
End Sub

While this code does copy and paste from cell A1 into B1, it forces Excel to use the clipboard, which adds a kind of middleman where it is not needed.

You can slightly enhance your macro by removing the middleman and performing a direct copy from one cell to the destination cell. This alternative code uses the Destination parameter to bypass the clipboard and copy the contents of cell A1 directly to cell B1.

Range("A1").Copy Destination:=Range("B1")

If you only need to copy values (and not formatting or formulas), you can further improve performance by avoiding the Copy method altogether. Simply set the value of the target cell to the same value as in the source cell. This method is about 25 times faster than using the Copy method.

Range("B1").Value = Range("A1").Value

If you only need to copy formulas from one cell to another (not values or formats), you can set the formula in the target cell to the same formula contained in the source cell.

Range("B1").Formula = Range("A1").Formula

Use the With Statement

When recording macros, you will often manipulate the same object multiple times.

For example, your code may change the formatting of cell A1 so that it is underlined, italicized, and bold formatted. If you record a macro while applying these formatting options to cell A1, you will get the following.

Sub Macro2()
    Range("A1").Select
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub

Unfortunately, this code is not as efficient as it should be, because it forces Excel to select and then reference each changed object.

You can save time and improve performance by using the With statement to perform multiple operations on a given object while only referencing the object once. The With statement in this example tells Excel to change three properties, but only references the Font object once, using fewer resources.

With Range("A1").Font
    .Bold = True
    .Italic = True
    .Underline = xlUnderlineStyleSingle
End With

Getting into the habit of chunking operations into With statements not only makes macros run faster, but also helps make the code easier to read.

Remove the Select methods

If you record a macro by entering the value 1234 in cell A1 of multiple worksheets, you will end up with code similar to this.

Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "1234"
Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "1234"
Sheets("Sheet3").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "1234"

As you can see, the macro recorder likes to use the "Select" method to explicitly select objects before manipulating them. While this code works quickly, it's not that efficient. It forces Excel to spend time explicitly selecting each object being manipulated.

Generally speaking, it is not necessary to select objects before processing them. In fact, you can greatly improve the performance of macros by not using the "select" method.

After recording your macro, get in the habit of changing the generated code, removing the select method. In this case, the optimized code would look like this. Note that nothing is selected. The code just uses the hierarchy of objects to apply the desired action.

Sheets("Sheet1").Range("A1").FormulaR1C1 = "1234"
Sheets("Sheet2").Range("A1").FormulaR1C1 = "1234"
Sheets("Sheet3").Range("A1").FormulaR1C1 = "1234"

Avoid Excessive Reference

When calling a method or property of an object, it needs to go through the IDispatch interface of the OLE component. The calls to these OLE components take time, so reducing the number of references to OLE components can improve the speed of the macro code.

For the invocation of object properties or methods, the representation method of Object.Method is generally used, that is, the "." symbol is used to invoke properties and methods.

Therefore, the number of method or property calls can be judged according to the number of symbols ".". The less the "." symbol, the faster the code runs.

For example the following statement includes 3 symbols ".".

ThisWorkbook.Sheet1.Range("A1").Value = 100

The following statement has only one symbol ".".

Activewindow.Top = 100

Here are some tricks to reduce the number of symbols "." to run faster.

First, when you need to refer to the same object repeatedly, you can set the object to a variable to reduce the number of calls. For example, the following code requires two calls per line.

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

Because the Sheets("Sheet1") object needs to be referenced repeatedly, it can be set to a variable sht first, so that each code only needs to be called once.

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

Second, if you don't want to declare a temporary variable sht, you can also use the With statement mentioned earlier. As shown in the following example:

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

Third, when there are a lot of loops, try to keep properties and methods outside the loop. When reusing a property value of the same object in a loop, you can first assign the property value to a specified variable outside the loop, and then use the variable in the loop, which can achieve faster speed. As shown in the following example:

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

Each loop in this example gets the Value property of the cell Cells(1,2). If you assign the Value property of Cells(1.2) to a variable before the loop starts, you will get a faster run. As shown in the following example:

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

The above code calls ThisWorkbook.Sheets("Sheet1") every time it loops. You can do this faster by using the With statement to move the call to ThisWorkbook.Sheets("Sheet1") outside the loop. As shown in the following example:

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

Avoid Using Variant Types

Beginners usually prefer to use Variant type variables, which has the advantage of being less complicated because any type of data can be used in without the problem of memory overflow if the data is too large for the Integer or Long data types. However, Varienmt type data requires more extra memory space than the other specified types (2 bytes for Integer data, 4 bytes for Long data, and 16 bytes for Variant data), VBA requires more time to process Variant-type data than other specified types of data. As the following example shows.

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

In the above code, lines 8 to 13 do 1 million operations of addition, subtraction, multiplication and division of Variant variables, and lines 17 to 22 do 1 million operations of addition, subtraction, multiplication and division of Integer variables. On my computer, the operation of the Variant variable took about 0.09375 seconds, while the operation of the Integer variable took about 0.03125 seconds. The results may vary from computer to computer, but Variant variables are significantly slower than Integer variables.

For this reason, it is recommended to avoid using Variant variables when you can explicitly use the specified data type.

Using Worksheet Functions or Methods

The Excel Worksheet Functions are generally much faster than just using VBA normal code to manipulating cell regions. As the following example shows.

Sub WorksheetFunctionTest()
    Dim Total1 As Long, Total2 As Long, i As Integer, tm As Date
    Dim cl
    tm = Timer
    For i = 1 To 1000
        For Each cl In Range("A1:A500")
            Total1 = Total1 + cl.Value
        Next
    Next i
    Debug.Print "VBA: " & Format((Timer - tm), "0.00000") & " seconds"
    tm = Timer
    For i = 1 To 1000
        Total2 = Total2 + WorksheetFunction.Sum(Range("A1:A500"))
    Next i
    Debug.Print "WorksheetFunction: " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

The WorksheetFunctionTest procedure compares the time taken by the two methods to calculate the sum of the worksheet range data.

Lines 5 to 9 use VBA to calculate the sum of range A1:A500, and lines 12 to 14 use the worksheet function SUM to calculate the sum of the same range, running each method 1000 times. On my computer, the former took about 0.78125 seconds, while the worksheet function method took only 0.01953 seconds.

Excel also includes other mathematical statistical functions such as PRODUCT, COUNTIF, etc., as well as some lookup functions such as VLOOKUP. Effective use of these functions can be obtained faster than the use of VBA code.

Using some other methods of manipulating cell areas such as Replace, Find and FindNext can also be faster than using VBA.

Using Array Instead of Range

If you only need to work with the values of the cells in the Range object without the properties and methods of the cells, you can use Array to work with the Range object, because the Array variables are much faster than the Range object's operations.

Sub ArrayTest()
    Dim Total1 As Long, Total2 As Long, i As Integer, tm As Date
    Dim cl
    Dim arr()
    tm = Timer
    arr = Range("A1:A500")
    For i = 1 To 1000
        For Each cl In arr
            Total1 = Total1 + cl
        Next
    Next i
    Debug.Print "Array: " & Format((Timer - tm), "0.00000") & " seconds"
    tm = Timer
    For i = 1 To 1000
        For Each cl In Range("A1:A500")
            Total2 = Total2 + cl.Value
        Next
    Next i
    Debug.Print "Range: " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

Lines 7 to 11 use Array to calculate the sum of range A1:A500, and lines 14 to 18 use Range to calculate the sum of the same range, running each method 1000 times. On my computer, the Array method took about 0.01563 seconds, while the Range method took only 0.72266 seconds.

Speed Up Your Macros VBA Code

In summary, you can usually speed up your macros with the following code.

'------------- mdlSpeedUpMacros -------------
Sub SpeedUpMacros()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    'ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
    'Place your macro code here
    'ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
End Sub