In This Chapter

Halting sheet calculations

Disabling sheet screen updating

Turning off status bar updating

Telling Excel to ignore events

Hiding page breaks

Suspending pivot table updates

Steering clear of copy and paste

Using the With statement

Avoiding the Select method

Limiting trips to the worksheet

As your 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.

You can take steps to improve the performance of your macros. In this chapter, you find 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.

If your workbook is formula intensive, you may not want Excel to trigger a recalculation every time a cell value is altered by your macro. 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.

Turning off the automatic calculation behavior of Excel can dramatically speed up your macro. The idea is to 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.

If your macro relies on updated values during processing, you'll want to force a calculation so that the macro has the latest values. You can force Excel to calculate by using the Application.Calculate method. Simply enter Application.Caculate as a line in your code where appropriate.

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. In most cases, you don’t need Excel using up resources to redraw the screen each time your macro performs some action.

In addition to setting the calculation mode to manual, 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. For example, if you copy and paste a range, Excel will show the progress of that operation on the status bar. Often times, the action is performed so fast that you don’t see the status bar progress. However, 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. That is to say, 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

As discussed in Chapter 3, 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, suppose you have a Worksheet_Change event implemented for Sheet1 of your workbook. Any time a cell or a range is altered, the Worksheet_Change event will fire.

So 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 imagine how this behavior would slow down your macro.

You can add another level of performance boosting by using the EnableEvents property to tell Excel to ignore events while your macro runs.

Simply 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

Although disabling events can indeed speed up your macros, you may need some events to trigger while your macro runs. Be sure to think about your specific scenario and determine what will happen if your worksheet or workbook events are turned off while your macro runs.

Hiding Page Breaks

Another opportunity for a performance boost can be found in 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. Each change you make to the structure of the pivot table requires Excel to recalculate the values in the pivot table for each pivot field your macro touches.

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.

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

    Range("A1").Select

    Selection.Copy

    Range("B1").Select

    ActiveSheet.Paste

Although this code will indeed copy from cell A1 and paste into B1, it forces Excel to utilize the clipboard, which adds a kind of middleman where there does not need to be one.

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.

Range(“A1”).Copy Destination:=Range(“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. For example, your code may change the formatting of cell A1 so that it is underlined, italicized, and formatted bold. If you were to record a macro that applies these formatting options to cell A1, you would get something like this:

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

Unfortunately, this code is not as efficient as it could be because it forces Excel to select and then change each property separately.

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

If you were to record a macro while entering the value 1000 in cell A1 for multiple sheets, you would end up with code that looks similar to the following:

    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1000"

    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1000"

    Sheets("Sheet3").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1000"

As you can see, Macro Recorder is fond of using the Select method to explicitly select objects before taking actions on them. Although this code will run fine, it is not efficient because it forces Excel to take the time to explicitly select each object that is being manipulated.

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.