Regular expression only returns 1 match

Question

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

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

It will not always be a single digit. For example, I may need to separate "XXX11-18"or something like that.

I have never used regular expressions, but I keep trying different things to make this work, and it seems that it finds only 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 realized that this was not the problem. The error actually occurs in the comment line, but I assume that it was only 1 match.

Google, , , , , RE, , , , .

!

Answer 1

@Shai Rado . , , .

1 , 1 . . , .

, , ( 1 ):

Answer 2

Function :

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

Sub

Option Explicit

Sub TestRegEx()

Dim Res As String

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

End Sub

, :

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

Answer 3

Another RegExp option that uses SubMatches:

Test

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

the 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