Excel VBAの高速化

こんにちは。シイノです。

最近、Excel VBAの仕事をする機会がありました。
この業界は長いですが、VBAの仕事は初めてです。
自端末で実行するプログラムなので、気になるのは「速度」ではないでしょうか。
と言うことで、実際に計測してみました。

プログラムの概要

設定用のファイルにデータファイル、出力ファイル名等を設定、
出力ファイルにデータを転記する処理です。

Dim objExcel As Excel.Application
Dim objWorkBook As Object
Set objExcel = CreateObject("Excel.Application")

'非表示でファイル作成
Set objWorkBook = objExcel.Application.Workbooks.Add

'書き込み処理
（省略）

'ファイル保存
objExcel.Application.DisplayAlerts = False
objWorkBook.SaveAs (ファイルパス)
objExcel.Application.DisplayAlerts = True

'終了処理
objWorkBook.Close
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing

実行

まずは、高速化など考えず実行してみましょう。
100行程度のデータを加工しながら読み込みます。
結果は1016秒(約17分)です。
これを成果物として提出したら、お客様に怒られかねません。

高速化

VBAの高速化と言えば、「無駄な表示を止める」こと。
それでは、「Application.ScreenUpdating」を追加していきましょう。

Dim objExcel As Excel.Application
Dim objWorkBook As Object
Set objExcel = CreateObject("Excel.Application")

'非表示でファイル作成
Set objWorkBook = objExcel.Application.Workbooks.Add

'表示停止
Application.ScreenUpdating = False

'書き込み処理
（省略）

'ファイル保存
objExcel.Application.DisplayAlerts = False
objWorkBook.SaveAs (ファイルパス)
objExcel.Application.DisplayAlerts = True

'表示再開
Application.ScreenUpdating = True

'終了処理
objWorkBook.Close
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing

確認

修正したVBAを実行してみましょう。
条件は先ほどと同じです。
結果は960秒(約16分)です。
多少早くなりましたが、誤差の範囲と言われても仕方がありません。

高速化その２

「無駄な表示を止める」だけではダメなのでしょうか？
そう言えば、出力ファイルは非表示にするために別オブジェクトにしていました。
このため、別オブジェクトに対しても「Application.ScreenUpdating」を追加します。

Dim objExcel As Excel.Application
Dim objWorkBook As Object
Set objExcel = CreateObject("Excel.Application")

'非表示でファイル作成
Set objWorkBook = objExcel.Application.Workbooks.Add

'表示停止
Application.ScreenUpdating = False
objExcel.Application.ScreenUpdating = False

'書き込み処理
（省略）

'ファイル保存
objExcel.Application.DisplayAlerts = False
objWorkBook.SaveAs (ファイルパス)
objExcel.Application.DisplayAlerts = True

'表示再開
Application.ScreenUpdating = True
objExcel.Application.ScreenUpdating = True

'終了処理
objWorkBook.Close
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing

確認その２

出来ることはすべてやったはず…と言うことで、再度VBAを実行します。
勿論条件は同じです。
結果は620秒(約10分)です。

まとめ

最終的には、実行時間を7分程度削減できました。
今回作成したVBAでは、この手法は有効ということが分かりましたね。
勿論、高速化の手法はこれだけではありません。
VBAの高速化については、様々なサイトで紹介されていますので、
もっと速く！と言う方は、他の手法も探してみてください。

関連記事

TOP
TOP