Excel VBAの処理速度を劇的に向上させる標準モジュールとクラスの作成方法

こんにちは!大阪市を拠点に活動している『縁紡ぐ』の稲垣です。
SWELLを使ったホームページ制作や、Excel、ACCESS、RPAなどのシステム開発を行っています。
また、Excel、Word、Outlookの研修や、情報セキュリティ研修も行っています。身近なITの相談相手になりたいと思っています。
お気軽にお問い合わせください。
Excel VBAの処理速度を劇的に向上させる標準モジュールとクラスの作成方法
Excel VBAを使用していると、大量のデータ処理や複雑な計算を行う際にパフォーマンスの問題に直面することがあります。処理が遅いと、作業の効率が大きく下がりますよね。そこで今回は、Excel VBAの処理速度を劇的に向上させるためのクラスを作成する方法をご紹介します。
処理速度に影響を与える要因
Excel VBAで処理速度が遅くなる主な要因として、以下の点が挙げられます。
- 画面更新の頻度
- 自動計算の設定
- 範囲選択やセルへのアクセス方法
これらの要因を適切に管理することで、処理速度を大幅に改善することが可能です。
今回は、画面更新や自動計算といった共通として使える部分を標準モジュールかクラス化していきます。
高速化の設計
処理速度の最適化を実現するために、以下の機能を持つクラスを作成します。
- 画面更新の停止
- 自動計算の停止
- イベント発生の停止
- マウスポインターを砂時計に変更
このクラスを利用することで、VBAのコードを変更することなく、処理速度の最適化を実現できます。
標準モジュールの作成方法
標準モジュールを作成しコードを作成します。


Option Explicit
Private xlAPp As Application
'#######################################
'目的:処理を高速化する
'引数:なし
'戻り値:なし
'エラー処理:
'メモ:
'作成日 作成者 2024/4/15 合同会社縁紡ぐ 稲垣
'#######################################
Public Sub startOptimization()
Set xlAPp = Application
With xlAPp
.ScreenUpdating = False '画面更新の停止
.DisplayAlerts = False 'メッセージの表示をOFF
.Calculation = xlCalculationManual '自動計算の停止
.EnableEvents = False 'イベント発生の停止
.Cursor = xlWait 'マウスポインターを砂時計に変更
End With
End Sub
'#######################################
'目的:高速化した処理を無効化する
'引数:なし
'戻り値:なし
'エラー処理:
'メモ:
'作成日 作成者 2024/4/15 合同会社縁紡ぐ 稲垣
'#######################################
Public Sub endOptimization()
Set xlAPp = Application
With xlAPp
.ScreenUpdating = True '画面更新の再開
.DisplayAlerts = True 'メッセージの表示をON
.Calculation = xlCalculationAutomatic '自動計算の再開
.EnableEvents = True ' イベントの再開
.Cursor = xlDefault 'マウスポインターをデフォルトに変更
End With
End Sub
Option Explicit
Sub test()
'処理の高速化
SpeedOptimizer.startOptimization
'==============================================
'処理
'==============================================
'処理の高速化を終了
SpeedOptimizer.endOptimization
End Sub
エラーなどで処理が中断した場合にも、必ず高速化の処理を終了する「SpeedOptimizer.endOptimization」を実施するようにしましょう。そうしないと、画面更新や、メッセージの表示、自動計算やイベントなどがオフのままになってしまいます。
クラスの作成方法
クラスモジュールを作成しコードを作成します。


Option Explicit
'#######################################
'目的:処理を高速化するためのクラス
'引数:なし
'戻り値:なし
'エラー処理:
'メモ:
'作成日 作成者 2024/4/15 合同会社縁紡ぐ 稲垣
'#######################################
Private WithEvents xlApp As Application
Private Sub Class_Initialize()
'#######################################
'目的:コンストラクタ
'引数:なし
'戻り値:なし
'エラー処理:
'メモ:
'作成日 作成者 2024/4/15 合同会社縁紡ぐ 稲垣
'#######################################
Set xlApp = Application
With xlApp
.ScreenUpdating = False '画面更新の停止
.DisplayAlerts = False 'メッセージの表示をOFF
.Calculation = xlCalculationManual '自動計算の停止
.EnableEvents = False 'イベント発生の停止
.Cursor = xlWait 'マウスポインターを砂時計に変更
End With
End Sub
Private Sub Class_Terminate()
'#######################################
'目的:デストラクタ
'引数:なし
'戻り値:なし
'エラー処理:
'メモ:
'作成日 作成者 2024/4/15 合同会社縁紡ぐ 稲垣
'#######################################
With xlApp
.ScreenUpdating = True '画面更新の再開
.DisplayAlerts = True 'メッセージの表示をON
.Calculation = xlCalculationAutomatic '自動計算の再開
.EnableEvents = True ' イベントの再開
.Cursor = xlDefault 'マウスポインターをデフォルトに変更
End With
End Sub
クラスの使用方法
処理を行うVBAのサブプロシージャまたは関数内で、このクラスのインスタンスを作成します。インスタンスが作成されると、自動的に画面更新と自動計算が停止し、処理が完了すると再開されます。
Sub TestProgressBar()
'SpeedOptimizerの変数宣言とインスタス生成 これで高速化されます
Dim speedOptimizer As speedOptimizer
Set speedOptimizer = New speedOptimizer
'繰り返し処理等
for i = 2 to ・・・
'処理
next i
'インスタンスをメモリから解放する段階で高速化のために設定していたものを元の設定に戻す
Set speedOptimizer = Nothing
End Sub
まとめ
Excel VBAでの処理速度を向上させるためには、画面更新や自動計算の管理が重要です。今回紹介したクラスを活用することで、これらの管理を簡単に行い、処理速度の改善が期待できます。

稲垣
- Excel、ACCESSでのシステム開発が得意
- ITスキルを共有し実践的に学びながら成長する人を見るのが幸せ
- 自家焙煎するほどのコーヒー好き
- 使用言語 VBA、Python、Javascript、Java、HTML、CSS etc.
- 保有資格 Kintoneアソシエイト、日商簿記検定2級、マンション管理士、管理業務主任者、情報セキュリティマネジメント、ExcelVBA etc.
- 業務フロー図の作成や業務時間分析を通して、効率化ポイントを探る人
- お客様にとって本当に良いことかを第一に考える人
コメント