「BakuJAN活用例」の解･体･親･書

親愛なる貴方のために・・・え？誰？

７.リストの項目を設定する

　あるグループに分けられた値が固定のもので、ユーザーにその中から１つを選択してもらうのに、便利なコントロールがあります。[ ListBoxコントロール ]と[ ComboBoxコントロール ]です。どちらも似たようなものですが、[ ComboBoxコントロール ]のほうは、プロパティの設定によって、機能も外観も変えられるリストコントロールです。

　簡単なリストを作るものに、[ AddItemメソッド ]と[ Array関数 ]があります。プロシージャの中で、リストの項目値を配列として与えます。

• 　★　 [ Array関数 ]で、リスト項目を設定する
• 　★　 リストを複数列で使い、表示する列数を設定する
• 　★　 リストで表示する列の幅を設定する
  Private Sub Btn_Mnuコード_Click() For i = 1 To 20 Cmb_QR_SIZ.AddItem i Next Cmb_QR_SIZ.Value = 1 Cmb_QR_ECL.List = Array("L", "M", "Q", "H") Cmb_QR_ECL.Value = "M" Lst_BC画像.ColumnCount = 3 Lst_BC画像.ColumnWidths = "120;140;72" MultiPage1.Value = 4 End Sub
• コンボボックス[ Cmb_QR_SIZ ]は、QRコードのモジュールサイズを決定するリストで、1〜20までの選択肢があります。[ For〜Nextステートメント ]で、カウンター用変数[ i ]の値を利用し、[ AddItemメソッド ]でリスト項目を順に設定しています。値が、単純に増減していくような数値を扱う場合、[ For〜Nextステートメント ]は便利です。「5、10、15･･･」などと、増減する幅が固定されているときは、[ For i = 1 To 20 Step 5 ]と[ Step ]値を使います。
  　
  　コンボボックス[ Cmb_QR_ECL ]は、QRコードのエラー訂正レベルを決定するリストで、「L/M/Q/H」の4つの選択肢があります。[ Array関数 ]で設定しています。コンボボックスコントロールのリスト項目は、非アクティブは畳まれています（デフォルト時）。リスト項目の特定の値を選択の規定値とするため、[ Valueプロパティ ]を使って規定の値が表示されている状態にしています。
  　
  　[ ColumnCountプロパティ ]を使うと、リスト内に「列」を作ることができます。また、複数の項目列を表示するには[ ColumnWidthsプロパティ ]で、表示する幅を指定します。項目によって表示される値が尻切れにならないよう、固定長のデータを左側に表示します。[ ListBoxコントロール ]と[ ComboBoxコントロール ]が持つリストは、データを格納できるという意味では、あたかもワークシートのように使う事ができます。リストにも、行と列のアドレスがあり、多次元配列で、リストの項目を設定することができます。

　ここでは、別々のリストの項目設定に、[ AddItemメソッド ]と[ Array関数 ]をそれぞれ使いました。リストの項目を自由に扱う方法を覚えると、格段にフォームが扱いやすくなります。

　次の例では、[ AddItemメソッド ]で追加したリスト項目を[ Listプロパティ ]によって多次元配列として項目を追加しています。

• 　★　 オプションボタンの値に併せ、多次元配列のリスト項目を追加する
• 　★　 外部ファイル（画像）のファイル名・作成日時・バイト数を取得する
  Private Sub 種別_Check() If Opt_C39.Value = True Then i = 1 If Opt_JAN.Value = True Then i = 2 If Opt_QRC.Value = True Then i = 3 Lst_BC画像.Clear: Img_BC画像.Picture = LoadPicture("") Select Case i Case 1: C_dat = Dir(ThisWorkbook.Path & "\c*.gif", 0) Case 2: C_dat = Dir(ThisWorkbook.Path & "\j*.gif", 0) Case 3: C_dat = Dir(ThisWorkbook.Path & "\*.bmp", 0) End Select i = 0 Do While C_dat <> "" Lst_BC画像.AddItem C_dat With CreateObject("Scripting.FileSystemObject").GetFile(C_dat) Lst_BC画像.List(i, 1) = .DateCreated Lst_BC画像.List(i, 2) = Format(.Size, "#,##0") & "byte" End With i = i + 1: C_dat = Dir() Loop End Sub
