マクロを簡単に高速化できる手法として、
以下の4つがよく使われます。
- Application.ScreenUpdatingによる「描画の停止」
- Application.Calculationによる「自動計算の停止」
- Application.EnableEventsによる「イベントマクロの停止」
- Application.Cursorによる「マウスカーソル描画の停止」
この4つに実際どの程度の効果があるのかを検証してみましょう。
なお、この4つは「やらないメリット」はほぼありませんので、
検証したからと言ってどれを使うかという話にはなりません。
仕様を知っておくための記事としてお使いいただければと思います。
- 検証ソースコード
- 単純なセルへの書き込み
- Selectへの効果 - ScreenUpdating
- 数式への効果 - Calculation
- イベントプロシージャへの効果 - EnableEvents
- Cursorの効果について
- ScreenUpdatingが効果的な処理
検証ソースコード
以下のTimerを使ったコードで検証します。
Dim 開始時刻 As Double, 終了時刻 As Double 開始時刻 = Timer 'Application.ScreenUpdating = False 'Application.Calculation = xlCalculationManual 'Application.EnableEvents = False 'Application.Cursor = xlWait ' ここに時間のかかる処理 'Application.ScreenUpdating = True 'Application.Calculation = xlCalculationAutomatic 'Application.EnableEvents = True 'Application.Cursor = xlDefault 終了時刻 = Timer Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"
以降のコードでは「ここに時間のかかる処理」部分だけを記載します。
単純なセルへの書き込み
「1シートしか存在せず、数式も1つもないブック」
に対して以下のコードを実行してみます。
Dim i As Long For i = 1 To 10000 Cells(i, 1) = i Next
| 使用した機能 | 結果 |
|---|---|
| そのまま実行 | 0.807秒 |
| ScreenUpdating | 0.796秒 |
| Calculation | 0.454秒 |
| EnableEvents | 0.743秒 |
| Cursor | 0.802秒 |
単純にセルに書き込みをするだけだと、
意外にもScreenUpdatingの効果はほとんどないですね。
対して効果が大きいのがCalculationで、
数式がひとつもなくとも、倍速に近い速さで処理を終えています。
これは「再計算をする前にそのセルを参照している数式の存在チェック」
が行われており、それを無くせる効果と考えることができそうです。
EnableEventsも多少は早くなっており、
たとえイベントプロシージャがなくても少しは速度効果があるようです。
Cursorについてはほぼ影響はありませんでした。
これは後程考察(所感)を記載します。
Selectへの効果 - ScreenUpdating
続いて以下のコードを実行した結果がこちらです。
Dim i As Long For i = 1 To 10000 Cells(i, 1).Select Selection.Value = 1 Next
| 使用した機能 | 結果 |
|---|---|
| そのまま実行 | 9.56秒 |
| ScreenUpdating = False | 1.41秒 |
マクロ記録を流用したマクロで発生しやすいSelect/Selectionコードですが、
これに対するScreenUpdatingの速度効果は絶大ですね。
と言っても本来はこの書き方はすべきではなく、
速度以前に安全性の面でも問題のある書き方です。
「そもそもこの書き方はしてはいけない」
というのは念頭に置いておいてください。
数式への効果 - Calculation
続いてコードはそのままに、B列に「=A1」という数式を入れてみます。
' コードは同じだが実行されるたびに右隣りのセルが連動する Dim i As Long For i = 1 To 10000 Cells(i, 1) = i Next
その結果がこちら
| 使用した機能 | 結果 |
|---|---|
| そのまま実行 | 4.96秒 |
| ScreenUpdating | 4.66秒 |
| Calculation | 0.50秒 |
| EnableEvents | 4.60秒 |
| Cursor | 5.01秒 |
当然と言えば当然の結果かもしれませんが、
数式の計算を止めるCalculationの効果は絶大ですね。
数式がなくても倍速だったCalculationですが、
「=A1」だけの数式ですら10倍速になりました。
ただのセル参照ですらこれなので、
LOOKUP系列やSUMIF/COUNTIF系列を使っていると、
数千～数万倍の速度差になる可能性もあります。
マクロ高速化四天王の最強は間違いなく彼でしょう。
「ScreenUpdatingをはじめとする高速化処理」
なんて呼ばれることもあり、知名度的に本記事のタイトルもこれにしましたが、
自分がメインでないことに憤っているかもしれません。
といいつつも、止めたらダメな数式にだけは注意して使用して下さい。
イベントプロシージャへの効果 - EnableEvents
続いて実行コードは変えないまま、シートモジュールに以下のコードを置いてみます。
' シートモジュールに空のイベントを置いておく Private Sub Worksheet_Change(ByVal Target As Range) End Sub ' コードは同じだが実行されるたびにイベントが発火する Dim i As Long For i = 1 To 10000 Cells(i, 1) = i Next
| 使用した機能 | 結果 |
|---|---|
| そのまま実行 | 1.08秒 |
| EnableEvents = False | 0.87秒 |
ただの空プロシージャのCallですが、しっかりと効果が出ていますね。
プロシージャ内に判定や処理が入っていくと、
ここからさらに高速化効果（そのまま実行時の遅さ）が出てきます。
といってもEnableEventsは高速化のためにやっているというよりは、
予期せぬ処理が入ってしまうことを防ぐ目的の方が大きいです。
イベントのあるシートへの処理を行うのであれば、
別に早くならなかったとしてもOFFにする必要がある点は押さえておきましょう。
Cursorの効果について
さて4つ目のCursorですが、
今回いろいろやったのですが効果が出た処理がありませんでした。
Formボタンを置いてみて、ボタンとセルを行き来しながら実行
みたいなことを試してみましたがほとんど効果はなし。。。
マウスカーソルの影響はExcelのバージョンやWindowsのバージョン、
対象のExcelファイルの状況によるところが大きいと感じています。
個人的な所感だけおいておくと、
- Excel2013から「処理するExcelファイルの上にカーソルがあると遅くなる」ようになった
- マクロ実行後にカーソルをタスクバーに避難するとマクロが高速化した
- この頃のExcelはApplication.Cursorの効力がかなり大きかった
- Excel2016でもそうだった気がするという情報提供あり
- いつの間にか↑の状況が消えた。xlWaitにしなくてもくるくるマークになるようにもなった
- 現在、365においてはCursorの効果は限定的な様子
- でもたまにこれを入れないと劇遅になるマクロがある（気がする）
という感じです。
この機能だけは私の感想という感じになってしまいますが、
一応ご参考ください。
ScreenUpdatingが効果的な処理
さてここまでで4つの機能の大まかな検証は終わりました。
CalculationとEnableEventsについては、
使っている数式やイベントに寄る話になるためこれで完了とします。
（Cursorも謎なのでこれで終わり笑）
あとはScreenUpdatingの効果が大きい処理を検証してみます。
表示範囲内/外での速度差
まずはちょっとコードを変えて、
10000回A1セルを編集してみます。
Dim i As Long For i = 1 To 10000 Cells(1, 1) = i Next
| 検証内容 | 結果 |
|---|---|
| A1セルを表示して実行 | 4.05秒 |
| A1セルを表示せず実行 | 0.816秒 |
| ScreenUpdating = False | 0.805秒 |
| (再掲)A10000までの実行 | 0.807秒 |
A1セルを10000回編集してみると、
A1～A10000を編集した実行結果と比べて明らかに遅くなっています。
しかしここでA2セルを一番上にしてA1セルを画面から見えなくすると、
ScreenUpdating=Falseとほぼ同速になりました。
セルの描画は「本当に描画するときに遅くなる」ようで、
ScreenUpdatingの効果も本当に描画しているときだけのようです。
試しに「別シート」「別ブック」でも実行してみましたが、
- 映っていない別シートでの処理はScreenUpdating=Falseと同速
- 別ブックも同じく映っていないシートであれば高速
- ActiveWorkbookでなくともそのブックのActiveSheetなら遅くなる
と、とにかく「映っていると遅い」という仕様のようですね。
もうひとつ面白い仕様として、おなじ10000回の処理を
- A1セルだけを10000回処理
- A1～T1セル(20個)を500回ずつ処理
- 100×100セルを1回ずつ処理
※ 画面を縮小表示し、↑3つともすべてのセルを表示して実行
この3つの処理にして比べてみると、
| 検証内容 | 結果 |
|---|---|
| A1セルだけを処理 | 4.05秒 |
| 20個のセルを処理 | 1.54秒 |
| 10000個のセルを処理 | 1.06秒 |
| (再掲)A10000までの実行 | 0.81秒 |
こんな風になりました。
同じセルを何度も書き換えると描画処理は遅くなるんですね。
不思議な仕様です。
すべて違うセルを処理するのであれば、
表示されていても2割増し程度で処理を完了できるようです。
背景色の設定 - Interior.Color
続いて背景色(Interior.Color)を設定した場合がこちらです。
| 検証内容 | 描画ON | 描画OFF |
|---|---|---|
| A1～A10000 | 0.626秒 | 0.570秒 |
| A1セルだけを10000回 | 3.454秒 | 0.617秒 |
値より描画が大変そうかな？と思いましたが、
そこまででもないようです。
表示されているセルに実行、同じセルに何度も実行
で遅くなるという仕様・倍率も値の変更と大体同じですね。
行の削除 - Delete
続いて行の削除を実行した結果がこちらです。
①・②どちらもA1セルを見ながら実行しています。
| 検証内容 | 描画ON | 描画OFF |
|---|---|---|
| 1行目を1000回削除 | 1.46秒 | 0.84秒 |
| 5000行目を1000回削除 | 1.41秒 | 0.77秒 |
今までとはうって変わってScreenUpdatingの効果が発揮されました。
セルのレイアウトが変更されると大きく描画を更新する必要があるようです。
さらに「表示されてなくても遅くなる」仕様のようで、
画面外の5000行目が削除されても変わらない遅延が発生しています。
値やプロパティの変更ではなく、
Rangeの構成にかかわる処理はScreenUpdatingの効果が大きいようですね。
ブックの開閉 - Workbooks.Open/Close
続いてブックを開閉してみます。
「ブックを開いて値を更新して閉じる」を10回行ってみましょう。
Dim i As Long For i = 1 To 10 Dim ブック As Workbook Set ブック = Workbooks.Open("C:\Users\wfsp\Desktop\テストファイル.xlsx") ' ここの処理のありなしで場合分け Dim R As Long Dim C As Long For R = 1 To 1000 For C = 1 To 20 ブック.Worksheets(1).Cells(R, C) = 1 Next Next ブック.Close False Next
| 検証内容 | 描画ON | 描画OFF |
|---|---|---|
| ブック開閉のみ | 3.49秒 | 2.73秒 |
| 中に値を書き込み | 21.36秒 | 20.99秒 |
ブックの開閉をScreenUpdating=False下で行うと、
見た目上はブックを開いていないかのように振る舞います。
よって視覚的にはだいぶ高速化しているように感じますが、
- ブックの開閉自体は2～3割高速化できる
- 非表示のまま行われる中身の処理は今までの検証と同じ
という結果になるため、実際の速度効果は中身の処理次第という感じですね。
フィルター関連 - AutoFilter
最後にフィルター関連の処理を試してみます。
最終系をこのコード↓として順次タイムを計ってみましょう。
Dim i As Long For i = 1 To 100 Dim ws As Worksheet Set ws = Worksheets.Add Sheet1.Range("A1").AutoFilter 1, i Sheet1.AutoFilter.Range.Copy ws.Paste Sheet1.AutoFilter.ShowAllData Next
| 検証内容 | 描画ON | 描画OFF |
|---|---|---|
| 抽出・クリアのみ | 0.68秒 | 0.33秒 |
| 結果をコピー(コピー元表示) | 3.95秒 | 3.43秒 |
| 結果をコピー(ペースト先表示) | 5.70秒 | 3.43秒 |
| シートを生成して結果をコピー | 9.36秒 | 3.95秒 |
こんな結果になりました。
- フィルターの抽出・クリアはそもそもの処理時間が少なく誤差
- コピーは描画が重くないがペーストは描画が重い
- シート生成もそこそこ重い上、生成シートがActiveSheetになってしまう仕様のためその後の処理も重くなる
ということで、最後は倍速以上の効果になっていましたね。
このあたりのUIを駆使し始めると、
ScreenUpdatingの効果は大きくなっていくようです。
以上でScreenUpdatingを始め4種の高速化機能の検証を終わります。
冒頭に書いた通りこの設定は「やらないメリット」はほぼありませんので、
検証したからと言って使う/使わないの判断を行うわけではありません。
仕様や効果を知っておくための記事としてお使いいただければと思います。
長文読了、おつかれさまでした！
