テキストボックスに入力した言葉を含む候補をコンボボックスに表示するのを一括で処理する方法。クラスモジュールって何だよ！の話。【VBA備忘録】

ズブの素人によるExcel VBA備忘録の第9回です。
例によって個人的な備忘録ですのでそのつもりでお読み下さいね。

どこか間違ってたら教えてくれると嬉しいです。

Contents

同じような処理がたくさんあるとメンテナンスがだるい。
何はなくともクラスモジュール。とりあえず書いてみ！
クラスモジュールを使ってフォーム内の操作を一括処理する話まとめ。

同じような処理がたくさんあるとメンテナンスがだるい。

けっこう前の記事で、「フォームに入力した単語を含む検索結果をコンボボックスに表示する」という処理のやり方を記事にしました。

Excel VBAで検索結果をコンボボックスに複数列反映させる方法。動的配列とReDimで見栄えもスッキリ【VBA備忘録】以前に作ったExcelのフォームを見ていて、どうもコンボボックスがブサイクだったのそれを直してみました。今回はそれについての備忘録で...

これをすることで私の仕事が微妙に捗ることになったのでこれはこれでいいんですが、見積などを作る際にはこの手の検索ボックスがたくさん必要になってきます。
2個や3個であればコピペして作ればいいんですが、これが10個とかになってくるとコピーやメンテナンスの際に非常にめんどくさいことこの上ありません。

コードを書く
コピーする
テキストボックスの名前を変える
コンボボックスの名前を変える

テキストボックスやコンボボックスが増えた分だけ上の作業が増えることになり、その分凡ミスも増えてきます。
楽をしたかったりミスを減らしたりするためにプログラムを組んでいるのにミスが増えてしまっては本末転倒もいいところです。

というわけで今回は、1つのコードで複数の似たような処理を一括でやってくれる処理について書いていきます。
内容としては、「テキストボックスに入力した単語を含む検索結果をコンボボックスに候補表示するコンビをたくさん作る」ということになります。

まず前提としては10組のコンビを作るとして、

テキストボックスの名前は「TBox1～10」
コンボボックスの名前は「CBox1～10」
TBox1に入力したらCBox1に候補表示、TBox2に入力したら～（以下略

という感じにします。

要は、

For i = 1 to 10
Sub Controls(“TBox” & i) _Change
Controls(“CBox” & i) = ～
End Sub
Next i

みたいなことができないかなー、ってことです。

三本松

実際にやってみて敢えなく弾かれたのは内緒です。

毒猫

とりあえずやってはみるんだ…。

三本松

みんな1回はやってるんじゃないかと勝手に思ってます。

何はなくともクラスモジュール。とりあえず書いてみ！

まあ上のようなコードがまかり通るわけもないので仕方なくやり方を調べてみると、とりあえず「クラスモジュール」なるものを使って動かすことで可能になるということがわかりました。

先に断っておきますと、私は未だにクラスとかモジュールとかの概念がイマイチよくわかっていません。
それでも何とか動かすことには成功しましたので、用途に応じてうまいこと応用していただければと思っていますよ。

毒猫

その段階で記事にしちゃうオマエが怖いよ。

三本松

いや基本自分の備忘録だしさ…。

まずはクラスモジュールの追加。

さらっとクラスモジュールとか言いましたが、これは手動で新規に呼び出して追加しなくてはいけません。
「挿入」タブをクリックすると「クラスモジュール」と出てきますのでそれをクリックすれば追加されます。思いの外簡単でした。

追加すると「Class1」という名前の書く場所が出てきますので、クラスモジュール用のコードはここに描いて進めていくことになります。
ここでは名前を変えずにClass1のまま進めていきますよ。

クラスモジュール内に書くべきこと。わかってなくても動けばいいのさ！

クラスモジュールが出てきたら、とりあえず下記のコードを書いてみて下さい。

Option Explicit

Private WithEvents TB As MSForms.TextBox   'テキストボックス用を格納する変数
Private CB As MSForms.ComboBox  'コンボボックスを格納する変数
'-----------------------------------------------------------------
Public Sub NC(ByVal t As MSForms.TextBox, c As MSForms.ComboBox)

    Set TB = t
    Set CB = c
    
End Sub
'------------------------------------------------------------------
Private Sub TB_Change()
    ’ここにやりたい処理を書き込む。
