CODEWITHSUNDEEP
regexvbaexcel
Tianna Wrona
Tianna Wrona

Reputation: 342

Regular Expression only returns 1 match

My VBA function should take a string referencing a range of units (i.e. "WWW1-5") and then return another string.

I want to take the argument, and put it in a comma separated string, So "WWW1-5" should become "WWW1, WWW2, WWW3, WWW4, WWW5".

It's not always going to be a single digit. For example, I might need to separate "XXX11-18" or something similar.

I have never used regular expressions, but keep trying different things to make this work and it seems to only be finding 1 match instead of 3.

Any ideas? Here is my code:

Private Function split_group(ByVal group As String) As String   
    Dim re As Object
    Dim matches As Object
    Dim result As String
    Dim prefix As String
    Dim startVar As Integer
    Dim endVar As Integer
    Dim i As Integer

    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "([A-Z]+)(\d+)[-](\d+)"
    re.IgnoreCase = False
    Set matches = re.Execute(group)

    Debug.Print matches.Count

    If matches.Count <> 0 Then
        prefix = matches.Item(0)
        startVar = CInt(matches.Item(1)) 'error occurs here
        endVar = CInt(matches.Item(2))
        result = ""

        For i = startVar To endVar - 1
            result = result & prefix & i & ","
        Next i

        split_group = result & prefix & endVar
    Else
        MsgBox "There is an error with splitting a group."
        split_group = "ERROR"
    End If

End Function

I tried setting global = true but I realized that wasn't the problem. The error actually occurs on the line with the comment but I assume it's because there was only 1 match.

I tried googling it but everyone's situation seemed to be a little different than mine and since this is my first time using RE I don't think I understand the patterns enough to see if maybe that was the problem.

Thanks!

Upvotes: 4

Views: 1030

Answers (3)

Tianna Wrona
Tianna Wrona

Reputation: 342

@Shai Rado 's answer worked. But I figured out on my own WHY my original code was not working, and was able to lightly modify it.

The pattern was finding only 1 match because it was finding 1 FULL MATCH. The full match was the entire string. The submatches were really what I was trying to get.

And this is what I modified to make the original code work (asking for each submatch of the 1 full match):

How I modified the code

Upvotes: 2

brettdj
brettdj

Reputation: 55672

Another RegExp option, this one uses SubMatches:

Test

Sub TestRegEx()
Dim StrTst As String
MsgBox WallIndside("WAL7-21")
End Sub

Code

Function WallIndside(StrIn As String) As String

    Dim objRegex As Object
    Dim objRegMC As Object
    Dim lngCnt As Long

    Set objRegex = CreateObject("VBScript.RegExp")
    With objRegex
        .Global = True
        .IgnoreCase = True
        .Pattern = "([a-z]+)(\d+)-(\d+)"
        If .test(StrIn) Then
            Set objRegMC = .Execute(StrIn)
            For lngCnt = objRegMC(0).submatches(1) To objRegMC(0).submatches(2)
                 WallIndside = WallIndside & (objRegMC(0).submatches(0) & lngCnt & ", ")
            Next
                WallIndside = Left$(WallIndside, Len(WallIndside) - 2)
        Else
            WallIndside = "no match"
        End If
    End With
End Function

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

Try the modified Function below:

Private Function split_metergroup(ByVal group As String) As String

    Dim re As Object
    Dim matches As Variant
    Dim result As String
    Dim prefix As String
    Dim startVar As Integer
    Dim endVar As Integer
    Dim i As Integer

    Set re = CreateObject("VBScript.RegExp")
    With re
        .Global = True
        .IgnoreCase = True
        .Pattern = "[0-9]{1,20}" '<-- Modified the Pattern
    End With

    Set matches = re.Execute(group)                  
    If matches.Count > 0 Then
        startVar = CInt(matches.Item(0)) ' <-- modified 
        endVar = CInt(matches.Item(1)) ' <-- modified
        prefix = Left(group, InStr(group, startVar) - 1) ' <-- modified
        result = ""

        For i = startVar To endVar - 1
            result = result & prefix & i & ","
        Next i    
        split_metergroup = result & prefix & endVar
    Else
        MsgBox "There is an error with splitting a meter group."
        split_metergroup = "ERROR"
    End If

End Function

The Sub I've tested it with:

Option Explicit

Sub TestRegEx()

Dim Res As String

Res = split_metergroup("DEV11-18")
Debug.Print Res

End Sub

Result I got in the immediate window:

DEV11,DEV12,DEV13,DEV14,DEV15,DEV16,DEV17,DEV18

Upvotes: 2

Related Questions