1万行のセルを1件ずつループで書き込むマクロを組んで、実行したらしばらく画面が固まった。そのときはとりあえず動けばいいと思って放置していた。
後になって、同じ処理を配列に一括で読み込んでからまとめて書き戻す形に変えたら、体感が明らかに変わった。1件ずつCellsにアクセスしていたのが、そもそもの遅さの原因だった。
この記事では、配列を軸にした高速化テクニックと、ScreenUpdatingなどの一時停止設定、そして「効いているのか」を体感ではなく数値で確認する方法までまとめました。
処理が遅くなる4つの原因
VBAが遅いと感じるとき、原因はだいたい以下の4つのどれかに当てはまります。
| 原因 | 対策 |
|---|---|
| セルへの逐次アクセス | 配列にまとめて読み書きする |
| 画面の再描画 | ScreenUpdatingを一時停止する |
| セル変更のたびの自動再計算 | Calculationを手動に切り替える |
| 変更をトリガーにしたイベント発火 | EnableEventsを一時停止する |
体感で一番差が出るのは配列化です。まずはここから見ていきます。
配列で一括読み書きする
セルを1件ずつ読み書きすると、そのたびにExcel側とのやり取りが発生します。件数が増えるほど、このやり取りの回数が処理時間に直結します。
遅い書き方：セルを1件ずつループで書き込む
Sub WriteBySlowLoop()
Dim i As Long
For i = 1 To 10000
Cells(i, 1).Value = i * 2
Next i
End Sub速い書き方：配列にまとめてから一括で書き戻す
配列上でデータを組み立ててから、最後に一度だけRangeへ代入します。Excelとのやり取りが1回で済むため、件数が多いほど差が開きます。
Sub WriteByArray()
Dim data() As Variant
Dim i As Long
ReDim data(1 To 10000, 1 To 1)
For i = 1 To 10000
data(i, 1) = i * 2
Next i
Range("A1:A10000").Value = data
End Sub既存データを読んで加工し、書き戻す
読み込みも同じ考え方です。Rangeをまとめて配列に代入すれば、その時点で全データがメモリ上に載ります。あとは配列に対してループ処理をして、最後にまとめて書き戻します。
Sub ProcessByArray()
Dim data As Variant
Dim i As Long
data = Range("A1:A10000").Value ' 一括取得
For i = 1 To UBound(data, 1)
data(i, 1) = data(i, 1) * 1.1
Next i
Range("A1:A10000").Value = data ' 一括書き戻し
End Sub1件だけを読み書きするならCellsのままで十分ですが、数百件を超えるループを見つけたら配列化を検討する、というのが僕の基準です。
ScreenUpdating・Calculation・EnableEventsを止める
配列化の次に効くのが、この3つの一時停止です。処理中に不要な再描画・再計算・イベント発火を止めることで、余計な負荷を減らします。
| 設定 | 止めると何が起きるか |
|---|---|
| ScreenUpdating | 処理中の画面の再描画が止まり、ちらつきがなくなる |
| Calculation | セル変更のたびの自動再計算が止まり、手動計算になる |
| EnableEvents | Worksheet_Changeなどのイベントが発火しなくなる |
戻し忘れるとどうなるか
これらをFalseにしたまま戻し忘れると、マクロ終了後も画面が固まったまま、セルを変更しても再計算されないまま、という状態が残ります。実際にやってしまったことがあります。
厄介なのは、処理の途中でエラーが起きて止まった場合です。正常終了だけを想定して末尾に戻す処理を書いていると、エラー時には戻されないまま終わってしまいます。
On Errorと組み合わせて確実に戻す
エラーが起きても起きなくても必ず戻す、という形にしておくのが安全です。On Error GoToで復元処理へ合流させます。
Sub FastProcessTemplate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
On Error GoTo Cleanup
' ここに重い処理を書く
Cleanup:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
If Err.Number <> 0 Then
MsgBox "エラーが発生しました: " & Err.Description, vbCritical
End If
End Sub正常終了時もラベルCleanupへそのまま流れ込んで復元されるので、正常系・異常系の両方で戻し漏れがなくなります。
無駄なSelect/Activateを削除し、オブジェクトは変数に入れる
録画マクロをそのまま使うと、SelectやActivateだらけのコードになりがちです。実際にはセルやシートを選択しなくても、オブジェクトを直接指定すれば操作できます。
' 遅い：選択操作が挟まる
Sub SlowWithSelect()
Sheets("Data").Select
Range("A1").Select
Selection.Value = "合計"
End Sub
' 速い：選択せず直接操作する
Sub FastWithoutSelect()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
ws.Range("A1").Value = "合計"
End Subループの中で同じシートを何度も参照する場合は、シート自体を変数に入れておくと、参照のたびにシートを探しに行く処理を省けます。
' 遅い：ループのたびにシートを探しに行く
For i = 1 To 1000
ThisWorkbook.Worksheets("Data").Cells(i, 1).Value = i
Next i
' 速い：変数に入れて使い回す
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
For i = 1 To 1000
ws.Cells(i, 1).Value = i
Next iワークシート関数を積極的に活用する
合計や平均のような集計は、VBAでループして自分で計算しなくても、Application.WorksheetFunction経由でシート関数をそのまま呼び出せます。内部的に最適化された処理なので、自前のループより高速です。
Sub UseWorksheetFunction()
Dim total As Double
total = Application.WorksheetFunction.Sum(Range("A1:A10000"))
End SubSum・Average・CountIf・Matchあたりは特に出番が多く、自前ループを置き換えられないか一度疑ってみる価値があります。
処理時間をTimer関数で計測する
「速くなった気がする」という体感だけで判断すると、実際には誤差レベルの改善しかしていなかった、ということもあります。効果を確認するときは、Timer関数で処理時間を数値で比較しています。
Sub MeasureTime()
Dim startTime As Single
startTime = Timer
' 計測したい処理
Call WriteByArray
Debug.Print "処理時間: " & Format(Timer - startTime, "0.000") & "秒"
End Sub改善前・改善後のコードをそれぞれ実行してイミディエイトウィンドウの数値を見比べるだけで、「本当に効いているテクニックか」が分かります。なお、Timerは深夜0時をまたぐと値がリセットされる仕様なので、日をまたぐ長時間処理の計測には向きません。
よくある失敗と対処
ScreenUpdatingなどを戻し忘れる
「ScreenUpdating・Calculation・EnableEventsを止める」で紹介したOn Errorとの組み合わせテンプレを使い、正常系・異常系どちらでも復元処理を通るようにしておくと防げます。
配列の次元を勘違いする
Rangeから取得した2次元配列はdata(行, 列)の順序になります。1行だけ、1列だけのRangeでも2次元配列として返ってくるため、うっかり1次元配列として扱おうとするとエラーになります。UBound(data, 1)・UBound(data, 2)で行数・列数をそれぞれ確認する習慣をつけておくと安心です。
まとめ
一番効くのは配列化、次点でScreenUpdatingなどの一時停止。この2つだけでも、体感が変わるレベルの差が出ます。
ただし、体感だけで満足せず、Timer関数で数値を取ってから「効いている」と判断するようにしています。感覚と実測がずれていることは、意外とよくあります。
コメント