VBA

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

In a recent post, I showed how to copy someone else’s VBA procedure (i.e., “macro”) and paste it into your own workbook. Fortunately you don’t have to rely on others for custom macros. Excel includes a Macro Recorder which captures your actions, so that you can replay them later, saving yourself time and keystrokes.

It’s easy to record and rerun macros. You can start the Macro Recorder in a number of ways.

The Macro Recorder will now record all of your steps until you turn it off, using one of several methods.

• Tools menu > Macro > Stop Recording
• Stop Recording button on the Visual Basic toolbar
• Stop Recording button on the Stop Recording toolbar

To see your handiwork, go to the VB Editor (VBE). There are three ways to get there:

• Tools menu > Macro > Visual Basic Editor
• Visual Basic Editor button on the Visual Basic toolbar
• Shortcut key combo Alt+F11

If your workbook had no macros to begin with, the Macro Recorder creates a new code module, which you can see in th Project Explorer Window. If the Project Explorer isn’t visible, go to View menu > Project Explorer.

To view the macro you’ve recorded, double click on the new code module.

The macro can be run in a number of ways:

• Tools menu > Macro > Macros, select the Macro, and click Run
• Shortcut Key combo Alt+F8, select the Macro, and click Run
• Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
• Click in the macro with the mouse and press F5

This is the first exposure most people have to VBA programming. In a series of upcoming posts, I’ll cover the following VB topics:

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