2021/11/18

複数の備品を同時予約可能な貸出台帳

• １．背景
• ２．システム概要
• 　２－１．備品の予約
• 　２－２．予約情報の確認
• 　２－３．予約の取消・日程変更
• 　２－４．貸出・返却処理
• ３．プログラムの流れ
• ４．データベースシート
• 　４－１．Table１（Sheet1）
• 　　よりみち（ExcelシートDBのテーブルのデータ型）
• 　４－２．Table２（Sheet2）
• ５．操作シート
• 　５－１．ワークシート上の数式など
• 　５－２．条件付き書式の設定
• 　５－３．シートモジュール（Sheet0）
• 　　５－３－１．日付の処理
• 　　５－３－２．ユーザー操作の処理
• ６．ブックモジュール（ThisWorkbook）
• ７．標準モジュール（Module1）
• 　７－１．システム設定他
• 　７－２．SQL文の実行とデータ取得
• 　　７－２－１．データベースへの接続準備など
• 　　７－２－２．Select文の場合
• 　　７－２－３．Insert文、Update文の場合
• 　７－３．日程エリアのデータ作成
• 　　よりみち（SQLに使えるExcel関数）
• 　７－４．日程エリアの条件付き書式設定
• ８．ユーザーフォーム（UserForm1）
• 　８－１．コントロール類のレイアウト
• 　　８－１－１．予約ページ（Page1）
• 　　８－１－２．貸出／返却ページ（Page2）
• 　　８－１－３．確認ページ（Page3）
• 　　８－１－４．変更ページ（Page4）
• 　８－２．フォームモジュール
• 　　８－２－１．フォーム設定部
• 　　８－２－２．フォーム起動部
• 　　８－２－３．共通データ作成
• 　　８－２－４．ページの切替
• 　　　よりみち（マルチページの有効・無効）
• 　　８－２－５．予約済み備品リストの作成
• 　　８－２－６．予約ページ（ページ１）の処理
• 　　　８－２－６－１．ページ１の設定
• 　　　８－２－６－２．貸出可能備品リストの作成
• 　　　８－２－６－３．予約実行
• 　　　　よりみち（起動初回のSQLは非常に遅い）
• 　　　８－２－６－４．ユーザー入力欄のクリア
• 　　８－２－７．貸出／返却ページ（ページ２）の処理
• 　　　８－２－７－１．ページ２の設定
• 　　　８－２－７－２．リストボックスの未選択化
• 　　　　よりみち（リストボックスの未選択化）
• 　　　８－２－７－３．貸出／返却処理の実行
• 　　８－２－８．確認ページ（ページ３）の処理
• 　　８－２－９．変更ページ（ページ４）の処理
• 　　　８－２－９－１．ページ４の設定
• 　　　８－２－９－２．リストボックスの未選択化
• 　　　８－２－９－３．日程変更スクロールバーの移動
• 　　　８－２－９－４．予約取消の実行
• 　　　８－２－９－５．予約日程変更の実行
• ９．最後に
• サンプルファイル

１．背景

会議室や備品を予約するシステムは、今まで何回か紹介してきましたが、同時に選択できるのは「１個」に限られていました。
　・備品の予約・貸出・記録ができる貸出管理表
　・ExcelシートDBとSQLを使った会議室予約システム
しかし、例えばカメラと三脚・ストロボのように「複数の備品をセットで使用」する場合、別々に予約するのは面倒です。

そこで今回は「複数の備品を同時予約可能」な予約貸出システムを紹介します。なお今回は「予約する場所」と「貸出・返却する場所」は異なると考え、「Excelのワークシートをデータベース」にし「SQLで操作」するシステムとしました。
なおSQLでの操作については、下記の項も参考にして下さい。
　・共有資料の登録と閲覧ができるサーバーシステム
　・ExcelシートDBとSQLを使った倉庫管理システム
　・ExcelシートDBとSQLを使った会議室予約システム
　・Excelシート上にDBを作り、SQLを使ってデータを入出力する

なお、今回はExcelのシートをデータベーステーブルにしていますが、Accessのデータベースファイルを使ったシステム等については、下記を参照して下さい。
「ExcelからAccessデータベースを作成・操作」
「Accessデータベースを使用した売上台帳」

２．システム概要

操作するシートは図２－１のような感じです。備品エリア・日付エリア・日程エリアに分けて説明します。

図２－１

備品エリアには、今回はカメラに関連した備品を縦方向に並べています。備品の管理No.、備品名、仕様の３列とし、この３列分の情報を操作フォーム上に表示し、選択・確認するようにしています。
日付エリアは今回２週間分を表示するようにし、左上のスクロールバーの操作で週単位に移動をさせます。また「今週」ボタンをクリックすることで、今週にジャンプ（正確には、今日の日付が日付エリアの左側の週（＝左半分）に入る）します。
日程エリアには、各備品がどの日付の時に予約及び貸出中・返却済になっているかをガントチャート風に表しています。各状態はセルの背景色（赤色・青色・灰色）で見易くしています。また、この日程エリア内をマウス操作することで、予約・貸出・返却等の操作をする操作フォームが起動します。

なお操作フォームは、マウスをどのように操作したか・どのセルを操作したかにより、フォーム上のページが切り替わり「全ての操作が１つのフォーム上で出来る」ようにしてあります。ですので、操作のたびにフォームを終了させる必要が無く、連続した作業を可能としています。
（そのため「閉じる」ボタンは設置せず、「フォームの右上×印をクリック」のみで閉じる仕様にしています。）

２－１．備品の予約

備品を予約するには、まず借用したい期間が表示されるようにスクロールバーまたは今週ボタンで日付を移動させます。そして図２－２のように「借用したい期間を、日程エリア内でセル選択」します。
選択する行位置を備品エリアの借りたい項目に合わせる必要はありませんし、既に予約の入っているセルに重なってもOKです。

図２－２

通常のセル選択は、図２－２の左側のように複数行の選択も可能ですが、今回システムでは、日程エリア内では「複数行を選択しても、自動的に単一行選択に修正（図２－２の右側）」されます。この仕様の目的は、ユーザーに単一行を選択させると言うよりは、複数エリアを選択させない（飛び地を作らない）という意味で、このような動作にしています。

ユーザーが借用期間をセル選択①すると、図２－３のように操作フォーム②が表示されます。

図２－３

ユーザーがセル選択した借用期間は、上部の日付エリアの日付に基づいていますので、その開始日・終了日を「貸出日」「返却日」としてフォーム上に表示します。
また、選択した「期間内で連続して貸出可能な（＝まだ予約等がされていない）備品」をフォームのリストボックスに表示します。表示される内容は、備品エリアの３列分の内容となります。

借用したい備品は、フォーム上のリストボックスをクリックして選択③します。複数の備品を選択可能で、再度クリックすることで選択解除できます。
また、フォーム下段に必要項目「氏名、部署、連絡先、パスワード」を入力④します。パスワードは、予約を消去したり期間を変更したりする際に使用するもので、半角英数字で入力します（今回は次数制限をしていないので１文字以上であればOKとしています）。

図２－４

また、この時点で借用期間を変更したい場合は、日程エリア内でセル選択を再度行えば、フォームは新たな日程情報に切り替わります（既にリストを選択した状態の時には、一旦解除されます）。
リスト選択、必要項目の入力が完了したら「予約」ボタン⑤をクリックします。
すると図２－５のように、日程エリア内の指定した備品の行に「予約済みの赤いセル」が追加されます。

図２－５

ユーザーが予約した内容は、予約番号（図２－５では「Y7」の内の「7」）に紐づけられたデータとして保存されています。また日程エリアでのセル選択範囲はユーザーが選択したままの状態ですので、フォーム上のリストボックスの「セル選択している期間内での貸出可能な備品」は、今回予約した備品を除いたものにデータ更新されます。
またフォーム下段の必要入力項目は、そのまま残留させていますので、連続して予約を取ることが可能です。

＜注意事項＞ 予約実行や以降で説明する貸出／返却処理、予約の変更・取消などは、SQL文のInsert文・Update文の実行でデータベースに追加・変更を加えています。システムを起動し、初めてInsert文・Update文を実行する際は、データベースへの接続等に相当時間がかかります。（私のPCでは初回の実行に約12秒） その間はExcelの操作が全く出来ない状態（無限Loopに入った感じ）ですが、しばらくすると実行が完了しますので、申し訳ありませんが待っていて下さい。 「よりみち」でも説明しますが、色々試してはみたのですが、いまだに上手い解決方法が見つかっていません。

２－２．予約情報の確認

日程エリア内には、自分の予約分も含めて全ての予約状況が表示されています。その情報（例えば、誰が借りているのか等）を確認するためには、図２－６のように「色のついたセル上で、マウス右クリック⑦」をします。

図２－６

すると操作フォームは「確認ページ⑧」に切り替わり、そこに予約情報が表示されます。なお、フォームが表示されていない状態で右クリックした場合には、フォームが新たに出現し確認ページが表示されます。
なお、この状態で異なるセルを右クリックすれば、そのセルの情報に切り替わります。また、予約済の背景色赤のセルだけではなく、貸出中（青い背景色）、返却済（灰色の背景色）のセルでも同様に情報が得られます。（空白セルで右クリックした場合は、そのセルには情報が無いので「新規に予約をする意思」と判断して、予約ページを表示するようにしています。）

２－３．予約の取消・日程変更

予約を取り消したり、予約の日程を変更したりするには、図２－６の確認ページで「取消・日程変更」ボタン⑨をクリックします。すると、図２－７のようにフォームが「変更ページ⑩」に切り替わります。

図２－７

この変更ページでは、「貸出日」「返却日」をスクロールバー⑪で変更可能です。但し「借用する備品は、そのまま変更しない」という前提ですので、既に前後に予約が入っている場合は、予約が重ならない範囲での日程変更操作となります。
例えば図２－８は、セル値が「Y7（ 7 は予約番号）」となっている予約に対する変更を行っている場面です。予約を早めるには「貸出日側のスクロールバーを移動」させることになりますが、管理番号201の三脚が11/2まで貸出中となっていますので「貸出日を早められても11/3まで（＝スクロールバーのMin限界）」となります。
また、返却日についても11/12から三脚に対して別の貸出予約が入っていますので、「返却日は最大でも11/11まで（＝スクロールバーのMax限界）」となります。
なお「貸出日と返却日が逆転しない」ように、スクロールバー値は制御しています。


図２－８

日程の修正が完了したら「予約時に入力したパスワード（図２－４の④の一番下）⑫」を再度入力し、「日程変更決定」ボタン⑬をクリックします。すると、日程エリアの予約情報が変更（例えば赤いセル範囲が変更）されます。
なお、パスワードは「ユーザーが予約時に登録したパスワード」が基本ですが、管理者パスワードでも変更可能な仕様にしています。これは「出張に備品を借用し滞在が延びた時に、連絡を受けた管理者が変更できる」ことが必要だとして考えた仕様です。（サンプルファイルでは、管理者パスワードは長さゼロの文字列にしていますので、パスワード未入力で試用が可能です）

一方予約を取り消す場合は、予約時パスワードを入力⑫後、「予約取消」ボタン⑭をクリックします。本当に取り消して良いかの確認を取った後、予約が削除されます。

なお、貸出中（青いセル）の情報について変更する際は、既に貸出を行っているため変更出来るのは返却日のみ（貸出日変更用のスクロールバーは消えます）となります。また「予約取消」のボタンも操作不可にしています。
返却済（灰色のセル）の場合は、既に完了していますので、図２－６の「取消・日程変更」のボタンが操作出来ないようにしています。

２－４．貸出・返却処理

備品の貸し出し・返却は、備品管理者の役割です。ユーザーが管理者のところにやって来たら、（ユーザーは、自分がこれから借用する備品がどれか分かっているはずなので）図２－９のように予約済のセルを「ダブルクリック⑮」します。

なお、ダブルクリックのイベント（BeforeDoubleClick）よりもセル選択のイベント（SelectionChange）の方が早く発生してしまうため、フォーム未表示状態の時にダブルクリックしても、SelectionChangeによって起動する予約ページの方が表示されてしまうことがあります。その時は一旦フォームを表示させておき、表示させたままで作業するセルをダブルクリックすると「貸出／返却」ページが表示され易いようです。

図２－９

表示された「貸出／返却ページ」の予約情報を確認し、フォーム下段に「管理者パスワード⑰」を入力した上で「貸出」ボタン⑱をクリックします。すると、図２－１０のようにセルの色が「赤色→青色に変化⑲」し、フォームは確認ページへ移動⑳します。

図２－１０

なお図２－９、図２－１０では、「予約済」→「貸出中」にする処理でしたが、貸出中（青色）のセルをダブルクリックした場合は「貸出中」→「返却済」の処理を行います。
異なる点は、図２－９のフォームの貸出ボタンが「返却」ボタンになるだけですが、返却ボタンをクリックすることで返却への処理が行われ、セルが青色から灰色に変わります。

３．プログラムの流れ

今回システムでは、日程エリアのセルには「状態（Status）＋予約番号」の形の文字列を記入しています。これは、ユーザーがクリックした場所（備品と日程の交点）の情報を「予約番号をキー」にして得るためと、日程エリアに設定した条件付き書式でガントチャート風に予約状況を見せるためです。

まず、システム起動時に様々な設定を行いますが、その１つとして「備品情報」のデータ配列を操作シートの備品エリアから取得します。このデータ配列は「これからユーザーに貸し出す備品リスト」としても使用されますし、また既に貸し出した備品の確認用のリストとしても使われます。


図３－１

ユーザーが操作シートを操作するとワークシートのイベントが発生し、その情報「セルの位置、セルの値、操作方法」がUserForm1に伝わりフォームが起動します。
まずフォームを起動する前に、ユーザー操作の情報をデータとして一時保存（ユーザーが操作したセルの情報）します。そして、その情報の内の「予約番号（Lno）」をキーにSQLを使って、Table1とTable2から詳細な情報を吸い上げ、データに追加します。
ですので、フォームが表示される時には、ユーザーが選択したセルの情報が全て配列（配列名Data）に格納されている事になります。

フォームで何のページを表示するかは、ワークシート側から渡された情報「ユーザーが選択しているセルの値（Statusの部分）、操作方法（選択したのか、Wクリックしたのか、右クリックしたのか）」によって振り分けます。
フォームは「予約ページ」「貸出/返却ページ」「確認ページ」「変更ページ」の４つのページに分けています。別々のフォームを起動することも可能ですが、違う作業でフォームを横に移動しておいたのに、新たなフォームを起動するたびに「Excelの真ん中」に現れるのは煩わしいと思い、今回はマルチページコントロールを使用しました。

各ページの表示内容は、フォーム起動時に格納したセル情報を使って作ります。
ただし「予約ページ」では、ユーザーが選択した期間で「使われていない備品」を探す必要がるので、SQLを使ってデータを収集します。
また「変更ページ」でも、「どこまでの日程変更ならば、他の予約に重複しないか」を調べる必要があるのでSQLを使います。

各ページの実行ボタンをクリックすると、フォーム上でのユーザーの操作、選択、入力に従ってSQL文を作成し、Table1及びTable2に対してデータ挿入・変更を行います。予約取消の場合には、本来のSQLであればDelete文を使うのですが、Excelシートをデータベースにしていると使用できませんので「Del列」を作り、そこに削除した日付を記入することで削除した事にしています。

最後に、更新されたデータを元にして「日程エリアの、状態（Status）＋予約番号」を書き換えることで、条件付き書式によりガントチャート風のグラフが変化します。

なお今回システムでは、備品管理者と予約者が別PCから操作することを想定していますので、操作用シートとデータベースシート（Table1、Table2）は別ブックになります。「サンプルファイル」では、操作シートとDBシートを同じブックに収めていますが、正式に運用する際には、少なくともデータベースのブックを置く場所（ファイルサーバー等）が必要になります。

４．データベースシート（サンプルファイルではSheet1・Sheet2）

今回は図４－１のように、ユーザーが操作するデータを共有のサーバー上のデータシートに保存するシステムです。この方法を使用すると、多くの人が同時に作業することが可能になります。
サーバー上に置くデータシートは、OracleやSQL-Serverなど本格データベースのテーブルに相当するものです。テーブルとデータをやり取りする方法は、本格データベースと同様のSQLという言語を使用します。

また今回システムには半固定の「備品」情報も必要です。この情報はデータシートには置かずに操作用シートに配置しています。半固定ということは変わる可能性がありますので、操作用シートを使用者に配ってしまうと修正が大変です。
ですので図４－１のように、ファイルサーバーに操作用シートを置き、ユーザーには操作用シートを読取専用で開いて作業してもらうのが良いと思います。


図４－１

今回はテーブル２個（２つのシート）を使用します。テーブルは、標準モジュール（図７－１の８９～９０行目）で、Table1 とTable2という変数名を与えています。
「Table1（Sheet1）」には、備品と借用期間、及び状態（予約済＝Y、貸出中＝K、返却済＝H）の情報を記録します。
「Table2（Sheet2）」には、予約をした人の名前や部署などの情報、及び予約・貸出・返却をした日付を記録します。２つのテーブルは、予約情報（Lno）で繋がっています。
また、データを削除した時にはTable1のDel列に削除の日時を記録するようにしています。

４－１．Table１（Sheet1）

Table１は図４－２のように、６列で構成されます。こちらのTable1にはユニーク番号（行を特定するもの）はありません。

図４－２

列名	内容
Lno	予約番号。Table2のユニーク番号
MngNo	備品番号（図２－１のB列の「管理No.」）
StartDay	貸出日（予約者がセル選択で指定した日付）
EndDay	返却日（予約者がセル選択で指定した日付）
Status	状態(予約済=Y､貸出中=K､返却済=H)
Del	ユーザーが予約取消を行った日時

図４－３

図４－２を見てみると、各列の値が全て左側に寄っていることから「全列、文字列としてデータが入力」されていることが分かります。先頭には「'（シングルクォーテーション）」が入っており、数値セルの左上には緑の三角印（「数値が文字列として保存されています」との注意書き）が付いています。 この状態は手入力で値入力したものでは無く、SQL文を使ってテーブルに記録したものです。 一般的なデータベースには、テーブルを作る時に「データ型を指定」して作ることでデータ型を揃えることが可能です。例えばA列は追番なので数値（Integer型など）に指定すれば、数値としてデータが入ってくれるのですが、Excelシートをデータベースのテーブルとした場合にはうまく行きません。 ただし、手修正で「'（シングルクォーテーション）」を削除しておくと、次からのデータが数値として記録される列（Lno列だけ？）もあるので、この辺に改善のヒントが隠されているのかもしれません。なお、文字列と数値を混在させると良い結果は出ないようです。 以上のことから、今回のシステムは「全ての列が、文字列のデータ」を前提としてプログラムを作りました。手動で「'（シングルクォーテーション）」を削除などの手修正を加えると、エラーが発生してしまう場合があります。

４－２．Table２（Sheet2）

Table２は図４－４のように、８列で構成されます。

図４－４

列名	内容
Lno	予約番号（１から始まるユニーク番号）
Lname	借用者
Dept	部署
Tel	連絡先
pw	取消･変更する際のパスワード
YoyakuD	予約日時（ユーザーが最初に予約した日時）
KasidasiD	貸出日時（管理者が貸出処理をした日時）
HenkyakuD	返却日時（管理者が返却処理をした日時）

図４－５

Table1のStatus情報やDel情報、Table2の予約日・貸出日・返却日の情報（＝テーブルの列）を、もう一方のテーブルに移動するとSQL文の発行が１つ少なくなる（全てを１つのテーブルにすれば、もっとSQL文が減る）のですが、正規化の度合いとSQL文の発行数のバランスを考えて、今回はこのようなテーブル構造としました。ベストでは無いかもしれません。

５．操作シート（サンプルファイルではSheet0）

５－１．ワークシート上の数式など

図５－１のように操作用のシート（サンプルファイルではSheet0）には、まず日付を変更するためのScrollBar1と、今週に日付をジャンプさせるためのCommandButton1を配置します。２つのコントロールは今回「ActiveXコントロール」で作成してますが、フォームコントロールでも可能（プロシージャ名の変更と、操作する対象オブジェクト名の変更が必要です）です。
CommandButton1の表示文字は、配置時にCaptionプロパティとFont.Sizeプロパティを手動で変更しています。

備品エリア（B5～D10セルの範囲）には、手動で備品を記入しています。B列の管理Noはユニークな番号としてシステム内で使用しますので、ダブリはNGです。サンプルファイルでは管理Noとして101、102などの数値を使用していますが、E101、G101などと文字列を使用することはOKです。ただし運用途中で管理Noを変更すると、それまでに予約した情報が表示されなくなってしまいますので、注意が必要です。

図５－１

日付エリア（E2～R4セルの範囲）の下段（Excelシートの４行目）には、曜日を記入しています。この曜日は手入力で、日曜から開始しています。また、土曜・日曜の薄オレンジ色の背景色は手入力で設定しています。

日付エリアの中段（Excelシートの３行目）には日付（月／日）が表示します。その中でＥ３セルは日付の基準となるセルで、マクロ側からＥ３セルに「表示のための基準日」を書き込むようにしています。
基準日の右隣（Ｆ３セル）には、数式で「= E3 + 1」と入力し、それを日付エリア中段の右端まで数式コピーします。このことでＥ３基準日から連続した日付け（今回サンプルファイルでは２週間分）が表示されることになります。

