VBA Dictionaryオブジェクト（連想配列）

VBA Dictionary（連想配列）の使用

VBAの連想配列は、コレクションオブジェクトのように扱うことができますが、より多くのプロパティとメソッドを持ち、さらなる柔軟性を提供します。

連想配列はデータをメモリ上に保存し、簡単に操作することができます。自動計算、バックグラウンドでのバックアップ、画面の更新が不要なので、コードの実行速度がかなり速くなります。

Dictionaryオブジェクトは、単語の意味を調べたいときに使う普通の辞書と同じように動作します。Dictionaryオブジェクトの各項目は、キー値と アイテム値を持っています。 key値を使ってDictionaryオブジェクトのitem値を検索するという点では、他言語の連想配列と同様の方法です。

Dictionaryオブジェクトは上記のように動作するため、キー値は従来の連想配列と同じようにすべて一意でなければなりません。 ある単語の意味を調べるために従来の辞書を開いたとき、その単語がまったく異なる2つの定義で複数回掲載されていた場合を想像してみてください。かなり困惑してしまいますよね。

キー値は通常、テキストか数字、またはその両方のこともあります。 ユーザーは、キーの名前を、数字だけでなくテキストとして覚える方が簡単な場合はよくあります。

コレクションオブジェクトと比較すると、コレクションオブジェクトは読み取り専用です。 コレクションオブジェクトは、2つのメソッド（AddとRemove）と2つのプロパティ（CountとItem）しか持ちません。一度コレクションオブジェクトに追加された項目は、削除することはできても、編集することはできないので、項目の値を変更する必要がある場合は、面倒な手続きが必要になります。

Dictionaryオブジェクトは、その中の項目数に合わせて、自動的にサイズが変化します。 従来の配列のように、サイズを定義する必要はありません。

Dictionaryオブジェクトは1次元で、データ型はVariant型であるため、数値、テキスト、日付など、あらゆるデータ型を入力することができます。

VBAの連想配列はExcelの標準機能ではないので、Dictionaryオブジェクトを使う際には、事前バインディングまたは遅延バインディングを用いて定義する必要があります。

Sub EarlyBindingExample()
    Dim MyDictionary As New Scripting.Dictionary
End Sub

Sub LateBindingExample()
    Dim MyDictionary As Object
    Set MyDictionary = CreateObject("Scripting.Dictionary")
End Sub

事前バインディングを使用する場合、Microsoft Scripting Runtimeライブラリへの参照を追加する必要があります。 Visual Basic Editor (VBE) ウィンドウのメニューバーから「ツール > 参照設定」を選択すると、利用可能なライブラリのリストがポップアップウィンドウに表示されます。

Microsoft Scripting Runtimeまでスクロールし、その横のボックスにチェックを入れます。 OKをクリックすると、このライブラリがVBAプロジェクトの一部となり、事前バインディングを使って参照できるようになります。 この記事のすべてのコード例では、事前バインディングを使用します。

事前バインディングを使用すると、すべてのコードが前もってコンパイルされるため、コードの実行速度が大幅に向上します。遅延バインディングでは、コードの実行中にオブジェクトがコンパイルされます。

Scripting Runtimeライブラリには、インテリセンスもあります。コードを書いていると、利用可能なメソッドやプロパティのリストが表示されるので、スペルミスによるプログラムのバグを防ぐことができます。

また、VBE上でF2を押し、Scriptingライブラリを選択すると、利用可能なすべてのメソッドとプロパティ、およびそれぞれに必要なパラメータが表示されます。

連想配列を含むExcelアプリケーションの配布

すでに述べたように、Scripting RuntimeライブラリはExcel VBAの一部ではないので、アプリケーションを他のユーザーに配布する場合、そのユーザーのコンピュータでScripting Runtimeライブラリにアクセスできる必要があります。 もしそうでない場合は、エラーが発生します。

Excelアプリケーションをロードする際に、このライブラリが存在するかどうかの確認を、VBAコードを含めても良いでしょう。 Workbook_Openイベントでこれを行うには、Dirコマンドを利用できます。

ファイルの場所は C:\Windows\System32\scrrun.dll です。

Dictionaryオブジェクトのスコープ

Dictionaryオブジェクトは、Excelワークブックが開かれている間のみ利用可能です。ワークブックが保存されても、オブジェクトの内容は保存されません。

モジュール内のすべてのルーチンで連想配列を利用できるようにするには、モジュールの一番上のDeclareセクションで連想配列を宣言 (Dim) する必要があります。 コード全体で連想配列を使用する場合は、グローバル・オブジェクトとして定義します。

Global MyDictionary As New Dictionary

連想配列の登録と読み込み

まず始めに、連想配列を作成し、それにデータを入力してみましょう。データが存在することを確認するために、その内容を表示します。

Sub PopulateReadDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3"、30

    For n = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(n) & " & MyDictionary.Items(n)
    Next n
End Sub

このコードでは、MyDictionary という新しいDictionaryオブジェクトを作成し、それに 3 つの項目を追加しています。 Add メソッドには、キーとアイテムという 2 つのパラメータがあり、これらは両方とも必須です。

キーと アイテムのデータ型は両方ともバリアントなので、数値、テキスト、日付など、あらゆる種類のデータを受け入れることができます。

連想配列の最初のアイテムは、以下のように追加できます。

MyDictionary.Add 10, "MyItem1"

キーとアイテムの値が逆になっていますが、検索キーが10になっただけで、このコード自体は動作はします。

ここで重要なのは、キー値は連想配列のルックアップ値であることを理解することです。これは、ExcelのVLOOKUP関数と非常によく似た働きをします。 すべてのキーは一意な値でなければならないので、キー値を指定すれば、瞬時にそのキーに対応する項目値を返すことができます。

連想配列のインデックスは0から始まるので、For…Nextループで使用する連想配列のカウントから1を引く必要があることに注意してください。

また、For…Eachループを使用して、連想配列内の値を読み取ることもできます。

Sub PopulateReadDictionary()
    Dim MyDictionary As New Scripting.Dictionary, I As Variant

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3", 30

    For Each I In MyDictionary.Keys
        MsgBox I & " & MyDictionary(I)
    Next I
End Sub

このコードは、各項目を繰り返し処理し、それぞれのキーと値を表示します。

項目インデックス番号の使用

キーやアイテムのインデックス番号を使って、値を読み取ることもできます。

Sub IndexNumbers()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.CompareMode = TextCompare

    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    MsgBox MyDictionary.Keys(2)
    MsgBox MyDictionary.Items(1)
End Sub

このコードは、インデックスが 0 から始まるので、キー であるitem3と、アイテム値 20 を返します。

キーコレクションやアイテムコレクション内の個々のキーやアイテム値は、インデックス番号を使って参照することができます。

連想配列のフィルタリング

これを行う直接的な方法はありませんが、コードを書くのは非常に簡単です。

Sub FilterDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "AAItem1", 10
    MyDictionary.Add "BBItem2", 20
    MyDictionary.Add "BBItem3", 30

    For Each I In Filter(MyDictionary.Keys, "BB")
        MsgBox MyDictionary.Item(I)
    Next I
End Sub

フィルタは、キーの値の先頭からしか機能しません。 また、フィルタの中でワイルドカードを使用することはできません。

このコードでは、キーが BBで始まる 2 つのアイテム値を返します。 これは、フィルタした値に基づいた連想配列のサブセットを提供し、それを別の連想配列やワークシートに転送することができます。キー名について慎重に計画を立て、それぞれに意味のある接頭辞があるようにしておけば、連想配列をさまざまな構成要素に分割することが簡単にできるようになります。

キーのアイテム値を変更する

Dictionaryオブジェクトは、項目値を変更できる点でコレクションよりも大きな利点があります。

MyDictionary("MyItem4") = "40"

コレクションでは、その項目を削除してから、再作成する必要がありました。

以下はアイテム値を変更するコードの例です。

Sub PopulateReadDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3", 30
    MyDictionary("MyItem2") = "25"
    MyDictionary("MyItem4") = "40"

    For n = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(n) & " & MyDictionary.Items(n)
    Next n
End Sub

上記のコードでは、連想配列内に3つの項目を設定し、MyItem2の値を20から25に変更しています。 また、MyItem4の値も40に変更しています。

このコードのAddステートメントでは、MyItem4は追加されていないことに注意してください。 存在しないキーの値を変更すると、自動的にそのキーが作成されます。これはエラーが発生しないので非常に便利なのですが、その分キー名には注意が必要です。キー名にうっかりスペルミスがあると、新しいキーが作成され、元のキー名には古い値が残ったままになってしまいます。

これは、Dictionaryオブジェクトの整合性問題につながる可能性があります。

キーが存在するかどうかテストする

キー値が連想配列内に存在するかどうかをチェックすることができます。

Sub CheckExistsDictionary()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "MyItem1", 10
    MyDictionary.Add "MyItem2", 20
    MyDictionary.Add "MyItem3"、30

    MsgBox MyDictionary.Exists("MyItem8")
End Sub

このコードでは、新しいDictionaryオブジェクトに 3 つの項目を追加し、定義していないキー のMyItem8 があるかどうかをテストしています。 ここでは False を返しますが、定義済みのキーのいずれかが使用されていれば、True を返します。

ワイルドカードは使用できません。検索テキストはデフォルトで大文字と小文字を区別しますが、これは変更することができます。（記事の後半を参照してください。）

連想配列における複数の値の使用

