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(50) 

This is an expensive operation and should be used sparingly.


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


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