VBA Dynamic Array (ReDim)

A dynamic array is something where you can resize the array and add more value to it while running the code. In this tutorial, we will explore the ways to write code where you can do both things resizing and adding more elements.

Create a Dynamic Array in VBA

1. First, declare an array with its name.
2. After that, the elements count left the parentheses empty.
3. Now, use the ReDim statement.
4. In the end, specify the count of elements you want to add to the array.

Dim myArray() As String
ReDim myArray(5)

Don’t forget to read this post from Joshua on the performance of fixed and dynamic arrays.

Dynamic Multidimensional Array

The example that we have used was a one-dimensional array and in the same way, you also use the ReDim statement to create dynamic multiple dimensional arrays.

Dim myArray() As String
ReDim myArray(5, 8)

Add New Item to a Dynamic Array in VBA

A dynamic array won’t make sense if you aren’t able to add a new item to it. In the example ahead, we will see how to add new elements once you redefine the count of items in an array.

'declaring array with no element.
'---------------------
Dim iNames() As String
'---------------------

'declaring variables to store counter _
'and elements from the range.
'----------------------
Dim iCount As Integer
Dim iElement As Integer
'----------------------

'get the last row number to decide the _
'number of elements for the array.
'------------------------------------
iCount = Range("A1").End(xlDown).Row
'------------------------------------

're-defining the elements for the array.
'-------------------
ReDim iNames(iCount)
'-------------------

'using a for loop to add elements in the array
'from the range starting from cell A1
'--------------------------------------------------
For iElement = 1 To iCount
    iNames(iElement - 1) = Cells(iElement, 1).Value
Next iElement
'--------------------------------------------------

'print all the elements from the
'to the immediate window
'--------------------
Debug.Print iNames(0)
Debug.Print iNames(1)
Debug.Print iNames(2)
'--------------------

Let’s understand this code step by step.

1. In the first step, you have declared an array with the name “myArray”.
2. After that, you have two variables to save counter values for the loop that you need to use further in the code to get values from the range and add those values as elements to the array.
3. Next, you have a line of code to get the last row from the range that further helps you to know the elements you need to add to the array.
4. In the fourth step, you used the ReDim statement to redefine array elements using the last row number you got from the previous step.
5. Next, you have FOR LOOP (For Next) that loops through the range starting from cell A1, takes values from there, and adds them as elements to the array one by one.
6. In the end, you have Debug.Print statement to print all the elements in the immediate window.

And when I add a new value to the range and rerun the code it automatically adds that item to the array and prints it to the immediate window.