VBA Dynamic Array

HomeVBA TutorialArraysVBA Dynamic Array

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 a 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 value 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 have used the ReDim statement to re-define arrays elements using the last row number you have got from the previous step.
  5. Next, you have FOR LOOP (For Next) that loops through the range starting from cell A1, take values from there, and add them as elements to the array one by one.
  6. In the end, you have Debug.Print statement to printing 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.

There’s More

Below are some of the links which could be useful for you and if you want to learn more about VBA, check out this Excel VBA Tutorial – The Best Way to Learn Programming in Excel.

VBA Add New Value to the Array | VBA Clear Array | VBA Loop Through an Array | VBA Multi-Dimensional Array | VBA Range to an Array | VBA Search for a Value in an Array | VBA Sort Array | VBA Array Length (Size) | VBA Array with Strings | ISARRAY Function | ARRAY Function | VBA Arrays

If you are trying to prepare for an interview check out these VBA Interview Questions and Answers. And if you found this tutorial helpful, you can support us to create more tutorials like this.