コンボボックスに重複しないユニークな値リスト（複数2列）を設定する（Excel VBA)

【本日のミッション】

コンボボックスに重複しないユニークな値リストを設定せよ。
今回値リストに使用する元データの項目は2つです。

1 ミッションの概要
2 作業手順
3 ユーザーフォーム Initializeにコード記述
4 ユーザーフォームを表示して動作を確認
5 セルデータを配列に格納
6 Dictionaryオブジェクトとは
7 Dictionary オブジェクトのメソッド・プロパティ
- 7.1 メソッド
- 7.2 プロパティ
8 Dictionary オブジェクトを使う準備
- 8.1 CreateObject 関数を利用する方法
- 8.2 参照設定を利用する方法
9 Dictionary オブジェクトにキー(key)・値(Items)を登録し、ユニークなリストを作成する
- 9.1 Addメソッドを利用する方法
10 Dictionary オブジェクトに指定したキー(key)が登録されているか確認してエラー回避
11 Dictionaryオブジェクトの件数を数える
12 Redimで配列数を設定する
13 DictionaryオブジェクトのKeysとItemsを、配列に格納する
- 13.1 Keysメソッド
- 13.2 Itemsメソッド
14 配列をコンボボックスの値リストに設定する
15 【参考】

ミッションの概要

コンボボックスに重複しないユニークな値リスト(2列)を設定せよ、というのが今回のミッションです。今回は元データの項目数が2つですが、1つの場合はコチラ→コンボボックスに重複しないユニークな値リスト（1列）を設定する（Excel VBA)の方が簡単です。

コンボボックスにListプロパティを使って配列に格納した複数列のセルデータを値リストとして設定（Excel VBA)で作成したコンボボックスを利用して設定していきます。

重複データを多数含むリストを、Dictionary オブジェクトを用いてユニークなリストにし、コンボボックスに設定します。同じ名前に異なる年齢が入力されている場合、今回の処理では初めに処理した年齢を使用するようにしています。

作業手順

今回作成するマクロの手順は下記の通りです。

１．セル範囲のデータを配列「ary_d」に格納します。

２．配列「ary_d」を、Dictionaryオブジェクト「dic_d」に格納します。
Dictionaryオブジェクトの「Key」は重複できない仕様のため、ユニークなリストになります。ユニークになるキー（key）には名前を、値（Items）には年齢を入れます。同じ名前（key）に異なる年齢が出てくる場合、初めに処理する名前（key）の年齢を使用します。

3．Dictionaryオブジェクト「dic_d」のKeysとItemsを、配列ary_listに格納します。

４．配列ary_listを、コンボボックスの値リストに設定します。

ユーザーフォーム Initializeにコード記述

上記作業手順を、ユーザーフォームのIntializeにコード記述します。

コンボボックスにListプロパティを使って配列に格納した複数列のセルデータを値リストとして設定（Excel VBA)で作成した「UserForm_Initialize」を下記の通り変更します。

Private Sub UserForm_Initialize()
    '【変数】
    Dim ary_d               'セルデータ用配列
    Dim dic_d As Object     'Dictionaryオブジェクト
    Dim d_ct As Long        '配列ary_d用カウンター
    Dim id As Long          'インデックス番号
    Dim ary_list            '値リスト用配列
    
    '■セルデータを配列ary_dに取り込み
    ary_d = Worksheets("サザエさん").Range("A2:B24")
    
    '■Dictionaryオブジェクトの準備
    Set dic_d = CreateObject("Scripting.Dictionary")
    
    '■配列ary_dをdic_dに格納
    For d_ct = 1 To UBound(ary_d, 1)
    
        '指定したキーがまだ登録されていなければ登録する
        If dic_d.Exists(ary_d(d_ct, 1)) = False Then
            dic_d.Add ary_d(d_ct, 1), ary_d(d_ct, 2) 'キー：名前、値：年齢
        End If
    Next d_ct
    
    '■配列ary_listの初期設定
    ReDim ary_list(0 To dic_d.Count - 1, 0 To 1)
    
    '■dic_dを配列ary_listに格納
    For id = 0 To dic_d.Count - 1
        ary_list(id, 0) = dic_d.Keys()(id)      'キー：名前
        ary_list(id, 1) = dic_d.items()(id)     '値：年齢
    Next id
    
    '■コンボボックスにdic_dを設定
    With ComboBox1
        .ColumnCount = 2    '表示列数
        .List = ary_list    '配列ary_listを値リストに設定
    End With
   
    '■変数の解放
    Set dic_d = Nothing
