Excel VBA Regex Match Position
Solution 1
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
Solution 2
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
Jon Lawton
Stack Overflow is here to save people like myself from the traps of life.
Updated on June 12, 2022Comments
-
Jon Lawton almost 2 years
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 aMatch
object to and then grab the position from there, but unlike vb, vba does not recognize theMatch
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!