Excelテーブル（ListObject）をVBAで使う基本と実務サンプル
Excelで表データを扱うなら、普通のセル範囲のまま処理するよりも テーブル化 して ListObject として扱うと、VBAがかなり書きやすくなります。
この記事では、Excelテーブルを使うメリットと、VBAでよく使う ListObject / ListRows / ListColumns / DataBodyRange の書き方をまとめます。
Excelテーブル化するメリット
Excelテーブルにすると、次のようなメリットがあります。
- データ範囲が増減しても、自動でテーブル範囲が広がる
- 列名で参照できるので、列位置が変わってもVBAが壊れにくい
- フィルターや集計行を使いやすい
- 書式が自動で引き継がれる
-
ListObjectとしてVBAから扱える
特に大きなメリットは、Excel側で表の範囲と見出し行を自動認識してくれることです。
そのためVBA側で、見出しが何行目にあるか、データの最終行や最終列がどこかを判定するコードを、毎回書く必要がありません。
業務ファイルでは、「A列からG列まで」のような固定範囲で参照するよりも、テーブル名と列名で参照したほうが、列の追加や並べ替えにも強く、保守しやすくなります。
Excelテーブルを作る
Excel上では、表の中のセルを選択して Ctrl + T でテーブル化できます。
テーブル化したら、テーブル名を分かりやすい名前に変更しておくのがおすすめです。
例:
tblSalestblItemstblOutput
VBAから参照するときに、この名前を使います。
ListObjectとは
ListObject は、ExcelのテーブルをVBAから扱うためのオブジェクトです。
Dim ws As Worksheet
Dim tbl As ListObject
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set tbl = ws.ListObjects("tblSales")
ListObject はワークシート上の ListObjects コレクションに含まれます。シート内の1つ目のテーブルを取るなら、次のようにも書けます。
Set tbl = ws.ListObjects(1)
ただし実務では、テーブル名で指定したほうが読みやすく安全です。
よく使う範囲
ListObject には、テーブルのどの範囲を扱うかによって便利なプロパティがあります。
| プロパティ | 対象 |
|---|---|
Range |
テーブル全体 |
HeaderRowRange |
ヘッダー行 |
DataBodyRange |
データ部分 |
TotalsRowRange |
集計行 |
InsertRowRange |
挿入行 |
' テーブル全体
tbl.Range.Select
' ヘッダー行
tbl.HeaderRowRange.Select
' データ部分
tbl.DataBodyRange.Select
注意点として、データ行が1件もないテーブルでは DataBodyRange が Nothing になることがあります。
If Not tbl.DataBodyRange Is Nothing Then
tbl.DataBodyRange.Select
End If
行数・列数を取得する
Debug.Print tbl.ListRows.Count
Debug.Print tbl.ListColumns.Count
データ行の件数を見たい場合は ListRows.Count を使います。
列名で列を取得する
列番号ではなく、列名で参照できるのがテーブルの強いところです。
Dim rngAmount As Range
Set rngAmount = tbl.ListColumns("金額").DataBodyRange
たとえば「金額」列だけを数値化したい場合は、次のように書けます。
tbl.ListColumns("金額").DataBodyRange.NumberFormatLocal = "#,##0"
列の位置が変わっても、列名が同じならコードを変えずに済みます。
特定列の最終行に値を入れる
Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
newRow.Range.Cells(1, tbl.ListColumns("品番").Index).Value = "A001"
newRow.Range.Cells(1, tbl.ListColumns("品名").Index).Value = "標準ねじ"
newRow.Range.Cells(1, tbl.ListColumns("金額").Index).Value = 1200
ListRows.Add は新しい行を追加し、追加された行を ListRow として返します。
行を追加する
一番下に追加するなら、これで十分です。
tbl.ListRows.Add
追加した行をすぐ使うなら、変数に受けます。
Dim row As ListRow
Set row = tbl.ListRows.Add
row.Range.Cells(1, 1).Value = "追加データ"
追加位置を指定することもできます。
' 1行目の位置に追加
tbl.ListRows.Add Position:=1
下のセルを必ず押し下げたい場合は AlwaysInsert:=True を指定します。
tbl.ListRows.Add AlwaysInsert:=True
列を追加する
Dim col As ListColumn
Set col = tbl.ListColumns.Add
col.Name = "備考"
位置を指定して追加する場合です。
Set col = tbl.ListColumns.Add(Position:=2)
col.Name = "カテゴリ"
テーブルは列名の重複を許容しません。存在する列名を追加しようとするとエラーが起きるので、追加前に同じ列名がないか確認すると安全です。
Function HasListColumn(ByVal tbl As ListObject, ByVal columnName As String) As Boolean
Dim col As ListColumn
For Each col In tbl.ListColumns
If col.Name = columnName Then
HasListColumn = True
Exit Function
End If
Next col
End Function
If Not HasListColumn(tbl, "備考") Then
tbl.ListColumns.Add.Name = "備考"
End If
テーブル内の特定セルを取得する
「何行目の、どの列か」を指定したい場合です。
Dim target As Range
Set target = tbl.ListColumns("金額").DataBodyRange.Cells(3, 1)
target.Value = 5000
ListColumns("金額").DataBodyRange は「金額」列のデータ部分だけなので、その中の3行目を指定しています。
フィルターをかける
tbl.Range.AutoFilter Field:=tbl.ListColumns("カテゴリ").Index, Criteria1:="A"
テーブルでは列の位置を ListColumns("列名").Index で取得できるので、列番号を直接書かずに済みます。
フィルターを解除する例です。
If tbl.AutoFilter.FilterMode Then
tbl.AutoFilter.ShowAllData
End If
集計行を表示する
tbl.ShowTotals = True
tbl.ListColumns("金額").TotalsCalculation = xlTotalsCalculationSum
集計行はテーブルの機能のひとつで、テーブルの下に合計や平均など何種類かの集計値を表示できます。
テーブルサイズを変更する
Resize を使うと、テーブル範囲を指定し直せます。
Dim newRange As Range
Set newRange = ws.Range("A1").Resize(100, 5)
tbl.Resize newRange
Resize は指定した範囲へテーブルサイズを変更します。セルを挿入したり移動したりする処理ではありません。
テーブルを普通の範囲に戻す
tbl.Unlist
Unlist を使うと、テーブル機能を解除して通常のセル範囲に戻せます。データ自体は残ります。
実務でよく使うサンプル
1. テーブルに1行追加して値を入れる
Sub AddSalesRow()
Dim ws As Worksheet
Dim tbl As ListObject
Dim row As ListRow
Set ws = ThisWorkbook.Worksheets("売上")
Set tbl = ws.ListObjects("tblSales")
Set row = tbl.ListRows.Add
With row.Range
.Cells(1, tbl.ListColumns("日付").Index).Value = Date
.Cells(1, tbl.ListColumns("品番").Index).Value = "A001"
.Cells(1, tbl.ListColumns("数量").Index).Value = 10
.Cells(1, tbl.ListColumns("金額").Index).Value = 1200
End With
End Sub
2. 特定列だけ空欄チェックする
Sub CheckBlankItemCode()
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("売上")
Set tbl = ws.ListObjects("tblSales")
If tbl.DataBodyRange Is Nothing Then Exit Sub
Set rng = tbl.ListColumns("品番").DataBodyRange
For Each cell In rng
If Len(cell.Value) = 0 Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub
3. テーブルを配列でまとめて処理する
セルを1つずつ読むより、配列に入れてから処理すると速くなることがあります。
Sub ReadTableToArray()
Dim ws As Worksheet
Dim tbl As ListObject
Dim data As Variant
Set ws = ThisWorkbook.Worksheets("売上")
Set tbl = ws.ListObjects("tblSales")
If tbl.DataBodyRange Is Nothing Then Exit Sub
data = tbl.DataBodyRange.Value
Debug.Print UBound(data, 1) ' 行数
Debug.Print UBound(data, 2) ' 列数
End Sub
構造化参照も便利
Excelの数式では、テーブル名や列名を使って次のように書けます。
下記は、金額列の合計値をSum関数を使って集計する例です。
VBAだけでなく、シート上の数式も読みやすくなります。
=SUM(tblSales[金額])
まとめ
ExcelテーブルをVBAで扱うなら、まずは次の書き方を覚えると実務でかなり使えます。
Set tbl = ws.ListObjects("tblSales")
tbl.ListRows.Add
tbl.ListColumns("金額").DataBodyRange
tbl.DataBodyRange
tbl.Range.AutoFilter Field:=tbl.ListColumns("カテゴリ").Index, Criteria1:="A"
セル番地に強く依存するコードより、テーブル名・列名で参照するコードのほうが、列追加や列移動に強くなります。
業務ファイルほど、まず表をテーブル化してからVBAを書くのがおすすめです。
また、Power AutomateやPower AppsなどのMicrosoft製品と連携する場合も、Excelデータのテーブル化が前提となる場面が多くあります。Excelの新しい関数やデータ連携機能でも、テーブル名や列名を使った構造化参照を利用することで、データの追加や列構成の変更に対応しやすくなります。
そのため、表を作成する段階からテーブル化を意識しておくと、後から別の処理やツールへ展開しやすく、データの再利用性も高まります。結果として、「最初からテーブルにしておいてよかった」と感じる場面が多いため、おすすめです。