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

Excel VBA クラス UserFormで機能を操作する(Lv.16)

Excel VBA クラス UserFormで機能を操作する方法
画像はCanva「Text to Image」で生成しました

「Excel VBAのクラス」についての第16回目です。前回で「元の表データ」をインスタンスにしてコレクション化し、内容に手を加えた結果をワークシートに書き出せるようになりました。

クラスモジュールに設定した「機能」を標準モジュールから「操作」しています。でも、標準モジュールでは「操作」が完了した段階で、クラスモジュールの「機能」は解放されてしまいます。

解放前に「操作」を途中で変更したり追加したりすることはできません。そこで今回は「UserForm」を利用して、ここまで設定した「機能」の操作を管理できるようにしていきたいと思います。

くるみこ
くるみこ

覚えていますか? 13回目の「コレクションの要素取得方法」で ListBox から Key を選択する方法で UserForm を使いました。この UserForm を改造して、いろいろな機能を追加して操作できるようにしていきたいと思います。

はい、覚えています。確か ListBox に要素の Key リストを表示して利用したんでしたね。
それを作り替えていくというわけですね。今回もよろしくお願いしますm(_ _)m

スポンサーリンク

はじめに

標準モジュールから UserForm を起動して、クラスモジュールに設定した「機能」を操作できるようにしていきます。

そうすることで、UserForm が起動している間は、コレクションやインスタンスが解放されずに保持されたまま操作できるようになります。

UserForm が終了すると、呼び出し側の標準モジュールのコードが進行してコレクションやインスタンスが開放されるという設定です。

【この記事でわかること
Excel VBA UserFormでコレクションを操作できるようにする方法

使用する UserForm は 13回目で作った次のものを改造していくようにします。

13回目の記事で作成したUserForm

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

操作用の UserForm を設定

13回目の UserForm を次のように設定し直しました。

コレクション操作フォームの設定
  1. ListBox のサイズを「Key」だけでなく、全プロパティ値が表示できるように広げました。
  2. CommandButton を5個追加配置し、各キャプションを変更しました。
  3. 「検索値で絞り込む場合」Frame 枠を1つ配置しています。
  4. Frame 内に検索値設定に使用する TextBox を5個配置しました。
  5. Frame 内に説明などの文字列はラベルを配置して設定しています。

実際に表示させてみた動画をはじめに貼っておきます。こんな感じになります。

UserForm_Initialize()

UserForm_Initialize() は UserForm が Load される際、最初に実行されるプロシージャです。

Private Sub UserForm_Initialize()
  Dim elem As Object
  Dim Index As Long
  Dim r As Long
  Dim ListBox As MSForms.ListBox
  Set ListBox = UserForm1.ListBox1
  With ListBox
    .Clear  'Listを初期化
    .ColumnCount = 7  '列数と幅を設定
    .ColumnWidths = "30;30;20;30;20;20;20"
    'ListBoxにKeyリストを追加する
    For Index = 1 To table.mycol.Count
      r = Index - 1
      Set elem = table.mycol.item(Index)
      .AddItem elem.ID
      .List(r, 1) = elem.Name
      .List(r, 2) = elem.Age
      .List(r, 3) = elem.Pet.Name
      .List(r, 4) = elem.Pet.Age
      .List(r, 5) = elem.Pet.Types
      .List(r, 6) = elem.Pet.Gender
      r = r + 1
    Next Index
  End With
  SelectedKey = ""  'Public変数初期化
End Sub

13回では、List はキーの「ID」だけ表示していましたが、全部表示する設定に変更しています。

【コード補足】

  • 9~10行目、リストの列数と各列の幅を設定しています。
  • 15行目、ListBox.AddItem で「ID」を追加します。
  • 16~21行目で、各列に ListBox.List(行,列) で値を設定していきます。

各コマンドボタンにコードを設定

それでは、設置したコマンドボタンにコードを設定しましょう。標準モジュールにこれまで設定してきたプロシージャを呼び出すコードの設定がメインです。

