マクロを速くする基本（画面更新の停止・計算の手動化・配列での一括処理）

マクロが遅くなる主な原因

数千行のデータを処理するマクロが何分もかかる場合、原因の多くは「セルへの読み書きの回数」です。VBA から Excel のセルに1回アクセスするたびに、画面の再描画や再計算が起きるため、ループの中でセルを1つずつ扱うと極端に遅くなります。

対策は大きく3つです。画面の更新を止めること、自動計算を一時的に止めること、そしてセルの値を配列にまとめて読み込み、配列の中で処理してから一括で書き戻すことです。

画面更新と自動計算を止める

処理の最初に止め、最後に必ず元に戻します。途中でエラーになって元に戻らないと、Excel が計算しない状態のまま残り、集計表の数字が更新されない事故につながります。そのため、エラー時も元に戻すように書きます。

Sub FastTemplate()
    On Error GoTo Finally
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' ここに本来の処理を書く

Finally:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "エラー：" & Err.Description
End Sub

配列で一括処理する

セル範囲の Value を Variant 型の変数に代入すると、範囲全体が2次元配列として一度に読み込まれます。配列の中で計算し、最後に範囲へ一度で書き戻せば、セルへのアクセスは2回だけになります。次の例は、C列（数量）×D列（単価）をE列（金額）に入れる処理です。

Sub CalcAmount()
    Dim ws As Worksheet, lastRow As Long, i As Long
    Dim data As Variant

    Set ws = ThisWorkbook.Worksheets("明細")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

    data = ws.Range("A2:E" & lastRow).Value   ' 一括で読み込む（1行目は見出し）
    For i = 1 To UBound(data, 1)
        If IsNumeric(data(i, 3)) And IsNumeric(data(i, 4)) Then
            data(i, 5) = data(i, 3) * data(i, 4)
        Else
            data(i, 5) = ""
        End If
    Next i
    ws.Range("A2:E" & lastRow).Value = data    ' 一括で書き戻す
End Sub

配列を使うときの注意点

Range.Value で作った配列は、添字が 0 ではなく 1 から始まります。また、範囲が1セルだけのときは配列ではなく単一の値になるため、データが1行しかない場合は別に扱うか、範囲を2行以上にする工夫が必要です。

書き戻すと数式は値に置き換わる点にも注意してください。数式を残したい列がある場合は、その列を範囲に含めないようにします。

• 配列の添字は 1 始まり（UBound(data, 1) が行数）
• 1セルだけの範囲は配列にならない
• 書き戻した範囲の数式は値になる

どれくらい速くなるか

環境によって差はありますが、1万行程度の処理であれば、セルを1つずつ扱うループに比べて数十倍以上速くなることも珍しくありません。まずは画面更新の停止だけでも効果があるので、既存のマクロに追加するところから始めると安全です。

ほかの記事