Excel VBA –ReDim Preserve

ReDim statement is used to resize a dynamic array. When ReDim Statement is used, all the values assigned prior to resize the array are erased.

By adding the Preserve keyword after the ReDim, the data in the array prior to resize can be kept.

We copy and paste the following macro into Excel. The Output of message box is 2. After using ReDim Preserve statement to resize the array, the Output of message box is 2. The value in the array is preserved. Then, after using ReDim statement to resize the array, the Output of message box is blank. The value in the array is erased.

 Sub test()

    Dim PmtStream() As Variant

    TotalCount = 10

    '-----ReDim PmtStream to the size which is needed.

    ReDim PmtStream(1 To TotalCount)

    '----we read the date to PmtStream.

    For i = 1 To 10

    PmtStream(i) = i * 2

    Next i    

     MsgBox PmtStream(1)

     ReDim Preserve PmtStream(1 To 20)

     MsgBox PmtStream(1)

     '-----After ReDim Preserve comment, the output is the same as the previous one

     ReDim PmtStream(1 To 10)

     MsgBox PmtStream(1)

     '-----After ReDim comment, the output is blank.

End Sub

 However, unlike ReDim statement, ReDim Preserve can only resize the last array dimension and the number of the dimensions cannot be changed.

For example,

Sub test()

    Dim PmtStream() As Integer

     ReDim Preserve PmtStream(1 To 10, 1 To 4)   ‘OK

     ReDim PmtStream(1 To 15, 1 To 10, 1 To 20)  ‘OK

     ReDim PmtStream(1 To 10, 1 To 10)           ‘OK

ReDim Preserve PmtStream(1 To 10, 1 To 4)   ‘OK

     ReDim Preserve PmtStream(1 To 15, 1 To 4)   ‘Run Time error

ReDim Preserve PmtStream(1 To 15)           ‘Run Time error

End Sub

© Copyright Exceltipsandkeys All Rights Reserved.