[Excel] テーブルと Excel VBA

ワークシート関数を使う時、これまでの「範囲」の変化に対応することができるテーブル機能は Excel 2007 以降の最大の機能拡張、、、なんてことも言われています。

過去にこのブログでもいくつかテーブル機能について紹介しました。

テーブルのすすめシリーズ
http://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
http://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
http://road2cloudoffice.blogspot.jp/2014/11/office-365-sharepoint.html

機能、関数で多くのメリットを提供するテーブルですが、Excel VBA の世界ではどうでしょう？

まず、表としての範囲が、テーブルというオブジェクト（ListObject オブジェクト）になるため、最終行＋空白問題（上から End モードで下に行ったとき、空白があったら止まる）、もしくは最終行＋ごみ問題（行の一番下から上に上がっても、途中にごみがあればそこで止まる）は基本的になくなります。

というのも、テーブルではテーブルの行数は End モードを使わずとも、テーブルのプロパティで確認することができるからです。テーブルの行数は ListRows.Count でわかります。

ここで標準的なテーブルを実践ワークシート協会の田中亨が推奨する「黄金テンプレート」で処理するコードを見てみると以下のようになります。

サンプルテーブル

サンプルテーブル

テーブルでも表の処理同様の VBA コードを書くことができます。
名前が田中だったら、個数X単価を合計に代入する、といった処理だと、田中亨の黄金テンプレートで考えると、

範囲はどこか？　

行は 3 から 12。もちろん、最終行の 12 が可変だとしても、End モードで特定します。

条件はなにか？

条件は、名前のセルが "田中" だったら、ですね。

処理は何か？

合計セルに個数X単価を代入する、です。

そうすると VBA のコードは以下のようになりますね。

Sub sample1()
    Dim i As Long
    For i = 3 To Range("B2").End(xlDown).Row
        If Cells(i, 2) = "田中" Then
            Cells(i, 5) = Cells(i, 3) * Cells(i, 4)
        End If
    Next i
End Sub

これをテーブルのオブジェクトを使って、テーブルらしく書いてみると、上記の表形式とは大きく違う点があります。それは「列番号」であり、「行番号」の考え方になります。

テーブル オブジェクトの世界では、ワークシートの「A列」や「D列」は意味がなく、あくまでテーブル内での列順序の番号です。上の例でいえば、名前の列は、B列で列番号2ではなく、あくまで名前フィールドであり、フィールドの番号はテーブル内で「1」になります。

さらに、テーブルというオブジェクトは「ListRowsコレクション」からデータ部分が構成されています。テーブルの行の各1行は ListRow オブジェクトとして扱うことができます。このことより、「何行あるか、わからないけど、For Each を使えば、そのテーブルのすべての行（ListRowsコレクション）を扱うことができる」として処理可能になります。

そのサンプル コードが以下です。

Sub sample2()
    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序
    Dim tb As ListObject
    Set tb = ActiveSheet.ListObjects("TestTable")  ''テーブル名がTestTableだった場合
    Dim R As ListRow
    For Each R In tb.ListRows
        If R.Range(1) = "田中" Then
            R.Range(4) = R.Range(2) * R.Range(3)
        End If
    Next R
End Sub

たぶん、R.Range(1) という書き方ってあまり使っていなかったと思います。

この R.Range(1) という書き方は、以下の書き方と同じものです。

R.Range.Item(1)

R.Range.Cells(1)

おもしろいですね。

VBAセミナースタンダードで田中亨がよく「上級者は2次元配列は使いません。だって、Excel VBA にはワークシートがあるんですから！」と言いますが、上記の表記はその流れです。（正確にはこれは一次元配列ですけど）

For Each でとってくる R は一次元配列（的なもの)です。（要素の型が違うので配列とは言えませんが、便宜上お許しを）この1次元配列は1列または一行で複数セルを選択した「セル範囲」のイメージです。今はテーブルなので、以下のような横方向の1行のセル範囲です。

[[名前] [個数] [単価] [合計] ]

この順序です。ですから、名前は (1) で、個数が (2)、、、となるわけです。

その表記方法が R.Range(1) だったり、R.Range.Item(1) だったり、R.Range.Cells(1) になるわけです。セル範囲の Range オブジェクトを配列のイメージとして扱っているんですね。

