Excel VBAで検索結果をコンボボックスに複数列反映させる方法。動的配列とReDimで見栄えもスッキリ【VBA備忘録】

以前に作ったExcelのフォームを見ていて、どうもコンボボックスがブサイクだったのそれを直してみました。
今回はそれについての備忘録です。

基本的に自分向けの備忘録ではありますが、もしかしたら誰かの役に立つかもしれないので一応公開しています。

Excelマクロのフォームで検索結果をコンボボックスに表示させ、それをセルに出力。

元々やりたかった作業は、

• フォームを起動。
• フォームのテキストボックスに検索語句を入力して選択肢を絞る。
• 検索結果をコンボボックスに表示させる。
• その中から選択した1つをセルに出力する。

というものです。

例えばたくさんある商品の中から検索ワードで絞って、その結果をコンボボックスに表示したいということですね。
そしてコンボボックスには、

• 商品コード
• 商品名
• 規格

を表示させたい、という前提で話を進めていきますよ。

とりあえずExcelのシートには、商品一覧があらかじめ入力されているものとしています。

「商品一覧」シートと出力のためのフォームの内容。

仮にこんな感じのシートがあるとします。シート名は「商品一覧」にしておきます。

フォームを使ってこの中から抽出したデータを別のシートに出力します。
仮に「出力」と名付けた別シートののA1セルに抽出したアイテムの商品コードを出力します。

商品名とか規格が欲しければ出力時に商品名、規格ををB1とC1に同時に出力させるか、B1、C1セルにVLOOKUP関数とか入れておけば問題ないでしょう。

利用するフォームはこんな感じです。名前は「form1」とでもしておきます。

テキストボックスの名前は「tbox1」、コンボボックスの名前は「cbox1」とでもしておきます。
ここに出た結果を、下の出力ボタン(名前は「button1」)を押すことでシートに吐き出させます。

まずは出力ボタンを押した時の挙動を設定。

出力のボタンを押したらコンボボックス内の商品コードが出力シートのA1セルに出力されるコードは以下です。

Private Sub button1_Click()
        Sheets("出力").Range("A1") = form1.cbox1
        Unload Me
End Sub

 

データが数字型の文字列で、数値として出力したい場合は「CLng関数」などで変換してあげるといいでしょう。
今回はもともと数値なのでそのままでいきます。

出力が終わったらフォームを閉じるように「Unload」を入れておきます。

他は何も選択していないときに警告が出るような仕組みもいいと思います。

Private Sub button1_Click()
    If form1.cbox1 = "" Then
        MsgBox "商品を指定してください"
    Else
        Sheets("出力").Range("A1") = form1.cbox1
        Unload Me
    End If
End Sub

 

スポンサーリンク

テキストボックスに入れた文字列を含む商品をコンボボックスに表示させる。

検索ワードをテキストボックスに打ち込んで、それを含む商品を洗い出して候補をコンボボックスに表示させるプログラムを組みます。
今回はテキストボックス中の値が変化したときに動くマクロを書いていきますが、間に検索ボタンを噛ませるなどやり方は色々あります。

検索条件は「ライクこめこめ」

まず、検索する場合は「Like * 語句 *」を使います。
「*」がワイルドカードのような働きをするのはExcelと一緒なんですね。

Dim item As String  '検索語句

item = Trim(form1.tbox1)
'テキストボックスの文字列から余分なスペースを削除

Sheets("商品一覧").Range("B" & i) Like "*" & item & "*" = True

 

これが成り立つ場合にその商品をコンボボックスに並べるようにすればいいので、これをIf文で囲ってやればいいということですね。
今回の場合は変数「item」を文字列の変数として使用しています。

コンボボックスに複数列表示させる設定。

一方、コンボボックスに検索結果を複数列表示させるのはプロパティをいじってやればOKです。

フォームデザインのコンボボックスをクリックすると左側にプロパティが出ます。

その中の「ColumnCount」の数値を増やせば表示列数が増えます。
今回は表示要素が3つあればいいので3にします。

その上の「BoundColumn」は、コンボボックス内のどの要素を出力するかの数値です。
今回は左端の商品コードを出力するので1にしていますが、商品名を出力したい場合は今回の場合では2を入れておけばOKです。

ちなみに各列の幅を調整するのは少し下の「ColumnWidths」で可能です。
各列の幅をポイント数で指定しますが各列の数値をセミコロン「；」で区切って使用します。
まあこれはコンボボックスの表示を確認しながらやればいいでしょう。

余談ですが、検索ワードを日本語で行う場合はテキストボックスの方のプロパティで「IMEMode」を4にしておけば入力欄が初めからひらがな入力になります。

指定した行数よりデータ数が多いとエラーになる。

コンボボックスへデータを入れ込むには配列を使うのが一般的ですが、私がやらかした失敗を少し書いておきます。

まず、配列の箱の大きさを決めるのですが、データ数が最初に決めた配列の行数より多くなってしまうとエラーになってしまいます。

下のは私が最初に作ったものと同様のマクロです。

Private Sub tbox1_Change()
    Dim i As Long  '行数
    Dim item As String  '検索語句
    Dim idata(2, 5) As Variant '配列の箱の定義 3列6行
    Dim h As Long  '配列内の行番号
    Dim lastrow As Long　'商品一覧の最終行取得に使用

    '商品一覧の最終行
    lastrow = Sheets("商品一覧").Cells(Rows.Count, 1).End(xlUp).Row　

　　'テキストボックスの文字列から余分なスペースを削除
    item = Trim(form1.tbox1)  

　　'とりあえずコンボボックス内をクリア
    form1.cbox1.Clear  

    '配列は0からスタートします。
    h = 0
    
    For i = 2 To lastrow　'2行目から最終行まで                          
        If Sheets("商品一覧").Range("B" & i) Like "*" & item & "*" = True Then
            idata(0, h) = Sheets("商品一覧").Range("A" & i)　'1列目に商品コードをセット
            idata(1, h) = Sheets("商品一覧").Range("B" & i)　'2列目に商品名をセット
            idata(2, h) = Sheets("商品一覧").Range("C" & i)　'3列目に規格をセット
            h = h + 1  '次の格納は次の行に
        End If                
    Next i

    '配列に格納されたデータをコンボボックスへ
    form1.cbox1.Column() = idata()　
     
End Sub

 

配列は3列6行と先に決めています。
列数や行数は勝手に増やしてはくれないため、仮に検索結果が7つ以上であった場合、配列の行数より検索結果が多くなりエラーが発生してしまいます。

絞りが甘いとそれだけでエラーになってしまうのはあまり具合がよくありません。

スポンサーリンク

指定する行数が多すぎるとエラーは出ないが非常に不細工。

そこで配列の箱を大きくしてみます。

    Dim idata(2,100) As Variant　'配列の箱の定義 3列101行

行数を多くすれば検索結果がそれより多くなる可能性は下がります。
これでエラーで止まることはなくなりましたが、新たな問題が発生します。

上のように、検索結果が少ない場合でもコンボボックスの表示が101個分の枠をとってしまい、見た目が不細工な上に検索結果が多いのか少ないのかも視覚的にわかりにくくなってしまいます。
検索結果がどのくらいの数なのか目星をつけられないので非常に使いにくいです。

配列の定義を先に決めずその都度増やしていく方法(動的配列)

そこで調べたところ、実は配列は先に大きさを決めなくても良いということが判明しました。「動的配列」とか言うそうです。

    Dim idata() As Variant　'配列の行数、列数を決めない

書き方は空の括弧でOKです。

しかし流石に空のままでは使えないので箱の大きさを改めて定義する必要があり、それに使用するのが「ReDim」なる便利ワードです。
これで配列に要素が追加されるたびに行数を1つ増やしていくなど柔軟に対応することが可能になるわけですね。

逆に最初の定義で箱の大きさを決めてしまうとReDimは使えなくなりますので、最初の定義のときは必ず空括弧「()」にしておきましょう。

そしてもう1つ大事なことがあり、ReDimだけだと定義のときに配列のデータを消してしまいます。
ですので、配列の内容を記憶したままにするためにReDimの後に「Preserve」を入れてやります。

If Sheets("商品一覧").Range("B" & i) Like "*" & item & "*" = True Then
     ReDim Preserve idata(2, h)
     idata(0, h) = Sheets("商品一覧").Range("A" & i)
     idata(1, h) = Sheets("商品一覧").Range("B" & i)
     idata(2, h) = Sheets("商品一覧").Range("C" & i)
     h = h + 1           
End If

 

こんな感じです。

そうするとコンボボックスの行数は常にデータの数と一致しますので、溢れてエラーになったり箱が大きすぎて使い勝手が悪くなったりせずに済みます。

そんなこんなで現段階でのマクロはこの様になっています。

Private Sub tbox1_Change()
    Dim i As Long  '行数
    Dim item As String  '検索語句
    Dim idata() As Variant '配列の箱の定義 枠は未定
    Dim h As Long  '配列内の行番号
    Dim lastrow As Long  '最終行数の取得に使用
    
    lastrow = Sheets("商品一覧").Cells(Rows.Count, 1).End(xlUp).Row
    '商品一覧の表の最終行
    
    item = Trim(form1.tbox1)
     'テキストボックスの文字列から余分なスペースを削除
    
    form1.cbox1.Clear
    'とりあえずコンボボックス内をクリア
    
    h = 0
    '商品名に検索語句を含むとき、商品コードを1列目、商品名を2列目、規格を3列目に格納
    '※配列は0からスタートします。
    
    For i = 2 To lastrow  '表の2行目から最終行まで
        If Sheets("商品一覧").Range("B" & i) Like "*" & item & "*" = True Then
            ReDim Preserve idata(2, h)  '配列の再定義 3列h行
            idata(0, h) = Sheets("商品一覧").Range("A" & i)
            idata(1, h) = Sheets("商品一覧").Range("B" & i)
            idata(2, h) = Sheets("商品一覧").Range("C" & i)
            h = h + 1  '次の格納は次の行に
        End If
    Next i
    
    form1.cbox1.Column() = idata()
    '配列内のデータをコンボボックスに代入    
End Sub

 

検索結果がないときの挙動を追加。

上のマクロでも使えないことはないのですがここでもう1つ問題点が出てきます。

普通に検索結果が表の中にある場合はこれでいいのですが、語句検索にヒットしなかった場合にエラーが出てしまいます。

変換ミスなどでも検索結果がゼロというのはあり得るので、いちいちこれが出てこられると鬱陶しいことこの上ありません。

そこで私が採ったのはコンボボックスへの表示をIf文の中に入れ込んで検索結果がない時は無視してしまうというやり方です。

If h > 0 Then '配列内にデータがある場合
    form1.cbox1.Column() = idata()
    '配列内のデータをコンボボックスに代入
End If

 

配列の行数を指定する変数は0からスタートして検索結果にヒットした商品がある場合は1行足されますのでヒットすれば変数は必ず1以上になります。
その場合だけコンボボックスへの表示を行うようにすれば検索結果がない場合にはコンボボックスは空欄となり、0ヒットだと気づくという算段です。

エラー回避はいろいろやり方があるはずなので、これにこだわらずお好きなやり方でやってみていただければいいかと思います。

スポンサーリンク

Excel VBAのコンボボックスに検索結果を表示させる方法まとめ。

というわけで最終的に出来上がったマクロがこちら。

まずはフォームを呼び出すマクロ。

Private Sub button1_Click()
    If form1.cbox1 = "" Then
        MsgBox "商品を指定してください"
    Else
        Sheets("出力").Range("A1") = form1.cbox1
        Unload Me
    End If
End Sub

 

そしてテキストボックス内の語句を含む検索結果をコンボボックスに並べるマクロがこちら。

Private Sub tbox1_Change()
    Dim i As Long  '行数
    Dim item As String  '検索語句
    Dim idata() As Variant '配列の箱の定義 枠は未定
    Dim h As Long  '配列内の行番号
    Dim lastrow As Long  '最終行数の取得に使用
    
    lastrow = Sheets("商品一覧").Cells(Rows.Count, 1).End(xlUp).Row
    '商品一覧の表の最終行
    
    item = Trim(form1.tbox1)
     'テキストボックスの文字列から余分なスペースを削除
    
    form1.cbox1.Clear
    'とりあえずコンボボックス内をクリア
    
    h = 0
    '商品名に検索語句を含むとき、商品コードを1列目、商品名を2列目、規格を3列目に格納
    '※配列は0からスタートします。
    
    For i = 2 To lastrow  '表の2行目から最終行まで
        If Sheets("商品一覧").Range("B" & i) Like "*" & item & "*" = True Then
            ReDim Preserve idata(2, h)  '配列の再定義 3列h行
            idata(0, h) = Sheets("商品一覧").Range("A" & i)
            idata(1, h) = Sheets("商品一覧").Range("B" & i)
            idata(2, h) = Sheets("商品一覧").Range("C" & i)
            h = h + 1  '次の格納は次の行に
        End If
    Next i
    
    If h > 0 Then '配列内にデータがある場合
        form1.cbox1.Column() = idata()
        '配列内のデータをコンボボックスに代入
    End If
End Sub

 

このマクロを組む時の要点は、

• コンボボックスの形などはプロパティで決める。
• 「テキストボックスの内容が変わったとき」マクロを組む。
• 検索ワードを含む商品を拾う時は「Like “*” & 文字列変数 & “*” = True」。
• コンボボックスに表示させるには配列を使用。
• 配列はなぜか1ではなく0からスタートする。
• 配列は先に行数や列数を決めなくてもいい。
• 空括弧「()」で動的配列を使う。
• 途中で「ReDim Preserve」を使って再定義、データが増えたら再定義を繰り返すと見栄えも良くなりエラーも防げる。
• 検索結果がない時のエラー回避を忘れずに。

こんな感じです。

私は最初、配列の箱の大きさを再定義できることを知らずに行数を3桁にして常にビローンとなったコンボボックスを長いこと使ってきました。
動的配列というのを知っていると、検索しなれていない人が候補を絞りきれずに溢れてエラーを起こすことや、コンボボックスがビローンとなって不細工になることを防ぐことができますので重宝しそうですね。

以上です！