[VBA]VBAの実行速度を劇的にアップさせる。

最近VBAを使う機会があったので、そのときに使った実行速度向上手法を紹介します。

通常、セルの値を設定/取得する場合、以下のようなコードになります。

Sheets(1).Cells(1, 1).Value = "A"
tmp = Sheets(1).Cells(1, 1).Value

特定の列のデータを順に取得して…といった場合は、ループを使って以下のようなコードになると思います。

For i = 1 To 10
    tmp = Sheets(1).Cells(i, 1).Value
    
    'Do something
Next

この場合、ループ回数が10回なのでそれ程気になりませんが、100、200、…とループ回数が増えるに従って非常に時間が掛かるようになります。

当然、Application.ScreenUpdating = Falseにしたりしますが、それでも処理が重たい場合があります。
そのような場合、以下のようにするとループを使う箇所の処理が、ちょっぱや！になります。

'ループで処理する値を取得/設定する範囲のRangeを取得
With Sheets(1)
    Set targetRange = .Range(.Cells(1, 1), .Cells(10, 1))
End With

'Rangeのデータを配列で取得
Dim values()
values = targetRange.Value

'配列に取得したデータで処理を行う
For i = 1 To 10
 tmp = values(i, 1)

 'Do something
Next

'値を設定するときは配列をRangeに一括設定
targetRange.Value = values

たったこれだけですが、実行速度は劇的に向上します。
試しに適当な処理で処理時間を計測してみました。

以下のコードで計測した結果…

Const LoopCount = 10000

Private Sub CommandButton1_Click()
    Application.Cursor = xlWait
    Application.ScreenUpdating = False
    
    'ループ Ver
    t = Timer
    For i = 1 To LoopCount
        tmp = Sheets(1).Cells(i, 1).Value
        Sheets(1).Cells(i, 2).Value = tmp * 10
    Next
    Debug.Print ("ループで1セルずつ取得/設定 : " & (Timer - t))
    
    'Range Ver
    t = Timer
    With Sheets(1)
        Set srcRange = .Range(.Cells(1, 1), .Cells(LoopCount, 1))
        Set dstRange = .Range(.Cells(1, 3), .Cells(LoopCount, 1))
    End With
    
    Dim values()
    values = srcRange.Value
    For i = 1 To LoopCount
        values(i, 1) = values(i, 1) * 10
    Next
    dstRange.Value = values
    
    Debug.Print ("Rangeで一括取得/設定 : " & (Timer - t))
    
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
End Sub

Debug.Print の出力結果は、

ループで1セルずつ取得/設定 : 0.4609375
Rangeで一括取得/設定 : 0.015625

なんと約30倍もの実行時間差が出ました！
実際、今までかなり時間が掛かっていた箇所を上記の方法で修正したところ、驚くほど速くなりました。
VBAで「遅いなぁ…」「もう少し速くならないかぁ…」とお悩みのあなた、この方法をぜひ試してみてください！