End Sub

例によってわけがわからないと思いますので1つずつ(私のわかる範囲で)説明してみます。

三本松

本人がよくわかっていないので本質の説明は期待しないで下さいね！

まずは一番上の「Option Explicit」ですが、これは「変数の宣言を省略することはまかりならん」というコードです。
宣言を忘れるとそれだけでエラーが出て教えてくれます。
今回は特に関係なさそうなんですが、調べたらクラスモジュールを使っている人はなぜかみんなこれを書いていましたのでそれに倣ってみました。
もしかしたらこれがないとうまく動かないのかもしれません。

ちなみにこの処理は、「ツール」→「オプション」で出てくる設定画面で「変数の宣言を強制する」にチェックを入れると自動で書いてくれるようになります。

次の行についてですが、まず「Private Withevents」を使うと、後に指定したオブジェクトのイベントを拾ってくれるようになります。
その次に来ている「TB」を、起点となるオブジェクトを格納する変数として設定しました。
次の「CB」はコンボボックスを格納する変数ですが、こちらは表示させるだけなのでWithEventsは使わずに宣言しています。
もし「コンボボックスの値をもとにラベルを表示したい」など、イベントの起点にしたい場合はこちらもWithEventsを使ってやればOKです。

三本松

わかりやすくテキストボックスとコンボボックスの略にしてみましたよ。

毒猫

自分がわかってないのにわかりやすくとか片腹痛いわ。

「As」の後は対象のオブジェクトの種類です。
テキストボックスは「MSForms.TextBox」、コンボボックスは「MSForms.ComboBox」を使います。

ここまでで、「TBに格納したテキストボックスが祭りの合図だ！ついでにコンボボックスやってるCBってヤツもいるからもよろしく！」というフリになるわけですね(多分)。

三本松

ちゃんと理解したら追記する予定です。

毒猫

予定は未定ってな！

三本松

・・・。

次の「NC」は私が勝手に決めた変数(？メソッド？)です(とりあえずNewClassの略でNCにしてみました)。
()内は宣言したNCが持っている内容(プロパティ？)の枠を設定しています。
テキストボックスの「t」とコンボボックスの「c」をもつ変数で、このNCを他で使用するときにはtとcに当たる引数が必要になります。

「Byval」は値渡しを定義するワードで、途中で変数の中身を入れ替えても元の値をそのまま使うために必要なんだそうです。

毒猫

理解が追いつかずにフワッフワしてるのが伝わってくるな。

三本松

わかってなくても書けば動くってのが大事なんだよ！

ここで大事なのはNCを宣言するのに「Public」を使っているところです。
ここをPublicにすることで、NCのみクラスモジュール外からのアクセスが可能になります。
NC以外についてはフォーム側から直接アクセスできないので、クラス側で作った変数の中でフォーム側で使用するのはNCだけということになります。
他のを書くと多分怒られます。
つまり、フォーム側でNCの中身さえきっちり書いておけば後はクラス側で処理してくれるということですね。

そして最後にここで宣言したtとcを上で宣言したTBとCBに格納してしまいます。
大丈夫ですよ？私も理解していません。

ちなみにここでは一括操作したいオブジェクトが2種類なのでNC()の中の引数は2個になっています。
もし「対応したラベルに表示したい」など他にも動かしたいオブジェクトがある場合は、上での宣言とNC()内の引数の数をその分だけ増やしていけばOKです。

三本松

理解してなくてもこれで応用できますね！

毒猫

書いてるやつが理解してない解説記事とは一体…。

そしてその下にはテキストボックスの値が変化したときのコードを記述していくことになります。
これでクラスモジュール側の下準備ができました。

フォーム側に書くべきこと。とりあえずクラスモジュールを呼び出すコードが要るらしい。

次は実際に結果の出るフォーム側の記述を書いていきます。
これを書くところはとりあえずフォーム内の要素をダブルクリックすれば出て来ます。

こちらに書くコードはこんな感じです。

Option Explicit

Private CL(1 To 10) As New Class1
'---------------------------------------------------------
Private Sub Userform_Initialize()
    Dim i As Integer
    For i = 1 To 10
        CL(i).NC Controls("TBox" & i), Controls("CBox" & i)
    Next i
End Sub

