VBAでDictionaryのデータを走査するループを速くする方法

久しぶりにVBAのプログラムを書いていて、ループの書き方で速度がものすごく違うのにびっくりしたのでメモ。

VBAでもDictionaryクラスを使うことができる。それをループで回すときの違い。



まず１万個のデータを作る。

Dim di As Dictionary
Set di = New Dictionary
   
For i = 1 To 10000
    key = "ABC" & i
    di(key) = i
Next

このデータをワークシートのA列、B列に書き込むループを書く。

ループ１： インデックス変数を使う

Dim ws As Worksheet
Set ws = Application.Worksheets("Sheet2")

For i = 0 To di.Count - 1
    key = di.Keys(i)
    ws.Cells(i + 1, "A") = key
Next

ループ２：For Each を使う

Dim ws As Worksheet
Set ws = Application.Worksheets("Sheet2")

i = 0
For Each v In di.Keys
    i = i + 1
    key = v
    ws.Cells(i, "B") = key
Next

ループ１とループ２の処理時間を計ってみると、驚くほど差があった。

• ループ１　６３秒
• ループ２　２秒

１万個のデータをワークシートに書き込みをするのに２秒かかっているようで、ループ１のループを下記のように書き換えても６０秒かかる。

For i = 0 To di.Count - 1
    key = di.Keys(i)
Next

ということで、DictionaryクラスではKeysメソッドは時間がかかるようだ。

VBAでプログラムを書くことは、もう、そんなにないだろうけど。