日付エリアの上段には「年」を表示します。といって全ての日付に対して年を表示すると返って見難くなりますので、表示初日セル（Ｅ２）と「年が変わった時の日付」にのみ表示しています。
数式としてはＥ２セルに「= YEAR( E3 )」と入力し、表示初日の年を表示させます。その右隣（Ｆ２セル）には「=IF(YEAR(F3)=YEAR(E3),"",YEAR(F3)) 」と入力し、日付エリア上段の右端まで数式コピーします。これにより図５－２のように、１つ前の日の年と異なる場合（＝１月１日）のみに年を表示させています。

図５－２

５－２．条件付き書式の設定

日程エリア（E5～R10セルの範囲）の１つ１つのセルは、予約等の情報がマクロ側から書き込まれます。書き込む内容は「状態＋予約番号」で、状態とは「Y（予約済）」「K（貸出中）」「H（返却済）」の３種類です。
今回、日程エリアをガントチャート風にするために、図５－３のように各セルの「先頭文字（＝状態）」に条件付き書式を反応させ、状態に対応した背景色（予約済＝赤色、貸出中＝青色、返却済＝灰色）にします。

図５－３

適用先は、日程エリア（今回は、E5～R10セル）を対象とし、先頭文字を検出するために、例えば赤色背景色の場合のルールは「=LEFT(E5,1)="Y"」などとしています。
なお、手動で条件付き書式を設定してもOKですが、設定するマクロ（図７－１１）も参考にして下さい。但し、起動毎に実行する必要はありません（備品の数を変更した時には、条件付き書式の再設定が必要）。

５－３．シートモジュール（Sheet0）

５－３－１．日付の処理

日付を変更するスクロールバーを操作（スクロールバー値の変更）した時には、図５－４のChangeイベントが発生します。
今回システムでは、予約等が可能なのは「システム初日（DayStart：サンプルファイルでは2021/8/1）」以降のみ　としていますので、日付の表示もシステム初日以降しか必要ありません。ですので、スクロールバーのMin値はゼロ（＝システム初日週）とし、スクロールバーのValue値の分だけ週が増えていくようにしています。

1. '==========　⇩(1)　日付変更用スクロールバーの操作　============
2. Private Sub ScrollBar1_Change()
4. 　DispDayStart.Value = DayStart + Me.ScrollBar1.Value * 7
5. 　Call makeGantt
7. End Sub

図５－４

４行目「DispDayStart.Value = DayStart + Me.ScrollBar1.Value * 7」は、日付の基準日のセル（DispDayStart：サンプルファイルではE3セル）に対して「システム初日（DayStart）＋スクロールバー値の数の週」を書き込みます。
５行目「Call makeGantt」は、標準モジュールの図７－９を呼び出し、日程エリアにデータを再出力します。これにより、新たな日付でガントチャートが更新されます。

「今週」ボタンをクリックした時には、図５－５のClickイベントが発生します。

9. '==========　⇩(2)　｢今週｣ボタンの実行　============
10. Private Sub CommandButton1_Click()
12. 　Me.ScrollBar1.Value = ((Date - Weekday(Date) + 1) - DayStart) / 7
14. End Sub

図５－５

１２行目「Me.ScrollBar1.Value = ((Date - Weekday(Date) + 1) - DayStart) / 7」は、本日の日付（Date）の週の日曜日をWeekday関数を使って計算し、システム初日（DayStart）を起算日としている週数にスクロールバー値を設定します。
スクロールバー値を変更することで、図５－４のChangeイベントが発生し、日程エリアのガントチャートが更新されます。

５－３－２．ユーザー操作の処理

ユーザーによる操作シート（サンプルファイルでは、Sheet0）上のセル操作に反応するため、操作シートのシートモジュールにイベントプロシージャを置きます。
ユーザー操作に反応する内容は、１つはマウス操作したセルの情報、もう１つは操作の方法です。今回「操作の方法」は「通常のセル選択操作（左クリック）」「セルをダブルクリック」「セルを右クリック」の３種を受け取り、ユーザーの意思をフォーム側へ伝えます。

まず、セル選択をした時には図５－６のSelectionChangeイベントが発生します。

16. '==========　⇩(3)　選択セルの変更　============
17. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
18. 　Static DisableEvent As Boolean 　　 '←再帰呼び出しをスルーさせるフラグ変数
20. 　If DisableEvent = True Then Exit Sub
22. 　On Error Resume Next
24. 　　DisableEvent = True
25. 　　　Intersect(DispArea, Target).Rows(1).Select
26. 　　DisableEvent = False
28. 　　If Not Err.Number = 0 Then Exit Sub
29. 　On Error GoTo 0
31. 　Set Target = Selection
33. 　Call DispArea_Operation(Target, "S")
34. End Sub

図５－６

１８行目の「Static DisableEvent As Boolean」は、再帰呼び出しされるSelectionChangeイベントの実行をスルーさせるフラグ変数です。このフラグ変数はモジュールレべル変数などで宣言する場合もありますが、「Static」で宣言することで「プロシージャ内のみで有効だが、値は実行後も保持される」変数になるため、再帰呼び出しされた先でも有効となります。
２０行目「If DisableEvent = True Then Exit Sub」では、そのフラグ変数DisableEventがTrueの時（再帰呼び出しをしたくない時）に Exit Sub でSelectionChangeイベントをスルーします。

２５行目「Intersect(DispArea, Target).Rows(1).Select」では、日程エリア内の一行分を選択しています。
詳細に説明すると、まず前半の「Intersect(DispArea, Target)」は、図５－７のように、ユーザーが選択したセル範囲（Target）と、日程エリア（DispArea）が重複するセル範囲を示しています。

図５－７

その重複した範囲「Intersect(DispArea, Target)」内の１行目「 Rows(1) 」は、図５－８の赤の点線枠ということになります。

図５－８

この範囲をマクロ側からセル選択「.Select」しますので、見た目は図５－９のようになります。

図５－９

ここで注意するべきことが２点あります。
１つ目は、マクロ側からとは言え「セル選択範囲を変更」していますので、「SelectionChangeイベントが発生」します。つまり自プロシージャから自プロシージャを呼び出す「再帰呼び出し」が発生するのです。再帰呼び出しされても動作に支障は無いのですが、フォームを呼び出す動作を繰返してしまいますので無駄です。
そのため２４行目「DisableEvent = True」でフラグ変数にTrueを代入し、再帰呼び出し側の２０行目「If DisableEvent = True Then Exit Sub」で、再帰呼び出し側をスルーさせています。

２つ目は、ユーザーが「日程エリア以外のみをセル選択」した場合です。その場合は「Intersect(DispArea, Target)」が返す値が「Nothing（＝重複するエリアは無い）」になります。Nothingに対する Rows(1) は存在しませんし、存在しないものをSelectも出来ませんので、エラーが発生します。
そのため２２行目「On Error Resume Next」で、エラーが発生してもスルーさせるようにしています。そして２８行目「If Not Err.Number = 0 Then Exit Sub」で、エラー発生（＝日程エリア以外のみをセル選択）の場合は、SelectionChangeを終了し、３３行目を実行させない（フォームを起動しない）ようにしています。

再帰呼び出しされたSelectionChangeイベントは、２０行目の「If DisableEvent = True Then Exit Sub」でスルーしてしまいますので、３１行目まで実行されるのは「ユーザーが複数行を選択した（かもしれない）時に呼び出された、最初のSelectionChangeイベントです。ですので、その引数Targetは「複数行（かもしれない）」の選択範囲となっています。
しかし、マクロによって「日程エリア内の１行のみを選択」したのですから、それに合わせるために３１行目「Set Target = Selection」を実行し、「現在セル選択している範囲（日程エリア内の１行）」を引数Targetに置き換えます。

そして、３３行目「Call DispArea_Operation(Target, "S")」で、図５－１２のDispArea_Operationを呼び出し、そこを経由してUserFormを起動します。
DispArea_Operationには２つの引数を渡します。第一引数は「現在選択しているセル範囲」、第二引数は「ユーザーがどの様な操作をしたか」です。第一引数は３１行目で置き換えたセル範囲Target、第二引数は「通常のセル選択」である印の「S（SelectionChangeの先頭のS）」です。

なお、３１行目「Set Target = Selection」を使わずに、３３行目を「Call DispArea_Operation(Selection, "S")」と、Selectionを直接指定してもOKです。

セルをダブルクリックした時には、図５－１０のBeforeDoubleClickイベントが発生します。

36. '==========　⇩(4)　セルのダブルクリック　============
37. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
39. 　If Intersect(DispArea, Target) Is Nothing Then Exit Sub
41. 　Cancel = True
43. 　Call DispArea_Operation(Target, "D")
44. End Sub

図５－１０

３９行目「If Intersect(DispArea, Target) Is Nothing Then Exit Sub」では、日程エリア外でダブルクリック操作をした時には、このプロシージャの処理をせずに抜け出します。
まず前半の「Intersect(DispArea, Target)」は、図５－７と同様で、ダブルクリックしたセル（Target）が日程エリア（DispArea）内であれば、その重複するセル範囲を返してきます。しかし日程エリア外であれば、重複する範囲が無いのでNothingとなります。
つまり、日程エリア外をダブルクリックした場合は、If文の「Intersect(DispArea, Target) Is Nothing」がTrueになるため、プロシージャを抜け出し（Exit Sub）ます。

ダブルクリックしたセルが日程エリア内であった場合は、４１行目「Cancel = True」を実行し、通常ダブルクリックでセルの編集モードになる事をキャンセルします。
そして４３行目「Call DispArea_Operation(Target, "D")」で、図５－１２のDispArea_Operationを呼び出し、そこを経由してUserFormを起動します。
第一引数は「ダブルクリックしたセル範囲」、第二引数は「ユーザーがどの様な操作をしたか」で、「ダブルクリック」である印の「D（DoubleClickの先頭のD）」です。

セルを右クリックした時には、図５－１１のBeforeRightClickイベントが発生します。

46. '==========　⇩(5)　セルの右クリック　============
47. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
49. 　If Intersect(DispArea, Target) Is Nothing Then Exit Sub
51. 　Cancel = True
53. 　Call DispArea_Operation(Target, "R")
54. End Sub

図５－１１

４９行目「If Intersect(DispArea, Target) Is Nothing Then Exit Sub」は、日程エリア外で右クリック操作をした時には、このプロシージャの処理をせずに抜け出します。

右クリックしたセルが日程エリア内であった場合は、５１行目「Cancel = True」を実行し、通常右クリックでメニューを出す事をキャンセルします。
そして５３行目「Call DispArea_Operation(Target, "R")」で、図５－１２のDispArea_Operationを呼び出し、そこを経由してUserFormを起動します。
第一引数は「右クリックしたセル範囲」、第二引数は「ユーザーがどの様な操作をしたか」で、「右クリック」である印の「R（RightClickの先頭のR）」です。

図５－６の３３行目、図５－１０の４３行目、図５－１１の５３行目から呼び出されるDispArea_Operationが図５－１２です。第一引数として「操作したセル範囲」、第二引数として操作内容（セル選択＝S、ダブルクリック＝D、右クリック＝R）を受け取ります。

56. '==========　⇩(6)　フォーム側へのユーザー操作を連絡　============
57. Private Sub DispArea_Operation(Target As Range, Shift As String)
58. 　Dim FirstD As Date 　　 '←ﾕｰｻﾞｰがセル選択した範囲の、左端位置（日付）
59. 　Dim LastD As Date 　　 '←ﾕｰｻﾞｰがセル選択した範囲の、右端位置（日付）
61. 　FirstD = Me.Cells(DispDayStart.Row, Target(1).Column).Value
62. 　LastD = Me.Cells(DispDayStart.Row, Target(Target.Count).Column).Value
64. 　Call UserForm1.UFstart(FirstD, LastD, Target(1).Value, Shift)
65. End Sub

図５－１２

このプロシージャは、SelectionChange（図５－６）、BeforDoubleClick（図５－１０）、BeforRightClick（図５－１１）で得た情報をまとめ、フォーム側へ伝えるものです。
もちろん各々からフォーム側へ伝えても良いのですが、各プロシージャで同じコードを書くことになってしまいますので、今回はこのような手法にしました。

６１行目「FirstD = Me.Cells(DispDayStart.Row, Target(1).Column).Value」では、選択したセル範囲（１行分）の左端セル（１番目のセル）が何日を示しているかを計算しています。日付は変数DispDayStart（日付の基準値：サンプルファイルではE3セル）の行に表示しているので、行位置は「DispDayStart.Row」、列位置はセル範囲（Target）の１番目なので「Target(1).Column」として求められます。
６２行目「LastD = Me.Cells(DispDayStart.Row, Target(Target.Count).Column).Value」は、選択セルの右端が何日を示しているかを計算しています。行位置は左端と同じですが、列位置は最後のセルですので「Target(Target.Count).Column」となります。

６４行目「Call UserForm1.UFstart(FirstD, LastD, Target(1).Value, Shift)」は、フォーム（UserForm1）上にある「UFstart」プロシージャを呼び出します。
引数として、セル範囲が何日から何日までを示しているか（FirstD, LastD）、そのセル範囲の先頭セルの値（Target(1).Value）、ユーザーがどんな操作をしたか（Shift）　を渡します。最後の引数Shiftは、プロシージャとして受け取った引数Shiftそのままを渡しています。

「UFstart」プロシージャは、受け取った情報を元にフォームを作成し、表示されていなければフォームを起動します。

６．ブックモジュール（ThisWorkbook）

今回システムでは「DispArea」「DispDayStart」などのRange範囲、またデータベースに接続するオブジェクトを変数として扱っています。そのためユーザーが操作シートで操作を始める前に「変数設定など」を行っておく必要があります。
そこで図６－１のブック（＝システム）をOpenする時に、６９行目「Call SystemIni」で、標準モジュールのSystemIniプロシージャ（図７－３）を呼び出し、「システムに必要な設定」を実行します。

67. '==========　⇩(7)　ブックを開く時　============
68. Private Sub Workbook_Open()
69. 　Call SystemIni
70. End Sub

図６－１

またブック（＝システム）を閉じる時には、図６－２のBeforeCloseイベントが発生します。通常ブックを閉じる際、ブックに変更があった場合は「･･･の変更内容を保存しますか？」とのコメントが表示されますが、このBeforeCloseイベントはそのコメントが出る前に発生します。

７４行目「Call SystemEnd」で標準モジュールのSystemEndプロシージャ（図７－５）を呼び出し、６９行目で設定した変数などをクリアしています。
７６行目「ThisWorkbook.Saved = True」は、「ブックの変更内容は保存済」の状態にします。ですので、この図６－２を終了した後、Excelがブックを閉じる時には「保存済み」となっているので「変更内容を保存？」のコメントが出ずにファイル終了することが出来ます。（もちろん、保存が必要な場合は、７４行目を削除して下さい。）

72. '==========　⇩(8)　ブックを閉じる時　============
73. Private Sub Workbook_BeforeClose(Cancel As Boolean)
74. 　Call SystemEnd
76. 　ThisWorkbook.Saved = True
78. End Sub

図６－２

「先行予約可能な備品予約・貸出システム」では、操作ボタンやワークシートを初めて操作した時に「変数設定など」を起動させていましたが、今回はシステムを開く時（＝ブックを開く時）に設定しています。
どちらでも良いと思いますが、Rangeやデータベース接続オブジェクトは、システム終了と共にちゃんとNothingをしておいた方が良いのでは　と思ったのと、「データは全て他のファイルに保存」しているシステムなのだから、ブックを閉じる時に「保存して終了」か「保存せずに終了」を迷う必要は無いだろう　ということで、今回はブックのOpen・Close時に変数設定・解除などをしています。

７．標準モジュール（Module1）

７－１．システム設定他

標準モジュール先頭の宣言部（図７－１）では、システム内で使用する定数・変数の宣言を行います。

80. '==========　⇩(9)　共通定数・変数の宣言　============
81. Public Const DayStart As Date = "2021/8/1" 　 '←システム初日
82. Public Const KPw As String = "" 　　　　　　　 '←管理者パスワード
84. Public DispArea As Range 　　　　　　　　　　　 '←予定表のエリア
85. Public DispDayStart As Range 　　　　　　　　　 '←日付の先頭セル位置
86. Public Equips As Variant 　　　　　　　　　　　　 '←機材の配列
88. Private DB_FILE As String 　　　　　　　　　　　 '←DBファイルのパス＋ファイル名
89. Public Const Table1 As String = " [Sheet1$] " 　 'テーブル名（Sheet1）
90. Public Const Table2 As String = " [Sheet2$] " 　 'テーブル名（Sheet2）
92. Private cn As Object 　　　　　　　　　　　　　　 '←ｺﾈｸｼｮﾝｵﾌﾞｼﾞｪｸﾄ変数
93. Private rs As Object 　　　　　　　　　　　　　　 '←ﾚｺｰﾄﾞｾｯﾄｵﾌﾞｼﾞｪｸﾄ変数

図７－１

８１行目「Public Const DayStart As Date = "2021/8/1"」は、システム初日を定数値として設定しています。日程エリアに表示可能な日付はこの日以降となります。なお値を変更する際は、「日曜日」の日付として下さい。
８２行目「Public Const KPw As String = ""」では、管理者のパスワードを設定します。サンプルファイルでは８２行目のように「""（長さゼロの文字列）」に設定していますので、このままでは貸出／返却処理、ユーザーの予約内容の日程変更・削除が、全てパスワード無しで可能な状態になっていますのでご注意下さい。

８４行目「Public DispArea As Range」は、日程エリアのセル範囲（図７－２）であるDispAreaを宣言します。
８５行目「Public DispDayStart As Range」は、日付の基準日のセル（図７－２）であるDispDayStartを宣言します。
８６行目「Public Equips As Variant」は、セル範囲では無く、図７－２の備品エリアのデータを保持する配列を宣言します。

図７－２

８８行目「Private DB_FILE As String」は、「データベースファイルのパス名＋ファイル名」を文字列として宣言します。図７－６のSQL文実行の中でのみ使用します。
８９行目「Public Const Table1 As String = " [Sheet1$] "」は、各SQL文の中で使用する「テーブル名」を定数宣言しています。テーブルの内容は、図４－２、図４－３です。
なお、Excelのワークシートをデータベースのテーブルとする際は、シート名＋「$」を角カッコで囲います。
９０行目「Public Const Table2 As String = " [Sheet2$] "」も、「テーブル名」の定数宣言です。テーブルの内容は、図４－４、図４－５です。

９２行目「Private cn As Object」は、ADO（ActiveX Data Objects）のConnectionオブジェクトの宣言です。また、９３行目「Private rs As Object」は、ADOのRecordsetオブジェクトの宣言です。
今まで（「ExcelシートDBとSQLを使った倉庫管理システム」など）は、SQL文を実行するプロシージャ（今回システムでは、図７－６）内で、都度宣言・生成していたのですが、少しでも動作を早くしたいと思い、オブジェクト生成をシステム起動時に行い、そのオブジェクトを使い続けることにしました。
この手法により、サンプルファイルで日付を移動するのに必要な時間が0.17秒→0.03秒（私のノートPC）と約1/5になるので、処理速度的には効果があるようです。

システム起動時（図６－１のWorkbook_Open）に呼び出される変数等の設定プロシージャが図７－３です。

95. '==========　⇩(10)　システムの変数等設定　============
96. Public Sub SystemIni()
97. 　Dim DBfilepath As String 　　　 '←DBファイルのパス
98. 　Dim DBfilename As String 　　　 '←DBファイルのファイル名
100. 　Set cn = CreateObject("ADODB.Connection")
101. 　Set rs = CreateObject("ADODB.Recordset")
103. '　DBfilepath = ThisWorkbook.Path & "¥" 　 '←サンプルファイルは､こちらを使用
104. 　DBfilepath = "¥¥Server¥Excel¥DB¥" 　　 '←DBファイルのあるパスに書き換える
105. '　DBfilename = ThisWorkbook.Name 　　 '←サンプルファイルは､こちらを使用
106. 　DBfilename = "it-068.xlsm" 　　　　　　 '←DBファイルのファイル名に書き換える
107. 　DB_FILE = DBfilepath & DBfilename
109. 　Set DispArea = Sheet0.Range("E5:R10")
110. 　Set DispDayStart = Sheet0.Range("E3")
111. 　Equips = DispArea.Offset(0, -3).Resize(, 3) 　　 '←備品の3列分を配列Equipsに代入
113. 　Sheet0.ScrollBar1.max = 500
114. 　Sheet0.ScrollBar1.min = 0
115. 　Sheet0.ScrollBar1.LargeChange = 4
116. 　Sheet0.ScrollBar1.SmallChange = 1
117. 　Sheet0.ScrollBar1.Value = (Date - Weekday(Date) + 1 - DayStart) / 7
119. 　Call makeGantt
121. End Sub

図７－３

１００行目「Set cn = CreateObject("ADODB.Connection")」は、ADOのコネクションオブジェクトを生成しています。
１０１行目「Set rs = CreateObject("ADODB.Recordset")」は、ADOのレコードセットオブジェクトを生成しています。
１００～１０１行目の cn、rsは、図７－１の９２～９３行目でモジュールレベルの変数として宣言していますので、システム起動中はずっと生きていることになります。