「CL」は書く側で決める変数(？)です(とりあえずクラスの略でCLにしてみました)。
「Class1」はさっき作ったクラスモジュールの名前で、デフォルトでClass1となっているので変えずにそのまま使いますよ。

そしてClassの前の「New」がクラスモジュールを使える状態にしてくれる魔法のようです。
CLの後の()内は、テキストボックスとコンボボックスの数だけグループが必要になるのでそれを決めている数字です。
要は「1～10まで10個の班を持ったクラスですよ」という宣言をしています(多分)。

その下はユーザーフォームを初期化した際に実行されるコードです。
「UserForm_Initialize()」はこのまま書いて下さい。理由はわかりませんがここにユーザーフォムのオブジェクト名(ここではUserform1)を書いても動いてくれません。

このコードの中でクラスの中のグループごとの設定を決めていきます。
前提で述べたとおりテキストボックスは「TBox1～10」、コンボボックスは「CBox1～10」と決めていますので、For文を使ってそれぞれコンビを設定していきます。

クラスの方で決めた「NC」はテキストボックス型の引数とコンボボックス型の引数の2つを必要とするので、NCのあとに同じくクラスの方で決めた引数に対応するオブジェクトを当てはめていきます。
ここではループで処理するために「Controls」セレクションというウルテクを使ってガサッと設定を済ませています。

これでフォーム側でクラスモジュールを呼び出す準備が整いました。

後はクラスモジュールにやりたい処理を書いていくだけ。

準備ができたらいよいよクラスモジュールにやりたい処理を書いていきます。

ここでは「テキストボックスの値が変更されたら、商品名にその値を含む商品をコンボボックスのリストに表示させる」というのが目的ですのでそれをクラスモジュールの方に記述していけばOKということになります。

記述の前に、今回の前提としては、

↓こんなフォームで

↓こんなデータを持つ「商品データ」シートから検索し

↓コンボボックスに「商品コード」、「商品名」、「内容量」を表示させたい

といった感じです。

先にコードを書いておくとこんなかんじです。

Private Sub TB_Change()
    Dim i As Long
    Dim item As String 'テキストボックスに入力した検索ワードを格納する変数
    Dim idata() As Variant 'コンボボックスにリストアップしたい商品を一時的に格納する配列
    Dim R As Long '商品データの行数を格納する変数
    Dim h As Long '配列のインデックス数を格納する変数
    Dim DWS As Worksheet '商品データのワークシートを格納する変数
    
    Set DWS = Worksheets("商品データ") '商品データのワークシートを変数にセット(記述簡易化のため)
    R = DWS.Cells(Rows.Count, 1).End(xlUp).Row + 1 '商品データの行数+1
    CB.Clear '念のためコンボボックスをクリア
    
    h = 0 '配列のスタートは0から
    item = Trim(TB.Value) 'テキストボックス入力したワードを変数に格納。Trimは変なスペースを入れちゃう奴対策
    For i = 2 To R - 1 '1行目は見出しなので2からスタート
        If DWS.Cells(i, 2) Like "*" & item & "*" = True Then '含む検索はLike*～*
            ReDim Preserve idata(2, h) '配列の枠を指定。先に格納した要素を消さないためPreserveを使用
            idata(0, h) = DWS.Cells(i, 1) '商品コードを1列目の最後に格納
            idata(1, h) = DWS.Cells(i, 2) '商品名を2列目の最後に格納
            idata(2, h) = DWS.Cells(i, 3) '内容量を3列目の最後に格納
            h = h + 1 '格納したら配列の枠を1個広げてあげる
        End If
    Next i
    
    If h > 0 Then '検索にヒットする商品がなかった場合は何もしない。エラー対策
        CB.Column() = idata() '配列の中身をコンボボックスに収納
    End If
    
    Set DWS = Nothing '念の為参照を破棄
    Set TB = Nothing
    Set CB = Nothing
End Sub

このコードをクラスモジュールに1つ記述するだけで、フォーム上に10個あるテキストボックスのどれに入力してもそれと対になるコンボボックスに検索結果が表示されます。
正直今まで10回同じコードを書いていたのがアホらしくなりますね。

例えば「コンボボックスに表示される商品情報を1つ追加したい」という要望があった場合、今までは追加のコードを書いて10回分コピーペーストしてからオブジェクト名をちまちま変えていたわけです。

三本松

途中で何番かわからなくなって変な動きをしたりとか散々でした。

