Excelマクロが遅い原因と高速化|ScreenUpdatingが効かないとき

スポンサーリンク

「前はすぐ終わっていたマクロが、最近はなかなか終わらない」
「ネットで見た Application.ScreenUpdating = False を入れたのに、ちっとも速くならない」

Excelのマクロを業務で使っていると、こんな場面に出くわしますよね。データが増えるほど待ち時間は伸びていきます。途中で固まったように見えると、不安にもなるもの。

マクロが遅い原因は、主に5つに整理できます。この記事では症状から原因を切り分け、コピペで使える高速化コードを紹介します。ScreenUpdating を入れても効かないときの確認点もまとめました。

いま実行中のマクロが止まらず困っているなら、先にマクロを強制終了する手順で止めてから読み進めてください。

Excelマクロが遅い原因は5つ|まず結論と対応バージョン

マクロを遅くする5つの原因

Excelマクロの遅さは、主に次の5つから来ています。

  1. 画面の再描画:セルを書き換えるたびに、Excel が画面を描き直す
  2. 数式の再計算:書き込みのたびに、ブック内の数式が計算し直される
  3. イベントの連鎖:セルの変更をきっかけに、別のマクロ(イベント処理)が自動で動く
  4. セルへの1件ずつのアクセス:ループの中でセルを1つずつ読み書きする
  5. Select・Activate の多用:シートやセルを選んでから操作する

1〜3は、Excel の設定を処理中だけ止める対策です。4と5は、コードの書き方そのものを変える対策になります。

ここで押さえておきたいのが、ScreenUpdating が担当するのは1だけという点。ほかの原因で時間がかかっている場合、画面更新を止めても体感が変わらないことがあります。

対応バージョン

対応バージョン: Windows のデスクトップ版 Excel(Microsoft 365 / 2021 / 2019 など)で動作します。Excel for the web では VBA マクロを実行・編集できません。Mac のデスクトップ版 Excel では、Windows 版と挙動が異なる場合があります。

NOTE

VBE(Visual Basic Editor)を開くショートカットは Alt + F11。「挿入」→「標準モジュール」を選ぶと、コードを貼り付ける場所ができます。

Option Explicit はモジュールの先頭に1回だけ書くものです。この記事のコードを同じモジュールに続けて貼るときは、2つ目以降の Option Explicit を消してください。

マクロを入れたブックは「Excel マクロ有効ブック(.xlsm)」で保存しましょう。.xlsx で保存するとマクロは保存されません。

症状から原因を切り分ける表

症状→原因→対処の早見表

まずは、いま起きている症状を次の表で探してみてください。

症状考えられる原因主な対処
実行中に画面がチラつく・スクロールが動く画面の再描画ScreenUpdating を False にする
セルを書き換えるたびに一瞬止まる数式の再計算Calculation を手動にして最後に戻す
別のシートのマクロまで動いている気配があるイベントの連鎖EnableEvents を False にする
画面を止めても、ループ中ずっと遅いセルへの1件ずつのアクセス配列で一括読み書きする
実行中にシートが切り替わる・選択範囲が動くSelect・Activate の多用シートとセルを直接指定する
設定もコードも直したのにまだ遅いブック側の要因・データ量不要な書式や図の整理、Dictionary の活用

複数の行に当てはまるなら、設定の対策(表の1〜3行目)とコードの対策(4・5行目)を両方入れてみてください。設定だけ、コードだけでは、残りの原因が手つかずのままになるからです。

遅い箇所の見つけ方

どこで時間がかかっているのかわからないときは、処理を区切って確かめるのが近道です。ブレークポイントで止めて1行ずつ進めると、重い行やループがはっきりします。

やり方はブレークポイントとステップ実行の使い方で解説しています。手軽に済ませたいなら、怪しい処理の前後に Debug.Print Timer を書く方法もおすすめ。イミディエイト ウィンドウに出た2つの数値の差が、その区間にかかったおおよその時間です。

原因別の対処コード

画面更新を止める(ScreenUpdating)

Application.ScreenUpdating を False にすると、Excel は画面を描き直さなくなります。処理中の動きは見えなくなる代わりに、描画のぶんだけ軽くなる仕組みです。

Option Explicit

Sub RunWithoutScreenUpdate()
    Application.ScreenUpdating = False   '画面の描き直しを止める

    '--- ここに重い処理を書く ---

    Application.ScreenUpdating = True    '必ず元に戻す
End Sub

