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

【本日のミッション】

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

ミッションの概要

リストボックスに重複しないユニークな値リスト(2列)を設定せよ、というのが今回のミッションです。

1つの場合はコチラ→リストボックスに重複しないユニークな値リスト(1列)を設定する(Excel VBA)
3つ以上の場合はコチラ→リストボックスに重複しないユニークな値リスト(複数列 3列以上)を設定する(Excel VBA)

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

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

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

作業手順

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

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

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

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

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

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

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

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

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

ユーザーフォーム 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 ListBox1
        .ColumnCount = 2    '表示列数
        .List = ary_list    '配列ary_listを値リストに設定
    End With
   
    '■変数の解放
    Set dic_d = Nothing
End Sub
■■■スポンサーリンク■■■

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

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

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

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

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

メイン画面が表示され、ユニークな値リストが表示されます。

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

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

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

セルデータを配列に格納

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

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

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

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

Dictionaryオブジェクトとは

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

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

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

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

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

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

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

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

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

リストボックスに重複しないユニークな値リスト(複数2列)を設定する(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 オブジェクトに関するインテリセンス機能(自動メンバー表示)が効かなくなります。

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

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

参照設定を利用する方法

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

【利用方法】

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

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

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

    リストボックスに重複しないユニークな値リスト(複数2列)を設定する(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 オブジェクトに関するインテリセンス機能(自動メンバー表示)を使うことができます。
    メンバーが表示されるので、入力ミスが減ります。リストボックスに重複しないユニークな値リスト(複数2列)を設定する(Excel VBA)

【デメリット】

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

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

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

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

リストボックスに重複しないユニークな値リスト(複数2列)を設定する(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(d_ct, 1), ary_d(d_ct, 2) 

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

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

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

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

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

リストボックスに重複しないユニークな値リスト(複数2列)を設定する(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(d_ct, 1)) =False Then
    dic_d.Add ary_d(d_ct, 1), ary_d(d_ct, 2) 
end if
■■■スポンサーリンク■■■

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

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

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

dic_d.Count

リストボックスに重複しないユニークな値リスト(複数2列)を設定する(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 1)

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

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

Keysメソッド

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

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

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

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

dic_d.Keys()(id)

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

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

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

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

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つ目のデータになります。

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

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

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

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

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

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

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

ListBox1.List = ary_list

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

リストボックスに重複しないユニークな値リスト(複数2列)を設定する(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)
既存のリストボックスの値リストの項目を1件 RemoveItemメソッドで削除する(Excel VBA)
リストボックス リストから初期値としてはじめに選択しておく値を設定する(Excel VBA)
リストボックスの値リストに設定された項目の件数を取得する(Excel VBA)
RowSourceプロパティを使って 複数列のセル範囲とリストボックスの値リストをリンク設定(Excel VBA)
リストボックスにListプロパティを使って配列に格納した複数列のセルデータを値リストとして設定(Excel VBA)
リストボックス 複数列の値リストの列幅を変えたり非表示にしたりする設定(Excel VBA)
リストボックスで選択しているデータがリストの上から何番目(インデックス値)かを取得(Excel VBA)
リストボックス 1列の値リストで選択しているデータをTextプロパティで取得(Excel VBA)
リストボックス 1列の値リストで選択しているデータをValueプロパティで取得(Excel VBA)
リストボックス 1列の値リストで選択しているデータをListプロパティで取得(Excel VBA)
リストボックス 複数列の値リストで選択しているデータを、列指定してTextプロパティで取得(Excel VBA)
リストボックス 複数列の値リストで選択しているデータを、列指定してValueプロパティで取得(Excel VBA)
リストボックス 複数列の値リストで選択しているデータを、列指定してListプロパティで取得(Excel VBA)
指定した行(インデックス値)のデータをリストボックスの値リストから選択する(Excel VBA)
リストボックスの値リストに列見出しを表示する設定/ColumnHeadsで見出しが設定できない理由(Excel VBA)
リストボックス値リストAddItemメソッド・RowSourceプロパティ・Listプロパティ違い Excel VBA
リストボックス TextプロパティとValueプロパティの違い(Excel VBA)
リストボックスに重複しないユニークな値リスト(1列)を設定する(Excel VBA)
リストボックスに重複しないユニークな値リスト(複数2列)を設定する(Excel VBA)

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