Excelのセルを配列に取り込むVBA

16468 ワード
VBA Excel 配列 Excel テキストリンク

VBAで配列を使う理由

せっかく、Excel VBAで処理を自動化しても、1セルずつ入出力していると、処理にかかる時間が長くなってしまう。特に、Excel VBAの場合、セルへの出力に時間がかかるので、大量のデータを処理する場合は、１セルずつ処理をして出力するよりも、配列で処理をして、一括で出力することで、処理の時間を短くできる。

具体例

A列の数字と、B列の数字を掛け算して、100007で割った余りを出力する

処理をするExcel Data

A列とB列に0～10000の乱数を入力 (1行目～50000行目まで)

例1: 一行ずつ処理する場合

simple_code
Public Sub test_case_1()
    Const DIV As Long = 100007
    Dim i As Long
    For i = 1 To 50000
        Range("C" & i).Value = Range("A" & i).Value * Range("B" & i).Value Mod DIV
    Next
End Sub

計算時間は、2.9921875秒
1行ずつ入出力しているので、処理が遅い。

例2: Rangeオブジェクトで処理する場合

using_object
Public Sub test_case_2()
    Const DIV As Long = 100007
    Dim i As Long 
    Dim r_in As Range, r_out As Range
    Set r_in = Range("A1:B50000")
    Set r_out = Range("C1:C50000") 
    For i = 1 To 50000
        r_out(i, 1) = r_in(i, 1) * r_in(i, 2) Mod DIV
    Next 
End Sub

計算時間は、2.3984375秒。
Rangeオブジェクトを使って、一括で情報を入力できるため、少し計算が早くなる。
しかし、1行ずつセルに出力させているので、まだ処理が遅い。一括で出力できると、もっと早くなるはず。

例3: 配列を使って処理する場合

using_array
Public Sub test_case_3()
    Const DIV As Long = 100007
    Dim i As Long
    Dim v_in As Variant, v_out As Variant

    v_in = Range("A1:B50000") ' Setをつけずに、Variant型変数にRangeオブジェクトを代入すると配列になる
    v_out = Range("C1:C50000") ' C1:C50000が空白なら空の配列になる

    For i = 1 To 50000
        v_out(i, 1) = v_in(i, 1) * v_in(i, 2) Mod DIV
    Next
    Range("C1:C50000") = v_out '一括でExcelシートに出力
End Sub

計算時間は、0.140625秒。
入力も出力も、一括して行っているので、処理時間が短い。
入力よりも、Excelワークシートへの出力を一括して行う方が、処理時間を大きく短縮できる。

計算時間比較 (10回分)

test_case 1 test_case_2 test_case_3
2.9921875 2.3984375 　0.140625 　
2.9375 1.9765625 0.109375
2.9921875 1.9765625 0.140625
2.9375 1.9453125 0.140625
2.84375 2.328125 0.1328125
2.9296875 2.046875 0.1484375
3.0078125 1.984375 0.1328125
2.953125 1.921875 0.1953125
3.3359375 1.984375 0.1484375
2.8828125 2.015625 0.15625

配列の使い方

セル範囲を配列に変換

例えば、以下のような3行4列の範囲のRangeオブジェクトを配列に渡すと、3 x 4の配列ができる。

array_intro
Public Sub get_array()
    Dim r As Range
    Dim v As Variant
    Set r = Range("A1:D3")
    v = r 'Rangeオブジェクトの中身が配列に格納される
End Sub

ローカルウィンドウで、配列の中身を見てみると、以下のようになっている。
v(i, j) は i行 j列目の要素に対応していることが分かる。
Variant型なので、String型、Double型、Boolean型も一つの配列に入れることができる。

配列で各要素ごとにデータ処理 (Lbound, Ubound関数)

得られた2次元配列v(i, j)の行数、列数は、Lbound, Ubound関数で取得できる。
Lbound, Ubound関数の第2引数は、要素範囲を取得する次元を指定している。

array_check
Public Sub check_array()
    Dim r As Range
    Dim v As Variant
    Set r = Range("A1:D3")
    v = r
    Debug.Print LBound(v, 1), UBound(v, 1) '配列vは、1行～3行まである → 1, 3が出力
    Debug.Print LBound(v, 2), UBound(v, 2) '配列vは、1列～4列まである → 1, 4が出力
End Sub

得られた配列に、以下の処理をして、Range("A5:D7")に出力してみる
1行目は、大文字に変換
2行目は、5を足す
3行目は、論理否定する

array_proc
Public Sub proc_array()
    Dim r_in As Range, r_out As Range
    Dim v As Variant
    Dim i As Long, j As Long

    Set r_in = Range("A1:D3")
    Set r_out = Range("A5:D7")
    v = r_in
    For i = LBound(v, 1) To UBound(v, 1)
        For j = LBound(v, 2) To UBound(v, 2)
            If i = 1 Then v(i, j) = UCase(v(i, j)) '1行目の処理
            If i = 2 Then v(i, j) = v(i, j) + 5    '2行目の処理
            If i = 3 Then v(i, j) = Not v(i, j)    '3行目の処理
        Next
    Next
    r_out = v

End Sub

出力結果は、以下の通り。

まとめ

Excelのセルの範囲を配列に入出力することは、簡単にできる割にはあまり活用されていない。
処理の高速化にもつながるので、活用できるようになりたい。