Excelのマクロツールを作成するときなどに、処理を高速化するためにVBAコード内に記載するべきコードを紹介します。
それぞれVBAコードの実行において処理を高速化させる手法になります。覚えておいて損はないです。
マクロでは本来のプログラム処理等以外でExcelシート内の画面更新がされると描写等に時間がかかり、それらの画面更新がボトルネックとなり処理終了まで時間を要することがあります。
※処理と画面描写等における時間差のイメージです。
そのため、処理途中では画面更新等の描写処理などをなくすことにより最終的な処理時間をなくすことができます。それを可能にするのが以下のコードとなります。
- Application.ScreenUpdating = False ’ 画面描写を停止する
- Application.Calculation = xlCalculationManual ’ 計算を手動に変更する
- Application.EnableEvents = False ’ 新たなイベントの発生を止める
詳細は以下より説明していきます。
不要なスクリーン更新を無効化する（Application.ScreenUpdating）
Application.ScreenUpdatingを一時的に無効にすることで、画面の更新を止めることができます。
画面の更新がかかる処理とは、例えば文字色の変更や罫線の記載など視覚的に変わる処理のことです。
処理毎に適宜画面が更新（文字色の変更や罫線の記載等）される場合は、Excelへの描写に多くの時間がかかります。いったん画面の更新をなくすことで実行結果が描写されるまでの時間が短くなります。
コードを記載した方が良い状況
- ループ内で多くのセルの値を変更する場合
- 複数のワークシート間を切り替える場合
- 大規模なデータのコピー、挿入、削除、計算などの操作を行う場合など
記載するコード
処理の最初に以下を記載することで、マクロ処理の実行速度が早くなります。
- Application.ScreenUpdating = False ’ 画面描写を停止する
不要な計算を避ける（Application.Calculation）
Application.Calculationを一時的に手動にすることで、セル内の数式等が自動計算されないようにできます。
本来はセル内の数式が変更されると関連する全てのセルの再計算が行われるところを自動計算を止めることで、不要な計算を回避して画面描写を防ぐことができ、計算する際に使われるはずであったリソースが節約されます。
コードを記載した方が良い状況
- ワークシートに大量のデータや数式が含まれる場合
- データの読み取りやフォーマット設定などの特定の処理で計算が不要な場合など
記載するコード
処理の最初に以下を記載することで、マクロ処理の実行速度が早くなります。
- Application.Calculation = xlCalculationManual ’ 計算を手動に変更する
イベント処理を一時的に無効化する（Application.EnableEvents）
Application.EnableEventsを変更して、イベントハンドラの実行を一時的に無効にします。
イベントハンドラは、特定のイベント（例: シートの変更、セルの変更、ワークブックのオープンなど）が発生した際に自動的に実行されるVBAコードです。
実行を一時的に無効にすることで、特定のイベントが発生してもそれに関連付けられたVBAコードが実行されません。
コードを記載した方が良い状況
- Worksheet_Changeイベントがある場合 例）セルにデータを入力または変更した場合などシート内のセルの内容が変更された際
- Workbook_SheetChangeイベントがある場合 例）ワークブック内の任意のシートでセルの内容が変更された際
- Workbook_Open、Workbook_Closeイベントがある場合 例）ワークブックがオープンされたときとクローズされた際
記載するコード
- Application.EnableEvents = False ’ 新たなイベントの発生を止める
最後にそれぞれ更新した内容を基に戻す
上記で紹介した３つのコードは処理の最後にそれぞれ最初の状態に戻す必要がありますのでご注意ください。
- 『Application.ScreenUpdating = True』 ’画面描写を更新させる
- 『Application.Calculation = xlCalculationAutomatic』 ’計算を自動に変更する
- 『Application.EnableEvents = True』 ’必要に応じて新たなイベントを発生させます
これらのコードを処理の最後に入れておかないと、マクロ実行終了後も引き継がれてしまいますので、基本的には記載が必要です。
必要に応じてエラーハンドリングを使用
上記３行の処理は、本来の処理実行中にエラーが発生した場合でも処理が実行されるようにする必要があります。
そのため以下のようなコードの構成にしてエラー発生時も問題なく元の設定状態に戻るようにしてください。
On Error GoTo ErrorHandler
Application.ScreenUpdating = False ' 画面描写を停止する
Application.Calculation = xlCalculationManual ' 計算を手動に変更する
Application.EnableEvents = False ' 新たなイベントの発生を止める
' ～本来実行したい処理を記載～
Application.ScreenUpdating = True ' 画面描写を開始する
Application.Calculation = xlCalculationAutomatic ' 自動計算を有効化する
Application.EnableEvents = True ' 新たなイベントの発生を始める
Exit Sub
ErrorHandler:
' エラー発生時にも当初の状態に戻るようにする
Application.ScreenUpdating = True ' 画面描写を開始する
Application.Calculation = xlCalculationAutomatic ' 自動計算を有効化する
Application.EnableEvents = True ' 新たなイベントの発生を始める
End Sub
高速化には遅延のボトルネックを見つけることが重要
これらの方法を組み合わせて、状況によりVBAコードの処理を高速化することができます。
ただし、最適化の際にはコードの可読性と保守性にも気を付けなければなりません。
最適化においては、コード内のどこの部分の処理がボトルネックであるかどうかを見つけ出し、必要に応じてその部分を重点的に最適化することが必要です。
