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

A class-based approach with BetterArray
In 2013, Ioannis commented that you could create a class to manage array resizing in chunks. An implementation with many other features—sorting, slicing, filtering, and conversion, to name a few—can now be found here: https://senipah.github.io/VBA-Better-Array/ (I am not connected to the project). A page on its capacity property explains the internal doubling process. More generally:

Stored in a single .cls file, the BetterArray class can easily be imported into any new or existing VBA project. It’s written in pure VBA and doesn’t use any external dependencies. As such, it should work in any application that supports VBA scripting across both Windows and Mac operating systems.

In other words, you simply download the class module (a single .cls file, linked here), drag it into your project^*, and from there it’s available like a collection or any other object to create. Here I use it to get the contents of the current directory:

Sub DemoBetterArray()

    Dim ba As BetterArray, tempDir As String, basicArray As Variant
    Set ba = New BetterArray
    tempDir = Dir("")
    Do While tempDir <> ""
        ba.Push tempDir 'I set no bounds, but I'm adding an element
        tempDir = Dir()
    Loop
    basicArray = ba.Items 'See results in a traditional array
    
End Sub

For this example, you could do similar with an ArrayList, generally available on Windows via .NET (but apparently obsolete in .NET). See a summary. In any case, there are major differences between these objects you could explore. For adding 1,000,000 integers, I found BetterArray to be several times faster than an ArrayList.

A tip for using the BetterArray documentation: While the page on examples is currently blank, the pages on methods (listed here) give many helpful examples of what the class can do in addition to expanding efficiently.

Expansion via Error Handling
Another possibility not yet discussed is to use error handling. The approach is demonstrated by Bruce McKinney in Hardcore Visual Basic, 2nd Edition (1997). The function below uses this idea.

Sub VectorFill(sourceVector As Variant, Index As Long, Value As Variant)
'Fills a 1d array at a specified index and increases the UBound if needed (by doubling it).
'Trim unneeded space separately with ReDim Preserve.

    Const resizeMultiplier As Integer = 2
    
    'With this statement, an out of bounds error will trigger a resize
    On Error GoTo ErrorHandling
    sourceVector(Index) = Value
    Exit Sub
    
'ErrorHandling used to resize array in chunks
ErrorHandling:
    newBound = (UBound(sourceVector) + 1) * resizeMultiplier '+1 helps with initial 0
    ReDim Preserve sourceVector(newBound)
    Resume 'New space is available, so go back and try again

End Sub 

The above function can be used as follows:

Sub DemoVectorFill()

    Dim dirContents() As Variant, i As Long
    ReDim dirContents(0)
    dirContent = Dir("")
    Do While dirContent <> ""
        VectorFill dirContents, i, dirContent
        dirContent = Dir
        i = i + 1
    Loop
    ReDim Preserve dirContents(i - 1)

End Sub

With this approach, you don't have to check capacity at each iteration, and leverage the error when it occurs. On my tests it is not faster than making that check (it’s a hair slower), but as sizes increase either way is much faster than ReDim Preserve at each iteration.

---

^{*If you try to copy and paste the BetterArray code into a class module, this will not entirely work. Class modules have some code that is hidden in the VBA editor, and won't be copied via copy paste. The two options are to drag the .cls file into the Project pane or use File --> Import File.}

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.

Answer 3

I see many (all) posts above relying on LBound/UBound calls upon yet potentially uninitialized VBA dynamic array, what causes application's inevitable death ...

Erratic code:

Dim x As Long Dim arr1() As SomeType ... x = UBound(arr1) 'crashes

Correct code:

Dim x As Long Dim arr1() As SomeType ... ReDim Preserve arr1(0 To 0) ... x = UBound(arr1)

... i.e. any code where Dim arr1() is followed immediatelly by LBound(arr1)/UBound(arr1) calls without ReDim arr1(...) in between, crashes. The roundabout is to employ an On Error Resume Next and check the Err.Number right after the LBound(arr1)/UBound(arr1) call - it should be 0 if the array is initialized, otherwise non-zero. As there is some VBA built-in misbehavior, the further check of array's limits is needed. Detailed explanation may everybody read at Chip Pearson's website (which should be celebrated as a Mankind Treasure Of VBA Wisdom ...)

Heh, that's my first post, believe it is legible.

Answer 4

In addition to Cody's useful comments it is worth noting that at times you won't know how big your array should be. The two options in this situation are

1. Creating an array big enough to handle anything you think will be thrown at it
2. Sensible use of Redim Preserve

The code below provides an example of a routine that will dimension myArray in line with the lngSize variable, then add additional elements (equal to the initial array size) by use of a Mod test whenever the upper bound is about to be exceeded

Option Base 1

Sub ArraySample()
    Dim myArray() As String
    Dim lngCnt As Long
    Dim lngSize As Long

    lngSize = 10
    ReDim myArray(1 To lngSize)

    For lngCnt = 1 To lngSize*5
        If lngCnt Mod lngSize = 0 Then ReDim Preserve myArray(1 To UBound(myArray) + lngSize)
        myArray(lngCnt) = "I am record number " & lngCnt
    Next
End Sub

Answer 5

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)

Answer 6

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