本ページには広告が含まれています。

Excel VBA クラス コレクションの要素取得方法(Lv.13)

Excel VBA クラスのコレクションから要素を取得する方法
画像はCanva「Text to Image」で生成しました

「Excel VBAのクラス」について第13回目です。前回「コンストラクタ」を利用してインスタンス化と同時に基本データを自動取得してコレクション化できるようになりました。今回は、コレクション化できたインスタンスの要素を取り出す方法についてです。

くるみこ
くるみこ

前回までに使ったコードの最後や途中で、確認用にMsgBoxに結果を出力していました。その他にもいろいろな方法があるので、いくつかの方法を見ていきましょう(^^)

わかりました。データを取り出す方法を知らなければ役に立ちませんものね。

今回もよろしくお願いしますm(_ _)m

スポンサーリンク

はじめに

コレクションオブジェクトについては第10回目で勉強しました。こちらをご覧ください。

ちなみに「Collectionオブジェクト」のメンバー(プロパティとメソッド)は次のとおりでした。

メンバー説明
Count プロパティ要素の個数(Long)を返します。値の取得のみ可能です。
Add メソッド要素を追加します。
Item メソッド要素を返(取得)します。
Romove メソッド要素を削除します。

「要素を取り出す」ということは、Itemメソッドを使うということになります。

それでは、その具体的な方法についてみていきましょう。

【この記事でわかること
Excel VBA のクラスでコレクション化されたインスタンスから要素を取り出す方法

 1 VBA の InputBox を使用する方法
 2 Application.InputBox を使用する方法
 3 UserForm の ListBox を使用する方法

使用する表データは前回のままです。

エクセルシートの表データ

この記事で使用したサンプルファイルを登録しています。リンク先からご利用ください。

Itemメソッドについて簡単に復習

コレクションから要素を取り出すには Itemメソッドを使います。

Itemメソッドは「コレクションの既定メソッド」なので「.Item」の部分は省略することが出来ます。

コレクション名に「( )」を付けてインデックス番号またはキー値を次のように指定して呼び出します。

コレクション名(1)   'インデックス番号1の要素を取得
コレクション名("A")  'キー値"A"の要素を取得

ループ処理で取り出す方法

標準モジュールのコレクション化が完了した後のコード部分に直接記述するか、下のコード例のようにサブルーチンを呼び出すようにして、実際の処理コードはサブルーチン側に記述します。

'標準モジュール
Sub rngCollectionTest()
  Dim table As clsCol
  'インスタンス作成⇒コンストラクタ起動
  Set table = New clsCol

  'この下の部分にサブルーチンを呼び出すコードを入れる
 'For Eachでコレクション内の要素毎にMsgBoxを表示する処理へ
  Call MsgBox_Element(table.mycol)

End Sub

コレクション化され集合となっている「要素=インスタンス」を取り出すにはループ処理が有効です。

For Each ~ Nextステートメントを使う【推奨】

ループ処理には、次の例のように For Each ~ Nextステートメントを使うのが良いでしょう。

'For Eachでコレクション内の要素毎にMsgBoxを表示する
Sub MsgBox_Element(ByVal Col As Collection)
  Dim c As Variant
  Dim i As Long
  For Each c In Col
    i = i + 1
    With c
      MsgBox "OWNER:" & .Name & "(" & .Age & "歳)" _
        & vbCrLf & "ペット名:" & .Pet.Name & _
        "(" & .Pet.Age & "歳)" & .Pet.Types & _
        "/" & .Pet.Gender, , _
        "Index=" & i & " , Key=" & .ID
    End With
  Next
End Sub

このコードでは MsgBox を使っていますが、イミディエイトウィンドウに出力させたい場合は Debug.Print に差し替えるだけでOKです。

For Next を使う場合

コレクション数をカウントして、インデックス番号ごとに処理する方法ももちろん使えます。

ただし、要素数が多い場合などは処理が増えるにしたがって処理速度がだんだん遅くなるので注意しましょう。これは、コレクションの特徴がチェーンのように次々につながるメモリ構造になっている影響です。全要素に対する処理には、高速な前段の For Each を使って処理しましょう。

ループ処理部分のコード例は次のとおりです。

  '要素数をカウントしてMsgBoxを表示
  Dim i As Long
  For i = 1 To table.mycol.Count  'カウントした要素数をループ
    With table.mycol(i)
      MsgBox "OWNER:" & .Name & "(" & .Age & "歳)" _
        & vbCrLf & "ペット名:" & .Pet.Name & _
        "(" & .Pet.Age & "歳)" & .Pet.Types & _
        "/" & .Pet.Gender, , _
        "Index=" & i & " , Key=" & .ID
    End With
  Next

