Dynamic Arrays in VBA

Sometimes you do not know the number of elements in an array before runtime.

In most other programming languages, working with arrays and adding and deleting elements is easy. In VBA, this is not the case.

However, there is a simple solution to dynamically populate arrays, with the ReDim statement.

How To Populate Dynamic Arrays

First of all, arrays can be static and dynamic in VBA.

A static array is created like this:

Dim myArray(0 To 2)

In static arrays, you cannot change the dimension after it is created. If you do, you will get an error telling you that the array has already been dimensioned. A dimension that goes from 0 to 2, means that the array can hold three values (from 0 to 2).

To instead create a dynamic array, just leave out the dimension like this:

Dim myArray()

After creating a dynamic array, you can always re-dimension the array later with the ReDim statement:

ReDim myArray(0 To 2)

You are actually required to re-dimension the array before using it, if you are working with dynamic arrays. The ReDim statement above will re-dimension the array. Please note that this will delete all the current values in the array.

To keep the values, instead use the ReDim Preserve statement:

ReDim Preserve myArray(0 To 2)

The following procedure displays how the ReDim and ReDim Preserve statements works.

Sub dynamicArray()
'Example of dynamic arrays and re-dimensioning
'From QDAtaLab.com

    'Create a dynamic array
    Dim myArray() As String

    'Dimension the dynamic array before it can be used
    ReDim myArray(0 To 0)

    'Set the value of the first instance in the array to "Hello World"
    myArray(0) = "Hello World"

    'Print the value of the first instance (0) in the array
    Debug.Print myArray(0) 'This line will output "Hello World" in the immediate console
    Debug.Print "----------"

    'Re-dimension the array to hold up to 3 values
    ReDim myArray(0 To 2)

    'This will output a blank value in the console, as the values were not preserved and no value has been assigned to the first instance (0)
    Debug.Print myArray(0)
    Debug.Print "----------"

    'Assign new values to myArray()
    myArray(0) = "First"
    myArray(1) = "Second"
    myArray(2) = "Third"

    'Re-dimension the array to hold up to 4 values, but this time we will preserve the current values
    ReDim Preserve myArray(0 To 3)

    myArray(3) = "Fourth"

    Debug.Print myArray(0) 'This will output "First" in the console, as the values were preserved.
    Debug.Print "----------"

    'Print out all values from the array in the console one by one. We do this with a For Each loop
    Dim element As Variant

    For Each element In myArray
        Debug.Print element
    Next element

    Debug.Print "----------"

End Sub

Just copy the code above to a VBA module, run the sub routine, and watch the immediate window. Going over the comments & code while watching the result, should make the example self-explanatory.

Please note that ReDim and especially ReDim Preserve can hurt the performance of your code. Avoid using in large loops. Instead it is better to ReDim the array, before the loop if possible.

Dynamically Populate Dynamic Arrays

A simple way to dynamically populate an array is to use a simple integer variable to keep track of the number of items in the array. The principle is to use a variable as a counter, and every time an element is put into the array, the counter is increased. The array can then be re-dimensioned with the new value the next time it needs to be used.

The following procedure displays how dynamic arrays can be populated and re-dimensioned “on the fly”.

Sub dynamicArrayPopulate()
'Example of simple method to dynamically populate dynamic arrays
'From QDataLab.com

    'Create a dynamic array called "myArray" and variable "i" that works as out counter
    Dim myArray() As String
    Dim i As Integer
    
    'Prepare the array to be used by re-dimensioning it
    ReDim myArray(0 To 0)
    
    'Set our counter to 0
    i = 0
    
    'Random if statement to demonstrate the usage, in this case the statement is true
    If 1 = 1 Then
        'Re-dimension the array while preserving values
        ReDim Preserve myArray(i)
        'Assign a value to myArray
        myArray(i) = "Hello"
        'Increate the counter by one, so it is ready for the next re-dimensioning
        i = i + 1
    End If
    
    'Another random if statement which is false
    If 1 = 2 Then
        ReDim Preserve myArray(i)
        myArray(i) = "Data"
        i = i + 1
    End If
    
    'Another random if statement that returns true
    If 2 = 2 Then
        ReDim Preserve myArray(i)
        myArray(i) = "World"
        i = i + 1
    End If
    
    'lastly, we print all elements in the array to the console with a for loop
    Dim elem As Variant
    
    For Each elem In myArray
        Debug.Print elem
    Next elem
    
End Sub

Again, the code has been well commented, so it should be self-explanatory what is going on.

If you have any questions, then feel free to leave a comment.