Excel VBAでマクロを実行すると、画面がちらついたり、シートが切り替わる様子が見えたりして、処理に時間がかかることがあります。
そんなときは、処理の前後に次の2行を入れて、画面描画（画面更新）を止めます。
Application.ScreenUpdating = False ' 画面描画を停止
' （ここに処理を書く）
Application.ScreenUpdating = True ' 画面描画を再開この記事では、実際にどれくらい速くなるのかの実測と、エラーが起きても画面が固まらない書き方、さらに速くする方法をまとめます。
Excel VBAは、業務で処理を開始するとほかの作業にExcelを使用する事が難しくなりますし、できるだけ速く処理を終わらせる、高速化は非常に重要です。
画面描画を停止する基本の書き方
Sub 画面描画を停止()
Application.ScreenUpdating = False ' 画面描画を停止
' ここに処理内容を書く
Application.ScreenUpdating = True ' 画面描画を再開
End SubApplication.ScreenUpdatingをFalseにすると、マクロがセルに書き込んだりシートを切り替えたりしても、画面が更新されなくなります。
画面を書き直す手間がなくなる分、処理が速くなります。
処理が終わったらTrueに戻して、画面を更新します。
シートを移動する様子や、セルを順に選んでいく様子を利用者に見せたくないときにも使えます。
実際にどれくらい速くなる？（実測）
次のコードで、A列の1〜10,000行目を順に選んで数字を入れる処理の時間を測りました。
Sub 速度を測る()
Dim startTime As Double
Dim i As Long
startTime = Timer
Application.ScreenUpdating = False ' 比較するときはこの行を削除する
For i = 1 To 10000
Cells(i, 1).Select
Cells(i, 1).Value = i
Next i
Application.ScreenUpdating = True
MsgBox "処理時間：" & Format(Timer - startTime, "0.00") & "秒"
End Sub| 条件 | 処理時間 |
|---|---|
| 画面描画を停止しない | 13.28秒 |
| 画面描画を停止する | 0.93秒 |
※結果はPCの性能や処理の内容で変わります。
Excel VBA A列の1〜10,000行目を順に選んで数字を入れる処理の時間（画面描画を停止しない場合）
Excel VBA A列の1〜10,000行目を順に選んで数字を入れる処理の時間（画面描画を停止した場合）
実際に計測した場合でも、10倍以上は処理速度が速くなっています。
また、Timerは、その日の0時からの秒数を返す関数です。
開始時と終了時の差で、処理にかかった時間が分かります。
ご自分のマクロでも、効果を確かめるのに使えます。
エラーで止まっても元に戻る書き方
ScreenUpdatingをFalseにしたまま途中でエラーが起きると、Trueに戻す行が実行されません。
後で紹介する自動計算の停止も同じで、エラーで止まると「数式が計算されない」状態が残ってしまいます。
エラーが起きても必ず元に戻すには、On Errorを使います。
Sub エラーでも元に戻す()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
' ここに処理内容を書く
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "エラーが発生しました：" & Err.Description
Resume ExitHandler
End Subエラーが起きるとErrHandlerに飛び、メッセージを表示したあとExitHandlerに戻って、画面描画を元に戻してから終わります。
さらに速くする方法
画面描画の停止と一緒によく使われるのが、次の2つです。
| 書き方 | 効果 | 戻し方 |
|---|---|---|
| Application.Calculation = xlCalculationManual | セルに書き込むたびに数式が再計算されるのを止める。数式が多いブックで効果が大きい | Application.Calculation = xlCalculationAutomatic |
| Application.EnableEvents = False | セルの変更などで動くイベントマクロ（Worksheet_Changeなど）を止める | Application.EnableEvents = True |
まとめて使うときは、この「ひな形」をコピーして、「ここに処理内容を書く」の部分を入れ替えて使ってください。
Sub 高速化のひな形()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' ここに処理内容を書く
ExitHandler:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox "エラーが発生しました：" & Err.Description
Resume ExitHandler
End Sub自動計算を止めている間は、数式の結果が更新されません。
処理の途中で数式の結果を読み取る場合は、その前に Application.Calculate で再計算してください。
そもそも画面を動かさない書き方にする
画面描画を止めるのは効果的ですが、コードの書き方を変えると、もっと大きく速くなることがあります。
① SelectやActivateを使わない
マクロの記録で作ったコードには、「Range(“A1”).Select」のような行がたくさん入ります。セルを選ばなくても、直接値を入れられます。
' 遅い書き方
Sheets("データ").Select
Range("A1").Select
Selection.Value = 100
' 速い書き方
Sheets("データ").Range("A1").Value = 100シートを切り替える様子を見せたくないときも、Selectを使わなければそもそも画面は切り替わりません。
② たくさんのセルは配列でまとめて書き込む
1セルずつ書き込むより、配列に値をためてから一度に書き込むほうが、はるかに速くなります。
Sub 配列でまとめて書き込む()
Dim data() As Variant
Dim i As Long
ReDim data(1 To 10000, 1 To 1)
For i = 1 To 10000
data(i, 1) = i
Next i
Range("A1:A10000").Value = data
End Sub| 条件 | 処理時間 |
|---|---|
| 1セルずつ書き込む（画面描画を停止） | 0.93秒 |
| 配列でまとめて書き込む | 0.01秒 |
Excel VBA 配列でまとめて書き込んだ場合の処理時間
まとめ
- 画面描画を止めるには Application.ScreenUpdating = False、戻すには True
- エラーで止まっても戻るように、On Errorで元に戻す処理を入れておく
- 数式が多いブックでは自動計算の停止、イベントマクロがあればEnableEventsも併せて使う
- さらに速くするなら、Selectを使わず、配列でまとめて書き込む
関連記事
