When using the redim preserve statement, can you explain the difference between using it inside the Loop statement and using it outside?

Viewed 47

I unconsciously used the redim preserve statement inside the loop statement. I thought it was faster and more suitable. Compared to Scott Craner's method below, I realized that my method was very wrong. Please explain the problem of the working principle when using the redim preserve statement in the loop statement.

Code test results. There is a 71 times difference in speed.

Loop in:  17.77539 
Scott's Loop out:  0.25 

Code to be compared

Sub set2DArrayinLoop()
    Dim arr(1 To 1000000) As String
    Dim arrNew() As String
    Dim v As Variant
    Dim i As Long, n As Long, j As Integer
    Dim st, et
    
    For i = 1 To UBound(arr) - 3 Step 5
        arr(i) = "a"
        arr(i + 2) = "b"
        arr(i + 4) = "c"
    Next i
    
    st = Timer
    For Each v In arr
        If v <> "" Then
            n = n + 1
            ReDim Preserve arrNew(1 To 5, 1 To n)
            For j = 1 To 5
                arrNew(j, n) = v
            Next j
        End If
    Next v
    et = Timer
    Debug.Print "Loop in: ", et - st
    Stop
End Sub

Sub set2DArrayOutLoop()
    Dim arr(1 To 1000000) As String
    Dim newArr() As String
    Dim i As Long, j As Integer, n As Long
    Dim st, et

    
    For i = 1 To UBound(arr) - 3 Step 5
        arr(i) = "a"
        arr(i + 2) = "b"
        arr(i + 4) = "c"
    Next i
    
    st = Timer
    
    ReDim newArr(1 To 5, 1 To UBound(arr))
    
    Dim j As Long
    n = LBound(newArr)
    
    For i = LBound(arr) To UBound(arr)
        If arr(i) <> "" Then
            For j = 1 To 5
                newArr(j, n) = arr(i)
            Next j
            n = n + 1
        End If
    Next i
    
    ReDim Preserve newArr(1 To 5, LBound(newArr) To n - 1)
    
    et = Timer
    Debug.Print "Scott's Loop out: ", et - st
    Stop
End Sub
0 Answers
Related