Word Hacks [Electronic resources]

Hack 32 Find and Replace Without Find and Replace

This brute-force hack lets you power through simple substitutions without complicated code.

VBA newbies (and even veterans) find it frustrating to use the Find and Replace command from within a macro.

Why the difficulty? Most people think of Find and Replace as an action, which would translate to a procedure in VBA. Word, however, uses a Find object to handle searching and replacing.

Scripting languages such as Python and Ruby implement their substitution capabilities as objects, too. If you implement Find as an object, you can assign and retain properties in memory for the next time you use the object. The same phenomenon occurs when you do a search and the text you last searched for remains in the "Find what" box.

While the Find object is a powerful tool for working with Word from VBA, its complexity can really bog you down when all you need is a quick fix. Find has more than two dozen properties, and while it does execute very quickly, you will probably spend any time you save using the Find object figuring out how to use it. This hack shows you how to replace it (pun intended) with a simple For Each loop [Hack #66] .

For example, say your document uses four different highlighting colors in each paragraph: red, blue, yellow, and green. Just as you finally finish applying the highlighting, your boss decides she prefers teal instead of blue and asks you to make the change. The clock reads 10 minutes to noon, and you want this project wrapped up before your lunch date.

First, you try EditReplace, but you quickly discover that although you can search for highlighting, you cannot specify a color.

You decide to try a macro instead, but 30 minutes later you still need to figure out how to coerce the Find object into doing your bidding. Hungry and frustrated, you finally give up and start making the change by hand. There must be a better way!

4.7.1 The Code

It would probably take you five minutes to write these five lines of code with the help of VBA's IntelliSense [Hack #1], and maybe another five minutes to test and debug it on a snippet of your document. And five lines of brute-force VBA is all it takes:

Sub FixHighlightColor( )
Dim char As Range
For Each char In ActiveDocument.Characters
If char.HighlightColorIndex = wdBlue Then
char.HighlightColorIndex = wdTeal
End If
Next char
End Sub

Notice that the macro iterates through each character in the active document (including spaces).

4.7.2 Running the Hack

Place this macro in the template of your choice [Hack #50] and either run it from the ToolsMacroMacros dialog or put a button for it on a menu or toolbar [Hack #1].

Obviously, it takes some time to run such a resource-intensive procedure. But on a sample 27-page document, with 83,000 characters (including spaces), this macro took a grand total of four minutes. You might have made that lunch date after all.

Of course, if your document is hundreds of pages long, or if you have dozens of documents to fix, it might make sense to develop more efficient code. But even if your document was 10 times longer than our sample (or you had nine more of them), this macro would have solved your problem before you even finished lunch.

4.7.3 Hacking the Hack

Although "each character in the active document" sounds all-encompassing, it leaves out a few important things. Each document is actually made of several story ranges: one for the main text, another for the footnotes, another for the headers and footers, and so on. The macro, however, searches only the main text story range, so if a header contained highlighting, the FixHighlightColor macro would fail to catch it.

To solve this problem, nest your code inside another For Each loop:

Sub FixHighlightColorInAllStories( )
Dim char As Range
Dim stry as Range
For Each stry In ActiveDocument.StoryRanges
For Each char In stry.Characters
If char.HighlightColorIndex = wdBlue Then
char.HighlightColorIndex = wdTeal
End If
Next char
Next stry
End Sub

To see how much simpler a For Each loop can be, take a look at the following macro, which performs the same substitution as the five-line FixHighlightColor macro shown above.

The Find object works a lot faster (about 70% faster on that same test document), but it's a lot trickier to code:

Sub FixHighlightUsingFind( )
Dim rngToSearch As Range
Dim rngResult As Range
Set rngToSearch = ActiveDocument.Range
Set rngResult = rngToSearch.Duplicate
Do
With rngResult.Find
.ClearFormatting
.Text = "
.Forward = True
.Wrap = wdFindStop
.Highlight = True
.Execute
End With
If rngResult.Find.Found = False Then 
Exit Do
End If
If rngResult.HighlightColorIndex = wdBlue Then
rngResult.HighlightColorIndex = wdTeal
End If
rngResult.MoveStart wdWord
rngResult.End = rngToSearch.End
Loop Until rngResult.Find.Found = False
End Sub

As the bolded lines show, the part of the macro that does the actual substitution is nearly identical to the FixHighlightColor macro above. Everything else is excess baggage.

Just like the For Each loop in the FixHighlightColor macro, the Find object in this macro misses items not in a document's main story range. To find everything, including headers, footers, footnotes, and text boxes, you need to wrap the Find and Replace inside of a For Each loop, as the following code shows:

Sub FindInEveryStory( )
Dim rngStory As Range
Dim rngToSearch As Range
Dim rngResult As Range
For Each rngStory In ActiveDocument.StoryRanges
Set rngToSearch = rngStory
Set rngResult = rngToSearch.Duplicate
Do
With rngResult.Find
.ClearFormatting
.Text = "
.Forward = True
.Wrap = wdFindStop
.Highlight = True
.Execute
End With
If rngResult.Find.Found = False Then 
Exit Do
End If
If rngResult.HighlightColorIndex = wdBlue Then
rngResult.HighlightColorIndex = wdTeal
End If
rngResult.MoveStart wdWord
rngResult.End = rngToSearch.End
Loop Until rngResult.Find.Found = False
Next rngStory
End Sub