Find Challenge

gmaxey · #1 01-07-2025, 05:20 AM

Batman1

I consider myself schooled. Thank you for the lesson. I was reading up on look ahead and look behind assertions last night before reading your reply this morning. I fully understand your pattern now and this my code with my own notes.

Code:

Sub RegExMethod()
Dim RegEx As Object, Matches As Object, Match As Object
Dim oRng As Range
  Set RegEx = CreateObject("VBScript.RegExp")
  With RegEx
    .Global = True
    .Pattern = "(?=\d{0,3}2)\d{4,5}"
    'Positive lookahead.
    'y = (?=\d{0,3}2) x = \d{4,5}
    'Match second part x only if first part y matches.
    'y matches 2, #2, ##2 or ###2
    'x matches 2####, #2###, ##2##, and ###2# or 2###, #2##, ##2# and ###2
  End With
  Set Matches = RegEx.Execute(ActiveDocument.Range.Text)
  For Each Match In Matches
    ActiveDocument.Range(Match.FirstIndex, Match.FirstIndex + Match.Length).HighlightColorIndex = wdBrightGreen
  Next
lbl_Exit:
  Exit Sub
End Sub

Now, in reading about the look ahead and look behind assertions earlier, I came across an example:

Pattern = "(?<=Tom|Jack)Sprat"
'Look behind assertions. - matches "x" only if "x" is preceded by "y". For example, (?<=Jack|Tom)Sprat matches "Sprat" only if it is preceded by "Tom" or "Jack." However, neither "Jack" nor "Tom" is part of the match results.

Sample text:

Jack Sprat, Johnny Sprat, Tom Sprat, Mary Sprat and Bill Sprat.

I learned (after some frustration) that look behind assertions are unfortunately not supported in VBA. So, how do achieve the same results with VBA? Here is a look ahead that comes close, but unlike the look behind described above, it returns both parts y and x.

Code:

Sub RegEx()
Dim RegEx As Object, Matches As Object, Match As Object
Dim oRng As Range
  Set RegEx = CreateObject("VBScript.RegExp")
  With RegEx
    .Global = True
    .Pattern = "(?=Tom|Jack)\S+ Sprat"
  End With
  Set Matches = RegEx.Execute(ActiveDocument.Range.Text)
  For Each Match In Matches
    'Returns Jack Sprat and Tom Sprat (Not just Sprat like lookback)
    ActiveDocument.Range(Match.FirstIndex, Match.FirstIndex + Match.Length).Select
  Next
lbl_Exit:
  Exit Sub
End Sub

batman1 · #2 01-07-2025, 07:05 AM

Quote:

Originally Posted by gmaxey

I learned (after some frustration) that look behind assertions are unfortunately not supported in VBA. So, how do achieve the same results with VBA? Here is a look ahead that comes close, but unlike the look behind described above, it returns both parts y and x.

As far as I remember, in VBA you can only use look ahead, you can't use look behind. I tested in Excel some time ago. Maybe in Perl, Python, but not in VBA. That's why I didn't mention look behind in the last post

Since you can't use look behind, you have to experiment. In this example, you can e.g.

Code:

Sub RegEx()
Dim RegEx As Object, Matches As Object, Match As Object
Dim oRng As Range
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .Pattern = "(?=Tom|Jack)\S+ (Sprat)"
    End With
    Set Matches = RegEx.Execute(ActiveDocument.Range.Text)
    For Each Match In Matches
        ActiveDocument.Range(Match.FirstIndex + Match.Length - Len(Match.SubMatches(0)), Match.FirstIndex + Match.Length).HighlightColorIndex = wdBrightGreen
        Debug.Print Match.SubMatches(0)
    Next
lbl_Exit:
  Exit Sub
End Sub

vivka · #3 01-07-2025, 08:43 AM

The first thing I did when I came home from work today was visiting this thread. Greg and Batman1, I take off my hat to both of you!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Any work-around for this challenge	RRB	Word	6	08-22-2023 11:47 AM
Formula challenge 3 criteria	JMC44	Excel Programming	0	04-04-2015 07:38 PM
Challenge for a genius ?	e.roberts	Office	3	07-13-2012 01:36 AM
Sorting Challenge	gbaker	Excel Programming	11	06-22-2012 09:39 AM
Word Challenge	jpotter2	Word	3	03-22-2011 02:07 PM