Insights

Categories

Word and Visual Basic for Applications (VBA): Worth the Effort? (Part 4)

Apr 29, 2016

This post is part of a series on VBA for Microsoft Word. If you haven’t read the earlier parts, you can find them here: Part 1, Part 2, and Part 3.

In the previous posts in this series, we looked at some simple auto-formatting examples. In this final post, we demonstrate how you can use Word’s macro recorder to discover what code is needed to perform a task and how that generated code can be integrated into your own macros.
Word File Loop Format With Macro

Using Word’s Record Macro Feature to Discover What Code is Needed

Let’s switch from writing code, and look at Word’s macro recording feature. Suppose you want to find all occurrences of the text ‘OK button’ in your document and format ‘OK’ with the appropriate character style, for example, Strong. You know how to find things using the UI, but you have no idea what code is needed to perform the same function. So let’s investigate this a little more.

First, add a few ‘OK button’ references to your test document so that you have something to find.

On the Developer tab, choose recorder icon Record Macro. (Note: if the Developer tab is not visible, choose File > Options > Customize Ribbon, select the Developer check box, and click OK.) The Record Macro dialog is displayed with a default macro name, typically Macro1, and recorded macros are saved in the Normal template by default.

record macro dialog

Click OK to accept the default settings.

Notice that the cursor changes to an arrow with a cassette tape icon recorder cassette icon to indicate that recording is now in progress.

Record the following steps:

  1. On the Home tab in the Editing panel, click Find > Advanced Find to open the Find and Replace dialog.
  2. Type ‘OK button’ into the Find what field and click Find Next. Word finds the next occurrence of the text in the document and highlights it. Click Cancel to close the Find and Replace dialog.
  3. Hold down Shift and press the Left arrow key seven times to deselect the last seven characters in the selection (that is, the word ‘button’ and the space before it). We deselect these characters because we don’t want to apply the Strong style to them; we want to apply the Strong style to the ‘OK’ part of the text only.
  4. Open the Styles gallery and select the Strong character style. The word ‘OK’ now appears in bold.
  5. Press the Right arrow key to deselect the word ‘OK’.

Switch to the Developer tab and choose recorder stop icon Stop Recording.

Now, click the Macros button, select the name of the macro you just recorded (Macro1) and click Edit. You should see code like the following:

 Sub Macro1()
 '
 ' Macro1 Macro
 '
 '
     Selection.Find.ClearFormatting
     With Selection.Find
         .Text = "OK button"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute
     Selection.MoveLeft Unit:=wdCharacter, Count:=7, Extend:=wdExtend
     Selection.Style = ActiveDocument.Styles("Strong")
     Selection.MoveRight Unit:=wdCharacter, Count:=1
 End Sub

A few things to note:

  • The first few lines beginning with ‘ (the apostrophe symbol in green) are comment lines. Any text that appears after an apostrophe is not treated as code, but as comment text. It is important to document your code so that others can understand it, so VBA adds these lines in anticipation that you will provide a good description. You can add more comment lines anywhere in the code.
  • Selection.Find.ClearFormatting clears the settings for any previous find operation.
  • With Selection.FindEnd With is the VBA statement combination that sets up the parameters of the search. This is where you specify the text to search for. The full power of Word’s search capability is available here, so you could equally search for something specialized like a style or impose some specific matching criteria. Refer to the Find and replace text or other items article for more information on the available options.
  • Selection.Find.Execute is the statement that actually performs the find operation.
  • Selection.MoveLeft moves the cursor to the left. In this example, it is used to deselect seven characters of the found text, in this case ‘ button’, because it should not be formatted with the Strong style.
  • Selection.Style applies the specified style, in this case Strong, to the selection.
  • Selection.MoveRight moves the cursor to the right. In this example, it is used to deselect text by moving the cursor one space to the right.

So you can easily see the code that Word automatically generated in response to your UI actions. This is a really powerful feature because you can combine the code that Word generates with your own code.

Modifying Recorded Generated Code with Your Own Code

Let’s take this generated code and modify it to find all occurrences of ‘OK button’ in a document and change them to ‘OK button’, where the word ‘OK’ has the appropriate Strong style.

In Part 2 of this series, you created a loop. Let’s do something similar here. First, let’s copy the Word-generated code into a new macro called ApplyStrongStyle and add a few lines to it. The new macro should look like the following, where the added lines are shown as highlighted text.

 Sub ApplyButtonStyle()
 '
 ' Autoformats occurrences of "OK button" so that "OK" has the
 ' appropriate "Strong" style.
 '
     Selection.Find.ClearFormatting
     With Selection.Find
         .Text = "OK button"
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindContinue
         .Format = False
         .MatchCase = False
         .MatchWholeWord = False
         .MatchWildcards = False
         .MatchSoundsLike = False
         .MatchAllWordForms = False
     End With
     Selection.Find.Execute

     Do While Selection.Find.Found = True
         Selection.MoveLeft Unit:=wdCharacter, Count:=7, Extend:=wdExtend
         Selection.Style = ActiveDocument.Styles("Strong")
         Selection.MoveRight Unit:=wdCharacter, Count:=1
         Selection.Find.Execute
     Loop
 End Sub

Some things to note:

  • Do While Selection.Find.Found = TrueLoop is a do loop that continues while our find text (in this case, ‘OK button’) is searched for in the document. When there are no more occurrences to find, the value of Selection.Find.Found changes to False and the loop ends.
  • Selection.Find.Execute performs the find operation as before, only in this case it runs within the loop.

With the cursor inside the subroutine, click (Run Sub/UserForm) on the toolbar (or from the menu, choose Run > Run Sub/User Form).

Result: Each occurrence of ‘OK button’ changes to ‘OK button’ where ‘OK’ is formatted with the Strong character style.

It doesn’t matter how big the document is, how many occurrences of ‘OK button’ there are, or where those occurrences are in the text, the macro formats them all correctly. You could extend this macro to find other UI components referenced in your text and format them in a similar way.

By using a combination of the code generated by Word and your own code, you have put together something quite useful in a short space of time. This is just a small example and with a little knowledge, the possibilities are endless.

One of the big advantages of working with VBA is the amount of information and support that is available on the web. Here are a couple of references to help you get started:

In addition to documentation describing the concepts and objects, look for video tutorials on YouTube and other sources. For example, here is a Microsoft Webcast called Using Visual Basic for Applications (VBA) Every Day Is Easier Than You Think that you may find helpful.

To sum up, in the final blog post in this series, you’ve discovered how Word’s macro recording feature can show you what code to use. You can then use that code in your own macros to accomplish quite significant tasks.

The intention of this blog series was to show the very basics of Word VBA and encourage writers to explore further if this is something that would be useful for their work.

Do you think we have achieved that goal? Write in and let us know. We are always keen to hear from fellow writers and colleagues.

Share This

Share This

Share this post with your friends!