Saving Search and Replace Information in a Macro

by Allen Wyatt
(last updated May 23, 2020)

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 Office 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

Finding Fields

Fields allow you to add simple dynamic content to your document. Here's how you can find the fields when you need to know ...

Discover More

Ensuring Rows and Columns are Empty

Before you go about deleting rows and columns helter-skelter, it is a good idea to determine if there is anything in the ...

Discover More

Dealing with Midnight Ending a Day

Dealing with times in Excel is fairly straightforward, except when it comes to midnight. Some people prefer that midnight ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More WordTips (ribbon)

Creating a String

Need to use a macro to create a text string? One easy way to do it is to use the String function, described in this tip.

Discover More

Saving Changes when Closing

If you write a macro that makes changes to a document, you may want that macro to save those changes. There are several ...

Discover More

Changing Roman Numerals to Arabic

In some documents Roman numerals might be used quite a bit. If you ever want to change the Roman numerals to their Arabic ...

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}] 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 five more than 5?

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.

Newest Tips
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.