Excel VBA テーブルを特定する方法について

テーブル操作第5回目です。VBAでテーブルを操作するにはテーブルを特定しなければ次の動作に移ることができません。テーブルを特定する方法について解説していきます。

くるみこ

前回は、テーブル内の範囲やセルなどの指定方法について勉強しましたが、VBAでその操作を行うには最初に目的のテーブルを特定する必要があります。複数のテーブルがあった場合などその特定方法を解説したいと思います。

そうですよね。テーブルがいつも同じ場所にあるとは限らないし、複数ある場合どうしたらいいのかまだ知りません！
よろしくお願いしますm(_ _)m

【この記事でわかることは】
・テーブルを特定する方法がわかります
・ブック内のテーブルがどのように設定管理されているかがわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel VBA テーブル内の範囲や特定位置を指定する方法

Excel VBA テーブル内の範囲やセルを指定するには ListObject のプロパティを知る必要があります。「DataBodyRange」「HeaderRowRange」「ListColumns」「ListRows」などの特徴を解説

www.kurumico.com

くるみこ

前回記事は、ListObjectの各プロパティの特徴やテーブル内の色々な部分の範囲やセルの指定方法の記事でした。覗いてみてね(^^)/

テーブルを特定する方法

テーブルを特定するにはは次のような方法があります。状況によって使い分けます。

テーブル内のセルから特定する

ListObjectオブジェクトがテーブル全体を表しているので、テーブル内に存在している任意のセルを使って特定することができます。この方法は、今までに記事でも使ってきた方法です。

前回記事のサンプルコード（一部抜粋）

Sub sample_01()
On Error Resume Next
Range("A1").ListObject.Range.Select
MsgBox "Range(""""A1"""").ListObject.Range.Select を実行しました！"
On Error GoTo 0
End Sub

・[テーブル内の任意のセル].ListObject のように ListObject とすることで指定しています。
・Range(“A1”)セルは、テーブル内のセルです。テーブル内のセルならどこでもOKです。
・テーブルの存在がわかっていたのでこの書き方でOKなのですが、テーブル内でなかった場合は当然エラーになります。

Application.InputBoxで指定したコード

Sub Sample_02()
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox( _
        Prompt:="テーブル内のセルを選択してください", _
        Title:="テーブル選択", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    rng.Worksheet.Activate
    'セルがListObjectかを確認してから処理する
    On Error Resume Next
    If rng.ListObject.Name = "" Then
        MsgBox "選択セルはテーブル（ListObject）ではありません。"
    Else
        MsgBox "テーブル名は「" & rng.ListObject.Name & "」です。"
    End If
    On Error GoTo 0
End Sub

・Application.InputBoxで選択したセルを「rng」Rangeオブジェクト変数に代入し
・rng.ListObject.Name でテーブル名を取得しています。テーブルを特定できた結果です。

シート内のテーブルを特定する

シートからテーブルを特定する場合は次のように書きます。

これは、テーブルを作成した際に特にテーブル名をしていない場合の例です。
Sheets(“Sheet1”)の一番目に作成したテーブルを示しています。

テーブルを指定する場合に注意すべき点

ワークシートには、複数のテーブルを作成できます。
仮にシートに4つのテーブルを、シート名を指定せずに作った場合は次のようになります。

問題は次のような場合に起こります。例えば２番目と３番目のテーブルを削除した場合です。

さて、どこが問題なのかわかりますか？
インデックス番号を見てください。削除したテーブルののインデックスは削除されていません。
４番目のテーブルのインデックスが２に変わっています。でもテーブル名は変わっていません。
このことから、ワークシート上にある複数のテーブルをVBAで操作する場合、番号で指定するのではなくテーブル名で指定するようにした方がよいでしょう。

テーブルの名前で特定する

注意すべき点はまだあります！！
いままの例では、わかりやすくするために「シート内」としていましたが、実は「テーブル」はブック単位で「テーブル番号」や「テーブル名」が設定管理されています。

「テーブル番号」や「テーブル名」の重複が無いように管理されています。例えば同じ名前を付けようとしても、自動的に番号がふられていくようになっています。これは、シートをコピーしていく場合と同じだと考えていただければよいと思います。

別のブックから「Sheet1」をコピーや移動してきた場合、すでに「Sheet1」があった場合は、自動的に「Sheet1（2）」のようになります。

前の項「シート内のテーブルを指定する」に出てきた、この例もテーブル名で指定しています。

ListObjects を使った場合はこの指定方法ですね。

テーブル名で特定する（構造化参照）

次の例は、「構造化参照」の場合の指定例です。
「構造化参照」の場合は、こんな指定が使えます。

これは、「テーブル1」の Range です。「Range」はテーブル全体ですが、「構造化参照」の場合この書き方で「テーブル」の全データを表しています。「見出し行」は含まれません。

「構造化参照」についてはまだ解説していません。
簡単に言うと「構造化参照」は、テーブル名と列名の組み合わせでデータを参照する方法です。
機会をみて、別途解説するようにしたいと思います。

テーブル名はブックレベルの名前として定義されていますので、どのワークシートからでも操作できます。これは、テーブル名で特定する大きな利点といえます。

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回は、サンプルファイルの登録はありません。
・今までに記事のサンプルファイルでよろしければお使いください(^^)

まとめと感想など

くるみこ

ブック内のテーブル番号やテーブル名がどのように設定管理されているかがわかりましたね。なるべく「テーブル名」で特定した方が良いのではないでしょうか。

「構造化参照」のところがすごく気になりました。
テーブルを削除した場合に番号がずれるのは困りますね！
「テーブル名」を使うように心がけていきます(^^)/

くるみこ

テーブル操作６回目は、「列」の操作方法についての解説を検討したいと思います(^^)

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています！
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ

【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。

www.kurumico.com