VBA — Add Items to an Array

Arrays in VBA are a little finicky and require a different procedure than programming langauges like C or Python. In VBA you cannot append an item to an array. Instead you must re-dimension the array to be one index larger than the previous.

To initialize the array:

Dim arr() as String

To add an item to the array:

Redim preserve arr(0)
arr(0) = string1

Redim preserve arr(1)
arr(1) = string2

or

Redim preserve arr(Ubound(arr) + 1)
arr(Ubound(arr)) = string2

You cannot check the Ubound (upper bounds) of an array if it does not have a dimension.

It is also important to note that you cannot initialize an array with a dimension and then change that dimension.

Dim arr(1) As String
arr(0) = string1
arr(1) = string2

Redim preserve arr(2)
// the line above would fail as the array already has a dimension

When looping:

For i = 0 to 10

   Redim preserve arr(i)
   arr(i) = i

Next i

Check out how to Sort Arrays