各コマンドボタンの設定

UserFormを閉じる(CommandButton1)

13回目では「OK」だったボタンは UserForm を閉じるだけのコードに変更しています。

'UserFormを閉じる
Private Sub CommandButton1_Click()
  Unload Me 'UserFormを閉じる
End Sub

コレクションに要素を追加(CommandButton2)

14回目の読み込み済みのコレクションに要素を追加するで設定したコードを呼び出す設定です。

'コレクションに要素を追加
Private Sub CommandButton2_Click()
  Call ColAddItem(table)
  UserForm_Initialize
End Sub
'検索値設定をリセットする
Private Sub CommandButton6_Click()
  Dim Ctrl As Control
  'TextBoxの入力値を全部消す
  For Each Ctrl In Controls
    If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
  Next Ctrl
  '表示を初期値に更新する
  UserForm_Initialize
End Sub

追加後にリストを更新するためにイニシャライズさせています。

コレクションをセルに書き込む(CommandButton3)

15回目で設定した、コレクション全体を指定したセル範囲に書き出すコードを呼び出します。

'コレクションをセルに書き込む
Private Sub CommandButton3_Click()
  Call OutputRangeSelect(table)
End Sub

書き出す際に、列見出しを一緒に書き込むかどうかを選択できるように、元のコードに少し手を加えています。

ListBox選択要素を書き込む(CommandButton4)

こちらも前回(15回)設定の ListBox の選択したリストを書き出すコードを呼び出します。

'ListBox選択要素を書き込む
Private Sub CommandButton4_Click()
  With ListBox1
    If .ListIndex = -1 Then
      '未選択の場合は何もしない
      MsgBox "リストが選択されていません!"
    Else
      SelectedKey = .List(.ListIndex, 0)
      Call GetKeyByListBox(table.mycol)
    End If
  End With
End Sub

書き出す際に、列見出しを一緒に書き込むかどうかを選択できるようにするのと、貼り付け先のセルを選択できるように元コードに少し手を加えています。

検索値で絞込実行(CommandButton5)

この部分は、新たに設定した部分です。

'検索値で絞込実行
Private Sub CommandButton5_Click()
  Dim elem As Object
  Dim Index As Long
  Dim r As Long
  Dim ListBox As MSForms.ListBox
  Set ListBox = UserForm1.ListBox1
  With ListBox
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "30;30;20;30;20;20;20"
    'ListBoxにKeyリストを追加する
    For Index = 1 To table.mycol.Count
      Set elem = table.mycol.item(Index)
      'フィルタでHITしたItemだけ追加する
      If cFilter(elem) Then
        .AddItem elem.ID
        .List(r, 1) = elem.Name
        .List(r, 2) = elem.Age
        .List(r, 3) = elem.Pet.Name
        .List(r, 4) = elem.Pet.Age
        .List(r, 5) = elem.Pet.Types
        .List(r, 6) = elem.Pet.Gender
        r = r + 1 'Listの行変数に1+
      End If
    Next Index
  End With
  SelectedKey = ""  'Public変数初期化
End Sub

【コード補足】

  • 16行目の cFilter(elem) 関数で、TextBox に入力された値がコレクションにあるか検索しています。True が返ってきた要素(Item)だけ ListBox に書き出すコードになっています。
  • cFilter(elem) 関数のコードは次のとおり。分割してないのでちょっと長いです。
