How to modify a recorded macro

Article contributed by Jay Freedman

Lots of articles advise you to use the macro recorder to get started with Word macros. An example is our own Creating a macro with no programming experience using the recorder. Although it's good advice, the macros you record often need tweaking—and sometimes they don't work at all.

What's wrong with the recorder, anyway?

The macro recorder's job is to translate your actions into programming code, using a language called Visual Basic for Applications (VBA). That's easy to do when you simply type some words into a document, or when you give a simple command such as Edit > Copy. It's harder for the recorder to make good code for more complicated commands such as File > Open or Edit > Replace. Sometimes the recorded code doesn't do exactly what you want it to do, and in a few situations the code is incorrect.

Cleaning out unneeded dialog arguments

You can record a macro while you make a change in the document by using a dialog, such as Format > Font or Format > Paragraph. You might think the macro recorder should capture only the settings that you changed, but it doesn't—instead, it throws into the macro every setting in the whole dialog! Look at what you might get if you just change the font size to 10 pt:

Sub Macro1()

  With Selection.Font

    .Name = "Times New Roman"

    .Size = 10

    .Bold = False

    .Italic = False

    .Underline = wdUnderlineNone

    .UnderlineColor = wdColorAutomatic

    .StrikeThrough = False

    .DoubleStrikeThrough = False

    .Outline = False

    .Emboss = False

    .Shadow = False

    .Hidden = False

    .SmallCaps = False

    .AllCaps = False

    .Color = wdColorAutomatic

    .Engrave = False

    .Superscript = False

    .Subscript = False

    .Spacing = 0

    .Scaling = 100

    .Position = 0

    .Kerning = 0

    .Animation = wdAnimationNone

  End With

End Sub

The lines that say "With Selection.Font" and "End With" mean that the lines between them set the properties of the font of the selected text. This is a shorthand way to refer to many properties of the same item, instead of writing "Selection.Font" at the beginning of each property's name.

In this macro the one property that you changed during recording is buried among the many that you didn't change, which take extra storage and extra execution time. That's bad enough. But suppose you replay the macro after you select text that's in a different font or a different color, thinking that it will just change the size to 10 pt. In fact, the macro will also change the text to Times New Roman and Automatic color, because the recorder captured every setting in the dialog. That probably isn't what you intended the macro to do.

Whenever you record the result of a dialog, you should inspect the code and delete all the extra settings. In this case, to apply only the size change, all you need is this:

Sub Macro1A()

  With Selection.Font

    .Size = 10

  End With

End Sub

There's now only one property being changed, so the "With Selection.Font" and "End With" are also unnecessary. The macro can be further simplified to this:

Sub Macro1B()

  Selection.Font.Size = 10

End Sub

If you aren't sure of the name of the property you need to keep, record a macro while you set a property to one value, and then record another macro while you set the same property to a different value. Compare the two macros—the property you want to keep is the one whose value changes, and the others can be deleted from the revised macro.

Making a macro more general

Suppose you record a macro that opens a document and then does something to it, such as changing the view. The beginning of the macro may look like this: 1

Sub Macro2()

  Documents.Open FileName:= "Lorem.doc" , _

      ConfirmConversions:= False , _

      ReadOnly:= False , AddToRecentFiles:= False , _

      PasswordDocument:= "" , PasswordTemplate:= "" , _

      Revert:= False , WritePasswordDocument:= "" , _

      WritePasswordTemplate:= "" , _

      Format:=wdOpenFormatAuto, XMLTransform:= ""

  ' more code, for example ...

  ActiveWindow.View = wdPrintView

End Sub

The recorded macro for opening a document, like the recording of the Format > Font dialog, contains unneeded things. In this case, they are parameters that contain information about the file, such as a password. The only parameter that's necessary is the FileName. You can remove the other parameters from the command, and Word will use its default values for them.

A more important problem is that every time you run the recorded macro, it will open the same document. This may be what you intend, but more likely you want the macro to let you choose which document to open.

One way to get the file's name into the macro is to display an input box, where you can type it in. The InputBox function shows a message box with a text entry field, and its result is the name that you type into the field.

Sub Macro2A()

  Dim MyFileName As String

  MyFileName = InputBox( "Enter file name to open:" , _

      "Open a Document" )

  If MyFileName <> "" Then

    Documents.Open FileName:=MyFileName

    ' more code, for example ...

    ActiveWindow.View = wdPrintView

  End If

End Sub

But this isn't very friendly. There's a good chance of making a typing mistake. And if the document isn't in the currently active folder, you have to type the entire path to it. A better idea is to use the File > Open dialog that's already built into Word, which lets you browse to and select the proper document. When you click the OK button in the dialog, Word opens the selected document. The macro is simpler, too, because it doesn't need a separate Documents.Open statement—the dialog handles it all for you.

Sub Macro2B()

  If Dialogs(wdDialogFileOpen).Show = - 1 Then

    ' more code, for example ...

    ActiveWindow.View = wdPrintView

  End If

End Sub

The word "Dialogs" in this code refers to a list of all of Word's built-in dialogs. Each dialog has a name that starts with "wdDialog". In this case, wdDialogFileOpen is the name of the built-in File > Open dialog, and the expression "Dialogs(wdDialogFileOpen)" selects that particular dialog from the list. To see all of the possible names, press F2 in the VBA editor to display the Object Browser, type wdDialog into the search box, and press Enter.

The word ".Show" refers to a method of the dialog. A method is an action that can be done—the .Show method causes the dialog to appear and execute (carry out its function).

