エクセルVBAでテーブル名を使用してテーブルを取得する方法

photo credit: Go-tea 郭天 Special 11/52 via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

請求書マクロの作成を題材として、エクセルVBAでテーブルを操作する方法についてお伝えしています。

前回の記事はコチラ。

エクセルVBAでテーブル操作～そのメリットと変換方法＆ListObjectの取得

エクセルVBAでテーブルを操作する方法について丁寧に解説していきます。今回は、テーブルとは、エクセルのデータリストをテーブルに変換する方法、VBAでテーブルをオブジェクトとして取得する方法をお伝えします。

そもそもテーブルとは何か、またテーブルを表すListObjectオブジェクトの取得の方法についてお伝えしました。

今回は、題材とする請求データのテーブルを例として、エクセルVBAでテーブル名を使用してテーブルを取得する方法について解説をしていきます。

では、行ってみましょう！

請求データのエクセルファイルの構成
請求データファイル上のテーブルをVBAで確認してみる
1. For Each~Next文でシートについてループする
2. For文でシート上のテーブルについてループする
テーブル名を変更する
1. テーブル名でListObjectオブジェクトを取得する
まとめ
連載目次：エクセルVBAでテーブルを活用した請求書マクロを作る

請求データのエクセルファイルの構成

まず、使用するエクセルファイルの構成について紹介していきます。

「請求データ.xlsm」というエクセルファイルに、以下２つのシートが含まれています。

「データ」シート: 請求する品目や価格のデータと取引先リストが含まれるシート
「ひな形」シート: 取引先ごとに作成する請求書のひな形として使用するシート

「データ」シート

「データ」シートはその名の通り、各種データを記載するシートです。

イメージはコチラです。

「請求データ」と「取引先マスタ」の２つのテーブルが用意されていますね。それぞれのテーブルの役割は以下の通りです。

「請求データ」テーブル: 請求する品目やその価格に関するデータのリスト
「取引先マスタ」テーブル: 取引先リスト。このリストの数だけ請求書を作成する

本来、シートには１種類の表リストを配置したいところですが、テーブル機能を使うことで、そのデメリットはスポイルすることができます。その話は後ほど解説します。

なお、こちらのシートはオブジェクト名を「wsData」と変更しています。

「ひな形」シート

そして「ひな形」シートのイメージはこちら。

こちらには、取引先ごとの品目をリストするためのテーブルが用意されていますが、まだデータが一つもありません。

なお、こちらのシートはオブジェクト名を「wsTemplate」としています。

請求書作成マクロの動作

請求書作成マクロの動作としては、以下のような流れを想定しています。

取引先ごとに新規ブックを作成する
「ひな形」シートをその新規ブックにコピー
コピーした「ひな形」シートのテーブルに、その取引先に請求する請求データを転記していく

請求データファイル上のテーブルをVBAで確認してみる

では、これらのテーブルの様子をVBAの視点で調べてみましょう。

以下のようなプロシージャを用意します。

Sub テーブルリスト()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Dim i As Long
    For i = 1 To ws.ListObjects.Count
        Debug.Print ws.Name, i, ws.ListObjects(i).Name
    Next i
Next ws
End Sub

このプロシージャを実行すると、イミディエイトウィンドウに以下のような出力が得られます。

各テーブルについて

シート名
インデックス
テーブル名

を出力して確認することができました。

For Each~Next文でシートについてループする

まず、3行目から8行目までのFor Each~Next文ですが、これは現在のマクロブックに存在する全てのシートについてのループ処理となります。

For Each~Next文はコレクションに含まれる要素全てについて繰り返すループ文です。

繰り返すたびに、別の要素を取り出して変数（今回の例ではWorksheet型の変数ws）にセットしてくれます。

つまり、「データ」シートと「ひな形」シートの２つについてのループですね。

For文でシート上のテーブルについてループする

その内側の5~7行目のFor~Next文ですが、繰り返しの最終値は以下のように設定されています。

ws.ListObjects.Count

まず、wsはループで対象となっているシート、それに対するListObjectsコレクションを取得しています。さらに、そのListObjcetsコレクションに対してCountプロパティでその要素数を取得しています。

ListObjectsコレクション.Count

つまり、このループは、それぞれのシートに存在するListObjectオブジェクト（つまりテーブル）の数だけ繰り返すループということになります。

なぜListObjectsコレクションに対してFor Each~Next文を使わなかったか

ListObjectsコレクションはコレクションなので、For Each~Next文を使うことができます。

ですが、今回あえてCountプロパティを使って最終値を求めつつ、For文を使ったのか…？

それは、出力する内容にListObjectオブジェクトのインデックスがほしかったからです。

ListObjectオブジェクトには残念ながらIndexプロパティが存在していませんので、その役割をiに担ってもらってということです。

テーブル名を変更する

さて、実際にマクロでテーブルを取得する際に、インデックスで取得しても良いのですが、テーブル名を変更して使うという手も考えられます。

もともとのテーブル名は「テーブル1」などという識別しづらいネーミングなのですが、これは自由に変更することができます。

テーブル名は、テーブルを選択した状態で、リボンの「テーブルツール」→「デザイン」の「テーブル名」欄で編集をします。

テーブル名でListObjectオブジェクトを取得する

例えば、以下のように変更をしてみましょう。

テーブル1: 請求データ
テーブル2: 取引先マスタ
テーブル3: ひな形データ

すると、以下のように各テーブルを取得することができます。

Sub テーブルの取得()

Debug.Print wsData.ListObjects("請求データ").Name
Debug.Print wsData.ListObjects("取引先マスタ").Name
Debug.Print wsTemplate.ListObjects("ひな形データ").Name

End Sub

インデックスよりテーブル名で指定したほうがコードの可読性は高くなります。一方で、テーブル名であれば他のユーザーがうっかり変更しちゃうというリスクも少ないでしょう。