１０４行目「DBfilepath = "¥¥Server¥Excel¥DB¥"」では、データベースファイルが存在する場所（パス名）を、１０６行目「DBfilename = "it-068.xlsm"」では、データベースファイルのファイル名を設定しています。
稼働させる環境に合わせて、パス名・ファイル名を設定し直して下さい。
なおサンプルファイルでは、１つのファイル内で操作シートとデータベースファイルを兼任させているため、１０３行目・１０５行目のコードの方を生かしています。
１０７行目「DB_FILE = DBfilepath & DBfilename」では、上記で設定したパス名（DBfilepath）、ファイル名（DBfilename）をつなげて、SQL文の実行（図７－６）時に「データベースファイル名」として使用しています。

１０９行目「Set DispArea = Sheet0.Range("E5:R10")」は、図７－２の「DispArea」の設定をしています。もし備品を増減する際は、ここの指定を変更する必要があります。
１１０行目「Set DispDayStart = Sheet0.Range("E3")」は、図７－２の「DispDayStart」の設定です。日付エリアの位置などの変更を行った場合は、指定変更が必要です。
１１１行目「Equips = DispArea.Offset(0, -3).Resize(, 3)」は、図７－２の備品エリアの情報を配列Equipsに格納しています。サンプルファイルでは備品列は３列としましたので、DispAreaの左側３列分を指定しています。備品列数を変更する際はOffsetの移動量・Resizeの取得列数の変更が必要なのと、フォームの各ページのListBoxの列数を変更する必要があります。

１１３～１１７行目は、操作シートの日付移動用スクロールバーの設定です。操作シート（Sheet0）上のスクロールバーのオブジェクト名は、図７－４のようにスクロールバーを選択することで名前ボックス（数式バーの左側）に表示されます。

図７－４

１１３行目「Sheet0.ScrollBar1.max = 500」は、スクロールバーMax値を500に設定しています。スクロールバー値は週の数を表しますので、約10年となります。状況に合わせて調整して下さい。
１１４行目「Sheet0.ScrollBar1.min = 0」はMin値をゼロにしています。これにより「システム初日（定数DayStart）」より前の日は表示できなくなります。
１１５行目「Sheet0.ScrollBar1.LargeChange = 4」は、スクロールバーのレール部をクリックした時の移動量を４に設定します。４（週間）ですので、約１ヶ月に相当します。
１１６行目「Sheet0.ScrollBar1.SmallChange = 1」は、スクロールバー両端のスクロール矢印をクリックした時の移動量を１にします。これにより１（週間）ずつの移動ができます。
１１７行目「Sheet0.ScrollBar1.Value = (Date - Weekday(Date) + 1 - DayStart) / 7」は、図５－５の「今週ボタン」の式と同じです。ですので、システム起動時は「まず今週が表示」されることになります。

システム終了時に、BeforeCloseイベントプロシージャ（図６－２）から呼び出されるのが図７－５です。

123. '==========　⇩(11)　システム変数の解除　============
124. Public Sub SystemEnd()
125. 　Set rs = Nothing
126. 　Set cn = Nothing
128. 　Set DispArea = Nothing
129. 　Set DispDayStart = Nothing
130. End Sub

図７－５

１２５行目「Set rs = Nothing」及び１２６行目「Set cn = Nothing」では、ADOのConnectionオブジェクト・Recordsetオブジェクトを解除させています。
また１２８行目「Set DispArea = Nothing」及び１２９行目「Set DispDayStart = Nothing」では、日程エリア・日付基準日のセル範囲を解除しています。

７－２．SQL文の実行とデータ取得

SQL文を実行し、テーブルからデータを取得するときに使用されるのが、図７－６です。
第一引数として「SQL文」を、第二引数として「テーブルから読み取り専用でデータを取得するか」を受け取ります。第二引数は単純に言えば「Select文の時＝True（第二引数は省略可）」「Insert文、Update文の時＝False指定」となります。

132. '==========　⇩(12)　SQL文の実行　============
133. Public Function SQL_exec4(sql As String, Optional ReadOnly As Boolean = True) As Variant
134. 　Const adOpenStatic = 3 　　 '←カーソルの種類を｢静的カーソル｣に設定(1,-1,3　は取得可)
135. 　Dim temp() As Variant 　　 '←データを格納する一時的な配列
136. 　Dim i As Long 　　　　　　 '←得られたデータの行数
137. 　Dim j As Long 　　　　　　 '←得られたデータの列数
139. '　Set cn = CreateObject("ADODB.Connection")　 　　 '今回は起動時に実行
140. '　Set rs = CreateObject("ADODB.Recordset")　 　　 '今回は起動時に実行
142. 　cn.provider = "MSDASQL"
143. 　cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
144. 　　　　　　　　　　　　　& "DBQ=" & DB_FILE & ";" _
145. 　　　　　　　　　　　　　& "ReadOnly=" & ReadOnly & ";"
147. '　cn.provider = "Microsoft.ACE.OLEDB.12.0"　 '
148. '　cn.ConnectionString = "Data Source=" & DB_FILE & ";" _　 '
149. '　　　　　　　　　　　　& "Extended Properties=""Excel 12.0;"""　 '
151. 　cn.Open
152. 　　If ReadOnly = True Then 　　　　　　　 '←Select文の場合
154. 　　　rs.Open sql, cn, adOpenStatic
155. 　　　　If rs.EOF = False Then 　　　　　　　 '←摘出ﾃﾞｰﾀが有る場合
156. 　　　　　ReDim Preserve temp(0 To rs.RecordCount - 1, 0 To rs.Fields.Count - 1)
157. 　　　　　i = 0
158. 　　　　　Do Until rs.EOF
159. 　　　　　　For j = 0 To rs.Fields.Count - 1
160. 　　　　　　　temp(i, j) = rs.Fields(j).Value
161. 　　　　　　　If IsNull(temp(i, j)) Then temp(i, j) = ""
162. 　　　　　　Next j
163. 　　　　　　i = i + 1
164. 　　　　　　rs.MoveNext
165. 　　　　　Loop
167. 　　　　Else 　　　　　　　 '←摘出ﾃﾞｰﾀが無い場合
168. 　　　　　ReDim temp(0 To 0, 0 To 0) 　　　 '←他の場合と同様に2次元配列を作る（各要素は空）
170. 　　　　End If
172. 　　　　SQL_exec4 = temp 　　　 '←配列を戻り値とする
173. 　　　rs.Close
175. 　　Else 　　　　　　　 '←Insert文、Update文の場合
176. 　　　On Error Resume Next
177. 　　　　cn.Execute sql
178. 　　　　If Not Err.Number = 0 Then SQL_exec4 = False
179. 　　　On Error GoTo 0
181. 　　End If
182. 　cn.Close
184. '　Set rs = Nothing　 　　 '今回は終了時に実行
185. '　Set cn = Nothing　 　　 '今回は終了時に実行
186. End Function

図７－６

従来の項では、Select文を実行し得られたデータは、「Temp = rs.GetRows」とGetRowsメソッドを使用して一気に配列に落とし、その後で修正を加えていました。今回は、他のサイトでも良く見かける「１行ずつ配列に格納」していく方法を紹介します。

７－２－１．データベースへの接続準備など

まず、従来はプロシージャ内で実行していた１３９行目「Set cn = CreateObject("ADODB.Connection")」１４０行目「Set rs = CreateObject("ADODB.Recordset")」は、起動時に図７－３の１００～１０１行目で実行済みですので、ここでは不要になります。

１４２行目「cn.provider = "MSDASQL"」では、データベースにアクセスするためのProviderを設定します。
１４３～１４５行目「cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & DB_FILE & ";" & "ReadOnly=" & ReadOnly & ";" 」では、ドライバーとしてExcel Driverを指定し、データベースソース（DBQ）として図７－３の１０７行目で作成した変数DB_FILEを指定します。またReadOnlyには引数で得られた変数ReadOnlyを指定します。

なお、見え消しにしている１４７～１４９行目のように、ProviderにはAccess系のACEも使用できます。ACEを使用する場合は接続文字列（ConnectionString）もセットで変更になります。

１５１行目「cn.Open」で、データベースソースへの接続を開きます。
１５２行目「If ReadOnly = True Then」は、引数ReadOnlyの値を調べています。True（＝SQL文がSelect文）の場合は１５４～１７３行目を実行し、False（＝SQL文がInsert文、Update文）の場合は１７６～１７９行目を実行します。

７－２－２．Select文の場合

１５４行目「rs.Open sql, cn, adOpenStatic」では、レコードセットオブジェクトのカーソルを開きます。Openメソッドの引数として、今回は３つ渡します。（従来の項では２つ）
１つ目（sql）はSQL文、２つ目（cn）は接続しているConnectionオブジェクトです。３つ目はプロバイダーがレコードセット を開く際に使用するカーソルの種類で、図７－７のように５種類（「指定せず」を除くと４種類）あります。

定数	値	内容	用途
adOpenUnspecified	-1	カーソルの種類を指定せず (結局､既定値に設定される)	順方向専用カーソルと同じ
adOpenForwardOnly	0	順方向専用カーソル･･･既定値 (ﾃｰﾌﾞﾙを開いた時点のﾚｺｰﾄﾞのみ参照可)	結果を単純にｽｸﾛｰﾙしながらﾃﾞｰﾀを変更する場合
adOpenKeyset	1	キーセットカーソルを使用 (他ﾕｰｻﾞｰによるﾚｺｰﾄﾞの更新を参照可)	結果ｾｯﾄが大きく､複数行を選択する必要がある場合
adOpenDynamic	2	動的カーソルを使用 (他ﾕｰｻﾞｰの全操作を参照可)	同時実行中の他ﾕｰｻﾞｰ操作と結果セットを同期する場合
adOpenStatic	3	静的カーソルを使用 (開いた時点のﾚｺｰﾄﾞのみ｡但し後ろ戻り可)	順方向専用カーソルと同じ

図７－７

今回システムでは、データ変更はExecuteメソッド（図７－６の１７７行目）を使用しているため、本来カーソルの指定は必要ないのですが、「データ行数（＝レコード数）」を取得する必要があるため、設定しています。
「データ行数」を取得するには「RecordCountプロパティ」を使いますが、「データ行数が取得できるカーソル」が図７－８のように決まっています。

カーソルタイプ	値	RecordCountの戻り値
adOpenForwardOnly	0	-1
adOpenKeyset	1	レコード数
adOpenDynamic	2	-1
adOpenStatic	3	レコード数

図７－８

ですので今回は、１５４行目のOpenメソッドの第三引数に「adOpenStatic」をカーソルに指定し、RecordCountでデータ行数を取得します。但し今回は実行時バインディングでADOをセットしていますので、「adOpenStatic」という定数値に値が入っていないため。１３４行目で「Const adOpenStatic = 3」と定数設定をしています。

１５５行目「If rs.EOF = False Then」では、抽出されたデータが１つ以上存在する場合に、１５６～１６５行目を実行します。逆にすると「rs.EOF = True」が、カーソルが示しているレコード位置は「RecordSetオブジェクトの最後のレコードの後（End of File）」ですので、「rs.EOF = False」は「最後のレコードの後では無い（＝レコードが有る）」事になります。

１５６行目「ReDim Preserve temp(0 To rs.RecordCount - 1, 0 To rs.Fields.Count - 1)」では、１３５行目で宣言した動的配列の大きさを「行方向：データ行数」「列方向：データ列数」にサイズ変更しています。
行数は、上述したように「rs.RecordCount」で取得します。また列数は「rs.Fields.Count」で取得します。行・列ともインデックスはゼロ始まりとしています。

１５８～１６５のDo～Loopで、レコードが全て無くなるまで（Until rs.EOF）回しながら処理をしていきます。レコード数が分かっているのでFor～Nextで回してもOKですが、今回はDo～Loopを使用しました。
また１６４行目の「rs.MoveNext」で、次の行・次の行へと、レコード移動（カーソル移動）をしています。

また、行位置計算（＝値を代入する際の配列の行方向位置特定）のために１５７行目「i = 0」とカウンタ変数を初期化しています（カウンタ変数ｉは宣言しただけなので初期値ゼロとなっているため、１５７行目は省略してもOKです）
また「rs.MoveNext」でカーソル移動する時に合わせて行位置も変更が必要なため、１６３行目「i = i + 1」でカウンタ変数ｉを１ずつ増やしています。

Do～Loop内では、１５９～１６２行目のFor～Nextで列位置を移動させ、１６０行目「temp(i, j) = rs.Fields(j).Value」で１レコードを１列ずつ配列に代入します。
但しデータベースから取得してきたデータの内、Excelで言えば「空白セル」の値は「Null」という値になります。このNullをExcelのVBAで処理しようとするとデータ変換など色々面倒な処理が必要になるので、１６１行目「If IsNull(temp(i, j)) Then temp(i, j) = ""」で「Null → ""（長さゼロの文字列）」に変換しています。

一方「rs.EOF = True（データが１つも抽出されない）」の場合は、１６８行目「ReDim temp(0 To 0, 0 To 0)」を実行し、「データが入っていない、二次元配列temp」を作成します。
わざわざ二次元配列にしているのは、この関数プロシージャの戻り値（配列）を受け取った側で「データが有っても無くても、二次元配列として処理を実行（処理方法が１種類）」出来るようにするためです。

ここまででSelect文の処理は終了で、完成した配列tempを呼出し元に戻す必要があるため、１７２行目「SQL_exec4 = temp」で戻り値に設定します。そして１７３行目「rs.Close」でレコードセットオブジェクトのカーソルを閉じます。

７－２－３．Insert文、Update文の場合

Insert文、Update文の場合（第二引数ReadOnly＝False）は、１７６～１７９行目を実行します。
１７７行目「cn.Execute sql」では、引数として受け取ったSQL文を実行します。しかし、この実行が失敗する場合も考える必要があります。
失敗するとどんなシステムでも困るのですが、特に今回は書き込むテーブルが複数存在する（失敗すると完全性がNG）ことと、且つ複数の備品を同時に予約できることからテーブルに対してInsert文・Update文を複数回発行する（失敗すると一部の備品しか登録されない）システムです。

一般的なデータベースならCommitコマンド・Rollbackコマンドによりデータベースの完全性を確保できるのですが、Excelシートをデータベースとして使用した場合は、（試したところ）SQL文を実行した時点で確定してしまうようです。
ですのでSQL文でのエラーを感知し、エラーが発生した際には修正のSQLを発行できるように、１７６行目「On Error Resume Next」でエラーをスルーさせ、１７８行目「If Not Err.Number = 0 Then SQL_exec4 = False」でエラー発生の際には「False」を戻すようにしています（エラーが無い場合は、Emptyが戻る）。

なお「Select文側（１５４～１７３行目）」でも同様のエラー処理が必要とは思いましたが、Select文なのでデータが得られないだけで、（マクロは停止してしまいますが）データベースとしての完全性は保たれているため今回はそのままとしました。

最後に１８２行目「cn.Close」で、データベースソースへの接続を切断します。

７－３．日程エリアのデータ作成

起動初期（図７－３の１１９行目）、及びスクロールバー等による日付変更、フォーム上での予約・貸出・返却・日程変更・予約取消時に呼び出される「日程エリアのデータ更新」プロシージャが図７－９です。

188. '==========　⇩(13)　日程エリアのデータ作成　============
189. Public Sub makeGantt()
190. 　Dim Sday As Date 　　　　　 '←貸出表エリアの左端日付
191. 　Dim Eday As Date 　　　　　 '←貸出表エリアの右端日付
192. 　Dim sql As String 　　　　　 '←SQL文
193. 　Dim buf1 As Variant 　　　　 '←SQL文で取得したデータ配列
194. 　Dim buf2 As Variant 　　　　 '←貸出表エリアのセル相当の配列
195. 　Dim i As Long 　　　　　　　 '←データ配列の行数
196. 　Dim j As Long 　　　　　　　 '←備品の種類
197. 　Dim k As Long 　　　　　　　 '←1つの予定の日数
198. 　Dim StartArrayCol As Integer 　 '←1つの予定の開始列位置
199. 　Dim EndArrayCol As Integer 　 '←1つの予定の終了列位置
201. 　Sday = DispDayStart.Value
202. 　Eday = Sday + 13
204. 　Application.ScreenUpdating = False
206. 　　sql = "Select * from " & Table1 & _
207. 　　　　　　" where DateValue(StartDay) <= #" & Eday & "#" & _
208. 　　　　　　" and DateValue(EndDay) >= #" & Sday & "#" & _
209. 　　　　　　" and Del is null "
211. 　　buf1 = SQL_exec4(sql)
213. 　　DispArea.Value = ""
214. 　　If IsEmpty(buf1(0, 0)) Then Exit Sub
216. 　　ReDim buf2(1 To DispArea.Rows.Count, 1 To DispArea.Columns.Count)
218. 　　For i = 0 To UBound(buf1, 1)
219. 　　　For j = 1 To UBound(Equips, 1)
220. 　　　　If buf1(i, 1) = CStr(Equips(j, 1)) Then
222. 　　　　　StartArrayCol = DateValue(buf1(i, 2)) - Sday + 1
223. 　　　　　If StartArrayCol < 1 Then StartArrayCol = 1
225. 　　　　　EndArrayCol = DateValue(buf1(i, 3)) - Sday + 1
226. 　　　　　If EndArrayCol > 14 Then EndArrayCol = 14
228. 　　　　　For k = StartArrayCol To EndArrayCol
229. 　　　　　　buf2(j, k) = buf1(i, 4) & buf1(i, 0)
230. 　　　　　Next k
231. 　　　　　Exit For
233. 　　　　End If
234. 　　　Next j
235. 　　Next i
237. 　　DispArea = buf2
239. 　Application.ScreenUpdating = True
240. End Sub

図７－９

２０１行目「Sday = DispDayStart.Value」は、日付エリアの左端の基準日セル（DispDayStart）の日付を取得し、変数Sdayに代入します。また２０２行目「Eday = Sday + 13」は、その日付に「＋１３」し、日付エリアの右端セルの日付としてます。

２０６～２３７行目では、日程エリアのセル値の書き換えを行いますので、そのままだとチラツキが発生します。その防止策として２０４行目「Application.ScreenUpdating = False」で、画面更新停止させています。

２０６～２０９行目は、テーブルから「日程エリアに少しでも引っ掛かっている情報を取得」するSQL文です。SQL文は長いので４つに分割して表示しています。
　２０６行目「sql = "Select * from " & Table1 & _」
　２０７行目「" where DateValue(StartDay) <= #" & Eday & "#" & _」
　２０８行目「" and DateValue(EndDay) >= #" & Sday & "#" & _」
　２０９行目「" and Del is null "」

まず２０６行目は、Table1から全ての列の情報を取得しています。
次に２０７行目＋２０８行目で、図７－１０のように、日程エリア（日付で、SdayとEdayで囲まれたエリア）にかかっているものを選択します。各情報のEndDayとエリア左端のSdayを比較し、且つ各情報のStartDayとエリア右端のEdayを比較することで、日程エリアに掛かっているのか否かが判断できます。
なお、例えばEndDay＝Sdayの情報も「日程エリアに掛かっている情報」になりますので、不等号には「＝」を加えます。
この式により図７－１０で言えば、②③④⑥の情報が取得できます。
図７－１０

ここで使用している日付の比較式は、構文風に記述すると以下のようになります。
　DateValue（テーブル内の日付列名：文字列）　比較演算子　#日付#　
ここで重要なのは「日付型同士を比較」することです。テーブル内の日付は、図４－２で説明したように「文字列」として記録されていますので、テーブルの列名に対しては「DateValue関数を使って文字列を日付型に変換」します。
一方比較する実値の日付（構文の右辺）に対しては「日付の両端を#（ハッシュマーク）で囲む」必要があります。

私の中では「SQLでは日付は『'（シングルクォーテーション）』で囲む」と記憶していたのですが、この方法はExcelシートDBでは上手くいかないようです。
また、文字列同士を比較する方法もうまく行きません。例えば「2021/11/3」と「2021/11/10」という文字列を比べると「2021/11/3」の方が大きいと判断されます。「2021/11/03」と月・日をゼロを含めた２桁で記録すれば、正しい比較ができそうですが、文字列比較に固執するのは少し横道にズレている気がします。

また以前の項では、日付や時刻を全て数値にしてテーブルに書き込んでいたのですが、一応正しく比較できるものの目視で日付・時刻と分からないのは、苦肉の策とは言え今ひとつでした。

２０９行目の「" and Del is null "」は、「予約取消の項目を除く」という意味です。今回システムでは、予約の取り消しが出来るようにし、取り消した場合はTable1のDel列に取消日時を入力するようにしています。ですので「Del is null」で「取消を行っていない項目の中で」というWhere文になります。

Excelシートのデータベースを使う際、テーブル内の文字列として記録されている数値を数値型に直す時、SQL文に「Val」という関数を何気なく使っていました。その時は、考えられる関数を色々試してみた結果として「Valを使うと数値に変換される」という事を発見？したと思っていたのです。「たぶん、SQLの関数の中にValがあるんだろう。辞書には載って無いけど」という意識でした。 しかし上記のDateValue関数が成立するとなると、辞書に載っていないでは済みそうにありません。あまりにもExcelっぽい関数です。この時、Val関数は発見した関数なんかでは無く、ExcelシートDBをテーブルとする場合には「Excelの関数（の一部）が使えるのでは」と気が付きました。 多くを試したわけではありませんが、例えばExcelのIsNullという関数は使えます（図８－１９の４８１行目で使っています）。SQLにもIsNullはあるのですが、引数の数が異なり、Excelは１つ、SQLは２つで異なる関数です。 余りにもExcelに偏ったSQL関数ばかりだとコードが読み難くなってしまうのでどうかとは思いますが、Excel関数が使えるとなるとSQLで表現できる幅が広がるのでは、と思います。