Many methods also have a value after they execute, which tells the macro something about what just happened (this is called "returning" the value). In this case, if you click the OK button in the dialog then the .Show method returns the value –1, but if you click the Cancel button or the X in the title bar then .Show returns the value 0. The VBA help topic for each method tells you what values that method can return and what they mean. You can use the returned value in an If statement, as in Macro2B, to decide what to do.

Similar changes to recorded code let you make macros that save files to variable locations, search for variable strings, and many other unrecordable variations. You can find out more at Getting help with calling Word's built-in dialogs using VBA.

Making toggle macros

The Italic, Bold, and Underline buttons on the toolbar are toggles—click the button once to turn it on, and again to turn it off. If you want to make your own toggle for something else, you can record separate macros for turning it on and off, but how do you combine them into one?

As an example, let's make a macro to toggle the font's outline property on and off. If you record the change to turn it on, and remove the unnecessary properties, you get this:

Sub Macro3()

  With Selection.Font

    .Outline = True

  End With

End Sub

One way to make a toggle macro from this code is to use an If statement. You test the current value and then assign the opposite value to the property:

Sub Macro3A()

  With Selection.Font

    If .Outline = False Then

      .Outline = True

    Else

      .Outline = False

    End If

  End With

End Sub

A more efficient way is to use the Not operator. If the value is False, then applying Not to the value returns True, and vice versa. With this operator the macro can be written as

Sub Macro3B()

  With Selection.Font

    .Outline = Not .Outline

  End With

End Sub

Fixing broken Replace macros

One of the most common actions to record is a Replace operation. A macro can be a great time-saver, since setting up the same Replace over and over can be time-consuming and it's easy to make a mistake. In one circumstance, though, the recorder creates a macro that simply doesn't work.

Suppose you record the replacement of all italic text with the same text in bold italic. While you're recording this operation, it works perfectly well. If you replay the macro on another document, though, nothing happens! What's the matter?

A look at the recorded code reveals the problem:

Sub Macro4()

  Selection.Find.ClearFormatting

  Selection.Find.Replacement.ClearFormatting

  With Selection.Find

    .Text = ""

    .Replacement.Text = ""

    .Forward = True

    .Wrap = wdFindContinue

    .Format = True

    .MatchCase = False

    .MatchWholeWord = False

    .MatchWildcards = False

    .MatchSoundsLike = False

    .MatchAllWordForms = False

  End With

  Selection.Find.Execute Replace:=wdReplaceAll

End Sub

There's no mention of italic or bold italic anywhere in this code. Except for the notation .Format = True (which tells Word to use formatting information about the .Text or .Replacement properties while searching or replacing), the recorder has completely missed the fact that you were replacing one format with another. To make this macro work as intended, you have to add these lines:

.Font.Italic = True

.Replacement.Font.Bold = True

The first of these lines tells Word to search for italic text. The second line tells it to make the replacement text bold—because it's already italic, it will become bold italic.

Besides making the macro correct, I like to make it consistent. The "With" and "End With" statements are meant to replace the references to Selection.Find; that both speeds up the macro and makes it easier to read. You can pull the ClearFormatting and Execute statements inside the With clause as well, to get this code:

Sub Macro4A()

  With Selection.Find

    .ClearFormatting

    .Replacement.ClearFormatting

    .Text = ""

    .Replacement.Text = ""

    .Font.Italic = True

    .Replacement.Font.Bold = True

    .Forward = True

    .Wrap = wdFindContinue

    .Format = True

    .MatchCase = False

    .MatchWholeWord = False

    .MatchWildcards = False

    .MatchSoundsLike = False

    .MatchAllWordForms = False

    .Execute Replace:=wdReplaceAll

  End With

End Sub

Naming and storing macros

When you record a macro, Word suggests a name like Macro1. By default, it puts the macro in a module named NewMacros in the Normal.dot template. You should make it a habit to rename your macros to give them descriptive names, and macros that are useful enough to keep should be stored in a more organized manner.

To rename a macro, all you need to do is change the word that follows "Sub" in the first line. For example, you could change the first line of Macro4A to

Sub ItalicToBoldItalic()

That name will appear in the Tools > Macro > Macros dialog, and you can tell what it does without having to look at the code.

You can organize macros into modules, just as you organize files into folders. In the VBA editor, use the View menu to display the Project Explorer and the Properties pane. On the Insert menu, click Module and notice that a folder named Module1 appears in the Project Explorer. Click that folder, and change its name in the Properties pane.

To move a macro from one module to another, cut its code from the editing pane, double-click the destination module in the Project Explorer, and paste the code into the destination's editing pane. Unfortunately, the Project Explorer doesn't support drag-and-drop movement of macros.

You can move an entire module from one template to another by using the Organizer (Tools > Macro > Macros > Organizer). Macros that are useful for a  specific type of document should be stored in modules in the template used to create that type of document. Macros that are generally useful should be stored in a global template, as explained in What do Templates and Add-ins store?

More fun to come

There are lots of other situations in which the macro recorder gives you code that's inefficient or doesn't do what you want or expect, and you should practice by improving it.

As you learn more about macros, sometimes you'll find it useful to record an action just to discover what statements in VBA are involved. Then you can throw away the recorded macro and write good code of your own.

There are times when you can't get the recorder to record anything useful because the commands you want to use are grayed out. Then you can usually find out more by looking at articles here, or asking questions in the VBA newsgroups.