Introduction to ReDim and ReDim Preserve
1-Dimensional ReDim Preserve Example
Dynamic Array Basics
ReDim Limitations
ReDim Preserve Multidimensional Array Example
Comments


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

wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


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

Array Values are Preserved
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
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
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:

Array Already Dimensioned VBA Redim Static Array
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 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 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()
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
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

wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


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
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.


Comments

If you haven’t already done so, please subscribe to my email list!

Share this article on social media, follow me on Google+ and Twitter for even more great VBA content, and leave a comment below if you have your own tips, tricks or questions.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.