Excel 2016 VBA Speed Tips - dummies

By John Walkenbach

VBA for Excel 2016 is fast, but it’s not always fast enough. (Computer programs are never fast enough.) Keep reading to discover some programming examples you can use to speed up your macros.

Turning off screen updating

When executing a macro, you can sit back and watch all the onscreen action that occurs in the macro. Although doing this can be instructive, after you get the macro working properly, it’s often annoying and can slow the performance of your macro considerably. Fortunately, you can disable the screen updating that normally occurs when you execute a macro. To turn off screen updating, use the following statement:

Application.ScreenUpdating = False

If you want the user to see what’s happening at any point during the macro, use the following statement to turn screen updating back on:

Application.ScreenUpdating = True

To demonstrate the difference in speed, execute this simple macro, which fills a range with numbers:

Sub FillRange()
  Dim r as Long, c As Long
  Dim Number as Long
  Number = 0
  For r = 1 To 50
    For c = 1 To 50
      Number = Number + 1
      Cells(r, c).Select
      Cells(r, c).Value = Number
    Next c
  Next r
End Sub

You see each cell being selected and the value being entered in the cells. Now insert the following statement at the beginning of the procedure and execute it again:

Application.ScreenUpdating = False

The range is filled much faster, and you don’t see the result until the macro is finished running and screen updating is (automatically) set to True.

When you’re debugging code, program execution sometimes ends somewhere in the middle without your having turned Screen updating back on. This sometimes causes Excel’s application window to become totally unresponsive. The way out of this frozen state is simple: Go back to the VBE, and execute the following statement in the Immediate window:

Application.ScreenUpdating = True

Turning off automatic calculation

If you have a worksheet with many complex formulas, you may find that you can speed things considerably by setting the calculation mode to manual while your macro is executing. When the macro finishes, set the calculation mode back to automatic.

The following statement sets the Excel calculation mode to manual:

Application.Calculation = xlCalculationManual

Execute the next statement to set the calculation mode to automatic:

Application.Calculation = xlCalculationAutomatic

If your code uses cells with formula results, turning off calculation means that the cells will not be recalculated unless you explicitly tell Excel to do so!

Eliminating those pesky alert messages

As you know, a macro can automatically perform a series of actions. In many cases, you can start a macro and then go hang out in the break room while Excel does its thing. Some Excel operations, however, display messages that require a human response. These types of messages mean that you can’t leave Excel unattended while it executes your macro — unless you know the secret trick.

You can instruct Excel to not display these types of alerts while running a macro.
You can instruct Excel to not display these types of alerts while running a macro.

The secret trick to avoiding these alert messages is inserting the following VBA statement into your macro:

Application.DisplayAlerts = False

Excel executes the default operation for these types of messages. In the case of deleting a sheet, the default operation is Delete. If you’re not sure what the default operation is, perform a test to see what happens.

When the procedure ends, Excel automatically resets the DisplayAlerts property to True. If you need to turn the alerts back on before the procedure ends, use this statement:

Application.DisplayAlerts = True

Simplifying object references

As you probably already know, references to objects can become very lengthy. For example, a fully qualified reference to a Range object may look like this:

Workbooks(“MyBook.xlsx”).Worksheets(“Sheet1”) _

If your macro frequently uses this range, you may want to create an object variable by using the Set command. For example, the following statement assigns this Range object to an object variable named Rate:

Set Rate = Workbooks(“MyBook.xlsx”) _

After defining this object variable, you can use the variable Rate rather than the lengthy reference. For example, you can change the value of the cell named InterestRate:

Rate.Value = .085

This is much easier to type than the following statement:

Workbooks(“MyBook.xlsx”).Worksheets(“Sheet1”). _
  Range(“InterestRate”) = .085

In addition to simplifying your coding, using object variables speeds your macros considerably.

Declaring variable types

You usually don’t have to worry about the type of data you assign to a variable. Excel handles all the details for you behind the scenes. For example, if you have a variable named MyVar, you can assign a number of any type to that variable. You can even assign a text string to it later in the procedure.

If you want your procedures to execute as fast as possible, tell Excel what type of data will be assigned to each of your variables. This is known as declaring a variable’s type.

In general, you should use the data type that requires the smallest number of bytes yet can still handle all the data assigned to it. When VBA works with data, execution speed depends on the number of bytes VBA has at its disposal. In other words, the fewer bytes data uses, the faster VBA can access and manipulate the data. An exception to this is the Integer data type. If speed is critical, use the Long data type instead.

If you use an object variable, you can declare the variable as a particular object type. Here’s an example:

Dim Rate as Range
Set Rate = Workbooks(“MyBook.xlsx”) _

Using the With-End With structure

Do you need to set a number of properties for an object? Your code runs faster if you use the With-End With structure. An additional benefit is that your code may be easier to read.

The following code does not use With-End With:

Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = True
Selection.Orientation = 0
Selection.ShrinkToFit = False
Selection.MergeCells = False

Here’s the same code, rewritten to use With-End With:

With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = True
  .Orientation = 0
  .ShrinkToFit = False
  .MergeCells = False
End With

When you use With-End With, make sure that each statement begins with a dot.