【ExcelVBA】Dictionaryで列番号を管理する - 和風スパゲティのレシピ

こんな表を処理するコードを書くとします。

売上データの表

このコードのメンテナンス性を向上させるテクニックとして、
以下のように列挙型変数Enumを用いる方法があります。

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

' ↓Enumを使って書き替える

Cells(R, C_売上データ.売上) = Cells(R, C_売上データ.価格) * Cells(R, C_売上データ.個数)

「売上＝価格×個数」というのがコードを見てわかるようになりますね。

さてこのEnum法も非常に強力なのですが、同じくらい便利な方法として、
Dictionaryを活用する方法がありますので紹介します。

※ Dictionaryを用いる方法はEnumより難易度が高いので、
　まだEnumを使ったことがない方は先にEnumを勉強することをおすすめします。

◇ Enumによる列番号管理の入門記事はこちら
www.limecode.jp

Dictionaryで列番号を管理するコード
Dictionary法のメリット(Enumより優れる点)
Dictionary法のデメリット(Enumより劣る点)
Dictionaryの生成を汎用関数にする

Dictionaryで列番号を管理するコード

Dictionaryで列番号を管理するには、
「Keyに見出し名/Itemに列番号」を格納する以下のコードを使用します。

Sub Dictionaryで列番号を管理する()
    
    Dim ws As Worksheet
    Set ws = Worksheets("売上データ")
    
    ' 列番号用のDictionaryを生成
    Dim C_売上データ As New Dictionary
    
    ' 見出しセルをループしてkeyに見出し名、itemに列番号を格納
    Dim C As Long
    For C = 1 To ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1

        If C_売上データ.Exists(ws.Cells(1, C).Value) = False Then
            C_売上データ.Add ws.Cells(1, C).Value, C
        End If
    
    Next
    
    ' その後の処理はこう書ける
    Dim R As Long
    For R = 2 To ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
    
        ws.Cells(R, C_売上データ("売上")) _
            = ws.Cells(R, C_売上データ("価格")) * ws.Cells(R, C_売上データ("個数"))
        
    Next
    
End Sub

上記のようにKeyに見出し名/Itemに列番号を格納したDictionaryを用意すれば、
セルの指定を

ws.Cells(R, C_売上データ("売上")) _
    = ws.Cells(R, C_売上データ("価格")) * ws.Cells(R, C_売上データ("個数"))

このように「見出し名の文字列を渡すことで実装できる」ようになります。

Enum同様に可読性が高く、もちろん改修にも強いので、
この方法も知っておくといいですね。

Enum法とDictionary法それぞれのメリットデメリットを比べると以下の通りです。

Dictionary法のメリット(Enumより優れる点)

列の追加/削除に対する改修が不要

例えばB列に「種別」という列が追加されたとき、
Enumの場合は以下の書き換えが必要になります。

Enum C_売上データ
    品物 = 1
    種別 ' ←ここに種別を追加する
    価格 ' ↓ここから下は自動的に3,4,…と再採番される
    個数
    売上
End Enum

　
これだけで改修が終わるのでEnumも十分強力なのですが、
さらにDictionaryでは一切の改修が不要になります。

見出し名-列番号のペアを実行の度に取得しているため、
列のレイアウトが変更されても問題なく動作するわけですね。

この「列名を見ている」というのが、Dictionary法の一番のポイントになります。
まずはこの点を抑えておきましょう。

同一マクロを複数のシートに使用できる

上記のコードを↓のシートに実行してみましょう。

別レイアウトの表データ

なんとまったく同じコードのまま実行できることがわかります。

見出し名-列番号のペアさえ合っていれば動くわけで、
列の追加対応が不要なのと同じ理屈ですね。

同じコードをレイアウトの異なるシートに対して実行できるという点は、
Enum法では難しいDictionary法の非常に強力なポイントです。

「似たシートをひとつのまとめシートに集計する」マクロなど、
この長所が活かせそうな場面では積極的にDictionary法を採用したいですね。

シンタックスハイライトによる可読性

VBE(コードを書く画面)の文字色設定を活用している場合は、

