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
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
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: