VBA and Regex with Named Groups

Question

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

Mathieu Guindon · Accepted Answer · 2017-03-22 22:58:59Z

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!

Ron Rosenfeld · Accepted Answer · 2017-03-22 20:58:16Z

1

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.

edited Mar 22, 2017 at 20:58

answered Mar 22, 2017 at 20:47

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

11 Comments

CoupFlu Over a year ago

This seems simpler now than I thought it was going to be. Can someone give me a walk through on picking out matching results with a loop. I keep seeing different methods used.

Ron Rosenfeld Over a year ago

@JamieSnipes Supposedly, For Each object in Collection is a bit more efficient than For I = 0 to .count-1. With your submatches, you are going to need to maintain a counter anyway.

Mathieu Guindon Over a year ago

You might want to mention that the tool isn't free.

Mathieu Guindon Over a year ago

Yes, that's exactly what I said earlier: use a For Each loop to iterate object collections. If you think about how arrays are represented in memory it's not surprising either that a For loop is faster for arrays. Object collections use a hidden [_NewEnum] property getter to yield the next object reference, they're literally optimized for For Each loops - it's much more work to retrieve an item by its index than using the enumerator.

Comintern Over a year ago

@RonRosenfeld - Indexing into a SAFEARRAY is simply pointer math and memory dereferencing. Once you have the base address and the element size, it's as simple as calculating the offset. I have a VBA demo in this answer.

|

Collectives™ on Stack Overflow

VBA and Regex with Named Groups

2 Answers 2

Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related