配列とは異なり、Dictionaryオブジェクトは一次元のみです。 このため、1つのキーに対応させたい値が複数ある場合、問題が発生することがあります。

これを回避するひとつの方法は、各項目の値を ‘|’ のような区切り文字で連結することです。

Sub MultipleValues()

    'Dictionaryオブジェクトと変数を作成する
    Dim MyDictionary As New Scripting.Dictionary, V1 As Integer, V2 As String
    Dim V3 As Date, Temp As String, N As Integer

    '複数の値を示すために3つの変数を設定する
    V1 = 5
    V2 = "複数の値の例"
    V3 = "22-Jul-2020 "です。

    '連結された値を"|"区切りで連想配列に追加する
    MyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"


    '連想配列に連結された値を変数に取り込む
    Temp = MyDictionary("MyMultipleItem")

    '連結された文字列を繰り返し、個々の値を分離する
    Do

        '区切り文字の位置を探す
        N = InStr(Temp, "|")

        '区切り文字がなくなったらDoループを終了する
        If N = 0 Then Exit Do

        '見つかったデリミタの位置までのテキストを表示する
        MsgBox Left(Temp, N - 1)

        '見つかったデリミタまでの文字を切り詰める 
        Temp = Mid(Temp, N + 1)
    Loop
End Sub

この問題を回避するもう一つの方法は、キー名のルールを独自に決めてしまうことです。 キー名に括弧や数字を使用してはいけない理由はありません。

Sub MultipleValues()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "Multiple(1)", 5
    MyDictionary.Add "Multiple(2)", "複数の値の例"
    MyDictionary.Add "Multiple(3)", "22-Jul-2020"

    For N = 1 To 3
        MsgBox MyDictionary("Multiple(" & N & ")")
    Next N
End Sub

このコードでは、連想配列に 3 つのキーを追加していますが、各キー名の括弧内に、サブ番号が含まれています。これにより、キー名を参照する際に、サブ番号を連結して使用することができます。 これは、Arrayオブジェクトを使うのと非常によく似ています。

項目の削除

キー値を参照することで、個々の項目を削除することができます。

MyDictionary.Remove ("MyItem2")

キー名は一意であるため、これはその特定のキーと項目値のみを削除することに注意してください。 また、連想配列の内容を完全に消去することもできます。

MyDictionary.RemoveAll

以下は、VBA で Remove を使用する例です。

Sub RemoveValues()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30
    MyDictionary.Remove ("Item2")

    For N = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(N) & " & MyDictionary.Items(N)
    Next N

    MyDictionary.RemoveAll

    MsgBox MyDictionary.Count
End Sub

このコードでは、3つの項目を連想配列に追加し、Item2 を削除しています。そして、Item2 がもう存在しないことを証明するために、連想配列を繰り返し処理します。

最後に、このコードは連想配列内のすべての項目を削除し、連想配列のアイテム数を表示します。

検索時の大文字と小文字の区別の変更

キーの検索を行った場合、デフォルトでは大文字と小文字が区別されます。 しかし、CompareModeプロパティを使用すると、これを変更することができます。 これは、Dictionaryオブジェクトを作成した後、連想配列にデータを追加する前に、コード内ですぐに行わなければならないことに注意してください。 比較モードが一度設定されると、そのオブジェクト内で再び変更することはできません。

Sub ChangeCaseSensitivity()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.CompareMode = TextCompare
    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    MsgBox MyDictionary.Exists("item2")
End Sub

この例では、比較モードがTextCompareに設定されており、大文字と小文字が区別されないことを意味します。 検索テキストがすべて小文字であるにもかかわらず、この例の最後にある Exists ステートメントは True を返します。

Excelでは、比較モードに使用できる値は2つだけです。 BinaryCompareは大文字と小文字を区別し、TextCompareは大文字と小文字を区別しません。

比較モードをBinaryCompareに設定した場合、キーの命名に注意が必要です。最初の文字が大文字になるように名前を設定した場合、値を変更するときに最初の文字が大文字のままであることを確認する必要があります。 小文字で始めると、新しいキーとして解釈され、連想配列の混乱やエラーにつながる可能性があります。

キーの値を変更し、BinaryCompareの使用によりそのキー名が存在しないと判断された場合、新しいキーと値が連想配列に追加されることに留意してください。 代わりにTextCompareを使用した場合、値の変更は大文字と小文字に関係なく、キーに反映されます。同じ項目で大文字と小文字が異なるものを追加しようとすると、すでに存在するためエラーが発生します。

連想配列のソート

コレクションオブジェクトと同様に、キーやアイテムの値を使って連想配列をソートする方法は用意されていません。 しかし、VBAコードはExcelワークブックにあるので、連想配列のデータを表形式でExcelに書き出し、Excelのソート機能を適用することができます。その後、RemoveAllを使用して連想配列をクリアし、ワークシートからソートされた値を追加することができます。 このコードでは、キーと項目の値の両方をソートします。

