Excel VBAを使っていて、処理情報が多いと動作が遅くなります。
この記事では、VBAを高速化する方法について紹介します。
壱高速化の方法がわかったら随時記事をアップデートしたいと思います。
高速化判断用サンプルプログラム
処理が高速化されるか判断するための以下のサンプルプログラムを用いて判断します。
Sub Sample1_高速化判断用()
'配列を宣言
Dim x As Long
Dim y As Long
Dim i As Long
Dim StartTime
Dim StopTime
Dim SyoriTime
StartTime = Time
'出力
i = 0
For y = 0 To 999
For x = 0 To 999
Cells(y + 1, x + 1) = i
i = i + 1
Next
Next
StopTime = Time
SyoriTime = (StopTime - StartTime) * 24 * 60 * 60
MsgBox "処理時間は、" & SyoriTime & "秒でした。"
End Sub■実行結果
横方向に1000個、縦方向に1000個のセルに10万個（0～999999まで）のデータを出力するプログラムになります。
StartTimeに開始時間、EndTimeに終了時間、SyoriTimeに計算結果を代入し、処理時間を計測して出力するようにしてみました。
試しに3回測定した結果は、平均82.7秒でした。
壱パソコンによっては、非常に時間がかかる場合もあるので、もし試す場合は、xとyの値を小さい値（例えば99）に変更して試して下さい。
Application.ScreenUpdatingを使って画面の更新を停止して高速化する
Application.ScreenUpdatingは、高速化する場合によく使われる、画面の更新を停止して高速化する定番の方法になります。
VBA処理の動作を非表示にし、高速で処理することができます。
Sub Sample2_画面更新停止()
'配列を宣言
Dim x As Long
Dim y As Long
Dim i As Long
Dim StartTime
Dim StopTime
Dim SyoriTime
'画面更新停止
Application.ScreenUpdating = False
StartTime = Time
'出力
i = 0
For y = 0 To 999
For x = 0 To 999
Cells(y + 1, x + 1) = i
i = i + 1
Next
Next
StopTime = Time
SyoriTime = (StopTime - StartTime) * 24 * 60 * 60
MsgBox "処理時間は、" & SyoriTime & "秒でした。"
End Sub■実行結果
13行目に画面更新を停止させる「Application.ScreenUpdating = False」を追加したコードになります。
画面更新を再開させるために、プロシージャの最後に「Application.ScreenUpdating = True」を入れる場合もありますが、再開させなくても問題ないため、このサンプルコードでは追記していません。
3回測定した結果は、平均74.3秒でした。
壱今回のサンプルプログラムではあまり時間短縮できませんでしたが、プログラムによっては、ぐっと短時間になることもあります。
配列を使って高速化する
VBAでは、セル1つ1つを処理するのに時間がかかります。
そのため、配列に1度保存した後に配列の中身を1度に出力（セルに貼り付ける）ことで処理を高速化できる場合があります。
Sub Sample3_配列保存出力()
'配列を宣言
Dim MyArray(999, 999) As Long
Dim x As Long
Dim y As Long
Dim i As Long
Dim StartTime
Dim StopTime
Dim SyoriTime
StartTime = Time
'配列にデータを保存
i = 0
For y = 0 To 999
For x = 0 To 999
i = i + 1
MyArray(y, x) = i
Next
Next
'出力
Range(Cells(1, 1), Cells(UBound(MyArray, 1), UBound(MyArray, 2))) = MyArray
StopTime = Time
SyoriTime = (StopTime - StartTime) * 24 * 60 * 60
MsgBox "処理時間は、" & SyoriTime & "秒でした。"
End Sub■実行結果
4行目に「MyArray」という配列を宣言し、15～21行目でその配列に出力するデータを保存します。
24行目で配列に保存されているデータをまとめて出力します。
3回測定した結果は、平均1秒でした。
まとめ
この記事では、VBAの処理を高速化するための2つの方法を紹介しました。
今回の実行結果をまとめると次の通りです。
| 回数 | そのまま | 画面更新停止 | 配列保存出力 |
|---|---|---|---|
| 1回目 | 79秒 | 75秒 | 1秒 |
| 2回目 | 86秒 | 74秒 | 1秒 |
| 3回目 | 83秒 | 74秒 | 1秒 |
| 平均値 | 82.7秒 | 74.3秒 | 1秒 |
Application.ScreenUpdatingは、1行コードを追加するだけで時間短縮できるため、とてもおすすめです。
Application.ScreenUpdating = False配列に保存して出力するのは、プログラムを作り直す必要があるため、手間はかかりますが今回のケースなどセル1つ1つを処理する場合と比べると圧倒的に処理時間を短縮できるので、VBA処理に時間がかかっている場合は、ぜひ試してみて下さい。
コメント