Regexp Return position VBA-CodePudding

I am looking to have my regexp return the value of of the pattern I am looking for or the position. Similar to how the Instr function works returning its position in a string I would like to be able to do this with patterns. what i have so far just replaces the patters and i cannot figure out how to have it return a position.

Sub test

Dim regex As Object
Dim r As Range, rC As Range
Dim firstextract As Long
' cells in column A
Set r = Range("A2:A3")
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"

' loop through the cells in column A and execute regex replace
Dim MyArray(10, 10) As Integer

For CntMtg = 1 To 100
For Each rC In r
    If rC.Value <> "" Then rC.Value = regex.Replace(rC.Value, "Extract from here")
Next rC
Next
End sub

CodePudding user response：

If you don't want to replace but just get the position of a hit, use the Execute-method. It returns a Collection of Matches. A match has basically three properties:
FirstIndex is the position of the match within your string
Length is the length of the match that was found
Value it the match itself that was found

If you can have more than one match within a string, you need to set the property Global of your regex, else the collection will at most find 1 hit.

The following code uses early binding (as it helps to figure out properties and methods), add a reference to Microsoft VBScript Regular Expressions 5.5.

Dim regex As RegExp
regex.Pattern = "[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"
regex.Global = True
    
Dim matches As MatchCollection, match As match
Set matches = regex.Execute(s)
For Each match In matches
    Debug.Print "Pos: " & match.FirstIndex & "  Len: " & match.Length & "  - Found: " & match.Value
Next

For details, see the official documentation: https://docs.microsoft.com/en-us/dotnet/standard/base-types/the-regular-expression-object-model