【ExcelVBA】セルの更新処理を速度アップ

Excelのマクロを組んでいて、イラッとするのが、その実行速度。
セルに組み込むマクロでは、セルそのものを逐一参照、更新する必要があるので、やはり速度低下は否めない。
そこで、VBAで処理しようと考えるのですが、ここでもセルの更新処理の速度が非常に遅いことに、またイラッとする。(したw)
そこで、Variant型の変数にセルの内容をごそっと取り込んで、更新が終わったらごそっと書き戻す方法を使うことで、飛躍的に速度UPを行う事が可能となる。
【使い方】 ・取込方法 Dim vList As Variant vList = Sheets("Sheets1").Range(Cells(1,1),Cells(mRow,mCol)) ・更新方法 Sheets("Sheets1").Range(Cells(1,1),Cells(mRow,mCol)) = vList
以下は、シート:Sheet1のセル(1,1)~(mRow,mCol)の範囲に連番を埋めるサンプル。
' Test1()のbProcを変更して、速度の違いを試してください。 ' bProc = True : Variant型で実行 / False : セルを直接更新
Sub Test() Dim bProc As Boolean bProc = True ' bProc = False Call TestExec(bProc) end Sub Sub TestExec(ByVal bProc) Dim mRow As Long Dim mCol As Long mRow = 1000 mCol = 1000 With Sheets("Sheet1") Dim vList As Variant if bProc then vList = .Range(Cells(1,1),Cells(mRow,mCol)) EndIf Dim nCol As Long Dim nRow As Long For nRow = 1 to mRow For nCol = 1 to mCol if bProc then vList(nRow,nCol) = (nRow-1)*mCol + nCol Else .Cells(nRow,nCol) = (nRow-1)*mCol + nCol Endif Next nCol Next nRow if bProc then .Range(Cells(1,1),Cells(mRow,mCol)) = vList Endif End With End Sub
こんな感じ。