２１１行目「buf1 = SQL_exec4(sql)」では、作成したSQL文を図７－６の「SQLの実行関数」に送ります。送っているSQL文はSelect文ですので、抽出結果として二次元配列が戻され変数buf1に代入されます。
２１３行目「DispArea.Value = ""」では、日程エリアを全て空にします。
２１４行目「If IsEmpty(buf1(0, 0)) Then Exit Sub」では、２１１行目で得られたSQLでの抽出結果が空（Empty）だった場合にプロシージャを抜け出します。これにより、予約等のない場合には日程エリアは空になります。

以降は２週間の間にデータがある場合です。
２１６行目「ReDim buf2(1 To DispArea.Rows.Count, 1 To DispArea.Columns.Count)」では日程エリアに一気にデータを貼り付ける（処理時間短縮化が目的）ために、日程エリアと同じ大きさの配列を作成します。
２１８行目「For i = 0 To UBound(buf1, 1)」は、SQLの抽出結果を１行ずつ調べます。
２１９行目「For j = 1 To UBound(Equips, 1)」は、備品のリストを上から調べて行きます。

２２０行目「If buf1(i, 1) = CStr(Equips(j, 1)) Then」では、抽出結果の（インデックスがゼロ始まりなので）２列目データ（MngNo列）と、備品リストの（インデックスが１始まりなので）１列目データ（備品管理No.）を比較します。
比較する際はデータ型に注意します。SQLで得られる結果のbuf内では「全て文字列」として扱われているようなので、備品リスト側もCstr関数で文字列型に変換してから比較をします。

２２０行目が成立した（データベース情報を記入する行位置が見つかった）時には、２２２～２３１行目を実行します。
まだ行位置しか明らかになっていないので、次は列位置（横方向（日付）は、どこからどこまでか）を計算します。

まず２２２行目「StartArrayCol = DateValue(buf1(i, 2)) - Sday + 1」で、「データベース情報のStartDay」と「日程エリア左端の日付」との差を求めます。図７－１０で③や④の場合だと「日程エリア左端から、いくつ離れたところから書き始めれば良いか」が分かることになります。
しかし、②や⑥の場合は日程エリアよりもStartDayが外側にありますので、マイナスの値が出てしまうことになります。そこで２２３行目「If StartArrayCol < 1 Then StartArrayCol = 1」で、マイナスになった場合には「日程エリアの左端（＝１）から書き始める」という補正を入れています。

書き終わりの位置も同じ考え方で、２２５行目「EndArrayCol = DateValue(buf1(i, 3)) - Sday + 1」で書き終わり位置を計算し、日程エリアから外れてしまう（>１４）場合は、２２６行目「If EndArrayCol > 14 Then EndArrayCol = 14」で、「日程エリアの右端（＝１４）で書き終える」という補正を入れています。

横方向の書き始め・書き終わり位置が決まりましたので、２２８～２３０行目で日程エリアに情報を書き出します。
まず書き込む先は、２１６行目で作成した配列buf2です。
２２８行目「For k = StartArrayCol To EndArrayCol」で、カウンタ変数kを２２２～２２６で計算した「日程エリア左端からの位置」の分だけ回します。そして２２９行目「buf2(j, k) = buf1(i, 4) & buf1(i, 0)」では、配列buf2の要素に「状態（buf1(i, 4)）＋予約番号（buf1(i, 0)）」を代入します。

データベースの１行分の情報の記入が完了したら、（残りの備品の管理Noは探す必要は無いので）２３１行目「Exit For」で２１９～２３４行目（カウンタ変数ｊで回しているFor～Next）を抜け、次の抽出データの処理に移ります。

全ての抽出データに対して処理（日程エリアへの書込み）が終了したら、２３７行目「DispArea = buf2」で、配列buf2のデータを日程エリアに貼り付けます。

７－４．日程エリアの条件付き書式設定

日程エリアへ書き込まれたデータ（状態＋予約番号）の先頭の「状態」に反応して、ガントチャート風に見せるためのセル背景色設定をしているのが条件付き書式です。図５－３のようになるように手動で設定してもOKですが、マクロで一気に設定するのが図７－１１です。但し、条件付き書式を毎回再設定する必要は無く、備品増減の際に手動で起動させて下さい。

242. '==========　⇩(14)　条件付き書式の設定　============
243. Private Sub makeFomula()
244. 　Dim Form As String 　　　 '←設定する数式
246. 　With DispArea.FormatConditions
247. 　　On Error Resume Next
248. 　　　.Delete
249. 　　On Error GoTo 0
251. 　　Form = "=Left(" & DispArea(1).Address(False, False) & ",1)=""Y"""
252. 　　With .Add(Type:=xlExpression, Formula1:=Form)
253. 　　　.Interior.Color = RGB(255, 0, 0)
254. 　　End With
256. 　　Form = "=Left(" & DispArea(1).Address(False, False) & ",1)=""K"""
257. 　　With .Add(Type:=xlExpression, Formula1:=Form)
258. 　　　.Interior.Color = RGB(0, 128, 255)
259. 　　End With
261. 　　Form = "=Left(" & DispArea(1).Address(False, False) & ",1)=""H"""
262. 　　With .Add(Type:=xlExpression, Formula1:=Form)
263. 　　　.Interior.Color = RGB(128, 128, 128)
264. 　　End With
266. 　End With
267. End Sub

図７－１１

まず、条件付き書式を設定する範囲は日程エリア（変数DispArea）ですので、２４６行目で「With DispArea.FormatConditions」とします。
そして、古い書式を削除すべく２４８行目「.Delete」で、日程エリア内の条件付き書式を削除します。ただし古い書式が無かった（システム初回設定時など）時にはエラーが出ますので、２４７行目「On Error Resume Next」でスルーさせます。

２５１行目「Form = "=Left(" & DispArea(1).Address(False, False) & ",1)=""Y"""」では、条件付き書式の数式を組み立てています。数式は適用先（DispArea）の左上セルに対して指定することになりますので、アドレスは「 DispArea(1).Address(False, False)」となります。Addressの後ろの(False, False)は、行・列とも$マークのついていない相対アドレスを指定します。

２５２行目「With .Add(Type:=xlExpression, Formula1:=Form)」で、組み立てた数式を条件付き書式に追加します。
２５３行目「.Interior.Color = RGB(255, 0, 0)」では、追加した書式が成立した時に変更する書式を設定します。今夏の場合、背景色を変更しますので「.Interior.Color」に色番号「RGB(255, 0, 0)　赤色」を指定します。

２５６~２５９行目、２６１～２６４行目は、数式の内、反応すべき「先頭の文字列」が異なるのと、設定する背景色が異なるだけですので、説明は割愛します。

８．ユーザーフォーム（UserForm1）

８－１．コントロール類のレイアウト

今回システムでは、「予約」「貸出／返却」「確認」「変更」の４つの操作を行います。それぞれに１つずつのフォームを作成しても良いのですが、４つとも同じような情報を扱うため、マルチページを使うことにしました。
これにより、連続したユーザー操作でもフォームの開く・閉じるの大袈裟な動作がなく、ページの切り替えだけで複数機能を表示・操作できることになります。

なおフォーム上に配置する各コントロールの番号は、どのページに配置するかには無関係に連番となります。全てのページで連番となると、コントロール配置後コードを組み立てる際に、コントロールを取り違える可能性があります。
ですので今回は、Page1のコントロールの番号は100番台、Page2のコントロールは200番台　のように、どのページのコントロールなのかが分かり易いようにしました。

８－１－１．予約ページ（Page1）

まず、フォーム一杯にマルチページ（MultiPage1）を配置し、ページは４ページ作成します。
Page１は、予約をする時に使用します。予約の期間は、ユーザーがセル選択した日程に対応します。その期間をフォーム上に明示する必要がありますので、開始日（＝貸出日）をLabel101に、終了日（＝返却日）をLabel102に表示します。
また、その期間で貸出可能な備品リストはListBox101に表示させ、ユーザー情報はTextBox101～104に入力してもらいます。

図８－１

Label101～103は、都度異なる値をマクロ側から書き込みますが、それ以外の表示Labelは配置時にCaptionを設定しています。

８－１－２．貸出／返却ページ（Page2）

Page２は、備品管理者が貸出／返却処理をする時に使用します。このページはセルをダブルクリックした時に表示されます。

図８－２

主は予約情報の確認ですので、予約情報等をLabelに表示することになります。また、ユーザーが予約で確保した備品はListBox201に表示（操作は不可）します。
情報を確認したら一番下のTextBox201に管理者用パスワードを入力し、CommandButton201をクリックし貸出／返却処理をします。

８－１－３．確認ページ（Page3）

Page３は、予約等の情報を確認する時に使用します。このページは基本的にセルを右クリックした時に表示されます。

図８－３

貸出／返却ページと同様で、予約情報等をLabel類とListBox301に表示しますが、予約の取り消しや予約日程期間の変更が可能な状況の場合には、一番下のCommandButton301をクリックすることにより、Page4へ移動が出来ます。

８－１－４．変更ページ（Page4）

Page４は、予約の取り消しや予約日程期間の変更に使用します。このページはPage3のCommandButton301をクリックした時に表示されます。

図８－４

予約情報はLabel類とListBox401に表示します。日付を変更するためのScrollBar401～402を日付Labelの横に配置し、予約を取り消す時のCommandButton401、日程を変更するためのCommandButton402を配置します。ボタンのそばには、予約をした者だけが分かるパスワードを入力するTextBox401を配置します。

８－２．フォームモジュール

８－２－１．フォーム設定部

フォーム内で使用する変数類を図８－５のように、宣言部で宣言します。

269. '==========　⇩(15)　フォームレベル変数宣言等　============
270. Dim IsNotEvent As Boolean 　　　 '←マクロによるイベント実行をしないようにするフラグ変数
272. Dim Data(1 To 14) As Variant 　　 '←ユーザーがクリックしたセルの情報を格納する配列
274. Enum f
275. 　Lno = 1:　　MngNo = 2:　　StartDay = 3:　　EndDay = 4:　　Status = 5
276. 　Lname = 6:　　Dept = 7:　　Tel = 8:　　pw = 9
277. 　YoyakuD = 10:　　KasidasiD = 11:　　HenkyakuD = 12
278. 　ClickStartD = 13:　　ClickEndD = 14
279. End Enum

図８－５

２７０行目「Dim IsNotEvent As Boolean」は、Page4（変更ページ）のScrollBarの値をマクロ側から変更する際に、無駄な実行をさせないために使用するフラグ変数です。

２７２行目「Dim Data(1 To 14) As Variant」は、ユーザーがクリックしたセルの情報を格納する配列を宣言しています。情報は全部で１４種あります。番号で指定しても良いのですが、分かり難いために２７４～２７９行目で「列挙型変数」を設定し、分かり易い変数名を使うことにしました。列挙型変数は、図８－６のようになります。

ｲﾝﾃﾞｯｸｽ	変数名	内容
1	Lno	予約番号
2	MngNo	備品管理No(配列)
3	StartDay	貸出日(予約後)
4	EndDay	返却日(予約後)
5	Status	現在の状態
6	Lname	予約者氏名
7	Dept	予約者部署
8	Tel	予約者連絡先
9	pw	予約者パスワード
10	YoyakuD	予約日時
11	KasidasiD	貸出日時
12	HenkyakuD	返却日時
13	ClickStartD	現在選択している開始日
14	ClickEndD	現在選択している終了日

図８－６

２７２行目「Dim Data(1 To 14) As Variant」で分かるように、配列DataはVariant型で宣言しています。どんな型でも入りますので、２番目のMngNoへは「ユーザーが予約した複数の備品No.」を「配列」として代入します。

フォームが起動される時に実行されるのが、図８－７のInitializeイベントプロシージャです。
情報によって変化しないコントロールプロパティを設定します。もちろんコントロール配置時に設定してしまってもOKですが、後から「どこの設定を変更したのか分からなくなる」のを防ぐために、表面上すぐに分かるCaption以外は、Initializeで設定するようにしています。

281. '==========　⇩(16)　フォームの初期設定　============
282. Private Sub UserForm_Initialize()
284. 　Me.MultiPage1.Pages(0).Caption = "予約"
285. 　Me.MultiPage1.Pages(1).Caption = "貸出/返却"
286. 　Me.MultiPage1.Pages(2).Caption = "確認"
287. 　Me.MultiPage1.Pages(3).Caption = "変更"
288. '　　---- 予約ページ ----
289. 　Me.ListBox101.ColumnCount = 3
290. 　Me.ListBox101.ColumnWidths = "30;35;45"
291. 　Me.ListBox101.MultiSelect = fmMultiSelectMulti
293. 　Me.TextBox104.IMEMode = fmIMEModeDisable
294. '　　---- 貸出／返却ページ ----
295. 　Me.ListBox201.ColumnCount = 3
296. 　Me.ListBox201.ColumnWidths = "30;35;45"
298. 　Me.TextBox201.IMEMode = fmIMEModeDisable
299. 　Me.TextBox201.PasswordChar = "*"
300. '　　---- 確認ページ ----
301. 　Me.ListBox301.ColumnCount = 3
302. 　Me.ListBox301.ColumnWidths = "30;35;45"
303. '　　---- 変更ページ ----
304. 　Me.ListBox401.ColumnCount = 3
305. 　Me.ListBox401.ColumnWidths = "30;35;45"
307. 　Me.TextBox401.IMEMode = fmIMEModeDisable
308. 　Me.TextBox401.PasswordChar = "*"
310. End Sub

図８－７

２８４～２８７行目は、ページのタブに名前を付けています。Pagesのインデックスはゼロ始まりのため、Page1はPages(0)となります。

２８９～２９１行目は、予約ページ（Page1）の予約可能な備品一覧を表示するリストボックスの設定です。
２８９行目「Me.ListBox101.ColumnCount = 3」は、３列表示にしています。今回の備品エリアは３列としたので、その全てを表示させるために３列としました。
２９０行目「Me.ListBox101.ColumnWidths = "30;35;45"」は、その３列の各列幅を設定します。ListBox101をフォーム上に配置した時に、幅（Width）は約113ポイントでしたので、３ポイント引いた110ポイントを適当に３等分しました。なお、最後の列の幅は、45（今回）以下であれば「残りの幅を全て最終列幅に割り当てる」ので、ListBoxの幅をTry&Errorで調整するのであれば小さ目（例えば30とか）にしておいてもOKです。
なお、横スクロールバーを出さないための設定については「先入先出の入出庫管理システム」を参照下さい。
２９１行目「Me.ListBox101.MultiSelect = fmMultiSelectMulti」は、複数行の選択を可能な設定にします。

２９３行目「Me.TextBox104.IMEMode = fmIMEModeDisable」は、予約者のパスワードを入力するテキストボックスを「半角のみ」の入力に設定しています。氏名・部署・連絡先を入力するテキストボックスについては、今回何の設定もしていません。

２９５～２９９行目は、貸出／返却ページのコントロールの設定です。
２９５行目「Me.ListBox201.ColumnCount = 3」は、予約済みの備品リストの表示を３列にしています。
２９６行目「Me.ListBox201.ColumnWidths = "30;35;45"」は、そのリストの各列幅を設定しています。予約ページのリストボックスと実幅はほぼ同じですので、各列幅も同じにしています。
２９８行目「Me.TextBox201.IMEMode = fmIMEModeDisable」は、管理者パスワード入力欄を半角のみに設定します。
２９９行目「Me.TextBox201.PasswordChar = "*"」は、手入力しているパスワードを隠すために、文字列を「*（アスタリスク）」に変更しています。

３０１～３０２行目は、確認ページのコントロールの設定です。
３０１行目「Me.ListBox301.ColumnCount = 3」は、備品リストボックスを３列表示にし、３０２行目「Me.ListBox301.ColumnWidths = "30;35;45"」で、各列幅を設定しています。

３０４～３０８行目は、変更ページのコントロールの設定です。
３０４行目「Me.ListBox401.ColumnCount = 3」は、備品リストボックスを３列表示にし、３０５行目「Me.ListBox401.ColumnWidths = "30;35;45"」で、各列幅を設定しています。

３０７行目「Me.TextBox401.IMEMode = fmIMEModeDisable」で、予約者パスワード入力欄を半角のみにし、３０８行目「Me.TextBox401.PasswordChar = "*"」で手入力しているパスワードを「*」の文字で隠します。

８－２－２．フォーム起動部

ワークシート側（図５－１２）から呼び出されるのが図８－８です。引数として、以下の４つを受取ります。
　・第１引数（FirstD）：セル選択範囲の左端の日付（貸出日相当）
　・第２引数（LastD）：セル選択範囲の右端の日付（返却日相当）
　・第３引数（Tvalue）：セル選択範囲の１番目セルの値（状態＋予約番号）
　・第４引数（Shift）：マウスの操作状態（S=左クリック、D=ダブルクリック、R=右クリック）

プロシージャ内の流れとしては、まずユーザーがクリックしたセル情報をフォームの各表示に使用するために、前半の３１５～３２０行目で「配列Dataに情報を格納」していきます。
その後の３２２～３２９行目で、ユーザーの操作・クリックした位置により、表示するページを組み立て、３３１行目でフォームを表示します。

312. '==========　⇩(17)　フォームの起動　============
313. Public Sub UFstart(FirstD As Date, LastD As Date, Tvalue As String, Shift As String)
315. 　Data(f.Lno) = Mid(Tvalue, 2)
316. 　Data(f.Status) = Left(Tvalue, 1)
317. 　Data(f.ClickStartD) = FirstD
318. 　Data(f.ClickEndD) = LastD
320. 　Call makeData
322. 　Select Case Shift & Data(f.Status)
323. 　　Case "DY", "DK" 　　　　　 '←ダブルクリック+予約済、貸出済の場合
324. 　　　Call LendRet_ini
325. 　　Case "RY", "RK", "RH" 　　 '←右クリック+予約・貸出・返却の場合
326. 　　　Call Check_ini
327. 　　Case Else 　　　　　　　　 '←セル選択を含むその他
328. 　　　Call Yoyaku_ini
329. 　End Select
331. 　If Me.Visible = False Then Me.Show vbModeless
332. End Sub

図８－８

配列Dataに情報を格納する部分では、まず３１５行目「Data(f.Lno) = Mid(Tvalue, 2)」で、引数Tvalueの２文字目以降（予約番号の部分）を取り出し、配列Dataに代入します。
ただし、日程エリア内の空白セルをクリックした場合には、「Mid(Tvalue, 2)」は「""（長さゼロの文字列）」を返し、それがData(f.Lno)に代入されることになります。

３１６行目「Data(f.Status) = Left(Tvalue, 1)」は、引数Tvalueの先頭の文字列（状態の部分）を配列Dataに代入します。なお、日程エリア内の空白セルをクリックした場合には「""（長さゼロの文字列）」が入ることになります。
３１７行目「Data(f.ClickStartD) = FirstD」は、引数の「選択したセル範囲の左端日付」を配列Dataに代入します。
３１８行目「Data(f.ClickEndD) = LastD」は、引数の「選択したセル範囲の右端日付」を配列Dataに代入します。

引数によって得られる情報は、以上４つです。残りの１０個は、３２０行目「Call makeData」で図８－１０を呼出して、配列Dataへ代入します。

次に表示するページを確定し、ページを表示する準備をします。
まず「操作方法（縦軸） × 状態（横軸）」の組み合わせ（図８－９）から、移動するページを考えます。

	なし	Y(予約済)	K(貸出中)	H(返却済)
S（左ｸﾘｯｸで選択)	Page1	Page1	Page1	Page1
D（ﾀﾞﾌﾞﾙｸﾘｯｸ）	Page1	Page2	Page2	Page1
R（右ｸﾘｯｸ）	Page1	Page3	Page3	Page3

図８－９

今回、特徴的な操作は「ダブルクリック」「右クリック」ですから、その操作で得られるページを先に決めます。
ダブルクリックは、管理者が「予約中→貸出の処理、貸出中→返却の処理」をする操作ですので、状態が予約中（Y）・貸出中（K）の時にPage2（貸出／返却ページ）に移動するようにします。返却済の状態の時には「次の状態が無い」ので、Page2の対象から外します。
また右クリックは、ユーザー・管理者が「予約中・貸出中・返却済の状態の情報を確認しに行く」操作ですので、状態が予約中（Y）・貸出中（K）・返却済（H）の時にPage3（確認ページ）に移動するようにします。空白セルを右クリックするのは、何を確認しようとしているのか不明なので対象から外します。
その他の時はPage1（予約ページ）に移動することにし、まとめたのが図８－９と言うことになります。

３２２行目「Select Case Shift & Data(f.Status)」は、この「ユーザー操作方法 × 状態」により表示するページを切り替えています。Page2が３２３行目「Case "DY", "DK"」、Page3が３２５行目「Case "RY", "RK", "RH"」、Page1が３２７行目「Case Else」に相当します。そして、ページに合った情報表示をするため、以下の〇〇_iniプロシージャを実行します。
　・Page2（貸出／返却ページ）の時：３２４行目「Call LendRet_ini」（図８－２２）
　・Page3（確認ページ）の時：３２６行目「Call Check_ini」（図８－２５）
　・Page1（予約ページ）の時：３２８行目「Call Yoyaku_ini」（図８－１５）

