Dynamic Array with ReDim Preserve VBA

Introduction to ReDim and ReDim Preserve

You can make a dynamic array while maintaining the values currently stored in the array by using the ReDim Preserve VBA statement. This tutorial will introduce you to dynamic arrays and it will show you how to make dynamic arrays with 2D and multidimensional arrays - which is harder than you’d think!

1-Dimensional ReDim Preserve Example
Dynamic Array Basics
ReDim Limitations
ReDim Preserve Multidimensional Array Example

Example - ReDim Preserve

1D ReDim Preserve VBA Example

Sub ReDimPreserveDemo()
Dim MyArray() As String

ReDim MyArray(1)
MyArray(0) = "zero"
MyArray(1) = "one"
ReDim Preserve MyArray(2)
MyArray(2) = "two"
MsgBox MyArray(0) & vbNewLine & MyArray(1) & vbNewLine & MyArray(2)
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

When you run this macro, the values stored in MyArray(0) and MyArray(1) are retained:

Array Values are Preserved

If you delete the Preserve keyword and try to run the same macro, the values stored in MyArray(0) and MyArray(1) are lost! Check it out:

Same Macro without the Preserve Keyword

Tutorial - ReDim and ReDim Preserve

Dynamic Array Basics

To make an array a dynamic array, you declare it with a regular Dim statement, but you don’t give it a size. All that means is you give your variable name with an empty set of parentheses afterward, like this:

Dim MyArray() As String 'this is a dynamic array

If you were to put a number in the parantheses, it will be a static array.

To give your array a new size, you would use a ReDim statement, instead of another Dim statement.

ReDim MyArray(1 to 5)

This will clear out any values you previously stored in your array, MyArray. We demonstrated this with the second message box in our earlier example.

If you want to resize your array while remembering all the elements in the array, you must use the Preserve keyword.

ReDim Preserve MyArray(1 to 5)

We demonstrated this in the first message box of our 1-D example

ReDim Limitations

There are a several rules you must follow when ReDimming a dynamic array, but I’ll present the 3 major (read: 3 most annoying) limitations of the ReDim statement.

Limitation 1: Can only ReDim Preserve Last Dimension

The ReDim statement is used to resize an array, but it clears ALL the values stored in your array unless you use the optional Preserve keyword with ReDim, like we did in the example above. The primary limitation of the ReDim Preserve statement is it can only change the upper bound of the last dimension of a multidimensional array, including simple 2D arrays in VBA. You don’t have to worry about this if you strictly use 1D arrays.

Sub RedimError()
Dim MyArray() As Integer
ReDim MyArray(1, 3)
ReDim Preserve MyArray(2, 3) 'This will cause an error
End Sub

Running this macro will cause an error because you tried preserving the array while re-dimensionalizing the FIRST dimension; you tried changing it from a 1 to a 2. Unless you get creative (like I’ll show you below), you can only redim the last dimension of an array.

Redim Preserve Multidimensional Array Runtime Error

Limitation 2: Cannot ReDim Static Arrays

Another limitation of ReDim - not exclusive to the Preserve keyword - is that you can’t resize an array that was statically defined. In other words, if you Dim a variable with specific dimensions, you’ll get an “Array Already Dimensioned” error when you try to ReDim it. Take the following example, for instance.

Sub RedimError2()
Dim MyArray(2) As Integer
ReDim MyArray(3) 'This will cause an error
End Sub

Because you defined your array at the top with a value of 2, you’ll get an error when you try to change the size with the ReDim statement:

Already Dimensioned VBA Redim Static Array

Limitation 3: Cannot use ReDim to Change Data Types

A third limitation of the ReDim statement is that you can’t use it to change your VBA data type. Doing so will create another error.

Sub RedimError3()
Dim MyArray() As Integer
ReDim MyArray(2) As Double 'This will cause an error
End Sub

This example tries to change the data type from an Integer to a Double. When you run this macro, you’ll get a compilation error declaring you can’t change data types of array elements:

ReDim Cannot Change VBA Array Data Type

ReDim Preserve Multidimensional Array

Let’s be real. This is what you came for. If you read ReDim Limitation 1, you know the Preserve keyword only works if you’re redimming the last dimension of an array. You can ReDim the last dimension easily, like we do in this example:

ReDim the Last Dimension of 2D array

Sub ReDimPreserve2D()
Dim MyArray() As String
ReDim MyArray(1, 3)
'put your code to populate your array here
ReDim Preserve MyArray(1, 5)
'you can put more code here
End Sub

To drive the message home, take a look at a real-life example. I’ll show you a macro that stores the array index positions in the 2D array of size (1,3). After it does this, the Array is resized to (1,5) and I’ll prove to you that the values previously stored in the array are retained.

Sub ReDimPreserve2D_real()
Dim MyArray() As String
ReDim MyArray(1, 3)
'put your code to populate your array here
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
    For j = LBound(MyArray, 2) To UBound(MyArray, 2)
        MyArray(i, j) = i & "," & j
    Next j
