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
10 000 iterations:
100 000 iterations:
300 000 iterations:
Rick Rothstein is right, it is a good idea to avoid using it too often.