VBA and Regex with named groups
Converting the method I used in Java to VBA turns out to be much more complicated than expected.
I have a regex which can be found here
It uses named groups of objects which, after some research, are not supported by VBA. I am trying to write a function for excel that will format IP addresses in different formats based on the input to the function.
How can I work with Named Groups to capture various templates?
Function formatIP(item As String, displayType As String) As String
'displayTypes CIDR,MASK,RANGE
'Set theRegEx = CreateObject("VBScript.RegExp")
Dim theRegEx As New RegExp
With theRegEx
.Global = True
.MultiLine = False
.IgnoreCase = False
.Pattern = "(?<address>\d{1,3}(?:\.\d{1,3}){2}\.(?<FromSeg>\d{1,3}))(?:(?:\/|\s+\/\s+)(?<CIDR>\d{1,2})|(?:-|\s+to\s+)(?<ToSeg>\d{1,3}(?![\d\.]))|(?:-|\s*to\s+)(?<ToIP>\d{1,3}(?:\.\d{1,3}){3})|\s+(?<Mask>25\d(?:\.\d{1,3}){3})|\s*)?"
.Execute (item)
End With
'Set MyMatches = theRegEx.Execute(item)
Debug.Print "SubMatches.Count: " & MyMatches.item(0).SubMatches.Count
If MyMatches.Count <> 0 Then
With MyMatches
For myMatchCt = 0 To .Count - 1
Debug.Print "myMatchCt: " & myMatchCt
For subMtCt = 0 To .item(subMtCt).SubMatches.Count - 1
Debug.Print "subMtCt: " & subMtCt
Debug.Print ("," & .item(myMatchCt).SubMatches.item(subMtCt))
Next
Next
End With
Else
Debug.Print "No Matches"
End If
formatIP = ""
End Function
source to share
Not. VBA uses VBScript regex, which doesn't support named groups. Use numbered capture groups instead.
And since your regex pattern is much more difficult to debug ... and since you asked for a workaround, if you have access to Visual Studio, you can hack the little COM visibility class library that provides the .net -flavored regex (much more like Java than VBScript) API that you can reference and use in your VBA code.
The downside is that you now need to manage the deployment of this library.
If you need to stick to VBScript-regex, use a tool like Expresso (no affiliation, free with registration required after 30 days) to help you.
Expresso understands .net regex, so it will assume your pattern is completely valid.
Or use the Rubberduck regex helper function, which understands VBScript-regex and tells you which part (s) of your template won't work in VBA (disclaimer: I maintain this project - it's free, open source, and actively supported) :
Good luck!
source to share
RegexBuddy is a program that can convert your Java regex to a vbScript compatible regex. It gives the same result as the example you posted in your link, but with numbered rather than named capture groups:
(\d{1,3}(?:\.\d{1,3}){2}\.(\d{1,3}))(?:(?:/|\s+/\s+)(\d{1,2})|(?:-|\s+to\s+)(\d{1,3}(?![\d.]))|(?:-|\s*to\s+)(\d{1,3}(?:\.\d{1,3}){3})|\s+(25\d(?:\.\d{1,3}){3})|\s*)?
It outputs the output to numeric groups like this:
1 Address
2 FromSeg
3 CIDR
4 ToSeg
5 ToIP
and the output debug.print
in your macro displays correctly using your examples from your link.
source to share