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

【本日のミッション】

コンボボックスに重複しないユニークな値リストを設定せよ。
今回値リストに使用する元データの項目は複数(3項目以上)あります。

ミッションの概要

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

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

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

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

作業手順

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

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

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

2.配列「ary_d」を、Dictionaryオブジェクト「dic_d」に格納します。
Dictionaryオブジェクトの「Key」は重複できない仕様のため、ユニークなリストになります。ユニークになるキー(key)には名前を、値(Items)には行番号を入れます。同じ名前(key)が複数出てくる場合は、一番初めに処理したデータを使用します

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

3.Dictionaryオブジェクト「dic_d」の1行目から順に、配列ary_listに格納します。
ID・性別・年齢は「dic_d」の値(Item)の「行番号」を使用して、配列「ary_d」から取得します。

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

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

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

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

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

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

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

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

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

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

ary_d = Worksheets("サザエさん").Range("A1:D24")

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

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

コンボボックスのプルダウンリストを表示させると、ユニークな値リストが表示されます。

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

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

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

セルデータを配列に格納

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

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

Dim ary_d
ary_d = Worksheets("サザエさん").Range("A1:D24")

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

Dictionaryオブジェクトとは

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

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

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

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

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

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

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

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

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

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

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

メソッド

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

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

プロパティ

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

プロパティ説明
CountDictionaryオブジェクトに登録された要素の数。
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
  1. まず、変数「dic_d」をObjectとして宣言します。

    Dim dic_d As Object

  2. 次に、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)を配列にして返します。

  3. 処理の最後に、変数「dic_d」を解放します。

    Set dic_d = Nothing

【メリット】

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

【デメリット】

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

    ↓これがインテリセンス機能(自動メンバー表示)です。
    コンボボックスに重複しないユニークな値リスト(複数列 3列以上)を設定する(Excel VBA)

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

参照設定を利用する方法

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

【利用方法】

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

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

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

    コンボボックスに重複しないユニークな値リスト(複数列 3列以上)を設定する(Excel VBA)
  2. 参照設定が完了したら、下記コードの記述が可能になります。
    Dim dic_d As New Scripting.Dictionary
    
    ***変数「dic_d」を利用した処理***
    
    Set dic_d = Nothing
  3. 変数「dic_d」をDictionary オブジェクトとして宣言します。
    この記述をすることで、変数「dic_d」をDictionary オブジェクトとして使うことができます。

    Dim dic_d As New Scripting.Dictionary

  4. 処理の最後に、変数「dic_d」を解放します。

    Set dic_d = Nothing

【メリット】

  • VBE上でDictionary オブジェクトに関するインテリセンス機能(自動メンバー表示)を使うことができます。
    メンバーが表示されるので、入力ミスが減ります。コンボボックスに重複しないユニークな値リスト(複数列 3列以上)を設定する(Excel VBA)

【デメリット】

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

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

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

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

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

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(t_row_d,2), t_row_d 

同じ名前が何度か出てきますが、今回は初めて出てきた名前と、同じ行の年齢を使用するようにしています。

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

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

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

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

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

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

そのエラーを回避するために、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(t_row_d, 2)) =False Then
    dic_d.Add ary_d(t_row_d, 2), t_row_d 
end if
■■■スポンサーリンク■■■

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

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

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

dic_d.Count

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

参考: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 3)

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

DictionaryオブジェクトのItemsメソッドを使って、対象データの行番号を取得し、対象データを配列に格納する。

Itemsメソッド

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

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

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

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

dic_d.items()(id)

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

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

Itemsメソッドで得た行番号を使用して、配列ary_dから「ID」「名前」「性別」「年齢」のデータを取得し、別の配列ary_listに格納します。

ary_list(id, 0) = ary_d(t_row_d, 1) 'ID
ary_list(id, 1) = ary_d(t_row_d, 2) '名前
ary_list(id, 2) = ary_d(t_row_d, 3) '性別
ary_list(id, 3) = ary_d(t_row_d, 4) '年齢

今回はわかりやすくするために、あえてfor~Nextを使用していません。

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

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

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

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

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

今回は、上記で作った配列「ary_list」を使用します。

ComboBox1.List = ary_list

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

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

【参考】

