和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > マクロの高速化 > マクロを高速化する基本設定4選 - Application

マクロを高速化する基本設定4選 - Application

マクロの高速化 入門・まとめ Excel設定変更 VBA

ExcelVBAには、処理の前後に書くだけでマクロを高速化できる、
「おまじない」のようなコードが存在します。

その処理内容は
「マクロ実行中のExcelの動き(描画や計算)を制限する」
というもので、本体のコードをほぼ変えることなく高速化ができます。

ただ機械的に書いておくだけで一定の高速化ができる優れものですので、
ぜひとも活用していきましょう。

基本コード

Sub サンプルマクロ()
    
    ' マクロの冒頭でExcelの各種更新を停止
    Application.ScreenUpdating = False ' 画面描画の停止
    Application.Calculation = xlCalculationManual ' 数式の計算を「手動」に
    Application.EnableEvents = False ' イベントマクロを停止
    Application.Cursor = xlWait ' マウスカーソルを「砂時計」に固定
    
    
    ' ここにマクロ本体の処理
    
    
    ' 処理の完了後にExcelの各種更新を再開
    Application.ScreenUpdating = True ' 画面描画の再開
    Application.Calculation = xlCalculationAutomatic ' 数式の計算を「自動」に
    Application.EnableEvents = True ' イベントマクロを再開
    Application.Cursor = xlDefault ' マウスカーソルの描画を再開
    
End Sub

解説

マクロの冒頭でExcelの各種更新を一旦停止し、
処理が完了後に元に戻すというコードです。

書かないメリットはほとんどありませんので、冒頭の通り
「おまじないだと思って作ったマクロすべてに書いておく」
運用でOKという、手軽で便利なコードですね。


この4コードは4つ同時に使用することがほとんどですので、
以下のようにSubにまとめることでより手軽に使用できるようになります。

Sub サンプルマクロ()

    Call Excelの自動更新を停止する

    ' マクロのメインコード

    Call Excelの自動更新を再開する

End Sub

' マクロ高速化用汎用Sub
Sub Excelの自動更新を停止する()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.Cursor = xlWait
End Sub
Sub Excelの自動更新を再開する()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.Cursor = xlDefault
End Sub

この関数を用意しておけば、以降はCall1行で済むようになります。


後述の「停止したままエラーで止まってしまった場合」の対処にも使えますので、
こちらの方法で使用することをおすすめします。


ちなみに「Ctrl + Space」による入力候補の表示機能が使えますので、
長い日本語部分は一切入力する必要はありません。

「ex」→「Ctrl+Space」→ 選択 →「Tab」で決定
Ex ⇒ Ctrl + Spaceの選択肢

各機能の概要

4つの機能の概要については以下の通りです。

画面更新の停止/再開 - ScreenUpdating

Application.ScreenUpdatingをFalseにすることで、
以降画面上の各種描画が更新されなくなります。


例えば、

  • Range("A1").Value = 1 でもセルの値が更新されない
  • Worksheets("○○").Activate でも画面上のシートが切り替わらない
  • Workbooks("○○").Open でも開いたブックは表示されない

という状態になります。

False設定中に保留されていたすべての描画は、
最後にTrueに戻した際に一気に更新されます。


DeleteやCopy/Paste、Select、Openなど、セル、シート、ブックの構成が変わる処理を20～30%高速化してくれます。

ブックの計算を手動/自動に切り替え - Calculation

Application.CalculationにxlCalculationManualを指定することで、
開いている全ブックの数式計算を「手動」に切り替えます。


4兄弟のうち圧倒的に効果が大きい高速化の本命コードで、

  • 「=A1」のようなただのセル参照でも10倍速
  • LOOKUPやSUMIFの多いファイルでは数百～数千倍
  • 数式が全くないファイルでも倍速くらいにはなる

といった感じで、他の3つを凌駕する性能があります。


反面、「数式の結果を利用するマクロ」では不具合の原因にもなりますので、
後述の「Calculateメソッド」での対応が必要な点にご注意ください。

イベントマクロの停止/再開 - EnableEvents

Worksheet_BeforeDoubleClickやWorksheet_Changeなど、
ユーザーの操作を検知して自動実行するマクロをイベントマクロと呼びます。

Application.EnableEventsをFalseにすることで、
このイベントマクロの実行(検知)を停止することができます。


特にセル値の変更を検知するWorksheet_Changeがある場合は、
絶対に書いておくべきコードです。

イベントを停止することがかなりの高速化になることはもちろん、
マクロがマクロを呼ぶことによる意図しない処理の抑制にもなります。


ちなみに「全くイベントのないブック」でもセル値変更が数%高速化します。
イベントがあるかどうかの判定がなくなる分ということでしょうね。

マウスカーソル形状の指定 - Cursor

