VBAでExcelのテーブル名と列名で値を取得する簡単な方法

テーブルと列名を指定してVBAで値を取得する方法を紹介します。１行目からレコード数まで順番に取得できます。ループでも使えて便利です。

例1：○○テーブルの●●列のi番目の値を取得する

例
テーブル名：支店一覧
列名：支店名

Sub TableValueSample()
  dim aList as ListObject
  Set aList = Range("支店一覧").ListObject
  Dim i as Long
  For i To aList.ListColumns("支店名").DatabodyRange.Count
    Debug.print aList.ListColumns("支店名").DatabodyRange(i)
  Next
End sub

実行結果
　大阪支店
　京都支店
　兵庫支店

実行すると営業所一覧テーブルの列見出し 営業所名の値が１行目から、イミディエイトウインドウに出力されるはずです。

取得した値を使ってFor文でループ処理をすることができます。このサンプルでは分かりやすいように繰り返し表記していますが、重複する部分はWithを使って整理すると良いでしょう。

注意点

Activeworkbookが変わると名前の範囲が参照できなくなるためエラーが起きます。そのため実行直後にSetでListObjectをセットしています。

メンテナンスを考慮して項目名はトップで指定して、For文の中は変数にしておくのが良いかもしれません。ケースバイケースだと思います。

例2 テーブルを配列に入れる

列名で指定してループで処理をすることができます。
・Uboundでテーブルのレコード数を取得できます。
・Listの型はvariantを使います。
・ここでは列番号1,2としていますが部分はEnumを使うと分かりやすいでしょう。

例　一覧テーブルの１列目の値に○○が含まれていたら3列目の値を該当とする。

Dim aList as variant
aList = Activesheet.ListObjects("一覧").DatabodyRange
For i = 1 to Ubound(aList)
  If instr(aList(i,1),"○○") then
    aList(i,2).value = "該当"
　Else
    aList(i,2).value = "該当なし"
  end if
Next

テーブルを使うメリット

なごみ

以上簡単ですが、調べても見当たらなかったので参考までにご紹介しました。この方法はcells(i,j)と比べて、項目名がコード内で分かるのがメリットです。
ひとつの参考にしてみてください。AccessでいうところのDlookup関数に近い感覚で使えるように、関数にしておくのも良いかもしれません。

手順が少なくてシンプルな以下の方法もあります。よろしければご覧ください。

あわせて読みたい

PowerQueryでワークシートのセルの値を利用する方法（パート2） セルに入力した値をPowerQueryで利用する方法をご紹介します。前回の記事よりもシンプルな方法です。PowerQueryに慣れてきた方はこの記事の方法の方が手順は少なくて済…