テーブルを扱う

Excelには“テーブル”という考えがある。
とても便利な機能であるのに、とっつきにくいのか使っている人は非常に少ないのが残念だが。
このテーブルに対応するオブジェクトがListObjectである。
扱い方としてはいくつかあるが、ListObjectのメンバにRangeがあるので、これでRange型に落とし込む手がある。
Dim a as ListObject
Dim r as Range
<aにテーブル(ListObject)をいれる>
r = a.Range
のように書けるので以後はRange型として扱えば良い。
VBAで表の範囲を決めるのはどうしているだろうか。
例えばRange("A1:B24")等と直接プログラムに書いていないだろうか。
もう少し気を利かせればプログラムで表を調べるだろうか。その場合も“左上”は決めないとうまくいかない。 いずれにしろ、表を移動させたりする時はそれに応じてなにかを書き換えなければならない。
しかし、テーブルとして扱えば“筋の良いやり方”でメンテナンス性が上がる。

テーブルというのは“テーブルの名前”で認識することができる。

前置きが長くなったが、そもそも上記のListObjectを得るにはどうするか説明をする。
ListObjectsはWorksheetのメンバである。
ListObjectはListObjectsのひとつになる。
ここではThisWorkbookからListObjectを手繰ってみる。
Dim sh As Worksheet
Dim Lobj As ListObject
Dim r As Range
Set sh = ThisWorkbook.Worksheets("Sheet1")
For Each Lobj In sh.ListObjects
    Set r = Lobj.Range
    Debug.Print Lobj.Name & vbTab & _
    r.Address(False, False) & vbTab & _
    r.Column & ":" & r.Row & _
    "(" & r.Columns.Count & "x" & r.Rows.Count & ")"
Next
これでSheet1にあるすべてのテーブルの名前と範囲とそのサイズを表示する。
プログラム中で見慣れないものとしてAdrress(False,False)があるが、これは例えばA2:E10みたいなセル範囲を示す文字列を返してくれる。
Columns.Countは行数、Rows.Countは列数となる。

テーブルの設定を正しくやってさえいれば、表を“スキャン”するまでもなく、データの範囲を知ることが出来る。
これはプログラムを複雑にしないし、より動作が高速になる。

別の見方をすれば、テーブルの設定によってプログラムに扱うべきデータの範囲を正しく知らせることができる、とも言える。
VBA云々の前に“テーブルを使う”ことはExcelを使うスキルの一つだから、他の人にも使ってもらう(業務引継なども含む)システムを作る上で重要なポイントである。

このブック内のテーブルをすべて表示させてみよう。
Dim sh As Worksheet
Dim Lobj As ListObject
Dim r As Range
For Each sh In ThisWorkbook.Worksheets
    For Each Lobj In sh.ListObjects
        Set r = Lobj.Range
        Debug.Print "Worksheet:" & sh.Name & vbTab & _
         "Table:" & Lobj.Name & vbTab & _
         "Range:" & r.Address(False, False)
    Next
Next
このブック(ThisWorkbook)内のすべてのテーブルとその範囲を表示させている。
これを応用すれば名前でテーブルを探し出すことが出来る、つまりシートの名前を変えても動くようにもに出来る。

テーブル全体をRangeとしても問題ないがもう少し楽な方法もある。
テーブルには“見出し行”とデータ本体という区別がある。
VBAでもこの二つは区別されていて、見出し行はHeaderRowRange、データはDataBodyRangeという名前で参照出来る。
Dim sh As Worksheet
Dim Lobj As ListObject
Dim head, body As Range
For Each sh In ThisWorkbook.Worksheets
    For Each Lobj In sh.ListObjects
        Set head = Lobj.HeaderRowRange
        Set body = Lobj.DataBodyRange
        Debug.Print "Worksheet:" & sh.Name & vbTab & _
        "Table:" & Lobj.Name & vbTab & _
        "Header Range:" & head.Address(False, False) & vbTab & _
        "Data Range:" & body.Address(False, False)
    Next
Next

Rangeで扱えるのは良いが、見出し名で引用できないかと思うのが普通だろう。
検索してみたがスッキリした方法がない。
これくらいなら許容範囲かと思うのでメモしておく
テーブルの見出しとして"first"があるとする。
データ部分の1行目の値を引き出すには以下のようにする。
Dim Lobj As ListObject
Set Lobj = ThisWorkbook.Worksheets("Sheet1") _ .ListObjects("TableA")
Debug.Print getItem(Lobj, "first", 1)
End Sub
Function getItem(obj As ListObject, s As String, i As Integer)
getItem = obj.ListColumns.Item(s).DataBodyRange.Item(i).Text
End Function
-