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:
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:
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.
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!
The entire purpose of macros is to allow you to automate repetitive or tedious tasks with relative ease. How easy the ...
Discover MoreIf 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 MoreDo 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 MoreFREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
Visit the WordTips channel on YouTube
FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments