VBA Dynamic Array (Redim & Redim Preserve)

This tutorial will demonstrate how to use dynamic arrays (redim and redim preserve) in VBA.

Dynamic Array

Dynamic Arrays are arrays that can change sizes (as opposed to static arrays, which are static).

To declare a dynamic array, you declare the array, but omit the array size:

Dim strNames() As String

Then, before you can assign values to your array, you must use the ReDim Statement to set the array to your desired size:

ReDim strNames(1 to 3)

Now, anytime you want to change your array size, simply use ReDim (or ReDim Preserve as we’ll learn about below).

Dynamic Variant Arrays

Note: Variant Arrays are a little different. With variant arrays, you don’t need to set the array size with ReDim before assigning values.

Sub TestArray()
'declare the variable
    Dim varNames() As Variant
'populate the array
    varNames() = Array("Fred", "Wilma", "Barney", "Betty")
'return the values
    MsgBox Join(varNames, ",")
End Sub

Redim vs. Redim Preserve

The ReDim statement resizes an array, clearing all existing values.

The ReDim Preserve statement resizes an array, keeping (“preserving”) all existing values.

Using ReDim

In practice, resizing an array with ReDim looks like this:

Sub TestReDim()
'declare the string array
   Dim strNames() As String
'resize the string array to be able to hold 3 values
   ReDim strNames(1 to 3)
'populate the array with 3 names
   strNames(1) = "Mel"
   strNames(2) = "Steve"
   strNames(3) = "Bob"
'show the result in the immediate window
   Debug.Print Join(strNames, vbCrLf)
End Sub

Using ReDim Preserve

In this example, we will use ReDim to set the initial dynamic array and then ReDim Preserve to resize the array, keeping the original values:

Sub TestReDim()
'declare the string array
   Dim strNames() As String
'resize the string array to be able to hold 3 values
   ReDim strNames(1 to 3)
'populate the array
   strNames(1) = "Mel"
   strNames(2) = "Steve"
   strNames(3) = "Bob"
'show the result in the immediate window
   Debug.Print Join(strNames, vbCrLf)
'redim but preseve the data
   ReDim Preserve strNames(1 to 4)
   strNames(4) = "Fred"
'show the result in the immediate window
   Debug.Print Join(strNames, vbCrLf)
End Sub

If you do not use the PRESERVE statement, you would lose the data that had been in the array previously.

In the  immediate window above, the array populated Mel, Steve and Bob.  When it was re-declared, it remove those values and instead returned 3 blank values and then the value ‘Fred’.   This is due to the PRESERVE statement being omitted.