Saving Search and Replace Information in a Macro

Simao has some macros doing a lot of searching and replacing. However, when the macros finish, he'd like to restore the search and replace parameters to the ones in effect just before he ran the macros. So, Simao wonders where to find all of the properties for search and replace so he can save them at the start of each macro and then restore them at the end.

As Simao alludes to, "good practice" is to have your macro return anything it changes to whatever state it was in before the macro ran. That way your macro won't mess up anything else that the user may be doing.

The idea in implementing this practice is to follow these steps:

1. Figure out what your macro will change
2. Create variables in which to store all the properties that will be changed
3. Store the properties in the variables you created
4. Do your work
5. Restore those properties from the variables you earlier set in step 3

The key here is step 1. If you cannot figure out what your macro will change, you cannot proceed with the other steps. With doing find and replace operations, there are a number of objects that can be involved, and each of those has a goodly number of properties.

One easy way to figure things out is to simply record a macro that does the Find and Replace operation, and then look at the code that the recorder produces. For instance, I recorded searching for the word "the" and replacing it with the word "THE." When I stopped the macro recorder and looked at what it created, this is the relevant part of what I saw:

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
    .Text = "the"
    .Replacement.Text = "THE"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = True
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With

I'll get back to the first two lines of this code in a moment but take look at all the other lines produced. With the .Find object, a whole passel of properties are specified. These are properties corresponding to the settings in the Find and Replace dialog box. So, you now have a list of variables you need to define, at a minimum, so you can save the settings of each of these properties and later restore them.

Now back to those first two lines of the code; they represent the major fly in the ointment. The .ClearFormatting method gets rid of any formatting defined for the Find and Replace operation. In order to see what properties are affected if I define formatting in the Find and Replace, I again recorded a macro of the same Find and Replace operation, but this time specifying that the replacement text should be bold and underlined. Everything that the recorder recorded was the same, with the exception of four code lines added right after the second line shown above:

With Selection.Find.Replacement.Font
    .Bold = True
    .Underline = wdUnderlineSingle
End With

These are the lines that specify the formatting I wanted to use in the replacement. In the earlier code, the macro recorder shows all of the properties of the .Find object. In this case, the only thing recorded is what I am changing, which just happens to be in the .Font object. There are, unfortunately, other objects that can be specified besides the .Font object--.Paragraph, .Tabs, .Language, and on and on. Plus, not only can these be set for the .Find object, but also for the .Replacement object. That's the potential of hundreds of properties that need to be stored and restored.

Sound impossible? Probably not impossible, but it is definitely daunting. Using the method of recording macros, you would need to do a find (or replace; the concepts are the same) and change the formatting of every possible formatting property that could be found or replaced. If you are successful in doing that, you'll end up with quite the list. Here, for instance, is the code generated for the Selection.Find.Font object:

With Selection.Find.Font
    .Size = 14
    .Bold = True
    .Underline = wdUnderlineSingle
    .StrikeThrough = True
    .DoubleStrikeThrough = False
    .Hidden = True
    .SmallCaps = True
    .AllCaps = False
    .Color = wdColorRed
    .Superscript = True
    .Subscript = False
End With

In other words, 11 different properties to save and reset. But wait! There's more! (Sounds like a Ginsu knife commercial, right?) I didn't make any changes on the Advanced tab of the Find Font dialog box, which would undoubtedly have expanded this list. You can get an idea of the total number of properties for the .Font object by looking at the Microsoft docs for that object's properties. (Make sure you expand the Properties link for the .Font object by using the drop-down list at the left of the page.)

https://docs.microsoft.com/en-us/office/vba/api/word.font

You'll see over 50 properties there. And, remember, there is a separate .Font object that belongs to the .Replacement object, and there are similar paired objects for other formatting elements (.Paragraph, .Tabs, etc., etc.).

Definitely daunting.

Once you understand how many properties are affected when you do a Find and Replace operation, you start to understand why many people forgo saving and restoring formatting settings for the operations. It is doable; it is not a minor undertaking, though.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (13762) applies to Microsoft Word 2007, 2010, 2013, 2016, 2019, and Word in Microsoft 365.