Excel VBA Regex Match Position

Question

How do I grab the position of the first matched result in a regular expression? See below.

Function MYMATCH(strValue As String, strPattern As String, Optional blnCase As Boolean = True, Optional blnBoolean = True) As String
    Dim objRegEx As Object
    Dim strPosition As Integer

    ' Create regular expression.
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.Pattern = strPattern
    objRegEx.IgnoreCase = blnCase

    ' Do the search match.
    strPosition = objRegEx.Match(strValue)

    MYMATCH = strPosition
End Function

For one, I'm not entirely certain what .Match is returning (string, integer, etc.). The one solution I found said I should create a Match object to and then grab the position from there, but unlike vb, vba does not recognize the Match object. I've also seen some code like the following, but I'm not necessarily looking for the value, just the first string placement:

If allMatches.count <> 0 Then
    result = allMatches.Item(0).submatches.Item(0)
End If

Somewhat ignoring any of the possible syntax errors above (mostly due to me changing variable types right and left), how do I easily/simply accomplish this?

Thanks!

You're right, my fault (I forgot that it helps everyone's reputation on this site)...besides that, I figured it out on my own...again. — Jon Lawton, Nov 28 '11 at 20:26
However, I'll post my answer in 8 hours for the benefit for anyone else who runs into this problem. — Jon Lawton, Nov 28 '11 at 20:36
Because I didn't have enough reputation to post immediately. — Jon Lawton, Nov 30 '11 at 17:05

score 14 · Accepted Answer · answered Nov 28 '11 at 21:25

You can use FirstIndex to return the position of matches using the Execute method, ie

Function MYMATCH(strValue As String, strPattern As String, Optional blnCase As Boolean = True, Optional blnBoolean = True) As String
    Dim objRegEx As Object
    Dim strPosition As Integer
    Dim RegMC

    ' Create regular expression.
    Set objRegEx = CreateObject("VBScript.RegExp")
    With objRegEx
        .Pattern = strPattern
        .IgnoreCase = blnCase
        If .test(strValue) Then
            Set RegMC = .Execute(strValue)
            MYMATCH = RegMC(0).firstindex + 1
        Else
            MYMATCH = "no match"
        End If
    End With
End Function

Sub TestMe()
    MsgBox MYMATCH("test 1", "\d+")
End Sub

score 5 · Answer 2 · edited Sep 22 '15 at 14:55

For the benefit of others who may be having this problem, I finally figured it out.

Option Explicit

Function CHAMATCH(strValue As String, strPattern As String, Optional blnCase As Boolean = True, Optional blnBoolean = True) As String
    Dim objRegEx As Object
    Dim objPosition As Object
    Dim strPosition As String

    ' Create regular expression.
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.Pattern = strPattern
    objRegEx.IgnoreCase = blnCase

    ' Do the search match.
    Set objPosition = objRegEx.Execute(strValue)
    strPosition = objPosition(0).FirstIndex

    CHAMATCH = strPosition
End Function

Instead of a Match type, just a regular Object type will do (considering all it's returning is a class). Then, if you want to grab the index location, just use .FirstIndex on the match [of your choice], or if you want the value, us .Value

Excel VBA Regex Match Position

2 Answers2