【EXCEL VBA】シートの範囲をテーブル形式に変換する ListObject

ワークシートでもVBAでもテーブルは便利(^o^)

ワースシート上でもお馴染みのテーブル形式の表をVBAで作成する方法を紹介します！

テーブル

テーブルってこんなやつです。

ワークシート上にある表の中に、アクティブセルを置き、「挿入タブ＞テーブル」で作れますね。

この”テーブル”ですが、VBAで言うとテーブルにした時点で、”ListObject（リストオブジェクト）”というオブジェクトが作られます。

こいつを、そもそもVBAから作りたいと思います(^o^)

範囲をテーブルに変換するには、ListObjectsコレクションのAddメソッドを実行します。

構文

シートオブジェクト.ListObjects.Add(SourceType, Source, LinkSource, XlListObjectHasHeaders, Destination, TableStyleName)

引数の説明はこちら

名前	必須 / オプション	データ型	説明
SourceType	省略可能	XlListObjectSourceType	クエリで使用されるソースの種類を指定します。 xlSrcExternal・・・外部データソース (Microsoft SharePoint Foundation サイト) xlsrcmodel・・・PowerPivot モデル xlSrcQuery・・・クエリ xlSrcRange・・・範囲 xlSrcXml・・・XML
Source	Optional	Variant	SourceType=xlSrcRangeの場合は、省略可能です。データソースを示すRangeオブジェクトの値を使用します。この引数を省略すると、Sourceにはリスト範囲検出コードが返す既定の範囲が指定されます。 SourceType=xlSrcExternalの場合は、データソースへの接続を示す1つの配列を指定します。配列の各要素には、文字列型(String)の値を次のように格納します。
LinkSource	省略可能	Boolean	外部データソースをListObjectオブジェクトにリンクするかどうかを指定します。 SourceTypeがxlsrcexternalの場合、既定値はTrueになります。 SourceTypeがxlsrcrange/省略されている場合は無効です。省略した場合は、エラーが返されます。
XlListObjectHasHeaders	省略可能	Variant	指定した範囲の1行目が、列名かどうかを指定します。1行目が列名の場合は”XlYes”、列名では無い場合は”xlNo”、自動判別の時は”xlGuess”を指定します。既定値は、 ”xlGuess” 1行目が列名では無い場合は、列１、列２など自動的に列名が作成されます。
Destination	省略可能	Variant	新しいリストオブジェクトの左上隅のリンク先として単一セル参照を指定するRangeオブジェクト。 Rangeオブジェクトが複数のセルを参照する場合は、エラーが生成されます。 SourceTypeがxlsrcexternalに設定されている場合は、Destination引数を指定する必要があります。SourceTypeがxlsrcrange出力に設定されている場合、引数Destinationは無視されます。移動先の範囲は、式で指定されたlistobjectsコレクションが含まれているワークシート上にある必要があります。新しい列は、新しいリストに適合するようにコピー先に挿入されます。したがって、既存のデータは上書きされません。
TableStyleName	省略可能	文字列型 (String)	TableStyleの名前。たとえば、”TableStyleLight1″のようになります。

ごちゃごちゃ色々ありますが、シンプルに行くと

シートオブジェクト.ListObjects.Add(SourceType:=xlSrcRange, Source:=Rangeオブジェクト)

今回は、ワークシートの範囲をテーブルに変換する予定なので、“SourceType“にワークシートの範囲という意味の”xlSrcRange“を指定し、”Source”で変換したい範囲のRangeオブジェクトを指定します。

具体的なサンプルでみてみましょう。

次のワークシートの範囲を、テーブルに変換します。

コードは次のようになります。

Sub sample()

    ActiveSheet.ListObjects.Add SourceType:=xlSrcRange, Source:=ActiveSheet.Range("A1").CurrentRegion
    
End Sub

”Source”は、A1セルを基準にしたいい感じのセル範囲を自動的に指定してくれる”CurrentRegion”を使っています。

なので、もちろん

ActiveSheet.Range(“A1:B15”)

みたいに指定してくれてもOKです。

実行します。

変換できました(^o^)

コードの引数がカッコを付けずに記載しているのは、こんかいは返り値を必要としていないからです。（変数に入れたりしていない）

ListObject形式の変数を用意して、作ったテーブルをその後は変数で操作するともっと便利です。

先程のサンプルを使って、範囲をテーブルに変換したあと、そのまま”テーブル”という変数にぶち込んで、名前行の名前に”さん”を追加していくコードです。

Sub sample()

    Dim テーブル As ListObject
    Set テーブル = ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=ActiveSheet.Range("A1").CurrentRegion)
    
    Dim r As ListRow
    For Each r In テーブル.ListRows
    
        r.Range.Cells(1) = r.Range.Cells(1) & "さん"
    
    Next
    
End Sub

ListRowsオブジェクトなど、ListObjectには便利に使えるオブジェクトがたくさん用意されています。

ここでは詳しくやりませんが、このようにわざわざ最大行数を取りにいったりしなくてもデータを操作出来ます(^o^)