Saving Search and Replace Information in a Macro

Written by Allen Wyatt (last updated December 21, 2023)
This tip applies to Word 2007, 2010, 2013, 2016, 2019, and Word in Microsoft 365


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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the WordTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Getting Rid of Addresses

Word interfaces easily with other Office programs, such as Outlook. As an example, the Envelopes and Labels dialog box ...

Discover More

Jumping to the Real Last Cell

Jumping to the last cell in a worksheet should be easy, but you may not always get the results that you expect. This tip ...

Discover More

Viewing Same Cells on Different Worksheets

When switching from one worksheet to another, you might want to view the same portion of the new worksheet that you were ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More WordTips (ribbon)

Automating a Manual Process with a Macro

The entire purpose of macros is to allow you to automate repetitive or tedious tasks with relative ease. How easy the ...

Discover More

Determining the Week of the Year

If you are working with dates in a macro, you may need to determine which week of the year a date falls within. This can ...

Discover More

Copying a Range of Pages in a Macro

Do you need to copy, within a macro, a range of pages? Because pages can be so fluid in Word, this can be a bit tricky. ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight more than 3?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Word that uses the ribbon interface (Word 2007 or later)? This site is for you! If you use an earlier version of Word, visit our WordTips site focusing on the menu interface.

Videos
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.