ボタンの上では指マーク、テキストの上ではIマークなど、
あわせた場所によってマウスカーソルの形状が変わります。

Application.CursorをDefault以外に指定することで、
このマウスカーソルの描画を止めて1つの形状に固定することができます。


この高速化効果はExcelのバージョンによるようで、
最新の365だと勝手に砂時計型固定になり、効果がないことも多いようです。

と言いつつたまに高速化効果が大きいマクロに遭遇しますので、
それこそおまじないだと思って書いておいてOKです。

使用時の注意点

このコードを使用する際の注意点を列挙しておきます。

マクロがエラーなどで止まってしまった際の対処法

このApplication4種を使用していて最も遭遇する問題が、
マクロがエラーで止まった際、最後の再開コードが実行されない問題です。

計算の「自動/手動」切替以外はExcel上では戻せませんので、
以下のいずれかの方法で復元します。


① イミディエイトウィンドウで各コードを実行する

今回の4コードはイミディエイトウィンドウでも設定可能です。

各設定が再開されないままマクロが終了してしまった場合は、

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.Cursor = xlDefault

をイミディエイトウィンドウで実行することで復元できます。


前述の「汎用Subプロシージャ」にしていた場合はもっと簡単で、
イミディエイトウィンドウで

Excelの自動更新を再開する

を実行するだけでOKです。
（この関数を書いたブックをアクティブにしてから実行してください）


②汎用関数をリボンやツールバーから実行する

先ほど紹介した「Excelの自動更新を再開する」という関数

Sub Excelの自動更新を再開する()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.Cursor = xlDefault
End Sub

は、リボンやツールバーに登録して使うことができます。


これをExcelを開いた際に自動で裏に開かれる個人用マクロブックに書いておくことで、
リボンやツールバーに登録して常時使用できるようになります。


この方法が最も便利ですのでこの設定をおすすめします。

個人用マクロブックの設定方法ついての詳細はこちらをどうぞ。

Calculationの注意点とCalculateメソッド

Calculationで計算を「手動」にした場合は、
マクロ実行中は数式セルの値が更新されなくなります。

よって数式が入ったセルの値をマクロで参照する場合は、
その直前に一旦再計算を行わないと古い値のまま使ってしまう不具合につながってしまいます。


この対策として、
計算の設定を「手動」にしたままで部分的に数式を再計算する
「Calculateメソッド」が用意されています。

Application.Calculation = xlCalculationManual ' 計算を手動に

データを追加・変更する処理

Worksheets("○○").Calculate ' ← ここで一旦該当シートだけ再計算

追加したデータを参照しているセルの値を使った処理

Application.Calculation = xlCalculationAutomatic

　

Calculateメソッドは「全ブック」「シート」「セル範囲」いずれかより実行できます。

Application.Calculate ' すべてのブックを再計算
Worksheets("○○").Calculate ' ← 該当シートだけ再計算
Columns("A").Calculate ' ← A列だけ再計算

　
途中で数式の計算を行う必要があるマクロでは、
このメソッドを用いて対応してください。

Application.DisplayAlertsについて

今回の4つと一緒にApplicationシリーズとしてたまに並んでいる機能として、
Application.DisplayAlertsプロパティがあります。


これは「警告メッセージをOFFにする」ことができる機能で、
例えばシートを削除する際に

Application.DisplayAlerts = False
Worksheets("○○").Delete
Application.DisplayAlerts = True

こんな風に使うことで、本来表示される
シートを削除する際の警告
この警告を表示せずスキップしてくれる機能です。


さてこの機能ですが、
「マクロ冒頭でFalseにして最後にTrueに戻す」
という使い方は絶対にしないでください。


この機能は意図しない警告まですべて非表示にしてしまうため、
例えば「名前を付けて保存する：SaveAsメソッド」の使用時、

  • マクロの書き手は「新規ブックを保存」するつもりで書いた。
  • ブック名の設定ミスで「既存ブックと同名」になってしまった。
  • この時DisplayAlertsがFalseだと警告もなく勝手に上書き保存する。

というかなり危険な罠につながります。


この機能はOn Error Resume Nextなどと同じく

  1. 非表示にしたい警告メッセージが出るメソッドの直前でFalseにして
  2. そのメソッドの実行直後にTrueに戻す

という使い方をしなければいけないコードです。

本記事で説明した4兄弟に加えてしまうことの無いよう、
注意して使用してください。

詳しい仕様や実際の高速化効果

これら4つのコードが実際にどの程度の高速化になるかは、
マクロの内容やExcelファイルの内容に依ります。

目安としてどの程度高速化されるかを検証してみましたので、
興味があれば覗いてみて下さい。


また、4つの機能の詳しい仕様や注意点は、
専用の記事を個別に用意しております。


詳しい仕様を知りたい場合や、何らかの問題に対処したい場合は、
以下の記事をご覧いただければと思います。