In a recent post, I showed how to record a macro to save yourself time and effort with that repetitive task. The recorder has captured all of your keystrokes, intentional and otherwise. Objects are selected first, then acted upon. Defaults are applied indiscriminately every time the recorder followed you into a dialog. Actions you take and undo are all recorded. The result is syntactically accurate, but inefficient.

Let’s clean up the code to make it run faster and smoother, without the flashing shown on screen. Go to the VB Editor and open the code module containing your recorded macro. The code recorded in the previous example is shown below.

Sub RecordedMacro()
 '
 ' RecordedMacro Macro
 ' Macro recorded 3/7/2008 by Jon Peltier
 '

'
     With Selection
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlBottom
         .WrapText = False
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
     End With
     With Selection.Font
         .Name = "Arial"
         .Size = 10
         .Strikethrough = False
         .Superscript = False
         .Subscript = False
         .OutlineFont = False
         .Shadow = False
         .Underline = xlUnderlineStyleNone
         .ColorIndex = xlAutomatic
     End With
     With Selection.Font
         .Name = "Arial"
         .Size = 12
         .Strikethrough = False
         .Superscript = False
         .Subscript = False
         .OutlineFont = False
         .Shadow = False
         .Underline = xlUnderlineStyleNone
         .ColorIndex = xlAutomatic
     End With
     Selection.Font.ColorIndex = 3
 End Sub
 

That’s a lot of code for the small number of actions that were done while recording. We centered the text in the selected cells, changed the font to Arial, changed the font size to 12 points, and changed the text to red. Every time an action was taken, properties which were not changed during that action were still recorded, despite being unchanged. For example, the font’s ColorIndex was twice ”changed” to xlAutomatic, before finally being changed to 3 (red in the default Excel color palette). Removing the redundant and default statements greatly reduces the code.

Sub RecordedAndFixedMacro()
 ' Macro recorded & fixed up 3/7/2008 by Jon Peltier
 '
     With Selection
         .HorizontalAlignment = xlCenter
     End With
     With Selection
         With .Font
             .Name = "Arial"
             .Size = 12
             .ColorIndex = 3
         End With
     End With
 End Sub
 

This macro didn’t have the usual repeated cycle of select an object, format the selection, select another object, so I’ve included another sample below.

Sub SelectAndFormatRecording()
 '
     Range("C9:G9").Select
     Selection.Font.Bold = True
     Range("C11:G11").Select
     Selection.Font.ColorIndex = 5
 End Sub
 

By avoiding the sequence of selections, the code is smaller and runs faster, and the screen doesn’t change or even flash with each subsequent selection change.

Sub FormatByReference()
 '
     Range("C9:G9").Font.Bold = True
     Range("C11:G11").Font.ColorIndex = 5
 End Sub
 

A technique for streamlining code is to group operations performed on each object within With/End With blocs. The first recorded macro above was recorded with several With/End With blocks. But frequently when recording macros, we are carrying out operations as we think of them, and operations on the same object may be scattered around the macro. In this case, we can construct our own blocks, first rearranging the code, then forming the blocks.

Sub DisorderlyRecording()
 '
     Range("J12:M19").Font.Bold = True
     Range("J14:M14").Font.ColorIndex = 5
     Range("J12:M19").Font.Name = "Courier New"
     Range("J14:M14").Font.Italic = True
     Range("J12:M19").HorizontalAlignment = xlCenter
 End Sub
 

Reordering…

Sub ReorderedRecording()
 '
     Range("J12:M19").HorizontalAlignment = xlCenter
     Range("J12:M19").Font.Bold = True
     Range("J12:M19").Font.Name = "Courier New"
     Range("J14:M14").Font.ColorIndex = 5
     Range("J14:M14").Font.Italic = True
 End Sub
 

Blocking…

Sub ReorderedAndBlocked()
 '
     With Range("J12:M19")
         .HorizontalAlignment = xlCenter
         With .Font
             .Bold = True
             .Name = "Courier New"
         End With
     End With
     With Range("J14:M14").Font
         .ColorIndex = 5
         .Italic = True
     End With
 End Sub
 

Note that you can nest one With/End With block within another.

For a more detailed look at optimizing recorded macros and using Excel’s object model more effectively, see my colleague Tushar Mehta’s Beyond the macro recorder.

In a series of upcoming posts, I’ll cover the following VB topics:

• Assigning a macro to a button or shape, ActiveX button, or menu or toolbar
• VBE settings
• VBE features
• Debugging and error proofing

Related Posts:

• How To: Use Someone Else’s Macro
• Make Your Recorded Macro Independent of Which Sheet is Active
• How To: Record Your Own Macro
• How To: Assign a Macro to a Button or Shape
• Quick VBA Routine: XY Chart with Axis Titles
• Chart Event Class Module to Highlight a Series
• Recorded Macro Error with Bubble and Stock Charts
• Stack Columns In Order Of Size With VBA
• Highlight a Series with a Click or a Mouse Over
• Count Bold Cells in a Range