システム開発メーカー勤務エクセルVBAの処理速度を上げる方法を教えてほしい
上記のお悩みを解決します。
- たった2行追加することで、エクセルVBAの処理速度向上が期待できます
- 時間計測をして、早くなっていることを確認済です
- Excelマクロを多用している方
それでは本題に入ります。
中小企業の小規模情シス（総員2名）勤務歴まもなく10年目。
SSD換装が大得意。
職場で日経パソコンや日経コンピュータを読み漁る日々。
おかげで、最新のパソコン機器やデバイスの知識は常にアップデート状態。
私が雑誌に読みふける一方で、取引先のシステム開発メーカーの方はプログラム作成・修正に追われている。
次第に、システム開発メーカーの中堅の方を憂うようになった。
システム開発屋さんから情シスに転職したい方の手助けをするためにブログを立ち上げた。
狙うは『中小企業の小規模情シス』。
一度きりしかない人生を少しでもより良いものにしませんか？
処理時間を早めるなら画面更新OFF/ONのコード
結論です。
マクロの最初と最後のほうに下記のコードを入れることで画面更新をOFF/ONすることができます。
マクロの最初
Application.ScreenUpdating = False ’画面更新OFF
マクロの最後
Application.ScreenUpdating = True ’画面更新ON
上記の2行のコードを記述するだけです。
下記の処理は、罫線を適当に引いてから数値入力＆コピペしています。（繰り返し10回）
画面更新なしの場合（高速化）クリックで動きます
画面更新ありの場合（遅め）クリックで動きます
画面更新をオフにすることで、約1/9.45に処理時間を短縮することができました。
画面更新とは？
マクロ実行時に画面がチカチカすることはありませんか？
画面チカチカは、マクロで実行している作業を画面表示しているだけなんです。
マクロに精通されていない方だと、チカチカ画面を見ると『すげぇ！』ってなります。
しかし、システム管理者からすると処理時間が余分かかっている重たい処理なんです・・・。（もったいない）
今回は、マクロ処理が重たくなるのを防ぐために『画面チカチカをオフにしよう』というお話です。
たった２行追加するだけなので、とても簡単かつ効果絶大です。
やらないのは損ですよぉ～。
実験・検証してみた
今回の実験では、画面更新をオフにすることで約4倍も処理が早くなりました。
実験内容
新シート追加→表作成の動作を10回繰り返す。
画面更新あり・なしで、それぞれ10回の処理時間の平均値を算出する。
結果
- 画面更新ありの平均値は『0.455078』
- 画面更新なしの平均値は『0.113672』
テストに使用したコード
こんなテストコードを用意したよ
Sub 画面更新あり処理時間計測テスト()
'画面更新off
'Application.ScreenUpdating = False
'時間計測 変数定義
Dim start_point As Double
Dim end_point As Double
Dim processing_time As Double
Dim i As Long
'スタート時間取得
start_point = Timer
'新シート追加
Sheets.Add After:=ActiveSheet
'メインルーチン
Dim z As Integer
For z = 1 To 10
'罫線引く
Dim x As Long, countmax As Long, no As Long
countmax = 100
For y = 2 To countmax
Range("A" & y & ":F" & y).Borders.LineStyle = xlContinuous
Next
'新シート追加
Sheets.Add After:=ActiveSheet
Next z
'初期位置に戻る
Sheets("Sheet1").Select
Range("A1").Select
'エンド時間取得
end_point = Timer
'処理時間表示
processing_time = end_point - start_point
MsgBox "処理時間：" & processing_time
'画面更新on
'Application.ScreenUpdating = True
End Sub
Sub 画面更新なし処理時間計測テスト()
'画面更新off
Application.ScreenUpdating = False
'時間計測 変数定義
Dim start_point As Double
Dim end_point As Double
Dim processing_time As Double
Dim i As Long
'スタート時間取得
start_point = Timer
'新シート追加
Sheets.Add After:=ActiveSheet
'メインルーチン
Dim z As Integer
For z = 1 To 10
'罫線引く
Dim x As Long, countmax As Long, no As Long
countmax = 100
For y = 2 To countmax
Range("A" & y & ":F" & y).Borders.LineStyle = xlContinuous
Next
'新シート追加
Sheets.Add After:=ActiveSheet
Next z
'初期位置に戻る
Sheets("Sheet1").Select
Range("A1").Select
'エンド時間取得
end_point = Timer
'処理時間表示
processing_time = end_point - start_point
MsgBox "処理時間：" & processing_time
'画面更新on
Application.ScreenUpdating = True
End Sub
実験結果の表
実験結果の表だよ
| 回数 | 画面更新あり | 画面更新なし |
|---|---|---|
| 1 | 0.421875 | 0.117188 |
| 2 | 0.449219 | 0.109375 |
| 3 | 0.449219 | 0.105469 |
| 4 | 0.472656 | 0.109375 |
| 5 | 0.425781 | 0.109375 |
| 6 | 0.464844 | 0.125000 |
| 7 | 0.488281 | 0.105469 |
| 8 | 0.476563 | 0.121094 |
| 9 | 0.460938 | 0.105469 |
| 10 | 0.441406 | 0.128906 |
| 平均値 | 0.455078 | 0.113672 |
実験をするごとにエクセルファイルを閉じてから開き直しをしています。
ご覧になって分かるように、『画面更新をオフ』にした方が処理速度が早いことが証明されました。
注意点
マクロの最初に、
Application.ScreenUpdating = False
マクロの最後に、
Application.ScreenUpdating = True
この2つのコード追記するだけですが、ちょっとしたミスでマクロを壊してしまう可能性もあります。
普段からVBAコードを触る方なら問題ないと感じますが、初めて触るよって方は要注意です。
作業前には必ずエクセルファイルのバックアップを取ることをオススメします。（バックアップと言っても、ファイルコピーするだけですが）
まとめ
ポイントと注意点をまとめておきます。
| ポイント | 注意点 |
|---|---|
| たった2行を追加するだけで、約4倍も処理速度が上がったよ！ | マクロに追記する場合には、ファイルのコピーを取っておこう！ |
普段の業務でマクロを使われている方は、ぜひいちど試してみることをオススメします。以上です。
※本記事の内容で実行された結果について、筆者は一切の責任を負いませんので、ご了承ください。 必ず自己責任で実行してください。
