処理が遅いVBAコードを改善するには「セル単位で処理をする」のをやめよう

xlsx形式エクセルファイルでは最大行数が 1,048,576 行まで拡張されたため、従来はRDBに保存されているような巨大な業務データを扱うこともできるようになりました。

Webアプリを作成していて常々思うのですが、Excelは100万行近くデータを表示させているのに全く遅くならないというのは凄いと思います。HTMLでのテーブルを表示は1万行でもハングしてしまいそうです。

データが大量になったEXCELに対してVBAを使う場合には、当然開発者も処理速度を考慮したプログラミングが必要となります。いくらEXCELが優秀だと言ってもVBAコードが駄目であれば、処理時間が大変なことになります。

過去に対応した案件では、「あまりにも時間がかかるので、処理を実行したまま帰宅している」と言われているアプリもありました。

そのようなアプリを作ってしまわないように、私がいつも実施している手順について紹介したいと思います。

基本中の基本「描画と計算を止める」

ある程度重い処理をするVBAであれば、「描画と計算を止める」は、ほぼ必須ですね。言葉の意味は以下の通りです。

描画を止める→VBA実行中に画面の表示が変化しない
計算を止める→参照セルの値が変更になっても、関数が再計算されない

描画と計算が止められていないコードでは、止まっているコードの何倍も時間がかかることがあります。特にセルに書き込んだりする処理があり、その値を計算式が参照していたら目に見えて処理が重くなります。

描画を止める処理と計算を止める処理は以下のコードです。

    Application.ScreenUpdating = False  '描画を停止
    Application.Calculation = xlCalculationManual   '計算を停止

これを処理の開始時に実行しておけば、性能面では相当改善されるはずです。

描画や計算を停止したままだと、VBA終了後にユーザーがEXCELを使えないので、処理の終了時に以下のコードで忘れずに再開します。

    Application.ScreenUpdating = True  '描画を再開
    Application.Calculation = xlCalculationAutomatic   '計算を再開

ちなみに、すぐに終わるような処理だったとしても、少なくとも描画は止めておくべきだと思います。

セルに値が書き込まれたりシートを遷移するような様子をユーザーに見せるのは格好悪いからです。

私は考えるのも面倒なので、VBAでコードを書く際には必ず描画や計算の停止と再開は実行するようにしています。

Dictionaryは重いので注意が必要

Dictionary(連想配列)は非常に便利であるため、私も必ずと言って良いほど使用します。

しかし、このDictionaryは格納されているItemの数が膨大になると検索に非常に時間がかかるようになるため注意が必要です。

とは言え、1万とかそんなものであれば全然余裕かなと思っています。

問題は10万の大台を超えてくるときですね。（そもそもメモリが大丈夫かも心配になりますが…）

こうなると処理が非常に遅くなってくるので、オブジェクトを分割したりしています。

Collectionで代替してみるというのも案だと思ったのですが、下記のサイトによると大して速度は変わらないらしいです。

https://qiita.com/pregum/items/071f72969d72d90cf826

Collectionの方にもあるが、Dictionaryの方が便利な機能があるのと、処理速度がDictionaryの方が若干早いのでこちらを使う

セル単位で処理せず、Rangeを有効に使う

ここからが本題です。

書き込みや読み込みが多い場合、Cellsを使用せずにRangeを使うのが非常に有効です。

私は、もとは10時間以上かかっていたVBAマクロを、この方法に切り替えて30分程度まで短縮できた経験があります。

そのくらい劇的に変わることもあるので、困った場合は一度は試してもらいたい方法です。

例として、A列の値を2倍してB列に設定するようなマクロを考えます。

このマクロをCellsで書いた場合は…

Sub test()
    For i = 1 To 3
        Cells(i, 2).Value = Cells(i, 1).Value * 2
    Next i
End Sub

こんな感じになると思います。次にRangeで書いた場合は…

Sub test()
    Dim v As Variant
    Dim v2 As Variant
    Dim i As Integer
    
    Set v = Range("A1:A3")
    ReDim v2(2, 0)
    
    i = 0
    For Each c In v
        v2(i, 0) = c.Value * 2
        i = i + 1
    Next c
    Range("B1:B3") = v2
End Sub

微妙に長くなってしまいましたがこんな感じです。

シートを読み込むときは、範囲を設定してRange型で一気に読み込む。※Set v = Range(“A1:A3”)の部分

シートを書き出すときは、配列に値を設定して、それをシートに一気に書き出す。 ※Range(“B1:B3”) = v2の部分

1つ1つセルごとに処理するより格段に早くなります！！

途中「c.value」のところでCellsオブジェクトを呼び出しているではないかと思われるかもですが、シートの読み書きで使わないのが重要であって、上記は問題ありません。

おそらくセルにアクセスする処理が、そこそこ時間がかかる処理で、 Rangeでその処理をループしなくて済むため早くなっているのではないかと予想しています。

問題はソースの可読性があまり良くないということですね。

私は「描画と計算を止める」は無条件で実施していましたが、こちらはできる限りCellsの方で書いて、性能問題がある場合だけRangeの方法を使っています。