VBA Tips

The Macro Recorder is a mixed blessing. On the one hand it is a great way to automate repetitive, potentially tedious tasks. In addition, it is a great tool for learning how to program when used to illustrate objects that you need to manipulate to perform a given task. On the other hand, the Macro Recorder produces terribly inefficient code, and if you rely on it as your primary example of how to write code, you will develop some nasty habits. This week I’m going to discuss some things you can do to spruce up recorded macros. Fortunately, it’s fairly easy to tweak recorded macro code so that it runs more efficiently. To demonstrate this, I’ll record a macro that does some fairly standard macro activities. Then I’ll go through it and remove the unnecessary code.

Figure 1: A raw worksheet in need of formatting
Click to enlarge

The following code listing shows the code recorded by the macro to produce the worksheet shown in Figure 2.

Listing 1: Code produced by the Macro Recorder:

Sub Macro1()
'
' Macro1 Macro
'
Columns("B:N").Select
Selection.ColumnWidth = 9.86
Range("B3:N3").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B4:N9").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("A4:A8").Select
Selection.InsertIndent 1
Selection.Font.Bold = True
Range("A9:N9").Select
Selection.Font.Bold = True
Range("N3:N8").Select
Selection.Font.Bold = True
Range("B3:M3").Select
Selection.Font.Bold = True
Range("A2").Select
Selection.Font.Bold = True
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
End Sub

Figure 2: The worksheet after running a formatting macro
Click to enlarge

Looking at listing 1, the biggest thing that jumps out at me is the use of the Select method. The Macro Recorder has to record everything you do — it can’t make assumptions or educated guesses about what is really important or needed. One terribly inefficient yet common habit to avoid is using the Select method in conjunction with Selection. It is not necessary to select items in order to manipulate them programmatically. In Excel, the vast majority of the things you do as a user is manipulate ranges.

In the recorded code, any line that starts with Selection is actually manipulating a Range object. The main way you can make this code more efficient is to modify it to eliminate the use of Select and Selection as shown in Listing 2. In this listing, I commented out the unnecessary code (lines that start with an apostrophe) and followed each logical task with the statement that replaces the “commented out” code.

Generally after cleaning a macro in this manner and verifying it still works, I would delete all of the commented code. I left it in this listing so that you could easily compare the changes I made.

‘ A cleaner version of the recorded macro

Sub MoreEfficientMacro()
Dim ws As Worksheet
Set ws = ActiveSheet

'Columns("B:N").Select
'Selection.ColumnWidth = 9.86
ws.Range("B:N").ColumnWidth = 9.86

'Range("B3:N3").Select
'With Selection
' .HorizontalAlignment = xlRight
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
'End With
ws.Range("B3:N3").HorizontalAlignment = xlRight

'Range("B4:N9").Select
'Selection.Style = "Comma"
'Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
'Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
ws.Range("B4:N9").NumberFormat = _
"_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

'Range("A4:A8").Select
'Selection.InsertIndent 1
'Selection.Font.Bold = True
ws.Range("A4:A8").InsertIndent 1
ws.Range("A4:A8").Font.Bold = True

'Range("A9:N9").Select
'Selection.Font.Bold = True
'Range("N3:N8").Select
'Selection.Font.Bold = True
'Range("B3:M3").Select
'Selection.Font.Bold = True
'Range("A2").Select
'Selection.Font.Bold = True
ws.Range("A9:N9").Font.Bold = True
ws.Range("N3:N8").Font.Bold = True
ws.Range("B3:M3").Font.Bold = True
ws.Range("A2").Font.Bold = True

'Rows("3:3").Select
'Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Range("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'Range("A2").Select
Set ws = Nothing
End Sub

This listing could be improved in several more ways, but given the space of this column and without providing more background, I’ll need to save them for another day.

If you remove the comments, the cleaned version has 13 lines of statements versus 32 lines in the original version. From a performance standpoint, the cleaned version runs in less than half the time of the original version. Next week, we’ll go just a little bit deeper on this and show how you can halve the running time of the cleaned version with two lines of code.