Personal website of Gregory K. Maxey, Commander USN (Retired)
Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.
If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!
If you are new to Word VBA and trying to get your head around how to use the .Find property; or if, like me, you've ever pulled out clumps of hair and scalp in frustration trying to use it, then this tips page might help.
Of all the VBA I've ever used, I've been most confounded and frustrated with the .Find property. It is capable of some pretty amazing feats, but with that capability comes complexity and a degree of fickleness that can take one to the brink of madness.
I am not even going to try to demonstrate or even enumerate everything that you can do with it. I'll only say that if you are impressed with what you can do with the Find and Replace dialog in the user interface, then you won't be disappointed with what you can achieve through VBA.
In this tips page I want to try to demonstrate a few fickle concepts\behaviors of the .Find property that have tripped me up in the past. These concepts are key to understanding and using the .Find property, and when you do you might be spared the hours of frustration that I've experienced
Note: For the demonstrations that follow, it will be best if you show the non-printing paragraph marks in your document.
If you use the built-in Find and Replace dialog during a Word instance, the settings (properties) that you define will stick during the current instance of Word.
To illustrate, perform the following:
Sub StickyDemo() With Selection.Find .Text = "Test" If .Execute Then 'Returns true if the defined text was found. MsgBox "I was found" Else MsgBox "I wasn't found." ActiveDocument.Words(1).Select MsgBox "But, here I am. What gives?" End If End With End Sub
1. See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.
2. When executing the procedures in these demonstrations, it will be more informative if you step through the procedures line by line using the VB Editor and the F8 key.
If you were unaware of sticky settings, like I once was, then you probably expected your defined text "Test" to be found using the VBA procedure. The reason it isn't, is because the sticky settings (properties in VBA speak) that you have applied with the user interface are implicitly applied to the VBA procedure. The procedure is looking for "Test," but it is looking for any instance that has the bold font attribute applied and that isn't to be found in your demonstration document.
You can eliminate this behavior in two ways:
The following procedures demonstrate both methods:
Sub StickyDemoII() With Selection.Find .Text = "Test" 'Note key (problem) property in this case is explicitly set. .Format = False 'Or as an alternate, I could have used the ClearFormatting method. '.ClearFormatting If .Execute Then MsgBox "I was found" Else MsgBox "I wasn't found." ActiveDocument.Words(1).Select MsgBox "But, here I am. What gives?" End If End With End Sub Sub StickyDemoIII() 'Call separate procedure. ResetFRParameters Selection.Range With Selection.Find .Text = "Test" If .Execute Then MsgBox "I was found" Else MsgBox "I wasn't found." ActiveDocument.Words(1).Select MsgBox "But, here I am. What gives?" End If End With End Sub Sub ResetFRParameters(oRng As Range) With oRng.Find .ClearFormatting .Replacement.ClearFormatting .Text = "" .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False .Execute End With End Sub
Note: Special thanks to Christian Wolkerdorfer for identifying and resolving a glitch in the ResetFRParameters routing. He discovered that routine needed a range object (versus simply the selection) to properly reset the listed parameters.
Using either method, you can ensure unwanted previously defined settings will not adversely affect your VBA .Find results.
Another way of putting that is to use an analogy. You might find a whole chick in a chicken egg but you can't find a whole chicken egg in a chicken egg. Or, in order for .Find to find a thing, that thing "must" be "in" the search range. It can't "be" the search range.
To illustrate, perform the following:
Sub CantFindInFind() Dim oRng As Word.Range Set oRng = Selection.Range ResetFRParameters oRng With oRng.Find .Text = "Barn" .Execute If .Found Then Beep 'It wasn't Else MsgBox "The thing you want to find must be in the search " _ & "range. It can't be the search range." End If End With End Sub
This fickle behavior has a relationship with the next key behavior. Whenever the Find object .Execute method determines that the defined "thing" to find is equal to the defined search range, the "always" dynamic search range is changed.
Of the fickle behaviors I wanted to demonstrate, this one was the hardest to get my head around. It can still cause me to trip up, and I often find myself puzzling over why something just won't work and then I remember.
Again, hopefully, some demonstrations will help.
First I want to review some differences you will observe when using Selection.Find vs. a DefinedRange.Find. This is nothing new and straight out of VBA Help.
Delete everything in your demonstration document and type Test Test Test. Place your cursor at the start of the text.
Step through the following code:
Sub Sel_Vs_Rng() Dim oRng As Word.Range 'When the Find object is returned using the Selection object, the selection 'is changed when text matching the find criteria is found. 'The following code selects and then bolds next occurrence of the word "Test" 'and continues until all occurrences are processed. ResetFRParameters oRng With Selection.Find While .Execute(FindText:="Test", Forward:=True) If .Found = True Then Selection.Range.Bold = True End If Wend End With 'When the Find object is returned using a Range object, the selection isn't 'changed, but the Range object is redefined. 'The following code searches for the next occurrence of the word "Test" in the defined 'search range (document main text storyRange). If found, oRng is redefined and bold 'is removed from the word. The selection is not changed. Set oRng = ActiveDocument.Content With oRng.Find While .Execute(FindText:="Test", Forward:=True) If .Found = True Then oRng.Bold = False End If Wend End With End Sub
When stepping through the code, you should have noticed:
Next select "Te" in the first instance of "Test" in your demonstration document text, and step through the code again.
Did you notice that the none of the instances of "Test" were selected or bolded in the Selection.Find portion of the code?
This is because "Test" is not contained in the defined search range and the default properties .Foward = True and .Wrap = wdFindStop terminated any additional search attempts.
Next select the first instance of "Test" in the text and step through the code again.
Here is where it starts getting perplexing. Did you notice that the two instances of "Test" outside the defined search range where processed? Did you notice that the instance of "Test" in the defined search range was not selected or altered by the Selection.Find process at all?
Why did this behavior occur? Why was the instance of "Test" in the defined search range not processed, and why did processing continue in text outside the defined search range?
The explanation (notice I don't say answer) is complicated. I've not found a reason for it, and I can't confirm that it is a bug. However, I can state with a fair amount of certainty that with default properties applied (specifically .Forward = True and .Wrap = wdFindStop) :
That is what happened. Why? Well I don't really know, but there are several theories. Some call it a bug pure and simple. Perhaps it is. I think when the condition above is detected then VBA somehow loses track of the original search range and rather than crash or roam aimlessly, it assigns a range that it knows will exist and continues processing.
I would certainly be interested in any theories (or facts) that readers may have to share.
Later in this tips page I will demonstrate some techniques that you can employ to overcome this seemingly "runaway" search range.
In the above demonstrations, the defined range in the second part of the procedures consisted of the entire document (main text storyRange). In both runs all three instances of Test in the text were processed from the first to the last as expected.
In the code comments, I wrote "When the Find object is returned using a Range object, the selection isn't changed, but the Range object is redefined." What does that mean?
Let's shift our attention to using a range object.
Sub RangeRedefinedDemoI() Dim oRng As Word.Range 'Define range. Set oRng = ActiveDocument.Content 'Evaluate oRng start and end points. Debug.Print oRng.Start & " " & oRng.End ResetFRParameters oRng With oRng.Find .Text = "Test" While .Execute 'Evaluate oRng start and end points. Debug.Print oRng.Start & " " & oRng.End oRng.Font.Color = wdColorRed Wend End With End Sub
Did you notice that oRng is dynamically redefined each time an instance of the "Test" is found? Your Immediate window results should appear as shown below:
Sub RangeRedefinedDemoII() Dim oRng As Word.Range 'Define search range. Set oRng = Selection.Range 'Evaluate oRng start and end points. Debug.Print oRng.Start & " " & oRng.End ResetFRParameters oRng With oRng.Find .Text = "Test" While .Execute 'Evaluate oRng start and end points. Debug.Print oRng.Start & " " & oRng.End oRng.Font.Color = wdColorRed Wend End With 'Evaluate oRng start and end points. Debug.Print oRng.Start & " " & oRng.End End Sub
Did you notice that start and end values of oRng were not changed? This is because the thing to find was not contained in the defined search range.
Sub RangeRedefinedDemoIII() Dim oRng As Word.Range 'Define search range. Set oRng = Selection.Range 'Evaluate oRng start and end points. Debug.Print oRng.Start & " " & oRng.End ResetFRParameters oRng With oRng.Find .Text = "Test" While .Execute 'Evaluate oRng start and end points. Debug.Print oRng.Start & " " & oRng.End oRng.Font.Color = wdColorAutomatic Wend End With End Sub
If you are grasping the fickle behavior of VBA .Find, then the result should not be surprising.
This fickle behavior is a direct result of the always dynamic search range behavior. It can and almost certainly will happen in your VBA procedures until you grasp the dynamic range concept and start bending it to your will.
For this demonstration we will start by using the existing text in your demonstration document. If you've been following along, then it should look like the text in previous illustration.
Sub ContinousLoopDemoI() Dim oRng As Word.Range Set oRng = ActiveDocument.Range Debug.Print oRng.Start & " " & oRng.End ResetFRParameters oRng With oRng.Find .Text = "Test" While .Execute Debug.Print oRng.Start & " " & oRng.End oRng.Text = "Testing" Debug.Print oRng.Start & " " & oRng.End Wend End With End Sub
Surprised? Had you simply executed that code, it would have run and run until it puked and your document would be full of long string of gibberish.
Did you observe the Immediate window results?
If you understand the dynamic nature of the search range, then you should see that "Test" spanning the range 0 to 4 is found in the original range 0 to 15, and it will be found again, and again, and again in the new defined range spanning 0 to 7. You are trapped in a continuous loop!
To avoid loops of this nature, if your processing code alters a redefined Find object range's .End property value, you will need to use the .Collapse method in your code.
Sub ContinuousLoopDemoII() Dim oRng As Word.Range Set oRng = ActiveDocument.Range Debug.Print oRng.Start & " " & oRng.End ResetFRParameters oRng With oRng.Find .Text = "Test" While .Execute Debug.Print oRng.Start & " " & oRng.End oRng.Text = "Testing" 'Added collapse method. oRng.Collapse wdCollapseEnd Debug.Print oRng.Start & " " & oRng.End Wend End With End Sub
The result is now what we would expect.
Did you notice the Immediate window results?
Sub PrematurelyBrokenLoopDemoI() Dim oRng As Range Set oRng = ActiveDocument.Content ResetFRParameters oRng With oRng.Find .Text = "Testing" .Forward = True 'Note use of MatchCase property. .MatchCase = True While .Execute oRng.Text = "TESTING" Wend End With End Sub
This behavior is another example of how the dynamic search range can produce perplexing results. As we've seen previously, after the first .Execute method the search range was dynamically redefined to match the range of the first instance of "Testing." That instance was changed to "TESTING." On the next .Execute method, the thing to find is not found in the defined search range and it isn't an exact match of the defined search range. VBA thinks it is done and the loop is ended.
To avoid this erratic behavior, you can employ the .Collapse method as demonstrated previously. As before, this will result in dynamically redefining the search range.End to the document range.End.
Note: As an alternative, you could omit the .Collapse method used in PBLII below, and use and explicit .Wrap property as shown in PBLIII.
Sub PBLII() Dim oRng As Range Set oRng = ActiveDocument.Content ResetFRParameters oRng With oRng.Find .Text = "Testing" .Forward = True .MatchCase = True While .Execute With oRng .Text = "TESTING" 'Force continuing loop. .Collapse wdCollapseEnd End With Wend End With End Sub Sub PBLIII() Dim oRng As Range Set oRng = ActiveDocument.Content ResetFRParameters With oRng.Find .Text = "Testing" .Forward = True .MatchCase = True 'Force continuing loop. .Wrap = wdFindContinue While .Execute oRng.Text = "TESTING" Wend End With End Sub
I am unable to definitively state the fixed rules for when to use the .Collapse method with the VBA .Find property. I can only advise you that if you writing and testing code and continuous or broken loops are a problem, then check to see if you have employed the .Collapse method.
Throughout the preceding demonstrations, I have made reference to "storyRange."
Unlike the global scope of a the built-in find and replace dialog, in a basic VBA .Find property procedure the scope of the search is limited to a single targeted storyRange. To illustrate,
You will notice that all four instances of "Testing" in the document were changed to "Test"
Sub LimitedScope() Dim oRng As Word.Range ResetFRParameters Selection.Range With Selection.Find .Text = "Test" While .Execute With Selection .Text = "Testing" .Font.Color = wdColorGreen .Collapse wdCollapseEnd End With Wend End With End Sub
The result illustrates how the .Find property in VBA search is limited to a single targeted storyRange. When Selection.Find is used that storyRange is the storyRange that contains the selection object.
Sub LimitedScopeRange() Dim oRng As Word.Range 'A single targeted storyRange. Set oRng = ActiveDocument.StoryRanges(wdPrimaryHeaderStory) ResetFRParameters oRng With oRng.Find .Text = "Test" While .Execute With oRng .Text = "Testing" .Font.Color = wdColorBlue .Collapse wdCollapseEnd End With Wend End With End Sub
When range .Find is used, that storyRange is the storyRange defined in the code.
To process the global scope of a document, the .Find property can be used in every storyRange in the document. This method is covered in detail in my:
Using a Macro to Replace Text Wherever It Appears in a Document.
1. Not to be pedantic, but after that article was published, I discovered that any text in the text frame of a shape object anchored to a header or footer storyRange is not processed.
2. This is also true when using the built-in find and replace dialog.
3. The only published methods that I am aware of to find all instances of defined Find text is my VBA Find and Replace Word template add-in, and an alternate method proposed by Jason Frost Simpson, aka "Frosty" here: http://www.vbaexpress.com/forum/archive/index.php/t-42203.html.
I've already addressed how to tame sticky settings, avoid continuous loops, and expand the scope of VBA .Find. Now I want to provide some practical examples and demonstrations on how to tame and gain control of the "always" dynamic search range and how you can work around the fact that you can't find a "thing" if that thing exactly matches the defined search range.
Not long ago, and probably what prompted me to gather my thoughts and publish this page, the following question was asked in a VBA support forum:
"If the selected text" should be an indicator to you that the objective is to search a limited defined range. The user does not want to search an entire storyRange.
Let's set up a demonstration:
Sub PracticalI() Dim oRng As Word.Range Set oRng = Selection.Range ResetFRParameters oRng With oRng.Find .Font.Color = wdColorBlack .Replacement.Font.Color = wdColorBlue .Execute Replace:=wdReplaceAll End With Set oRng = Selection.Range With oRng.Find .Font.Color = wdColorAutomatic .Replacement.Font.Color = wdColorBlue .Execute Replace:=wdReplaceAll End With End Sub
Pretty straightforward. We defined a search range, we searched for the black text and replaced it with blue, we redefined the search range and searched for default automatic color text and replaced it with blue. The red text was unaffected. Viola!
Not so fast. Don't break out the champagne just yet.
The effect of the dynamic search range revealed again! This time it behaves a little differently (due to our use of "Replace") and the redefined dynamic range is the original range.Start and document range.End.
If you don't curse that is alright, I've probably covered that for you. Being a former sailor, my VBA sessions are not for tender ears.
Our conundrum in this practical problem is two fold. As you have seen, 1) we can't find a "thing" when that thing exactly matches the search range, and 2) when that thing does exactly match the search range, the dynamically redefined range is a "runaway" range.
So how do you find a "thing" that exactly matches the search range and prevent a runaway range? You don't! Instead to you employ some VBA trickery:
Sub PracticalIV() Dim oRng As Word.Range Dim oRngToSrch As Word.Range 'Insert a temporary range modifier. Selection.Range.InsertAfter "~" Selection.MoveEnd wdCharacter, 1 Set oRng = Selection.Range ResetFRParameters oRng 'Add unique (unanticipated formatting to the modifier). oRng.Characters.Last.Font.ColorIndex = wdBrightGreen With oRng.Find .Font.Color = wdColorAutomatic .Replacement.Font.Color = wdColorBlue .Execute Replace:=wdReplaceAll End With Set oRng = Selection.Range With oRng.Find .Font.Color = wdColorBlack .Replacement.Font.Color = wdColorBlue .Execute Replace:=wdReplaceAll End With 'Delete the modifier. oRng.Characters.Last.Delete End Sub
The result after running the code is as expected.
This method works because we confounded the two primary fickle behaviors with VBA .Find. First we ensured that the thing to find did not exactly match the search range. In doing so, we prevented the dynamic search range from being redefined to the end of the document.
For more on the runaway range see my: Find in Defined Range tips page. Here I demonstrate how you can employ the Range.InRange method to tame a runaway range.
This space is a work in progress. When I discover something that I've tripped up over or find interesting related to VBA .Find, then I'll try to post it here.
Tips for performing wild card searches abound on the internet. My favorite is published by Word MVP Graham Mayor: Find and Replace using wildcards. In that page Graham provides and explains all the specials symbols used in wild card searches.
Recently, I attempted to use the following .Find.Text string in a VBA .Find procedure.
My objective, really not that important, was to define two groups in the "Find what" search. The first being two or more spaces, and the second being an opening parenthesis. Based on my former understanding of the use of special codes, I thought it would work.
It won't. When I executed the code, I received:
Thanks to Paul Edstein aka "Macropod," I discovered my problem. It seems that despite preceding the opening parenthesis "(" with "\" i.e., "\(" as prescribed when using wildcards, the pattern evaluates as invalid when placed within group symbols "(\()"
The solution is to put the open parenthesis symbol within range symbols "" with or without the prescribed "\". Either of these two lines will work well.
There are some things that you simply cannot do with the built-in Find & Replace dialog. Here is an example from a popular support forum post.
Finding upper case letters using the Find dialog is simplicity itself. However, AFAIK, there is no way to replace each letter found with its lower case equivalent.
The solution is a little more complex than the actual question implies.
It is already clear that the first word of the document, paragraph or sentence should not be processed. Additionally, there are common names, initials, abbreviations, and title case e.g., Mr. A. Dumas wrote "The Count of Monte Cristo." etc., that should not be processed.
This means that the solution can not simply be an indiscriminate process of finding upper case characters and replacing them with their lower case equivalent. Consider the following example text:
Using the following code, you can quickly process the example text to meet your objective. This process could never be fully automatic due to the complexity of the language and innumerable situations requiring a human decision. Still, with experience learned, you can often revise procedures like this to better suit your needs.
Sub FindAndConvertUCaseToLCase() Dim oRngStart As Word.Range Dim oRng As Word.Range Set oRngStart = Selection.Range.Duplicate Set oRng = ActiveDocument.Content ResetFRParameters oRng With oRng.Find 'You don't want to find the first word in each paragraph. .Text = "([!^13])[A-Z]?" .MatchWildcards = True While .Execute 'You will probably want to bypass initials (e.g., the A. in A. Smith) If Not oRng.Characters.Last = "." Then 'You will probably want to bypass common abbreviations. Select Case oRng.Words(2) 'Add to these as your experience and needs dictate. Case "Mr", "Mrs", "Dr" 'Do nothing. Case Else With oRng .Characters.First.Next.Select Select Case MsgBox("Do you want to convert this character?", _ vbQuestion + vbYesNoCancel, "Convert ??") Case Is = vbYes .Characters.First.Next = LCase(oRng.Characters.First.Next) .Collapse wdCollapseEnd Application.ScreenRefresh Case Is = vbNo 'Do nothing. Case Else GoTo lbl_Exit End Select End With End Select End If Wend End With lbl_Exit: oRngStart.Select Exit Sub End Sub
Note: You might wonder why the code above does not exclude processing the first word of sentences. The reason is that the sentence collection in Word is and always has been broken. See: Deduced Sentences
That's it! I hope you have found this tips page useful and informative.
Do you want to make a payment for consulting work or donate to help support this site?
PayPal is a safe, easy way to pay online.
Use the appropriate currency "Donate" button to make a payment or donation.