End Sub

■■■スポンサーリンク■■■

ユーザーフォームを表示して動作を確認

このコード内にカーソルを置いたまま、［F5］キーをクリックすると（ユーザーフォームの実行）ユーザーフォームが表示されます。

配列に入れるデータの参照先にシート名が含まれていないと、アクティブになっているシートのセルデータが使用されるので注意が必要です。シート名を参照先に含めていない場合は、参照データのシートをアクティブにしてからマクロを実行してください。

ary_d = Worksheets("サザエさん").Range("A2:B24")

メイン画面が表示されます。

コンボボックスの値リストから項目を選択すると、ユニークな値リストが表示されます。

今回は、［閉じる］ボタンを付けていないため［×］ボタンでユーザーフォームを閉じます。

セルデータを配列に格納

セル範囲のデータは、一括で配列に格納することができます。
（参照：セル範囲を一気に変数（配列）に入れる～バリアント型(Variant)変数を配列として使用する～（Excel VBA））

変数「ary_d」を、バリアント型(Variant)で宣言し（データ型を省略すると、勝手にバリアント型(Variant)になります。）セル範囲をそのまま入れちゃうだけでOK!

Dim ary_d
ary_d = Worksheets("サザエさん").Range("A2:B24")

Dictionaryオブジェクトとは

通常の配列は、１つの要素に１つの値を登録することができます。

下記例でいうと、インデックス番号「0」の要素には、「サザエ」という値が入っています。

インデックス番号「0」を指定すると、「サザエ」という値を取り出すことができます。

Dictionary オブジェクトの場合、１つのキー(key)に１つの値(item)を関連付けることができます。そして1つ目の要素から「0」から始まるインデックス番号が付きます。

keyは重複登録不可という仕様のため、ユニークなリストを作るのに利用できます。

下記例でいうと、key「サザエ」に、item「24」が入っています。そしてそのデータのインデックス番号は「0」です。

key「サザエ」を指定すると、item「24」を取り出すことができます。また、インデックス番号「0」を指定するとkey「サザエ」、item「24」どちらの値も取り出すことができます。

参考：Dictionary オブジェクト（連想配列）とはビッグデータ高速処理を可能にする技 (Excel VBA)

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

メソッド

Dictionary オブジェクトのメソッドは下記の通りです。

メソッド	説明
Add key, item	新しいキー(key)を登録し、値(item)を関連付けます。（キーが既に存在する場合はエラーになります。）
Exists(key)	指定したキー(key)が存在するかどうか、論理値(True/False)を返します。
Items	Dictionaryオブジェクトの値（item）全てを配列にして返します。 ※この時の配列のインデックス番号は0(ゼロ)から始まります。「items()(インデックス番号)」で、指定したインデックス番号の値（item)を取得します。
Keys	Dictionaryオブジェクトのキー(key)全てを配列にして返します。 ※この時の配列のインデックス番号は0(ゼロ)から始まります。「keys()(インデックス番号)」で、指定したインデックス番号のキー(key)を取得します。
Remove key	指定したキー(key)と、値（item）を削除します。（指定したキーが存在しない場合はエラーになります。）
RemoveAll	全てのキー(key)と値（item）を削除します。

プロパティ

Dictionary オブジェクトのプロパティは下記の通りです。

プロパティ	説明
Count	Dictionaryオブジェクトに登録された要素の数。
Item(key)	【キー(key)が登録されている場合】・キー(key)に関連付けられた値（item）を取得します。・「Dictionaryオブジェクト.Item(key) =別の値」で、既存の値(item)を別の値に変更します。【キー(key)が登録されていない場合】新しいキー(key)を登録し、値(item)を関連付けます。（キーが既に存在する場合でもエラーになりません。）いずれの場合も、「item」は省略可能。
Key(key)	キー(key)を他のキーに変更します。
CompareMode	キー(key)の区別に、大文字と小文字を区別するか否か指定。 vbBinaryCompare(0)：大文字と小文字を区別します。 vbTextCompare(1)　：大文字と小文字を区別しません。

参考：Dictionary オブジェクト（連想配列）とはビッグデータ高速処理を可能にする技 (Excel VBA)

■■■スポンサーリンク■■■

Dictionary オブジェクトを使う準備

Dictionary オブジェクトは普通の配列のように、宣言するだけ使用することはできません。
Dictionary オブジェクトを使用する方法は、以下の2つになります。

