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

Question

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?

What is the value of labelCounter when it falls over? Have you got Option Base set to 1? — barryleajo
– barryleajo, Commented Feb 5, 2015 at 22:29
Where is the code residing inside the userform or in a separate module. If the latter don't use Me use the actual form name — Jeanno
– Jeanno, Commented Feb 5, 2015 at 22:38

Tim Williams · Accepted Answer · 2015-02-05 23:37:55Z

5

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

answered Feb 5, 2015 at 23:37

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Acantud · Accepted Answer · 2015-02-05 23:24:56Z

I think your only issue is you created an array (arrayTag) without specifying how many elements are in it. As far as I recall, when creating an array you need to either A.) Specify the number of elements in it, or B.) Create an array the way you did (With empty parenthesis) and then ReDim it once you reach a point in the code execution where you know how many elements are in it. I think you can also ReDim Preserve to change the size of the array without deleting its contents.

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

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related