Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Word's Fickle VBA .Find Property
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

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.  emoticon hairpull

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

Fickle Key Concepts\Behaviors of the .Find Property

Site Note IconNote: For the demonstrations that follow, it will be best if you show the non-printing paragraph marks in your document.

fickle find 01

Sticky Settings

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:

fickle find 02


fickle find 03


fickle find 04

fickle find 056

fickle find 06

Now close the Find dialog
VBA Script:
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

Site Note IconNotes:

      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:

VBA Script:
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

Site Note IconNote: 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.

You can Find  things "in" a Search Range , but you can't Find "the" Search Range

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:

fickle find 07
VBA Script:
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.  

The Search Range is "Always" a Dynamic Range, regardless of how it is defined.

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.

fickle find 08

Step through the following code:

VBA Script:
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.

fickle find 10

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.

fickle find 09

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. 

VBA Script:
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:

fickle find 11
fickle find 12
VBA Script:
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.

fickle find 13
VBA Script:
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.

fickle find 14

Remember:

Beware Continuous and Prematurely Broken Loops

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.

Continuous Loop

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.

VBA Script:
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.

fickle find 15

Did you observe the Immediate window results?

fickle find 16

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.

VBA Script:
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.

fickle find 17

Did you notice the Immediate window results?

fickle find 18

Prematurely Broken Loop

VBA Script:
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
fickle find 17A

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.

Site Note IconNote: As an alternative, you could omit the .Collapse method used in PBLII below, and use and explicit .Wrap property as shown in PBLIII.

VBA Script:
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.

Limited Scope of  basic VBA .Find  Procedures

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,

fickle find 19
fickle find 20

You will notice that all four instances of "Testing" in the document were changed to "Test"

VBA Script:
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.

fickle find 21
VBA Script:
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.

fickle find 22

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.

Site Note IconNotes:
    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.

Taming the Beast!  More on Gaining Control of VBA .Find

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:

fickle find 23
Just make sure you have all three text color conditions clearly defined and included in the selection.
VBA Script:
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!

fickle find 24

Not so fast.  Don't break out the champagne just yet.

fickle find 25
Text before executing the procedure.

fickle find 26
Text after executing the procedure.

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:

VBA Script:
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.

fickle find 27

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.

General Gotchas and Interesting Tips

Site Note IconThis 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.

Pattern Error - Using parenthesis "(" or ")" in a wild card search group.

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:

fickle find 28

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.

Find and Selectively Convert Upper Case Characters to Lower Case

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:

fickle find 29
Original text

fickle find 30
Indiscriminate replacement.

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.

VBA Script:
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

Site Note IconNote:  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

Conclusion

That's it! I hope you have found this tips page useful and informative.

Share

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

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.


Search my site or the web using Google Search Engine

Google Search Logo