VBA テーブルとListObjects

このチュートリアルでは、VBAで、テーブルとListObjectsを操作する方法を紹介します。

VBAのテーブルとListObjects

このチュートリアルでは、VBAを使用して表を作成し、表に簡単なソートを追加し、表をフィルタリングし、その他の表関連タスクを実行する方法について説明します。

VBAでテーブルを作成する

ListObjects.Addメソッドは、ワークシートの範囲に基づいて、ワークシートに表を追加することができます。Sheet1というワークシートに($A$1:$B$8)で示された範囲があります。

次のコードは、デフォルトの表スタイルを使用して、範囲 ($A$1:$B$8) に基づいて、Table1 という表をワークシートに追加するものです。

Sub CreateTableInExcel()

ActiveWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _
"Table1"

End Sub

結果は次のようになります。

VBAで表の一番右に列を追加する

テーブルの最後に列を追加するには、ListColumns.Addメソッドを使用することができます。下図のようなTable1というテーブルがあります。

以下のコードでテーブルに列を追加すると、常にテーブルの一番右に列が追加されます。

Sub AddColumnToTheEndOfTheTable()

ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").ListColumns.Add

End Sub

結果は次のようになります。

VBAで表の一番下に行を追加する

ListRows.Addメソッドを使うと、テーブルの一番下に行を追加することができます。下図にTable1というテーブルを用意しました。

以下のコードを実行すると、常にテーブルの一番下に行が追加されます。

Sub AddRowToTheBottomOfTheTable()

ActiveSheet.ListObjects("Table1").ListRows.Add

End Sub

結果は次のようになります。

VBAで簡単なソートを追加する

VBAでテーブルをソートすることができます。下図のTable1というテーブルがありますが、VBAでSales 列を低いものから高いものへソートすることができます。

以下のコードでは、Sales列を昇順にソートしています。

Sub SimpleSortOnTheTable()

Range("Table1[[#Headers],[Sales]]").Select
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Sales]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort

.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With

End Sub

結果は次のようになります。

VBAで表をフィルタする

VBAを使用してExcelテーブルをフィルタリングすることもできます。Table1という表があり、1500以上の売上だけを表示するようにフィルタリングしてみましょう。

Autofilterメソッドには5つのオプションのパラメータがあります。2列目であるSales列をフィルタリングしたいので、Fieldを2に設定し、日付と数値に使用されるxlAnd演算子パラメータを使用します。

Sub SimpleFilter()

 ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
 ">1500", Operator:=xlAnd

End Sub

結果は次のようになります。

VBAのShowAllDataメソッドでフィルタをクリアする

フィルタを解除するには、WorksheetクラスのShowAllDataメソッドにアクセスすればOKです。表のフィルタ（複数可）をクリアするには、まず表のセルを選択する必要がありますが、これはVBAで行うことができます。 ShowAllDataメソッドは、ワークシートにフィルターが適用されているかどうかをチェックしておかないと、エラーが発生してしまいます。次のコードは、これを解決する方法を示しています。

Sub ClearingTheFilter()

Range("Table1[[#Headers],[Sales]]").Select
If ActiveWorkbook.Worksheets("Sheet1").FilterMode = True Then
ActiveSheet.ShowAllData

End If

End Sub

Excelの表からすべてのフィルタを解除する

ListObjectsクラスのShowAllDataメソッドにアクセスするには、まずテーブルのセルを選択する必要があります。次のコードで、これを行うことができます。

Sub ClearAllTableFilters()
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").AutoFilter.ShowAllData
End Sub

VBAで行を削除する

ListRows.Deleteメソッドを使うと、テーブルのデータベース内の行を削除することができます。どの行を削除するかは、行番号で指定します。以下のようなTable1というテーブルがあるとします。

このテーブルのデータボディの2行目を削除したい場合、次のようなコードで可能になります。

Sub DeleteARow()

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListRows(2).Delete

End Sub

結果は次のようになります。

VBAで列を削除する

ListColumns.Deleteメソッドを使うと、テーブルから列を削除することができます。下図のTable1というテーブルがあります。

最初の列を削除するには、次のコードを使用します。

Sub DeleteAColumn()

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).Delete

End Sub

結果は次のようになります。

VBAでテーブルを通常の範囲に戻す

VBAを使用して、テーブルを通常の範囲に戻すこともできます。次のコードは、Table1を通常の範囲に戻します。

Sub ConvertingATableBackToANormalRange()

ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1").Unlist

End Sub

VBAでワークシート内の全テーブルに帯付き列と書式を追加する

ListObjectsコレクションを使って、ワークシート内のすべてのテーブルにアクセスすることができます。以下のシートでは、2つの表がありますが、VBAを使用して、一度に両方の表に帯状の列を追加し、両方の表のデータセクションのフォントを太字に変更したいと思います。

Sub AddingBandedColumns()

Dim tbl As ListObject
Dim sht As Worksheet

Set sht = ThisWorkbook.ActiveSheet

For Each tbl In sht.ListObjects
tbl.ShowTableStyleColumnStripes = True
tbl.DataBodyRange.Font.Bold = True
Next tbl

End Sub

結果は次のようになります。

VBAのAccessでDoCmd.RunSQLを使ってテーブルを作成する

VBAでAccessにテーブルを作成する主な方法の1つは、DoCmd.RunSQLメソッドを使用して、SQLステートメントでアクションクエリを実行することです。 サンプルフォームにボタンがあり、ボタンをクリックすると、ProductsTableというテーブルが作成され、ProductsIDという主キーフィールドとSalesというフィールドの2つのフィールドまたは列が作成されます。

このテーブルを作成するために、次のコードを使用します。

Private Sub cmdCreateProductsTable_Click()

DoCmd.RunSQL "CREATE TABLE ProductsTable " _
& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"

End Sub

結果は次のようになります。

VBAを使ってccessのテーブルをフィルタする

AccessでもDoCmd.ApplyFilterメソッドを使えば、テーブルにフィルタをかけることができます。AccessでProductsTableという下図のような簡単なテーブルを作成しました。 フォームでこのボタンを押すと、1500以上の売上だけが表示されるようにしたいと思います。

そこで、次のようなコードでこれを実現します。

Private Sub cmdFilter_Click()

DoCmd.OpenTable "ProductsTable"

DoCmd.ApplyFilter , "[Sales]>1500 "

End Sub

結果は次のようになります。