cFilter(elem) 関数でコレクション内を検索する
'検索値があったらTrueを返す
Function cFilter(ByVal elem As Object) As Boolean
  Dim sList As String
  Dim str As Variant
  Dim a As Long, b As Long, c As Long, d As Long
  
  With elem
    If TextBox2.Value <> "" Then a = TextBox2.Value
    If TextBox3.Value <> "" Then b = TextBox3.Value
    If TextBox4.Value <> "" Then c = TextBox4.Value
    If TextBox5.Value <> "" Then d = TextBox5.Value
    If TextBox1.Value <> "" Then
      str = Split(TextBox1.Value, ",")
      '要素内の値を","区切りで繋げる
      sList = .ID & "," & .Name & "," & .Age _
        & "," & .Pet.Name & "," & .Pet.Age _
        & "," & .Pet.Types & "," & .Pet.Gender
      '文字列検索
      Select Case UBound(str)
        Case 0
          '1文字列検索
          If sList Like "*" & str(0) & "*" Then
            cFilter = True: Exit Function
          End If
        Case 1
          '2文字列検索
          If sList Like "*" & str(0) & "*" And _
              sList Like "*" & str(1) & "*" Then
            cFilter = True: Exit Function
          End If
        Case 2
          '3文字列検索
          If sList Like "*" & str(0) & "*" And _
              sList Like "*" & str(1) & "*" And _
              sList Like "*" & str(2) & "*" Then
            cFilter = True: Exit Function
          End If
      End Select
    Else
      '飼い主とペットの年齢で検索
      Dim sCase As String
      If a = 0 Then sCase = "0" Else sCase = "1"
      If b = 0 Then sCase = sCase & ",0" Else sCase = sCase & ",1"
      If c = 0 Then sCase = sCase & ",0" Else sCase = sCase & ",1"
      If d = 0 Then sCase = sCase & ",0" Else sCase = sCase & ",1"
      Select Case sCase
        Case "0,0,0,0"
            cFilter = True: Exit Function
        Case "1,0,0,0"
          If .Age >= a Then _
            cFilter = True: Exit Function
        Case "1,1,0,0"
          If .Age >= a And .Age < b Then _
            cFilter = True: Exit Function
        Case "1,1,1,0"
          If .Age >= a And .Age < b And _
            .Pet.Age >= c Then _
            cFilter = True: Exit Function
        Case "1,1,1,1"
          If .Age >= a And .Age < b And _
            .Pet.Age >= c And .Pet.Age < d Then _
            cFilter = True: Exit Function
        Case "0,1,0,0"
          If .Age < b Then _
            cFilter = True: Exit Function
        Case "0,1,1,0"
          If .Age < b And _
            .Pet.Age >= c Then _
            cFilter = True: Exit Function
        Case "0,1,1,1"
          If .Age < b And _
            .Pet.Age >= c And .Pet.Age < d Then _
            cFilter = True: Exit Function
        Case "0,0,1,0"
          If .Pet.Age >= c Then _
            cFilter = True: Exit Function
        Case "0,0,1,1"
          If .Pet.Age >= c And .Pet.Age < d Then _
            cFilter = True: Exit Function
        Case "1,0,1,1"
          If .Age >= a And _
            .Pet.Age >= c And .Pet.Age < d Then _
            cFilter = True: Exit Function
        Case "0,0,0,1"
          If .Pet.Age < d Then _
            cFilter = True: Exit Function
        Case "1,0,0,1"
          If .Age >= a And _
            .Pet.Age < d Then _
            cFilter = True: Exit Function
        Case "1,1,0,1"
          If .Age >= a And .Age < b And _
            .Pet.Age < d Then _
            cFilter = True: Exit Function
        Case "1,0,1,0"
          If .Age >= a And _
            .Pet.Age >= c Then _
            cFilter = True: Exit Function
        Case "0,1,0,1"
          If .Age < b And _
            .Pet.Age < d Then _
            cFilter = True: Exit Function
      End Select
    End If
  End With
End Function

このコードでは「文字列」優先で文字列が無い場合「数値」を処理します。

