Populating VBA dynamic arrays

Question

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 

Answer 1

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 

Answer 2

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.