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!
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.
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,
If you want to resize your array while remembering all the elements in the array, you must use the
ReDim Preserve MyArray(1 to 5)
We demonstrated this in the first message box of our 1-D example
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
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
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
To use the custom
- Your array
- The new size you want your first dimension to be
- The new size you want your last dimension to be
That’s what I did in my
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.