VBA セル操作の高速化 — Range vs 配列一括読み書き・Calculation制御

1万行のセルをRangeで1つずつループ処理していたマクロが、実行に10分以上かかって固まっていたことがあります。配列に一括で読み書きしてCalculationを手動に切り替えるだけで劇的に速くなった経験を共有します。

遅い原因：セルへの個別アクセス

図1: 個別アクセス vs 配列一括読み書きの性能差

VBAとExcelのセルオブジェクトはCOM境界をまたいで通信します。ループで1セルずつアクセスするたびにこのオーバーヘッドが発生し、1万行を超えると体感できる遅さになります。

VBA — 遅いパターン（NG）

' NG: 1万行 × 1列 = 1万回のセルアクセス
Sub SlowExample()
    Dim i As Long
    For i = 1 To 10000
        Cells(i, 1).Value = i * 2   ' 毎回COM境界をまたぐ
    Next i
End Sub

配列への一括読み込み

VBA — 配列への一括読み込み

Option Explicit

Sub BulkReadExample()
    Dim ws  As Worksheet
    Dim arr As Variant

    Set ws = ThisWorkbook.Sheets("データ")

    ' Range.Value でそのまま2次元配列に転送（1回のCOM呼び出し）
    arr = ws.Range("A1:C10000").Value

    ' arr(行, 列) でアクセス（1始まり）
    Dim i As Long
    For i = 1 To UBound(arr, 1)
        ' ── VBA 側だけで処理（COM境界をまたがない）──
        If arr(i, 2) > 100 Then
            arr(i, 3) = arr(i, 2) * 1.1
        End If
    Next i

    ' 処理結果を一括書き戻し
    ws.Range("A1:C10000").Value = arr
End Sub

配列からの一括書き込み

VBA — 配列から一括書き込み

Sub BulkWriteExample()
    Dim rows As Long: rows = 10000
    Dim cols As Long: cols = 3

    ' 2次元配列をメモリ上で組み立て
    Dim arr(1 To 10000, 1 To 3) As Variant
    Dim i As Long
    For i = 1 To rows
        arr(i, 1) = i
        arr(i, 2) = i * 100
        arr(i, 3) = "データ" & i
    Next i

    ' 一括書き込み（1回のCOM呼び出し）
    ThisWorkbook.Sheets("出力").Range("A1").Resize(rows, cols).Value = arr
End Sub

Application プロパティの制御

プロパティ	設定値	効果
`Application.ScreenUpdating`	`False`	画面再描画を停止（最も効果大）
`Application.Calculation`	`xlCalculationManual`	自動再計算を停止
`Application.EnableEvents`	`False`	イベント発火を停止
`Application.DisplayStatusBar`	`False`	ステータスバー更新を停止

高速化テンプレート

VBA — 高速化テンプレート

Option Explicit

Sub FastProcessTemplate()
    ' ── 高速化設定 ON ──
    Application.ScreenUpdating = False
    Application.Calculation    = xlCalculationManual
    Application.EnableEvents   = False

    On Error GoTo Cleanup

    ' ── メイン処理 ──
    Dim arr As Variant
    arr = ThisWorkbook.Sheets("Sheet1").Range("A1:E10000").Value

    Dim i As Long
    For i = 1 To UBound(arr, 1)
        ' ... 配列内で処理 ...
    Next i

    ThisWorkbook.Sheets("Sheet1").Range("A1:E10000").Value = arr

Cleanup:
    ' ── 高速化設定を必ず戻す ──
    Application.ScreenUpdating = True
    Application.Calculation    = xlCalculationAutomatic
    Application.EnableEvents   = True

    If Err.Number <> 0 Then
        MsgBox "エラー: " & Err.Description, vbCritical
    End If
End Sub

⚠️ 必ず元に戻す

エラー発生時でも Calculation = xlCalculationAutomatic と EnableEvents = True に戻してください。Manual のままにするとExcelの数式が更新されない状態で残り、ユーザーが気づきにくいバグになります。

まとめ

セル操作高速化の3原則：① セルへの個別アクセスをなくす（配列に一括転送）、② ScreenUpdating を False にする、③ Calculation を Manual にする。これだけで大抵のボトルネックは解消できます。

✅ 次の章では…

PART 12 では 非同期・タイマー処理 を解説します。Application.OnTime の使いどころと制約を紹介します。

→ PART 12 — 非同期・タイマー処理へ

セル操作の高速化でよくある誤り

セル操作の最適化で大きな効果を得るには、なぜ遅いかの原因を正しく理解する必要があります。よくある誤りを確認します。

誤り/失敗パターン	何が起きるか	正しい対処/防止策
ScreenUpdating=FalseだけでSで速くなると思う	ScreenUpdatingは画面描画を抑制するだけで、セルへの逐次アクセスが遅い原因は解消されない	ScreenUpdatingに加え、セル読み書きを配列経由の一括処理に変更することで劇的な高速化が得られる
ループ内でRangeをその都度参照する	1セルごとにExcelオブジェクトへのアクセスが発生し、数千行で数分かかる処理になる	処理前にVariant型の配列にRangeの値を一括取得し、計算後に配列を一括書き戻す方式に変更する
Calculationの再設定を忘れる	xlCalculationManualのまま処理が終了し、以降の通常作業でも自動計算されない状態になる	Calculation=xlCalculationManualはOn Error Gotoのハンドラーも含め、必ず元の値(xlCalculationAutomatic)に戻す

PART 11 — セル操作の高速化
Range vs 配列一括読み書き・Calculation制御