各ページの表示準備が完了したら、３３１行目「If Me.Visible = False Then Me.Show vbModeless」で、フォームをモードレス（フォーム起動中もシート操作可）で起動します。
前半の「If Me.Visible = False Then」は、「フォームが起動されていなかったら」という意味で、既に起動されていたら３２２～３２９行目の「〇〇_iniプロシージャの実行」により、ページが切り替わることになります。この手法（毎回、新しいフォームを起動しない）により、ユーザーが操作し易い場所にフォームを移動させた後、異なる操作をしてもフォームの位置が変わらないようになります。

８－２－３．共通データ作成

図８－８の３２０行目から呼び出される「配列Dataへの情報の代入」が、図８－１０です。
プロシージャの役目としては、ワークシート側から渡された「予約番号（Data(f.Lno)に代入済み）」を元に、Table1とTable2から「シートから渡された以外の情報」を配列Dataに代入することです。

334. '==========　⇩(18)　共通データ作成　============
335. Sub makeData()
336. 　Dim sql As String 　　　 '←SQL文
337. 　Dim buf As Variant 　　 '←SQLで取得されたデータ配列
338. 　Dim i As Long 　　　　 '←登録されている備品数
340. 　If Not IsNumeric(Data(f.Lno)) Then Exit Sub
342. 　sql = "Select startday,endday,MngNo from " & Table1 & _
343. 　　　　　" where val(Lno)=" & Data(f.Lno)
344. 　buf = SQL_exec4(sql)
346. 　　Data(f.StartDay) = DateValue(buf(0, 0))
347. 　　Data(f.EndDay) = DateValue(buf(0, 1))
349. 　　Data(f.MngNo) = WorksheetFunction.Index(buf, 0, 3)
350. 　　If UBound(Data(f.MngNo), 1) > 1 Then
351. 　　　ReDim buf(1 To UBound(Data(f.MngNo), 1))
352. 　　　For i = 1 To UBound(Data(f.MngNo), 1)
353. 　　　　buf(i) = Data(f.MngNo)(i, 1)
354. 　　　Next i
355. 　　　Data(f.MngNo) = buf
356. 　　End If
358. 　sql = "Select Lname,Dept,Tel,Pw,YoyakuD,KasidasiD,HenkyakuD from " & Table2 & _
359. 　　　　　" where val(Lno)=" & Data(f.Lno)
360. 　buf = SQL_exec4(sql)
362. 　　Data(f.Lname) = buf(0, 0)
363. 　　Data(f.Dept) = buf(0, 1)
364. 　　Data(f.Tel) = buf(0, 2)
365. 　　Data(f.pw) = buf(0, 3)
366. 　　Data(f.YoyakuD) = buf(0, 4)
367. 　　Data(f.KasidasiD) = buf(0, 5)
368. 　　Data(f.HenkyakuD) = buf(0, 6)
370. End Sub

図８－１０

まず、３４０行目「If Not IsNumeric(Data(f.Lno)) Then Exit Sub」では、図８－８の３１５行目で代入した予約番号が空（＝セル選択した先頭セルが空白セル）の場合は、そのセルからは情報が得られませんので、プロシージャを抜けます。

予約番号がある場合には、その予約番号を頼りにしてTable1とTable2から情報を得て、配列Dataにデータを代入します。
まずTable1では、３４２～３４３行目のSQL文「sql = "Select startday,endday,MngNo from " & Table1 & " where val(Lno)=" & Data(f.Lno)」で、貸出日（startday）・返却日（endday）・備品No（MngNo）を取得します。
なお、このSQL文では「複数の備品を同時に予約した場合、複数行のデータが抽出」されます。予約番号が同じであれば、どの行も貸出日・返却日は同じで、異なるのは備品Noだけです。

３４４行目「buf = SQL_exec4(sql)」で、標準モジュールの図７－６を呼出してSQL文を実行し、その抽出結果を変数bufに代入します。そのbufの値を使って３４６～３５６行目で配列Dataに値を代入します。

３４６行目「Data(f.StartDay) = DateValue(buf(0, 0))」は、Select文の１番目の列名「startday」の値を配列Dataに代入しています。抽出データが複数行存在しても、startday値は同じですので１行目（インデックスはゼロ）の値を使います。
３４７行目「Data(f.EndDay) = DateValue(buf(0, 1))」は、Select文の２番目の列名「endday」の値を配列Dataに代入しています。

３４９行目「Data(f.MngNo) = WorksheetFunction.Index(buf, 0, 3)」では、Select文の３番目の列名「MngNo」の値を配列Dataに代入します。ワークシート関数Indexを使って、SQLによる抽出データbuf（２次元配列）の３列目を切り出し、配列として代入しています。
ワークシート関数Indexは３つの引数を取ります。１番目は処理する配列、２番目が行位置、３番目が列位置です。Index関数は、ワークシートや配列のどこか１箇所の要素を取り出すのが通常の使い方ですが、図８－１１のように「３番目引数（列位置）にゼロを指定すると、行全体が取得」でき、逆に「２番目引数（行位置）にゼロを指定すると、列全体が取得」できます。

図８－１１

但し、複数行の元配列から「列全体を取得」した場合、図８－１１の右側のように「二次元配列」となります。
一方、単一行の元配列から「列全体を取得」した場合は、図８－１２の右側のように「一次元配列」となるのです。


図８－１２

今回システムでは、SQLにより抽出されるデータは、単一行の場合も複数行の場合もあります（ユーザーが予約する備品が１つの場合も複数の場合もある）。しかし単一行・複数行で配列の形が異なるのでは、その配列データを使って処理をする工程で処理を分ける必要が出てくるので好ましくありません。
ですので今回は、「一次元配列に揃える」ことにしました（備品NoなどをListBoxに並べるだけなので、一次元の方が扱い易いと考えました）。

配列の行数は「UBound(Data(f.MngNo), 1)」で調べられます（Index関数で切り出した時には、配列のインデックスは１始まりになっている）ので、３５０行目「If UBound(Data(f.MngNo), 1) > 1 Then」で「複数行（二次元配列）だった場合」は、３５１～３５５行目で「一次元配列を作る」処理をします。
まず、３５１行目「ReDim buf(1 To UBound(Data(f.MngNo), 1))」で、入れ物である一次元配列を作成します。配列変数は既に不要になった変数bufを使っています。
３５２行目「For i = 1 To UBound(Data(f.MngNo), 1)」で、カウンタ変数ｉを行数分回し、３５３行目「buf(i) = Data(f.MngNo)(i, 1)」で、二次元配列を一次元配列に入れ直しています。
最後に３５５行目「Data(f.MngNo) = buf」で、一次元配列でデータを更新します。

次にTable2では、３５８～３５９行目のSQL文「"Select Lname,Dept,Tel,Pw,YoyakuD,KasidasiD,HenkyakuD from " & Table2 & " where val(Lno)=" & Data(f.Lno)」で、予約番号（Data(f.Lno)）に紐づいた　予約者（Lname）・部署（Dept）・連絡先（Tel）・パスワード（Pw）・予約日時（YoyakuD）・貸出日時（KasidasiD）・返却日時（HenkyakuD）を取得します。Table2では、予約番号（Lno）はユニーク番号になりますので、単一行が得られる事になります。
３６０行目「buf = SQL_exec4(sql)」で、標準モジュールの図７－６を呼出してSQL文を実行し、その抽出結果を変数bufに代入します。そのbufの値を使って３６２～３６８行目で配列Dataに値を代入します。

３６２行目「Data(f.Lname) = buf(0, 0)」は、Select文１番目の「Lname（予約者）」を配列Dataに代入します。
３６３行目「Data(f.Dept) = buf(0, 1)」は、２番目の「Dept（部署）」を配列Dataに代入します。
３６４行目「Data(f.Tel) = buf(0, 2)」は、３番目の「Tel（連絡先）」を配列Dataに代入します。
３６５行目「Data(f.pw) = buf(0, 3)」は、４番目の「Pw（パスワード）」を配列Dataに代入します。
３６６行目「Data(f.YoyakuD) = buf(0, 4)」は、５番目の「YoyakuD（予約日時）」を配列Dataに代入します。
３６７行目「Data(f.KasidasiD) = buf(0, 5)」は、６番目の「KasidasiD（貸出日時）」を配列Dataに代入します。
３６８行目「Data(f.HenkyakuD) = buf(0, 6)」は、７番目の「HenkyakuD（返却日時）」を配列Dataに代入します。

以上により、配列Dataに全ての情報が格納されたので、フォーム内での表示にはこの配列Dataを極力使う事で、SQL文の都度の発行を防止できます。

８－２－４．ページの切替

マルチページは「タブを選択することで、どのページでも表示可能」なものです。それが本来の使い方かと思いますが、しかし今回のように「セル選択した場所により、各ページの表示が異なるシステム」では、どのページを選択されても大丈夫なように全ページの準備を整えておく必要があります。
ですので今回はユーザーのマウス操作と選択したセル値により「開くページを１ページに固定」することで、１つのページだけ準備をすれば良いように、と考えました。図８－１３では、その処理をしています。

引数pとして、開くページ（Pagesのインデックスはゼロ始まり）を受け取り、そのページのみを操作可能にします。

372. '==========　⇩(19)　ページの切替　============
373. Private Sub PageEnable(p As Integer)
374. 　Dim i As Integer 　　 '←頁数
376. 　For i = 0 To Me.MultiPage1.Pages.Count - 1
378. 　　If i = p Then
379. 　　　Me.MultiPage1.Pages(i).Enabled = True
380. 　　Else
381. 　　　Me.MultiPage1.Pages(i).Enabled = False
382. 　　End If
384. 　Next i
386. 　Me.MultiPage1.Value = p
388. End Sub

図８－１３

まず、マルチページの表示を操作するプロパティとして、今回は下記の２種類を使用しています。 　・Enabledプロパティ：指定のPagesを有効にするか否か（True＝有効False＝無効） 　・Valueプロパティ：MultiPageコントロールの中で、何ページ（ゼロ始まりのインデックス）をアクティブにするか EnabledをFalseに設定すると、そのページは無効になり選択不可能になります。またValueプロパティは複数ページが有効になっている場合、その中からアクティブページを選択できます。 今回システムでは、４ページの内１ページだけを有効にします。普通に考えれば、１ページだけを有効にし残りを無効にすれば、有効にしたページが表示されそうです。 しかし、有効・無効の設定の順番によっては、うまく行かない時があります。全ページを一旦無効にした後、１ページだけ有効にすると、有効にしたページが表示されない（真っ白な状態）のです。Repaintメソッドを実行しても表示されないことから「実際には表示されている」状態でも無さそうです。 なお全ページを無効にせず、少なくとも１ページだけは有効状態を保ちながら有効ページを指定すれば、このような表示されない状態は発生しません。 しかし有効にしたページが表示されない状態でも、「Valueプロパティで、どこかのページをアクティブにする」と表示されます。アクティブにするのは有効ページで無くてもOKです。 表示されない理由が把握できず、うまく説明できませんが、今回は「指定するページのみを有効」にした後「有効ページをアクティブ」にすることにしました。アクティブにするのはどこのページでもOKですが、有効ページの方が感覚的に合うからです。

３７６行目「For i = 0 To Me.MultiPage1.Pages.Count - 1」では、カウンタ変数ｉをページの数だけ回します。
３７８行目「If i = p Then」では、処理するページが、引数として受け取った「表示するページ（p）」である場合に、３７９行目「Me.MultiPage1.Pages(i).Enabled = True」で、表示するページを有効にします。
それ以外のページは３８１行目「Me.MultiPage1.Pages(i).Enabled = False」で無効にします。

ページの有効・無効が完了した後、３８６行目「Me.MultiPage1.Value = p」で「有効にしたページをアクティブ」にします。

８－２－５．予約済み備品リストの作成

「貸出／返却ページ」「確認ページ」「変更ページ」には、それぞれ「予約済みの備品リスト」を確認するためのListBoxを設けています。ページは違ってもリストの中身は一緒ですので、リスト作成のプロシージャを外置きにしたのが図８－１４です。
引数として、リストを表示するLisbBoxを受取ります。受け取る際のデータ型はControl型です。「LBox As ListBox」では「データ型が違う」というエラーが発生します。

390. '==========　⇩(20)　備品リスト作成　============
391. Public Sub makeList(LBox As Control)
392. 　Dim i As Integer 　　 '←予約した備品の数量
393. 　Dim j As Integer 　　 '←備品の全数量
394. 　Dim k As Long 　　　 '←ListBoxの行位置
396. 　LBox.Clear
398. 　k = 0
399. 　For i = 0 To UBound(Data(f.MngNo), 1) - 1
400. 　　For j = 1 To UBound(Equips, 1)
401. 　　　If Data(f.MngNo)(i + 1) = CStr(Equips(j, 1)) Then
402. 　　　　LBox.AddItem ""
403. 　　　　LBox.List(k, 0) = Equips(j, 1)
404. 　　　　LBox.List(k, 1) = Equips(j, 2)
405. 　　　　LBox.List(k, 2) = Equips(j, 3)
406. 　　　　k = k + 1
407. 　　　　Exit For
408. 　　　End If
409. 　　Next j
410. 　Next i
412. End Sub

図８－１４

まず３９６行目「LBox.Clear」で、リストボックスをクリアします。

３９８行目「k = 0」で、リストボックスの行位置を指定します。「あれ？『カウンタ変数ｉ＝リストボックスの行位置』なのに、わざわざ『k』という変数を何故使うのか」と思われた方もいるかもしれません。実は私も、最初はそうしていました。
しかし運用の途中で「備品Noを変更」してしまうと、「Table1からは古い備品Noのデータも抽出される」のに「最新の備品リスト（Equips）には、その備品Noが無い」現象が発生するのです。そうなるとリストボックスの行位置とカウンタ変数ｉにズレが生じてしまうのでエラーが発生します。
ですので、リストボックスの行位置を示す別の変数Kを設け、ズレが生じても大丈夫なようにしています。（但し、変更された備品Noのデータは、二度とリストボックスに表示されないことになります。）

３９９行目「For i = 0 To UBound(Data(f.MngNo), 1) - 1」では、カウンタ変数ｉを予約済みの備品数だけ回します。
４００行目「For j = 1 To UBound(Equips, 1)」は、備品リストの総数だけカウンタ変数ｊを回します。
４０１行目「If Data(f.MngNo)(i + 1) = CStr(Equips(j, 1)) Then」では、予約済み備品の備品Noと備品リストの備品Noが合致した時には４０２～４０７行目を実行します。

４０２行目「LBox.AddItem ""」では、今回３列のリストなので、まずリストボックスに新しい行を作成します。
４０３行目「LBox.List(k, 0) = Equips(j, 1)」で、新しく作ったリスト行の１列目に備品Noを入れます。
４０４行目「LBox.List(k, 1) = Equips(j, 2)」で２列目に備品名称を入れます。
４０５行目「LBox.List(k, 2) = Equips(j, 3)」で３列目に備品の仕様を入れます。
４０６行目「k = k + 1」で、リストの行位置を１つ増やします。
４０７行目「Exit For」では、もうこれ以上For j～Nextを回しても、４０１行目はヒットしないため、For j～Nextを抜け出し、次のカウンタ変数ｉに移行します。

８－２－６．予約ページ（ページ１）の処理

８－２－６－１．ページ１の設定

UFstartプロシージャ（図８－８）の３２８行目から呼び出されるのが、予約ページを作成する図８－１５です。

414. '==========　⇩(21)　ページ１の設定　============
415. Private Sub Yoyaku_ini()
417. 　Call PageEnable(0)
419. 　Me.Label101.Caption = Data(f.ClickStartD)
420. 　Me.Label102.Caption = Data(f.ClickEndD)
421. 　Me.Label103.Caption = "未定"
423. 　Call EnabledEquipList(Equips)
424. End Sub

図８－１５

４１７行目「Call PageEnable(0)」では、図８－１３を呼び出し、引数のページ「インデックスがゼロ始まりなので、予約ページ（ページ１ ＝ Pages(0) ）」のみを有効な状態にします。

４１９行目「Me.Label101.Caption = Data(f.ClickStartD)」では、図８－８の３１７行目で配列Dataに代入された「選択したセル範囲の左端日付」を、フォーム上の貸出日のCaptionに設定します。
４２０行目「Me.Label102.Caption = Data(f.ClickEndD)」では、図８－８の３１８行目で配列Dataに代入された「選択したセル範囲の右端日付」を、フォーム上の返却日のCaptionに設定します。
４２１行目「Me.Label103.Caption = "未定"」では、予約を行う前の状態として「予約番号」欄を未定とします。

４２３行目「Call EnabledEquipList(Equips)」では図８－１６を呼び出し、「ユーザーが選択した日程範囲で、貸出可能な備品のリスト」を作成します。引数には、図７－３の１１１行目で作成した「備品リスト（配列Equips）」を渡します。

配列EquipsはPublicとして宣言していますので、図８－１６のEnabledEquipListプロシージャの中でも、もちろん参照できます。引数で渡すのには、もちろん理由があります。
貸出可能備品リストの作成工程で使いたいリストは「備品リスト≒貸出可能備品リスト」ですので、備品リストをコピーして使いたいのですが、配列をFor～Nextを二重にしてコピーするのは面倒です。
そこで、呼出し元で配列Equipsを引数として渡し、受け取る側で「ByValとして受け取る」ことで「配列Equipsのコピーを作り、貸出可能なリスト『配列EnabledEquips』をプロシージャ内で使用」することが出来るのです。

８－２－６－２．貸出可能備品リストの作成

図８－１５の４２３行目から呼び出されるのが図８－１６です。引数としてPublic配列変数のEquips（備品リスト）を「ByVal（値渡し）」として受け取ります。値渡しで受け取ると、その引数にプロシージャ内で変更を与えたとしても「元のデータへは変更を与えない」ことが出来ます。

426. '==========　⇩(22)　貸出可能備品リストの作成　============
427. Private Sub EnabledEquipList(ByVal EnabledEquips As Variant)
428. 　Dim i As Long 　　　　 '←備品の総数
429. 　Dim j As Long 　　　　 '←使用中のデータ数,未使用のデータ数
430. 　Dim sql As String 　　 '←SQL文
431. 　Dim buf As Variant 　　 '←SQLにより抽出されたデータ配列
433. 　sql = "Select MngNo from " & Table1 & _
434. 　　　　" where DateValue(startDay) <= #" & Data(f.ClickEndD) & "#" & _
435. 　　　　" and DateValue(EndDay) >= #" & Data(f.ClickStartD) & "#" & _
436. 　　　　" and del is null " & _
437. 　　　　" group by MngNo "
439. 　buf = SQL_exec4(sql)
441. 　If IsEmpty(buf(0, 0)) = False Then
442. 　　For i = 1 To UBound(EnabledEquips, 1)
443. 　　　For j = 0 To UBound(buf, 1)
444. 　　　　If CStr(EnabledEquips(i, 1)) = buf(j, 0) Then
445. 　　　　　EnabledEquips(i, 1) = ""
446. 　　　　　Exit For
447. 　　　　End If
448. 　　　Next j
449. 　　Next i
450. 　End If
452. 　Me.ListBox101.Clear
454. 　j = 0
455. 　For i = 1 To UBound(EnabledEquips, 1)
456. 　　If Not EnabledEquips(i, 1) = "" Then
457. 　　　Me.ListBox101.AddItem ""
458. 　　　Me.ListBox101.List(j, 0) = EnabledEquips(i, 1)
459. 　　　Me.ListBox101.List(j, 1) = EnabledEquips(i, 2)
460. 　　　Me.ListBox101.List(j, 2) = EnabledEquips(i, 3)
461. 　　　j = j + 1
462. 　　End If
463. 　Next i
465. End Sub

図８－１６

４３３～４３７行目のSQL文では、ユーザーが選択したセル範囲の期間内に一部でも含まれる予約等の備品Noを抽出しています。
４３３行目「sql = "Select MngNo from " & Table1 & _」は、Table1（Sheet1）から、備品Noを抽出します。
４３４行目「" where DateValue(startDay) <= #" & Data(f.ClickEndD) & "#" & _」と４３５行目「" and DateValue(EndDay) >= #" & Data(f.ClickStartD) & "#" & _」で、期間内に含まれるデータを絞り込んでいます。これは、図７－１０の考え方と一緒です。
４３６行目「" and del is null " & _」は、予約取消をしたデータ（del列に取消日時が入力されている）は、対象外としています。
４３７行目「" group by MngNo "」は、備品Noでデータをまとめています。これは図８－１７のように、検索する範囲内に「備品Noが同一のデータ（もちろん予約番号は異なる）」がある場合は、備品Noを１つにまとめているのです。つまり図８－１７で言えば「指定した期間内では、備品Noが202のものは貸出できない」「貸出出来ない備品Noの情報が2つであっても1つであっても、貸出できない事には変わり無い」のです。

図８－１７

４３９行目「buf = SQL_exec4(sql)」では、上記のSQL文を実行し、その抽出データを変数bufに代入します。
４４１行目「If IsEmpty(buf(0, 0)) = False Then」ではデータが１行以上抽出された（式では、抽出データが空では無い　という意味）場合に、４４２～４４９行目を実行します。

