ReDim Preserve
Ratings: 
(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)
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
Output:
MyArray(0):
MyArray(1):
MyArray(2):two