Excel VBA Formula Matching Position

Question

How can I capture the position of the first consistent 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 

Firstly, I'm not quite sure what .Match returns (string, integer, etc.). One of the solutions found says that I have to create a Match object and then grab a position from there, but unlike vb , vba does not recognize the Match object. I also saw some code as shown below, but I'm not necessarily looking for a value, just the first placement of the string:

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

To some extent, ignoring any possible syntax error above (mainly because of the changing types of variables on the right and left), how can I easily / simply do this?

Thanks!

Answer 1

You can use FirstIndex to return the position of matches using the Execute method, i.e.

 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 

Answer 2

In the interest of others who might have 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 the Match type, only the regular Object type will be executed (given that everything returned is a class). Then, if you want to capture the location of the index, just use .FirstIndex in the match [of your choice], or if you want a value, give us .Value