個別の要素を取り出す方法3例

個別に特定の要素だけ取り出す方法についても考えてみましょう。

MsgBox やイミディエイトウィンドウに出力するのではなく、セルに書き出す方法で検討します。

InputBoxで指定する

1つ目は、VBAの InputBox に取得したい要素の Key を入力させる方法です。

'KeyをInputBoxで指定し各プロパティをセルに書き込む
Sub GetKeyByInputBox(ByVal Col As Collection)
  Dim Elem As Object
  Dim Key As Long 'String
    
redo:
  Key = InputBox("取得するKeyのIndexを入力", "要素のKeyを指定")
  If Col.Count >= Key And Key > 0 Then
    Set Elem = Col.Item(Key)
    Cells(10, 1).Value = Elem.ID
    Cells(10, 2) = Elem.Name
    Cells(10, 3) = Elem.Age
    Cells(10, 4) = Elem.Pet.Name
    Cells(10, 5) = Elem.Pet.Age
    Cells(10, 6) = Elem.Pet.Types
    Cells(10, 7) = Elem.Pet.Gender
  Else
    MsgBox "存在しない Key が指定されました!": GoTo redo
  End If
End Sub
VBAのInputBoxの表示

このコードでは、書き込み先セルの場所はあらかじめコード内で指定しています。

各プロパティ毎、セルを横方向に書き込む設定です。

Key は Index番号を入力する設定にしていますが、Key値を入力させることへも変更できます。

その場合は、4行目の変数の型指定を String に変更し、8行目の If 分岐の条件設定を変更する必要があります。

Application.InputBoxを使う方法

2つ目の例は、Application.InputBoxType:=8 を使って、Keyが入力されているセルを選択する方法です。

'Application.InputBoxで指定したKeyのセル横方向に書き込む
Sub GetKeyByAppInputBox(ByVal Col As Collection)
  Dim Elem As Object
  Dim rng As Range
  Dim Key As Variant
  Dim r As Long, c As Long, i As Long, x As Long
    
redo:
  On Error Resume Next
  Set rng = Application.InputBox( _
    Prompt:="指定するKeyのセルを選択してください", _
    Title:="セル(Key)選択", Type:=8)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub
  r = rng.Row     'セルの行番号
  c = rng.Column  'セルの列番号
  Key = rng.Value 'Key変数にセル値を代入
  x = Col.Count   '要素数カウント
  'Keyの存在確認をしてから処理する
  For i = 1 To x  'Keyが要素に存在するかチェック
    If Col.Item(i).ID = Key Then Exit For
  Next i
  If x + 1 >= i Then 'ループ内で抜けているかで分岐
    Set Elem = Col.Item(Key)
    Cells(r, c + 1) = Elem.Name
    Cells(r, c + 2) = Elem.Age
    Cells(r, c + 3) = Elem.Pet.Name
    Cells(r, c + 4) = Elem.Pet.Age
    Cells(r, c + 5) = Elem.Pet.Types
    Cells(r, c + 6) = Elem.Pet.Gender
  Else
    MsgBox "存在しない Key が指定されました!": GoTo redo
  End If
End Sub
Application.InputBoxのダイアログ

このコードでは、単独セルの選択だけを想定してしています。コードを追加すれば、複数セルの選択に対応させるようにすることも可能です。

参考までに以前書いた InputBox の使い方についての記事がこちらです。

ListBoxからKeyを選択する方法

3つ目は、ListBoxを利用する方法です。ListBoxを利用するには UserForm を使う必要があります。

UserFormを用意します

画像のように、プロジェクトのユーザーフォーム(UserForm1)を追加します。追加したUserForm1にListBoxとCommandButtonを配置します。各サイズを調整し、UserForm1とCommandButtonのプロパティのCaptionだけ変更しています。

UserForm設置と設定

Userform側のコード

UserForm側に必要なコードは、コマンドボタンのクリックイベントだけです。
次のように設定します。

Private Sub CommandButton1_Click()
  With ListBox1
    If .ListIndex = -1 Then
      '未選択の場合は何もしない
    Else
      SelectedKey = .List(.ListIndex, 0)
    End If
  End With
  Unload Me 'UserFormを閉じる
End Sub

6行目で変数「SelectedKey」に選択したリストの Key値を渡しています。
(変数 SelectedKey は 標準モジュール側で Public で宣言します)

リストを選択していなくても「OK」ボタンを押すと終了する設定にしています。

ListBoxからKeyを選択するコード例

各要素のKeyをListBoxに表示させて、選択されたKeyに対して処理を行います。

