ReDim Preserve


Preserving Values

ReDim will re-initialize the array and destroy any data in it unless you use the Preserve keyword.
Preserve copies the elements from the old array to the new array.
It is possible to resize an array without losing the existing values by using the Preserve keyword.
When you use Preserve you can only change the size of the upper bound (not the lower bound).

ReDim Preserve aCellValues(5) 

This is an expensive operation and should be used sparingly.


Partial Clearing

You can use the ReDim Preserve statement to keep only some values.

Dim myArray() As String 
ReDim myArray(1 to 4)
Debug.Print UBound(myArray) '4
myArray(1) = "one"
myArray(2) = "two"
myArray(3) = "three"
myArray(4) = "four"

' removes the last 2 items, keeping the first 2 items
ReDim Preserve myArray(1 to 2)

Debug.Print myArray(1)
Debug.Print myArray(2)

Never In A Loop

The ReDim Preserve statement creates a new array - and the elements of the old array get copied into the new one.
If you are running on low memory this can cause virtual memory to be used which slows down performance.

Public Sub RunThis() 
Dim starttime As Single
Dim timeelapsed As Single

    starttime = VBA.Timer
    Call Slower
' Call Faster
    timeelapsed = VBA.Timer - starttime
    Call MsgBox(timeelapsed & " seconds")
End Sub

Public Sub Slower()
    Dim myText() As String
    Dim lcount As Long
    For lcount = 0 To 5000000
        ReDim Preserve myText(lcount)
        myText(lcount) = lcount
    Next lcount
End Sub

Public Sub Faster()
    Dim myText() As String
    Dim lcount As Long
    ReDim Preserve myText(5000000)
    For lcount = 0 To 5000000
        myText(lcount) = lcount
    Next lcount
End Sub


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext