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

【本日のミッション】

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

ミッションの概要

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

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

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

重複データを多数含むリストを、Dictionary オブジェクトを用いてユニークなリストにし、リストボックスに設定します。

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

作業手順

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

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

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

2.配列「ary_d」を、Dictionaryオブジェクト「dic_d」に格納します。
Dictionaryオブジェクトの「Key」は重複できない仕様のため、ユニークなリストになります。今回は値(Items)は使用しないので空欄にしています。

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

3.Dictionaryオブジェクト「dic_d」の「Keys」を リストボックスの値リストに設定します。

リストボックスに重複しないユニークな値リスト(1列)を設定する(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用カウンター
    
    '■セルデータを配列ary_dに取り込み
    ary_d = Worksheets("サザエさん").Range("A2:A24")
    
    '■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), ""       'キー:名前、値:空欄
        End If
    Next d_ct
    
    '■リストボックスにdic_dのKeysを設定
    ListBox1.List = dic_d.Keys
   
    '■変数の解放
    Set dic_d = Nothing
End Sub
■■■スポンサーリンク■■■

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

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

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

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

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

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

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

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

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

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

セルデータを配列に格納

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

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

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

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

Dictionaryオブジェクトとは

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

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

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

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

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

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

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

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

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

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

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

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

メソッド

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 オブジェクトを使う準備

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 オブジェクトに関するインテリセンス機能(自動メンバー表示)が効かなくなります。

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

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

参照設定を利用する方法

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

【利用方法】

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

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

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

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

【デメリット】

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

    リストボックスに重複しないユニークな値リスト(1列)を設定する(Excel VBA) 
■■■スポンサーリンク■■■

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

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

リストボックスに重複しないユニークな値リスト(1列)を設定する(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の名前キーとして登録し、は使用しないので空欄としています。

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

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

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

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

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

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

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

Dictionary オブジェクトをリストボックスの値リストに設定する

Keysメソッドでキー(key)を一気に配列にする

Keysメソッドは、Dictionaryオブジェクトのキー(key)全てを配列にして返します。

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

参考:Dictionary オブジェクト(連想配列)に登録されたキー(key)を一気に取得する(Excel VBA)

Listプロパティで値リストに設定

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

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

今回の配列は、上記で説明したKeysメソッドで配列にしたデータになります。

ListBox1.List = dic_d.Keys

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

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

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