CreateObject 関数を利用する方法

関数を利用して、Dictionary オブジェクトを使えるようにする方法です。今回はこちらの方法を使用しています。

【利用方法】

Dim dic_d As Object
Set dic_d = CreateObject("Scripting.Dictionary")

＊＊＊変数「dic_d」を利用した処理＊＊＊

Set dic_d = Nothing

まず、変数「dic_d」をObjectとして宣言します。

Dim dic_d As Object
次に、CreateObject関数を利用して、変数「dic_d」にDictionary オブジェクトをセットします。

Set dic_d = CreateObject(“Scripting.Dictionary”)

この記述をすることで、変数「dic_d」をDictionary オブジェクトとして使うことができます。

例1：dic_d.Exists(key)→キー(key)の存在の確認します。
例2：dic_d.Keys→Dictionary オブジェクトのキー(key)を配列にして返します。
処理の最後に、変数「dic_d」を解放します。

Set dic_d = Nothing

【メリット】

関数の利用だけで、簡単なコードの記述だけで利用することができます。

【デメリット】

VBE上でDictionary オブジェクトに関するインテリセンス機能（自動メンバー表示）が効かなくなります。

↓これがインテリセンス機能（自動メンバー表示）です。

インテリセンス機能（自動メンバー表示）が働かないと、入力ミスの可能性が生じます。
この機能が必要な方は、次にご紹介します参照設定をご利用ください。

参照設定を利用する方法

VBE画面の設定を利用する方法です。

【利用方法】

VBE画面[ツール] ー [参照設定] をクリックします。

「Microsoft Scripting Runtime」にチェックを入れて<OK>ボタンをクリックし
ます。

参照設定が完了したら、下記コードの記述が可能になります。

Dim dic_d As New Scripting.Dictionary

＊＊＊変数「dic_d」を利用した処理＊＊＊

Set dic_d = Nothing

変数「dic_d」をDictionary オブジェクトとして宣言します。
この記述をすることで、変数「dic_d」をDictionary オブジェクトとして使うことができます。

Dim dic_d As New Scripting.Dictionary
処理の最後に、変数「dic_d」を解放します。

Set dic_d = Nothing

【メリット】

VBE上でDictionary オブジェクトに関するインテリセンス機能（自動メンバー表示）を使うことができます。
メンバーが表示されるので、入力ミスが減ります。

【デメリット】

参照設定をしていないと、変数「dic_d」の宣言の処理でエラーが出てしまいます。
「コンパイルエラー: ユーザ定義型は定義されていません。」

Dictionary オブジェクトにキー(key)・値(Items)を登録し、ユニークなリストを作成する

Dictionary オブジェクトのキー（key）は重複不可の仕様のため、同じキー（key）を2つ以上登録することはできません。自動的に重複なしのユニークなリストになります。

Dictionary オブジェクトにキー（key）を登録する方法には、Addメソッドを利用する方法、Itemプロパティを利用する方法の2つありますが、今回はAddメソッドを利用する方法を使用しています。

（詳しくはコチラ→Dictionary オブジェクトに新しくキー（key）を登録し、値（item）を関連付ける（Excel VBA））

Addメソッドを利用する方法

メソッド	説明
Add key, item	新しいキー(key)を登録し、値(item)を関連付けます。（キーが既に存在する場合はエラーになります。）

Dictionaryオブジェクト.Add キー, 値

↑Dictionary オブジェクトに キー（key）を登録し、そのキー（key）に値（item）を関連付けます。

今回は配列ary_dの名前をキー（key）として登録し、年齢を値（item）として登録します。

dic_d.Add ary_d(d_ct, 1), ary_d(d_ct, 2)

Dictionary オブジェクトに指定したキー(key)が登録されているか確認してエラー回避

Dictionary オブジェクトのAddメソッドを実行する際、既に同じkeyが登録してあると、

実行時エラー’457′:
このキーは既にこのコレクションの要素に割り当てられています。

というエラーが出てしまいます。

そのエラーを回避するために、Existsメソッドを使用して登録済のキー(key)かどうか確認し、未登録の場合のみ、Addメソッドを行うようにします。

メソッド	説明
Exists(key)	指定したキー(key)が存在するかどうか、論理値(True/False)を返します。

Dictionaryオブジェクト.Exists(キー)

↑Dictionary オブジェクトにキー(key)が既に登録されていたら「True」を返し、登録されていなかったら「False」を返します。

