エクセルVBAで献立自動化、栄養士さんお助けツール、リストビューコントロールの使い方

Excel VBA

シチュエーション

■小規模老人保健施設の給食部門で献立を作成するとき使用する料理のレシピが必要になります。そのレシピを作成するには膨大な量の食品リストから食材を選択しますが、食品リスト(約2500)をある程度絞り込んで表示した中から選択させるという機能をリストビューを使てやってみたいと思います。

下のカードからレシピシステムを詳しく解説していますのでご覧ください

エクセルVBAで献立自動化、栄養士さんお助けツール、レシピの作成
■ レシピ作成システムとCSVファイル レシピ作成用フォーム 事業所給食サービスでまず必要になるのが献立です。 その献立の元になるのが料理のレシピになります。 ここでは下図の様なフォームを利用してレシピを作成しています。 ...

■リストビューの準備

■CSVファイルの準備(文部科学省の日本食品栄養成分表)

下のカードからCSVファイル、二次元配列の使い方を詳しく解説していますのでご利用下さい。

エクセルVBAで献立自動化、栄養士さんお助けツール、csvファイルと二次元配列
CSVファイルを取り込んで二次元配列を通してセル範囲に展開する ■事業所管理の自動化でデータ操作の元になるCSVファイルを取扱うことは多くあります。 今回はCSVファイルをシートのセル範囲に取り込む処理を考えてみます。 ■前提条件...

リストビューの準備

■VBAフォームのツールボックスのコントロールには初期状態ではリストビューは表示されてないので、ツールボックスにリストビューを追加します。

リストビューを使えるようにする

■まず、フォームを追加します。フォームの追加については下のカードに詳しく解説しています

小規模老人保健施設の給食業務向けExcelVBA練習帳、給食帳票管理にVBAを活用、UserFormからセルを操作する1、フォームを作って表示する
UserFormからセルを操作する1 今回からVBAの初心者用解説を始めます。 第一回目はフォームを作って様々なコントロールを配置する要領を練習します。 UserForm作成 ■Excelの無地のシートを開く(青枠部分) ...

 

 

■新しく追加した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に格納した一行分の値をセル範囲に渡しています

■セル範囲の指定については下のカードからも参照できます

エクセルVBAで献立自動化、栄養士さんお助けツール、csvファイルと二次元配列
CSVファイルを取り込んで二次元配列を通してセル範囲に展開する ■事業所管理の自動化でデータ操作の元になるCSVファイルを取扱うことは多くあります。 今回はCSVファイルをシートのセル範囲に取り込む処理を考えてみます。 ■前提条件...

まとめ

ここまで解説してきたリストビューは使い慣れると非常に便利で使いやすいコントロールです。データを取り出す処理や多くの項目を簡単な処理でセットしたり、一覧データをマウスのローラーでスクロールできたりもするので、とても使いやすいですね。

リストボックスでも同様のことはできますが、やはりリストビューのほうが使いやすいので多用しています。

表のデータを扱うときは欠かせないコントロールになっています。

ダウンロード

■リストビューの動作をまとめたファイルを下のカードからダウンロードできます

 

 

コメント

タイトルとURLをコピーしました