Excel VBA が数万行で固まる時の高速化7選 — Range を配列に読んで一括処理する

みなさんこんにちは、ヒロポンです！

Excel のマクロ、数万行になった途端に固まりませんか？？

セルを1つずつループして処理するやつ。1000行なら一瞬です。それが5万行を超えたあたりで数分かかる。下手すると応答なし。原因ははっきりしてます。直し方にも定石がある。

今回は、数万行で固まる VBA を速くする7つのポイントを、実務で効く順にまとめました。核は「Range を配列に一括で読む」の1点。ここを押さえるだけで桁が変わります。コピペで動く形で置いていくので、手元の遅いマクロに当てていってください。

なぜ数万行で固まるのか

犯人は主に2つ。

1つめが、セル（Range）への逐次アクセス。Cells(i, 1) みたいにセルを1個触るたびに、VBA と Excel 本体の間でやり取りが走ります。え、そんなに重いの？？って思うくらい1回は軽いんですが、それが5万回積み上がると効いてくる。セルを触った回数が、そのまま処理時間に乗るイメージです。

2つめが、画面の再描画と自動再計算。ループ中にセルへ書き込むたび、画面が描き直されて数式が再計算される。これも1回ずつは軽い。数万回で地獄になります。

裏を返せば、この2つを潰せば速くなる。順に見ていきます。

⏱ 高速化インパクト早見表

先に効果の目安を1枚で。上から効きます。

配列一括だけ難易度が「中」です。効果が段違いなので最優先。では1つずつ。

1. Range を配列に一括読み込みする（最重要）

一番効くのがこれ。セルを1個ずつ触るのをやめて、範囲まるごと Variant 配列に読み込みます。

配列に入れてしまえば、あとはメモリ上の計算です。メモリ上のループは、セルを触るのに比べて桁違いに速い。処理が終わったら、また範囲まるごと1回で書き戻す。この「読むのも書くのも1回ずつ」が効きます。

やり取りが「5万回 → 2回」になる。これが桁違いの正体です！！

扱う範囲が広いほど、この差は開きます。1万行では速さを感じなくても、10万行になると「数分 → 数秒」くらいの差になることも珍しくない。

教訓: セルを回すな、配列を回せ。

2. Application.ScreenUpdating を False にする

ループ中の画面のチラつき、あれ全部描き直してます。処理の間だけ止めましょう。

Application.ScreenUpdating = False
' ここで重い処理
Application.ScreenUpdating = True   ' 終わったら忘れず戻す

こんな感じで挟むだけ。配列一括と併用すると、さらに効きます。

教訓: 処理中は画面を描かせない。

3. Application.Calculation を Manual にする

数式がたくさん入ったシートだと、セルに書き込むたびに全再計算が走る。これを手動に切り替えて止めます。

Application.Calculation = xlCalculationManual
' ここで重い処理
Application.Calculation = xlCalculationAutomatic  ' 終わったら戻す

数式が多いシートほど効きます。数式ゼロのシートなら、ここは効果薄です。

教訓: 数式が多いなら再計算を止める。

4. Application.EnableEvents を False にする

Worksheet_Change みたいなイベントを仕込んでる場合、書き込みのたびにそのイベントが発火します。処理中は止めておく。

Application.EnableEvents = False
' ここで重い処理
Application.EnableEvents = True   ' 終わったら戻す

イベントを使ってないブックなら、ここは無くてもいい。使ってる時だけです。

教訓: 自作イベントがあるなら止める。

5. 1〜4 を1つのテンプレにまとめる

毎回書くのは面倒なので、ひな形にしておくと楽です。エラーで中断しても設定が戻るように、On Error も入れておきます。

Sub FastProcess()
    ' 描画・再計算・イベントを止める
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    On Error GoTo Cleanup   ' 途中でコケても戻す

    Dim data As Variant
    data = Range("A1:C50000").Value   ' 一括読み込み（2次元・1始まり）

    Dim r As Long
    For r = 1 To UBound(data, 1)
        data(r, 3) = data(r, 1) * data(r, 2)   ' メモリ上で計算
    Next r

    Range("A1:C50000").Value = data   ' 一括書き戻し

Cleanup:
    ' 戻し忘れると Excel が重いままになるので忘れずに
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

この形の肝は On Error GoTo Cleanup です。処理の途中でエラーが出ても、Cleanup: のラベルに飛んで、止めた設定を戻してから終われます。これが無いと、エラーで中断した瞬間に画面更新も再計算も止まったまま放置される。Excel が重いままになります。

いい感じに使い回せます。中身の処理だけ差し替えれば、たいていのマクロがこの形に収まる。

教訓: 戻す処理は Cleanup にまとめて漏れなく通す。

6. 配列は「1始まり」で回す（落とし穴）

ここからは、速くしたあとに踏む落とし穴です。

Range.Value で受けた配列は、行も列も1始まりの2次元配列になります。Dim data(0 To ...) みたいな0始まりのクセで data(0, 0) と書くと、最初の行が抜けたり、範囲外エラーになったりする。

Dim data As Variant
data = Range("A1:C100").Value

' ん？ 0 から回すと1行目が抜ける
' For r = 0 To UBound(data, 1) - 1  ← これはダメ

' 1始まりで回す。範囲は LBound / UBound で取ると安全
Dim r As Long
For r = LBound(data, 1) To UBound(data, 1)
    ' data(r, 1) が1列目、data(r, 2) が2列目…
Next r

LBound / UBound で範囲を取っておけば、始まりが何であれ事故りません。

教訓: Range 由来の配列は1始まり。範囲は LBound / UBound で。

7. 配列の「型」に注意する（落とし穴）

配列に読み込むと、セルの中身が Variant の中で色々な型になります。ここが最後の罠。

• エラー値（#N/A #DIV/0! など）… Variant のエラー型で入る。そのまま計算すると実行時エラー
• 日付 … Date 型で入るので、数値のつもりで足すと想定とズレることがある
• 空セル … Empty で入る。計算では0扱いだが、文字列結合だと空文字
• 文字列として入った数値 … 見た目は数字でも、文字列として格納されている列がある。1000 や 1,000 は掛け算のときに暗黙変換されることが多いが、$1,000 のように数値化できない書式が混じると型不一致（実行時エラー 13）になり得る。ここは挙動が環境で変わるので、実機で確認しておくと安全

配列にした瞬間、中身は Variant の寄せ集めになる、ということです。見た目が数字でも、中身は文字列やエラー。これが普通にあります。

こんな感じで型がバラつくので、エラー値が混じる可能性がある列は IsError でよけてから触ります。

Dim r As Long
For r = 1 To UBound(data, 1)
    If IsError(data(r, 1)) Then
        data(r, 3) = 0            ' エラーセルは0などで逃がす
    Else
        data(r, 3) = data(r, 1) * 1.1
    End If
Next r

速くはなったのに、#N/A 1個で全体が止まる。よくあるハマり方です。実データを流す前に、エラー値と空セルの扱いだけは決めておくと安全。

ひとつ注意。IsError は #N/A などのエラー値専用で、文字列として入った数値（テキスト数値）は捕まえられません（IsError("1,000") は False）。「数値として計算できるか」を確かめたいなら、IsError ではなく IsNumeric() で判定します。

' テキスト数値かどうかは IsNumeric で見る
If IsNumeric(data(r, 1)) Then
    data(r, 3) = CDbl(data(r, 1)) * 1.1  ' 数値化してから計算
Else
    data(r, 3) = 0                        ' 数値化できないものは逃がす
End If