左上セルを指定してテーブルを配列にして返す関数

### セル範囲を決め打ちして固定範囲のテーブルを読み込む

Excelのワークシートにあるテーブルを2次元配列に格納したいことは、VBAでプログラミングしていてよくあると思います。

次のようにテーブルのセル範囲が固定されている場合、

![fixed-table.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/afb243c3-e1fc-bd64-c5d9-6774f5591737.jpeg)

テーブルのセル範囲`B3:D7`（ワークシート1`Sheet1`）を決め打ちしてRangeオブジェクトで配列が読み込めます。

コードと出力結果は次のようになります。

```vbnet:標準モジュール
Sub GetArrayByRange()

  Dim Table As Variant
    
  Table = Sheet1.Range("B3:D7")

  ' 1行目
  Debug.Print Table(1, 1); Table(1, 2); Table(1, 3)
  ' 2行目
  Debug.Print Table(2, 1); Table(2, 2); Table(2, 3)

End Sub
```

![output1.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/bf598435-0431-e442-de86-49bc65ca5bd7.jpeg)


### 左上セルを指定して可変範囲のテーブルを読み込む

セル範囲が固定しているなら上記のようにすればいいのですが、テーブルの行数がデータの追加ごとに増えるとか、行数・列数がプログラムにとって未定のテーブルを読み込みたいときは、テーブルのセル範囲を調べる必要があります。

今、空っぽの`B2`、1行1列の`B6`、3行1列の`B10:B12`、3行3列の`B16:D18`の4つのテーブルをワークシート1`Sheet1`に用意しました。

![variable-table.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/5fd037fe-4c7a-4e51-ae61-a4e2a0372f35.jpeg)

これらのテーブルのような次元（行数、列数）が違うテーブルを、それぞれのテーブルの左上のセルだけを指定して読み込みたいと思います。

```vbnet:標準モジュール
' セルPointCellと同列の最終行のセルを返す
Function GetEndRow(ByRef PointCell As Range) As Range

  If PointCell.Offset(1, 0).Value = "" Then
    ' 1行下のセルが空ならPointCellを返す
    Set GetEndRow = PointCell
  Else
    Set GetEndRow = PointCell.End(xlDown)
  End If

End Function


' セルPointCellと同行の最終列のセルを返す
Function GetEndColumn(ByRef PointCell As Range) As Range

  If PointCell.Offset(0, 1).Value = "" Then
    ' 1列右のセルが空ならPointCellを返す
    Set GetEndColumn = PointCell
  Else
    Set GetEndColumn = PointCell.End(xlToRight)
  End If

End Function


' ワークシートの表の左上のセルLeftTopを引数に表を2次元配列にして返す
Function GetTableAs2dArray(ByRef LeftTop As Range) As Variant

  Dim RightBottom            As Range
  Dim Scalar(1 To 1, 1 To 1) As Variant

  ' 表の右下のセルを求める
  Set RightBottom = LeftTop.Worksheet.Cells(GetEndRow(LeftTop).Row, GetEndColumn(LeftTop).Column)
  ' 表を2次元配列として返す
  If LeftTop.Address = RightBottom.Address Then
    ' Range()は1セルだと配列を返さないので2次元配列Scalarを経由させる
    Scalar(1, 1) = LeftTop.Value
    GetTableAs2dArray = Scalar
  Else
    GetTableAs2dArray = Range(LeftTop, RightBottom)
  End If

End Function
```

引数のセルから使用されている最終行のセルを返すGetEndRow関数、引数のセルから使用されている最終列のセルを返すGetEndColumn関数、これら2つの関数を使ってセル範囲を特定してテーブルを配列として返すGetTableAs2dArray関数の3つを作りました。

#### 空っぽのテーブル

GetTableAs2dArray関数を使ってみます。

まず、空っぽのテーブル（左上セル`B2`）です。

```vbnet:標準モジュール
Sub EmptyTable()

  Dim Table As Variant
    
  Table = GetTableAs2dArray(Sheet1.Range("B2"))

End Sub
```

![output-empty-table.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/d06ad040-a673-1c9c-776d-98c563c125cb.jpeg)

GetTableAs2dArray関数の戻り値Table変数は2次元配列で、値は`Table(1,1) = Empty`となっています。

#### 1行1列のテーブル

次に、1行1列のテーブル（左上のセル`B6`）を読み込みます。

```vbnet:標準モジュール
Sub Table1x1()

  Dim Table As Variant
    
  Table = GetTableAs2dArray(Sheet1.Range("B6"))
  Debug.Print Table(1, 1)

End Sub
```

