CODEWITHSUNDEEP
regexvbaexcel
CoupFlu
CoupFlu

Reputation: 321

VBA and Regex with Named Groups

Converting a method I had in Java to VBA is turning out to be much more difficult than anticipated.

I have a regex which can be found here

It uses named object groups which after some research, do not appear to be supported by VBA. I'm attempting to write a function for excel which will format IP addresses into different formats based on the inputs to the function.

How can I work around using Named Groups in order to capture the different patterns? 

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

Upvotes: 3

Views: 1822

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

You don't. VBA uses VBScript-flavored regex, which doesn't support named groups. Use numbered capture groups instead.

And since that makes your regex pattern much harder to debug... and since you asked for a work-around, if you have access to Visual Studio you could whip up a little COM-visible class library that exposes a .net-flavored regex (much more similar to Java's than VBScript's) API that you could reference and use in your VBA code.

The downside is that you now need to manage deployment of that library.

If you have to stick with VBScript-regex, then use a tool like Expresso (no affiliation, free with registration required after 30 days) to help you out.

Expresso regex analyzer

Expresso understands .net regex though, so it will deem your pattern completely valid.

Or, use Rubberduck's regex assistant feature, which understands VBScript-regex and will tell you which part(s) of your pattern won't work in VBA (disclaimer: I maintain that project - it's free, open source and actively maintained):

Rubberduck regex analyzer

enter image description here

Good luck!

Upvotes: 4

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

RegexBuddy is a program which can convert your Java regex to a vbScript compatible regex. It gives the same output as the example you posted in your link, but with numbered rather than named capturing 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 gives the output into numbered groups as follows:

1 Address
2 FromSeg
3 CIDR
4 ToSeg
5 ToIP

and the debug.print output in your macro appears to be correct, using your examples from your link.

Upvotes: 1

Related Questions