2016-07-05

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 […]

  • ジャンプ より:

    文字列操作編楽しみにしています!

  • 簡単にExcelマクロを高速化する方法 | webico blog へ返信する コメントをキャンセル

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