Microsoft の公式リファレンスも「マクロが終わったら True に戻す」よう案内しています。マクロ終了時に自動で戻るとされることもありますが、それを前提にはしないでください。確実にするには、自分で True に戻すのが安全です。

計算を手動にして元に戻す(Calculation)

数式が多いブックでは、セルに書き込むたびに再計算が走ります。そこで Application.Calculation を手動(xlCalculationManual)に切り替えます。手動なら、計算するのは明示的に指示したときだけです。

Option Explicit

Sub RunWithManualCalc()
    Dim calcMode As XlCalculation

    calcMode = Application.Calculation             '今の計算モードを保存
    Application.Calculation = xlCalculationManual  '手動にする

    '--- ここに重い処理を書く ---

    Application.Calculation = calcMode             '保存した状態に戻す
End Sub

ポイントは、最後に「自動」と決め打ちせず、保存しておいた元の状態に戻すこと。計算モードは Excel アプリケーション全体の設定なので、開いているほかのブックにも影響します。もともと手動で使っていた人の設定を、勝手に自動へ変えずに済むわけです。

途中で数式の結果を使うなら、その直前に Application.Calculate で計算させましょう。

イベントを止める(EnableEvents・効果は条件次第)

イベントとは、「セルが変更された」「シートが切り替わった」などをきっかけに自動で動く仕組みのこと。シートには Worksheet_Change などのイベント処理が書かれていることがあります。その場合、マクロがセルに書き込むたびにそちらも動くわけです。

Option Explicit

Sub WriteWithoutEvents()
    Application.EnableEvents = False   'イベント処理を一時的に止める

    '--- ここにセルへ書き込む処理を書く ---

    Application.EnableEvents = True    '必ず元に戻す
End Sub

効果が出るのは、イベント処理やイベントを監視するアドインがある場合です。どちらもないブックでは、変化を感じにくいかもしれません。

注意したいのは2点。まず、ループの1回ごとに True と False を切り替えると、かえって遅くなることがあります。切り替えは処理の最初と最後の1回ずつにしましょう。

もう1つは戻し忘れです。エラーや中断で False のまま残ると、以降は Worksheet_Change などが動かなくなることがあります。

Select・Activate をやめる

マクロの自動記録で作ったコードには、Select と Selection がたくさん並びがちです。公式のパフォーマンス解説でも、選択してから操作するよりセルを直接指定するほうが処理の負荷が小さい、とされています。

Option Explicit

'Before:選んでから操作する(自動記録に多い書き方)
Sub CopyValues_Select()
    Worksheets("売上").Select
    Range("D2:D6").Select
    Selection.Copy
    Worksheets("集計").Select
    Range("B2").Select
    ActiveSheet.Paste
End Sub

'After:シートとセルを直接指定する
Sub CopyValues_Direct()
    Worksheets("売上").Range("D2:D6").Copy _
        Destination:=Worksheets("集計").Range("B2")
End Sub

どちらも「売上」シートの D2:D6 を「集計」シートの B2 に貼り付けるコードです。After は画面上の選択が動かないので、シートが切り替わるチラつきも起きません。セルの指定方法に自信がない場合は、RangeとCellsの使い分けを見ておくと書き換えやすくなります。

セルを1件ずつ読み書きしない|配列で一括処理する書き換え

ループの中で Cells(i, 2).Value のようにセルを1つずつ読み書きするコード。これだと、そのたびに Excel とのやり取りが発生します。

そこで使うのが配列。配列は、たくさんの値をまとめて入れておける変数の「箱の列」です。範囲の値を一度で配列に取り込み、VBA の中だけで計算して、最後に一度で書き戻します。

ちょっとむずかしく見えますが、やっていることはシンプルです。例として、次の「売上」シートで D列の金額(数量×単価)を求めます。

行A:商品B:数量C:単価D:金額(マクロで計算)
2ボールペン121201,440
3ノート5180900
4クリップ3015450
5ファイル82502,000
6付せん20981,960

Before:1件ずつ読み書きするコード

Option Explicit

Sub CalcAmount_Slow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("売上")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        'B列とC列を1セルずつ読み、D列に1セルずつ書く
        ws.Cells(i, "D").Value = ws.Cells(i, "B").Value * ws.Cells(i, "C").Value
    Next i
End Sub

5行なら一瞬です。ところが1行ごとに「読む・読む・書く」の3回ずつセルに触れるため、行数が増えるほど待ち時間も伸びていきます。

After:配列に取り込んで一括で書き戻すコード

