Excel VBAの性能改善

はじめに

Excel VBAを使って、ちょっとした定型作業をツール化することがありますが、処理時間が非常に長くなるケースがあります。
VBAでExcelを操作するときにはいろいろとコツがあるので、それを紹介してみた
いと思います。

今回作成したサンプル

    Dim i As Long
    Dim j As Long

    For i = 0 To MAX_ROW
        For j = 1 To MAX_COL
            sheet.Cells(i + 5, j).FormulaR1C1 = "=ROW() *COLUMN()"
        Next
    Next

ひたすら、各セルに行＊列の計算式を代入するプログラムです。
MAX_ROW=500、MAX_COL＝100で私のPCで55秒程度かかりました。

改善方法

1．画面の描画を止める

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    Dim i As Long
    Dim j As Long

    For i = 0 To MAX_ROW
        For j = 1 To MAX_COL
            sheet.Cells(i + 5, j).FormulaR1C1 = "=ROW() *COLUMN()"
        Next
    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

画面描画と、自動計算を止めるようにしました。
結果、3.5秒と大幅短縮

２．セルへの代入に配列を使う

    Dim i As Long
    Dim j As Long

    Dim values(0 To MAX_ROW, 1 To MAX_COL)

    For i = 0 To MAX_ROW
        For j = 1 To MAX_COL
            values(i, j) = "=ROW() *COLUMN()"
        Next
    Next

    sheet.Range(sheet.Cells(5, 1), sheet.Cells(5 + MAX_ROW, MAX_COL)).FormulaR1C1 = values

なんと、0.3秒・・・

まとめ

画面描画しない改善については、前から知っていたのですが、配列で代入の改善効果には驚きました。
セルに対する操作の回数を減らすほうがより良い効果を生むようですね。今回は試しませんでしたが、セルの入力規制や背景色を変えるなども同様に、Rangeでの操作にしたほうが圧倒的に早くなります。