VBA(マクロ)作成の前、リボンに開発タブを表示させる(Excel VBA)
ユーザーフォームの作成方法 準備(Excel VBA)
ユーザーフォームのオブジェクトの名前を「UserForm1」から変更する方法(Excel VBA)
ユーザーフォームの上のバー(タイトルバー)の名前を「UserForm1」から変更する(Excel VBA)
ユーザーフォームのオブジェクトウィンドウとコードウィンドウを表示切替させる方法(Excel VBA)
「オブジェクト名が不正です」を出さないようにユーザーフォームオブジェクト名を命名規則に従って変更(Excel VBA)
エクセルファイルが開いた時にユーザーフォームを表示させる方法(Excel VBA)
エクセル フォームのラベルのテキストを縦書きにする方法(Excel VBA)
AddItemメソッドを使ってコンボボックスの値リストを設定する(Excel VBA)
RowSourceプロパティを使ってセル範囲とコンボボックスの値リストをリンク設定させる(Excel VBA)
RowSourceプロパティでリンク設定したコンボボックス値リストにデータを追加・削除する(Excel VBA)
コンボボックスにListプロパティを使って 配列に格納したセルデータを値リストとして設定する(Excel VBA)
既存のコンボボックスの値リストにAddItemメソッドでデータを追加する(Excel VBA)
既存のコンボボックスの値リストのデータを、Clearメソッドで全て削除して初期化する(Excel VBA)
コンボボックス 値リストから選択しているデータを、クリア(選択解除)して空白にする(Excel VBA)
既存のコンボボックスの値リストの項目を1件 RemoveItemメソッドで削除する(Excel VBA)
選択する項目の文字数に応じてコンボボックスのサイズ(幅・高さ)を自動変更する(Excel VBA)
コンボボックス 値リストに設定した項目以外は入力できないようにする(Excel VBA)
コンボボックス 値リストに設定した項目以外も入力できるようにする(Excel VBA)
コンボボックス プルダウンを開いた時の値リストの最大表示件数を指定する(Excel VBA)
コンボボックス ドロップダウンのポチってするところ(ドロップボタンスタイル)を▼から変更する(Excel VBA)
コンボボックス リストの中から初期値を設定するListIndexプロパティ(Excel VBA)
コンボボックスの値リストに設定された項目の件数を取得する(Excel VBA)
RowSourceプロパティを使って、複数列のセル範囲とコンボボックスの値リストをリンク設定(Excel VBA)
コンボボックスにListプロパティを使って配列に格納した複数列のセルデータを値リストとして設定(Excel VBA)
複数列の値リストからコンボボックスに表示する列を設定(Excel VBA)
コンボボックス 複数列の値リストの列幅を変えたり非表示にしたりする設定(Excel VBA)
複数列の値リストから コンボボックスに表示している列のデータを取得(Excel VBA)
コンボボックスに表示されている項目がリストの上から何番目(インデックス値)かを取得(Excel VBA)
指定した行(インデックス)のデータを 値リストからコンボボックスに選択表示させる(Excel VBA)
コンボボックスの値リストに列見出しを表示する設定 ColumnHeadsで見出しが設定できない理由(Excel VBA)
コンボボックス ドロップダウンの▼(ドロップボタン)を表示・非表示にするタイミングを設定(Excel VBA)
コンボボックス 表面の形状を色々な立体的表示に変更する設定(Excel VBA)
コンボボックスの左端の余白「セレクションマージン」って何?この余白を無くす方法は?(Excel VBA)
コンボボックス値リストAddItemメソッド・RowSourceプロパティ・Listプロパティ違い Excel VBA
コンボボックス 値リストの表示項目件数を変更する設定(Excel VBA)
コンボボックスで選択した値を別フィールドにドラッグアンドドロップで簡単テキスト移動できる設定(Excel VBA)
コンボボックス 複数列ある値リストから表示されていない列のデータを取得する方法1(Excel VBA)
コンボボックス 複数列ある値リストから表示されていない列のデータを取得する方法2(Excel VBA)
コンボボックス TextプロパティとValueプロパティの違い(Excel VBA)
コンボボックスに重複しないユニークな値リスト(1列)を設定する(Excel VBA)
コンボボックスに重複しないユニークな値リスト(複数2列)を設定する(Excel VBA)
コンボボックスに重複しないユニークな値リスト(複数列 3列以上)を設定する(Excel VBA)

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