Excel VBAで実用ツール(Dictionary、Collection、ArrayList、SortedList編)

2022-04-01

目次

■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]ボタンを押下。

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

  1. Dim dic As Dictionary

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

  1. Set dic = New Dictionary
  2. 'ここにオブジェクトを使用した処理を記述
  3. 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プロパティに値を設定します。

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Dim sht As Worksheet
  2. Dim dic As Dictionary
  3. Dim i As Integer
  4. Private Sub Sample1()
  5.     Set sht = ActiveSheet
  6.     Set dic = New Dictionary
  7.     For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
  8.         If dic.Exists(sht.Cells(i, 1).Value) = False Then
  9.             dic.Add sht.Cells(i, 1).Value, 1
  10.         Else
  11.             dic.Item(sht.Cells(i, 1).Value) = dic.Item(sht.Cells(i, 1).Value) + 1
  12.         End If
  13.     Next i
  14.     For i = 0 To dic.Count – 1
  15.         sht.Cells(i + 1, 3) = dic.Keys(i)
  16.         sht.Cells(i + 1, 4) = dic.Items(i)
  17.     Next i
  18.     Set dic = Nothing
  19. End Sub

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

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

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

  1. Dim sht As Worksheet
  2. Dim dic As Dictionary
  3. Dim arr() As Variant
  4. Dim i As Integer
  5. Private Sub Sample2()
  6.     Set sht = ActiveSheet
  7.     Set dic = New Dictionary
  8.     For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
  9.         If dic.Exists(sht.Cells(i, 1).Value) = False Then
  10.             dic.Add sht.Cells(i, 1).Value, 1
  11.         Else
  12.             dic.Item(sht.Cells(i, 1).Value) = dic.Item(sht.Cells(i, 1).Value) + 1
  13.         End If
  14.     Next i
  15.     arr = dic.Keys
  16.     arr = SortArray(arr)
  17.     For i = LBound(arr) To UBound(arr)
  18.         sht.Cells(i + 1, 3) = arr(i)
  19.         sht.Cells(i + 1, 4) = dic.Item(arr(i))
  20.     Next i
  21.     Set dic = Nothing
  22. End Sub
  23. Private Function SortArray(arr() As Variant) As Variant()
  24.     Dim i, j As Integer
  25.     Dim w As Variant
  26.     For i = LBound(arr) To UBound(arr) – 1
  27.         For j = i + 1 To UBound(arr)
  28.             If arr(i) > arr(j) Then
  29.                 w = arr(i)
  30.                 arr(i) = arr(j)
  31.                 arr(j) = w
  32.             End If
  33.         Next j
  34.     Next i
  35.     SortArray = arr
  36. 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」を選択しました。

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

  1. Dim arr As ArrayList

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

  1. Set arr = New ArrayList
  2. 'ここにオブジェクトを使用した処理を記述
  3. 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の要素を順に取出しています。

  1. Dim sht As Worksheet
  2. Dim dic As Dictionary
  3. Dim arr As ArrayList
  4. Dim wkey As Variant
  5. Dim i As Integer
  6. Private Sub Sample3()
  7.     Set sht = ActiveSheet
  8.     Set dic = New Dictionary
  9.     For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
  10.         If dic.Exists(sht.Cells(i, 1).Value) = False Then
  11.             dic.Add sht.Cells(i, 1).Value, 1
  12.         Else
  13.             dic.item(sht.Cells(i, 1).Value) = dic.item(sht.Cells(i, 1).Value) + 1
  14.         End If
  15.     Next i
  16.     Set arr = New ArrayList
  17.     For Each wkey In dic.keys
  18.         arr.Add wkey
  19.     Next wkey
  20.     arr.Sort
  21.     i = 0
  22.     For Each wkey In arr.ToArray
  23.         i = i + 1
  24.         sht.Cells(i, 3) = wkey
  25.         sht.Cells(i, 4) = dic.item(wkey)
  26.     Next wkey
  27.     Set arr = Nothing
  28.     Set dic = Nothing
  29. End Sub

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

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

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

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

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

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

  1. If dic.Exists(キー) = False Then
  2.     ReDim アイテムの配列(0)
  3.     varr(0) = アイテム
  4.     dic.Add キー, アイテムの配列
  5. Else
  6.     アイテムの配列 = dic.Item(キー)
  7.     ReDim Preserve アイテムの配列(UBound(アイテムの配列) + 1)
  8.     varr(アイテムの配列) = アイテム
  9.     dic.Item(キー) = アイテムの配列
  10. 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~」にすると、配列が初期化されてしまいます。

  1. Dim sht As Worksheet
  2. Dim dic As Dictionary
  3. Dim karr() As Variant
  4. Dim varr() As Variant
  5. Dim i As Integer
  6. Dim j As Integer
  7. Private Sub Sample4()
  8.     Set sht = ActiveSheet
  9.     Set dic = New Dictionary
  10.     For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
  11.         If dic.Exists(sht.Cells(i, 1).Value) = False Then
  12.             ReDim varr(0)
  13.             varr(0) = sht.Cells(i, 2).Value
  14.             dic.Add sht.Cells(i, 1).Value, varr
  15.         Else
  16.             varr = dic.Item(sht.Cells(i, 1).Value)
  17.             If CheckValue(varr, sht.Cells(i, 2).Value) = False Then
  18.                 ReDim Preserve varr(UBound(varr) + 1)
  19.                 varr(UBound(varr)) = sht.Cells(i, 2).Value
  20.                 dic.Item(sht.Cells(i, 1).Value) = SortArray(varr)
  21.             End If
  22.         End If
  23.     Next i
  24.     karr = dic.keys
  25.     karr = SortArray(karr)
  26.     For i = 0 To dic.Count – 1
  27.         sht.Cells(i + 1, 3) = karr(i)
  28.         varr = dic.Item(karr(i))
  29.         For j = 0 To UBound(varr)
  30.             sht.Cells(i + 1, j + 4) = varr(j)
  31.         Next j
  32.     Next i
  33.     Set dic = Nothing
  34. End Sub
  35. Private Function CheckValue(ValueTable() As Variant, wValue As Variant) As Boolean
  36.     Dim i As Integer
  37.     For i = LBound(ValueTable) To UBound(ValueTable)
  38.         If ValueTable(i) = wValue Then
  39.             Exit For
  40.         End If
  41.     Next i
  42.     If i <= UBound(ValueTable) Then
  43.         CheckValue = True
  44.     Else
  45.         CheckValue = False
  46.     End If
  47. End Function
  48. Private Function SortArray(arr() As Variant) As Variant()
  49.     Dim i, j As Integer
  50.     Dim w As Variant
  51.     For i = LBound(arr) To UBound(arr) – 1
  52.         For j = i + 1 To UBound(arr)
  53.             If arr(i) > arr(j) Then
  54.                 w = arr(i)
  55.                 arr(i) = arr(j)
  56.                 arr(j) = w
  57.             End If
  58.         Next j
  59.     Next i
  60.     SortArray = arr
  61. 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シートに書き出しています。

  1. Dim sht As Worksheet
  2. Dim kdic As Dictionary
  3. Dim vdic As Dictionary
  4. Dim karr As ArrayList
  5. Dim varr As ArrayList
  6. Dim wkey As Variant
  7. Dim wval As Variant
  8. Dim i As Integer
  9. Dim j As Integer
  10. Private Sub Sample5()
  11.     Set sht = ActiveSheet
  12.     Set kdic = New Dictionary
  13.     For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
  14.         If kdic.Exists(sht.Cells(i, 1).Value) = False Then
  15.             Set vdic = New Dictionary
  16.             vdic.Add sht.Cells(i, 2).Value, “"
  17.             kdic.Add sht.Cells(i, 1).Value, vdic
  18.         Else
  19.             Set vdic = kdic.Item(sht.Cells(i, 1).Value)
  20.             If vdic.Exists(sht.Cells(i, 2).Value) = False Then
  21.                 vdic.Add sht.Cells(i, 2).Value, “"
  22.                 Set kdic.Item(sht.Cells(i, 1).Value) = vdic
  23.             End If
  24.         End If
  25.     Next i
  26.     Set karr = New ArrayList
  27.     For Each wkey In kdic
  28.         karr.Add wkey
  29.     Next wkey
  30.     karr.Sort
  31.     i = 0
  32.     For Each wkey In karr.ToArray
  33.         i = i + 1
  34.         sht.Cells(i, 3) = wkey
  35.         Set vdic = kdic.Item(wkey)
  36.         Set varr = New ArrayList
  37.         For Each wval In vdic
  38.             varr.Add wval
  39.         Next wval
  40.         varr.Sort
  41.         j = 3
  42.         For Each wval In varr.ToArray
  43.             j = j + 1
  44.             sht.Cells(i, j) = wval
  45.         Next wval
  46.     Next wkey
  47.     Set varr = Nothing
  48.     Set karr = Nothing
  49.     Set vdic = Nothing
  50.     Set kdic = Nothing
  51. 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関数は単純な処理なので、説明は省略します)。

  1. Dim sht As Worksheet
  2. Dim kcol As Collection
  3. Dim vcol As Collection
  4. Dim karr() As Variant
  5. Dim varr() As Variant
  6. Dim i As Integer
  7. Dim j As Integer
  8. Private Sub Sample6()
  9.     Set sht = ActiveSheet
  10.     Set kcol = New Collection
  11.     Set vcol = New Collection
  12.     For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
  13.         If CheckKey(kcol, sht.Cells(i, 1).Value) = False Then
  14.             kcol.Add Item:=sht.Cells(i, 1).Value, Key:=sht.Cells(i, 1).Value
  15.             ReDim varr(1 To 1)
  16.             varr(1) = sht.Cells(i, 2).Value
  17.             vcol.Add Item:=varr, Key:=sht.Cells(i, 1).Value
  18.         Else
  19.             varr = vcol.item(sht.Cells(i, 1).Value)
  20.             If CheckValue(varr, sht.Cells(i, 2).Value) = False Then
  21.                 ReDim Preserve varr(1 To UBound(varr) + 1)
  22.                 varr(UBound(varr)) = sht.Cells(i, 2).Value
  23.                 vcol.Remove (sht.Cells(i, 1).Value)
  24.                 vcol.Add Item:=varr, Key:=sht.Cells(i, 1).Value
  25.             End If
  26.         End If
  27.     Next i
  28.     ReDim karr(1 To kcol.Count)
  29.     For i = 1 To kcol.Count
  30.         karr(i) = kcol.Item(i)
  31.     Next i
  32.     karr = SortArray(karr)
  33.     For i = LBound(karr) To UBound(karr)
  34.         sht.Cells(i, 3) = karr(i)
  35.         varr = vcol.Item(karr(i))
  36.         varr = SortArray(varr)
  37.         For j = LBound(varr) To UBound(varr)
  38.             sht.Cells(i, j + 3) = varr(j)
  39.         Next j
  40.     Next i
  41.     Set vcol = Nothing
  42.     Set kcol = Nothing
  43. End Sub
  44. Private Function CheckKey(col As Collection, key As Variant) As Boolean
  45.     CheckKey = False
  46.     On Error Resume Next
  47.     Call col.Item(key)
  48.     If Err.Number = 0 Then
  49.         CheckKey = True
  50.     End If
  51. End Function
  52. Private Function CheckValue(ValueTable() As Variant, wValue As Variant) As Boolean
  53.     Dim i As Integer
  54.     For i = LBound(ValueTable) To UBound(ValueTable)
  55.         If ValueTable(i) = wValue Then
  56.             Exit For
  57.         End If
  58.     Next i
  59.     If i <= UBound(ValueTable) Then
  60.         CheckValue = True
  61.     Else
  62.         CheckValue = False
  63.     End If
  64. End Function
  65. Private Function SortArray(arr() As Variant) As Variant()
  66.     Dim i, j As Integer
  67.     Dim w As Variant
  68.     For i = LBound(arr) To UBound(arr) – 1
  69.         For j = i + 1 To UBound(arr)
  70.             If arr(i) > arr(j) Then
  71.                 w = arr(i)
  72.                 arr(i) = arr(j)
  73.                 arr(j) = w
  74.             End If
  75.         Next j
  76.     Next i
  77.     SortArray = arr
  78. End Function

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

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

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

