エクセルVBA 配列の速度比較

ExcelのVBAでプログラムを組む際に、大量のデータを処理するのに使うのが配列系の処理である。ただ、プログラムの組み方によっては処理時間に大きく差が出てくるようだ。データ量が少なければよいが、多くなってくると、処理時間が延びてしまい、使い勝手も悪くなる。そこでベストな配列の使い方を考慮してみる。

VBAでは主に配列として使えるのが、静的配列、動的配列、コレクションである。3択になるわけだがどれを選ぶのがよいだろうか?

試しに単純に変数を宣言して、適当なデータ(10,000,000個)を配列に書き込む時間を測定してみた。できるだけ高速で処理時間を短縮できるものを選択したい。

静的配列

Dim Dimtest(10000000) As String
Do
    DoEvents
    Dimtest(i) = "テスト" & i
    If i = 10000000 Then
        Exit Do
    End If
    i = i + 1
Loop

処理時間→35秒

動的配列

Dim Dimtest() As String
Do
    DoEvents
    ReDim Preserve Dimtest(i)
    Dimtest(i) = "テスト" & i
    If i = 10000000 Then
        Exit Do
    End If
    i = i + 1
Loop

処理時間→102秒

コレクション

Dim CL As Collection
Set CL = New Collection
Do
    DoEvents
    CL.Add "テスト" & i
    If i = 10000000 Then
        Exit Do
    End If
    i = i + 1
Loop

処理時間→35秒

動的配列はReDimが入るため処理がかなり遅くなるようだ。静的配列とコレクションは意外にもほぼ同じ処理時間であった。これらの結果からコレクションを使うのがベストだ。コレクションであればAddで簡単にデータを追加していけるし、Countでデータ数もすぐにわかる。（逆に静的配列だと固定の領域が作られているので、データが入っているのか、入っていないのかを確かめないといけないし、データ数も自前でカウントしないといけない）

また、このコレクションを読み出す方法でも、処理時間が違う。（下記は65000データをシートに書き出した処理時間である）

For Eachを使う方法

For Each c In CL
    Workbooks(ThisWorkbook.Name).Sheets(1).Cells(i, 1) = c
    i = i + 1
Next

処理時間→12秒

Forを使う方法

For i = 1 To CL.Count
    Workbooks(ThisWorkbook.Name).Sheets(1).Cells(i, 1) = CL(i)
Next

処理時間→16秒

For Eachを使ったほうが速いという結果が出た。もしかしたら環境によっても変わるかもしれないが、私の環境ではこのような結果となった。

あくまでも参考程度にして欲しいが、少しでも処理時間を短縮したいという方は試してみると良いだろう。