Populating VBA dynamic arrays


The following code gives me error 9 "subscript out of range". I meant to declare a dynamic array so that the dimension changes as I add elements to it. Do I have to create a "spot" on the array before I store something in it like in JS?

Sub test_array()
    Dim test() As Integer
    Dim i As Integer
    For i = 0 To 3
        test(i) = 3 + i
    Next i
End Sub

This question is tagged with arrays vba

~ Asked on 2012-01-13 13:01:10

The Best Answer is


in your for loop use a Redim on the array like here:

For i = 0 to 3
  ReDim Preserve test(i)
  test(i) = 3 + i
Next i

~ Answered on 2012-01-13 13:08:41


As Cody and Brett mentioned, you could reduce VBA slowdown with sensible use of Redim Preserve. Brett suggested Mod to do this.

You can also use a user defined Type and Sub to do this. Consider my code below:

Public Type dsIntArrayType
   eElems() As Integer
   eSize As Integer
End Type

Public Sub PushBackIntArray( _
    ByRef dsIntArray As dsIntArrayType, _
    ByVal intValue As Integer)

    With dsIntArray
    If UBound(.eElems) < (.eSize + 1) Then
        ReDim Preserve .eElems(.eSize * 2 + 1)
    End If
    .eSize = .eSize + 1
    .eElems(.eSize) = intValue
    End With

End Sub

This calls ReDim Preserve only when the size has doubled. The member variable eSize keeps track of the actual data size of eElems. This approach has helped me improve performance when final array length is not known until run time.

Hope this helps others too.

~ Answered on 2013-08-21 18:23:44

Most Viewed Questions: