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 · Answer 1 · 2017-03-22T22:58:59.780

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 · Answer 2 · 2017-03-22T20:58:16.867

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 '17 at 20:58

answered Mar 22 '17 at 20:47

Ron Rosenfeld

53,870
7
28
60

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. – CoupFlu Mar 22 '17 at 21:06
@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. – Ron Rosenfeld Mar 22 '17 at 21:38
For Each is *definitely* faster than a For loop for iterating object collections. – Mathieu Guindon Mar 22 '17 at 22:19
1

You might want to mention that the tool isn't free. – Mathieu Guindon Mar 22 '17 at 22:57
@Mat'sMug That's one reason why I provided the translation. Although all that was necessary was to remove the `capture name tags` from the regex. I just looked at the web site. The price is $39.95 with a three month money-back if not satisfied guarantee. I have found it an excellent tool. – Ron Rosenfeld Mar 23 '17 at 01:52
@Mat'sMug That's what I have read about the `For Each` loops. And that's what I frequently use. But I've never actually timed it myself. Is it also faster if one has to increment a counter on each pass through the loop? – Ron Rosenfeld Mar 23 '17 at 01:55
[This guy timed it](http://analystcave.com/vba-for-loop-vs-for-each-loop/) - "*the For Each loops was almost 27x faster than the For loop*" ...that's *orders of magnitude* faster. So.. Yeah. – Mathieu Guindon Mar 23 '17 at 02:29
@Mat'sMug Interesting that speed difference was for looping a Collection. The For ... loop was 3x faster looping an array. – Ron Rosenfeld Mar 23 '17 at 02:45
1

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. – Mathieu Guindon Mar 23 '17 at 03:03
2

@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](http://stackoverflow.com/a/39146283/4088852). – Comintern Mar 23 '17 at 03:23
@Mat'sMug Many thanks to you for your more detailed explanations. – Ron Rosenfeld Mar 23 '17 at 11:31

VBA and Regex with Named Groups

2 Answers2