10 Ways to Speed Up Your Macros
As your Excel macros become increasingly robust and complex, you may find that they lose performance. When discussing macros, the word performance is usually synonymous with speed. Speed is how quickly your VBA procedures perform their intended tasks. Following are ten ways to help keep your Excel macros running at their optimum performance level.
Halting sheet calculations
Did you know that each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet? In worksheets that have a large amount of formulas, this behavior can drastically slow down your macros.
You can use the Application.Calculation property to tell Excel to switch to manual calculation mode. When a workbook is in manual calculation mode, the workbook will not recalculate until you explicitly trigger a calculation by pressing the F9 key.
Place Excel into manual calculation mode, run your code, and then switch back to automatic calculation mode.
Sub Macro1() Application.Calculation = xlCalculationManual 'Place your macro code here Application.Calculation = xlCalculationAutomatic End Sub
Setting the calculation mode back to xlCalculationAutomatic will automatically trigger a recalculation of the worksheet, so there is no need to press the F9 key after your macro runs.
Disabling sheet screen updating
You may notice that when your macros run, your screen does a fair amount of flickering. This flickering is Excel trying to redraw the screen to show the current state of the worksheet. Unfortunately, each time Excel redraws the screen, it takes up memory resources.
You can use the Application.ScreenUpdating property to disable screen updates until your macro has completed. Disabling screen updating saves time and resources, allowing your macro to run a little faster. After your macro code has finished running, you can turn screen updating back on.
Sub Macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = False 'Place your macro code here Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
After you set the ScreenUpdating property back to True, Excel will automatically trigger a redraw of the screen.
Turning off status bar updates
The Excel status bar, which appears at the bottom of the Excel window, normally displays the progress of certain actions in Excel. If your macro is working with lots of data, the status bar will take up some resources.
It’s important to note that turning off screen updating is separate from turning off the status bar display. The status bar will continue to be updated even if you disable screen updating. You can use the Application.DisplayStatusBar property to temporarily disable any status bar updates, further improving the performance of your macro:
Sub Macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.DisplayStatusBar = False 'Place your macro code here Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.DisplayStatusBar = True End Sub
Telling Excel to ignore events
You can implement macros as event procedures, telling Excel to run certain code when a worksheet or workbook changes.
Sometimes, standard macros make changes that will trigger an event procedure. For instance, if you have a standard macro that manipulates several cells on Sheet1, each time a cell on that sheet is changed, your macro has to pause while the Worksheet_Change event runs.
You can add another level of performance boosting by using the EnableEvents property to tell Excel to ignore events while your macro runs.
Set the EnableEvents property to False before running your macro. After your macro code is finished running, you can set the EnableEvents property back to True.
Sub Macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False 'Place your macro code here Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.EnableEvents = True End Sub
Hiding page breaks
Each time your macro modifies the number of rows, modifies the number of columns, or alters the page setup of a worksheet, Excel is forced to take time recalculating the page breaks shown on the sheet.
You can avoid this behavior by simply hiding the page breaks before starting your macro.
Set the DisplayPageBreaks sheet property to False to hide page breaks. If you want to continue to show page breaks after your macro runs, set the DisplayPageBreaks sheet property back to True.
Sub Macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False Activesheet.DisplayPageBreaks = False 'Place your macro code here Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.EnableEvents = True Activesheet.DisplayPageBreaks = True End Sub
Suspending pivot table updates
If your macro manipulates pivot tables that contain large data sources, you may experience poor performance when doing things like dynamically adding or moving pivot fields.
You can improve the performance of your macro by suspending the recalculation of the pivot table until all pivot field changes have been made. Simply set the PivotTable.ManualUpdate property to True to defer recalculation, run your macro code, and then set the PivotTable.ManualUpdate property back to False to trigger the recalculation.
Sub Macro1() ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True 'Place your macro code here ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False End Sub
Steering clear of copy and paste
It’s important to remember that although Macro Recorder saves time by writing VBA code for you, it does not always write the most efficient code. A prime example is how Macro Recorder captures any copy-and-paste action you perform while recording.
You can give your macros a slight boost by cutting out the middleman and performing a direct copy from one cell to a destination cell. This alternate code uses the Destination argument to bypass the clipboard and copy the contents of cell A1 directly to cell B1.
If you need to copy only values (not formatting or formulas), you can improve performance even more by avoiding the Copy method all together. Simply set the value of the destination cell to the same value found in the source cell. This method is about approximately 25 times faster than using the Copy method:
Range("B1").Value = Range("A1").Value
If you need to copy only formulas from one cell to another (not values or formatting), you can set the formula of the destination cell to the same formula contained in the source cell:
Range("B1").Formula = Range("A1").Formula
Using the With statement
When recording macros, you will often manipulate the same object more than once. You can save time and improve performance by using the With statement to perform several actions on a given object in one shot.
The With statement utilized in the following example tells Excel to apply all the formatting changes at one time:
With Range("A1").Font .Bold = True .Italic = True .Underline = xlUnderlineStyleSingle End With
Getting into the habit of chunking actions into With statements will not only keep your macros running faster but also make it easier to read your macro code.
Avoiding the Select method
Macro Recorder is fond of using the Select method to explicitly select objects before taking actions on them. There is generally no need to select objects before working with them. In fact, you can dramatically improve macro performance by not using the Select method.
After recording your macros, make it a habit to alter the generated code to remove the Select methods. In this case, the optimized code would look like the following:
Sheets("Sheet1").Range("A1").FormulaR1C1 = "1000" Sheets("Sheet2").Range("A1").FormulaR1C1 = "1000" Sheets("Sheet3").Range("A1").FormulaR1C1 = "1000"
Note that the nothing is being selected. The code simply uses the object hierarchy to apply the needed actions.
Limiting trips to the worksheet
Another way to speed up your macros is to limit the amount of times you reference worksheet data in your code. It is always less efficient to grab data from the worksheet than from memory. That is to say, your macros will run much faster if they do not have to repeatedly interact with the worksheet.
For instance, the following simple code forces VBA to continuously return to Sheets(“Sheet1”).Range(“A1”) to get the number needed for the comparison being performed in the If statement:
For ReportMonth = 1 To 12 If Range("A1").Value = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth
A much more efficient method is to save the value in Sheets(“Sheet1”).Range(“A1”) to a variable called MyMonth. This way, the code references the MyMonth variable instead of the worksheet:
Dim MyMonth as Integer MyMonth = Range("A1").Value For ReportMonth = 1 To 12 If MyMonth = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth
Consider leveraging variables to work with data in memory as opposed to directly referencing worksheets.