今回は、VBAでListObject(テーブル)を扱う際の基礎知識を解説します。
ListObjectとは何か
Excel VBAでのListObjectは、Excelのテーブルをプログラム的に参照し操作するためのオブジェクトです。Excelのシートにデータテーブルを作成すると、これをListObjectとしてVBAコードからアクセスし、管理することができます。
念のため、テーブルというのは以下の画像の右側のようなものです。左側の状態も、内容はテーブルになっていますが、右側のようにExcelでテーブルとして設定することで、関連するデータを行と列の形式で整理し、管理しやすくするための機能です。
ListObject(テーブル)の作成とアクセス
まずは、テーブルの作成とアクセスの方法について説明します。ここでいうアクセスというのは、あるテーブルをVBA上で使えるようにする前準備だと思ってください。テーブルに対して何か操作をするには、まず下記のようにSetステートメントを用いてアクセスする必要があります。Setステートメントについては以下を参照ください。
既存のテーブルにアクセスする
最もよく使われる方法として、既に作成済のテーブルにアクセスするには、以下のようにテーブルの名前を使用します。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")このコードでは、”Table1″ という名前のテーブルにアクセスしています。なお、テーブル名は以下のようにExcel上で確認することができます。
既存の範囲をテーブルに変換する
すでにデータが入力されている範囲をテーブルに変換する場合、以下のようにコードを記述できます(A1:D20にデータが入っているケース)。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange,
Source:=ActiveSheet.Range("A1:D20"), XlListObjectHasHeaders:=xlYes)
tbl.Name = "Table1"ListObject(テーブル)の構成要素
Excelのテーブル（ListObject）は、データを整理しやすくするためにいくつかの重要な構成要素を持っています。VBA学習の初期には必要のない知識かもしれませんが、テーブルをVBAで高度に扱うならば、これらの要素を理解することで、データの操作がより柔軟に、効率的に行えるようになります。
Header、DataBody、とは何か
Header
Headerはテーブルの1行目の見出し行のことです。例えば、後述のHeaderRowRangeプロパティを使って以下のようにすることで、テーブルのヘッダーを選択できます。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.HeaderRowRange.SelectDataBody
DataBodyは実際のデータが含まれるテーブルの主要部分です。データボディは行と列で構成され、各セルには具体的なデータが格納されます。例えば以下のようにDataBodyRangeプロパティを使うことで、テーブルのデータボディを選択できます。Headerとの違いは赤字部分です。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.DataBodyRange.Selectテーブル全体
なお、以下のように指定すると、HeaderとDataBodyを合わせたテーブル全体が選択されます。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.Range.SelectListObjectのプロパティ
では次に、ListObject(テーブル)をより具体的に操作するために理解すべきプロパティについて紹介します。
ListRows
ListRowsは テーブル内の行を操作するためのプロパティです。行の追加、削除、特定行のデータ参照などが可能です。以下の例は、新しい行を1行追加する例です。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.ListRows.AddListColumns
ListColumnsは テーブルの列を管理するプロパティです。列の追加、削除、列内データの参照が行えます。以下の例では、”都道府県”という名前の列の値を全て”東京”に統一しています。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.ListColumns("エリア").DataBodyRange.Value = "東京"HeaderRowRange
HeaderRowRangeはテーブルのヘッダー行の範囲を示すプロパティです。ヘッダー行に対する操作やデータアクセスに利用します。以下の例では、見出しを太字にしています。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.HeaderRowRange.Font.Bold = TrueDataBodyRange
DataBodyRangeはデータボディ全体を参照するプロパティです。この範囲を通じてデータの一括処理や抽出が可能です。以下の例では、データボディを黄色く塗りつぶしています。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.DataBodyRange.Interior.Color = RGB(255, 255, 0)TotalsRowRange
合計行の範囲を管理するプロパティです。合計行が有効であれば、この範囲を通じて合計値にアクセスすることができます。以下の例では、テーブルの合計行が表示されている場合に、その行のA列に任意のテキストを設定し、そのフォントを太字しています。
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
If tbl.ShowTotals Then
tbl.TotalsRowRange.Cells(1).Value = "合計金額(百万円)"
tbl.TotalsRowRange.Cells(1).Font.Bold = True
End Ifなお、ListObject(テーブル)の合計行というのは、Excelの以下の機能です(デフォルトはオフ)。
ListObjectプロパティでSetが必要なケース
上述のListObjectプロパティに関わる操作をする際、それを更に変数に格納したい場合にはSetをし直す必要がありますので、注意してください。以下、コード例です。
Dim tbl As ListObject, newRow As ListRow
Set tbl = ActiveSheet.ListObjects("Table1")
Set newRow = tbl.ListRows.Add
newRow.Range.Cells(1, 1).Value = "使用できません。"
コメント