Faster code in Excel VBA - speed up your macros

With small macros speed is irrelevant - they execute in a split second. However when your programs start to grow bigger and bigger, efficient code and speed becomes an isssue.

You can do several things to boost the speed, and one of the most effective tricks is to switch off screen updating, when the code is executing.

Screen updating

If the macro writes to cells or switches between worksheets, the screen can flicker a lot. Excel uses many resources on screen updating, and often you can get a considerable speed increase by switching off screen updating, when your macro starts.

When the macro has finished, you switch the updating back on. It looks like this:

Application.ScreenUpdating = False 'Switch off

Application.ScreenUpdating = True  'Switch on

It is a pain, if the program gets an error and crashes before it has switched the screen updating back on. So it makes sense to use error handling to make sure that the updating is switched on no matter what. You can do it like this:

Sub Something()

On Error GoTo ErrorHandle

Application.ScreenUpdating = False

'Action

BeforeExit:
Application.ScreenUpdating = True

Exit Sub
'We land here in case of an error
ErrorHandle:
MsgBox Err.Description & " Sub Something"
Resume BeforeExit 'Sends you back to BeforeExit

End Sub

Normally the screen updating is switched back on just before "Exit Sub". If you don't use error handling (like here), the program will just crash.

Here we wrote "On Error GoTo ErrorHandle", so if you get an error, the program will jump to the label "ErrorHandle:" and after the errorhandling it will meet the "Resume BeforeExit". This tells the program to go back to the label "BeforeExit", and screen updating will be switched back on before exit.

Automatic calculation

In normal mode Excel will recalculate, if you change a cell value that affects other cell values. This too can steal time, if you run a large application, and it is possible to switch off the automatic calculation:

Application.Calculation = xlCalculationManual    'Switch off

Application.Calculation = xlCalculationAutomatic 'Switch on

However you must consider, that if automatic calculation is switched off, Excel will not update cell values, so if your macro needs updated cell values, it is necessary to force a recalculation, either for a specific range: "Range("MyRange").Calculate" or the whole workbook: "Calculate".

Lean code

Very often you can speed up execution by writing lean code. Here are a few tips.

Use Range objects instead of "Selection"

If you use recorded macros, it is usually possible to increase speed considerably by changing the code slightly. Recorded macros tend to select cells and then work with the Selection. It can look like this:

Range("A1:A6").Select
Selection.Font.Bold = True

'and

Range("A1:A6").Select
Selection.ClearContents

It is much faster to operate directly on the range without selecting it. Like this:

Range("A1:A6").Font.Bold = True

'and

Range("A1:A6").ClearContents

Also, when you record a macro, the code will often contain a lot of unnecessary statements such as the text is not underlined, striked through or whatever. Usually it is quite safe to delete this.

With...End With

If you need to manipulate an object (e.g. a cell) more than once, it can save time to operate directly on the object using "With...End With" instead of going through the whole "chain of command" to access the object. Like this:

With Worksheets(1).Range("A1").Font
   .Italic = True
   .Underline = xlUnderlineStyleSingle
   .ColorIndex = 3
End With

Worksheets(1).Range("A1").Font.Italic = True
Worksheets(1).Range("A1").Font.Underline = xlUnderlineStyleSingle
Worksheets(1).Range("A1").Font.ColorIndex = 3

Select Case instead of If Then

If the next step depends on the value of a variable, it is faster to use a "Select Case" construction instead of many "If...Then" sentences. For instance:

Select Case vValue
   Case Is = 0
      'Action
   Case Is > 0 < 10
      'Action
   Case Is >= 10
      'Action
   Case Else
      'Action
End Select

Worksheet functions

It can be good fun to write your own functions, but if one of Excel's built-in functions can do the trick, it will always be much faster. For instance the following example of using the worksheet average function is much faster than any homemade average function:

MyAverage = Application.WorksheetFunction.Average(Range("A1:A6"))

For Each loops

If you need to loop through collections (like e.g. Worksheets or Ranges), the fastest loop is a For Each Next construction. The first loop below will be faster than the second:

Dim rCell as range
Dim rRange as Range

Set rRange = Range("A1:A10")
For Each rCell in rRange
   MsgBox rCell.Value
Next

'******************

Dim i as Integer
Dim rRange as Range

Set rRange = Range("A1:A10")
For i = 1 To rRange.Count
   MsgBox rRange.Item(i).Value
Next i

Arrays and Ranges

If you have many operations on big tables (Ranges), it can sometimes be much faster to copy the range to an array, make the operations in the array and then copy back.