Excel VBA データ範囲をテーブルに設定する

スポンサーリンク

データ範囲をテーブルに設定する方法

1行目が見出し行で2行目以降にデータが入力されているデータは、テーブルとして認識させることができます。データ範囲をテーブルとして認識させると、集計行を表示して各列のデータ件数や合計、平均などの集計値を簡単に表示できます。また、
テーブルに名前をつけるとデータ範囲をその名前で管理できるため、操作が楽になります。

ListObjects【リストオブジェクツ】プロパティ

ワークシート内のテーブルの集まりを表すListObjects【リストオブジェクツ】コレクションを取得するにはWorksheet【ワークシート】オブジェクトListObjects【リストオブジェクツ】プロパティを使用します。

ListObjects【リストオブジェクツ】プロパティの書式と設定値の説明

オブジェクト.ListObjects

  • オブジェクト(必須)
    Worksheet【ワークシート】オブジェクトを指定します。

Add【アド】メソッド

データ範囲をテーブルに設定するにはListObjects【リストオブジェクツ】コレクションのAdd【アド】メソッドを使用します。データ範囲をテーブルに設定すると、そのデータ範囲は一つの「リストオブジェクト」として操作できるようになります。ワークシートでは複数のテーブルを管理できます。複数のテーブルをまとめたものがListObjects【リストオブジェクツ】コレクションです。

Add【アド】メソッドの書式と設定値の説明

[]内は省略可能です。
オブジェクト.Add[(SourceType,Source,LinkSource,
XlListObjectHasHeaders,Destination)]

  • オブジェクト(必須)
    Worksheet【ワークシート】オブジェクトのListObjects【リストオブジェクツ】プロパティで取得したListObjects【リストオブジェクツ】コレクションを指定します。
  • SourceType【ソースタイプ】(省略可)
    テーブルの元データの種類をXlListObjectSourceType列挙型の定数または、値で指定します。

    XlListObjectSourceType列挙型
    定数 内容
    xlSrcExternal
    【エックスエルソースエクスターナル】
    0 外部データ ソース
    SharePoint Foundation サイト
    xlSrcRange
    【エックスエルソースレンジ】
    1 セル範囲【既定値】
    xlSrcXml
    【エックスエルソースエックスエムエル】
    2 XML
    xlSrcQuery
    【エックスエルソースクエリ】
    3 クエリ
    xlSrcModel
    【エックスエルソースモデル】
    4 PowerPivot モデル
  • Source【ソース】(省略可)
    元データを指定します。引数SourceType【ソースタイプ】 がxlSrcRange【エックスエルソースレンジ】 の場合は、省略可能です。データ ソースを示す Range 【レンジ】オブジェクトの値を使用します。この引数を省略すると、Source【ソース】 にはリスト範囲検出コードが返す既定の範囲が指定されます。引数SourceType【ソースタイプ】がxlSrcExternal 【エックスエルソースエクスターナル】の場合は、データ ソースへの接続を示す 1 つの配列を指定します。配列の各要素には、文字列型 の値を次のように格納します。
    0 – SharePoint サイトの URL
    1 – ListName
    2 – ViewGUID
  • LinkSource【リンクソース】(省略可)
    外部データソースをListObject【リストオブジェクト】にリンクするかどうかをブール型の値で指定します。

    設定値 内容
    True リンクする
    False リンクしない

    引数SourceType【ソースタイプ】 が xlSrcExternal【エックスエルソースエクスターナル】 の場合は、既定値は True です。引数SourceType【ソースタイプ】 が xlSrcRange【エックスエルソースレンジ】 の場合は無効となり、省略しないとエラーが返されます。

  • XlListObjectHasHeaders【エックスエルリストオブジェクトハズヘッダーズ】(省略可)
    先頭行が見出しかどうかをXlYesNoGuess列挙型の定数または、値を指定します。見出しがない場合は自動的に生成されます。

    XlYesNoGuess列挙型
    定数 内容
    xlGuess 0 見出しがある場合はその場所を Excel が特定します。
    xlYes 1 範囲全体が並べ替えられません。
    xlNo 2 範囲全体が並べ替えの対象になります。【既定値】
  • Destination【ディスティネーション】(省略可)
    新規作成するリストオブジェクトの左上端となる単一のセルを参照する Range【レンジ】 オブジェクトを指定します。 引数SourceType【ソースタイプ】 が xlSrcExternal【エックスエルソースエクスターナル】 の場合は、必ず指定します。引数SourceType【ソースタイプ】 が xlSrcRange【エックスエルレンジ】 の場合は、指定しません。

