Populating VBA dynamic arrays
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)