• 　指定したパスにあるファイルのうち、オプションボタンコントロールの値と[ Select Caseステートメント ]によって、ユーザーの選択に応じたファイル分類（ファイル名の最初の文字と拡張子による判定）をピックアップします。
   
  　[ Do While〜Loopステートメント ]で、フォルダ内の該当ファイルを[ Dir関数 ]とワイルドカード[ *（アスタリスク） ]を使ってピックアップし、[ Listプロパティ ]のそれぞれの次元のリスト項目へ追加しています。[ DateCreatedプロパティ ]はファイルの作成日時を取得し[ Sizeプロパティ ]はファイルサイズを取得します。

　ユーザーフォームに配置するコントロールは、[ Visual Basic Editor ]のツールボックスに15種類あります。それらの中でも、リストを扱うコントロールほど、多岐に渡る設定手段が用意されているものはありません。[ AddItemメソッド ]と[ Array関数 ]以外にも、[ RowSourceプロパティ ]を使って、ワークシートのセル範囲をリスト項目に直接指定することもできます。

　外部ファイルの扱いと、リストコントロールの扱いを憶えれば、ワークシートの呪縛といわれる「･･･65536個のデータ･･･」から、逃れられるでしょう。

• 　★　 セル範囲をコンボボックスのリスト項目に設定する
• 　★　 列（セル）の中で、データの入力されている空白以外のセルを数える
  Private Sub Btn_Mnu顧客_Click() Cmb_C顧客名.RowSource = "CMS_名簿!b2:b" & _ WorksheetFunction.CountA(Sheets("CMS_名簿").Range("b:b")) End Sub
• [ RowSourceプロパティ ]は、一見すると定まったセル範囲しか扱えないように考えがちです。でも変動する顧客数（セルの行数）は、その都度数えてリストのソース範囲とするコードを書いておけば、動的なワークシートの値と連携するフォームも安心です。
  　
  　ここでは、リスト項目として利用されるデータが、セルの１行目から途切れなく続いているとして、[ CountA関数 ]を使ってデータの数を数え、「データの数 = 行数」として扱っています。

• 　★　 ワークシートを数えて、リスト項目に追加する
• 　★　 名前付け規則に沿ったシート名だけをリスト項目へ追加する
  Private Sub Btn_Mnuレジ_Click() Cmb倉庫.Clear For i = 1 To Sheets.Count If Left(Sheets(i).Name, 4) = "POS_" Then Cmb倉庫.AddItem Sheets(i).Name End If Next Sheets(Cmb倉庫.List(0, 0)).Activate MultiPage1.Value = 2 Cmb倉庫.ListIndex = 0 Lst領収.ColumnCount = 3 Lst領収.ColumnWidths = "90;204;72" Lst印刷ラベル.ColumnCount = 2 Lst印刷ラベル.ColumnWidths = "0;" End Sub
• 　リストは、空の状態で項目を設定しないと、それまでの項目に追加されてしまうので、最初にリストを空にします。次に、ブックに含まれる全てのワークシート数を求め、その中から、特定の名前（「POS_」で始まるシート名）を持ったシートだけをコンボボックス[ Cmb倉庫 ]のリストに追加します。
  　
  　リストボックス[ Lst_領収 ]には、リスト１行に「商品コード/商品名/金額」の３つを表示させるため、[ ColumnCountプロパティ ]で項目を3列、[ ColumnWidthsプロパティ ]で各列の表示幅をそれぞれ設定しています。

　外部ファイルに値を保存し、必要になった際にファイルから値を取り出して計算することもできます。次の例では、社員の出社時間・退社時間を記録するログファイル（外部テキストファイル）から、選択した従業員の社員コードをもとにして、その月の出勤日数・出勤時間と退社時間から、出勤日ごとの就労時間の計算を行い、リストボックスに表示するプロシージャです。

図：氏名を選択すると、リストボックスに勤務状態を表示する