下記例は キー(key)が dictinary オブジェクト「dic_d」に登録されていない（False）場合、If 内のAddメソッドを実行します。

If dic_d.Exists(キー) ＝False Then
    dic_d.Add キー, 値
end if

今回はキー「名前」が登録されていない場合、「名前」「値」の登録を行います。

If dic_d.Exists(ary_d(d_ct, 1)) ＝False Then
    dic_d.Add ary_d(d_ct, 1), ary_d(d_ct, 2) 
end if

■■■スポンサーリンク■■■

Dictionaryオブジェクトの件数を数える

プロパティ	説明
Count	Dictionaryオブジェクトに登録された要素の数。

Dictionaryオブジェクト.Count

指定したDictionaryオブジェクトに登録された要素の数を返します。
今回は、Dictionaryオブジェクト「dic_d」の要素数「８」を返します。

dic_d.Count

参考：Dictionary オブジェクト（連想配列）とはビッグデータ高速処理を可能にする技 (Excel VBA)「Dictionary オブジェクトに登録されたキー(Key)の数をカウントする」の項

Redimで配列数を設定する

処理開始時は値リストの件数が不明なため、配列「ary_list」の要素数を宣言することができません。

Dictionary オブジェクト「dic_d」を作成後、値リストの件数が判明するので、配列「ary_list」の配列数を設定します。

今回は2次元配列を使用しますので、配列の縦方向が1次元（行）、横方向が2次元（列）となります。

ReDim 配列名(1次元インデックスの最小値 To 最大値, 2次元インデックスの最小値 To 最大値)

Dictionary オブジェクト「dic_d」の件数（dic_d.Count）は「8」ですが、今回はインデックスの最小値を「0」としたいため「dic_d.Count – 1」=「7」とします。

最小値を「0」とする理由は、この後Dictionary オブジェクトから配列に格納する時のインデックス番号を同じにするためです。

ReDim ary_list(0 To dic_d.Count - 1, 0 To 1)

DictionaryオブジェクトのKeysとItemsを、配列に格納する

Keysメソッド

「keys()(インデックス番号)」で、指定したインデックス番号のキー(key)を取得します。

メソッド	説明
Keys	Dictionaryオブジェクトのキー(key)全てを配列にして返します。 ※この時の配列のインデックス番号は0(ゼロ)から始まります。また、「keys()(インデックス番号)」で、指定したインデックス番号のキー(key)を取得します。

Dictionaryオブジェクト.keys()(インデックス番号)

keysメソッドで返されるキー(key)全体「keys」から、インデックス番号のキー(key)を取得します。

今回は、Dictionaryオブジェクト「dic_d」のインデックス番号「id」から、キー(key)「名前」を取得します。

dic_d.Keys()(id)

Dictionaryオブジェクトのインデックス番号は「0」から始まるため、インデックス番号「0」は1つ目のデータになります。

参考：Dictionary オブジェクト（連想配列）に登録されたキー（key）をインデックス番号から取得（Excel VBA）

Itemsメソッド

「items()(インデックス番号)」で、指定したインデックス番号の値（item)を取得します。

メソッド	説明
Items	Dictionaryオブジェクトの値（item）全てを配列にして返します。 ※この時の配列のインデックス番号は0(ゼロ)から始まります。また、「items()(インデックス番号)」で、指定したインデックス番号の値（item)を取得します。

Dictionaryオブジェクト.items()(インデックス番号)

itemsメソッドで返される値（item)全体から、インデックス番号の値（item)を取得します。

今回は、Dictionaryオブジェクト「dic_d」のインデックス番号「id」から、値（item)「年齢」を取得します。

dic_d.items()(id)

Dictionaryオブジェクトのインデックス番号は「0」から始まるため、インデックス番号「０」は１つ目のデータになります。

参考：Dictionary オブジェクト（連想配列）に登録された値（item）を、キー（key）やインデックス番号を指定して取得する（Excel VBA）

配列をコンボボックスの値リストに設定する

Listプロパティを使用すると、配列に格納したデータを一括で値リストに設定することができます。

コンボボックスオブジェクト.List = 配列

今回は、上記で説明したKeysメソッド・Itemsメソッドで作った配列「ary_list」を使用します。

ComboBox1.List = ary_list

参考：コンボボックスにListプロパティを使って配列に格納したセルデータを値リストとして設定する（Excel VBA)

【参考】

■■■スポンサーリンク■■■