![output-1x1-table.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/a02007a6-1975-fcb1-b95b-c11773dba7fb.jpeg)

戻り値Table変数はさっきと同様に2次元配列で、値は`Table(1,1) = 11`となっています。


#### 3行1列のテーブル

3行1列のテーブル（左上のセル`B10`、セル範囲`B10:B12`）を読み込みます。

```vbnet:標準モジュール
Sub Table3x1()

  Dim Table As Variant

  Table = GetTableAs2dArray(Sheet1.Range("B10"))
  Debug.Print Table(1, 1)
  Debug.Print Table(2, 1)
  Debug.Print Table(3, 1)

End Sub
```

![output-3x1-table.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/d894cccc-f467-9bcd-c0df-8397d72ec372.jpeg)

戻り値Tableは`Table(1,1) = 11`、`Table(2,1) = 21`、`Table(3,1) = 31`の2次元配列です。


#### 3行3列のテーブル

3行3列のテーブル（左上のセル`B16`、セル範囲`B16:D18`）を読み込みます。

```vbnet:標準モジュール
Sub Table3x3()

  Dim Table As Variant
  Dim RowIndex As Long
  Dim ColumnIndex As Long
  
  Table = GetTableAs2dArray(Sheet1.Range("B16"))
  
  For RowIndex = LBound(Table, 1) To UBound(Table, 1)
    For ColumnIndex = LBound(Table, 2) To UBound(Table, 2)
      Debug.Print "(" & RowIndex & "," & ColumnIndex & ") ="; Table(RowIndex, ColumnIndex)
    Next
  Next

End Sub
```

![output-3x3-table.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/c2ea155d-c1e3-752e-4d4b-6a0efa0ce975.jpeg)

戻り値Tableは

- 1行目 `Table(1,1) = 11`、`Table(1,2) = 12`、`Table(1,3) = 13`
- 2行目 `Table(2,1) = 21`、`Table(2,2) = 22`、`Table(2,3) = 23`
- 3行目 `Table(3,1) = 31`、`Table(3,2) = 32`、`Table(3,3) = 33`

の2次元配列です。


#### 関数のポイント

こんな感じで、任意のm行n列のテーブルを2次元配列で返すGetTableAs2dArray関数を作って、使い方のサンプルを載せました。

この関数は、引数のセルが空でも、1行1列（1つの値）でも、2行以上2列以上のテーブルとなっていても、2次元配列`Table(i, j)`を返します。

戻り値の変数の次元が決まっているので、戻り値`Table`を使うときはいつでもLboundとUboundを使ったForループでOKです。（LboundやUboundは配列でないとエラーを出します）

```vbnet:標準モジュール
For RowIndex = LBound(Table, 1) To UBound(Table, 1)
  For ColumnIndex = LBound(Table, 2) To UBound(Table, 2)
    Table(RowIndex, ColumnIndex)
  Next
Next
```

#### ちょっと応用

[ワークシート内のセルを検索してDictionaryオブジェクトに蓄積する関数 - Qiita](https://qiita.com/kosukemaeda/items/23fa771c2799a75ed048)と組み合わせると、次のようなワークシートの複数のテーブルを一括処理できます。

![tables.jpg](https://qiita-image-store.s3.amazonaws.com/0/197109/e71980cc-8d01-c173-60d1-16aefbd94177.jpeg)

各テーブルの近くに（上記の例ではテーブルの左上セルの1つ上）目印となる文字列を入力しておきます。

次のように目印のセルを全て検索して、蓄積し、ループで1つずつテーブルを処理します。

```vbnet:標準モジュール
Sub MultiTable()

  Dim FoundCellSet As Dictionary
  Dim CellAddress  As Variant
  Dim Table        As Variant

  ' 引数1のワークシート内の引数2と完全一致するセルを全て探してFoundCellSetに代入する
  Set FoundCellSet = FindCells(Sheet1, "[Table]")

  ' 見つかったセルからテーブルを2次元配列で読み込み処理する
  For Each CellAddress In FoundCellSet
    ' テーブルをセットする
    Table = GetTableAs2dArray(FoundCellSet(CellAddress).Offset(1, 0))
    ' テーブルに何らかの処理をする
  Next

  Set FoundCellSet = Nothing

End Sub
```

複数のテーブルで同じ処理がしたい、テーブルはそれぞれ行数・列数が異なるという場合にかなり有効だと思います。例えば、私は、ワークシート上のテーブルを一気にSQLiteデータベースに追加したいというときにこのルーチンを使っています。