画面の更新を止めてマクロ(VBA)のスピードアップ

スポンサーリンク

コードによっては時間がかかる処理時間

マクロ(VBA)で色々な処理をしていると中には時間がかかる処理もあります。
その場合、実行された処理が終わるまで他の処理ができず時間をムダに過ごすことになるので、何とか早く処理が終わるようにと色々工夫してコードを作成します。

今回はマクロ(VBA)の実行時間の短縮で最も効果が高かった「ディスプレイの更新を止める」VBAコードを紹介します。

画面の更新はかなり負荷がかかる

マクロを実行している中で、処理に伴って画面を更新していく作業はかなり負担がかかり、実行時間に影響が出ます。
特に画面が更新されるスクロール、セルに値を入れる、罫線を引く、などの処理は画面の更新のせいで遅くなっているといってもいいくらい表示画面を更新するのに時間がかかっています。
場合によっては10倍くらい処理時間が変わることもあります。

スポンサーリンク

画面の更新を止めるVBAコード

画面の更新を止めるVBAコードは以下のようなものです。

Application.ScreenUpdating = False

またVBAの処理を終了するとき、もしくは画面の更新を再開するときには以下のVBAコードを書きます。

Application.ScreenUpdating = True

これで画面の更新が再開されます。VBAコードの終了時にはルール上書いておいた方が無難なようです。
通常はこのコードは書いていなくても、VBAの処理が終わると画面の更新は再開されるようになります。

処理時間の計測

先日私が作成した罫線を修正するコードを例に、処理時間の差を計測します。
測定は3回行います。また、1回の処理が短すぎるので、秒数の計測は100回処理を繰り返したものを100で割って算出しています。

①用いるファイル

会社で使用する共有のPCの予約表のファイルですが、皆で共有すると大体罫線などが無茶苦茶にされてしまうため罫線修正のマクロを入れています。
シートは以下のような形式になっています。

41-001

②罫線修正するVBAコード

処理時間を計測するコードは以下のようになります。これを100回繰り返した時間を計測し、100で割って1回当たりの処理時間を算出します。

Dim r As Range
Dim i As Integer, x As Integer

Sub 罫線修正()
Application.ScreenUpdating = False
i = Cells(4, 1).End(xlDown).Row
罫線全部消す
For x = 3 To i
Set r = Range(Cells(x, 1), Cells(x, 2))
周囲を線で囲む
Next x
Set r = Range(Cells(3, 3), Cells(i, 10))
格子状に罫線を引く
Set r = Range(Cells(3, 1), Cells(i, 2))
周囲を太線で囲む
Set r = Range(Cells(3, 3), Cells(i, 6))
周囲を太線で囲む
Set r = Range(Cells(3, 7), Cells(i, 10))
周囲を太線で囲む
Set r = Nothing
Application.ScreenUpdating = True
End Sub

Private Sub 格子状に罫線を引く()
Dim 色 As Integer
色 = 16
With r.Borders
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlThin
End With
End Sub

Private Sub 周囲を線で囲む()
Dim 色 As Integer
色 = 16
With r.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlThin
End With
With r.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlThin
End With
With r.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlThin
End With
With r.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlThin
End With
End Sub

Private Sub 周囲を太線で囲む()
Dim 色 As Integer
色 = 16
With r.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlMedium
End With
With r.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlMedium
End With
With r.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlMedium
End With
With r.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 色
.Weight = xlMedium
End With
End Sub

Private Sub 罫線全部消す()
Cells.Borders(xlDiagonalDown).LineStyle = xlNone
Cells.Borders(xlDiagonalUp).LineStyle = xlNone
Cells.Borders(xlEdgeLeft).LineStyle = xlNone
Cells.Borders(xlEdgeTop).LineStyle = xlNone
Cells.Borders(xlEdgeBottom).LineStyle = xlNone
Cells.Borders(xlEdgeRight).LineStyle = xlNone
Cells.Borders(xlInsideVertical).LineStyle = xlNone
Cells.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

③結果

結果は以下の表のようになりました。

計測時間

画面更新あり

画面更新なし

1回目

0.21秒

0.03秒

2回目

0.21秒

0.03秒

3回目

0.22秒

0.03秒

画面更新をストップして処理を実行するだけで、処理時間が平均0.213秒→0.03秒に短縮されました。

今回用いたVBAコードは簡単な処理しかしていないので、いずれにしても1秒以下で処理が終わりますが、複雑にすればするほど、時間がかかってきます。
その時に画面更新を止めるだけで1/10とはいかなくても1/7くらいの時間に短縮できるのはかなり大きいと思います。

これまでマクロやVBAコードを作っていて処理に時間がかかると悩んでいる方はぜひ試してみてください。

コメント