インハムシステムの自動化ノート

現役エンジニアが、Excel VBA・Google Apps Scriptによる業務効率化のヒントをお届けします。

【Excel VBA】マクロが遅いときの高速化テクニック|処理時間を1/10にする設定

3000行のデータを処理するマクロに5分かかる。実行中はExcelが真っ白になって、固まったのか動いているのか分からない。こうした「遅いマクロ」は、コードの書き方を変える前に、まず実行環境の設定を見直すだけで劇的に改善することがあります。

この記事では、VBAマクロの処理速度を上げるための設定と、見落としやすい遅さの原因について解説します。


なぜマクロは遅くなるのか

VBAの処理が遅い原因は、大きく3つに分けられます。

  1. 画面の再描画 — セルに値を書き込むたびに、Excelが画面を描き直している
  2. 数式の再計算 — 1セル書き換えるたびに、シート中の数式が再計算されている
  3. セルへのアクセス回数 — 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の実務的なテクニックを紹介していますので、ぜひチェックしてみてください。