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