【コード補足】

  • 2行目、この関数は Boolean 型です。正の場合 True を返します。
  • 8~11行目で、各TextBoxに値が記入されていた場合、変数に代入します。
  • 12行目は、文字列検索用の TextBox1 に入力されているかどうかで分岐する設定です。
  • 13~38行目までが文字列検索用のコード部分です。
  • 13行目、TextBox1 に入力されている設定(カンマ区切り)を変数 str に配列で渡しています。
  • 15行目、要素(Item)の各プロパティ値を全てカンマ区切りで繋げて変数 sList に代入します。
  • 19行目、Select Case UBound(str) で配列の要素数で分岐させる設定です。
  • 20~38行目、Case毎の処理です。設定された文字列は、部分一致で検索してHITした場合は関数に True を代入して抜ける設定です。
  • 39行目以降が数値検索の部分です。数値のプロパティは「年齢」の部分です。
  • 42~45行目で、各 TextBox 毎に設定がある場合は「1」ない場合は「0」をカンマ区切りで文字列に設定して sCase 変数に代入しています。例: “1,1,0,0” など
  • 46~103行目までが、sCase の値による分岐処理です。入力される設定のすべてのケースを設定しています。設定された数値にHITした場合に True を返します。

検索値設定をリセットする(CommandButton6)

こちらは、UserForm の検索設定用に値を入力できるテキストボックスを設定していますが、そのテキストをリセットするボタンです。

'検索値設定をリセットする
Private Sub CommandButton6_Click()
  Dim Ctrl As Control
  'TextBoxの入力値を全部消す
  For Each Ctrl In Controls
    If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
  Next Ctrl
  '表示を初期値に更新する
  UserForm_Initialize
End Sub

【コード補足】

  • 設置しているすべての TextBoxコントロール の値を空欄にするだけのコードです。
  • 最後に表示を初期値に更新するため UserForm_Initialize を実行するようにしています

標準モジュール側のコード

UserFormを起動する標準モジュールのコードは次のとおりです。

'標準モジュール
Sub rngCollectionTest()
  'インスタンス作成⇒コンストラクタ起動
  Set table = New clsCol  
  UserForm1.Show  'ユーザーフォームを表示  
  Set table = Nothing
End Sub

ユーザーフォームを表示した段階で、処理がユーザーフォームに移ります。

ユーザーフォームが閉じられると処理が戻ってきます。

最後に、Set table = Nothing でインスタンスを開放すると、Collection と保持していたインスタンスすべてを開放して終了します。

ワークシートにコマンドボタンを設置して起動

シートにコマンドボタンを設置して「rngCollectionTest」を呼び出すように設定します。

シートにコマンドボタンを設置

このボタンを押せばマクロが開始します。

まとめ(おわりに)

以上、Excel VBA クラスのコレクションを操作するための UserForm の作成でした。

くるみこ
くるみこ

クラスの16回目の内容はいかがでしたか。これで UserForm を起動させている間は、コレクションを保持したままいろいろな操作ができるようになりましたね。

UserForm に検索機能を付けたのですごく便利になりました。
検索の方法もいろいろ勉強できてよかったです。

くるみこ
くるみこ

今回、検索機能をテストするために表データを1000行に増やしています。

実は、処理速度なども確認したかったので30,000行に増やしてみたのですが、17,257行目の処理中に実行時エラー6「オーバーフローしました」で停止してしまいました。

17,256 × 7 = 120,792個の値までは処理できていたことになります。

コレクションの最大要素数について少し調べてみましたが見つかりませんでした。この部分は次回以降で詳しく調べてみたいと思っています。

まとめ

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

Excel VBA でコレクションを UserForm を使って操作する方法

【UserFormの基本設定】

  1. UserFormを配置する。
  2. 各種機能を開始するためのコマンドボタンを必要数配置。
  3. コマンドボタンのクリックイベントに機能を呼び出すコードを設定する。
  4. 標準モジュールからUserFormを起動するコードを設置する。
  5. UserFormを終了するコマンドボタンを設定する。
  6. 必要に応じて「ListBox」や検索用に「TextBox」などを追加設定する。

基本的にはこんな感じです。詳しくは今回のサンプルファイルで内容をご確認ください。

次回は、今回のテスト時に発生した、17,257行目の処理中に実行時エラー6「オーバーフローしました」で停止したことについて詳しく調べてみたいと思います。

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)

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

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

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

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

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