Next i
ReDim Preserve MyArray(1, 5)
Stop
End Sub

Here’s the proof

ReDim Preserve 2D Array Last Dimension

The elements that were added show up as empty strings. That’s easy. The challenging part is using ReDim Preserve when you want to ReDim the first dimension of your 2D array.

ReDim Any Dimension of 2D array

Because you can only ReDim Preserve the last dimension of an array, you have to get creative if you want to resize the first dimension of an array while preserving the values. I’ll demonstrate one way to do this with a 2D array.

A lot of people like to use the Tranpose function to fix the “Subscript out of range” run-time error message I showed you in ReDim Limitation 1, but I prefer to use a custom user-defined function, called ReDimPreserve. I like this function because it lets me re-dimensionalize either the first dimension, the last dimension, or BOTH dimensions of my 2D array at the same time. That’s pretty awesome!

Sub ReDimPreserve2D_AnyDimension()
Dim MyArray() As Variant
ReDim MyArray(1, 3)
'put your code to populate your array here
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
    For j = LBound(MyArray, 2) To UBound(MyArray, 2)
        MyArray(i, j) = i & "," & j
    Next j
Next i
MyArray = ReDimPreserve(MyArray, 2, 4)
End Sub

Private Function ReDimPreserve(MyArray As Variant, nNewFirstUBound As Long, nNewLastUBound As Long) As Variant
    Dim i, j As Long
    Dim nOldFirstUBound, nOldLastUBound, nOldFirstLBound, nOldLastLBound As Long
    Dim TempArray() As Variant 'Change this to "String" or any other data type if want it to work for arrays other than Variants.    MsgBox UCase(TypeName(MyArray))
'---------------------------------------------------------------
'COMMENT THIS BLOCK OUT IF YOU CHANGE THE DATA TYPE OF TempArray
    If InStr(1, UCase(TypeName(MyArray)), "VARIANT") = 0 Then
        MsgBox "This function only works if your array is a Variant Data Type." & vbNewLine & _
               "You have two choice:" & vbNewLine & _
               " 1) Change your array to a Variant and try again." & vbNewLine & _
               " 2) Change the DataType of TempArray to match your array and comment the top block out of the function ReDimPreserve" _
                , vbCritical, "Invalid Array Data Type"
        End
    End If
'---------------------------------------------------------------
    ReDimPreserve = False
    'check if its in array first
    If Not IsArray(MyArray) Then MsgBox "You didn't pass the function an array.", vbCritical, "No Array Detected": End
    
    'get old lBound/uBound
    nOldFirstUBound = UBound(MyArray, 1): nOldLastUBound = UBound(MyArray, 2)
    nOldFirstLBound = LBound(MyArray, 1): nOldLastLBound = LBound(MyArray, 2)
    'create new array
    ReDim TempArray(nOldFirstLBound To nNewFirstUBound, nOldLastLBound To nNewLastUBound)
    'loop through first
    For i = LBound(MyArray, 1) To nNewFirstUBound
        For j = LBound(MyArray, 2) To nNewLastUBound
            'if its in range, then append to new array the same way
            If nOldFirstUBound >= i And nOldLastUBound >= j Then
                TempArray(i, j) = MyArray(i, j)
            End If
        Next
    Next
    'return the array redimmed
    If IsArray(TempArray) Then ReDimPreserve = TempArray
End Function

Note: For this function to work, your array MUST be defined as a Variant. Alternatively, you can modify the TempArray variable in the ReDimPreserve function to declare the variables as whatever data type you want, like a string. You’ll get a pretty warning message if your array isn’t a variant data type.

I can’t take full credit for this solution. I found a similar function a while back and modified it for my needs. Why reinvent the wheel!

This code example has two different procedures. The second one (the ReDimPreserve function) is the one you most likely want to copy and paste in your own macro. The first procedure (the ReDimPreserve2D_AnyDimension subroutine) simply demonstrates how to call the ReDimPreserve function to change the size of an array.

To use the custom ReDimPreserve function, you pass it three arguments:

1. Your array
2. The new size you want your first dimension to be
3. The new size you want your last dimension to be

That’s what I did in my ReDimPreserve2D_AnyDimension subroutine. If you don’t want to change the size of an element, simply pass it the same value of your previous array!

Each element will be preserved in your newly ReDimmed array. Check it out:

ReDim Preserve any dimension of a 2D Array

You can see how I changed the size of my array from (1,3) to (2,4). The new array elements show up as “Empty” in the resized array.

If you’d like to ReDim Preserve a multidimensional array larger than two-dimensions, your best bet is to construct your array in such a way that only the number of elements in the last dimension will need to be preserved. Otherwise, it can get pretty complicated.

We introduced some pretty advanced redim preserve macros in this tutorial. If you want to become a VBA array expert, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.

If you haven’t already done so, join our VBA Insiders using the form below. After that, share this article on social media and follow me on Twitter for even more great VBA content.