Redim Preserve

ReDim Preserve

Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading…

ReDim Preserve is the way to make a dynamic array and maintain the values currently stored in the array by using the ReDim Preserve. If you want to resize your array while remembering all the elements in the array, you must use the Preserve keyword. However, there are some limitations:

1. Redim for Dynamic Array only
2. ReDim Preserve Last Dimension only
3. Data type can not be changed on Redim

1. ReDim for Dynamic Array only

Dim MyArray() as integer ‘ Declare as Dynamic Array 
ReDim MyArray(3)

Declare as Static Array will cause an error “Array already dimensioned” 

Dim MyArray(2) as integer ‘ Declare as Static Array 
ReDim MyArray(3)

2. ReDim Preserve Last Dimension only

Dim MyArray() as string 
ReDim MyArray(0, 2)

ReDim Preserve MyArray(0, 3) ‘Redim the last element of the array.

Redim the first element of the array will cause an error “Subscription out of range” 

Dim MyArray() as string 
ReDim MyArray(0, 2)

 ReDim Preserve MyArray(1, 2) ‘Redim the first element of the array 

3. Data type can not be changed on Redim

Redim the first element of the array will cause an error “Can’t change data types of array elements” 

Dim MyArray() as string 
ReDim MyArray(2) as integer ‘Change data type to integer

ReDim Preserve MyArray(0, 3) 

Example

Redim with Preserve keyword keeps all previous arrays remain intact

Sub ReDimWithPreserve()
        Dim MyArray() As String
        ReDim MyArray(1)
       MyArray(0) = “zero”
       MyArray(1) = “one”
       ReDim Preserve MyArray(2) ‘With Preserve keyword
       MyArray(2) = “two”
       Debug.Print “MyArray(0):” & MyArray(0) & vbCrLf & _
                             “MyArray(1):” & MyArray(1) & vbCrLf & _
                             “MyArray(2):” & MyArray(2)
End Sub  

Output:

MyArray(0):zero
MyArray(1):one
MyArray(2):two

Redim With No Preserve keyword, all previous arrays are lost

Sub ReDimNoPreserve()
        Dim MyArray() As String
        ReDim MyArray(1)
       MyArray(0) = “zero”
       MyArray(1) = “one”
       ReDim MyArray(2) ‘With no Preserve keyword
       MyArray(2) = “two”
       Debug.Print “MyArray(0):” & MyArray(0) & vbCrLf & _
                             “MyArray(1):” & MyArray(1) & vbCrLf & _
                             “MyArray(2):” & MyArray(2)
End Sub

Output:

MyArray(0):
MyArray(1):
MyArray(2):two