Sub SortMyDictionary()

    Dim MyDictionary As New Dictionary
    Dim Counter As Long

    'ランダムな順序の項目で連想配列を構築する
    MyDictionary.Add "Item5", 5
    MyDictionary.Add "Item2", 15
    MyDictionary.Add "Item4", 11
    MyDictionary.Add "Item1", 2
    MyDictionary.Add "Item3", 19

    '今後のために連想配列の項目数を取得する
    Counter = MyDictionary.Count
    
    'Sheet1のA列とB列に連続したセルに各キーと項目をコピーし、連想配列を繰り返し実行する
    For N = 0 To MyDictionary.Count - 1
        Sheets("Sheet1").Cells(N + 1, 1) = MyDictionary.Keys(N)
        Sheets("Sheet1").Cells(N + 1, 2) = MyDictionary.Items(N)
    Next N

    'Sheet1をアクティブにして、Excel のソート機能でデータを昇順に並べ替える
    Sheets("Sheet1").Activate
    Range("A1:B" & MyDictionary.Count).Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 _
        Key:=Range("A1:A5"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:= xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    '連想配列の内容をクリアする
     MyDictionary.RemoveAll

    'セル値を空のDictionaryオブジェクトにコピーして再構築する
    For N = 1 To Counter
        MyDictionary.Add Sheets("Sheet1").Cells(N, 1).Value, Sheets("Sheet1").Cells(N, 2).Value
    Next N

    '連想配列を繰り返し、アイテムの値の順番を確認する
    For N = 0 To MyDictionary.Count - 1
        MsgBox MyDictionary.Keys(N) & " & MyDictionary.Items(N)
    Next N

    'Sheet1をクリアする（削除しても良い）
    Sheets("Sheet1").Range(Cells(1, 1), Cells(Counter, 2)).Clear
End Sub

このコードでは、5つのランダムオーダーの値が追加された連想配列を作成しています。 アイテムの数を変数に取り込み、連想配列を繰り返し、キーと項目の値をワークシートの別々の列に書き出します。

そして、A列をキーとしてソートします。連想配列はRemoveAll メソッドにより完全にクリアされ、コードはワークシートのセル値を繰り返し処理し、連想配列に追加していきます。 最後に、このコードは連想配列を繰り返し、キーと項目の値を連結して表示し、ソートが成功したことを証明します。 ソートする際のキーとなる列を変更することで、データをアイテム値でソートすることもできます。

キーのリストをワークシートにコピー

次のコードを使用すると、すべてのキー値のリストをワークシートにコピーすることができます。

Sub CopyKeyList()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.CompareMode = TextCompare
    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    Sheets("Sheet1").Range("A1").Value = Join(MyDictionary.Keys, vbLf)
End Sub

これで、ワークシートに結果が表示されます。

次のコードを使用すると、連想配列全体をワークシートにコピーすることができます。

Sub CopyIntoWorksheet()
    Dim MyDictionary As New Scripting.Dictionary

    MyDictionary.Add "Item1", 10
    MyDictionary.Add "Item2", 20
    MyDictionary.Add "Item3", 30

    Range("A1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Keys)
    Range("B1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Items)
End Sub

ワークシートは次のようになります。

連想配列とコレクションの比較

連想配列はコレクションより高速です。

コレクションは標準でVBAに組み込まれています。連想配列は、追加するMicrosoft Scripting Dictionaryへの参照設定、または遅延バインディングを使用して作成されたオブジェクトが必要です。

コレクションのアイテムは、一度しか書き込めませんが、何度でも読み込めます。連想配列では、アイテムの値を変更することができます。コレクションでアイテムを変更しようとすると、一度アイテムを削除し、変更したアイテムを再び追加する必要があります。

コレクションはインデックス値で動作するため、どのインデックス値がどこに属しているかを調べるのが難しい場合がありますが、 連想配列は、アイテムを見つけるために一意のキー値を使用します。

大きな コレクションでは、1 つのアイテムの取得に 連想配列よりも時間がかかります。

コレクションでは、キーはデータを検索するためにのみ使用され、検索することはできませんが、連想配列では、キーが存在するかどうかをテストすることができ、特定のアイテムを見つけるために使用することができます。

コレクションは大文字と小文字が区別され、これを変更することはできませんが、連想配列では、比較モードを設定して、大文字と小文字を区別するかしないかを決めることができます。

コレクションでは、キー値は文字列でなければなりませんが、連想配列では、数値、日付など、任意のデータ型にすることができます。

コレクション内のすべての項目を削除するには、コレクション・オブジェクトを再定義する必要がありますが、 連想配列には、このためのRemoveAllメソッドがあります。