Option Explicit

Sub CalcAmount_Fast()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim src As Variant          '読み込み用(B列〜C列)
    Dim result() As Variant     '書き戻し用(D列)
    Dim colQty As Long          '数量の列番号(配列内)
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("売上")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub   'データがなければ終了

    '1. B2:C最終行を、2次元配列として一括で読み込む
    src = ws.Range("B2:C" & lastRow).Value
    colQty = LBound(src, 2)

    '2. 書き戻し用の配列を、読み込んだ行数と同じ大きさで用意
    ReDim result(LBound(src, 1) To UBound(src, 1), 1 To 1)

    '3. 配列の中だけで計算する(セルには触らない)
    For i = LBound(src, 1) To UBound(src, 1)
        result(i, 1) = src(i, colQty) * src(i, colQty + 1)
    Next i

    '4. 配列と同じ大きさの範囲に、一括で書き戻す
    ws.Range("D2").Resize(UBound(result, 1) - LBound(result, 1) + 1, 1).Value = result
End Sub

実行すると、D2:D6 に 1,440・900・450・2,000・1,960 が入ります。結果は Before と同じで、セルとのやり取りは「読み込み1回・書き戻し1回」だけになりました。

押さえておきたい点は次の3つです。

  • 読み込んだ配列は2次元:src(行, 列) の形で取り出す。添字の範囲は LBound・UBound で取ると、開始番号を気にせずに済む
  • 1セルだけの範囲は配列にならない:Range("B2").Value のように1セルを読むと、ただの値が返る。このコードは B列〜C列の2列を読むので、データが1行でも配列になる
  • 書き戻す範囲は配列と同じ大きさにする:範囲のほうが大きいと、余ったセルにエラー値が入る。Resize で行数をそろえているのはそのため

ScreenUpdating = False を入れても効かないときの確認点

画面更新を止めたのに速くならない。そんなときは、次の順に確認してみてください。

遅さの原因が画面更新以外にある

最初に疑いたいのがこれ。画面描画より、ループ中のセルへの1件ずつのアクセスや Select の多用のほうが重いことがあります。この場合は ScreenUpdating だけでは足りません。

配列への書き換え(「セルを1件ずつ読み書きしない」の節)を試してみましょう。Select を使っているなら、直接指定への書き換えもあわせて行います。

呼び出し先で ScreenUpdating が True に戻っている

メインのマクロで False にしていても、盲点になりやすいのが Call で呼び出した別の Sub です。その中に Application.ScreenUpdating = True があると、通った時点で画面更新が再開します。

VBE で Ctrl + F を押すと検索画面が開きます。検索対象を「カレントプロジェクト」にして、ScreenUpdating を探してみてください。

また、DoEvents や MsgBox、別のブックを開く操作をはさむと、画面が更新されることがあるとされます。ループの中にこれらがないかも見ておくと安心です。

エラーや中断で設定が戻らないまま残っている

途中でエラーが起きたり Esc キーで中断したりすると、最後の「元に戻す」行まで進みません。すると、計算が手動のまま、イベントが止まったままになることがあります。

戻っていないと感じたら、次のマクロを1回実行して元に戻しましょう。

Option Explicit

Sub ResetAppSettings()
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "画面更新・イベント・計算を元に戻しました"
End Sub

普段から手動計算で使っている場合は、Calculation の行を消してから実行してください。

EnableEvents を止めておらずイベントが連鎖している

画面更新は止めていても、イベントは動いたままになっていないでしょうか。イベント処理の中でさらにセルを書き換えていると、再計算や別のイベントが連鎖していきます。

症状の早見表の「別のシートのマクロまで動いている気配がある」に当てはまるか確認してみてください。当てはまるなら、EnableEvents もあわせて止めましょう。

計算が手動のまま戻っていない

前回のマクロが途中で止まり、計算モードが手動のまま残っているケースです。こちらは遅さではなく、数式の結果が更新されない形で表れます。

手動のまま再計算が必要になると、ステータスバーに「計算」と表示されます。これが戻し忘れの目印。

計算モードは保存時の状態がブックにも記録されるため、次に開いたときまで影響することがあります。手動計算の見分け方と解除の手順は、数式が自動で計算されないときの直し方にまとめました。

開始・終了ペアのテンプレで設定を必ず戻す

ここまでの「戻らない」問題は、次のテンプレでまとめて防げます。今の状態を保存してから OFF にし、エラーが起きても Finish で必ず元に戻す形です。