※テーブルに設定済みの範囲に対してListObjects【リストオブジェクツ】コレクションのAdd【アド】メソッドを使用するとエラーにります。

A1セルから始まるデータ範囲をテーブル設定のコードと解説

Sub テーブル設定()
On Error GoTo エラー処理
Range("A1").Select
ActiveSheet.ListObjects.Add
Exit Sub
エラー処理:
MsgBox "テーブルは作成済みです。"
End Sub
2行目【On Error GoTo エラー処理】
On Error【オンエラー】ステートメントと GoTo【ゴウトゥ】ステートメントを使用してエラーが発生したら(テーブル設定済みの範囲にテーブル設定をした場合)6行目の「エラー処理」ラベルまで処理をジャンプします。


3行目【Range(“A1”).Select】
見出しを含む、データの始まりのセルを選択します。A1セルから始まるアクティブセル領域を参照するので、データ範囲全体を選択する必要はありません。


4行目【ActiveSheet.ListObjects.Add】
Worksheet【ワークシート】オブジェクトのListObjects【リストオブジェクツ】プロパティで取得したListObjects【リストオブジェクツ】コレクションのAdd【アド】メソッドを使用して、データ範囲にテーブルを作成します。


5行目【Exit Sub】
Exit【エグジット】ステートメントでSub【サブ】プロシージャを途中で終了します。


6~7行目
【エラー処理:
MsgBox “テーブルは作成済みです。”】

エラーが発生した場合、1行目のステートメントで6行目の「エラー処理」ラベルまで処理がジャンプし、7行目からの処理を実行します。

実行結果


テーブル設定を解除する方法

テーブル設定を解除するには、まずテーブルに設定されている色やフォントなどのスタイルをListObject【リストオブジェクト】オブジェクトのTableStyle【テーブルスタイル】プロパティで解除します。テーブルの設定解除はListObject【リストオブジェクト】オブジェクトのUnlist【アンリスト】メソッドを使用します。テーブルが設定されていない範囲にUnlist【アンリスト】メソッドを使用すると
エラーになります。

テーブル設定解除のコードと解説

Sub テーブル解除()
On Error Resume Next
With ActiveSheet.ListObjects(1)
     .TableStyle = ""
     .Unlist
End With
End Sub
2行目【On Error Resume Next】
テーブル範囲を表すListObject【リストオブジェクト】オブジェクトが存在しない場合、つまり範囲がテーブル設定されていない場合、エラーが発生するので、On Error【オンエラー】ステートメントとResume Next【リズームネクスト】ステートメントを使用して
エラーが発生してもデバックモードで停止させないで処理を続行させます。


3行目【With ActiveSheet.ListObjects(1)】
Worksheet【ワークシート】オブジェクトのListObjects【リストオブジェクツ】プロパティで参照したインデックス番号1のテーブルをWith【ウィズ】ステートメントで指定します。


4行目【.TableStyle = “”】
With【ウィズ】ステートメントで指定したテーブルを表すListObject【リストオブジェクト】オブジェクトのTableStyle【テーブルスタイル】プロパティに「””」長さ0の文字列(空白)を指定して既定のスタイルが適用されているテーブルのスタイルを解除します。


5行目【.Unlist】
With【ウィズ】ステートメントで指定したテーブルを表すListObject【リストオブジェクト】オブジェクトのUnlist【アンリスト】メソッドを使用してテーブルを解除します。

