ワークシートでもVBAでもテーブルは便利(^o^)
EXCELに慣れてくると、ワークシート上のデータを扱うときに”テーブル形式”を使うようになると思います。
数式が自動でコピーされたり色々便利ですよね(^o^)
もちろんVBAでもワークシートの”テーブル”を扱う機能が用意されています。
とっても便利なので紹介させていただきます!
ListObject(リストオブジェクト)
ワークシート上のテーブルは分かると思いますが、念の為。

ワークシート上にある表の中に、アクティブセルを置き、「挿入タブ>テーブル」で作れますね。
この”テーブル”ですが、VBAで言うとテーブルにした時点で、”ListObject(リストオブジェクト)”というオブジェクトが作られます。
VBAのプログラムから、テーブルを扱う場合は、このListObjectを捕まえることになります。
※VBAからテーブルを作成する方法についてはこちら
テーブルを捕まえる
まずは、このテーブルの捕まえ方です。
テーブルを捕まえるには、いくつか方法があります。
- テーブルのセルから指定する
- テーブルがあるシートから指定する
- テーブルの名前だけで指定する
です。
1つずつ見ていきましょう!
テーブルのセルから指定する
テーブル内のセルから、指定する場合は、テーブルがあるセル(テーブル内ならどこでもOK)に続けて、ListObjectを指定します。
Range(“A1”).ListObject
Sub sample()
Dim テーブル As ListObject
Set テーブル = Range("A1").ListObject
End Sub

こんな感じです。
Range(“A1”)は、Range(“B4”)でも、Range(“H14”)でも、テーブルの範囲内ならどこでもOKです。
テーブルがあるシートから指定する
シートから指定するには、シートオブジェクトからListObjects(インデックス)か、 ListObjects(テーブル名)で指定しいます。
シートオブジェクトから指定する場合は、ListObjyec”s”と複数形になっていることに注意してください。
インデックスで指定する場合
シート.ListObjects(1)
Sub sample()
Dim シート As Worksheet
Set シート = ActiveSheet
Dim テーブル As ListObject
Set テーブル = シート.ListObjects(1) ’1つ目に作成したテーブルを指定(インデックスは1から)
End Sub
同じシート上にあるテーブルには、作られた順番にインデックスが振られています。ListObjectsに続いてインデックスをしてすることで指定のテーブルを取得出来ます。

テーブルのインデックスは、テーブルが消されたときに繰り上がりますので注意してください。
例えば、1つのシートにテーブルが3つあった場合は、インデックス2のテーブルを削除すると、インデックス3のテーブルが、インデックス2になります。
テーブル名で指定する場合
シート.ListObjects(“テーブル1”)
Sub sample()
Dim シート As Worksheet
Set シート = ActiveSheet
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1") ’テーブル名を指定する
End Sub
テーブルには1つ1つにテーブル名が付けられています。ワークシート上の「テーブルデザインタブ>テーブル名」で確認することが出来ます。テーブルデザインタブは、確認したいテーブルの範囲内にアクティブセルを置くことで表示されます。

テーブル名は変更出来るので、扱いやすい名前に変えておくと便利です。
テーブルの名前だけで指定する
こちらについては、構造化参照という方法とセットで後ほど説明します。
テーブル内のデータを取得する(見出し・データ・行・列)
テーブルは、「見出し」「データ」「行」「列」で構成されていますが、それぞれの部位を指定することが出来ます。

テーブル全体(見出し・データ・行・列)の取得
見出しとデータ、つまりテーブルそのものの取得
ListObject.Range
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'テーブル全体のデータ
Dim rng As Range
Set rng = テーブル.Range ’ここ ListObject.Range
End Sub

見出しの取得
見出し(タイトル部分)の取得
ListObject.HeaderRowRange
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'タイトル部分の取得(データ含まない)
Dim rng As Range
Set rng = テーブル.HeaderRowRange ’ここ ListObject.HeaderRowRange
End Sub

データ部分の取得(見出し含まない)
見出し(タイトル部分)の取得
ListObject.DataBodyRange
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'データ部分の取得(見出し含まない)
Dim rng As Range
Set rng = テーブル.DataBodyRange ’ここ ListObject.DataBodyRange
End Sub

列の取得
”見出し含む”列の取得
複数ある列を集めた、ListColumnsコレクションから、特定の列をインデックスか列名で指定します。
ListColumnオブジェクトになります。
上記は、列そのものになりますので、中の実データを指定する場合は、更に.Rangeを続けます。
こちらは、Rangeオブジェクトです。
ListObject.ListColumns(インデックス).Range
ListObject.ListColumns(列名).Range
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'列の取得
Dim col_data As ListColumn
Set col_data = テーブル.ListColumns(1) 'インデックスで取得(3)は性別列
'列のデータを取得
Dim rng As Range
Set rng = col_data.Range '列の実データを取得
End Sub
インデックスで指定する場合は、左の列から1、2、3となります。
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'列の取得
Dim col_data As ListColumn
Set col_data = テーブル.ListColumns("性別") '列名で取得
'列のデータを取得
Dim rng As Range
Set rng = col_data.Range '列の実データを取得
End Sub
列名で指定する場合の列名は、見出しの行に入っている項目名がそのまま列名になっています。

