Revising recorded Visual Basic macros

The macro recorder is a great tool for discovering the Visual Basic methods and properties you want to use. If you don't know what properties or methods to use, turn on the macro recorder and manually perform the action. The macro recorder translates your actions into Visual Basic code. There are, however, some limitations to recording macros. You cannot record the following:

Conditional branches
Variable assignments
Looping structures
Custom user forms
Error handling
Text selections made with the mouse (you must use keyboard combinations).

To enhance your macros, you may want to revise the code recorded into your module.

Removing the Selection property

Macros created using the macro recorder depend on the selection. At the beginning of most recorded macro instructions, you'll see "Selection." Recorded macros use the Selection property to return the Selection object. For example, the following example moves the selection to the Temp bookmark and inserts text after the bookmark.

Sub Macro1()
Selection.Goto What:=wdGotoBookmark, Name:="Temp"
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeText Text:="New text"
End Sub

This macro accomplishes the task, but there are a couple of drawbacks. First, if the document doesn't have a bookmark named Temp, the macro posts an error. Second, the macro moves the selection, which may not be appropriate. Both of these issues can be resolved by revising the macro so that it doesn't use the Selection object. This is the revised macro.

Sub MyMacro()
If ActiveDocument.Bookmarks.Exists("Temp") = True Then
    endloc = ActiveDocument.Bookmarks("Temp").End
    ActiveDocument.Range(Start:=endloc, _
        End:=endloc).InsertAfter "New text"
End If
End Sub

The Exists method is used to check for the existence of the bookmark named Temp. If the bookmark is found, the bookmark's ending character position is returned by the End property. Finally, the Range method is used to return a Range object that refers to the bookmark's ending position, so that text can be inserted using the InsertAfter method. For more information on defining Range objects, see Working with Range objects.

Using With…End With

Macros instructions that refer to the same object can be simplified using a With…End With structure. For example, the following macro was recorded when a title was added at the top of a document.

Sub Macro1()
Selection.HomeKey Unit:=wdStory
Selection.TypeText Text:="Title"
Selection.ParagraphAlignment.Alignment = wdAlignParagraphCenter
End Sub

The Selection property is used with each instruction to return a Selection object. The macro can be simplified so that the Selection property is only used once.

Sub MyMacro()
With Selection
    .HomeKey Unit:=wdStory
    .TypeText Text:="Title"
    .ParagraphAlignment.Alignment = wdAlignParagraphCenter
End With
End Sub

The same task can also be performed without using the Selection object. The following macro uses a Range object at the beginning of the active document to accomplish the same task.

Sub MyMacro()
With ActiveDocument.Range(Start:=0, End:=0)
    .InsertAfter "Title"
    .ParagraphFormat.Alignment = wdAlignParagraphCenter
End With
End Sub

Removing unnecessary properties

If you record a macro that involves selecting an option in a dialog box, the macro recorder records the settings of all the options in the dialog box, even if you only change one or two options. If you don't need to change all the options, you can remove the unnecessary properties from the recorded macro. The following recorded macro includes a number of options from the Paragraph dialog box (Format menu).

Sub Macro1()
With Selection.ParagraphFormat
    .LeftIndent = InchesToPoints(0)
    .RightIndent = InchesToPoints(0)
    .SpaceBefore = 6
    .SpaceAfter = 6
    .LineSpacingRule = 0
    .Alignment = wdAlignParagraphLeft
    .WidowControl = True
    .KeepWithNext = False
    .KeepTogether = False
    .PageBreakBefore = False
    .NoLineNumber = False
    .Hyphenation = True
    .FirstLineIndent = InchesToPoints(0)
    .OutlineLevel = 10
End With
End Sub

However, if you only want to change the spacing before and after the paragraph, you can change the macro to the following.

Sub MyMacro()
With Selection.ParagraphFormat
    .SpaceBefore = 6
    .SpaceAfter = 6
End With
End Sub

The simplified macro executes faster because it sets fewer properties. Only the spacing before and after are changed; all the other settings for the selected paragraphs are unchanged.

Removing unnecessary arguments

When the macro recorder records a method, the values of all the arguments are included. The following macro was recorded when the document named Test.doc was opened. The resulting macro includes all the arguments for the Open method.

Sub Macro1()
Documents.Open FileName:="C:\My Documents\Test.doc", _
    ConfirmConversions:= False, ReadOnly:=False, _
    AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto
End Sub

The arguments that are not needed can be removed from the recorded macro. For example, you could remove all of arguments set to an empty string (for example, WritePasswordDocument:=""), as shown.

Sub MyMacro()
Documents.Open FileName:="C:\My Documents\Test.doc", _
    ConfirmConversions:= False, _
    ReadOnly:=False, AddToRecentFiles:=False, _
    Revert:=False, Format:=wdOpenFormatAuto
End Sub