• 　★　 カンマ区切りの外部テキストファイルを開く
• 　★　 外部テキストファイルから、リストボックスの選択値と同じものをリストアップする
• 　★　 ２つのタイムスタンプから、経過時間（勤務時間）を計算する
  Private Sub Cmb_CmsB_Change() If Cmb_CmsB.ListIndex <> -1 Then i = Cmb_CmsB.ListIndex + 2 If Cmb_CmsB.Text = Empty Then Edt_CmsA.Value = Empty Edt_CmsC.Value = Empty Edt_CmsS.Value = Empty Edt_CmsT.Value = Empty Lbl_CmsCnt.Caption = Empty Img_Cms001.Picture = LoadPicture("") Exit Sub End If With ThisWorkbook.Sheets("CMS_名簿") Edt_CmsA.Value = .Cells(i, 1) Edt_CmsC.Value = .Cells(i, 3) Edt_CmsS.Value = .Cells(i, 19) Edt_CmsT.Value = .Cells(i, 20) Img_Cms001.Picture = LoadPicture(Dir(ThisWorkbook.Path & "\" _ & Edt_CmsA.Value & ".jpg", 0)) End With Lst_CmsCnt.Clear Lbl_CmsCnt.BackColor = RGB(255, 0, 0) Lbl_CmsCnt.Caption = Empty Lst_CmsCnt.ColumnCount = 3 Lst_CmsCnt.ColumnWidths = "122;74;" With CreateObject("Scripting.FileSystemObject") _ .OpenTextFile(ThisWorkbook.Path & "\cmslogin.log", 1, True, -2) Do While .AtEndOfLine <> True L_dat = Split(.ReadLine, ",") If L_dat(1) = """" & "IN:" & Edt_CmsA & """" And _ Mid(L_dat(0), 2, 4) = Edt_CYY.Value And _ Val(Mid(L_dat(0), 7, 2)) = Edt_CMM.Value Then If Lst_CmsCnt.ListCount = 0 Then Lst_CmsCnt.AddItem Mid(L_dat(0), 2, Len(L_dat(0)) - 2) Else If Mid(Lst_CmsCnt.List(Lst_CmsCnt.ListCount - 1, 0), 9, 2) _ <> Mid(Mid(L_dat(0), 2, Len(L_dat(0)) - 2), 9, 2) Then Lst_CmsCnt.AddItem Mid(L_dat(0), 2, Len(L_dat(0)) - 2) Else Lst_CmsCnt.List(Lst_CmsCnt.ListCount - 1, 1) = "-> " _ & Mid(L_dat(0), 13, 8) Lst_CmsCnt.List(Lst_CmsCnt.ListCount - 1, 2) = "= " _ & Right("00" & Hour(TimeValue(Mid(L_dat(0), 13, 8)) _ - TimeValue(Right( _ Lst_CmsCnt.List(Lst_CmsCnt.ListCount - 1, 0), 8))), 2) _ & ":" _ & Right("00" & Minute(TimeValue(Mid(L_dat(0), 13, 8)) _ - TimeValue(Right( _ Lst_CmsCnt.List(Lst_CmsCnt.ListCount - 1, 0), 8))), 2) _ & ":" _ & Right("00" & Second(TimeValue(Mid(L_dat(0), 13, 8)) _ - TimeValue(Right( _ Lst_CmsCnt.List(Lst_CmsCnt.ListCount - 1, 0), 8))), 2) End If End If ElseIf L_dat(1) = """" & "RG:" & Edt_CmsA & """" And _ Mid(L_dat(0), 2, 4) _ = Edt_CYY.Value And Val(Mid(L_dat(0), 7, 2)) = Edt_CMM.Value _ And _ Lbl_CmsCnt.BackColor <> RGB(0, 255, 255) Then Lbl_CmsCnt.BackColor = RGB(255, 255, 255) ElseIf L_dat(1) = """" & "RM:" & Edt_CmsA & """" Then Lbl_CmsCnt.BackColor = RGB(0, 255, 255) End If Loop .Close Lbl_CmsCnt.Caption = Lst_CmsCnt.ListCount End With End Sub
• 　27行目以降、[ CreateObject関数 ]で、従業員全員の出退社時刻が記録されるログファイルを開きます。[ Do While〜Loopステートメント ]で、ログファイルの最後の行まで、処理を繰り返します。１行ずつデータを取り出し、[ Split関数 ]で、アクセス日時とユーザー名をカンマ区切りで分けています。
  　
  　タイムカードとして出退社時間を記録しているテキストファイルへは、時系列で昇り順にログが記録されます。タイムカード勤怠管理は、１日一人あたりＩＮとＯＵＴの２回のログデータが記録されます。選択された社員コードが、その日の日付で最初に見つかった行の時間を出勤時間、その日の日付で２番目に見つかった行の時刻を退社時間としています。
  　
  　同一日付の同一ユーザーのアクセスが、２回以上の複数回有った場合は、最初の時間をログイン、最後の時間をログアウトの時間としてピックアップし、滞在時間を計算します。
  　
  　あとは[ TimeValue関数 ]を使って、何時何分何秒までの経過時間を計算しています。

　ここまでの解説で、ExcelのVBAマクロ、ユーザーフォームを使ったタイムカードシステムの作り方がマスターできました。手順をおさらいすると次のようになります。

• ユーザーが勝手にマクロを終了させないように、画面はフルスクリーンにする
• マクロの判断で、月ごとの外部ファイルを新規に作成、編集する 
• 外部ファイルを解析し、勤務時間の計算を行う 

　遅刻や早退、有給休暇や遅番/早番など、記録される時間に意味合いをもたせるには、ログの項目（カンマやタブスペースなどの区切り文字）を増やします。対応するコントロールをフォームに追加するなどすれば、自分オリジナルの勤怠管理システムができるでしょう。

番外７.

　VBAマクロを中心にしてExcelブック・ワークシート・セルを見ていくと、「呪縛」なるものが見えてきます。１ブック内のワークシートの枚数、ワークシート内の行列数の制限、セルの文字数などのことで、Excelはデータベースソフトではありませんが、Microsoft(R) Access に近い使い方をしたくなります。

　商品の在庫などを扱う場合、必要な情報は商品名だけではありません。ひとくちに価格といっても、仕入価格や卸価格、販売・実売価格などの種類を持っていることでしょう。その他にも型番や商品コード、分類など、複数の情報が必要になります。

　商品は１つではないでしょうから、数十点、数百点、流通系なら数万点の品揃えにあわせ、大量の商品情報を持つことでしょう。それらを元に、様々な帳票を印刷したり、販売係数の管理を行うわけですから、Excelブックのサイズも肥大するわけです。

　そこで、ワークシートの役割と、データベースの役割を分けてみたらどうでしょうか。結果的に、ワークシートは、人のための体裁（見栄え）に終始し、巨大なデータベースは、必要に応じて外部ファイルから読み込んで使う方法です。

• 　★　 外部ファイルのデータをリストボックスへ直接読み込む
  Private Sub フォーム表示002_Click() Load UserForm1 UserForm1.Caption = ThisWorkbook.Name Set O_Item = CreateObject("Scripting.FileSystemObject") Set F_Item = O_Item.GetFile("item.txt") Set S_Item = F_Item.OpenAsTextStream(1, 0) S_dat = S_Item.ReadLine Vrow = Len(S_dat) _ - Len(WorksheetFunction.Substitute(S_dat, " ", "")) + 1 If Not S_Item.AtEndOfStream Then S_Item.ReadAll Vcol = S_Item.Line - 1 S_Item.Close ReDim V_dat(Vcol, Vrow) Vcol = 1: Vrow = 1 Set S_Item = F_Item.OpenAsTextStream(1, 0) Do While S_Item.AtEndOfStream <> True S_dat = S_Item.ReadLine Vrow = 1 Do While IsError(Application.Search(" ", S_dat)) = False i = WorksheetFunction.Find(" ", S_dat, 1) - 1 V_dat(Vcol, Vrow) = Left(S_dat, i) S_dat = Mid(S_dat, i + 2) If IsError(Application.Search(" ", S_dat)) = True Then Vrow = Vrow + 1 V_dat(Vcol, Vrow) = S_dat Exit Do End If Vrow = Vrow + 1 Loop Vcol = Vcol + 1 Loop S_Item.Close With UserForm1 .Caption = ThisWorkbook.Name .Lbl_001.Caption = V_dat(1, 1) .Lst_001.ColumnCount = UBound(V_dat, 2) .Lst_001.ColumnWidths = ";0;0;0" For i = 1 To UBound(V_dat, 2) .Cmb_000.AddItem V_dat(1, i) Next .Cmb_000.Value = V_dat(1, 1) For i = 2 To UBound(V_dat, 1) .Lst_001.List = V_dat Next .Lst_001.RemoveItem 0 .Show End With End Sub
• 　起動直後のマクロブックには、商品データは一切入っていません。ユーザーの操作に応じて、外部ファイルの中から必要な商品データを直接リストボックスへと読み込んでいます。
  　
  　商品が決定され、リストとしての項目が必要とされなくなった時点で、リストボックス[ Lst_001 ]の項目を[ Clearメソッド ]で初期化します。

　このマクロブックでは、ワークシートを様式をもった帳票として印刷するとき以外は使いません。商品の選択や数量の指定をユーザーフォーム上で行い、帳票の印刷レイアウトも、マクロの中で指定しますので、ワークシートには書式の設定も無い状態です。ブックにあるのは、次の章で説明する「リレーショナルデータベース」のテーブルとなるVBAマクロだけです。