”列そのもの”を取得してから、”列の中の実データ”を取得するというのが分かりにくいので注意してください。
”見出しを抜いた”列データの取得
ListColumn.Rangeで取得したデータは、見出しの入った列のデータになっています。
ここから、見出し無しのデータを取得するには、ListColumnに対して、.Rangeではなく、.DataBodyRangeを使います。
テーブル全体に対して、データ部分のみ取得した時と同じ方法ですね。
ListObject.ListColumns(インデックス).DataBodyRange
ListObject.ListColumns(列名).DataBodyRange
※ListObject.ListColumns(列名).DataBodyRangeの方だけサンプル付けます。
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'列の取得
Dim col_data As ListColumn
Set col_data = テーブル.ListColumns("性別") '列名で取得
'列のデータを取得
Dim rng As Range
Set rng = col_data.DataBodyRange '列の実データを取得(見出し無し)
End Sub

行の取得
”見出し含む”行の取得
複数ある行を集めた、ListRowsコレクションから、特定の列を行番号で指定します。
ListRowオブジェクトになります。
※ListRowsコレクションには、見出し行は含まれません。
ListObject.ListRows
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'行データの取得
Dim row_data As ListRows
Set row_data = テーブル.ListRows
End Sub

上記のRowsコレクションにインデックスを指定して、それぞれの行を指定します。
列の時と同じように、これは行そのものなので、中のデータを取得する場合は、.Rangeを続けます。
ListObject.ListRows(行番号).Range
Sub sample()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Dim テーブル As ListObject
Set テーブル = sheet.ListObjects("テーブル1")
'行データの取得
Dim row_data As ListRows
Set row_data = テーブル.ListRows
'3行目のデータを取得する
Dim rng As Range
Set rng = row_data(3).Range
End Sub
行番番号は、見出しを含まずに1から始まります。

構造化参照を使ってテーブルを指定する
先程飛ばした、テーブルを捕まえる3つ目の方法です。
ListObjectではなく、Rangeを使って捕まえます。
テーブルの名前だけで指定する
Range(テーブル名)の形で指定します。
Range(“テーブル1”)
Sub sample()
Dim テーブル As Range
Set テーブル = Range("テーブル1")
End Sub
この場合、型がRangeになりますので変数の型指定など注意してください。

また、ListObjectでは無いので、特定の列やデータを指定するときに、ListObjectと違いがあるので注意する必要があります。
構造化参照について
この方法で、テーブルの部位を指定する場合は、構造化参照を使用します。
構造化参照とは、
ワークシート上だと次のような感じです。
=テーブル1[@ふりがな]

「やすい けんいち」がセルに表示されます。

「@」は、数式を入れたセルと同じ行という意味になります。※VBAでは使えません
見出しの参照
=テーブル1[#見出し]

見出し(一部)の参照
=テーブル1[[#見出し],[ふりがな]:[年齢]]

列の参照(見出し含む)
=テーブル1[ [ #すべて ], [ 名前 ] ]

列の参照(データのみ)
=テーブル1[ 名前 ]

テーブル全て(見出し含む)
=テーブル1[ #全て ]

テーブル全て(データのみ)
=テーブル1

年齢列の平均値を出す場合
=AVERAGE(テーブル1[年齢])

確定すると、平均値が表示される

このように数式の中でも指定出来ます。
つまり、
Range(“テーブル1”)
ですね。
=テーブル1
と同じになりますので、見出しを含まないデータ全体であることが分かりますね。
ただし、ここまで紹介したようにワークシート上の構造化参照は
[#すべて] [#データ] [#見出し] [#集計]のように、日本語で表記されていましたが、VBAの場合は日本語は使えません。
[#すべて] → [#All]
[#データ] → [#Data]
[#見出し] → [#Headers]
[#集計] → [#Totals]
といったように、全て英語表記になりますので注意してください。
構造化参照を使った、各部位の指定のサンプルです。
テーブル全体の取得(見出し業を含む)
Range(”テーブル名[#All]”)
Sub sample()
'テーブル全体の取得(見出し行含む)
Dim rng As Range
Set rng = Range("テーブル1[#All]")
End Sub

テーブル全体の取得(データのみ)
Range(”テーブル名”)
または
Range(”テーブル名[#Data]”)
Sub sample()
'テーブル全体の取得(見出し行含む)
Dim rng As Range
Set rng = Range("テーブル1")
End Sub

見出し行の取得
Range(”テーブル名[#Headers]”)
Sub sample()
'テーブル全体の取得(見出し行含む)
Dim rng As Range
Set rng = Range("テーブル1[#Headers]")
End Sub

列の取得(見出し含む列全体)
Range(”テーブル名[[#ALL], [列名]]”)
Sub sample()
'テーブル全体の取得(見出し行含む)
Dim rng As Range
Set rng = Range("テーブル1[[#All], [性別]]")
End Sub
列の取得(データのみ)
Range(”テーブル名[列名]”)
または
Range(”テーブル名[[#Data], [列名]]”)
Sub sample()
'テーブル全体の取得(見出し行含む)
Dim rng As Range
Set rng = Range("テーブル1[[#Data], [性別]]")
End Sub

行の取得
構造化参照を使った、行の取得は出来ません。
まとめ
今回は、VBAでのテーブル操作の基本となる、テーブル全体や各部位の指定方法を紹介しました!
次回以降で、フィルターや行や列の挿入や削除について説明したいと思います!
コメント