マクロを組んだ時に「やたらと処理が速度が遅いな…」と感じたことはありませんか？
処理の中で扱うデータ量が多かったり、別のブックを開いたりすると処理が終わるまで時間が掛かることがあります。
エクセルが「応答なし」のまま操作できなくなることがありませんか？
実際にVBAの処理速度を改善する書き方をするとどのくらい差が出るのか比較してみようと思います！
サンプル
今回は処理時間を計測しながら比較してみようと思います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub Sample() Dim dblStart As Double: dblStart = Timer '開始時刻を取得 Dim i As Long With Sheet1 .Cells.Clear For i = 1 To 100000 .Cells(i, 1) = i .Cells(i, 2) = i + 10 .Cells(i, 3) = i + 20 .Cells(i, 4) = i + 30 .Cells(i, 5) = i + 40 .Cells(i, 6) = i + 50 .Cells(i, 7) = i + 60 .Cells(i, 8) = i + 70 .Cells(i, 9) = i + 80 .Cells(i, 10) = i + 90 .Cells(i, 11) = i + 100 Next i End With Dim dblEnd As Double: dblEnd = Timer '終了時刻を取得 Dim dbltime As Double: dbltime = dblEnd - dblStart '所要時間を計算 Debug.Print "実施時間は" & Format$(Int(dbltime * 10 ^ 4 + 0.5) / 10 ^ 4) & "秒でした" '所要時間の表示 End Sub |
上記マクロを実行した結果がこちらです👇
処理の内容としてはA～K列までAを10ずつ加算した数字が入り、縦には10万行まで加算した値が設定されています。
実行結果(対策なし)
速度改善の処理をせずに実行した結果は約42.8秒でした。
途中でExcelが応答なしになったので、処理が進んでいるのかフリーズしているのかわからなくなりました。
対策方法
画面の更新を停止
まずは画面の更新をリアルタイムで確認する必要はなので、止めてしまいましょう。
画面の更新を止める場合は処理の前に「Application.ScreenUpdating = False」と設定します。
|
1 2 3 4 5 |
Application.ScreenUpdating = False ' 描画を停止 ' 処理 Application.ScreenUpdating = True ' 描画を再開 |
処理の最後には「Application.ScreenUpdating = True」と設定して画面が更新するように戻してください。
画面の更新を止めてから処理時間を測定すると約42秒とほぼ変化がありませんでした。
自動計算を停止
続いてExcelの自動計算を停止しましょう。
「Application.Calculation = xlCalculationManual」となっている間は手動計算に切り替わります。
|
1 2 3 4 5 |
Application.Calculation = xlCalculationManual ' 手動計算 ' 処理 Application.Calculation = xlCalculationAutomatic ' 自動計算 |
処理の最後には「Application.Calculation = xlCalculationAutomatic」と設定して自動計算に戻してください。
画面更新、自動計算を停止すると約9秒ほど早くなりました。
今回は参照するセルが多いため、自動計算の停止が効果的でした。
セルの配列化
最後にセルにアクセスする回数を最小限に変更します。
For文で11回*100000回セルを参照していた分を配列に変更して、For文を抜けたあとに1回だけセルに設定するように修正してみました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Dim arr(1 To 100000, 1 To 11) As Variant With Sheet1 .Cells.Clear For i = 1 To 100000 ' .Cells(i, 1) = i ' .Cells(i, 2) = i + 10 ' .Cells(i, 3) = i + 20 ' .Cells(i, 4) = i + 30 ' .Cells(i, 5) = i + 40 ' .Cells(i, 6) = i + 50 ' .Cells(i, 7) = i + 60 ' .Cells(i, 8) = i + 70 ' .Cells(i, 9) = i + 80 ' .Cells(i, 10) = i + 90 ' .Cells(i, 11) = i + 100 arr(i, 1) = i arr(i, 2) = i + 10 arr(i, 3) = i + 20 arr(i, 4) = i + 30 arr(i, 5) = i + 40 arr(i, 6) = i + 50 arr(i, 7) = i + 60 arr(i, 8) = i + 70 arr(i, 9) = i + 80 arr(i, 10) = i + 90 arr(i, 11) = i + 100 Next i .Range("A1:K" & 10000) = arr End With |
実行結果は約1.4秒です！
比較するまでもなく一瞬で処理が終わりました…。
これでセルのアクセス回数が多いほど処理速度に影響があることがわかりますよね。
二次元配列を使った記事はこちらからどうぞ！
まとめ
最終的なサンプルコードは下記のようになりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Option Explicit Sub Sample() Application.ScreenUpdating = False Application.EnableEvents = False Dim dblStart As Double: dblStart = Timer '開始時刻を取得 Dim i As Long Dim arr(1 To 100000, 1 To 11) As Variant With Sheet1 .Cells.Clear For i = 1 To 100000 arr(i, 1) = i arr(i, 2) = i + 10 arr(i, 3) = i + 20 arr(i, 4) = i + 30 arr(i, 5) = i + 40 arr(i, 6) = i + 50 arr(i, 7) = i + 60 arr(i, 8) = i + 70 arr(i, 9) = i + 80 arr(i, 10) = i + 90 arr(i, 11) = i + 100 Next i .Range("A1:K" & 10000) = arr End With Dim dblEnd As Double: dblEnd = Timer '終了時刻を取得 Dim dbltime As Double: dbltime = dblEnd - dblStart '所要時間を計算 Debug.Print "実施時間は" & Format$(Int(dbltime * 10 ^ 4 + 0.5) / 10 ^ 4) & "秒でした" '所要時間の表示 Application.ScreenUpdating = True Application.EnableEvents = True End Sub |
配列を使いこなすことが、VBA処理速度改善の近道です！