#はじめに
ExcelVBAを使ってとある処理をしようとしたところ、配列の使い方が良くなかったために少し失敗したので、情報を共有したいと思います。
先に結論を述べてしまうと、ReDim Preserveを大量に使用すると、処理が遅くなるようでした。
#処理の概要と原因
- テキストファイルを読み込み、配列その1にデータを格納
- 配列その1のデータのうち、一定条件に合うもののみ配列その2に移行
- 配列その2のデータを配列その3に移行
- 配列その3のデータをワークシートに書き出し
このような処理を行うコードを書いたところ、配列1から2に移すデータの件数が多い場合に、なぜか時間がかかってしまいました。
そこで各処理の時間を測ってみると、以下のようなことがわかりました。
- 1は結構早い。そもそも、常にテキストを全て読み込んでいるので、「移行件数が多いときに時間がかかる」という事象の原因にはならない。
- 3は同じサイズの配列を2つも作ってデータをやりとりしており、無駄な処理をしているようにみえる。しかし、時間は全然かかっていない。
- 4はそれなりに時間がかかる。ExcelVBAを使う上で必ず課題になる「画面描画」を行うため、ある程度時間がかかるのは仕方ない。しかし、今回は、時間がかかる主な原因にはなっていないようである。
つまり、2のプロセスに問題があるようです。
#詳しく
2のプロセスで、配列その2に移行するデータの件数が不確定であることから、配列その2は「初めは最小サイズにしておき、格納するデータがあれば少しずつサイズを大きくしていく」というプログラムにしていました。
どうもこのために動作が遅くなってしまったようでした。
以下、簡単な検証をしてみます。
Sub test()
Dim arr() As Variant
Dim i As Long
For i = 1 To 100000
ReDim Preserve arr(i)
arr(i) = i
Next i
End Sub
10万回ループを行い、10万個の要素を配列に格納しています。
要素を格納する前に、毎回配列のサイズを1個分ずつ広げています。
ループ回数を増やして時間を計測したところ、以下のような結果になりました。
- ループ100,000回：1秒未満
- ループ500,000回：約4秒
- ループ1,000,000回：約16秒
どんどん遅くなっていきます。
実際のプログラムでは2次元配列を使用していたので、2次元配列でテストしてみます。
Sub test()
Dim arr() As Variant
Dim i As Long
For i = 1 To 100000
ReDim Preserve arr(1, i)
arr(0, i) = i
arr(1, i) = i
Next i
End Sub
結果は以下のとおりです。
- ループ100,000回：1秒未満
- ループ500,000回：約16秒
- ループ1,000,000回：約69秒
とても実用レベルには至らない実行時間になってしまいました。
#改善
初めから大きな配列を作って値を格納し、最後にサイズを調整する方法に変えてみました。
Sub test()
Dim arr() As Variant
ReDim arr(10000000)
Dim i As Long
For i = 1 To 100000
arr(i) = i
Next i
ReDim Preserve arr(i - 1)
End Sub
最初に1千万個分の大きさの配列を作り、最後に、実際に格納された要素数分のサイズに縮めています。
(最初の配列のサイズをこんなに大きくする必要はないのですが・・・テスト用に適当なサイズにしているだけです。また、配列のインデックスは0から始まるので、厳密には、サイズは要素数+1個分になります。)
結果は以下のとおりです。
- ループ100,000回：1秒未満
- ループ500,000回：1秒未満
- ループ1,000,000回：1秒未満
- ループ10,000,000回：1秒未満
2次元配列も試してみます。
Sub test()
Dim arr() As Variant
ReDim arr(10000000)
Dim i As Long
For i = 1 To 100000
arr(0, i) = i
arr(1, i) = i
Next i
ReDim Preserve arr(1, i - 1)
End Sub
結果は以下のとおりです。
- ループ100,000回：1秒未満
- ループ500,000回：1秒未満
- ループ1,000,000回：1秒未満
- ループ10,000,000回：約1秒
おお、早くなりました。
#おわりに
ReDim Preserveを多用すると処理が遅くなることがわかりました。
配列の要素数が予め特定できない場合は上記のような工夫をしたり、そもそも配列ではなく、CollectionやDictionaryを使用した方が良いですね。