Breaking up multiple numeric ranges - questions?

[Rhudi]

I am working with a list of vlan ranges. Some of these will be ranges: "101-120" or individual numbers: "150,158,201" or even a combination: "101-120,150,158,201,300-450,2000-2500".
What I needed to do is break this up into an array that contains every number that is part of the whole listed group. That array is used to compare if a number from something else is a match; part of the listed range.
In the numbers above, 105, 150, 302,2000,2499 or 2500 would match. But, 121, 155, 210, 1999, 2501 would not match.

The source data comes from a Cisco switch configuration file, obtained with 'show run'. Within that text file will be an area similar to this:

!
interface GigabitEthernet5/1
 switchport
 switchport trunk encapsulation dot1q
 switchport trunk allowed vlan 1-1316,1318-1334,1336-3371,3376-3415,3420-3423
 switchport trunk allowed vlan add 3428-3465,3468-3483,3500-3539,3576-3587
 switchport trunk allowed vlan add 3600-4094
 switchport mode trunk
 mtu 9216
!

I open the text file into an array, where each element of the array is loaded with the text file with the appropriate vbLf (or vbCrLf - I have a way to be sure).
When the array is loaded (has more than -1 elements) I can use Application.Match to find the line that has the interface line I'm using.
I will use:

    t = Application.Match("interface GigabitEthernet" & Mid(Sheets(1).Range("B4"), 3), sDataArray, 0)
    If Not IsError(t) Then ...

If 't' is an error, it wasn't found and I can go do stuff. If the string is found, it will be a number that is an index within the array to the next line.
In this example, sDataArray(t) will be " switchport". I will use a do-loop until I find the trailing "!".
During that loop, I will add the numeric parts to an intermediate string until I have every line.
The code for that, looks like this:

    t = Application.Match("interface GigabitEthernet" & Mid(Sheets(1).Range("B4"), 3), sDataArray, 0)
    If Not IsError(t) Then
        VLANList = ""
        Do Until sDataArray(t) = "!"
            If Left(sDataArray(t), 31) = " switchport trunk allowed vlan " Then
                If Left(Mid(sDataArray(t), 31), 5) = " add " Then
                    VLANList = VLANList & Mid(sDataArray(t), 36) & ","
                Else
                    VLANList = VLANList & Mid(sDataArray(t), 32) & ","
                End If
            End If
            t = t + 1
        Loop
        If Right(VLANList, 1) = "," Then VLANList = Left(VLANList, Len(VLANList) - 1)
    Else
        MsgBox "The switch ( " & Range("D4") & " ) does not appear to use Port " & "GigabitEthernet" & Mid(Sheets(1).Range("B4"), 3)
        End
    End If

The variable VLANList will wind up with this (from the above example):
"1-1316,1318-1334,1336-3371,3376-3415,3420-3423,3428-3465,3468-3483,3500-3539,3576-3587,3600-4094"

When that variable is set, I do this:

    sVLANArray = Split(VLANList, ",")

    VLANList = ""
    If UBound(sVLANArray) = -1 Then
        MsgBox "No VLANs are specified." & vbLf & "Assume 1 through 4094 are used!"
        For z = 1 To 4094
            VLANList = VLANList & z & ","
        Next z
        sVLANArray = Split(VLANList, ",")
    End If
    For y = LBound(sVLANArray) To UBound(sVLANArray)
        iVLANArray = Split(sVLANArray(y), "-")
        For z = iVLANArray(LBound(iVLANArray)) To iVLANArray(UBound(iVLANArray))
            VLANList = VLANList & z & ","
        Next z
    Next y

    VLANArray = Split(VLANList, ",")
    VLANList = ""

When this is done, VLANArray will contain all the individual numbers that are within the skipping lists of numbers. I realise there is a potential glitch here, if the VLANList has no 'ranges', but is instead only a 'skip-list' (I believe I figured that out).

I then use Application.Match to find the values I need to confirm.

I have two questions: 1) is this horribly clunky? 2) Can I confirm that only exact matches will be found? I don't want to be looking for 310 and get a false match because the list has something like 3101.