Find All in VBA

Topic

New Reply

When I use the Find dialog box there is a checkbox to highlight all items found in document. When you check it, the Find button changes to Find All. When I click Find All it selects all occurrences of every match. When I use the macro recorder it creates the following code:

Code:

Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "Amount"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With

For some reason the macro recorder left out the execute statement. It must have left something else out because when I add the execute statement right before the end with, and then run it, it finds and selects only the first occurrence. How do I change the macro so that it finds ALL matches and selects all occurrences?

As a side note, one nice thing about using Find All is that the dialog box actually tells you how many occurrences the text is found in the document.

Reply | Quote

Replies

Unfortunately, Microsoft omitted to add support for “Find All” in the VBA object model for Word. In other words, Find All cannot be executed from a macro.

You can loop through all occurrences of the search text in VBA, but that’s not the same as Find All.

Reply | Quote

Unfortunately, Microsoft omitted to add support for “Find All” in the VBA object model for Word. In other words, Find All cannot be executed from a macro.

You can loop through all occurrences of the search text in VBA, but that’s not the same as Find All.

Thanks Hans. I was hoping maybe the macro recorder had left a property setting out as well as the execute statement. Back to the drawing board.

Reply | Quote

Unfortunately, Microsoft omitted to add support for “Find All” in the VBA object model for Word. In other words, Find All cannot be executed from a macro.

You can loop through all occurrences of the search text in VBA, but that’s not the same as Find All.

I suspect this omission was intentional and carefully considered. In the visual context of an open document window, Find All is a perfectly sensible concept. However, in the procedural world of VBA, it is a tad more difficult to work with sets of things, and, often, computationally less efficient.

This isn’t to say that it can’t be done in VBA, only that I can understand why it wasn’t done. For what it’s worth, the same is true of the Find object in Excel, with which I have much more intimate, and recent, experience.

To support Find All in VBA would require the Execute method to return a collection of Range objects, which could be computationally expensive, in terms of both processing and memory.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

I suspect this omission was intentional and carefully considered. In the visual context of an open document window, Find All is a perfectly sensible concept. However, in the procedural world of VBA, it is a tad more difficult to work with sets of things, and, often, computationally less efficient.

This isn’t to say that it can’t be done in VBA, only that I can understand why it wasn’t done. For what it’s worth, the same is true of the Find object in Excel, with which I have much more intimate, and recent, experience.

To support Find All in VBA would require the Execute method to return a collection of Range objects, which could be computationally expensive, in terms of both processing and memory.

For what it’s worth, this analysis seems a little off the mark to me, in that it’s differentiating between what could be done in the “visual context of an open document window” versus what would need to happen in the “procedural world of VBA”. This leaves out that what is going on under the hood in Word, to make the visual display in the document window happen, is itself just code running – perhaps C++ rather than VBA, but the underlying objects and procedural logic are almost certainly very similar: almost every function that you can perform in VBA (and object you can work against), is a wrapper for the underlying procedures and objects (likely built in C or C++) that are making Word work.

Would a function running in C++ be more computationally efficient than its equivalent wrapper running in VBA? – yes. But that doesn’t necessarily explain the omission of an equivalent VBA function, to something that exists in the user interface. There are a bunch of functions in the Office user interface, that we can’t touch via VBA – the reasons for these omissions could be numerous (budget constraints, release deadlines, departure of a key programmer from the team at a key time), but not necessarily always driven by logical, programmatic constraints.

Gary

Reply | Quote

Gary,

For what it’s worth, this analysis seems a little off the mark to me, in that it’s differentiating between what could be done in the “visual context of an open document window” versus what would need to happen in the “procedural world of VBA”. This leaves out that what is going on under the hood in Word, to make the visual display in the document window happen, is itself just code running – perhaps C++ rather than VBA, but the underlying objects and procedural logic are almost certainly very similar: almost every function that you can perform in VBA (and object you can work against), is a wrapper for the underlying procedures and objects (likely built in C or C++) that are making Word work.

Not necessarily, because, until fairly recently, support in VBA for collections has been rather halfhearted. In contract, the MFC library, which underlies most of Microsoft Office, includes very ruch support for arrays and hashtables (indexed lists) of objects, which work quite well, and can contain virtually anything.

Would a function running in C++ be more computationally efficient than its equivalent wrapper running in VBA? – yes. But that doesn’t necessarily explain the omission of an equivalent VBA function, to something that exists in the user interface. There are a bunch of functions in the Office user interface, that we can’t touch via VBA – the reasons for these omissions could be numerous (budget constraints, release deadlines, departure of a key programmer from the team at a key time), but not necessarily always driven by logical, programmatic constraints.

Nevertheless, your guess could well be on the mark, especially given that, even without direct access to a found objects collection, among others, there is almost always a way to emulate them in VBA, although the implementation might be tedious, cumbersome, and suboptimal. I’ve encountered similr shortcomings with other “missing” collections, such as the set of custom document properties.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

Out of curiosity, did Microsoft fix this in 2007 or the next 2010 version?

Reply | Quote

I don’t know!

Reply | Quote

I don’t know!

That’s okay Hans. Just curious. Have a great day!

Reply | Quote

2007 does not include a Find All either. There is now a button that allows you to highlight the occurrences of the text, but it does not get recorded by the macro recorder.

Reply | Quote

2007 does not include a Find All either. There is now a button that allows you to highlight the occurrences of the text, but it does not get recorded by the macro recorder.

Thanks for that confirmation. Maybe one day Microsoft will recognize that we need one.

Reply | Quote

Thanks for that confirmation. Maybe one day Microsoft will recognize that we need one.

Probably the reason a Find All isn’t in VBA is that VBA also has never had any way to deal with discontiguous selections (the kind you can make with Ctrl and the mouse), which is what Find All would produce. The KB article here explains the few things that can be done. Every version since 2002 (including 2010) has made no changes in this area.

Reply | Quote

2007 does not include a Find All either. There is now a button that allows you to highlight the occurrences of the text, but it does not get recorded by the macro recorder.

The reading highlight’s highlight all does not select all instances, and the highlighting can only be cleared by selecting Clear highlighting. (The feature seems to have been the first step to the new find features of W2010.)

But we can still, in W2007 (& 2010 beta), select all instances by clicking the Find in button and the choosing Main document (or appropriate choice). Word reports the number of instances found, and the selected items can be acted on (formatted, copied, deleted, and such).

Pam

Reply | Quote

Pam,

But we can still, in W2007 (& 2010 beta), select all instances by clicking the Find in button and the choosing Main document (or appropriate choice). Word reports the number of instances found, and the selected items can be acted on (formatted, copied, deleted, and such).

This suggests that the interactive editing engine takes the processing and memory hit required to make a collection of the matches. Have you written to the Office development team with a suggestion that VBA should support it, too?

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

If you are desperate enough, it might be possible to do this with VBA by using the SendKeys command. The following code works for me, using Word 2002, by highlighting all instances of “the” in the active document:

SendKeys “the”
SendKeys “%T”
SendKeys “%F”
SendKeys “%{F4}”

With Dialogs(wdDialogEditFind)
.Display
End With

This works by selecting (toggling?) the “Highlight all items found in:” option (and running the code twice seems to make it work regardless of the initial setting).

To use this you might need to worry about the sticky Find settings, and whether or not the “Find what” content exists in the document. There’s also the flashing dialog to contend with (setting ScreenUpdating to False doesn’t seem to make a difference).

In my experience the SendKeys command can be flaky, which is why I only ever use it if nothing else works.

Reply | Quote