3000行のデータを処理するマクロに5分かかる。実行中はExcelが真っ白になって、固まったのか動いているのか分からない。こうした「遅いマクロ」は、コードの書き方を変える前に、まず実行環境の設定を見直すだけで劇的に改善することがあります。
この記事では、VBAマクロの処理速度を上げるための設定と、見落としやすい遅さの原因について解説します。
なぜマクロは遅くなるのか
VBAの処理が遅い原因は、大きく3つに分けられます。
- 画面の再描画 — セルに値を書き込むたびに、Excelが画面を描き直している
- 数式の再計算 — 1セル書き換えるたびに、シート中の数式が再計算されている
- セルへのアクセス回数 — VBAとExcelのやり取り自体に時間がかかる
このうち1と2は、マクロの実行中だけ止めておけます。これだけで処理時間が数分の1になるケースは珍しくありません。
高速化の基本:実行前に切る4つの設定
マクロの先頭でオフにし、終了時に元へ戻します。
Sub FastMacro() ' --- 高速化設定 --- Application.ScreenUpdating = False ' 画面の再描画を止める Application.Calculation = xlCalculationManual ' 自動計算を止める Application.EnableEvents = False ' イベント処理を止める Application.DisplayStatusBar = False ' ステータスバーの更新を止める ' --- ここに本来の処理を書く --- Dim i As Long For i = 1 To 30000 Cells(i, 1).Value = i Next i ' --- 設定を元に戻す --- Application.DisplayStatusBar = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
それぞれの意味は次の通りです。
| 設定 | 効果 | 注意点 |
|---|---|---|
| ScreenUpdating | 画面描画を止める。効果が最も大きい | 戻し忘れると画面が更新されない |
| Calculation | 数式の再計算を止める | 戻し忘れると数式が更新されなくなる |
| EnableEvents | Worksheet_Change等のイベントを止める | 戻し忘れると入力チェックが動かなくなる |
| DisplayStatusBar | ステータスバーの表示更新を止める | 効果は小さめ |
EnableEvents は特に重要です。 シートに入力チェックのイベントマクロを仕込んでいる場合、VBAから値を書き込むたびにそのイベントが発火し、処理が二重三重に走ります。
戻し忘れを防ぐ書き方
上のコードには弱点があります。処理の途中でエラーが起きると、設定が切られたままマクロが止まってしまうことです。画面が更新されない、数式が計算されない、という状態でExcelが残り、ユーザーは「壊れた」と感じます。
これを防ぐには、エラー処理と後始末をセットにします。
Sub SafeFastMacro() On Error GoTo Finally Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' --- 本来の処理 --- Call MainProcess Finally: ' エラーの有無にかかわらず必ず通る Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True If Err.Number <> 0 Then MsgBox "エラーが発生しました: " & Err.Description, vbExclamation End If End Sub
On Error GoTo のラベルを「後始末専用の出口」として使うのがポイントです。正常終了でもエラーでも同じ場所を通るため、設定の戻し忘れが起きません。
どこが遅いかを測る
体感で「遅い」と判断すると、見当違いの場所を直すことになります。Timer関数で実測してから手を入れます。
Sub MeasureTime() Dim startTime As Double startTime = Timer Call MainProcess Debug.Print "処理時間: " & Format(Timer - startTime, "0.00") & "秒" End Sub
結果はイミディエイトウィンドウ(Ctrl+G で表示)に出ます。処理をいくつかのブロックに分けて計測すれば、時間を食っている箇所がはっきりします。
Sub MeasureByBlock() Dim t As Double t = Timer Call ReadData Debug.Print "読み込み: " & Format(Timer - t, "0.00") & "秒" t = Timer Call CalcData Debug.Print "計算: " & Format(Timer - t, "0.00") & "秒" t = Timer Call WriteData Debug.Print "書き出し: " & Format(Timer - t, "0.00") & "秒" End Sub
見落としやすい「遅さの原因」
設定を変えても改善しない場合、ファイル側に原因があることがあります。
使用範囲が肥大化している
Ctrl+End を押したとき、データのない遠い場所(XFD1048576 など)にジャンプするファイルは、使用範囲が無駄に広がっています。過去に大量の行を消した名残です。この状態では、シート全体を対象にする処理が極端に遅くなります。
不要な行・列を「削除」(クリアではなく行ごと削除)してから上書き保存し、ファイルを開き直すと使用範囲がリセットされます。
条件付き書式が大量に設定されている
行のコピーを繰り返すと、条件付き書式のルールが分裂して数百件に増えていることがあります。「ホーム」→「条件付き書式」→「ルールの管理」で件数を確認し、不要なものを整理します。ルールが多いシートは、セルを1つ書き換えるだけでも再評価が走るため重くなります。
揮発性関数を多用している
OFFSET、INDIRECT、TODAY、NOW、RAND は、どこか1セルが変わるだけでシート中の該当関数がすべて再計算されます。数が多い場合は INDEX など非揮発性の関数に置き換えられないか検討します。
セルへのアクセス回数が多い
ループの中で1セルずつ読み書きしていると、その回数だけExcelとのやり取りが発生します。数万件を扱う場合は、配列に一括で読み込んでから配列上で処理し、最後にまとめて書き戻す方法が有効です。
Sub ArraySample() Dim arr As Variant Dim i As Long ' 一括で読み込む arr = Range("A1:C10000").Value ' 配列上で処理する(Excelとのやり取りは発生しない) For i = 1 To UBound(arr, 1) arr(i, 3) = arr(i, 1) * arr(i, 2) Next i ' 一括で書き戻す Range("A1:C10000").Value = arr End Sub
進捗を表示して「固まった」と思わせない
ScreenUpdating を切ると画面が止まるため、長い処理では不安を与えます。ステータスバーに進捗を出すと解決します。
Sub ShowProgress() Dim i As Long Dim total As Long total = 50000 Application.ScreenUpdating = False Application.DisplayStatusBar = True ' 進捗表示のためここはTrue For i = 1 To total ' --- 処理 --- ' 1000件ごとに進捗を更新する If i Mod 1000 = 0 Then Application.StatusBar = "処理中... " & i & " / " & total & " 件" DoEvents End If Next i Application.StatusBar = False ' 通常表示に戻す Application.ScreenUpdating = True End Sub
進捗更新を毎回行うと、それ自体が処理を遅くします。1000件ごとなど間引いて更新するのがコツです。最後に Application.StatusBar = False を忘れると、メッセージが表示されたまま残ります。
まとめ
- まず
ScreenUpdatingとCalculationを切る。これだけで大きく変わる - 設定の戻し忘れを防ぐため、
On Error GoToで後始末の出口を用意する - Timer関数で計測してから手を入れる。体感で判断しない
- 設定を変えても遅い場合は、使用範囲の肥大化・条件付き書式・揮発性関数を疑う
- ループ内のセルアクセスは、配列への一括読み書きに置き換える
高速化は「速く書く」より「無駄な処理をさせない」ほうが効果的です。まずは4つの設定から試してみてください。
他の記事でもExcel VBAの実務的なテクニックを紹介していますので、ぜひチェックしてみてください。