SortedListオブジェクトの詳細

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

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

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

(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シートに書き出しています。

  1. Dim sht As Worksheet
  2. Dim ksrt As SortedList
  3. Dim vsrt As SortedList
  4. Dim i As Integer
  5. Dim j As Integer
  6. Private Sub Sample7()
  7.     Set sht = ActiveSheet
  8.     Set ksrt = New SortedList
  9.     For i = 1 To sht.Cells(Rows.Count, 1).End(xlUp).Row
  10.         If ksrt.Contains(sht.Cells(i, 1).Value) = False Then
  11.             Set vsrt = New SortedList
  12.             vsrt.Add sht.Cells(i, 2).Value, “"
  13.             ksrt.Add sht.Cells(i, 1).Value, vsrt
  14.         Else
  15.             Set vsrt = ksrt.item(sht.Cells(i, 1).Value)
  16.             If vsrt.Contains(sht.Cells(i, 2).Value) = False Then
  17.                 vsrt.Add sht.Cells(i, 2).Value, “"
  18.                 Set ksrt.item(sht.Cells(i, 1).Value) = vsrt
  19.             End If
  20.         End If
  21.     Next i
  22.     For i = 0 To ksrt.keys.Count – 1
  23.         sht.Cells(i + 1, 3) = ksrt.getkey(i)
  24.         Set vsrt = ksrt.getbyindex(i)
  25.         For j = 0 To vsrt.keys.Count – 1
  26.             sht.Cells(i + 1, j + 4) = vsrt.getkey(j)
  27.         Next j
  28.     Next i
  29.     Set vsrt = Nothing
  30.     Set ksrt = Nothing
  31. End Sub

国本温子(著),緑川吉行(著),できるシリーズ編集部(著)
出版社:インプレス
発売日:2022/3/23
単行本(ソフトカバー):A5判/912ページ

大村あつし(著),古川順平(著)
出版社:技術評論社
発売日:2021/1/9
単行本(ソフトカバー):A5判/800ページ

高橋宣成(著)
出版社:技術評論社
発売日:2019/11/25
単行本(ソフトカバー):B5変形判/576ページ

実用ツール

Posted by hides