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


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

#具体例

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

### 処理をするExcel Data
A列とB列に0～10000の乱数を入力 (1行目～50000行目まで)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80231/7a18bace-5da6-18e3-a5db-fbcb5a8ac138.png)

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

```vb: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オブジェクトで処理する場合

```vb: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: 配列を使って処理する場合

```vb: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の配列ができる。
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80231/5fbe2c88-0ed4-7376-ddd6-d43ab129e2ed.png)

```vb: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型も一つの配列に入れることができる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80231/1030b167-f412-ec9e-3921-6b7f295a1811.png)

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

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

```vb: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行目は、論理否定する

```vb: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
``` 

出力結果は、以下の通り。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80231/0e438674-484d-d6f6-25ac-4f647cfc8f52.png)

# まとめ

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