しかしクラスモジュールを使えば、改変するコードはクラスモジュール内に記述した1つだけで済んでしまいます。これは楽！

ただし処理に絡んでくるオブジェクト自体が増えた場合は、

クラスモジュール上部での宣言
NC()内の引数

を追加してあげるのをお忘れなく。

ちなみに商品を確定した際コンボボックスをトリガーとした処理を行いたい場合は、クラス上部の「CB」を宣言している行を

Private WithEvents CB As MSForms.ComboBox

に変えてあげて、次の行にラベルを宣言します。

↓ラベル用の変数はとりあえずLBにしています。

Private LB As MSForms.Label

その上でNC()内の引数にラベル分を追加します。

↓クラスモジュール側(ここではラベル用の変数は「ｌ(小文字のL)」にしています。)

Public Sub NC(ByVal t As MSForms.TextBox, c As MSForms.ComboBox, l As MSForms.Label)
    Set TB = t
    Set CB = c
    Set LB = l
End Sub

↓フォーム側(ラベルの名前は「Lbl1～10」としています。)

Private Sub Userform_Initialize()
    Dim i As Integer
    For i = 1 To 10
        CL(i).NC Controls("TBox" & i), Controls("CBox" & i), Controls("Lbl" & i)
    Next i
End Sub

あとはクラスモジュールにコンボボックスが確定したときにやりたい処理のコードを記述してやれば完成です(Set 〇〇 = Nothingを書く場合は処理の最後に移動しておきましょう)。

Private Sub CB_Change()
    LB.Caption = ～
End Sub

クラスモジュールを使ってフォーム内の操作を一括処理する話まとめ。

フォーム内に同じ操作が必要になるオブジェクトがたくさんあるとだるい。
クラスモジュールを使えば1つのコードで複数のオブジェクトに同じ操作をさせることができる。
コピペの必要がないので機能の追加やメンテナンスが楽になってミスも減る。
クラスモジュールの追加はツールバーから。
一括操作したいオブジェクトの名前は番号以外同じ名前にしておく。
一括操作の肝は「Private Withevents」。
クラスモジュール内ではまず対にするオブジェクトの宣言をする。
「Public Sub」で一括操作用のメソッド(？)を作り、フォーム側のコードからの入口にする。
その下に動かす内容のコードを書く。
フォーム側ではクラスを呼び出す処理を書く。
「Private 〇〇(1 To ××) As New Class1」でクラス内を分割してやることで個々の処理を行える。
クラス内の割当は「Private Sub UserForm_Initialize()」で書き始める。
↑ユーザーフォームの名前が何であれなぜか「UserForm」と書く。
番号だけ違うオブジェクトをループで当てはめていくには「Controls」を使う。
よくわかってなくてもちゃんと書けば動く！

個人的に一番大事なのは一番下のやつです。
何度もいいますが私はクラスモジュールの本質は全く理解できていません。
そりゃあ理解していないより理解している方がいいのは確かですが、仕事を楽にするためにプログラミングを使っている以上優先すべきは仕事を楽にすることです。
もしあなたがプログラミングの専門家になりたいのであれば本質は最優先かもしれません。
逆にとにかく目の前の仕事を効率化したいのであれば本質の理解に割くリソースが膨大だと思ったらとりあえず上っ面だけ借りて仕事を進めるのも大事なことです。
私も「多分この先数をこなしていけばいずれなんとなくわかってくるだろう」と気楽に構えています。

正直私がこの作業のコードを完成させるためにいろんなHPを見ましたが、私のやりたかった作業をどストライクで書いているページは見つけられませんでした。
他の作業をクラスモジュールで行う解説ページをいくつも見てそれを自分のやりたい作業にすり合わせて何とかたどり着いた答えです。
クラスモジュールの基本解説をしてくれているページも見ましたが、それを自分のやりたい作業にどうつながっているのかは多分膨大な時間を使ってしまうと思いスルーした次第でございますよ。

全く何もわかっていない人間が書いている記事ではありますが、この作業をしたい人にはストライクな記事になること、そうでない人には何らかのヒントになるパーツとして機能すれば幸いです。

毒猫

言い訳長いなオイ。

三本松

ちゃんと断っておかないと詳しい人とかにボコボコにされるかもしれないからさ…。

わかってきたら追記していきますので今はこれで許してください…。

以上です！