ExcelVBA連想配列+二次元配列で、特定の単語ごとに、最速で条件分岐する

連想配列

　連想配列（Dictionary オブジェクト）使ってますか？御存知のとおり、文字列をキーにした配列です。
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/dictionary-object
（リンク　microsoft公式）

　えっ重複チェックにしか使ってない？itemにいらない情報しか入れたことがないって？奇遇ですね。半年前までの私と完全に同じです。
　ただ、それはあまりにもったいない！Excelならではの連想配列の使い途があるんです。

連想配列の利点

　ハッシュ検索を行えることです。特定の単語と一致するものを検索するとき、先頭から順に確認していく線形探索法を使っていては，平均探索回数が（ｎ＋１）/２となってしまうところ，連想配列に登録されたkeyにあるかをexistsでチェックすれば，平均探索回数１回で検索できるのです。
https://www.atmarkit.co.jp/ait/articles/0603/31/news116.html
（リンク　＠IT atmarkit）
　なので，重複チェックに使ったりするわけです。
　ただ，重複チェックはkeyの情報だけでできるので，itemにはいらない情報を入れることになります。カウンタとか。それだけではあまりにもったいない！

文字列データの修整に，連想配列を使ってみる

　スクレイピングしたデータや、社内システムから引いてきたデータの表記変更をするとします。
　私は、こういうときExcel上に表を作って表記のユーザー編集を可能にしています。SelectCase文の条件分岐を表にするイメージです。
　たとえば，氏名から略称を引くための表を作成したとします。

もし氏名に該当したら略称をメッセージで出すというマクロを書きたいとき，単純に上から順に繰り返し処理で確認するとこのようになります。

繰り返し処理

Sub msg_short_name()
    Dim i As Integer
    i = 1
        Do While Cells(i, 1) <> ""
                If Cells(i, 1) = Cells(1, 4) Then
                    MsgBox Cells(i, 2)
                End If
            i = i + 1
        Loop
End Sub

ただ、これを繰り返すとどうなるでしょうか。

１回探すなら（ｎ＋１）/２
４回探したら（ｎ＋１）/２＊４
かなり時間がかかってしまいます。

　そこで、連想配列の出番です。

連想配列

Sub msg_short_name()
    Dim nameArray As Object
    Set nameArray = CreateObject("Scripting.Dictionary")
    Dim i As Integer
    i = 1
        Do While Cells(i, 1) <> ""
                If Not nameArray.Exists(Cells(i, 1)) Then
                    nameArray.Add Cells(i, 1).Value, Cells(i, 2).Value
                End If
            i = i + 1
        Loop
            If nameArray.Exists(Cells(1, 4).Value) Then
                MsgBox nameArray.Item(Cells(1, 4).Value)
            End If
End Sub

※cellの値をkeyやitemとして代入したり，existsで確認したりするときは，.valueをつけてセルの値であることを明示する必要があります。

　いったん全てのkey(氏名)，item(略称)を連想配列に格納したら，その後はnameArray.Exists(文字列)で存在確認でき，nameArray.Item(文字列)でitemを取り出すことができます。
１回探すなら１
４回探したら４
と，表の行数にかかわらない処理速度で要素を取り出せるのです。

もっと複雑な条件分岐に対応する

　連想配列自体は二次元配列にすることができません。
　ただ，連想配列のKeyから、もっと色々な情報を引きたくなったら、セルの行を外部キーにして、可変二次元配列と関係づけ、データベースのようにつなげることができます。

たとえば，氏名から略称・受賞作の双方を引く必要があったとすると，下の図のように配列をつくります。

連想配列＋可変二次元配列

Sub msg_short_name()
    Dim nameArray As Object
    Set nameArray = CreateObject("Scripting.Dictionary")
    Dim shortNameArray() As String
    Dim i As Integer
    i = 1
        Do While Cells(i, 1) <> ""
                If Not nameArray.Exists(Cells(i, 1)) Then
                    nameArray.Add Cells(i, 1).Value, i
                    
                    ReDim Preserve shortNameArray(1, i)
                    shortNameArray(0, i) = Cells(i, 2).Value
                    shortNameArray(1, i) = Cells(i, 3).Value
                End If
            i = i + 1
        Loop
            If nameArray.Exists(Cells(1, 4).Value) Then
                Dim foreignKey As Integer
                foreignKey = nameArray.Item(Cells(1, 4).Value)
                Dim msg As String
                msg = "セル(1,4)の外部キー：" & foreignKey & vbLf
                msg = msg & "略称：" & shortNameArray(0, foreignKey) & vbLf
                msg = msg & "代表作：" & shortNameArray(1, foreignKey)
                MsgBox msg
            End If
End Sub

※可変二次元配列は、最後の要素数しか可変にできません。

氏名の文字列から、略称・受賞作の両方を引けるようになりました。

連想配列の使いどころ

　ずばり、文字列についてそこそこ分岐の多いSelectCase文を使うときです。特定の単語ごとに関連要素を引くとき、と言い換えることもできます。
　分岐を増やせば増やすほど遅くなるというときの特効薬になります。セルの読み込みは一回のみですし、分岐のための検索は、平均一回のみで済みますから。
　ただ、含む(Instr)という分岐などには使えないので、やはりスクレイピングなどのローデータ処理が向くと思います。

　あまりに膨大な連想配列(や可変二次元配列)を作ると、メモリを圧迫してしまう可能性はありますが、文字列処理レベルではまず大丈夫かと思います。