Populating VBA dynamic arrays Populating VBA dynamic arrays vba vba

Populating VBA dynamic arrays


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

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


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 IntegerEnd TypePublic 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 WithEnd 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.


Yes, you're looking for the ReDim statement, which dynamically allocates the required amount of space in the array.

The following statement

Dim MyArray()

declares an array without dimensions, so the compiler doesn't know how big it is and can't store anything inside of it.

But you can use the ReDim statement to resize the array:

ReDim MyArray(0 To 3)

And if you need to resize the array while preserving its contents, you can use the Preserve keyword along with the ReDim statement:

ReDim Preserve MyArray(0 To 3)

But do note that both ReDim and particularly ReDim Preserve have a heavy performance cost. Try to avoid doing this over and over in a loop if at all possible; your users will thank you.


However, in the simple example shown in your question (if it's not just a throwaway sample), you don't need ReDim at all. Just declare the array with explicit dimensions:

Dim MyArray(0 To 3)