テーブルに名前とスタイルを設定する

売上を管理しているテーブルに「売上データ」とテーブル名をつけておけば、その後のVBAのコード内で「売上データ」としてそのデータ範囲が扱え管理が楽です。テーブルに設定したデータ範囲には、自動的にスタイルが適用されます。これも自分が見やすいスタイルを適用すればデータの視認性が高まります。

コードと解説

Sub テーブル設定()
Range("A1").Select
with ActiveSheet.ListObjects.Add
 .Name("売上データ")
 .TableStyle = "TableStyleLight6"
End With
End Sub
2行目
データ範囲の始まりのA1セルを選択します。


3行目
Addメソッドでテーブルを追加してwith ステートメントで
指定します。


4行目
Nameプロパティでテーブルに「売上データ」と
名前をつけます。


5行目
TableStyleプロパティでスタイルを設定します。
TableStyleLight6は「淡色」の6番を指定しています。

既定のテーブルスタイル一覧

Light(ライト)は「淡色」の一覧からの選択になります。一覧の最初はスタイル無しで、横方向に2番目を1としてインデックス番号で指定します。



Medium(ミディアム)「中間」左上スミを1として横方向にインデックス番号が増加します。



Dark(ダーク)「濃色」左上スミを1として横方向にインデックス番号が増加します。



スタイルのオプションの設定

スタイルを適用した場合は、スタイルにオプションを設定することができます。プロパティの値にTrue(オン)/False(オフ)を指定します

スタイルオプションプロパティ一覧

プロパティ 既定値 内容
ShowHeaders True 見出し行
ShowTableStyleColumnStripes False 列のストライプ
ShowTableStyleFirstColumn True 1列目用の書式
ShowTableStyleLastColumn True 最終列用の書式
ShowTableStyleRowStripes True 行ごとのストライプ
ShowTotals False 合計行
ShowAutoFilterDropDown True フィルターの矢印

設定したテーブル全体を選択する方法

テーブルに設定した範囲を選択するには、ListObjectオブジェクトに対してRangeプロパティを使った上でSelectメソッドを使用します。

ActiveSheet.ListObjects(“売上データ”).Selectでテーブル範囲を選択できそうですがSelectメソッドを使用するときはListObject.Range プロパティを使用します。

テーブル全体を選択するコード例

Sub テーブル全体選択()
ActiveSheet.ListObjects("売上データ").Range.Select
End Sub

テーブルの見出しとデータ部分をわけて選択する方法

ListObjectのHeaderRowRange【ヘッダーロウレンジ】プロパティを使うとテーブルの見出し行を取得できます。DataBodyRange【データボディレンジ】プロパティを使用するとテーブルのデータ部分の範囲を取得できます。

テーブルの見出しを選択するコード例

ActiveSheet.ListObjects("売上データ").HeaderRowRange.Select

テーブルのデータ部分を選択するコード

ActiveSheet.ListObjects("売上データ").DataBodyRange.Select

テーブルに新規データ用の行を追加する方法

テーブルの最終行の一つ下に行を追加するときはListObjecctのListRows【リストロウズ】コレクションに対してAddメソッドを使います。

テーブルの最終行の下に行を追加するコート例

Sub 行追加()
Range("A1").Select
Selection.ListObject.ListRows.Add
End Sub

テーブルの中に行を挿入する方法

ListRows.Addメソッドに引数を指定するとその位置に行を挿入することができます。

テーブルの3行目に新たな行を挿入するコード例

ActivSheet.ListObject("売上データ").ListRows.Add 3

テーブル範囲を拡張する方法

テーブル範囲の下の行にデータが追加された場合、その追加された行もテーブル範囲に収める場合はResize【リサイズ】メソッドの引数に更新後のセル範囲を指定して実行します。

テーブル範囲を拡張更新するコード例

Sub 範囲拡張()
With ActiveSheet.ListObject("売上データ")
  .Resize.Range.CurrentRegion
End With
End Sub

以上で、データ範囲をテーブルに設定する方法についての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告