How slow is ReDim Preserve?
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.
Related posts:
Copy excel table filter criteria (vba)
Learn how to return values depending on udf deployment (vba)
Adjust stock chart axis automatically
Recommended categories
The way we used to minimize the amount of times ReDim Preserve needed to be called within a loop (way back in my compiled VB days) was to do the redimension is "chunks". That is, we guessed at the maximum amount of elements we though the array would have to handle and, if we were wrong, only then would we perform the ReDim Preserve increasing the array in size by the chuck amount. The nice thing about this method is that if we had no idea how many elements would finally be needed, it still reduced considerably the number of ReDim Preserves that would need to be performed. At the end of the procedure, we would perform one more ReDim Preserve in order to size the array to the actual number of elements it ended up containing. The following is my recollection of the code "structure" we used to use to do this (it has been a while since I needed to do this, and the untested pseudo-code below was made up off the top-of-my-head, but I am pretty sure it is correct)...
Dim Counter As Long, SomeArray() As String Const ChunkSize As Long = 1000 .... .... ReDim SomeArray(1 To ChunkSize) Do While Whatever If ConditionIsMet Then Counter = Counter + 1 If Counter > UBound(SomeArray) Then ReDim Preserve SomeArray(1 To UBound(SomeArray) + ChunkSize) End If SomeArray(Counter) = SomeStringValue End If Loop ReDim Preserve SomeArray(1 To Counter) .... ....[...] should mention that. I recently posted the following (modified slightly) to this blog article... How slow is ReDim Preserve? | Get Digital Help - Microsoft Excel resource The way we used to minimize the amount of times ReDim Preserve needed to be called within a loop [...]
If I am not mistaken, Redim Preserve creates another array of the specified (larger) size, then copies the existing array elements into the new array. That alone means it will be relatively slower. Also, I like the timeGetTime API for measuring execution time
There is a parallel conversion over in the MrExcel forum where I posted the message I posted here over there in response to a ReDim Preserve comment. That prompted one of the participants to test (among other things) the time difference between using ReDim Preserve repeatedly and using the "chunk" method I posted. Here is the results...
http://www.mrexcel.com/forum/excel-questions/655300-better-array-collection-objects.html#post3249459
Interesting! I also want to know why reading from a collection is so slow?
For those of you who want to know more about the timeGetTime API:
A Comparison of Early and Late Binding