ワークシートからデータを取得する

Excelでは、配列をセル範囲に一括セットしたり、その逆にセル範囲のデータを一括して配列に取り込んだりできます。

ワークシートのデータを配列に読み込むとき、セルからひとつずつ配列に読み込んでいく方法と一括して配列に取り込む方法とを比較して動作速度を測ってみます。

テストプログラムは下記の通りです。

VBEの標準モジュールに貼りつけて実行してみてください。

Public Sub test2()
    Dim rr As Long
    Dim cc As Long
    Dim idx As Long
    Dim aryRows As Variant
    Dim tm1 As Variant
    Dim tm2 As Variant
    Dim aryTest As Variant
    Dim aryRange As Variant
    
    'セル行数の配列を作成
    aryRows = Array(1000, 5000, 10000, 30000, 60000)
    With Worksheets("test1")
        'ワークシート「test1」にテストデータを出力します
        For idx = 0 To UBound(aryRows)
            .Cells.ClearContents
            ReDim aryTest(0 To aryRows(idx) - 1, 0 To 0)
            '配列に数値を格納します
            For rr = 1 To aryRows(idx)
                aryTest(rr - 1, 0) = rr
            Next rr
            '配列をセル範囲に出力します
            .Range(.Cells(1, 1), .Cells(aryRows(idx), 1)) = aryTest
        
                    
            'それぞれのセルからデータを配列に取り込みます
            '午前0時から現在までの秒数
            tm1 = Timer
            ReDim aryRange(0 To aryRows(idx) - 1, 0 To 0)
            For rr = 1 To aryRows(idx)
                aryRange(rr - 1, 0) = .Cells(rr, 1).Value
            Next rr
            '処理時間を保存します
            '小数点表示
            tm1 = Format(Timer - tm1, "0.0######")
            
            '一括して取り込みます
            tm2 = Timer
            aryRange = .Range(.Cells(1, 1), .Cells(aryRows(idx), 1))
            
            '処理時間を保存します
            tm2 = Format(Timer - tm2, "0.0######")
            
            '処理時間を出力します
            Debug.Print aryRows(idx) & "行", "セルごと：" & tm1, "一括：" & tm2
            
        Next idx
    End With
    
End Sub

実行結果は下記の通り。ただし、PCの性能により絶対値は変わってくると思いますので、両者の差のみに注目してください。

一見して明らかなように、配列に一括して取り込む方が断然早いですね。ただし、1000行程度までならどちらでも大差ないようです。

セルをひとつひとつ指定して配列に取り込む方法は、プログラムのロジックが分かりやすく、コーディングも簡単なために、ちょっとやってみるにはいい方法ですが、データ数が多くなるととたん処理時間が長くなってしまいます。ぜひVBAが用意している配列への一括取り込みを使用しましょう。

一