４４２行目「For i = 1 To UBound(EnabledEquips, 1)」は、カウンタ変数ｉを備品リストの数だけ回します。使用している配列EnabledEquipsは、配列Equipsのコピーなのでサイズは一緒です。
４４３行目「For j = 0 To UBound(buf, 1)」では、カウンタ変数ｊを抽出行数の分だけ回します。bufのインデックスはゼロ始まりです。
４４４行目「If CStr(EnabledEquips(i, 1)) = buf(j, 0) Then」は、備品リストの備品Noと抽出データの備品Noを比較し、合致した時には４４５行目「EnabledEquips(i, 1) = ""」で「備品リストの備品Noを削除」します。
なお４４４行目で、備品NoをCStr関数で文字列に変換しているのは、SQLで抽出したデータが文字列のため、データ型を合わせているためです。
備品Noは重複が無い事になっています。一度４４４行目が引っ掛かってしまえば、それ以降For j～Nextを回し続けても引っ掛かりません。ですので無駄を省くために４４６行目「Exit For」で、４４３～４４８行目のFor j～Nextを抜け、次のFor i～Nextに移ります。

４４１～４５０行目の二重For～Nextを抜けた時、配列EnabledEquipsの備品No列には「指定した期間には、予約等が重なっていない備品No」が残されています。このデータを使って、４５４～４６３行目で「貸出可能な備品リスト」を作成します。
まず、４５２行目「Me.ListBox101.Clear」で、リストボックスをクリアします。

４５４行目「j = 0」は、リストボックスの行位置（ゼロ始まり）を示します。
４５５行目「For i = 1 To UBound(EnabledEquips, 1)」は、カウンタ変数ｉを備品リストの数だけ回します。
４５６行目「If Not EnabledEquips(i, 1) = "" Then」では、備品Noが存在する場合に４５７～４６１行目を実行します。４４１～４５０行目で「日程が重なっている備品Noは、空にした」ため、残されたのが「貸出可能な備品」になるためです。

４５７行目「Me.ListBox101.AddItem ""」で、リストボックスに新しい行を追加します。今回リストボックスは３列仕様にしていますので、まずは新しい行を起こしてから、各列に値を入れる必要があります。
４５８行目「Me.ListBox101.List(j, 0) = EnabledEquips(i, 1)」で、リストボックス１列目に備品リストの１列目（備品No）を書き込みます。
４５９行目「Me.ListBox101.List(j, 1) = EnabledEquips(i, 2)」で、リストボックス２列目に備品リストの２列目（備品名）を書き込みます。
４６０行目「Me.ListBox101.List(j, 2) = EnabledEquips(i, 3)」で、リストボックス１列目に備品リストの３列目（仕様）を書き込みます。
４６１行目「j = j + 1」で、リストボックスの行位置を１つ増やします。

８－２－６－３．予約実行

「予約」ボタンをクリックした時に呼び出されるのが、図８－１９です。予約実行の流れは、図８－１８のようになります。

図８－１８

467. '==========　⇩(23)　予約実行　============
468. Private Sub CommandButton101_Click()
469. 　Dim i As Integer 　　　　　 '選択可能な備品数(リスト総数)
470. 　Dim j As Integer 　　　　　 '選択した備品の個数
471. 　Dim newLno As Variant 　　 '←新･予約No
472. 　Dim buf As Variant 　　　　 '←Table2へのInsert文が成功したか否か
473. 　Dim sql As String 　　　　　 '←SQL文
475. 　If Trim(Me.TextBox101) = "" Or Trim(Me.TextBox102) = "" _
476. 　　　Or Trim(Me.TextBox103) = "" Or Trim(Me.TextBox104) = "" Then
477. 　　MsgBox "項目が未入力です"
478. 　　Exit Sub
479. 　End If
481. 　sql = "Select IIF(Isnull(max(val(Lno))), 0, max(val(Lno))) +1 from " & Table1
482. 　newLno = SQL_exec4(sql)
484. 　j = 0
485. 　For i = 0 To Me.ListBox101.ListCount - 1
486. 　　If Me.ListBox101.Selected(i) = True Then
488. 　　　sql = "Select count(*) from " & Table1 & _
489. 　　　　　　" where MngNo='" & Me.ListBox101.List(i, 0) & "'" & _
490. 　　　　　　" and DateValue(startDay) <= #" & Data(f.ClickEndD) & "# " & _
491. 　　　　　　" and DateValue(EndDay) >= #" & Data(f.ClickStartD) & "# " & _
492. 　　　　　　" and del is null "
493. 　　　buf = SQL_exec4(sql)
495. 　　　If Not buf(0, 0) = 0 Then GoTo ErrStep
497. 　　　sql = "Insert into " & Table1 & "(Lno,MngNo,StartDay,EndDay,Status) values (" & _
498. 　　　　　　newLno(0, 0) & "," & _
499. 　　　　　　Me.ListBox101.List(i, 0) & "," & _
500. 　　　　　　"'" & Data(f.ClickStartD) & "'," & _
501. 　　　　　　"'" & Data(f.ClickEndD) & "'," & _
502. 　　　　　　"'Y'" & ")"
503. 　　　buf = SQL_exec4(sql, False)
505. 　　　If IsEmpty(buf) = False Then GoTo ErrStep
506. 　　　j = j + 1
507. 　　End If
508. 　Next i
510. 　If j = 0 Then
511. 　　MsgBox "リストを選択した後、予約実行ボタンをクリックして下さい"
512. 　　Exit Sub
513. 　End If
515. 　sql = "Insert into " & Table2 & "(Lno,Lname,Dept,Tel,Pw,YoyakuD) values (" & _
516. 　　　　newLno(0, 0) & "," & _
517. 　　　　"'" & Me.TextBox101.Text & "'," & _
518. 　　　　"'" & Me.TextBox102.Text & "'," & _
519. 　　　　"'" & Me.TextBox103.Text & "'," & _
520. 　　　　"'" & Me.TextBox104.Text & "'," & _
521. 　　　　"'" & Now & "')"
522. 　buf = SQL_exec4(sql, False)
524. 　If IsEmpty(buf) = False Then GoTo ErrStep
526. 　Me.Label103.Caption = newLno(0, 0)
527. 　Call EnabledEquipList(Equips) 　　　　 '/// リストを再作成
528. 　Call makeGantt
530. 　Exit Sub
532. ErrStep: 　　　　 '←SQLでエラーが出ると、Table1とTable2でデータ整合性が崩れるので揃える
534. 　sql = "Update " & Table1 & " set Del='" & Date & "'" & _
535. 　　　　" where val(Lno)=" & newLno(0, 0)
536. 　buf = SQL_exec4(sql, False)
538. 　If IsEmpty(buf) = False Then
539. 　　MsgBox "エラー修正処理に失敗しました。データの整合性が取れていない可能性があります" & _
540. 　　　　　　vbNewLine & "管理者まで連絡下さい。"
541. 　Else
542. 　　MsgBox "処理に失敗しました。他の人と重複している可能性もあります。" & _
543. 　　　　　　vbNewLine & "データは保存されていません"
544. 　End If
546. End Sub

図８－１９

４７５～４７９行目では、ユーザー入力欄（今回は、氏名・部署・連絡先・パスワード）の４項目をチェックをしています。
４７５～４７６行目「If Trim(Me.TextBox101) = "" Or Trim(Me.TextBox102) = "" Or Trim(Me.TextBox103) = "" Or Trim(Me.TextBox104) = "" Then」で、どれかに未入力があった場合は４７７～４７８行目を実行します。
４７７行目「MsgBox "項目が未入力です"」でコメントを出し、４７８行目「Exit Sub」でプロシージャを抜けます。
つまり、入力欄に１つでも未入力があった場合には、コメントが出るだけで予約処理は行われません。

４８１行目「sql = "Select IIF(IsNull(max(Val(Lno))), 0, max(Val(Lno))) +1 from " & Table1」は、Table1のLno列から最大値を求め、それに＋１をした値を返してくれるSQL文です。
「よりみち」でも説明しましたが、ここではSQLには無い関数「IIF」「IsNull」「Val」を使っています。機能はExcel関数と同じです。「max」関数はSQL側にもExcel側にも有り同じ機能なので、どちらの機能で動いているかは分かりません。
また「sql = "Select IIF(max(val(Lno)) IS NULL, 0, max(val(Lno))) +1 from " & Table1」としてもOKです。

なおTable2にもLno列がありますので、そちらから最大値を取る方法も考えられますが、あまりおすすめできません。
それは「先に予約されてしまった」または「データ追加時にエラーが出て、追加できなかった」ときに、５３４～５３６行目で「Table1に書き込んでしまったデータに対してDel行に削除日時を入力」しているからです。逆に言うと「Table2にデータを追加する前」に、失敗した「Table1のデータを削除」していますので「Table1には存在する新しい予約番号（削除済み）が、Table2には無い」のです
もしTable2側で新・予約番号を取得すると、Table1の中に同じ予約番号なのに「削除済みデータと死んでいるデータが混在」することになってしまうのです。混在してもたぶん大丈夫そうですが、データを見返した時には訳がわからなくなります。

このSQL分を４８２行目「newLno = SQL_exec4(sql)」で実行し、その抽出結果（Table2のLno列の最大値に＋１した値）を変数newLnoに代入します。

４８４～５０８行目では、貸出可能備品リストの各行がユーザーによって選択されているか否かを調べ、選択されている行について「Table1にデータを追加」しています。
まず４８４行目「j = 0」は、何行選択されていたかを数えるためのカウンタ変数の初期化です。
４８５行目「For i = 0 To Me.ListBox101.ListCount - 1」では、貸出可能備品リスト（ListBox101）をカウンタ変数ｉでリストの行数分だけ回します。
４８６行目「If Me.ListBox101.Selected(i) = True Then」では、そのリスト行が選択されていたら、４８８～５０６行目を実行します。

４８８～４９２行目は、図８－１６の４３３～４３７行目のSQL文と似ていて「指定した期間」に「登録しようとしている備品Noが既に登録」されている「データ個数」を求めるSQL文です。貸出可能備品リストから選択したのだから「空いているはず」なのですが、それは「貸出可能備品リストを表示した時点」での話です。あなたとほぼ同時に検索をし、一瞬早く予約してしまったかもしれませんので「本当に空いているのか」を再確認をしているのです。

４８８行目「sql = "Select count(*) from " & Table1 & _」は、Table1から抽出したデータの個数を求めています。
４８９行目「" where MngNo='" & Me.ListBox101.List(i, 0) & "'" & _」は、MngNo（備品No）が「選択したリスト行の備品No」であるデータに絞り込みます。
４９０行目「" and DateValue(startDay) <= #" & Data(f.ClickEndD) & "# " & _」と４９１行目「" and DateValue(EndDay) >= #" & Data(f.ClickStartD) & "# " & _」で、期間を絞りこんでいます。
４９２行目「" and del is null "」で、予約取消をしていないデータに絞り込んでいます。

４９３行目「buf = SQL_exec4(sql)」で、上記SQL文を実行し、その抽出結果を変数bufに代入します。
buf(0, 0)には「抽出された行数が入っています（抽出されなければゼロ）」ので、４９５行目「If Not buf(0, 0) = 0 Then GoTo ErrStep」で、ゼロでは無い（＝データを追加したい期間に既にデータが入っている）場合は、ラベル「ErrStep（５３２行目）」へスキップし、「既に新項目として追加してしまったデータを削除（＝Del列に削除日時を入力）」します。

４９７～５０２行目は、Table1にデータを追加するSQL文です。
４９７行目「sql = "Insert into " & Table1 & "(Lno,MngNo,StartDay,EndDay,Status) values (" & _」は、Table1の「Lno,MngNo,StartDay,EndDay,Status」の列にデータを追加します。追加するデータはvaluesの後のカッコ内に並べます。
４９８行目「newLno(0, 0) & "," & _」は、１番目のLno列のデータです。４８２行目で得た変数newLnoの値を入れます。
４９９行目「Me.ListBox101.List(i, 0) & "," & _」は、２番目のMngNo列のデータです。貸出可能備品リストの選択行の備品Noを入れます。
５００行目「"'" & Data(f.ClickStartD) & "'," & _」は、３番目のStartDay列のデータです。選択しているセル範囲の左端の日付を入れます。
５０１行目「"'" & Data(f.ClickEndD) & "'," & _」は、４番目のEndDay列のデータです。選択しているセル範囲の右端の日付を入れます。
５０２行目「"'Y'" & ")"」は、５番目のStatus列のデータです。予約実行の場面なので、予約である「Y」の文字列を入れます。

５０３行目「buf = SQL_exec4(sql, False)」で、上記のSQL文を実行します。実行するのはInsert文なので戻り値は無いのですが、SQL_exec4関数プロシージャでは「SQL実行処理が成功したらEmpty、失敗した時はFalseを返す」ようにしています。
ですので ５０５行目「If IsEmpty(buf) = False Then GoTo ErrStep」でEmptyでは無い（＝実行処理失敗）ときには、ラベル「ErrStep（５３２行目）」へスキップし、「既に新項目として追加してしまったデータを削除（＝Del列に削除日時を入力）」します。

５０６行目「j = j + 1」で、何行選択されていたかを数えるカウンタ変数ｊに＋１をし、リストボックスの次の行の処理に移動します。

全てのリスト行についての処理が完了した後、５１０行目「If j = 0 Then」で、カウンタ変数ｊ（何行選択されていたか）の値を調べます。そのｊがゼロだった場合には、５１１～５１２行目を実行します。
ｊは４８４行目「j = 0」で初期化された後、「リストボックスが選択状態の時」だけ５０６行目「j = j + 1」で＋１ずつ値を増やします。ですので、「If j = 0 Then」が成立するのは「リストボックスの選択状態が１行も無かった」時となります。
選択状態が１行も無かった時は、５１１行目「MsgBox "リストを選択した後、予約実行ボタンをクリックして下さい"」でコメントを出し、５１２行目「Exit Sub」で予約処理を中止します。

５１５～５２１行目のSQL文は、Table2側にデータを追加するものです。
５１５行目「sql = "Insert into " & Table2 & "(Lno,Lname,Dept,Tel,Pw,YoyakuD) values (" & _」は、Table2の「Lno,Lname,Dept,Tel,Pw,YoyakuD」の列にデータを追加します。追加するデータはvaluesの後のカッコ内に並べます。
５１６行目「newLno(0, 0) & "," & _」は１番目のLno列のデータです。４８２行目で得た変数newLnoの値を入れます。
５１７行目「"'" & Me.TextBox101.Text & "'," & _」は２番目のLname列のデータです。氏名のテキストボックスに入力された値を入れます。
５１８行目「"'" & Me.TextBox102.Text & "'," & _」は３番目のDept列のデータです。部署のテキストボックスに入力された値を入れます。
５１９行目「"'" & Me.TextBox103.Text & "'," & _」は４番目のTel列のデータです。連絡先のテキストボックスに入力された値を入れます。
５２０行目「"'" & Me.TextBox104.Text & "'," & _」は５番目のPw列のデータです。パスワードのテキストボックスに入力された値を入れます。
５２１行目「"'" & Now & "')"」は６番目のYoyakuD列のデータです。ここは予約の処理ですので、予約日時として今の日時を入力します。

５２２行目「buf = SQL_exec4(sql, False)」で上記SQL文を実行し、処理結果（SQL実行処理が成功したらEmpty、失敗したらFalse）がbufに戻ります。
その戻り値を５２４行目「If IsEmpty(buf) = False Then GoTo ErrStep」で調べ、Emptyでは無い（＝実行処理失敗）ときには、ラベル「ErrStep（５３２行目）」へスキップし、「既に新項目として追加してしまったデータを削除（＝Del列に削除日時を入力）」します。

予約処理完了後、５２６行目「Me.Label103.Caption = newLno(0, 0)」でフォーム上に「今回取得した予約番号」を表示します。
５２７行目「Call EnabledEquipList(Equips)」では、フォーム上の貸出可能備品リストを再作成します。これは「予約処理が完了したので、今回予約した備品は、現在セル選択している期間では借用できない」ため、リストを更新します。
５２８行目「Call makeGantt」では、操作シートの日程エリアを再描画し、今回予約したデータを反映させています。

５３０行目「Exit Sub」で、このプロシージャを抜けます。これはSQLエラー処理を５３２行目以降で行っているため、そのままだと「全ての処理がエラー処理を実行してしまう」のを避けるためです。

５３２行目「ErrStep:」は、いわゆるエラー処理です。４９５行目・５０５行目・５２４行目からスキップしてくる可能性があります。目的は「新規にデータを追加したが、途中でエラーが出たため、追加しなかった事にする」ものです。
一般のデータベースではCommitやRollbackで整合性を確保できるのですが、Excelシートをデータベースとして使用する場合は、１つ１つのSQL文実行で書込みが確定してしまうようなので、このような仕組みを考えました。

どんな流れになるかを図８－２０で説明します。今回システムの予約で、貸出可能備品リストから２つを選択した上で予約を実行したところを表しています。
リストから２つを選択しているので、全部で５箇所のチェックが存在することになります。そして、エラー処理「ErrStep」にスキップしたときに、新たなデータ行が追加されている状態（図８－２０の下の表の〇印）なのか、または追加が失敗した状態（×印）なのかを表で表しています。

図８－２０
今回の例では、成功すればTable1に２行、Table2に１行の新規データ行が追加されるのですが、
　・判断１：まだ、どちらのテーブルにもデータは追加されていない。
　・判断２：Table1に追加しようとしたが失敗したため、データは追加されていない。
　・判断３：Table1に１行追加されている。
　・判断４：Table1に２番目のデータを追加しようとしたが、失敗したためTable1に１行追加されている状態。
　・判断５：Table2に追加しようとしたが失敗したため、Table2には追加されていない。Table1には２行追加済み。
となります。つまり、新たな予約番号で作られた「Table1のデータを削除相当」にすれば良いことになります。

これをSQL文にしたのが５３４～５３５行目です。
５３４行目「sql = "Update " & Table1 & " set Del='" & Date & "'" & _」で、Table1のDel列に今の日時を書込みます。
５３５行目「" where val(Lno)=" & newLno(0, 0)」では、予約番号（Lno)がInsert文に使用した変数newLnoの値のデータ行に絞りこんでいます。
５３６行目「buf = SQL_exec4(sql, False)」で、上記SQL文を実行し、ここでもSQLの実行が失敗したらFalseが返ってきます。

５３６行目の戻り値bufがEmptyで無い（＝エラー処理が失敗）時には、Table1に消さなければいけないデータ行がそのまま残ってしまった可能性があります（図８－２０の判断３～判断５）ので、５３９～５４０行目「MsgBox "エラー修正処理に失敗しました。データの整合性が取れていない可能性があります" & vbNewLine & "管理者まで連絡下さい。"」とコメントを出します。
またエラー処理が成功した時は、５４２～５４３行目「MsgBox "処理に失敗しました。他の人と重複している可能性もあります。" & vbNewLine & "データは保存されていません"」とコメントを出します。
各コメントはもう少し詳しく書いても良いかもしれません。

なお図８－２０の判断１～２の場合は、削除すべき行が無いために「Updateする行が抽出されません」が、Updateで書き換えた行が無いからと言ってエラーが出る訳ではありません。試しでやってみてもbufにはEmptyが返ってきて「正常に実行された」ことになります。

サンプルファイルを操作してみて気付かれたと思いますが、InsertやUpdateのSQL文（予約の実行、貸出／返却処理、予約取消・日程変更処理）をシステム起動後初回に実行させると、非常に時間がかかります（私のPCで約12秒）。 色々調べたのですが、データベースに接続するために必要な時間のようで、ある程度仕方がないようです。 しかし「Select文の実行（日程エリアの表示など）には初回でも二回目でも時間は不変」であることから、単純な接続手続きの問題でもなさそうです。 そうは言ってもユーザーへの負担軽減は必要と考え、システム起動時に「データベースに対し、一旦Connection.Open～Close」をする方法を試してみましたが効果は見えませんでした（Select文実行では時間が掛からない事からも、ある程度頷けます）。 それならばと、システム起動時に「テーブルに意味の無いUpdate文を実行」してみるという「プレ実行」を試してみました。この方法だと、ユーザーにしてみれば「システムの起動に必要な処理」と思ってくれそうで、あまり違和感はありません。 しかし効果となると、２回目以降「必ず短い時間で処理が終わる」わけでは無く、効果がある時もあれば、やはり時間がかかる場合もあるのです。どうして100%の効果が出ないのか把握できていません。 ですので今回システムには、起動時の「プレ実行」機能は入れていません。起動後初回に実行が遅いために、実用上問題があるとは思いますが御了承ください。良い方法を見つけたら、また紹介いたします。

８－２－６－４．ユーザー入力欄のクリア

各ページでの処理が完了した際、どのページに移動するのが良いのかを色々考えたのですが、変更ページで「予約を取り消し」を実行したあと予約ページに移動するとしました。その時には「前に予約した人のパスワードが見えてしまっては困る」と思い、予約者情報入力テキストボックスを空にすることが必要だと考えました。

図８－２１は、予約ページに移動してくる時に、予約者情報入力テキストボックスを空にするものです。
内容は極簡単で、５５０～５５３行目で４つのテキストボックスを空にしています。

548. '==========　⇩(24)　入力欄のクリア　============
549. Sub Page0Erase()
550. 　Me.TextBox101.Text = ""
551. 　Me.TextBox102.Text = ""
552. 　Me.TextBox103.Text = ""
553. 　Me.TextBox104.Text = ""
554. End Sub

図８－２１

