シチュエーション
■小規模老人保健施設の給食部門で献立を作成するとき使用する料理のレシピが必要になります。そのレシピを作成するには膨大な量の食品リストから食材を選択しますが、食品リスト(約2500)をある程度絞り込んで表示した中から選択させるという機能をリストビューを使てやってみたいと思います。
下のカードからレシピシステムを詳しく解説していますのでご覧ください
■リストビューの準備
■CSVファイルの準備(文部科学省の日本食品栄養成分表)
下のカードからCSVファイル、二次元配列の使い方を詳しく解説していますのでご利用下さい。
リストビューの準備
■VBAフォームのツールボックスのコントロールには初期状態ではリストビューは表示されてないので、ツールボックスにリストビューを追加します。
リストビューを使えるようにする
■まず、フォームを追加します。フォームの追加については下のカードに詳しく解説しています
■新しく追加したUserFormをマウスでクリックすると、ツールボックスが表示される(外側の赤枠部分)
■更に、コントロールが並んでいるところの内側の赤枠部分の辺りを右クリックするとメニューが出てくる。
■その他のコントロール(A)…をクリックする。
■下図でコントロールの追加ウインドウが開くのでMicrosoft ListView Control,Version6.0を見つけたらその左にあるチェック用の枡をクリックして×印をつけたら、OKボタンを押します。
■下図のようにコントロールが追加されるので、これをクリックしてフォーム上で適当な位置にリストビューを展開する。(マウスを適当な位置でクリックしてそのまま斜め下に引きずる)
リストビューにCSVファイルの食品リストを表示する
■テキストボックスを追加配置
■CSVファイルをリスト表示
■テキストボックスに入力した文字列でCSVファイルをあいまい検索した結果の食品リストをリストビューに一覧表示する。
■下図では、シートに付けたコマンドボタンをクリックして、表示したフォームの検索用Textboxに文字列「だいこ」と入力した段階でリストビューに「だいこ」が含まれる食品名の栄養データを表示している様子が分かります。
■選択行をシートへ転記
■リストビューの一覧データをどれかクリックしたときその行のデータを取り出してSheet1に転記しています。
全部のコード
フォームが開く前のリストビュー初期設定の処理
Private Sub UserForm_Initialize() 'リストビューの初期設定 With ListView1 .View = lvwReport '一覧表示 .FullRowSelect = True '選択を行全体に変更 .AllowColumnReorder = True '列幅の変更を許可 .Gridlines = True 'グリッド線の追加 .CheckBoxes = True 'チェックボックス許可 '-----列名セット---------------------------------------'csvの項目列の配列順位 '.ColumnHeaders.Add , "ID", "レシピ番号", 30 'Array(1,3,4,6,7,9,10,11,13,18,24,25,28,58,60) '.ColumnHeaders.Add , "hinngunn", "食品群", 40 .ColumnHeaders.Add , "syokubann", "食品コード", 45 '1 '.ColumnHeaders.Add , "sakuinn", "索引番号", .ColumnHeaders.Add , "syokuhinnmei", "食品名", 200 '3 .ColumnHeaders.Add , "haikiritu", "廃棄率(%)", 30 '4 .ColumnHeaders.Add , "kcal", "エネルギー", 50 '6 .ColumnHeaders.Add , "suibunn", "水分", 30 '7 .ColumnHeaders.Add , "tannpaku", "たんぱく質", 30 '9 .ColumnHeaders.Add , "sisitu", "脂質", 30 '10 .ColumnHeaders.Add , "koresute", "コレステロール", 30 '11 .ColumnHeaders.Add , "kabutu", "炭水化物", 30 '13 .ColumnHeaders.Add , "seni", "食物繊維", 30 '18 .ColumnHeaders.Add , "kariumu", "カリウム", 30 '24 .ColumnHeaders.Add , "karusiumu", "カルシウム", 30 '25 .ColumnHeaders.Add , "tetu", "鉄分", 30 '28 .ColumnHeaders.Add , "bitaC", "ビタミンC", 30 '58 .ColumnHeaders.Add , "ennbunn", "塩分", 30 '60 End With End Sub
①の部分がリストビューの外見の設定です
②の部分はリストビュー上段の項目部分の設定です
④は項目表示用の名称、③は名前を管理するためのID(適当な文字列でOK)、⑤項目幅の調整の設定です
検索用テキストボックスの処理
Private Sub TextBox1_Change() If TextBox1.Value = "" Then MsgBox "■ 空白ですよ?" ListView1.ListItems.Clear Exit Sub End If Call dataInput(TextBox1.Value) End Sub
■テキストボックスに入力された文字列を引数にして下図に示したSubプロシージャdataInputを呼び出しています
リストビューに表示するための処理
Private Sub dataInput(str As String) 'リストビューに引き数であいまい検索後のcsvを入れる ' Dim arryData As Variant Dim filePath As String Dim listArry As Variant 'csvの二次元配列に格納 filePath = ActiveWorkbook.Path & "\recipdata\seibuncsv.csv" arryData = det_in(filePath) 'あいまい検索の結果行を配列で受け取る listArry = outPutArr(arryData, str) '検索結果該当データが無いときはここで処理終了 If IsEmpty(listArry) Then Exit Sub 'リストビューに検索結果を表示 With ListView1 .ListItems.Clear '前回データを消去 For r = 1 To UBound(listArry, 1) '行の繰り返し .ListItems.Add Text:=listArry(r, 0) For i = 1 To UBound(listArry, 2) '列の繰り返し .ListItems(r).SubItems(i) = listArry(r, i) Next i Next r End With End Sub
処理の流れ
■2500件余りが収録された食品データCSVファイルを扱いやすくするため、二次元配列に格納する。 arryData = det_in(filePath)
■テキストボックスに入力した文字列で二次元配列の中をあいまい検索した結果を更に二次元配列に格納する。 listArry = outPutArr(arryData, str)
■検索結果の配列データをリストビューに一覧表示する。
.ListItems(r).SubItems(i) = listArry(r, i)
食品SCVファイルを二次元配列にして返す処理
Function det_in(fail As String) As Variant 'csvファイル食材のリストを二次元配列に格納して返す Dim fso As Object 'FileSystemObject Dim ffile As Object 'File Dim myData As Variant Dim myDataLine As Variant Dim Ccnt As Long, Rcnt As Long Dim i As Long, j As Long 'オブジェクト作成 Set fso = CreateObject("Scripting.FileSystemObject") Set ffile = fso.OpenTextFile(fail, 1) ' 配列の上限設定 myDataLine = Split(ffile.readline, ",") Ccnt = UBound(myDataLine) '列数 ffile.readall Rcnt = ffile.Line '行数 ffile.Close ReDim myData(Rcnt, Ccnt) ' 2次元配列に格納 Set ffile = fso.OpenTextFile(fail, 1) j = 0 Do Until ffile.AtEndOfStream myDataLine = Split(ffile.readline, ",") For i = 0 To Ccnt myData(j, i) = myDataLine(i) Next j = j + 1 Loop ffile.Close det_in = myData End Function
■CSVファイルを二次元配列に格納して返す定番ファンクション関数でこの関数は各種の処理で多用される関数です。「標準モジュール」に置いて、他のシステムを開発するときもコピーして使い回して活用しています。
あいまい検索の結果を二次元配列にして返す処理
Public Function outPutArr(arrayData As Variant, str As String) As Variant 'あいまい検索した結果の行データを返す Dim result As Variant 'リザルト Dim r As Long Dim i As Integer, c As Integer Dim dataJun As Variant 'csvの項目配列順(この項目だけ必要 15個) dataJun = Array(1, 3, 4, 6, 7, 9, 10, 11, 13, 18, 24, 25, 28, 58, 60) '検索の件数を数える(配列の要素数を知るため) c = 0 For r = 0 To UBound(arrayData, 1) If arrayData(r, 3) Like "*" & str & "*" Then c = c + 1 End If Next r '検索結果が無かったら終わる(empty) If c < 1 Then Exit Function '配列の要素数を決定 ReDim result(c - 1, 14) c = 0 For r = 0 To UBound(arrayData, 1) - 1 'あいまい検索 If arrayData(r, 3) Like "*" & str & "*" Then For i = 0 To 14 'リストビューの項目分だけ配列に残す result(c, i) = arrayData(r, dataJun(i)) Next i c = c + 1 End If Next r '返却 outPutArr = result End Function
■この検索処理はここでのリストビューにおいて一番メインになる処理です。食品データは2500件程度あるので当然絞り込まないと見ずらいリストになりますが、効率的に必要なデータを取るためにあいまい検索を使っています。テキストボックスに食品データの「食品名」に含まれる文字列の一部を書き込んだときの処理として、絶対検索より幅広いデータを集めることができると思います。
リストビューの一覧をクリックしたときの処理
リストを表示して、あるデータを選択したときそのデータを取り出す処理ですが、ここでは取り出したデータをシートに書き出すことにしました。いちいちシートに書き出さなくても、更に配列にデータを格納することもできます。アイデア次第で様々なことができると思います。
Private Sub ListView1_Click() Dim result(14) As Variant Dim koumoku(14) As String Dim i As Integer Dim LastRow As Long With ListView1.SelectedItem MsgBox .Text & Chr(13) & _ .SubItems(1) & Chr(13) & _ Chr(13) & _ "okですね!" '----------------------------------- result(0) = .Text koumoku(0) = ListView1.ColumnHeaders(1) For i = 1 To 14 result(i) = .SubItems(i) koumoku(i) = ListView1.ColumnHeaders(i + 1) Next i End With 'シートに転記(項目が無いときは項目も転記) With Sheet1 LastRow = .Cells(Rows.Count, "A").End(xlUp).Row If LastRow = 1 Then .Range(.Cells(LastRow, 1), .Cells(LastRow, 15)).Value = koumoku .Range(.Cells(LastRow + 1, 1), .Cells(LastRow + 1, 15)).Value = result Else .Range(.Cells(LastRow + 1, 1), .Cells(LastRow + 1, 15)).Value = result End If End With End Sub
‘———————————–
■この部分でセル範囲に渡す値の配列を作ります
result(0) = ListView1.SelectedItem.Text
■リストビューにあるデータ行の一番左側の先頭列の値はSelectedItem.Textで受け取りますがそれ以降の列の値はSelectedItem.SubItems(i)で受け取るので注意が必要です。
koumoku(0) = ListView1.ColumnHeaders(1) ■項目名の取得
For i = 1 To 14
result(i) = ListView1.SelectedItem.SubItems(i) ■先頭列以降の列の値を取得
koumoku(i) = ListView1.ColumnHeaders(i + 1) ■項目名の取得
Next i
■上で配列resultに格納した一行分の値をセル範囲に渡しています
■セル範囲の指定については下のカードからも参照できます
まとめ
ここまで解説してきたリストビューは使い慣れると非常に便利で使いやすいコントロールです。データを取り出す処理や多くの項目を簡単な処理でセットしたり、一覧データをマウスのローラーでスクロールできたりもするので、とても使いやすいですね。
リストボックスでも同様のことはできますが、やはりリストビューのほうが使いやすいので多用しています。
表のデータを扱うときは欠かせないコントロールになっています。
ダウンロード
■リストビューの動作をまとめたファイルを下のカードからダウンロードできます
book4 listview
コメント