ws.Cells(R, C_売上データ("売上")) _
    = ws.Cells(R, C_売上データ("価格")) * ws.Cells(R, C_売上データ("個数"))

このように「色がついた部分でコードを読む」ことができます。
私の設定の場合は、黄色だけ見てもコードが読めるということですね。

これも地味ながら便利な点だと思います。

※VBEの設定を行う方法はこちらを参考ください。

以上がDictionary法の主なメリットになります。

特に「同じコードをレイアウトの異なるシートに対して実行できる」というのが、
Dictionary法の最大のメリットですので覚えておきましょう。

Dictionary法のデメリット(Enumより劣る点)

反対にDictionaryがEnumに比べて劣る点が以下の通りです。

列見出し名を変更するとマクロが動かなくなる

列の挿入/削除には強いDictionaryですが、逆に列名の変更に弱くなってしまいます。

例えば「価格」を「単価」に変更してしまうと、

C_売上データ("価格")

これが0を返すようになってしまい、Cellsの指定部分で

アプリケーションまたはオブジェクト定義のエラー

が発生してしまいます。

列見出し名と列番号のペアを使ってコードを書いているため、
このリスクに対応するのはEnumより難しくなります。

コーディング時に入力候補(インテリセンス)が使用できない

Enumの非常に強力なメリットである、

Enumの入力候補

この入力候補の活用がDictionaryにはできません。

列名をいちいちタイピングする必要がありますし、
大量のセル指定があると、「"」2回の入力も割と面倒に感じます。

同名の列を指定できない

よくあるこんなシート↓

同名の列が規則的に続く表

これをDictionary法では指定することができません。

「Dictionaryのためにこのレイアウトをやめろ」
というのはややVBA原理主義が過ぎる気がするので、
こんなシートを処理する際は、DictionaryはあきらめてEnumを使いましょう。

Enumなら割ときれいな設計になりますし、
入力リストもわかりやすくなります。

Enumの入力リスト種別分け

Enumは実際の列番号をコーディング中に見れる

これはDictionary法のデメリットというよりはEnumの小ワザという感じなのですが、
Enumにはクイックヒント「Ctrl+i」を使った、

Enumのクイックヒント表示

この列番号の表示機能があります。

意外と役立つときがあるので覚えておくといいかもしれません。

プロシージャ(Sub/Function)分割への対応が必要

Dictionaryはコード内で生成したものを使用するため、
プロシージャを分割する場合はそのままでは使えません。

いちいち全プロシージャに引数に渡すのも大変なのですが、
モジュール変数にすると各プロシージャを単独実行できなくなるため、
マクロに応じて対応を決める必要があります。

Enumに比べて準備がやや大変

機械的に定義すればよいEnumに比べて、
DictionaryはFor文を書く必要があるのでコーディングが少し大変です。

その対策として、後述の「汎用関数化」が重要になります。

実際にDictionary法を常用するなら、この準備は必須といっていいかもしれません。

Dictionaryの生成を汎用関数にする

Dictionaryの「準備が大変」というデメリットを解消する方法として、
以下のように汎用関数化する方法があります。

' 列番号をDictionaryに格納する汎用関数
Function GetDic列番号リスト(ws As Worksheet, 見出し行 As Long) As Dictionary
    
    Set GetDic列番号リスト = New Dictionary
    
    Dim C As Long
    For C = 1 To ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1

        If GetDic列番号リスト.Exists(ws.Cells(見出し行, C).Value) = False Then
            GetDic列番号リスト.Add ws.Cells(見出し行, C).Value, C
        End If
    
    Next
    
End Function

' 実際のコード
Sub Dictionaryで列番号を管理する()
    
    Dim ws As Worksheet
    Set ws = Worksheets("売上データ")
    
    Dim C_売上データ As Dictionary
    Set C_売上データ = GetDic列番号リスト(ws, 1)
        ' ↑汎用関数によってDictionaryの生成コードが1行になる
    
    Dim R As Long
    For R = 2 To ws.UsedRange.Rows.Count
    
        ws.Cells(R, C_売上データ("売上")) _
        = ws.Cells(R, C_売上データ("価格")) * ws.Cells(R, C_売上データ("個数"))
        
    Next
    
End Sub