繰り返しますが、ポイントは A列、B列といったワークシート上の絶対的な番地では無い！ということです。

上記の例はサンプルなので処理や条件がシンプルなものですが、これが複雑になってくると、Cells(行, 列) でセルを指定するやり方と、R.Range(要素) で処理すべきセルを指定する方法のどちらがいいかはだんだん変わってきます。

その意味から、変数を使って、テーブルの列順序を定義すると、さらに可読性が高まります。（IMEオン・オフの操作があるので日本語変数がいいわけではありませんが、サンプルとして定義します）

Sub sample3()

    Dim tb As ListObject

    Set tb = ActiveSheet.ListObjects("TestTable")

    Dim 名前 As Long, 個数 As Long, 単価 As Long, 合計 As Long

    名前 = tb.ListColumns("名前").Index

    個数 = tb.ListColumns("個数").Index

    単価 = tb.ListColumns("単価").Index

    合計 = tb.ListColumns("合計").Index

    

    Dim R As ListRow

    For Each R In tb.ListRows

        If R.Range(名前) = "田中" Then

            R.Range(合計) = R.Range(個数) * R.Range(単価)

        End If

    Next R

End Sub

たった5～6行の処理のための変数宣言と変数設定で7行は、、、と思いますが、通常はもっと条件や処理が複雑になるので、これだけで「行数が多い、無駄だ」と判断するのは早計だと思います。 For Each から Next R までの処理の「可読性」を見てください。実にわかりやすい表記になっています。

たぶん、テーブル ListObject オブジェクトを VBA で扱うのは、このオブジェクト変数を使ってテーブルを設定し、テーブル内のフィールドの順序を変数として設定していくのが一番良い方法だと思います。

ここでテーブル機能で重要な「構造化参照」が出てきてませんよね？

いろいろ試した結果、この「構造化参照」は VBA を編集するツールである VBE で実装されていないため、インテリセンスのような形で、文字通りの「構造化参照」という機能は実装されていないようです。テーブル名を指定すれば、そのテーブルにあるフィールドが表示される、、、ような機能は VBE には無い、ということです。

では、VBE に期待しないで表記したらどうなるでしょう？

ここでも Excel の Range オブジェクトの実装、、、「すげー」となるわけです（笑

VBE では構造化参照のメリット享受できないので使う理由がないのですが、無理やりやると以下のようなコードを書くことができます。

Sub sample4()

    ''1 名前, 2 個数, 3 単価, 4 合計　←テーブル内での順序

    Dim R As Range

    For Each R In Range("TestTable").Rows

        If R.Cells(1) = "田中" Then

            R.Cells(4) = R.Cells(2) * R.Cells(3)

        End If

    Next R

End Sub

テーブルを ListObject として宣言しない分だけ行数が短いですが、R.Cells(1) などを番号ではなくフィールド名で、、、となると、宣言や設定のためそれなりに行数が増えます。さらに、構造化参照が VBE でサポートされていないために、複雑、長くなればなるほどめんどうなことが発生してきます。(*1 最後に追記してます）

以上のことより、通常、テーブルを VBA で扱うのであれば、テーブルは ListRow のかたまりであり、テーブルを ListRows コレクションとして処理するのが王道でしょう。

そのときの For Each として１つ１つ処理するのは ListRow オブジェクトであり、その ListRow オブジェクトの扱いは「一次元j配列」的になる、と言えるでしょうね。

今後、データ接続でテーブル形式でデータを取り込むことが多くなります。そのときは、上記の考え方でテーブルをマクロで扱うことも多くなると思います。

(*1) 構造化参照が少なすぎるとおもったので追記。
構造化参照方式で以下のような表記が可能です。

Range("テーブル1[名前]")

上記でテーブル１の名前フィールドのデータ部分の範囲を指し示すことができます。
しかし、この Range(構造化参照) は、あくまで Range による範囲（つまり、named range) のため、ListObject との関連がありません。

そのため、Range("テーブル[名前]").Column は、ワークシート上の列番号を返します。


[PR] VBAセミナーを受けた後は、これさえあれば何もいらない。

Read More