2010年11月11日

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

 あるグループに分けられた値が固定のもので、ユーザーにその中から1つを選択してもらうのに、便利なコントロールがあります。 [ ListBoxコントロール ]と[ ComboBoxコントロール ]です。どちらも似たようなものですが、[ ComboBoxコントロール ]のほうは、 プロパティの設定によって、機能も外観も変えられるリストコントロールです。
 簡単なリストを作るものに、[ AddItemメソッド ]と[ Array関数 ]があります。プロシージャの中で、リストの項目値を配列として与えます。
  •  ★  [ Array関数 ]で、リスト項目を設定する

     ★  リストを複数列で使い、表示する列数を設定する

     ★  リストで表示する列の幅を設定する



     コンボボックス[ 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関数 ]をそれぞれ使いました。 リストの項目を自由に扱う方法を覚えると、格段にフォームが扱いやすくなります。
  •  ★  オプションボタンの値に併せ、多次元配列のリスト項目を追加する

     ★  外部ファイル(画像)のファイル名・作成日時・バイト数を取得する



     指定したパスにあるファイルのうち、オプションボタンコントロールの値と[ Select Caseステートメント ]によって、 ユーザーの選択に応じたファイル分類(ファイル名の最初の文字と拡張子による判定)をピックアップします。
     [ Do While〜Loopステートメント ]で、フォルダ内の該当ファイルを[ Dir関数 ]とワイルドカード[ *(アスタリスク) ]を使ってピックアップし、 [ Listプロパティ ]のそれぞれの次元のリスト項目へ追加しています。 [ DateCreatedプロパティ ]はファイルの作成日時を取得し、[ Sizeプロパティ ]はファイルサイズを取得します。

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

     ★  列(セル)の中で、データの入力されている空白以外のセルを数える


     [ RowSourceプロパティ ]は、一見すると定まったセル範囲しか扱えないように考えがちです。 でも変動する顧客数(セルの行数)は、その都度数えてリストのソース範囲とするコードを書いておけば、 動的なワークシートの値と連携するフォームも安心です。
     ここでは、リスト項目として利用されるデータが、セルの1行目から途切れなく続いているとして、 [ CountA関数 ]を使ってデータの数を数え、「データの数 = 行数」として扱っています。

  •  ★  ワークシートを数えて、リスト項目に追加する

     ★  名前付け規則に沿ったシート名だけをリスト項目へ追加する


     リストは、空の状態で項目を設定しないと、それまでの項目に追加されてしまうので、 最初にリストを空にします。次に、ブックに含まれる全てのワークシート数を求め、 その中から特定の名前(「POS_」で始まるシート名)を持ったシートだけをコンボボックス[ Cmb倉庫 ]のリストに追加します。
     リストボックス[ Lst_領収 ]には、リスト1行に「商品コード/商品名/金額」の3つを表示させるため、 [ ColumnCountプロパティ ]で項目を3列、[ ColumnWidthsプロパティ ]で各列の表示幅をそれぞれ設定しています。

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

  •  ★  カンマ区切りの外部テキストファイルを開く

     ★  外部テキストファイルから、リストボックスの選択値と同じものをリストアップする

     ★  2つのタイムスタンプから、経過時間(勤務時間)を計算する


     27行目以降、[ CreateObject関数 ]で、従業員全員の出退社時刻が記録されるログファイルを開きます。 [ Do While〜Loopステートメント ]で、ログファイルの最後の行まで、処理を繰り返します。 1行ずつデータを取り出し、[ Split関数 ]で、アクセス日時とユーザー名をカンマ区切りで分けています。
     タイムカードとして出退社時間を記録しているテキストファイルへは、時系列で昇り順にログが記録されます。 タイムカード勤怠管理は、1日一人あたりINとOUTの2回のログデータが記録されます。選択された社員コードが、 その日の日付で最初に見つかった行の時間を出勤時間、その日の日付で2番目に見つかった行の時刻を退社時間としています。
     同一日付の同一ユーザーのアクセスが、2回以上の複数回有った場合は、最初の時間をログイン、 最後の時間をログアウトの時間としてピックアップし、滞在時間を計算します。
     あとは[ TimeValue関数 ]を使って、何時何分何秒までの経過時間を計算しています。

 ここまでの解説で、ExcelのVBAマクロ、ユーザーフォームを使ったタイムカードシステムの作り方がマスターできました。 手順をおさらいすると次のようになります。
  • ユーザーが勝手にマクロを終了させないように、画面はフルスクリーンにする
  • マクロの判断で、月ごとの外部ファイルを新規に作成、編集する
  • 外部ファイルを解析し、勤務時間の計算を行う

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

番外7.

 VBAマクロを中心にしてExcelブック・ワークシート・セルを見ていくと、「呪縛」なるものが見えてきます。 1ブック内のワークシートの枚数、ワークシート内の行列数の制限、セルの文字数などのことで、Excelはデータベースソフトではありませんが、 Microsoft(R) Access に近い使い方をしたくなります。
 商品の在庫などを扱う場合、必要な情報は商品名だけではありません。ひとくちに価格といっても、仕入価格や卸価格、 販売・実売価格などの種類を持っていることでしょう。その他にも型番や商品コード、分類など、複数の情報が必要になります。
 商品は1つではないでしょうから、数十点、数百点、流通系なら数万点の品揃えにあわせ、大量の商品情報を持つことでしょう。 それらを元に、様々な帳票を印刷したり、販売係数の管理を行うわけですから、Excelブックのサイズも肥大するわけです。
 そこで、ワークシートの役割と、データベースの役割を分けてみたらどうでしょうか。結果的に、ワークシートは、 人のための体裁(見栄え)に終始し、巨大なデータベースは、必要に応じて外部ファイルから読み込んで使う方法です。
posted by くきお at 11:11| Comment(0) | ExcelVBAマクロ | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。