８－２－７．貸出／返却ページ（ページ２）の処理

８－２－７－１．ページ２の設定

UFstartプロシージャ（図８－８）の３２４行目から呼び出されるのが、貸出／返却処理のページを作成する図８－２２です。

556. '==========　⇩(25)　ページ２の設定　============
557. Private Sub LendRet_ini()
559. 　Call PageEnable(1)
561. 　Select Case Data(f.Status)
562. 　　Case "Y"
563. 　　　Me.CommandButton201.Caption = "貸出"
564. 　　Case "K"
565. 　　　Me.CommandButton201.Caption = "返却"
566. 　End Select
568. 　Me.Label200.Caption = Data(f.Lno)
569. 　Me.Label201.Caption = Data(f.StartDay)
570. 　Me.Label202.Caption = Data(f.EndDay)
571. 　Me.TextBox201.Value = ""
573. 　Call makeList(Me.ListBox201)
575. 　Me.Label203.Caption = Data(f.Lname)
576. 　Me.Label204.Caption = Data(f.Dept)
577. 　Me.Label205.Caption = Data(f.Tel)
578. 　Me.Label206.Caption = Data(f.YoyakuD)
579. 　Me.Label207.Caption = Data(f.KasidasiD)
581. End Sub

図８－２２

５５９行目「Call PageEnable(1)」は、図８－１３を呼び出します。引数に「１」を指定していますので、Page2（Pages(1)：貸出／返却ページ）をアクティブにしています。

５６１～５６６行目では、実行ボタン（CommandButton201）の表面文字を「貸出」または「返却」に切り替えています。
５６１行目「Select Case Data(f.Status)」で「状態」を調べ、５６２行目「Case "Y"」は、現在「Y（予約中）」の時には５６３行目「Me.CommandButton201.Caption = "貸出"」で「次の状態の『貸出』」にボタン表面文字を切り替えます。
また５６４行目「Case "K"」は、「K（貸出中）」の時には５６５行目「Me.CommandButton201.Caption = "返却"」で「次の状態の『返却』」にボタン表面文字を切り替えます。

５６８行目「Me.Label200.Caption = Data(f.Lno)」で、予約番号を表示します。
５６９行目「Me.Label201.Caption = Data(f.StartDay)」で、貸出日付を表示します。
５７０行目「Me.Label202.Caption = Data(f.EndDay)」で、返却日付を表示します。
５７１行目「Me.TextBox201.Value = ""」で、管理者パスワード入力用テキストボックスを空にします。

５７３行目「Call makeList(Me.ListBox201)」で、図８－１４を呼び出し「予約済みの備品リスト」を作ります。なお引数に「Me.ListBox201」コントロールを渡していますので、貸出／返却ページのListBox201に作成します。

５７５行目「Me.Label203.Caption = Data(f.Lname)」で、予約者名を表示します。
５７６行目「Me.Label204.Caption = Data(f.Dept)」で、部署名を表示します。
５７７行目「Me.Label205.Caption = Data(f.Tel)」で、連絡先を表示します。
５７８行目「Me.Label206.Caption = Data(f.YoyakuD)」で、予約日時を表示します。
５７９行目「Me.Label207.Caption = Data(f.KasidasiD)」で、貸出日時を表示します。なお貸出日が表示されるのは、貸出日時データが存在する「現時点が貸出中（K）」の場合であり、現在「予約中」の場合は「貸出日時は空」なので表示されません。

８－２－７－２．リストボックスの未選択化

貸出／返却ページのリストボックスは、リストのどれかを選択するものでは無く「予約済みの備品リスト」で確認するだけのものです。ですので、リストボックスを選択する必要はありません（選択できてしまうと、別の操作ができるのでは？と勘違いしてしまう可能性があり、選択できないのがベストです）。

リストボックスを選択不能にするには「Me.ListBox201.Enabled = False」という手法があります。確かに選択できませんが、リスト項目が多くある場合には「縦スクロールバーが動かず」に下の方のリスト行を確認することが出来ません。
また「Me.ListBox201.Locked = True」という手法もありますが、縦スクロールバーはやはり動きません。

今回は「リストの選択は可能だが、すぐに選択状態を外す」ことにします。そのイベントプロシージャが図８－２３です。

583. '==========　⇩(26)　リストボックスの未選択化　============
584. Private Sub ListBox201_AfterUpdate()
585. 　If Not Me.ListBox201.ListIndex = -1 Then
586. 　　Me.ListBox201.Selected(Me.ListBox201.ListIndex) = False
587. 　End If
588. End Sub

図８－２３

リストボックスのリストをマウスでクリックした場合、以下のような順序でイベントが発生します。
｢MouseMove｣→｢MouseDown｣→｢Change｣→｢Click｣→｢BeforeUpdate｣→｢AfterUpdate｣→｢MouseMove｣→｢MouseUp｣
そしてリストボックスが選択された（ListIndexが変わる）と分かるのは｢Changeイベント｣以降です。
ですのでChange以降のイベントで処理すれば「未選択状態を作る」ことが可能なはずなのですが、試してみると「Changeイベント、Clickイベントでは失敗（選択できてしまう）」することが分かりました（原因ははっきり分からない）。
うまく行ったのは「BeforeUpdate、AfterUpdate」であり、遅く反応するイベントの方が確実では　と思い、今回は「AfterUpdateイベント」を使うことにしました。

まず、選択させたく無いリストボックスのMultiSelectプロパティは「fmMultiSelectSingle（既定値）」であることが必要です。その上で、５８５行目「If Not Me.ListBox201.ListIndex = -1 Then」で、リストボックスのリスト行を選択したことを感知させます。
そして５８６行目「Me.ListBox201.Selected(Me.ListBox201.ListIndex) = False」で、その選択行を未選択状態にします。

但しこの手法でも、リストが選択状態になってしまう場面があります。 フォームの初回起動時は、今回起動表示場所を指定していないので「Excelの真ん中」で起動することになります。セルをダブルクリックすることで「貸出／返却ページ」が開くのですが、そのページの「リストボックスがちょうどダブルクリックしているセルの場所に重なる」と、リストが選択（ダブルクリックでリストを選択したことになる？）状態となってフォーム起動されるのです。 たぶんイベント発生の順序と処理の順序の関係で、このような現象になる（処理スピードの異なるPCでは試していません）と思うのですが、その選択されたリストボックスを更に操作しようとすると図８－２３のイベントが効いて、選択状態は解除されます。もう少し良い方法があるのかもしれません。

８－２－７－３．貸出／返却処理の実行

貸出／返却ページのCommandButton201をクリックした時に実行されるのが、図８－２４です。ボタン表面の文字列は、クリックしたセルの状態（Data(f.Status)の値）により「貸出」または「返却」になっています。

590. '==========　⇩(27)　貸出／返却処理の実行　============
591. Private Sub CommandButton201_Click()
592. 　Dim sql As String 　　　　　 '←SQL文
593. 　Dim NextStatus As String 　 '←次の状態(Status)
594. 　Dim Dsql As String 　　　　 '←変更日時を書き込むDBの列名
595. 　Dim buf As Variant 　　　　 '←Updateを実行した結果(Falseは失敗)
597. 　If Not Me.TextBox201.Text = KPw Then Exit Sub
599. 　Select Case Data(f.Status)
600. 　　Case "Y"
601. 　　　NextStatus = "K"
602. 　　　Dsql = "KasidasiD"
603. 　　Case "K"
604. 　　　NextStatus = "H"
605. 　　　Dsql = "HenkyakuD"
606. 　End Select
608. 　sql = "update " & Table1 & "set status= '" & NextStatus & "'" & _
609. 　　　　　　　　　" where val(Lno) = " & Data(f.Lno)
610. 　buf = SQL_exec4(sql, False)
612. 　If IsEmpty(buf) = False Then GoTo ErrStep
614. 　sql = "update " & Table2 & "set " & Dsql & "= '" & Now() & "'" & _
615. 　　　　　" where val(Lno)= " & Data(f.Lno)
616. 　buf = SQL_exec4(sql, False)
618. 　If IsEmpty(buf) = False Then GoTo ErrStep
620. 　Call makeGantt
621. 　Call makeData
622. 　Call Check_ini
624. 　Exit Sub
626. ErrStep:
628. 　sql = "update " & Table1 & "set status= '" & Data(f.Status) & "'" & _
629. 　　　　　" where val(Lno) = " & Data(f.Lno)
630. 　buf = SQL_exec4(sql, False)
632. 　If IsEmpty(buf) = False Then
633. 　　MsgBox "エラー修正処理に失敗しました。データの整合性が取れていません" & _
634. 　　　　　　vbNewLine & "管理者まで連絡下さい"
635. 　Else
636. 　　MsgBox "処理に失敗しました。データは保存されていません"
637. 　End If
638. End Sub

図８－２４

５９７行目「If Not Me.TextBox201.Text = KPw Then Exit Sub」では、まず管理者パスワードの確認を行っています。管理者パスワードは、標準モジュール図７－１の８２行目で設定されている変数KPwです。違っていれば、プロシージャを抜けます。

５９９～６０６行目では、データベースへの処理をするに当たり、以下の項目の設定しています。
　・Table1に書き込む、処理を実行した後の状態記号（K または H）・・・変数NextStatus
　・Table2に実行日時を記入する先の列名（KasidasiD または HenkyakuD）・・・変数Dsql

５９９行目「Select Case Data(f.Status)」で現在の状態（Data(f.Status)）により分岐をさせています。
６００行目「Case "Y"」は、現在の状態が予約中（Y）の時には、６０１行目「NextStatus = "K"」で「次の状態をK（貸出）」とし、６０２行目「Dsql = "KasidasiD"」で「変更日時を記入する列をKasidasiD（貸出日の列）」としています。
また６０３行目「Case "K"」は、現在の状態が貸出中（K）の時には、６０４行目「NextStatus = "H」で「次の状態をH（返却）」とし、６０５行目「Dsql = "HenkyakuD"」で「変更日時を記入する列をHenkyakuD（返却日の列）」としています。

６０８～６０９行目のSQL文は、Table1のデータを書き換えるものです。
６０８行目「sql = "update " & Table1 & "set status= '" & NextStatus & "'" & _」は、Table1のStatus列に「次の状態」を書き込みます。
６０９行目「" where val(Lno) = " & Data(f.Lno)」では、書き込む行を「Lno列が選択中の予約番号」に絞り込んでいます。
６１０行目「buf = SQL_exec4(sql, False)」では、上記SQL文を実行します。

SQL文の実行が成功した時にはSQL_exec4関数はEmptyを返し、また失敗した時にはFalseを返してきます。
その変数bufを６１２行目「If IsEmpty(buf) = False Then GoTo ErrStep」で調べ、失敗した時にはラベル「ErrStep（６２６行目）」へスキップします。

６１４～６１５行目のSQL文は、Table2のデータを書き換えるものです。
６１４行目「sql = "update " & Table2 & "set " & Dsql & "= '" & Now() & "'" & _」は、Table2の「６０２行目・６０５行目で設定した列名」に今の日時を書き込みます。
６１５行目「" where val(Lno)= " & Data(f.Lno)」では、書き込む行を「Lno列が選択中の予約番号」に絞り込んでいます。
６１６行目「buf = SQL_exec4(sql, False)」では、上記SQL文を実行します。

６１８行目「If IsEmpty(buf) = False Then GoTo ErrStep」で、SQL_exec4関数からの戻り値を調べ、失敗した時にはラベル「ErrStep（６２６行目）」へスキップします。

Table1とTable2への書込みが完了したら、６２０行目「Call makeGantt」で日程エリアを更新します。これにより、セル値の先頭文字列が書き換わり、セルの背景色が変更（赤色→青色、または青色→灰色）されます。
また６２２行目「Call Check_ini」で、「貸出／返却処理後は、確認ページに移動」させますので、その実行前の６２１行目「Call makeData」で、フォーム内で共通情報として使っている配列Dataの情報を最新状態にしています。

６２６行目以降はエラー処理ですので、正常に終了した場合は６２４行目「Exit Sub」でプロシージャを抜け出します。
エラー処理「ErrStep」にスキップしてきた時は、図８－２０と同様にTable1の方だけ修正すれば良いことになります。

６２８～６２９行目のSQL文は、書き換えてしまった（かもしれない）Table1のデータを元に戻します。
６２８行目「sql = "update " & Table1 & "set status= '" & Data(f.Status) & "'" & _」は、Table1のStatus列に「現在の状態」を書き込みます。このエラー処理にステップしてきたと言うことは、６２１行目「Call makeData」は実行されていませんので、Data(f.Status)の値は処理する前の状態を表しています。
６２９行目「" where val(Lno) = " & Data(f.Lno)」は、書き込む行を「Lno列が選択中の予約番号」に絞り込んでいます。

６３０行目「buf = SQL_exec4(sql, False)」では、元に戻すSQL文を実行し、成功した時にはEmptyが、失敗した時にはFalseが戻り変数bufに代入されます。
そのbuf値を６３２行目「If IsEmpty(buf) = False Then」で調べ、また失敗だった場合は、６３３～６３４行目「MsgBox "エラー修正処理に失敗しました。データの整合性が取れていません" & vbNewLine & "管理者まで連絡下さい"」とコメントを出します。エラー処理成功時は、６３６行目「MsgBox "処理に失敗しました。データは保存されていません"」とコメントを出します。

８－２－８．確認ページ（ページ３）の処理

ページ３は「確認ページ」です。UFstartプロシージャ（図８－８）の３２６行目から呼び出されるのが図８－２５です。

640. '==========　⇩(28)　確認ページの作成　============
641. Private Sub Check_ini()
643. 　Call PageEnable(2)
645. 　Select Case Data(f.Status)
646. 　　Case "H"
647. 　　　Me.CommandButton301.Enabled = False
648. 　　Case Else
649. 　　　Me.CommandButton301.Enabled = True
650. 　End Select
652. 　Me.Label300.Caption = Data(f.Lno)
653. 　Me.Label301.Caption = Data(f.StartDay)
654. 　Me.Label302.Caption = Data(f.EndDay)
656. 　Call makeList(Me.ListBox301)
658. 　Me.Label303.Caption = Data(f.Lname)
659. 　Me.Label304.Caption = Data(f.Dept)
660. 　Me.Label305.Caption = Data(f.Tel)
661. 　Me.Label306.Caption = Data(f.YoyakuD)
662. 　Me.Label307.Caption = Data(f.KasidasiD)
663. 　Me.Label308.Caption = Data(f.HenkyakuD)
665. End Sub

図８－２５

６４３行目「Call PageEnable(2)」は、図８－１３を呼び出します。引数に「2」を指定しているのでPages(2)（＝Page3：確認ページ）をアクティブにします。

確認ページの一番下に配置したCommandButton301は、Page4の「変更ページ」に移動するボタンです。各状態での変更可否は以下の通りです。
　・予約中（Y）：予約取消＝可能、予約の日程変更＝可能
　・貸出中（K）：予約取消＝不可、貸出日の変更＝不可（既に貸し出しているから）、返却日の変更＝可能
　・返却済（H）：予約取消＝不可、日程変更＝不可（既に完了しているから）

つまり、状態が返却済みの場合は「変更ページに行っても何も出来ない」ので変更ページに行けないようにします。
６４５行目「Select Case Data(f.Status)」で状態を調べ、６４６行目「Case "H"」で状態が返却済みの場合は、６４７行目「Me.CommandButton301.Enabled = False」でボタンを無効にします。
その他の場合（６４８行目「Case Else」）は、６４９行目「Me.CommandButton301.Enabled = True」でボタンを有効にします。

６５２行目「Me.Label300.Caption = Data(f.Lno)」では、予約番号を表示します。
６５３行目「Me.Label301.Caption = Data(f.StartDay)」では、貸出日を表示します。
６５４行目「Me.Label302.Caption = Data(f.EndDay)」では、返却日を表示します。

６５６行目「Call makeList(Me.ListBox301)」で、図８－１４を呼び出し「予約済みの備品リスト」を作ります。なお引数に「Me.ListBox301」コントロールを渡していますので、確認ページのListBox301に作成します。

６５８行目「Me.Label303.Caption = Data(f.Lname)」で、予約者名を表示します。
６５９行目「Me.Label304.Caption = Data(f.Dept)」で、予約者の部署名を表示します。
６６０行目「Me.Label305.Caption = Data(f.Tel)」で、連絡先を表示します。
６６１行目「Me.Label306.Caption = Data(f.YoyakuD)」で、予約日時を表示します。
６６２行目「Me.Label307.Caption = Data(f.KasidasiD)」で、貸出日時を表示します。
６６３行目「Me.Label308.Caption = Data(f.HenkyakuD)」で、返却日時を表示します。
なお貸出日時・返却日時が表示されるのは、それぞれ貸出中以降・返却済みの場合のみです。

貸出／返却ページのリストボックスと同様、確認ページのリストボックスも「確認するだけのリスト」のため、選択状態にさせないのが、図８－２６のAfterUpdateイベントプロシージャです。
６６９行目「If Not Me.ListBox301.ListIndex = -1 Then」で、リストボックスのリスト行を選択したことを感知させ、６７０行目「Me.ListBox301.Selected(Me.ListBox301.ListIndex) = False」で、その選択行を未選択状態にします。

667. '==========　⇩(29)　リストボックスの未選択化　============
668. Private Sub ListBox301_AfterUpdate()
669. 　If Not Me.ListBox301.ListIndex = -1 Then
670. 　　Me.ListBox301.Selected(Me.ListBox301.ListIndex) = False
671. 　End If
672. End Sub

図８－２６

確認ページの最下段のボタンをクリックした時のイベントプロシージャが図８－２７です。
６７６行目「Call Dchange_ini」で図８－２８を呼び出し、変更ページへ移動します。

674. '==========　⇩(30)　日程消去・変更ボタン　============
675. Private Sub CommandButton301_Click()
676. 　Call Dchange_ini
677. End Sub

図８－２７

８－２－９．変更ページ（ページ４）の処理

８－２－９－１．ページ４の設定

ページ４は「変更ページ」です。確認ページのボタン（図８－２７）から呼び出されるのが図８－２８です。

679. '==========　⇩(31)　ページ４の設定　============
680. Private Sub Dchange_ini()
681. 　Dim SQL As String 　　　 'SQL文
682. 　Dim SQL1 As String 　　 'SQL文の一部（予約した備品一覧）
683. 　Dim buf As Variant 　　 'SQL文の結果
685. 　Call PageEnable(3)
687. 　Select Case Data(f.Status)
688. 　　Case "Y"
689. 　　　Me.ScrollBar401.Visible = True
690. 　　　Me.CommandButton401.Enabled = True
691. 　　Case "K"
692. 　　　Me.ScrollBar401.Visible = False
693. 　　　Me.CommandButton401.Enabled = False
694. 　End Select
696. 　IsNotEvent = True
697. '//最大移動可能日
698. 　　SQL1 = "select MngNo from " & Table1 & " where val(Lno)=" & Data(f.Lno)
700. 　　SQL = "select min(DateValue(startday)) from " & Table1 & " where MngNo in (" & SQL1 & ") " & _
701. 　　　　　　" and DateValue(StartDay) > #" & Data(f.EndDay) & "# and Del is Null"
702. 　　buf = SQL_exec4(SQL)
704. 　　If buf(0, 0) = "" Then
705. 　　　Me.ScrollBar401.max = DateAdd("m", 1, Data(f.EndDay)) - DayStart
706. 　　Else
707. 　　　Me.ScrollBar401.max = DateValue(buf(0, 0)) - DayStart - 1
708. 　　End If
710. 　　Me.ScrollBar402.max = Me.ScrollBar401.max
712. '//最小移動可能日
713. 　　SQL = "select max(DateValue(endday)) from " & Table1 & " where MngNo in (" & SQL1 & ") " & _
714. 　　　　　　" and DateValue(EndDay) < #" & Data(f.StartDay) & "# and Del is Null"
715. 　　buf = SQL_exec4(SQL)
717. 　　If buf(0, 0) = "" Then
718. 　　　Me.ScrollBar401.min = DateAdd("m", -1, Data(f.StartDay)) - DayStart
719. 　　　If Me.ScrollBar401.min < 0 Then Me.ScrollBar401.min = 0
720. 　　Else
721. 　　　Me.ScrollBar401.min = DateValue(buf(0, 0)) - DayStart + 1
722. 　　End If
724. 　　Me.ScrollBar402.min = Me.ScrollBar401.min
726. 　　Me.ScrollBar401.Value = Data(f.StartDay) - DayStart
727. 　　Me.ScrollBar402.Value = Data(f.EndDay) - DayStart
729. 　IsNotEvent = False
731. 　Me.Label400.Caption = Data(f.Lno)
732. 　Me.Label401.Caption = Data(f.StartDay)
733. 　Me.Label402.Caption = Data(f.EndDay)
735. 　Me.TextBox401.Value = ""
737. 　Call makeList(Me.ListBox401)
739. 　Me.Label403.Caption = Data(f.Lname)
740. 　Me.Label404.Caption = Data(f.Dept)
741. 　Me.Label405.Caption = Data(f.Tel)
743. End Sub

図８－２８

６８５行目「Call PageEnable(3)」は、図８－１３を呼び出します。引数に「3」を指定しているのでPages(3)（＝Page4：変更ページ）をアクティブにします。