Option Explicit

Sub RunFastTemplate()
    Dim scrState As Boolean
    Dim evtState As Boolean
    Dim calcState As XlCalculation
    Dim errMsg As String

    '1. 今の状態を保存する
    scrState = Application.ScreenUpdating
    evtState = Application.EnableEvents
    calcState = Application.Calculation

    On Error GoTo ErrHandler

    '2. 処理中だけOFFにする
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    '3. ここに本来の処理を書く(別のSubをCallしてもよい)

Finish:
    '4. エラーが起きても、ここで必ず元に戻す
    On Error Resume Next   '戻す処理は途中で失敗しても最後まで進める
    Application.Calculation = calcState
    Application.EnableEvents = evtState
    Application.ScreenUpdating = scrState

    If errMsg <> "" Then MsgBox "エラーで中断しました:" & errMsg, vbExclamation
    Exit Sub

ErrHandler:
    errMsg = Err.Description   'Resume で消える前にエラー内容を控える
    Resume Finish
End Sub

エラーが起きると ErrHandler に飛び、内容を errMsg に控えてから Finish へ戻ります。Resume を通るとエラー情報が消えるので、先に控えておくのがポイントです。

「True に戻す」ではなく「保存した値に戻す」形なので、呼び出し先にこのテンプレを入れても、呼び出し元の設定を壊しません。Application.DisplayStatusBar(ステータスバーの表示)も、不要なら同じ要領で加えられます。

ただし、Esc の中断画面で「終了」を押した場合は Finish を通らないことがあります。そのときは前述の ResetAppSettings で戻してください。On Error の書き方の詳細はVBAのエラー処理が効かない原因と対処で解説しています。

それでも遅いとき(ブック側要因・データ量)

設定もコードも見直したのに遅いなら、ブック側に原因があるかもしれません。例えば次のようなものです。

  • 大量の図形や画像が貼られている
  • 条件付き書式が細かく分かれて、広い範囲にかかっている
  • 何も入っていない行・列まで使用範囲として残っている

こうしたブックの整理は、Excelが重い・フリーズする・保存できないときの対処法が参考になります。マクロを動かす前から Excel 自体が重いなら、Excelが重い・応答なしになるときの対処法もチェックしてみてください。

ループの中で「別の表から一致する行を探す」処理をしている場合は、検索のしかたが重さの原因になっていることもあります。Dictionaryを使った照合と集計に置き換えると、探す処理を軽くできます。

よくある質問とまとめ

よくある質問

Q. ScreenUpdating はマクロが終わると自動で True に戻りますか?

戻るとされることもありますが、前提にしないほうが安全です。公式リファレンスも、マクロの終わりに True へ戻すよう案内しています。開始・終了ペアのテンプレのように、自分で戻す書き方にしておきましょう。

Q. 配列に読み込むときは .Value と .Value2 のどちらがいいですか?

公式のパフォーマンス解説では、読み取りには .Value2 が速いとされています。ただし .Value2 は日付を数値(シリアル値)のまま返します。日付を扱うなら .Value、数値や文字列だけなら .Value2 と使い分けると迷いません。

Q. 進み具合を見たくて DoEvents を入れたら遅くなりました。

DoEvents は Excel の応答をよくする代わりに、呼びすぎるとマクロが遅くなります。ループの毎回ではなく、何行かに1回だけ呼ぶようにしてみてください。

Q. Excel for the web(ブラウザー版)で速く動かす方法はありますか?

Excel for the web では VBA マクロを実行・編集できません。マクロ入りのブックは「デスクトップ アプリで開く」から、デスクトップ版 Excel で実行してください。

まとめ

Excelマクロが遅いときは、まず原因が5つのどれかを切り分けるのが近道です。

  • 画面の再描画・数式の再計算・イベントの連鎖は、処理中だけ設定を止めて最後に戻す
  • セルへの1件ずつのアクセスは、配列で一括読み込み・一括書き戻しにする
  • Select・Activate は、シートとセルを直接指定する書き方に変える
  • 設定は「True に戻す」より「保存した値に戻す」。On Error で、エラー時も必ず戻す

ScreenUpdating を入れても効かないときは、遅さの原因が画面更新以外にあるのかもしれません。症状の早見表から原因を絞り込み、配列への書き換えとテンプレを試してみてください。

マクロでできる自動化の全体像は、Excel VBAで業務を自動化するガイドで紹介しています。

タイトルとURLをコピーしました