4

I would like to go through and find all of the "End" statements in my code but skipping all of the "End x" statements like "End If", "End Sub", "End function", etc.--Just the pure "End". My thought was to use pattern matching, but I am unsure of how to do that.

I already tried using "End\n" and "End[\n]".

VBE search box example

Does anyone know how to search for words that end in new lines?

Tomerikoo
  • 18,379
  • 16
  • 47
  • 61
ScottC
  • 41
  • 1
  • 1
    Maybe look at: https://www.ozgrid.com/forum/index.php?thread/59444-vba-find-pattern-matching-whole-line/ – Tim Williams Oct 28 '21 at 16:43
  • I'm curious what the use case of this is? Do you just have a huge module you are hoping to save a little time on, or were you envisioning some kind of programmatic application of this? – TylerH Oct 28 '21 at 19:05

2 Answers2

1

The "find" function in the VBA editor does not support this kind of parameter/functionality.

You will have to manually step through the results and skip the ones you don't want to skip, or manually modify the "End" instances you don't want to catch, then search & replace, and finally restore all the End instances back to what you want.

TylerH
  • 20,799
  • 66
  • 75
  • 101
  • Ok, thanks for letting me know. What is the pattern matching option good for then? – ScottC Oct 28 '21 at 18:38
  • @ScottC Not much, unfortunately. I think the Pattern Matching functionality actually only works in the "Find" function, rather than the "Find & Replace" function. You can use ?, *, !, .., etc. IIRC, but it's been years since I even last tried to use that. I don't think there's any documentation left surviving today, either... that code has not been touched by MSFT in probably 20 years. – TylerH Oct 28 '21 at 19:04
  • @ScottC, TylerH is right; MSFT did not even bother to update the Find and Replace engine from WordVBA; if you think VBA Regex is obsolete, wait until you have to use Word VBA find and replace... the best source is from [Graham Mayor](http://www.gmayor.com/replace_using_wildcards.htm). But stick with Regex (Pattern Matching). The only use of Word Find and Replace instead of Regex is because using it you can preserve formatting after inserting text; with Regex you have to format again for yourself... no use for this in VBA editor. – Marcelo Scofano Diniz Oct 28 '21 at 21:24
  • I am not quit sure what you want to do with it, so try to paste your code in Word, press Ctrl+H and insert ``End^p`` in Find what and ``End`` in Replace with. My apologies if I misunderstood you question. – Elio Fernandes Oct 28 '21 at 21:57
1

Apologies for answering so long after the question was asked, but thought this information would help future readers as this question is still being actively found.

@TylerH is right that the specific search requested by the user cannot be performed in the VBE Find tool. For information, when "Use Pattern Matching" is selected the VBE Find tool supports use of:

? - single character

* - zero or more characters (on the same line)

# - single digit (0 to 9)

[charlist] - any single character in charlist

[!charlist] - any single character not in charlist

... where charlist can be a range of characters (eg [A-Z]) but must be in order (eg [Z-A] is not valid), it can also include multiple ranges of characters (eg [A-BD-E] matches A, B, D or E). Also to match any of ?, * or # then enclose them in square brackets (eg [*] matches an asterisk).

This means the VBE Find tool performs very similarly (perhaps identically ... but I can't provide assurances, VB and VBA not being the same language) to the VB Like operator, for which documentation is here

The alternative (which will perform the specific search in the question) is to use the 'Find Text' tool in the VBE Add-In MZ-Tools - though note MZ-Tools is a paid-for tool ... please note I am NOT in any way associated with MZ-Tools or it's author. The search text to use in MZ-Tools for the specific search requested in the question is: end\r?$

JohnM
  • 2,422
  • 2
  • 8
  • 20