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

name

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

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

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

前回の記事はコチラ。

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

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

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

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

スポンサーリンク

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

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

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

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

「データ」シート

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

イメージはコチラです。

請求データファイルの「データ」シートのイメージ

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

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

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

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

「ひな形」シート

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

請求書データファイルの「ひな形」シートのイメージ

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

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

請求書作成マクロの動作

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

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

請求データファイル上のテーブルを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

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

各シートのテーブルリストをイミディエイトウィンドウに出力

各テーブルについて

  1. シート名
  2. インデックス
  3. テーブル名

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

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

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

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

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

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

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

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

まとめ

以上、エクセルVBAでテーブル名を使用してテーブルを取得する方法についてお伝えしました。

表を「範囲」ではなくて、「名前」でそして「オブジェクト」として管理できるのは、テーブル機能の大きなメリットです。

次回は、もう少し突っ込んで、ListObjectの構成について考えてみましょう。

エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法
エクセルVBAで請求書作成マクロを題材としてテーブルを操作する方法をお伝えしています。今回は、VBAから見たテーブルの構造を確認すべく、テーブルの見出し行・データ行・集計行の範囲の取得をする方法をお伝えします。

どうぞお楽しみに!

連載目次:エクセルVBAでテーブルを活用した請求書マクロを作る

エクセルのとっても便利な機能「テーブル」。VBAで操作するときも、テーブルならではの便利さを味わうことができます。請求書マクロを題材にVBAによるテーブルの操作方法をお伝えしていきます。
  1. エクセルVBAでテーブル操作~そのメリットと変換方法&ListObjectの取得
  2. エクセルVBAでテーブル名を使用してテーブルを取得する方法
  3. エクセルVBAでテーブルの見出し行・データ行・集計行の範囲の取得をする方法
  4. エクセルVBAでテーブルのデータ行についてループ処理を行う方法
  5. エクセルVBAでテーブルのデータ行を追加して値を入力をする方法
  6. エクセルVBAでテーブルを走査して取引先ごとのブックを作るマクロ
  7. エクセルVBAでテーブル上の請求データを新規ブックに転記するマクロの作り方
  8. エクセルVBAで請求書作成マクロをテーブルを活用して作る
  9. エクセルVBAでテーブルと列挙体を組み合わせると最強のコードが書ける理由
タイトルとURLをコピーしました