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
– Jon Lawton, Commented Nov 28, 2011 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
– Jon Lawton, Commented Nov 28, 2011 at 20:36
Because I didn't have enough reputation to post immediately. — Jon Lawton
– Jon Lawton, Commented Nov 30, 2011 at 17:05

brettdj · Accepted Answer · 2011-11-28 21:25:58Z

15

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

answered Nov 28, 2011 at 21:25

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brettdj · Accepted Answer · 2015-09-22 14:55:30Z

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

Collectives™ on Stack Overflow

Excel VBA Regex Match Position

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related