Dynamic Arrays in VBA – ReDim and Preserve

In one of the earlier articles, we learned how to use one-dimensional and multidimensional arrays. Today we will learn about another important concept of using Dynamic Arrays. We will learn to use 1D dynamic arrays and why we need this concept in VBA.

So let us begin learning.

What are Dynamic Arrays?

An Array is a kind of variable that acts like a mini database. We can store a number of homogenous values in one array. There are mainly two types of arrays.

• Static Arrays
• Dynamic Arrays

We cannot resize the maximum limit of the number of elements allowed in a static array. While in Dynamic Arrays, we can always resize the array based on our requirement at that moment. Dynamic arrays can be one-dimensional as well as multidimensional.

Syntax – ReDim Statement in VBA

When we want to use a Dynamic array, we would first declare an array without mentioning the array limit. Next, we would use the ReDim Statement to add size to the array. We can also re-dimension the array using the ReDim statement anywhere in our code.

'syntax to declare a dynamic array in VBA
Dim <arr_name>() As <data_type>
ReDim <arr_name>(size)

• First, we would declare the array specifying its name and the data type of the elements it can store.
• We would use an empty pair of parenthesis followed by the name of the array to not mention the size of the array and make it dynamic.
• Next, we would use the ReDim statement to specify the size of the dynamic array. Use the ReDim statement followed by the name of the array and its size in the parenthesis.

Note that when we use the ReDim statement, it erases the value of previously stored elements in the array. We have discussed this issue and how to resolve the issue in the next section of this article.

Examples to Learn Dynamic Arrays

In this section of the blog, we would learn to implement the concept of Dynamic Arrays along with learning new concepts.

Example 1 – Simple Example for using 1D Dynamic Array

In this example, we will print the numerical values in the worksheet. We will store numerical values in an integer array and then print the array elements in the worksheet range.

Use the following VBA Sub Procedure to implement.

Sub ex1DDynamicArray()
'declaring a dynamic integer type array
Dim myarr() As Integer
'using ReDim to define size of dynamic array
ReDim myarr(2) As Integer
'initializing the elements of dynamic array
myarr(0) = 10
myarr(1) = 20
myarr(2) = 30
'printing the elements of array in first three cells of first column A
'UBound function returns the maximum upper limit of the specified array and dimension number
'UBound(array_name,dimension_number)
For i = 0 To UBound(myarr, 1)
'Cells(row,column) refer to the cell formed by specified row and column number
'Column number is fixed. and Row number is i+1, from 1 to 3
Cells(i + 1, 1).Value = myarr(i)
Next i
End Sub

To better understand this code, you must read the comments within this Sub Procedure. Here we have used the UBound function to get the upper bound of myarr(2) which is 2.

When you run this Sub Procedure, it would print the values in cells A1, A2, and A3 in the active worksheet.

Example 2 – Print the Last Cell value of a Column

In this example, we have column A having data in consecutive cells. We would read and save the data from this column in the array until a blank cell is encountered in column A. Thereafter, we would print the contents of the last filled cell of column A.

Let us say we have data in the range A1:A5.

We would first count and store the total number of contiguous filled cells (5) starting from cell A1 in a variable named cell_count ( the End property of Range).

Thereafter, we declare a dynamic array of size equal to cell_count-1 so that it could store the elements having indexes from 0 to cell_count-1.

At last, we print the value of the array element having index cell_count-1. This is the value of the last cell in the contiguous range of cells in column A.

Use the following Sub Procedure to implement the work.

Sub exPrintLastCell()

'code to store the values in contiguous range form worksheet into an array
'print the contents of last cell of column A
'declare a variable to store total number of filled cells starting from A1
Dim cell_count As Integer
Dim myarr() As Integer

'a1 is starting cell
'Range("a1").End(xlDown) gives address of last filled cell
cell_count = Range("a1", Range("a1").End(xlDown)).Count

'adding array limit
ReDim myarr(cell_count - 1)
'storing the worksheet cell values into the array
For i = 0 To cell_count - 1
myarr(i) = Cells(i + 1, 1)
Next i
'printing the content of last element of array
MsgBox "last element of array is:" & myarr(cell_count - 1)

End Sub

Now that you run this Sub procedure, it would print the contents of the last filled cell of column A starting from A1.

This is the output for different situations.

Preserving Values – ReDim

When we use the ReDim keyword, it destroys the previous values stored in the dynamic array. for example, we have this Sub Procedure to prove it.

Sub exReDimDestroysArray()
Dim arr() As Integer
ReDim arr(2)
'storing contents in array
arr(0) = 1
arr(1) = 2
arr(2) = 3
'using ReDim to change the array size
ReDim arr(3)
'contents deleted stored before
MsgBox arr(0) & arr(1) & arr(2)
End Sub

We can use the ReDim followed by the Preserve keyword to preserve the contents of the dynamic array when we resize it.

Sub exReDimWithPreserve()
Dim arr() As Integer
ReDim arr(2)
'storing contents in array
arr(0) = 1
arr(1) = 2
arr(2) = 3
'using ReDim with Preserve to change the array size and preserve the stored values
ReDim Preserve arr(3)
'contents not deleted
MsgBox arr(0) & arr(1) & arr(2)
End Sub

This brings us to an end.

Thank you for reading.