Author: Oscar Cronquist Article last updated on August 20, 2012

Rick Rothstein commented:

Redim Preserve does not execute all that quickly, so it is usually a good idea to avoid using it too often.

That made me curious, how slow is Redim Preserve? I am comparing ReDim and ReDim Preserve.

I remember reading how Jimmy Pena created a test procedure timing split and InstrRev. With that in mind I created this procedue:

Sub TestRedimPreserve()
Dim starttimePreserve As Single
Dim endtimePreserve As Single
Dim starttimeRedim As Single
Dim endimeRedim As Single
Dim arr As Variant
ReDim arr(0)

starttimePreserve = Timer
For i = 0 To 300000
    arr(i) = Rnd()
    ReDim Preserve arr(i + 1)
Next i
endtimePreserve = Timer

starttimeRedim = Timer
ReDim arr(300000)
For i = 0 To 300000
    arr(i) = Rnd()
Next i
endtimeRedim = Timer

MsgBox "Redim Preserve: " & Format(endtimePreserve - starttimePreserve, "#.###") & vbCrLf & _
"Redim: " & Format(endtimeRedim - starttimeRedim, "#.###")

End Sub

Test results:

10 000 iterations:

100 000 iterations:

300 000 iterations:

Rick Rothstein is right, it is a good idea to avoid using it too often.