Excel VBAで表の列名とセルの列番号のスマートなマッピング方法

表の項目名と列番号のマッピング

Excel VBAで表を扱うとき、表の列名とセルの列番号をどのように結びつけるのがスマートなのか、といつも頭を悩ませています。

例えば、こんな表があったとします。

(なんちゃって個人情報を使わせていただきました。）

１行目が表の列名になっているのですが、全てのレコードのアドレスにアクセスしたいと思ったとします。そのコードをどのように書くか、ということが今回のトピックです。

マジックナンバーで書いちゃう

絶対自分しか使わないし、規模もめちゃめちゃ小さい。そしてコードの修正も絶対ない。使い捨て。そんな状況なら、これが一番手っ取り早い。

   Dim i as Long
   for i = 2 to 8
      Debug.printRange("C" & i)
      Debug.print Cells(i,3)
   Next i

"C"とか 3 がアドレスを示していることがコードからは全くわかりません。

こう書く場面はほとんどない（あってはいけない！！）と思います。

列挙型を使って列名で列番号を定義する

Enum enumSample
   名前 = 1
   ふりがな = 2
   アドレス = 3
   性別 = 4
   年齢 = 5
   誕生日 = 6
   婚姻 = 7
   都道府県 = 8
   携帯 = 9
   キャリア = 10
   カレーの食べ方 = 11
End Enum

Sub sample()
   Dim i As Long
   For i = 1 To 8
      Debug.Print Cells(i, enumSample.アドレス)
   Next i
End Sub

列挙型に列番号をあらかじめ定義しておき、列番号を知りたいときは列挙型を参照します。

こうすることで、このコードはアドレスを参照していることが明確になりましたね。

でも、列挙型の場合、定義がハードコーディングされているのと変わらないので、列が追加になったら列挙型の定義を直す必要があり面倒です。

このくらいの列数であればあまり問題にならないのですが、列数が多い場合は列挙型を書くのも一苦労です。また保守も手間がかかります。

ADOを使ってアクセスする

ADOを使う場合。例はこちらの記事も参照いただけますと幸いです。

www.tekizai.net

この手法の弱点は、シートの１行目に表の列名（フィールド）がないといけない、ということです。

きれいに作られた表であればいいのですが、あらかじめ体裁の決まっている帳票や、既存のワークシートなどでは１行目に列名があることは稀です。

また、ADOはシートに接続し、処理を行うまでのオーバーヘッドが大きいので、処理速度に難があります。

最近落ち着いたところ

最近これが自分の中で正解か？というところに落ち着いてきたので、その手法も紹介したいと思います。 それは、Collectionを使う書き方です。

Sub sample()
   Dim fldColl As Collection: Set fldColl = New Collection
   Dim lastCol As Long: lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
   Dim i As Long
   For i = 1 To lastCol
      fldColl.Add Item:=i, Key:=Cells(1, i).Value
   Next i
   For i = 1 To 8
      Debug.Print Cells(i, fldColl("アドレス"))
   Next i
End Sub

   Dim fldColl As Collection: Set fldColl = New Collection
   Dim lastCol As Long: lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

列名と列番号を格納するためのCollectionを準備します。

列名が書かれている行の最右を取得し、lastCol変数に格納します。

   Dim i As Long
   For i = 1 To lastCol
      fldColl.Add Item:=i, Key:=Cells(1, i).Value
   Next i

列名の行を１セルずつ走査しCollectionに格納しています。この場合は１行目なのでKeyの部分はCells(1,i)です。

２行目ならCells(2,i)となります。

このときキモになるが、Collectionの「キー」に列名を設定し、値にその列番号をセットすることです。

あまり使われないCollectionのキーですが、これを使って実現しています。

こうすることで、表の列名と列番号を結びつけることができます。

   For i = 1 To 8
      Debug.Print Cells(i, fldColl("アドレス"))
   Next i

アドレス列にアクセスしていることが明白になります。

列挙型との違いは、あらかじめ定義しておく必要がないことです。途中に項目が追加になってもコードを直す必要はありません(あくまでこの部分の話)。

またADOのように、列名が書かれている行を気にする必要はありません。

終わりに

列名と列番号をスマートに扱いたい、という思いのもと試行錯誤の結果、落ち着いたところを紹介しました。

まだまだ、これからも探求を続けていきたいと思います。

まだまだこんなアイディアがあるよ、という方がいましたら教えて頂けると幸いです。

というわけで、ここまでお読みいただき、ありがとうございました。