６８７～６９４行目では、変更ページ上のコントロールで、状態によっては操作してはいけないスクロールバー・ボタンの有効無効を設定しています。このページへは、状態が「予約中（Y）」または「貸出中（K）」しか入れません。その状態とコントロール類の有効無効は以下の通りです。
　・予約中（Y）：予約の取り消し＝可、貸出日の変更＝可、返却日の変更＝可
　・貸出中（K）：予約の取り消し＝不可、貸出日の変更＝不可（既に貸し出しているため）、返却日の変更＝可
そこで、貸出中は「予約取消ボタン」の使用を不可、貸出日の変更スクロールバーの使用を不可にします。

６８７行目「Select Case Data(f.Status)」で状態（Data(f.Status)）を調べ、６８８行目「Case "Y"」で「予約中（Y）」であれば、６８９行目「Me.ScrollBar401.Visible = True」で貸出日のスクロールバーを使用可にし、６９０行目「Me.CommandButton401.Enabled = True」で予約取消ボタンを使用可にします。
一方、６９１行目「Case "K"」で「貸出中（K）」であれば、６９２行目「Me.ScrollBar401.Visible = False」で貸出日のスクロールバーを使用不可にし、６９３行目「Me.CommandButton401.Enabled = False」で予約取消ボタンを使用不可にします。

なお、スクロールバーの使用可不可には「Enabledプロパティ」を使いませんでした。理由は、Enabled = Falseを使うと「スクロールバーは動かない」のに「スクロールバーの姿は『使用可』の時と同じ」で、ユーザーに「動かせるスクロールバーと認識」されてしまうために「スクロールバーの表示そのものを消す」ことにしました。

６９８～７２７行目では、日程変更のスクロールバーの設定です。変更ページの日程変更用スクロールバーは、上側の「貸出日の変更用スクロールバー（ScrollBar401）」と、下側の「返却日の変更用スクロールバー（ScrollBar402）」に分かれています。
スクロールバーのValue値は「システム初日（DayStart）をゼロとした日付」とし、Min値・Max値はそれぞれ「移動可能な限界日付」としています。Min値・Max値は２つのスクロールバーとも同じ値を設定し、初期のValue値は「現在の貸出日・返却日に設定」します。
またスクロールバーを移動した時に、もう一方のスクロールバー値を追い抜かない（貸出日と返却日を逆転させない）ようにします。

まず６９６行目「IsNotEvent = True」は、フラグ変数IsNotEventのフラグを立て、スクロールバーをマクロ側から操作してもスクロールバーのイベントプロシージャが動かないようにしています。

６９８～７１０行目は「最大移動可能日」の設定、７１３～７２４行目は「最小移動可能日」です。
「最大・最小」はあまりピンと来ない呼び名ですが、図８－２９のように「Y7（11/4～11/8で、備品No.101と201を予約）」が日程変更対象とした場合、返却日を最大限延期できるのは11/11ですので、ここを「最大移動可能日」とし、また貸出日は11/2まで前出しできるので、ここを「最小移動可能日」という呼び名にしています。

図８－２９

まず６９８行目「SQL1 = "select MngNo from " & Table1 & " where val(Lno)=" & Data(f.Lno)」では、対象予約番号の備品NoをTable1から取得するSQL文を組み立てます。このSQL文は、７００～７０１行目の「実行させるSQL文の中のSQL文（サブクエリ）」とします。
実は備品NoはData(f.MngNo)として配列の形で既に入っているのですが、７００行目の「where MngNo in (･･･)」の形で使うには、備品Noをカンマ区切りで並べてやらないといけません。「配列から１つずつ取り出して、カンマで区切って、最後のカンマは削除して・・・」というやり方もあるのですが、面倒そうなのでサブクエリとしました。

７００行目「SQL = "select min(DateValue(startday)) from " & Table1 & " where MngNo in (" & SQL1 & ") " & _」の前半はTable1から「開始日」の内で一番小さい日付を取得します。ここで「DateValue関数」を使っているのは、Table1内では日付は文字列として保存されているため、日付型に変更した上で比較（最小値を求める）するためです。
後半のWhere文は、「６９８行目のサブクエリで得られた備品No」の内のどれかにMngNoが合致するデータという事になります。

７０１行目「" and DateValue(StartDay) > #" & Data(f.EndDay) & "# and Del is Null"」は、開始日が対象データの返却日より後のデータに絞りこんでいます。「Del is Null」は予約取消をしたものを省いています。
つまり７００～７０１行目のSQL文は、図８－２９で言うと「Y4のデータ」が抽出され、その開始日の最小値＝11/12が得られるSQL文と言うことになります。
７０２行目「buf = SQL_exec4(SQL)」でそのSQL文を実行し、得られた日付が変数bufに代入されます。

但し、対象データより先には「予約が全く無い」場合も存在し、その時には空のデータが返ってきます。
７０４行目「If buf(0, 0) = "" Then」は、「先にはデータが無い」ことを表しており、その時にはとりあえず７０５行目「Me.ScrollBar401.max = DateAdd("m", 1, Data(f.EndDay)) - DayStart」で、「対象データの返却日より１ヶ月先まで返却日延長可能」としました。
７０６行目「Else」でデータが存在する時は、７０７行目「Me.ScrollBar401.max = DateValue(buf(0, 0)) - DayStart - 1」で「予約の入っているデータの内、最も早い開始日」の１日前をスクロールバーのMAX値に設定します。

７１０行目「Me.ScrollBar402.max = Me.ScrollBar401.max」では、返却日変更用のスクロールバー（ScrollBar402）のMAX値も同じ値を設定します。

７１３～７１４行目のSQL文は、対象データよりも前にあるデータの内「最も大きな終了日」を取得します。
まず７１３行目「SQL = "select max(DateValue(endday)) from " & Table1 & " where MngNo in (" & SQL1 & ") " & _」の前半は、Table1から「終了日」の内、一番大きい日付を取得します。後半のWhere文は、「６９８行目のサブクエリで得られた備品No」の内のどれかにMngNoが合致するデータという事になります。

７１４行目「" and DateValue(EndDay) < #" & Data(f.StartDay) & "# and Del is Null"」は、終了日が対象データの貸出日より前のデータに絞りこんでいます。「Del is Null」は予約取消をしたものを省いています。
つまり７１３～７１４行目のSQL文は、図８－２９で言うと「Y2のデータ」が抽出され、その終了日の最大値＝11/1が得られるSQL文と言うことになります。
７１５行目「buf = SQL_exec4(SQL)」でそのSQL文を実行し、得られた日付が変数bufに代入されます。

対象データより「前にはデータが無い」時は７１７行目「If buf(0, 0) = "" Then」で引っ掛かります。
その時にはとりあえず７１８行目「Me.ScrollBar401.min = DateAdd("m", -1, Data(f.StartDay)) - DayStart」で、「対象データの貸出日より１ヶ月前まで貸出日を前出し可能」としました。
但し、その１ヶ月前が「システム初日よりも前」だとしたら、システム初日よりも前から予約が出来てしまうことになりますので、７１９行目「If Me.ScrollBar401.min < 0 Then Me.ScrollBar401.min = 0」で、「スクロールバーのMin値をゼロ（システム初日）に設定」します。

７２０行目「Else」でデータが有った時には、７２１行目「Me.ScrollBar401.min = DateValue(buf(0, 0)) - DayStart + 1」で、スクロールバーのMin値を「最大の終了日の翌日」に設定します。
７２４行目「Me.ScrollBar402.min = Me.ScrollBar401.min」で、ScrollBar402にも同じMin値を設定します。

７２６行目「Me.ScrollBar401.Value = Data(f.StartDay) - DayStart」は、貸出日側のスクロールバーValue値を対象データの「貸出日」に設定します。
７２７行目「Me.ScrollBar402.Value = Data(f.EndDay) - DayStart」は、返却日側のスクロールバーValue値を対象データの「返却日」に設定します。
以上でスクロールバーの設定が完了したので、７２９行目「IsNotEvent = False」で「フラグ変数IsNotEvent」のフラグを降ろします。

７３１行目「Me.Label400.Caption = Data(f.Lno)」では、予約番号を表示します。
７３２行目「Me.Label401.Caption = Data(f.StartDay)」は、現状の貸出日を表示します。この値はScrollBar401を動かすことで書き換えられます。
７３３行目「Me.Label402.Caption = Data(f.EndDay)」は、現状の返却日を表示します。この値はScrollBar402を動かすことで書き換えられます。
７３５行目「Me.TextBox401.Value = ""」は、パスワード欄をクリアします。

７３７行目「Call makeList(Me.ListBox401)」で、図８－１４を呼び出し「予約済みの備品リスト」を作ります。なお引数に「Me.ListBox401」コントロールを渡していますので、変更ページのListBox401に作成します。

７３９行目「Me.Label403.Caption = Data(f.Lname)」は、予約者氏名を表示します。
７４０行目「Me.Label404.Caption = Data(f.Dept)」は、部署名を表示します。
７４１行目「Me.Label405.Caption = Data(f.Tel)」は、連絡先を表示します。

８－２－９－２．リストボックスの未選択化

確認ページのリストボックスも「確認するだけのリスト」のため、選択状態にさせないのが、図８－３０のAfterUpdateイベントプロシージャです。

745. '==========　⇩(32)　リストボックスの未選択化　============
746. Private Sub ListBox401_AfterUpdate()
747. 　If Not Me.ListBox401.ListIndex = -1 Then
748. 　　Me.ListBox401.Selected(Me.ListBox401.ListIndex) = False
749. 　End If
750. End Sub

図８－３０

７４７行目「If Not Me.ListBox401.ListIndex = -1 Then」で、リストボックスのリスト行を選択したことを感知させ、７４８行目「Me.ListBox401.Selected(Me.ListBox401.ListIndex) = False」で、その選択行を未選択状態にします。

８－２－９－３．日程変更スクロールバーの移動

貸出日／返却日の変更用スクロールバーを操作（Value値を変更）した時には、Changeイベントが発生します。
貸出日用のスクロールバーの時が図８－３１、返却日用のスクロールバーの時が図８－３２になります。

752. '==========　⇩(33)　貸出日の変更　============
753. Private Sub ScrollBar401_Change()
754. 　If IsNotEvent = True Then Exit Sub
756. 　If Me.ScrollBar401.Value > Me.ScrollBar402.Value Then
757. 　　Me.ScrollBar401.Value = Me.ScrollBar402.Value
758. 　Else
759. 　　Me.Label401.Caption = DayStart + Me.ScrollBar401.Value
760. 　End If
761. End Sub

図８－３１

７５４行目「If IsNotEvent = True Then Exit Sub」は、フラグ変数IsNotEventにフラグを立てている場合（図８－２８の６９６行目）はマクロ側からスクロールバーを設定をしている時なので、７５６～７６０行目を実行せずに終了します。

ユーザーがスクロールバーを操作した場合、まず７５６行目「If Me.ScrollBar401.Value > Me.ScrollBar402.Value Then」で「返却日変更用スクロールバーの値を追い越していないか」を調べます。
もし追い越していたら７５７行目「Me.ScrollBar401.Value = Me.ScrollBar402.Value」で、貸出日変更用スクロールバーの値を返却日変更用スクロールバーの値と同じ（＝借りるのは、１日だけとなる）にします。
７５７行目を実行することにより、スクロールバーのValue値が変わっていれば図８－３１が再帰呼び出しされ、その時は「Me.ScrollBar401.Value = Me.ScrollBar402.Value」なので７５９行目が実行され、新しい貸出日としては「返却日」と同じ日付が表示されることになります。

追い越していない場合は、７５９行目「Me.Label401.Caption = DayStart + Me.ScrollBar401.Value」で、動かした位置の日付を新しい貸出日として表示します。

763. '==========　⇩(34)　返却日の変更　============
764. Private Sub ScrollBar402_Change()
765. 　If IsNotEvent = True Then Exit Sub
767. 　If Me.ScrollBar401.Value > Me.ScrollBar402.Value Then
768. 　　Me.ScrollBar402.Value = Me.ScrollBar401.Value
769. 　Else
770. 　　Me.Label402.Caption = DayStart + Me.ScrollBar402.Value
771. 　End If
772. End Sub

図８－３２

７６５行目「If IsNotEvent = True Then Exit Sub」は、フラグ変数IsNotEventにフラグを立てている場合（図８－２８の６９６行目）はマクロ側からスクロールバーを設定をしている時なので、７６７～７７１行目を実行せずに終了します。

ユーザーがスクロールバーを操作した場合、まず７６７行目「If Me.ScrollBar401.Value > Me.ScrollBar402.Value Then」で「貸出日変更用スクロールバーの値を追い越していないか」を調べます。
もし追い越していたら７６８行目「Me.ScrollBar402.Value = Me.ScrollBar401.Value」で、返却日変更用スクロールバーの値を貸出日変更用スクロールバーの値と同じ（＝借りるのは、１日だけとなる）にします。
７６８行目を実行することにより、スクロールバーのValue値が変わっていれば図８－３２が再帰呼び出しされ、その時は「Me.ScrollBar402.Value = Me.ScrollBar401.Value」なので７７０行目が実行され、新しい返却日としては「貸出日」と同じ日付が表示されることになります。

追い越していない場合は、７７０行目「Me.Label402.Caption = DayStart + Me.ScrollBar402.Value」で、動かした位置の日付を新しい返却日として表示します。

８－２－９－４．予約取消の実行

変更ページの「予約取消」ボタンをクリックした時に呼び出されるのが図８－３３です。

774. '==========　⇩(35)　予約取消ボタン　============
775. Private Sub CommandButton401_Click()
776. 　Dim buf As Variant 　　 '←SQL実行の戻り値
777. 　Dim SQL As String 　　　 '←SQL文
779. 　If Me.TextBox401.Text = Data(f.pw) Or Me.TextBox401.Text = KPw Then
780. 　　If MsgBox("本当に取り消して良いですか?", vbYesNo) = vbNo Then Exit Sub
782. 　　SQL = "Update " & Table1 & " set Del= '" & Now() & "' where val(Lno)=" & Data(f.Lno)
783. 　　buf = SQL_exec4(SQL, False)
785. 　　If IsEmpty(buf) = False Then
786. 　　　MsgBox "処理に失敗しました。データは消去されていません"
787. 　　　Exit Sub
788. 　　End If
790. 　Else
791. 　　MsgBox "パスワードが違います。"
792. 　　Exit Sub
793. 　End If
795. 　Call makeGantt
796. 　Call Page0Erase
797. 　Call Yoyaku_ini
799. End Sub

図８－３３

７７９行目「If Me.TextBox401.Text = Data(f.pw) Or Me.TextBox401.Text = KPw Then」では、パスワード欄に入力した文字列が「予約時に登録したパスワード」または「管理者パスワード」に合致しているか否かを確認します。
合致している場合は、７８０行目「If MsgBox("本当に取り消して良いですか?", vbYesNo) = vbNo Then Exit Sub」で、本当に取り消して良いかのメッセージを出し、「いいえ」を選択すると取消処理を中止します。

取消メッセージで「はい」を選択した場合は、７８２行目「SQL = "Update " & Table1 & " set Del= '" & Now() & "' where val(Lno)=" & Data(f.Lno)」で取消のSQL文を作成します。内容は、Table1を対象データの予約番号で絞り込み、その「Del列に今の日時を書き込む」ものです。
このSQL文を７８３行目「buf = SQL_exec4(SQL, False)」で実行します。

もし処理が失敗（７８５行目「If IsEmpty(buf) = False Then」）した時には、７８６行目「MsgBox "処理に失敗しました。データは消去されていません"」でコメントを出し、７８７行目「Exit Sub」で「取消処理を中止」します。
取消処理が１件失敗しただけで、Table1とTable2の整合性が取れなくなった訳では無いので、書き戻し処理等はありません。

またパスワードが違った場合には、７９１行目「MsgBox "パスワードが違います。"」でコメントを出し、７９２行目「Exit Sub」で処理を中止します。

取消処理が完了したら、７９５行目「Call makeGantt」で日程エリアのデータを更新（この時点で、取り消したデータが消える）し、７９６行目「Call Page0Erase」で図８－２１を呼び出し「予約ページの予約者入力欄をクリア」します。
その後で７９７行目「Call Yoyaku_ini」で図８－１５を呼び出して「予約ページを表示」します。

「予約取消後に、どのページにどの状態で戻る」かは、様々な考え方があると思います。貸出／返却ページのように確認ページに戻ることも考えたのですが、予約取消の場合は「選択しているセルのデータが消える」ので、空白セルを確認ページで表示することになってしまい矛盾します。
そうなるとフォームをUnLoadしてしまうか、または予約ページに行くか　くらいしか思いつかず、予約ページに行くことにしました。但し、ユーザーの入力欄をクリアするか否かは難しく、私は「入力した内容が違っていたので、予約を取り直す」こともあるだろうと考え、入力欄をクリアしています。

８－２－９－５．予約日程変更の実行

変更ページの「日程変更決定」ボタンをクリックした時に呼び出されるのが図８－３４です。なお、貸出日／返却日変更用スクロールバーを操作せずに決定ボタンをクリックしても、実行されます。

801. '==========　⇩(36)　日程変更ボタン　============
802. Private Sub CommandButton402_Click()
803. 　Dim buf As Variant 　　 '←SQL実行の戻り値
804. 　Dim SQL As String 　　　 '←SQL文
806. 　If Me.TextBox401.Text = Data(f.pw) Or Me.TextBox401.Text = KPw Then
807. 　　SQL = "Update " & Table1 & " set StartDay= '" & Me.Label401.Caption & "'," & _
808. 　　　　　" EndDay= '" & Me.Label402.Caption & "' where val(Lno)=" & Data(f.Lno)
809. 　　buf = SQL_exec4(SQL, False)
811. 　　If IsEmpty(buf) = False Then
812. 　　　MsgBox "処理に失敗しました。データは変更されていません"
813. 　　　Exit Sub
814. 　　End If
816. 　Else
817. 　　MsgBox "パスワードが違います。"
818. 　　Exit Sub
819. 　End If
821. 　Call makeGantt
822. 　Call makeData
823. 　Call Check_ini
825. End Sub

図８－３４

８０６行目「If Me.TextBox401.Text = Data(f.pw) Or Me.TextBox401.Text = KPw Then」では、パスワード欄に入力した文字列が「予約時に登録したパスワード」または「管理者パスワード」に合致しているか否かを確認します。
合致している場合は、８０７～８０８行目で「貸出日・返却日の日付を変更するSQL文」を組み立てます。

８０７～８０８行目「SQL = "Update " & Table1 & " set StartDay= '" & Me.Label401.Caption & "'," & " EndDay= '" & Me.Label402.Caption & "' where val(Lno)=" & Data(f.Lno)」は、Table1を「"where val(Lno)=" & Data(f.Lno)」でデータを対象の予約番号に絞り込み、StartDay列をLabel401（変更した貸出日）で置き換え、EndDay列をLabel402（変更した返却日）で置き換えるSQL文です。
そのSQL文を８０９行目「buf = SQL_exec4(SQL, False)」で実行します。

実行に失敗（If IsEmpty(buf) = False Then）したら、８１２行目「MsgBox "処理に失敗しました。データは変更されていません"」でコメントを出し、８１３行目「Exit Sub」で日程変更処理を中止します。
また、パスワードが違っていた（８１６行目「Else」）時には、８１７行目「MsgBox "パスワードが違います。"」でコメントを出し、８１８行目「Exit Sub」で日程変更処理を中止します。

日程変更処理が完了したら、８２１行目「Call makeGantt」で日程エリアのデータを更新（この時点で、変更されたデータが日程エリアに反映される）します。セル選択しているデータの情報（貸出日・返却日）が変わったのですから、８２２行目「Call makeData」でフォームが保存している配列Dataを更新し、８２３行目「Call Check_ini」で「確認ページ」に移動します。

図８－３３と同様、どのページに戻るかは難しいですが、日程エリアにデータが残っているので確認ページで良いかな？と考えています。ただし日程の変更度合い（期間の移動、貸出日を遅くする、返却日を早くする）によっては、日程変更後に図８－３５のように、「日程エリアでは空白セルを選択している」のに「確認ページが表示」されている状態が生まれます。これはユーザーの操作では生まれない状態なので「ちょっと、まずいかも」とも思っています。

図８－３５

９．最後に

Excelシートをデータベースのテーブルにするシステムは、このサイトでも何例か紹介してきました。しかしOracleなどの本格データベースに比べるとデメリットも多くあります。
まず、SQLのDelete操作が出来ないために、今回のようなUpdateを使ってDel列に値を書き込んだり、データの整合性を保つための仕組みも必要だったりします。またテーブルのデータ型が指定できないので、型揃えに注意を払う必要もあります。

一方、ファイルサーバーのようなものがあれば、結構簡単に「複数人で同時に使えるシステム」が作れますし、データベース導入に必要な特別な知識も不要です。またSQL文の中でExcelの関数が使えそうだというのは今回の収穫でした。

また、フォームのコントロールとして「マルチページ」を今回初めて使いました。ページ上のLabelコントロール等はページ単位で管理できるのかと思っていたのですが、フォーム内での一括管理のようで、コントロールの名前付けが意外に面倒でした。もう少しうまく管理できないものかと思います。
ただし、フォームを起動しっぱなしで動かすシステムにとっては実に良いコントロールだ、というのが分かりました。これも今回の収穫です。


複数の備品を同時予約可能な貸出台帳（it-068.xlsm）
（サンプルファイルは、編集可能な状態で開いてください。読取専用ではエラーになります。）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。