配列を経由してコレクションに格納

複数のシートにあるテーブルからデータを集めるために、一つのコレクションに格納する必要があった。そこで、シートと範囲をループさせてデータを収集しようとして、ふと思った。この二つは、どちらが早いのだろう？

1. 各セルから値を取得して、コレクションに格納
2. 一旦配列に値を格納してから、コレクションへ

何となく２．が早いんだろうと思いながらも、どれぐらい差が出るか分からない。
そこで、恐らく世界中で何度となく検証されているであろう上記を確認してみた。
※今回の対象は値のみ。

まずＡ１から数えて１００００行１００列の範囲に、そのアドレスを入力する。

Sub 下ごしらえ()
    Dim r As Range
    For Each r In Range("A1").Resize(10000, 100)
        r = r.Address(False, False)
    Next
End Sub

１．各セルから値を取得して、コレクションに格納する場合

今回も、はけたさんの時間測定コレクションを使わせてもらった。
www.excelspeedup.com
いつも有難うございます。

Sub test_1()
    Dim ET As ElapsedTime
    Set ET = New ElapsedTime
    ET.init ET_MsgBox, "0.0"
    
    Dim col As Collection
    Set col = New Collection
    Dim r As Range
    For Each r In Range("A1").Resize(10000, 100)
        col.Add r.Value
    Next
End Sub

結果：1.6秒

２．一旦配列に値を格納してから、コレクションへ移し替え

Sub test_2()
    Dim ET As ElapsedTime
    Set ET = New ElapsedTime
    ET.init ET_MsgBox, "0.0"
    
    Dim seq As Variant
        seq = Range("A1").Resize(10000, 100).Value
    
    Dim col As Collection
    Set col = New Collection
    Dim s As Variant
    For Each s In seq
        col.Add s
    Next
End Sub

結果：0.5秒

ちみなに、この書き方でも同じ結果が得られた。

Sub test_2()
    Dim ET As ElapsedTime
    Set ET = New ElapsedTime
    ET.init ET_MsgBox, "0.0"
    
    Dim col As Collection
    Set col = New Collection
    Dim s As Variant
    For Each s In Range("A1").Resize(10000, 100).Value
        col.Add s
    Next
End Sub

この結果を、１秒差と見るべきか、３分の１と見るべきかは、良く判らない。端末の性能にもよるだろうし。ただ、値だけを扱うのであれば、一旦配列で受けた方が処理速度に有利なのは間違いなさそうだ。

今後のマクロ作成時、留意するとしよう。

参考まで。