VBA

Excel VBA 高速化アプローチ【プロパティ編】

投稿日:

Excel VBA を高速化する方法をまとめてみました。今回は「プロパティ編」です。

このエントリーで記載しているサンプルコードは説明のためにあえて冗長にしてる部分もあります。ご自分のマクロに適した形に読み替えてご覧ください┏○))

使わない機能はオフにする

マクロの実行中、不要な更新、再計算、イベントの発生を制御することによりパフォーマンスが改善します。これらの制御に難しいロジックは必要なく、オブジェクトのプロパティをちょろっと変えるだけです。簡単にできるので目的に応じて実装してください。

画面更新制御 – Application.ScreenUpdating

マクロを実行した時、セルの内容変更、書式設定、シート表示切替などの処理があると、その処理毎に目紛しく画面が切り替わります。この目紛しい「画面の再描画」はメモリリソースを多く消費するそうで、処理が遅くなる大きな原因となります。

ApplicationオブジェクトのScreenUpdatingプロパティは画面更新のON/OFFを切り替えるプロパティです。このプロパティに False を設定して画面更新をOFFにすることで所謂「ちらつき」を抑え処理を高速化できます。

ステータスバー更新制御 ‐ Application.DisplayStatusBar

ステータスバーは状態や進捗状況などの情報を表示する便利なモノなのですが、パフォーマンスの面からみるとその更新は制御した方がよさそうです。

ApplicationオブジェクトのDisplayStatusBarプロパティはステータスバーの表示/非表示を切り替えられます。このプロパティにFalseを設定してステータスバーを非表示にすることで処理を高速化できます。

『あれ?Application.ScreenUpdatingで画面更新制御すればステータスバーの更新制御いらないんじゃね?』
とか思われるかもしれませんが、Application.ScreenUpdatingプロパティではステータスバーの更新制御はできません。ですので別途Application.DisplayStatusBarプロパティにFalseを設定してステータスバーを非表示にする必要があります。

シートの再計算停止その1 – Application.Calculation

既定ではシートの再計算は自動になっているため、シートに数式がある場合はセルが変更される度に再計算が行われ処理速度の低下に繋がります。

ApplicationオブジェクトのCalculationプロパティはシートの再計算の自動/手動を切り替えられます。このプロパティに xlCalculationManual(-4135) を設定して計算方法を手動にすることで処理を高速化できます。

処理終了後は xlCalculationAutomatic(-4105) を設定してシートの再計算を再度有効にします。なお、xlCalculationAutomatic を設定した時点でシートの再計算が実行されます。

処理の途中で再計算が必要な場合は、最低限の範囲で再計算を行います。可能であれば計算範囲が一番狭いRangeオブジェクトの再計算が理想です。

シートの再計算停止その2 – Worksheet.EnableCalculation

シートの再計算を停止するもう1つの方法として、WorksheetオブジェクトのEnableCalculationプロパティがあります。このプロパティにFalseを設定するとシートの再計算が利用できなくなります。シートの再計算が停止されるのでパフォーマンスは向上しますが、明示的にCaluculateメソッドで再計算を要求しても再計算されないので注意が必要です。

イベント発生制御 – Application.EnableEvents

プロジェクト内にWorkbookイベントやWorksheetイベントがあり、イベントのトリガーとなる処理がマクロにある場合はその都度イベントが発生して処理速度低下の原因となります。

ApplicationオブジェクトのEnableEventsプロパティはイベント発生のON/OFFを切り替えられます。このプロパティにFalseを設定することでイベントの発生を停止し処理を高速化できます。

このプロパティで制御できるのはWorkbookやWorksheetなどApplicationオブジェクトメンバーのイベントのみで、UserFormやそのコントロールイベントは対象となりませんのでご注意ください。

ページ区切り線非表示 – Worksheet.DisplayPageBreaks

マクロの実行により使用範囲(UsedRangeプロパティ)が変更される場合、Excel は印刷範囲をその都度再計算し直すため処理速度の低下に繋がります。

