Excel VBA 高速化アプローチ【プロパティ編】
Excel VBA を高速化する方法をまとめてみました。今回は「プロパティ編」です。
使わない機能はオフにする
マクロの実行中、不要な更新、再計算、イベントの発生を制御することによりパフォーマンスが改善します。これらの制御に難しいロジックは必要なく、オブジェクトのプロパティをちょろっと変えるだけです。簡単にできるので目的に応じて実装してください。
画面更新制御 - Application.ScreenUpdating
マクロを実行した時、セルの内容変更、書式設定、シート表示切替などの処理があると、その処理毎に目紛しく画面が切り替わります。この目紛しい「画面の再描画」はメモリリソースを多く消費するそうで、処理が遅くなる大きな原因となります。
ApplicationオブジェクトのScreenUpdatingプロパティは画面更新のON/OFFを切り替えるプロパティです。このプロパティに False を設定して画面更新をOFFにすることで所謂「ちらつき」を抑え処理を高速化できます。
Application.ScreenUpdating = False ' ~ここに処理~ Application.ScreenUpdating = True
ステータスバー更新制御 ‐ Application.DisplayStatusBar
ステータスバーは状態や進捗状況などの情報を表示する便利なモノなのですが、パフォーマンスの面からみるとその更新は制御した方がよさそうです。
ApplicationオブジェクトのDisplayStatusBarプロパティはステータスバーの表示/非表示を切り替えられます。このプロパティにFalseを設定してステータスバーを非表示にすることで処理を高速化できます。
Application.DisplayStatusBar = False ' ~ここに処理~ Application.DisplayStatusBar = True
『あれ?Application.ScreenUpdatingで画面更新制御すればステータスバーの更新制御いらないんじゃね?』
とか思われるかもしれませんが、Application.ScreenUpdatingプロパティではステータスバーの更新制御はできません。ですので別途Application.DisplayStatusBarプロパティにFalseを設定してステータスバーを非表示にする必要があります。
シートの再計算停止その1 - Application.Calculation
既定ではシートの再計算は自動になっているため、シートに数式がある場合はセルが変更される度に再計算が行われ処理速度の低下に繋がります。
ApplicationオブジェクトのCalculationプロパティはシートの再計算の自動/手動を切り替えられます。このプロパティに xlCalculationManual(-4135) を設定して計算方法を手動にすることで処理を高速化できます。
Application.Calculation = xlCalculationManual ' ~ここに処理~ Application.Calculation = xlCalculationAutomatic
処理終了後は xlCalculationAutomatic(-4105) を設定してシートの再計算を再度有効にします。なお、xlCalculationAutomatic を設定した時点でシートの再計算が実行されます。
処理の途中で再計算が必要な場合は、最低限の範囲で再計算を行います。可能であれば計算範囲が一番狭いRangeオブジェクトの再計算が理想です。
'開いているすべてのブックを再計算 Application.Calculate '特定のワークシートを再計算 ActiveSheet.Calculate '指定されたセル範囲を再計算 ActiveSheet.Range("A1:C10").Calculate
シートの再計算停止その2 - Worksheet.EnableCalculation
シートの再計算を停止するもう1つの方法として、WorksheetオブジェクトのEnableCalculationプロパティがあります。このプロパティにFalseを設定するとシートの再計算が利用できなくなります。シートの再計算が停止されるのでパフォーマンスは向上しますが、明示的にCaluculateメソッドで再計算を要求しても再計算されないので注意が必要です。
'再計算停止 ActiveSheet.EnableCalculation = False '再計算を要求しても再計算されない ActiveSheet.Calculate 'Trueを設定すると再計算を実行 ActiveSheet.EnableCalculation = True
イベント発生制御 - Application.EnableEvents
プロジェクト内にWorkbookイベントやWorksheetイベントがあり、イベントのトリガーとなる処理がマクロにある場合はその都度イベントが発生して処理速度低下の原因となります。
ApplicationオブジェクトのEnableEventsプロパティはイベント発生のON/OFFを切り替えられます。このプロパティにFalseを設定することでイベントの発生を停止し処理を高速化できます。
Application.EnableEvents = False ' ~ここに処理~ Application.EnableEvents = True
このプロパティで制御できるのはWorkbookやWorksheetなどApplicationオブジェクトメンバーのイベントのみで、UserFormやそのコントロールイベントは対象となりませんのでご注意ください。
ページ区切り線非表示 - Worksheet.DisplayPageBreaks
マクロの実行により使用範囲(UsedRangeプロパティ)が変更される場合、Excel は印刷範囲をその都度再計算し直すため処理速度の低下に繋がります。
WorksheetオブジェクトのDisplayPageBreaksプロパティは印刷範囲を示す区切り線(点線)の表示/非表示を切り替えます。このプロパティにFalseを設定することで印刷範囲の再計算を停止し、処理の高速化が期待できます。
ActiveSheet.DisplayPageBreaks = False ' ~ここに処理~ ActiveSheet.DisplayPageBreaks = True
なお、このプロパティはプリンターがセットアップされていない場合はエラーとなります。
ピボットテーブル再計算停止 - PivotTable.ManualUpdate
何かしらのピボットテーブルの操作がある場合、Excelはテーブル全体に対して再計算を行うため処理速度低下の原因となります。
PivotTable.ManualUpdateプロパティはピボットテーブルの再計算を自動/手動に切り替えられます。このプロパティにTrueを設定することでピボットテーブルの再計算を停止し処理を高速化できます。
ActiveSheet.PivotTables(1).ManualUpdate = True ' ~ここに処理~ ActiveSheet.PivotTables(1).ManualUpdate = False
なお、このプロパティはプログラム終了後に自動でFalse(自動再計算)に設定し直されます。
プリンター通信無効化 - Application.PrintCommunication
ページ設定(PageSetupプロパティ)を変更する場合、プリンターとの通信が処理速度に影響します。
ApplicationオブジェクトのPrintCommunicationプロパティはプリンターとの通信の有効/無効を切り替えられます。このプロパティにFalseを設定してプリンターとの通信を無効化することで処理を高速化できます。
Application.PrintCommunication = False With ActiveSheet.PageSetup ' ~ここにページ設定処理~ End With Application.PrintCommunication = True
Application.PrintCommunicationにTrueを設定した時に変更したPageSetupプロパティがコミットされます。
なお、このプロパティはExcel 2010 で追加されたプロパティです。
処理後は各プロパティを元に戻すのを忘れずに!
一部を除き各プロパティは処理終了後も設定した値が保持されます。再計算を停止すれば自動で再計算されないままになってしまうし、画面更新を停止すると基本操作もままなりません(文章では説明しづらいですが、画面更新が停止のままだと悲惨です)。
なので明示的に処理実行前の値を再設定する必要があります。
実行前の状態は状況によって変わるので一度変数に退避しておき、実行後に退避した変数の値を設定しなおすのが良いかと思います。
また、エラーが発生すると変更したプロパティが元に戻されずに処理が中断されてしまう可能性があるため、しっかりとエラートラップをすることも忘れずにしましょう!
Sub ImprovePerformance() Dim stateScreenUpdating As Boolean Dim stateCalculation As XlCalculation 'エラートラップ On Error GoTo ErrTrap '状態を退避 stateScreenUpdating = Application.ScreenUpdating stateCalculation = Application.Calculation '機能オフ Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' ~ここに処理~ '正常終了 GoTo FIN ErrTrap: ' ~エラー時の処理~ FIN: '実行前の状態に戻す Application.ScreenUpdating = stateScreenUpdating Application.Calculation = stateCalculation End Sub
まとめ
冒頭でも述べましたが、このエントリーでご紹介した方法はどれもプロパティを変えるだけで実現できる簡単なものばかりです。目的に応じて必要なものを実装してください:)
次は「Excel VBA 高速化アプローチ【文字列操作編】」を予定しています。現在執筆中ですのでしばしお待ちを:D
ご質問などありましたらお気軽にお問合せください:)
HAYs
コメント2件
文字列操作編楽しみにしています!
[…] Excel VBA 高速化アプローチ【プロパティ編】 | Dev-Clips […]