テーブルの列の指定方法を色々考えてみました。配列に入れてクラス使用

この記載は、以下の続きです。

タイトルのマクロは、以下です。

<標準モジュール>
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)

  1. クラスインスタンスの生成vbaコードをコピーするDim c As clsTableColumns Set c = New clsTableColumns clsTableColumns クラスのインスタンス c を生成し、各列インデックスをクラスのプロパティで管理できるようにします。
  2. テーブルオブジェクトの設定vbaコードをコピーするSet c.Table = ws.ListObjects(1) Table プロパティにワークシートの 1 番目のテーブルオブジェクト (ListObject) を設定します。これにより、クラス内部で対象テーブルを参照できるようになります。
  3. データを配列 tbl に格納
    tbl 変数にテーブルのデータ部分(ヘッダー以外)を配列形式で格納します。これにより、後のデータ操作が効率化されます。
  4. テーブル行のデータを出力
    For ループを使用し、各行の「氏名」列の値を Debug.Print に出力します。c.氏名 を使うことで、列名「氏名」をインデックス番号で参照できます。

クラスモジュール (clsTableColumns)

  1. プライベート変数 tbl
    テーブルオブジェクト(ListObject)を保持し、Table プロパティで参照・操作するために利用します。
  2. プロパティ TablevbaコードをコピーするPublic Property Set Table(ByVal argTbl As ListObject) Set tbl = argTbl End Property Table プロパティに ListObject を設定するためのメソッドで、標準モジュールからテーブルを設定できます。
  3. 列インデックス取得のためのプライベート関数 getColumn
    getColumn 関数は、引数に指定した列名(文字列)に対応する列インデックスを返します。
  4. 列名ごとのプロパティ (氏名, 所属部署, 担当地区, 受注件数, 受注額) 各列のプロパティは静的変数 c を使用してインデックス番号をキャッシュし、複数回のアクセスで効率を上げています。初回取得時のみ getColumn 関数でインデックス番号を取得し、それ以降はキャッシュ済みのインデックス番号を返します。

メリットとデメリット

  • メリット
    • 列インデックスの取得をクラスで管理するため、テーブル構造が変更された場合もクラスを修正するだけで対応が可能。
    • インデックスをキャッシュすることで、頻繁なアクセスでもパフォーマンスを最適化。
    • クラスを用いることでインテリセンス(自動補完)が有効になるため、コードの可読性が向上します。
  • デメリット
    • クラスの設定がやや複雑になるため、設定に多少の手間がかかります。

このマクロとクラスモジュールは、Excelテーブルの列インデックスを動的に扱いたい場合に非常に便利です。

他の例は、以下を参照

Follow me!