以陣列變數寫入EXCEL 儲存格範圍可節省運算時間

這幾天重新翻閱《Excel VBA 徹底研究》這本書。
書中第11章提到，將資料逐一寫入儲存格範圍是非常消耗時間的，建議改用陣列變數將數值寫入儲存格範圍。

以下以傳統方式跑100*100的範圍，花了3.18秒。

Sub LoopFillRange()

    Dim cellsDown As Long, cellsAcross As Integer

    Dim CurrRow As Long, CurrCol As Integer

    Dim StartTime As Double

    Dim CurrVal As Long

    

    Cells.Clear

    cellsDown = Val(InputBox("How many cells down?"))

    cellsAcross = Val(InputBox("How many cells across?"))

    

    StartTime = Timer

    

    CurrVal = 1

    

    Application.ScreenUpdating = False

    

    For CurrRow = 1 To cellsDown

        For CurrCol = 1 To cellsAcross

            ActiveCell.Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal

            CurrVal = CurrVal + 1

        Next CurrCol

    Next CurrRow

    

    Application.ScreenUpdating = True

    

    MsgBox Format(Timer - StartTime, "00.00") & "秒"

End Sub

以下以陣列變數的模式，跑100*100的儲存格僅花了 0.05秒。
重點在於我用橘色框起來的 theRange.Value = tmpArray 這段敘述。

Sub ArrayFillRange()

    Dim cellsDown As Long, cellsAcross As Long

    Dim i As Long, j As Long

    Dim CurrRow As Long, CurrCol As Integer

    Dim StartTime As Double

    Dim CurrVal As Long

    Dim tmpArray() As Long

    Dim theRange As Range

    

    Cells.Clear

    cellsDown = Val(InputBox("How many cells down?"))

    cellsAcross = Val(InputBox("How many cells across?"))

    

    StartTime = Timer

    ReDim tmpArray(1 To cellsDown, 1 To cellsAcross)

    Set theRange = ActiveCell.Range(Cells(1, 1), Cells(cellsDown, cellsAcross))

    CurrVal = 0

    

    Application.ScreenUpdating = False

    

    For i = 1 To cellsDown

        For j = 1 To cellsAcross

            tmpArray(i, j) = CurrVal + 1

            CurrVal = CurrVal + 1

        Next j

    Next i

    

    theRange.Value = tmpArray

    Application.ScreenUpdating = True

    

    MsgBox Format(Timer - StartTime, "00.00") & "秒"

End Sub