【VBA】処理が遅い本当の原因｜シート操作が重くなる仕組み

今回から数回に分けて、Excel VBAの「ソート」「配列」「連想配列(Dictionary)」について書いていきます。

きっかけは新卒時代の出来事です。他部署の方と雑談していたときに、「重くて困っているマクロがある」と相談を受けました。正式に依頼された案件ではなく、話の流れで内容を見てみることになったものです。

ヒアリングと実際のコードを確認してみると、作業自体の難易度はそれほど高くありませんでした。保有車両などの情報がすべて行データとして出力されるCSVから、顧客データと車両データの両方がそろっている行だけを抽出し、並び替えるという内容です。

ただし、並び替えの工程に問題がありました。6万行近いCSVをいったんシートに貼り付け、シート上でソートを実行する作りになっていたのです。しかもコードは他の方が作成したもので、「マクロの記録」で作ったコードに近い、整理されていない内容でした。これを4ファイル分繰り返すため、当然ながら処理は重く、下手をすると半日近くかかっていました。

シート上での並び替えが遅さの原因だとは当時から分かっていたため、独学でクイックソートと連想配列(Dictionary)を調べて書き直したところ、処理時間は数分まで短縮できました。

今はコードを自分で書く機会は減りましたが、AIに実装を任せる場面が増えたからこそ、「何をしているか」くらいは理解しておきたいと考えるようになりました。そこで、当時の記憶をたどりながら、基礎から整理し直してみることにします。

第1回となる今回は、「なぜシート上の操作は遅くなるのか」という原理の部分を扱います。具体的なソートの実装比較は次回以降で取り上げます。

シート操作が遅くなる主な原因
まとめ

シート操作が遅くなる主な原因

VBAでシート(セル)を操作するコードが遅くなる原因は、大きく3つに分けられます。

セル1つひとつのやり取りにコストがかかる
画面の再描画が都度発生する
セルを書き換えるたびに再計算が走る

一つずつ見ていきます。

セル1つひとつのやり取りにコストがかかる

VBAからセルの値を読み書きするとき、そのたびにワークシートに対してアクセス要求が発生します。ループの中でCells(i, 1).Valueのような処理を6万回近く繰り返すと、その回数分だけシートとのやり取りが発生することになります。

一方、値をいったんVBAの配列(メモリ上のデータ)に読み込んでしまえば、その後の処理はメモリ内で完結します。配列同士の比較や入れ替えは、シートとのやり取りに比べてはるかに高速です。

実際に、2万行に対して「セルに1つずつ書き込む」パターンと「配列に格納してから一括で書き込む」パターンを比べる、次のようなコードで確認してみます。

Sub デモ_セル操作と配列操作の速度差()

    Dim ws As Worksheet
    Dim i As Long
    Const 件数 As Long = 20000
    Dim 開始時刻 As Double
    Dim 所要時間A As Double, 所要時間B As Double
    Dim データ() As Variant

    Set ws = ActiveSheet

    '--- パターンA：セルに1つずつ書き込む ---
    開始時刻 = Timer
    For i = 1 To 件数
        ws.Cells(i, 1).Value = i
    Next i
    所要時間A = Timer - 開始時刻

    ws.Cells.ClearContents

    '--- パターンB：配列に格納してから一括で書き込む ---
    ReDim データ(1 To 件数, 1 To 1)
    開始時刻 = Timer
    For i = 1 To 件数
        データ(i, 1) = i
    Next i
    ws.Range(ws.Cells(1, 1), ws.Cells(件数, 1)).Value = データ
    所要時間B = Timer - 開始時刻

    Debug.Print "セルに1つずつ書き込み: " & Format(所要時間A, "0.000") & " 秒"
    Debug.Print "配列に格納→一括書き込み: " & Format(所要時間B, "0.000") & " 秒"

    MsgBox "セルに1つずつ: " & Format(所要時間A, "0.000") & " 秒" & vbCrLf & _
           "配列で一括: " & Format(所要時間B, "0.000") & " 秒", vbInformation, "処理時間の比較"

End Sub

F8キーでステップ実行すると、セルへの書き込み処理が実行されるタイミングで、該当行が黄色くハイライトされます。

実行すると、次のような結果になります。

セルに1つずつ書き込むと約0.5秒かかったのに対し、配列に格納してから一括で書き込むと約0.02秒で終わりました。同じ処理でも、書き込み方法によって30倍以上の差が出ています。

画面の再描画が都度発生する

既定の設定では、セルの値が変わるたびに画面が再描画されます。1回あたりは気づかないほどの処理でも、6万行分繰り返すと無視できない時間になります。Application.ScreenUpdatingをFalseにすると、処理が終わるまで再描画を止められます。

同じ2万行への書き込みで比較すると、ScreenUpdating=Trueのままで約0.67秒、Falseにすると約0.58秒でした。他の要因に比べると差は小さいものの、効果は確認できます。

画面更新を止めている間は、処理が進んでいるのかどうかが画面から分かりません。非モーダルのUserFormにラベルで進捗を出す方法は、【VBA】非モーダルUserFormで進捗表示を作る方法にまとめています。

セルを書き換えるたびに再計算が走る

シートに数式が入っている場合、セルの値が変わるたびに再計算が実行されます(既定の自動計算モード)。書き込み先のセル自体に数式がなくても、同じシート上や別シートに数式があれば、書き込みのたびに再計算の対象になります。Application.CalculationをxlCalculationManualに切り替えることで、処理中の自動再計算を止められます。

数式を用意した状態で試すと、自動計算のままでは約6.93秒、手動計算に切り替えると約0.45秒でした。数式が絡む場面では、この設定の影響がいちばん大きく出ます。

※ 上記の秒数はいずれも検証環境での参考値です。PCのスペックやExcelのバージョンによって変わります。

これらの設定は、処理の途中でエラーが起きると元に戻らないことがあります。エラーで止まっても確実に戻す書き方は、【VBA】エラー時も設定を元に戻す｜ScreenUpdating等の後片付けで紹介しています。