テーブルの列の指定方法を色々考えてみました。配列に入れてクラス使用
この記載は、以下の続きです。
タイトルのマクロは、以下です。
<標準モジュール>
Sub tableColumnArrayWithClass()
' クラスをインスタンス化
Dim c As clsTableColumns
Set c = New clsTableColumns
Dim wb As Workbook
Set wb = ThisWorkbook ' 現在のブックを設定
Dim ws As Worksheet
Set ws = wb.Worksheets(1) ' 一つ目のシートを設定
' クラスのTableプロパティにテーブルを設定
Set c.Table = ws.ListObjects(1) ' 一つ目のテーブルを設定
' クラスの各プロパティ(列インデックス)をデバッグ出力
Debug.Print c.氏名, c.所属部署, c.担当地区, c.受注件数, c.受注額
' テーブルのデータのみを配列に入れる
Dim tbl As Variant
tbl = ws.Range("営業[#Data]") ' テーブルのデータ部分のみを配列に格納
' Rangeの前に、wsを入れているのは、別ブックがアクティブになっているとエラーになるため。
' これにより、特定のワークシートを明示的に指定して参照している。
Dim rw As Long
' 配列の行数分だけループを回す。UBound(tbl)は配列の最終行を取得。
For rw = 1 To UBound(tbl)
' 「氏名」列の各行の値をデバッグ出力
Debug.Print tbl(rw, c.氏名)
Next rw
End Sub
<クラスモジュール: clsTableColumns >
Private tbl As ListObject ' テーブルオブジェクトを保持するためのプライベート変数
' TableプロパティのSetメソッド。テーブルオブジェクトを設定する
Public Property Set Table(ByVal argTbl As ListObject)
Set tbl = argTbl
End Property
' 指定された列名に対応する列番号を取得するためのプライベート関数
Private Function getColumn(ByVal argName As String) As Long
' 列のインデックスを返す
getColumn = tbl.ListColumns(argName).index
End Function
' 氏名プロパティのGetメソッド。列インデックスを返す
Public Property Get 氏名() As Long
Static c As Long ' キャッシュ用の静的変数
If c <> 0 Then 氏名 = c: Exit Property ' キャッシュが存在する場合、それを返す
c = getColumn("氏名") ' 列インデックスを取得してキャッシュに保存
氏名 = c ' 列インデックスを返す
End Property
Public Property Get 所属部署() As Long
Static c As Long
If c <> 0 Then 所属部署 = c: Exit Property
c = getColumn("所属部署")
所属部署 = c
End Property
Public Property Get 担当地区() As Long
Static c As Long
If c <> 0 Then 担当地区 = c: Exit Property
c = getColumn("担当地区")
担当地区 = c
End Property
Public Property Get 受注件数() As Long
Static c As Long
If c <> 0 Then 受注件数 = c: Exit Property
c = getColumn("受注件数")
受注件数 = c
End Property
Public Property Get 受注額() As Long
Static c As Long
If c <> 0 Then 受注額 = c: Exit Property
c = getColumn("受注額")
受注額 = c
End Property
このVBAマクロは、クラスを用いてExcelテーブルの列インデックスを管理し、列名を動的に指定できるようにしたプログラムです。これにより、テーブル構造の変更に柔軟に対応しつつ、列インデックスを効率的に扱えるようになります。詳細は以下の通りです。
マクロ構造の概要
標準モジュール (tableColumnArrayWithClass
)
- クラスインスタンスの生成vbaコードをコピーする
Dim c As clsTableColumns Set c = New clsTableColumns
clsTableColumns
クラスのインスタンスc
を生成し、各列インデックスをクラスのプロパティで管理できるようにします。 - テーブルオブジェクトの設定vbaコードをコピーする
Set c.Table = ws.ListObjects(1)
Table
プロパティにワークシートの 1 番目のテーブルオブジェクト (ListObject
) を設定します。これにより、クラス内部で対象テーブルを参照できるようになります。 - データを配列
tbl
に格納tbl
変数にテーブルのデータ部分(ヘッダー以外)を配列形式で格納します。これにより、後のデータ操作が効率化されます。 - テーブル行のデータを出力
For
ループを使用し、各行の「氏名」列の値をDebug.Print
に出力します。c.氏名
を使うことで、列名「氏名」をインデックス番号で参照できます。
クラスモジュール (clsTableColumns
)
- プライベート変数
tbl
テーブルオブジェクト(ListObject
)を保持し、Table
プロパティで参照・操作するために利用します。 - プロパティ
Table
vbaコードをコピーするPublic Property Set Table(ByVal argTbl As ListObject) Set tbl = argTbl End Property
Table
プロパティにListObject
を設定するためのメソッドで、標準モジュールからテーブルを設定できます。 - 列インデックス取得のためのプライベート関数
getColumn
getColumn
関数は、引数に指定した列名(文字列)に対応する列インデックスを返します。 - 列名ごとのプロパティ (
氏名
,所属部署
,担当地区
,受注件数
,受注額
) 各列のプロパティは静的変数c
を使用してインデックス番号をキャッシュし、複数回のアクセスで効率を上げています。初回取得時のみgetColumn
関数でインデックス番号を取得し、それ以降はキャッシュ済みのインデックス番号を返します。
メリットとデメリット
- メリット:
- 列インデックスの取得をクラスで管理するため、テーブル構造が変更された場合もクラスを修正するだけで対応が可能。
- インデックスをキャッシュすることで、頻繁なアクセスでもパフォーマンスを最適化。
- クラスを用いることでインテリセンス(自動補完)が有効になるため、コードの可読性が向上します。
- デメリット:
- クラスの設定がやや複雑になるため、設定に多少の手間がかかります。
このマクロとクラスモジュールは、Excelテーブルの列インデックスを動的に扱いたい場合に非常に便利です。