Excel – Subscript out of range, Excel VBA, Trying to create an array within a loop

excelrangesubscriptvba

I'm trying to loop through all the controls in my form and for each time it catches a Label, the Tag element of the Label is added into the array.

Dim labelCounter As Integer
labelCounter = 0
Dim arrayTag() As String

For Each ctl In Me.Controls
    Select Case TypeName(ctl)
        Case "Label"
        arrayTag(labelCounter) = ctl.Tag
        labelCounter = labelCounter + 1
    End Select
Next

I keep getting the Subscript out of range error. What's going wrong here?

Best Answer

Sub Tester()

    Dim labelCounter As Integer
    Dim arrayTag() As String
    Dim ct As String, ctl

    labelCounter = 0
    ReDim arrayTag(0 To labelCounter)
    For Each ctl In Me.Controls
        ct = TypeName(ctl)
        If ct = "Label" Then
            If labelCounter > 0 Then
                ReDim Preserve arrayTag(0 To labelCounter)
            End If
            arrayTag(labelCounter) = ctl.Tag
            labelCounter = labelCounter + 1
        End If
    Next
    'Debug.Print Join(arrayTag, ",")
End Sub
Related Topic