Excel VBAで実用ツール（Dictionary、Collection、ArrayList、SortedList編）

2022-01-312024-01-07

■Dictionaryオブジェクトについて

Dictionaryオブジェクトは、File System Object(FSO)と同様にMicrosoft Scripting Runtimeに含まれているオブジェクトで、Microsoftのドキュメントには「DictionaryオブジェクトはPERL連想配列に相当します」と記載されています。

連想配列は添字の代りにキーを指定して要素を特定できる配列で、例えば、都道府県をキー、県庁所在地をアイテムとする、下図のような連想配列を作成すれば、「Item(“神奈川県")」のようにキーを指定することで、『"横浜市"』というアイテムを参照することができます。

⇒詳細はMicrosoftのサイト（Dictionaryオブジェクト）を参照して下さい。

Key	Item
“神奈川県"	“横浜市"
“埼玉県"	“さいたま市"
“千葉県"	“千葉市"
・・・	・・・

Dictionaryオブジェクトの詳細

Dictionaryオブジェクトで提供されているメソッドとプロパティは下表の通りです。

(Dictionaryオブジェクトのメソッドとプロパティ)

Addメソッド
説明	ディクショナリに新しいキーおよび項目を追加する
定義	Sub Add(Key As Variant, Item As Variant)
CompareModeプロパティ
説明	文字列を比較するメソッドを設定/取得する
定義	Property CompareMode() As CompareMethod
Countプロパティ(読み取り専用)
説明	ディクショナリ内の項目数を取得する
定義	Property Count() As Long
Existsメソッド
説明	指定したキーがディクショナリに含まれているかどうかを示す
定義	Function Exists(Key As Variant) As Boolean
Itemプロパティ(既定)
説明	指定したキーに対する項目を設定/取得する
定義	Property Item(Key As Variant) As Variant
Itemsメソッド
説明	ディクショナリ内のすべての項目を含む配列を取得する
定義	Function Items() As Variant
Keyプロパティ
説明	キーの値を設定する(キーを別のキーに変更する)
定義	Property Key(Key As Variant) As Variant
Keysメソッド
説明	ディクショナリ内のすべてのキーを含む配列を取得する
定義	Function Keys() As Variant
Removeメソッド
説明	指定したキーをディクショナリから削除する
定義	Sub Remove(Key As Variant)
RemoveAllメソッド
説明	ディクショナリからすべての情報を削除する
定義	Sub RemoveAll()

Dictionaryオブジェクトを使用するための事前準備

Dictionaryオブジェクトを使用するための事前準備は、以下の3ステップです。
⇒オブジェクトを使用するための事前準備とオブジェクトブラウザによる調査方法については、Excel VBAでオブジェクト指向プログラミング（事前準備編）を参照して下さい。

①ライブラリの参照設定
Visual Basic Editor(VBE)のファイルメニューから[ツール]-[参照設定]を選択し、参照設定ダイアログでMicrosoft Scripting Runtimeのチェックボックスにチェックを入れ、[OK]ボタンを押下。

②オブジェクト変数の定義
プログラムの宣言部でオブジェクト変数を定義。

Dim dic As Dictionary

③インスタンスの生成
プログラムの処理部でインスタンスを生成。

Set dic = New Dictionary
'ここにオブジェクトを使用した処理を記述
Set dic = Nothing

（参考）「Set dic = New Dictionary」の部分を「Set dic = CreateObject(“Scripting.Dictionary")」と記述すれば、ステップ①（ライブラリの参照設定）は不要ですが、一部の機能が正しく動作しなかったりしますので、事前バインディングした方が良いです。

■Excelシートに入力された文字列を入力し、Dictionaryオブジェクトで単語の出現頻度を集計するツール

本来の使い方ではないと思いますが、Dictionaryオブジェクトを使用すると、単語（または文字）の出現頻度を簡単に集計することができます。

Dictionaryオブジェクトにキーやアイテムを追加する処理

Dictionaryオブジェクトにキーとアイテムを追加する場合はAddメソッドを実行しますが、キーが既に存在しているとエラーになるため、予めExistsメソッドでキーが存在するかどうかを確認する必要があります。

Existsメソッドを実行すると、指定したキーが存在する場合はTrue、存在しない場合はFalseが返ります。

Addメソッドのキーにはオブジェクトを指定できるため、Excelシートの内容をキーとして登録する際、「sht.Cells(○,○)」のように指定するとRangeオブジェクトが登録されてしまい、見た目の値が同じでも同一キーと認識されないため、「sht.Cells(○,○).Value」のようにValueプロパティを明示的に指定する必要があります。
Addメソッドのアイテムにも配列やオブジェクトを含む、あらゆるデータ型を指定できるので、1つのキーに対して複数の値を登録することもできますが、値の重複はチェックしてくれません。
なお、既に登録されているキーを変更する場合はKeyプロパティ、アイテムを変更する場合はItemプロパティに値を設定します。

If dic.Exists(キー) = False Then
dic.Add キー, アイテム
Else
キーが存在する場合の処理（必要に応じてアイテムの更新等）
End If

Dictionaryオブジェクトからキーやアイテムを取出す処理

Dictionaryオブジェクトからキーやアイテムを取出したい時、キー指定で特定のアイテムを参照する場合はItemメソッド、全てのキーやアイテムを参照する場合はCountプロパティ、Keysメソッド、Itemsメソッドを使用します。

Countプロパティで登録されている要素数を取得できますが、KeysメソッドとItemsメソッドで返される配列の添字の範囲は、０～Count-1までです。

(キー指定で特定のアイテムを参照する処理)

アイテムの値 = dic.Item(キー)

(全てのキーとアイテムを参照する処理(For…Next))

For i = 0 To dic.Count – 1
キーの値 = dic.Keys(i)
アイテムの値 = dic.Items(i)・・・または「アイテムの値 = dic.Item(dic.Keys(i))」
Next i

(全てのキーとアイテムを参照する処理(For Each…Next))

For Each wkey In dic.Keys
キーの値 = wkey
アイテムの値 = dic.Item(wkey)
Next wkey

サンプル・プログラム(1)～単純に出現頻度を集計

行番号9～15がキーを登録し、件数をカウントする処理で、ExcelシートのA列に格納された文字列をキー、件数をアイテムとして登録しています。
行番号10でキーが存在するかどうかを確認し、存在しない場合は行番号11でキーとアイテム(数字の1)を登録、存在する場合は行番号13でアイテムの値を+1して再登録しています。

行番号17～20がキーとアイテムを取出す処理で、行番号18でキー、行番号19でアイテムを参照し、それぞれExcelシートのC列とD列にセットしています。

Dim sht As Worksheet
Dim dic As Dictionary
Dim i As Integer
Private Sub Sample1()
Set sht = ActiveSheet
Set dic = New Dictionary
For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
If dic.Exists(sht.Cells(i, 1).Value) = False Then
dic.Add sht.Cells(i, 1).Value, 1
Else
dic.Item(sht.Cells(i, 1).Value) = dic.Item(sht.Cells(i, 1).Value) + 1
End If
Next i
For i = 0 To dic.Count – 1
sht.Cells(i + 1, 3) = dic.Keys(i)
sht.Cells(i + 1, 4) = dic.Items(i)
Next i
Set dic = Nothing
End Sub

サンプル・プログラム(2)～自作の関数でキーをソート

Dictionaryオブジェクトにはソート機能がなく、サンプル・プログラム(1)を実行すると、登録された順にキーが表示されてしまうため、キーをソートする処理を追加したものが以下のサンプル・プログラムです。

キーを登録する処理はサンプル・プログラム(1)と同じですが、行番号18でキーの内容を配列にコピーし、行番号19でSortArray関数により配列をソートしてから、行番号20～23でExcelシートに書き出しています。
(SortArray関数は単純なバブル・ソートなので、説明は省略します)

Dim sht As Worksheet
Dim dic As Dictionary
Dim arr() As Variant
Dim i As Integer
Private Sub Sample2()
Set sht = ActiveSheet
Set dic = New Dictionary
For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
If dic.Exists(sht.Cells(i, 1).Value) = False Then
dic.Add sht.Cells(i, 1).Value, 1
Else
dic.Item(sht.Cells(i, 1).Value) = dic.Item(sht.Cells(i, 1).Value) + 1
End If
Next i
arr = dic.Keys
arr = SortArray(arr)
For i = LBound(arr) To UBound(arr)
sht.Cells(i + 1, 3) = arr(i)
sht.Cells(i + 1, 4) = dic.Item(arr(i))
Next i
Set dic = Nothing
End Sub
Private Function SortArray(arr() As Variant) As Variant()
Dim i, j As Integer
Dim w As Variant
For i = LBound(arr) To UBound(arr) – 1
For j = i + 1 To UBound(arr)
If arr(i) > arr(j) Then
w = arr(i)
arr(i) = arr(j)
arr(j) = w
End If
Next j
Next i
SortArray = arr
End Function

■ArrayListオブジェクトについて

前項のサンプル・プログラム(2)では自作の関数でキーをソートしましたが、既存のオブジェクトで処理できないかと考えた結果、ArrayListオブジェクトを使用してサンプル・プログラムを書換えてみました。

ArrayListオブジェクトは、.NET FrameworkのSystem.Collectionネームスペースに含まれているオブジェクトですが、Excel VBAからも使用可能です。

⇒詳細はMicrosoftのサイト（ArrayListクラス）を参照して下さい。

ArrayListオブジェクトの詳細

ArrayListオブジェクトで提供されているメソッドとプロパティは下表の通りです(※)。

(※).NET Frameworkのバージョンにより、提供されているメソッドおよびプロパティに相違があるため、実際に使用するバージョンの情報を確認して下さい。

(ArrayListオブジェクトのメソッドとプロパティ)

Addメソッド
説明	ArrayListの末尾に要素を追加する
定義	Function Add(value As Variant) As Long
Clearメソッド
説明	ArrayListから全ての要素を削除する
定義	Sub Clear()
Cloneメソッド
説明	ArrayListの簡易コピーを作成する
定義	Function Clone() As Variant
Containsメソッド
説明	ArrayList内に存在するかどうかを示す
定義	Function Contains(value As Variant) As Boolean
CopyToメソッド
説明	ArrayListを1次元配列にコピーする
定義	Sub CopyTo(Array As _Array, index As Long)
Countプロパティ(読み取り専用)
説明	ArrayListに含まれる要素の数を取得する
定義	Property Count() As Long
Equalsメソッド
説明	2つのインスタンスが等しいかどうかを示す
定義	Function Equals(obj As Variant) As Boolean
GetEnumeratorメソッド
説明	ArrayListを反復処理する列挙子を返す
定義	Function GetEnumerator() As IComparable
GetHashCodeメソッド
説明	現在のインスタンスのハッシュコードを返す
定義	Function GetHashCode() As Long
GetTypeメソッド
説明	現在のTypeを返す
定義	Function GetType() As _Type
IndexOfメソッド
説明	最初に値が出現した位置のインデックス番号を返す
定義	Function IndexOf(value As Variant) As Long
Insertメソッド
説明	ArrayListに要素を挿入する
定義	Sub Insert(index As Long, value As Variant)
IsFixedSizeプロパティ(読み取り専用)
説明	ArrayListが固定サイズかどうかを示す
定義	Property IsFixedSize() As Boolean
IsReadOnlyプロパティ(読み取り専用)
説明	ArrayListが読み取り専用かどうかを示す
定義	Property IsReadOnly() As Boolean
IsSynchronizedプロパティ(読み取り専用)
説明	ArrayListへのアクセスが同期されているかどうかを示す
定義	Property IsSynchronized() As Boolean
Itemプロパティ(既定)
説明	指定したインデックスにある要素を設定/取得する
定義	Property Item(index As Long) As Variant
Removeメソッド
説明	ArrayList内の要素を削除する
定義	Sub Remove(value As Variant)
RemoveAtメソッド
説明	ArrayListの指定したインデックスにある要素を削除する
定義	Sub RemoveAt(index As Long)
SyncRootプロパティ(読み取り専用)
説明	ArrayListへのアクセスを同期するために使用できるオブジェクトを取得する
定義	Property SyncRoot() As Variant
ToArrayメソッド
説明	ArrayListの要素を新しいObject配列にコピーする
定義	Sub ToArray() As Object()

ArrayListオブジェクトを使用するための事前準備

ArrayListオブジェクトを使用するための事前準備は、以下の3ステップです。
⇒オブジェクトを使用するための事前準備とオブジェクトブラウザによる調査方法については、Excel VBAでオブジェクト指向プログラミング（事前準備編）を参照して下さい。

①ライブラリの参照設定
Visual Basic Editor(VBE)のファイルメニューから[ツール]-[参照設定]を選択し、参照設定ダイアログで[参照]ボタンを押下し、使用する「mscorlib.tlb(※)」を選択し、mscorlib.dllのチェックボックスにチェックを入れ、[OK]ボタンを押下。

(※)筆者の場合は「C:\Windows\Microsoft.NET\Framework64\v4.0.31039\mscorlib.tlb」を選択しました。

②オブジェクト変数の定義
プログラムの宣言部でオブジェクト変数を定義。

Dim arr As ArrayList

③インスタンスの生成
プログラムの処理部でインスタンスを生成。

Set arr = New ArrayList
'ここにオブジェクトを使用した処理を記述
Set arr = Nothing

（参考）「Set arr = New ArrayList」の部分を「Set arr = CreateObject(“System.Collections.ArrayList")」と記述すれば、ステップ①（ライブラリの参照設定）は不要です。

サンプル・プログラム(3)～ArrayListオブジェクトでキーをソート

キーを登録する処理はサンプル・プログラム(2)と同じですが、行番号19でArrayListオブジェクトをインスタンス化し、行番号20～22でDictionaryのキーをArrayListに追加、行番号23でArrayListをソートした後、行番号25～29の繰返し処理でArrayListの要素を順に取出しています。

Dim sht As Worksheet
Dim dic As Dictionary
Dim arr As ArrayList
Dim wkey As Variant
Dim i As Integer
Private Sub Sample3()
Set sht = ActiveSheet
Set dic = New Dictionary
For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
If dic.Exists(sht.Cells(i, 1).Value) = False Then
dic.Add sht.Cells(i, 1).Value, 1
Else
dic.item(sht.Cells(i, 1).Value) = dic.item(sht.Cells(i, 1).Value) + 1
End If
Next i
Set arr = New ArrayList
For Each wkey In dic.keys
arr.Add wkey
Next wkey
arr.Sort
i = 0
For Each wkey In arr.ToArray
i = i + 1
sht.Cells(i, 3) = wkey
sht.Cells(i, 4) = dic.item(wkey)
Next wkey
Set arr = Nothing
Set dic = Nothing
End Sub

■Excelシートに入力された文字列を入力し、Dictionaryオブジェクトでクロス・リファレンスを作成するツール

タイプ・ライブラリの情報をExcelツールで取得(※)し、ExcelシートのA列にメソッド名またはプロパティ名、B列にオブジェクト名を格納したExcelシートを用意し、A列をキー、B列をアイテムとしてDictionaryに登録することにより、クロス・リファレンス・リスト(メソッドまたはプロパティからオブジェクトを検索するための索引）を作成することができます。

(※)タイプ・ライブラリ情報の取得方法については、「Excel VBAでファイル解析（タイプ・ライブラリ編）」を参照して下さい。

Dictionaryオブジェクトで、1つのキーに複数のアイテムを登録する処理

Dictionaryオブジェクトのアイテムには配列を使用できますので、1つのキーに複数のアイテムを登録することができます。
但し、アイテムの中身については何のチェックもされませんので、同じ値を登録しないようにする処理や値を昇順に並べ替える処理を行いたい場合は、自分で処理を組込む必要があります。

(アイテムに配列を登録する処理)

If dic.Exists(キー) = False Then
ReDim アイテムの配列(0)
varr(0) = アイテム
dic.Add キー, アイテムの配列
Else
アイテムの配列 = dic.Item(キー)
ReDim Preserve アイテムの配列(UBound(アイテムの配列) + 1)
varr(アイテムの配列) = アイテム
dic.Item(キー) = アイテムの配列
End If

サンプル・プログラム(4)～アイテムに配列を使用

行番号12～25がキーとアイテムを登録する処理で、ExcelシートのA列に格納された文字列をキー、B列に格納された文字列をアイテムとして登録しています。
行番号13でキーが存在するかどうかを確認し、存在しない場合は行番号14でアイテムの配列を初期化後、行番号15でアイテムを配列にセットし、行番号16でキーとアイテム(配列)を登録、存在する場合は行番号18でアイテムを配列に読込み、行番号19でアイテムが登録済かどうかをチェックし、未登録の場合は行番号20で配列を再宣言(要素数を+1)し、行番号21でアイテムを配列に追加し、行番号22でSortArray関数により配列をソートしてからアイテム(配列)を再登録しています。

行番号27～35がキーとアイテムを取出す処理で、行番号27でキーの内容を配列にコピーし、行番号28でSortArray関数により配列をソートしてから、行番号29～35でExcelシートに書き出しています。
(CheckValue関数、SortArray関数は単純な処理なので、説明は省略します)。

なお、行番号20で「ReDim Preserve～」としていますが、Preserveを指定せず「ReDim～」にすると、配列が初期化されてしまいます。

Dim sht As Worksheet
Dim dic As Dictionary
Dim karr() As Variant
Dim varr() As Variant
Dim i As Integer
Dim j As Integer
Private Sub Sample4()
Set sht = ActiveSheet
Set dic = New Dictionary
For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
If dic.Exists(sht.Cells(i, 1).Value) = False Then
ReDim varr(0)
varr(0) = sht.Cells(i, 2).Value
dic.Add sht.Cells(i, 1).Value, varr
Else
varr = dic.Item(sht.Cells(i, 1).Value)
If CheckValue(varr, sht.Cells(i, 2).Value) = False Then
ReDim Preserve varr(UBound(varr) + 1)
varr(UBound(varr)) = sht.Cells(i, 2).Value
dic.Item(sht.Cells(i, 1).Value) = SortArray(varr)
End If
End If
Next i
karr = dic.keys
karr = SortArray(karr)
For i = 0 To dic.Count – 1
sht.Cells(i + 1, 3) = karr(i)
varr = dic.Item(karr(i))
For j = 0 To UBound(varr)
sht.Cells(i + 1, j + 4) = varr(j)
Next j
Next i
Set dic = Nothing
End Sub
Private Function CheckValue(ValueTable() As Variant, wValue As Variant) As Boolean
Dim i As Integer
For i = LBound(ValueTable) To UBound(ValueTable)
If ValueTable(i) = wValue Then
Exit For
End If
Next i
If i <= UBound(ValueTable) Then
CheckValue = True
Else
CheckValue = False
End If
End Function
Private Function SortArray(arr() As Variant) As Variant()
Dim i, j As Integer
Dim w As Variant
For i = LBound(arr) To UBound(arr) – 1
For j = i + 1 To UBound(arr)
If arr(i) > arr(j) Then
w = arr(i)
arr(i) = arr(j)
arr(j) = w
End If
Next j
Next i
SortArray = arr
End Function

サンプル・プログラム(5)～アイテムにDictionaryオブジェクトを使用

Dictionaryオブジェクトを入れ子にして、アイテムをDictionaryオブジェクトのキーに登録し、ArrayListオブジェクトでキーをソートすることにより、自作の関数を使用しない処理方式としています。

行番号15～27がキーとアイテムを登録する処理で、ExcelシートのA列に格納された文字列をキー、B列に格納された文字列をアイテムとして登録しています。
行番号16でキーが存在するかどうかを確認し、存在しない場合は行番号17でアイテム用のDictionaryを生成後、行番号18でアイテムをアイテム用のDictionaryに登録し、行番号19でキーとアイテム(Dictionary)を登録。存在する場合は行番号21でアイテムをアイテム用のDictionaryに読込んだ後、行番号22でアイテムが登録済かどうかチェックを行い、未登録の場合は行番号23でアイテムをアイテム用のDictionaryに追加し、行番号24でアイテム(Dictionary)を再登録しています。

行番号29～49がキーとアイテムを取出す処理で、キーについては行番号29でArrayListオブジェクトをインスタンス化、行番号30～32でArrayListに追加、行番号33でArrayListをソートし、行番号37でExcelシートに書き出し、アイテムについては行番号38でアイテムをアイテム用のDictionaryに読込み、行番号39でArrayListオブジェクトをインスタンス化、行番号40～42でArrayListに追加、行番号43でArrayListをソートしてから、行番号45～48でExcelシートに書き出しています。

Dim sht As Worksheet
Dim kdic As Dictionary
Dim vdic As Dictionary
Dim karr As ArrayList
Dim varr As ArrayList
Dim wkey As Variant
Dim wval As Variant
Dim i As Integer
Dim j As Integer
Private Sub Sample5()
Set sht = ActiveSheet
Set kdic = New Dictionary
For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
If kdic.Exists(sht.Cells(i, 1).Value) = False Then
Set vdic = New Dictionary
vdic.Add sht.Cells(i, 2).Value, “"
kdic.Add sht.Cells(i, 1).Value, vdic
Else
Set vdic = kdic.Item(sht.Cells(i, 1).Value)
If vdic.Exists(sht.Cells(i, 2).Value) = False Then
vdic.Add sht.Cells(i, 2).Value, “"
Set kdic.Item(sht.Cells(i, 1).Value) = vdic
End If
End If
Next i
Set karr = New ArrayList
For Each wkey In kdic
karr.Add wkey
Next wkey
karr.Sort
i = 0
For Each wkey In karr.ToArray
i = i + 1
sht.Cells(i, 3) = wkey
Set vdic = kdic.Item(wkey)
Set varr = New ArrayList
For Each wval In vdic
varr.Add wval
Next wval
varr.Sort
j = 3
For Each wval In varr.ToArray
j = j + 1
sht.Cells(i, j) = wval
Next wval
Next wkey
Set varr = Nothing
Set karr = Nothing
Set vdic = Nothing
Set kdic = Nothing
End Sub

■Colletionオブジェクトについて

これまでDictionaryオブジェクトを使用したサンプル・プログラムを紹介して来ましたが、Dictionaryオブジェクトの代りに、VBAに内蔵されているCollectionオブジェクトを使用しても同様の処理を実現できます。

⇒詳細はMicrosoftのサイト（Collectionオブジェクト）を参照して下さい。

Collectionオブジェクトの詳細

Collectionオブジェクトで提供されているメソッドとプロパティは下表の通りです。

提供されている機能は4つしかなく、非常に単純でわかり易いですが、機能が提供されていない部分については、自分で処理を組む必要があります。

(Collectionオブジェクトのメソッドとプロパティ)

Addメソッド
説明	コレクションにメンバーを追加する
定義	Sub Add(Item, [Key], [Before], [After])
Countプロパティ(読み取り専用)
説明	コレクション内のオブジェクト数を取得する
定義	Function Count() As Long
Itemメソッド
説明	コレクションの特定メンバーを返す
定義	Function Item(Index)
Removeメソッド
説明	コレクションからメンバーを削除する
定義	Sub Remove(Index)

サンプル・プログラム(6)～Collectionオブジェクトを使用

Collectionオブジェクトにはキーの存在チェックを行う機能がないため、CheckKey関数により存在チェックを行っています。
また、キーを登録し、登録したキーでアイテムを参照することはできるものの、キーそのものを参照することができない(※)ため、キーとアイテムをそれぞれCollectionオブジェクトに登録しています。
なお、Dictionaryオブジェクトと同様、アイテムに配列を登録することはできますが、重複チェックやソート処理は自分で作り込む必要があります。

(※)Addメソッドでアイテム、キーの順に指定できますが、キーは任意項目で省略可能です(DictionaryオブジェクトのAddメソッドは、キー、アイテムの順に指定し、両方とも必須項目です)。
Itemメソッドでアイテムを取出す場合、「Item(添字)」または「Item(キー)」の指定が可能ですが、添字は1から始まることに注意が必要です(DictionaryオブジェクトのItemsメソッドで指定する添字は0から始まります)。

行番号14～29がキーとアイテムを登録する処理で、ExcelシートのA列に格納された文字列をキー、B列に格納された文字列をアイテムとして登録しています。
行番号15でキーが存在するかどうかを確認し、存在しない場合は行番号16でキー、行番号17～19でアイテムをCollectionに登録。存在する場合は行番号21でアイテムを配列に読込み、行番号22でアイテムが登録済かどうかチェックを行い、未登録の場合は行番号23で配列を再宣言(要素数を+1)、行番号24でアイテムを配列にセットし、行番号25でキーとアイテムを削除後、行番号26で再登録しています(※)。

(※)Colletionオブジェクトにはアイテムを変更する機能がないため、Removeメソッドで削除し、Addメソッドで再登録します。

行番号31～43がキーとアイテムを取出す処理で、行番号31～34でキーの内容を配列にコピーし、行番号35でSortArray関数により配列をソートしてから、行番号36～43でExcelシートに書き出しています。

行番号49～56のCheckKey関数はキーの存在チェックを行う関数ですが、行番号52でItemメソッドを実行、行番号53～55でエラー有無を判定し、エラーがない場合(=キーが存在している場合)にTrueを返しています。
(CheckValue関数、SortArray関数は単純な処理なので、説明は省略します)。

Dim sht As Worksheet
Dim kcol As Collection
Dim vcol As Collection
Dim karr() As Variant
Dim varr() As Variant
Dim i As Integer
Dim j As Integer
Private Sub Sample6()
Set sht = ActiveSheet
Set kcol = New Collection
Set vcol = New Collection
For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
If CheckKey(kcol, sht.Cells(i, 1).Value) = False Then
kcol.Add Item:=sht.Cells(i, 1).Value, Key:=sht.Cells(i, 1).Value
ReDim varr(1 To 1)
varr(1) = sht.Cells(i, 2).Value
vcol.Add Item:=varr, Key:=sht.Cells(i, 1).Value
Else
varr = vcol.item(sht.Cells(i, 1).Value)
If CheckValue(varr, sht.Cells(i, 2).Value) = False Then
ReDim Preserve varr(1 To UBound(varr) + 1)
varr(UBound(varr)) = sht.Cells(i, 2).Value
vcol.Remove (sht.Cells(i, 1).Value)
vcol.Add Item:=varr, Key:=sht.Cells(i, 1).Value
End If
End If
Next i
ReDim karr(1 To kcol.Count)
For i = 1 To kcol.Count
karr(i) = kcol.Item(i)
Next i
karr = SortArray(karr)
For i = LBound(karr) To UBound(karr)
sht.Cells(i, 3) = karr(i)
varr = vcol.Item(karr(i))
varr = SortArray(varr)
For j = LBound(varr) To UBound(varr)
sht.Cells(i, j + 3) = varr(j)
Next j
Next i
Set vcol = Nothing
Set kcol = Nothing
End Sub
Private Function CheckKey(col As Collection, key As Variant) As Boolean
CheckKey = False
On Error Resume Next
Call col.Item(key)
If Err.Number = 0 Then
CheckKey = True
End If
End Function
Private Function CheckValue(ValueTable() As Variant, wValue As Variant) As Boolean
Dim i As Integer
For i = LBound(ValueTable) To UBound(ValueTable)
If ValueTable(i) = wValue Then
Exit For
End If
Next i
If i <= UBound(ValueTable) Then
CheckValue = True
Else
CheckValue = False
End If
End Function
Private Function SortArray(arr() As Variant) As Variant()
Dim i, j As Integer
Dim w As Variant
For i = LBound(arr) To UBound(arr) – 1
For j = i + 1 To UBound(arr)
If arr(i) > arr(j) Then
w = arr(i)
arr(i) = arr(j)
arr(j) = w
End If
Next j
Next i
SortArray = arr
End Function

■SortedListオブジェクトについて

.NET FrameworkのSystem.Collectionネームスペースの中にSortedListオブジェクトがあったので、前項のサンプル・プログラム(6)をSortedListオブジェクトを使って書換えてみました。

SortedListオブジェクトについて、Microsoftのサイトには「キーによって並べ替えられ、キーとインデックスを使ってアクセスできる、キー/値ペアのコレクションを表します」と記載されています。
⇒詳細はMicrosoftのサイト（SortedListクラス）を参照して下さい。
また、参照設定の方法については、本稿の「ArrayListオブジェクトについて」の箇所を参照して下さい。

SortedListオブジェクトの詳細

SortedListオブジェクトで提供されているメソッドとプロパティは下表の通りです(※)。

下表を見ていただくとわかりますが、さまざまな機能が提供されており、

(SortedListオブジェクトのメソッドとプロパティ)

Addメソッド
説明	SortedListオブジェクトに新しいキーおよび項目を追加する
定義	Sub Add(key As Variant, value As Variant)
Clearメソッド
説明	SortedListオブジェクトから全ての要素を削除する
定義	Sub Clear()
Cloneメソッド
説明	SortedListオブジェクトの簡易コピーを作成する
定義	Function Clone() As Variant
Containsメソッド
説明	SortedListオブジェクトに特定のキーが格納されているかどうかを確認する
定義	Function Contains(key As Variant) As Boolean
CopyToメソッド
説明	SortedListオブジェクトの要素をArrayオブジェクトにコピーする
定義	Sub CopyTo(Array As _Array, index As Long)
Countプロパティ(読み取り専用)
説明	SortedListオブジェクトに格納されている要素の数を取得する
定義	Property Count() As Long
Equalsメソッド
説明	指定されたオブジェクトが現在のオブジェクトと等しいかどうかを判断する
定義	Function Equals(obj As Variant) As Boolean
GetEnumeratorメソッド
説明	IDictionaryEnumeratorオブジェクトを反復処理するSortedListオブジェクトを返す
定義	Function GetEnumerator() As IDictionaryEnumerator
GetHashCodeメソッド
説明	既定のハッシュ関数として機能する
定義	Function GetHashCode() As Long
GetTypeメソッド
説明	現在のインスタンスのTypeを取得する
定義	Function GetType() As _Type
IsFixedSizeプロパティ(読み取り専用)
説明	SortedListオブジェクトが固定サイズかどうかを示す値を取得する
定義	Property IsFixedSize() As Boolean
IsReadOnlyプロパティ(読み取り専用)
説明	SortedListオブジェクトが読み取り専用かどうかを示す値を取得する
定義	Property IsReadOnly() As Boolean
IsSynchronizedプロパティ(読み取り専用)
説明	SortedListオブジェクトへのアクセスが同期されているかどうかを示す値を取得する
定義	Property IsSynchronized() As Boolean
Itemプロパティ(既定)
説明	SortedListオブジェクト内の特定のキーに関連付けられている値を設定/取得する
定義	Property Item(key As Variant) As Variant
Keysプロパティ(読み取り専用)
説明	SortedListオブジェクト内のキーを取得する
定義	Property Keys() As ICollection
Removeメソッド
説明	指定したキーを持つ要素をSortedListオブジェクトから削除する
定義	Sub Remove(key As Variant)
SyncRootプロパティ(読み取り専用)
説明	SortedListオブジェクトへのアクセスを同期するために使用できるオブジェクトを取得する
定義	Property SyncRoot() As Variant
ToStringプロパティ(既定)(読み取り専用)
説明	現在のオブジェクトを表す文字列を返す
定義	Property ToString() As String
Valuesプロパティ(読み取り専用)
説明	SortedListオブジェクト内の値を取得する
定義	Property Values() As ICollection

サンプル・プログラム(7)～SortedListオブジェクトを使用

SortedListオブジェクトを入れ子にして、親のSortedListオブジェクトにはキー、子のSortedListオブジェクトには値を登録しています。

SortedListオブジェクトに要素を登録する場合は、Containsメソッドでキーの存在チェックを行い、存在しなければAddメソッドでキーを追加しています。
また、SortedListオブジェクトの要素を参照する場合は、インデックス番号順にキーと値(子のSortedListオブジェクト)に参照すれば、キーの昇順に読み出されますので、要素をソートする必要はありません。

行番号11～23がキーと値を登録する処理で、ExcelシートのA列に格納された文字列をキー、B列に格納された文字列を値として登録しています。
行番号12でキーが存在するかどうかを確認し、キーが存在しない場合は行番号13～15でキーと値を登録。キーが存在する場合は行番号17で値を子のSortedListオブジェクトに読込み、行番号18で値が登録済かどうかチェックを行い、未登録の場合は行番号19～20で値を追加しています。

行番号25～31がキーと値を取出す処理で、行番号26でキー、行番号27～30で値をExcelシートに書き出しています。