WorksheetオブジェクトのDisplayPageBreaksプロパティは印刷範囲を示す区切り線(点線)の表示/非表示を切り替えます。このプロパティにFalseを設定することで印刷範囲の再計算を停止し、処理の高速化が期待できます。

なお、このプロパティはプリンターがセットアップされていない場合はエラーとなります。

ピボットテーブル再計算停止 – PivotTable.ManualUpdate

何かしらのピボットテーブルの操作がある場合、Excelはテーブル全体に対して再計算を行うため処理速度低下の原因となります。

PivotTable.ManualUpdateプロパティはピボットテーブルの再計算を自動/手動に切り替えられます。このプロパティにTrueを設定することでピボットテーブルの再計算を停止し処理を高速化できます。

なお、このプロパティはプログラム終了後に自動でFalse(自動再計算)に設定し直されます。

プリンター通信無効化 – Application.PrintCommunication

ページ設定(PageSetupプロパティ)を変更する場合、プリンターとの通信が処理速度に影響します。

ApplicationオブジェクトのPrintCommunicationプロパティはプリンターとの通信の有効/無効を切り替えられます。このプロパティにFalseを設定してプリンターとの通信を無効化することで処理を高速化できます。

Application.PrintCommunicationにTrueを設定した時に変更したPageSetupプロパティがコミットされます。
なお、このプロパティはExcel 2010 で追加されたプロパティです。

処理後は各プロパティを元に戻すのを忘れずに!

一部を除き各プロパティは処理終了後も設定した値が保持されます。再計算を停止すれば自動で再計算されないままになってしまうし、画面更新を停止すると基本操作もままなりません(文章では説明しづらいですが、画面更新が停止のままだと悲惨です)。

なので明示的に処理実行前の値を再設定する必要があります。

実行前の状態は状況によって変わるので一度変数に退避しておき、実行後に退避した変数の値を設定しなおすのが良いかと思います。

また、エラーが発生すると変更したプロパティが元に戻されずに処理が中断されてしまう可能性があるため、しっかりとエラートラップをすることも忘れずにしましょう!

まとめ

冒頭でも述べましたが、このエントリーでご紹介した方法はどれもプロパティを変えるだけで実現できる簡単なものばかりです。目的に応じて必要なものを実装してください:)

次は「Excel VBA 高速化アプローチ【文字列操作編】」を予定しています。現在執筆中ですのでしばしお待ちを:D

プロパティ設定関連でもしここに載っていない高速化の方法がありましたらコメント欄で教えてくださいませ┏○))

ご質問などありましたらお気軽にお問合せください:)

HAYs

-VBA
-, ,


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

options

省略可能な引数を定義するOptionalキーワード

Excel VBA で省略可能な引数を持つプロシージャを作成するにはOptionalキーワードを使用します。このエントリーではOptionalキーワードの概要を説明します。 Optionalキーワード …

【Excel VBA】ブックが閉じられた事を検出する方法

先日ちょっとしたツールを作っている時に他のブックが閉じられた事を検出したい場面に遭遇したのですが、WorkbookオブジェクトにもApplicationオブジェクトにも既定のイベントにはそれっぽいのは …

StringBuilder For Excel VBA

Excel VBAでStringBuilderを使う

 VB.NET には StringBuilder というクラスが実装されています。このクラスは文字列連結に特化しており、ループ内での文字列連結など処理中に文字列の連結が何度も出現する場合にその力を発揮 …

Alphabet blocks

Mid関数とMidステートメント

Excel VBA のヘルプで「Mid」と検索すると、Midキーワードは「Mid関数」と「Midステートメント」の2つの構文で使用される旨の説明が表示されます。今回はこの「Mid関数」と「Midステー …

VBA配列基礎

通常の変数は1つの変数に1つの値しか格納できませんが、配列を利用すれば一つの変数に同じデータ型の複数の値を格納することができます。このエントリーでは、VBAで配列を利用するための基本事項をご紹介致しま …