テーブルの列の指定方法を色々考えてみました。配列に入れてユーザ定義型で設定
この記載は、以下の続きです。
タイトルのマクロは、以下です。
' ユーザ定義型
Private Type index
氏名 As Long ' 氏名列のインデックスを格納
所属部署 As Long ' 所属部署列のインデックスを格納
担当地区 As Long ' 担当地区列のインデックスを格納
受注件数 As Long ' 受注件数列のインデックスを格納
受注額 As Long ' 受注額列のインデックスを格納
End Type
' テーブルの列の指定方法。配列に入れて、ユーザ定義型変数で指定
' 利点：高速。列が移動してもコードは変更不要。インテリセンス使用可能。
' 欠点：変数の設定に手間がかかる
Sub tableColumnArrayWithTypeVariables()
Dim wb As Workbook
Set wb = ThisWorkbook ' 現在のブックを設定
Dim ws As Worksheet
Set ws = wb.Worksheets(1) ' 一つ目のシートを設定
Dim list As ListObject
Set list = ws.ListObjects(1) ' 一つ目のテーブルを設定
' ユーザ定義型変数を宣言
Dim c As index
' 各列のインデックスをユーザ定義型変数に格納
c.氏名 = list.ListColumns("氏名").index
c.受注額 = list.ListColumns("受注額").index
c.受注件数 = list.ListColumns("受注件数").index
c.所属部署 = list.ListColumns("所属部署").index
c.担当地区 = list.ListColumns("担当地区").index
' 各列のインデックスをデバッグ出力
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このVBAマクロコードは、Excelテーブルの列インデックスをユーザー定義型で管理し、効率的かつ柔軟にデータを操作するための方法を示しています。以下にコードの各部分の詳細と役割を説明します。
1. ユーザ定義型 index の定義
Private Type index
氏名 As Long ' 氏名列のインデックスを格納
所属部署 As Long ' 所属部署列のインデックスを格納
担当地区 As Long ' 担当地区列のインデックスを格納
受注件数 As Long ' 受注件数列のインデックスを格納
受注額 As Long ' 受注額列のインデックスを格納
End Typeindexという名前のユーザー定義型を作成しています。これにより、各列のインデックス（列位置）を格納するための専用の型を定義しています。index型のフィールドには、「氏名」「所属部署」「担当地区」「受注件数」「受注額」という項目が含まれており、これらの各列のインデックス（列番号）を保持します。
2. tableColumnArrayWithTypeVariables サブプロシージャの設定と初期化
Sub tableColumnArrayWithTypeVariables()
Dim wb As Workbook
Set wb = ThisWorkbook ' 現在のブックを設定
Dim ws As Worksheet
Set ws = wb.Worksheets(1) ' 一つ目のシートを設定
Dim list As ListObject
Set list = ws.ListObjects(1) ' 一つ目のテーブルを設定wbとwsはそれぞれ現在のブックとその1つ目のシートを参照します。listには、シート上の1つ目のテーブル（ListObject）を格納しています。このテーブルは、コード内で「営業」と名付けられているものと想定されています。
3. index 型変数 c の設定と列インデックスの取得
Dim c As index
' 各列のインデックスをユーザ定義型変数に格納
c.氏名 = list.ListColumns("氏名").index
c.受注額 = list.ListColumns("受注額").index
c.受注件数 = list.ListColumns("受注件数").index
c.所属部署 = list.ListColumns("所属部署").index
c.担当地区 = list.ListColumns("担当地区").indexindex型の変数cを宣言し、それぞれの列名に対応するインデックスを取得してcのフィールドに格納します。- これにより、列の順番が変更されても列名でインデックスを取得できるため、コードを変更する必要がなくなります。
4. データ部分を配列 tbl に格納
Dim tbl As Variant
tbl = ws.Range("営業[#Data]") ' テーブルのデータ部分のみを配列に格納tblはVariant型の変数で、Excelのテーブル内のデータ部分を配列として格納しています。- この方法により、配列としてデータを操作できるため、テーブルを直接参照するよりも処理が高速になります。
5. 各行のデータをループ処理
Dim rw As Long
' 配列の行数分だけループを回す。UBound(tbl)は配列の最終行を取得。
For rw = 1 To UBound(tbl)
' 「氏名」列の各行の値をデバッグ出力
Debug.Print tbl(rw, c.氏名)
Next rw- 配列
tbl内の各行をループ処理して、「氏名」列のデータをDebug.Printで出力します。 tbl(rw, c.氏名)のように、ユーザー定義型変数cのフィールドを使用して列インデックスを指定するため、コードの可読性が高く、かつcの値を変更することで他の列を参照することも容易です。
まとめ
- 利点：コードが高速で、列の位置が変わっても動作します。また、列インデックスに名前でアクセスできるため、インテリセンス機能も有効活用できます。
- 欠点：各列インデックスをユーザー定義型に格納する手間が必要です。しかし、この設定によって以降のコードがシンプルで、メンテナンス性も高くなります。
他の例は、以下を参照
Follow me!