'UserFormのリストからKeyを選択
Sub GetKeyByListBox(ByVal Col As Collection)
  Dim Elem As Object
  Dim Index As Long
  Dim ListBox As MSForms.ListBox
  Set ListBox = UserForm1.ListBox1
  'ListBoxにKeyリストを追加する
  For Index = 1 To Col.Count
    Set Elem = Col.Item(Index)
    ListBox.AddItem Elem.ID
  Next Index
  SelectedKey = ""  'Public変数初期化
  UserForm1.Show  'ユーザーフォームを表示
  If SelectedKey <> "" Then
    Set Elem = Col.Item(SelectedKey)
    Cells(10, 1).Value = Elem.ID
    Cells(10, 2) = Elem.Name
    Cells(10, 3) = Elem.Age
    Cells(10, 4) = Elem.Pet.Name
    Cells(10, 5) = Elem.Pet.Age
    Cells(10, 6) = Elem.Pet.Types
    Cells(10, 7) = Elem.Pet.Gender
  Else
    MsgBox "Key が選択されませんでした!"
  End If
End Sub

このコードは複数選択(MultiSelect)は想定していません。(対応できるように変更は可能です)

書き込み先のセルも、10行目にあらかじめ設定しています。(書き込み先セルを選択するように設定することも可能ですが)

【コードの補足】

・5行目の「Dim ListBox As MSForms.ListBox」は、標準モジュール側でListBoxにリストを設定するために変数を用意しています。

・6行目「Set ListBox = UserForm1.ListBox1」でユーザーフォームに設置したListBox1を紐づけています。

・8~11行目が、コレクション内の全アイテムの「Key」=ID をListBoxに「AddItem」メソッドで追加している処理です。

・12行目で、Publicで宣言しているグローバル変数「SelectedKey」を初期化しています。

・13行目でUsetFormを表示させます。

UserFormのListBoxに表示されたKey

画像のようにKeyリストが表示されます。
ここで取得したいKeyを選択して「OK」ボタンをクリックします。

・14行目からはセルに書き込む処理ですが「SelectedKey」が空の場合は処理せずにメッセージを出して終了すします。メッセージは24行目の「MsgBox “Key が選択されませんでした!”」です。

まとめ(おわりに)

以上、Excel VBA クラスでコレクション化させたインスタンスの要素を取得する方法について3つ紹介しました。

くるみこ
くるみこ

クラスの13回目はいかがでしたか。

3つの方法についてみてきましたがいかがでしたか。基本的な部分はわかったのではないかと思います。これを参考にしていろいろ変更を加えてみてください。

UserFormを使うのはひさしぶりだったけど、簡単なものだったので大丈夫でした。

複数選択するように変更を加えることが出来るか試してみようと思います(^^)
次回もよろしくお願いしますm(_ _)m

まとめ

最後に、今回勉強した内容を整理しておきましょう。

Excel VBA クラスでコレクション化させたインスタンスの要素を取得する方法

・ループ処理は、For Each ~ Nextステートメントを使った方が良いようです。

・個別の要素を取り出すには、Key値 または KeyのIndex が必要です。

・VBAの「InputBox」や「Application.InputoBox」でKeyを指定したり、UserFormの「ListBox」でKeyを一覧表示させて選択させるなどの方法が使えます。

次回は、コレクションに要素を追加したり、削除したりする方法について勉強ます。よろしければ引き続き次もお付き合いください。

Excel VBA クラスについての記事一覧

Excel VBA クラスの作成・使い方について(Lv.1)
Excel VBA クラスのプロパティ設定について(Lv.2)
Excel VBA クラスにメソッドを設定する方法(Lv.3)
Excel VBA クラスをインスタンス化する方法(Lv.4)
Excel VBA クラス Propertyプロシージャ(Lv.5)
Excel VBA クラス Property Getプロシージャ(Lv.6)
Excel VBA クラス Property Letプロシージャ(Lv.7)
Excel VBA クラス Property Setプロシージャ(Lv.8)
Excel VBA シートの表データからクラスを作成 (Lv.9)
Excel VBA クラス インスタンスをコレクション化(Lv.10)
Excel VBA クラス コンストラクタとデストラクタ(Lv.11)
Excel VBA クラス コンストラクタを利用する方法(Lv.12)
Excel VBA クラス コレクションの要素取得方法(Lv.13)
Excel VBA クラス コレクション要素の追加と削除(Lv.14)
Excel VBA クラス コレクションの内容を書き出す(Lv.15)
Excel VBA クラス UserFormで機能を操作する(Lv.16)
Excel VBA クラス コレクションの最大要素数について(Lv.17)

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

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

記事のサンプルファイルをダウンロードできます

この記事で使用したサンプルファイルを登録しました。リンク先からご利用ください。

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください