Results 1 to 15 of 15

Thread: Find All in VBA

  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.
    You know it's time to diet when you push away from the table and the table moves.

  2. The Following User Says Thank You to Omega3 For This Useful Post:

    yken (2015-02-17)

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  4. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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.
    You know it's time to diet when you push away from the table and the table moves.

  5. #4
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post
    Out of curiosity, did Microsoft fix this in 2007 or the next 2010 version?
    You know it's time to diet when you push away from the table and the table moves.

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't know!

  7. #6
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    I don't know!
    That's okay Hans. Just curious. Have a great day!
    You know it's time to diet when you push away from the table and the table moves.

  8. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  9. #8
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by mdbct View Post
    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.
    You know it's time to diet when you push away from the table and the table moves.

  10. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    Quote Originally Posted by Omega3 View Post
    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.

  11. #10
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Slinky View Post
    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 Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2006
    Location
    Maryland, USA
    Posts
    690
    Thanks
    17
    Thanked 66 Times in 56 Posts
    Quote Originally Posted by mbarron View Post
    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
    Pam Caswell

  13. #12
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Pam,

    Quote Originally Posted by PamCaswell View Post
    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 Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  14. #13
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Wellington, New Zealand
    Posts
    143
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  15. The Following User Says Thank You to William For This Useful Post:

    yken (2015-02-17)

  16. #14
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Quote Originally Posted by David Gray View Post
    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

  17. #15
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Gary,

    Quote Originally Posted by Gary Frieder View Post
    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.

    Quote Originally Posted by Gary Frieder View Post
    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 Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •