2020/05/11

ExcelシートDBとSQLを使った会議室予約システム

• １．背景
• ２．システムの概要
• 　２－１．シート側
• 　２－２．ダイアログ側
• 　２－３．データの流れ
• ３．データを保存するファイル（データベースファイル）
• 　３－１．名簿情報
• 　３－２．会議室情報
• 　３－３．予約データ
• ４．マクロの概要
• 　４－１．起動時（準備段階）
• 　４－２．操作時
• ５．プログラム
• 　５－１．ユーザーフォーム
• 　　５－１－１．フォーム画面
• 　　５－１－２．フォームコード
• 　　　５－１－２－１．UserFormの起動時イベントプロシージャ
• 　　　５－１－２－２．UserFormの表示直後イベントプロシージャ
• 　　　５－１－２－３．「予約実行」ボタンを押した時のプロシージャ
• 　　　５－１－２－４．「修正・削除」ボタンを押した時のプロシージャ
• 　　　５－１－２－５．「本日」ボタンを押した時のプロシージャ
• 　　　５－１－２－６．リストボックスの内容を作るプロシージャ
• 　　　５－１－２－７．スクロールバー（日付）を動かした時のプロシージャ
• 　　　５－１－２－８．ダイアログ右上の×印で終了した時のプロシージャ
• 　５－２．Workbookのコード
• 　５－３．WorkSheet（Sheet1）
• 　５－４．標準モジュール
• 　　５－４－１．定数・変数宣言部
• 　　５－４－２．アプリ準備プログラム
• 　　５－４－３．データファイルの存在確認
• 　　５－４－４．ユーザー名簿の呼び出し
• 　　５－４－５．操作しているPCのユーザー名を調べる
• 　　５－４－６．日付欄の書式設定
• 　　５－４－７．会議室情報を取得し、シートに書き込み
• 　　５－４－８．時刻軸（縦軸）の消去・記入
• 　　５－４－９．予約データを呼び出す
• 　　５－４－１０．予約データを１つ１つのブロックにしてシートに表示する
• 　　５－４－１１．作業シートの予約状況記入範囲を消去
• 　　５－４－１２．会議室予約実行
• 　　５－４－１３．予約の修正・削除
• 　　５－４－１４．選択した予約が、自分が予約したものか否かを調べる
• 　　５－４－１５．予約のダブりが無いかチェックするプロシージャ
• 　　５－４－１６．アプリケーションの終了
• 　　５－４－１７．SQL文を受け取り、データベースファイルとやり取りする
• 　　５－４－１８．データベースファイルとやり取り方法２
• ６．最後に

１．背景

以前「Excelシート上にDBを作り､SQLを使ってﾃﾞｰﾀを入出力する」で、Excelシートをデータベースのテーブルとし、別なExcelからSQL文を使ってデータを操作する方法について紹介しました。
その後本サイトでの内容が「簡単なアプリ作りを通して、知識というのかヒントみたいなものを紹介する」みたいな方向に流れてきているので、「ただ手法を紹介するだけ」に留まってしまった上の項目を、何かのシステムとして活かしている状態で再紹介したいと思い、今回のアプリを考えました。
SQL ServerやAccsess、Oracleなどは敷居が高いと言う方にも、、RDB（リレーショナル・データベース）の良さをExcelで簡単に実現できる事を知って頂けると嬉しいです。

当サイトで、SQLを使用した「具体的なアプリ」としては以下のものがあります。参考にして下さい。
「ExcelシートDBとSQLを使った倉庫管理システム」
「共有資料の登録と閲覧ができるサーバーシステム」
「複数の備品を同時予約可能な貸出台帳」

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

２．システムの概要

操作画面は、図２－１のようにしました。

図２－１

２－１．シート側

日付（Ｃ２セル）単位で縦に時刻・横に複数の会議室を配置し、既に予約した会議予定が並んでいます。
会議室のセルにマウスを当てる（図２－１ではＥ３セル）と、会議室の備品・利用可能人数などがコメントとして表示されます。
会議予定は、予約者ごとに設定されている色で配色し、枠内には部署･氏名･会議名称を記入し、どの時間帯・どの会議室が空いているのかが一目で見える様にしています。
ちなみに、シートは保護されていて、ユーザーが勝手に書き換えることはできません。（書き換えても、元のデータが書き換わる訳では無いので、意味はありませんが）

２－２．ダイアログ側

操作は全てダイアログ上で行います。
まず予約希望の日に移動するために、ダイアログ内の左上のスクロールバーを動かします。日付はダイアログ上とシート上（Ｃ２セル）に表示されます。
日付が決まりましたら空いている会議室・時間帯を目視で探し、予約する時間帯をシート上で範囲選定します。範囲選定すると、ダイアログ内にも「選択した日付・会議室・開始時間と終了時間」が表示されます（ダイアログの左半分）。
（図２－１では「Ｘ２２会議室」を「2020年4月24日」の「12:00～13:00」で選択している事になります。）

ダイアログの右上リストには、あなたの名前（名簿上で、部署・氏名と使用しているＰＣのユーザー名が結びついている）が表示され、選択済みの状態になっています。

会議名称（任意：記入しなくても予約できる状態になっています）をテキストボックスに記入し、「予約実行」ボタンを押せば、データが保存（データベース登録）され、画面上でも予約済みの事が分かるようになります。
（図２－１では「予約実行」ボタンを押す前の状態を表しています）。

尚、予約を取り消したい場合及び日時変更をしたい場合は、対象の予約をシート上で選択し「修正・削除」ボタンを押す事で、データは削除されます。削除済みデータの情報はダイアログの右下に表示されますので、修正の場合はその情報を参照しながら新たに設定します。
システムを終了するには、ダイアログの右上の×印を押すことで、システム（ブック）が閉じます。

２－３．データの流れ

登録データなどを保存しているExcelファイルは、図２－２のように「共有ファイルサーバ」に置かれることを想定しています。
複数ユーザーによる同時作業が可能で、データの置いてあるサーバにアクセス（読み書き可能）できる事が前提です。

図２－２

ユーザーが操作するマクロ付きExcelファイルは、ユーザーのＰＣ内に置いてある事を想定していますが、そのファイルを共有ファイルサーバーや、社内のWebサーバーに置いておき、読み取り専用で開くようにしても動くはずです。

３．データを保存するファイル（データベースファイル）

今回のシステムでは、１つのExcelブック内にシートを分けて３つのテーブル（データを入れておく表）を作りました。
本格的なデータベースとの対比で言えば、Excelブック＝「データベース」、Excelシート＝「テーブル」になります。また各シートの１行目は列の名前でカラム名相当になり、その下に続くデータがレコード相当になります。

「項目を増やしたい」「カラム名が気に入らない」と言う方も多くいらっしゃると思います。と言って、このシートだけを変更したり追加・削除すると、システムは１００％動かなくなります。（追加は無視されるだけかもしれませんが）
今回のアプリは、SQL文を使ってブック間でデータをやり取りしていると言う点では、正式な「リレーショナルデータベース（RDB）を使ったアプリケーション」ですので、手を入れるのでしたら慎重な取り扱いをお願いします。

確かにExcelは気楽に修正・追加・削除が可能なアプリですが、それは優秀な人間の頭脳がその変更を認識し調整しながら使用しているために可能な事なのです。

３－１．名簿情報

「Sheet1」は、会議室予約を行う社員の名簿です。テーブル名はシート名になりますので「Sheet1」です。

図３－１

１行目には、列名（カラム名）が記載してあります。Ａ列から順に以下の内容になっています。

empno（必須）	社員番号。数値を想定しています。英文字等が入る場合は、関連コードの修正が必要です。
dept	部署名。組織によっては更に複雑になると思いますが、今回は同名者の識別の意味で使いました。
name	氏名
UserName（必須）	PCのユーザー名。Windowsの｢設定｣→｢アカウント｣→｢ユーザーの情報｣で得られるユーザーです。 大文字小文字まで含めて正確に記載必要。 空欄または間違えて記入していると予約できません。
tel	電話番号。今回はこの情報は使用していません。
color1 color2 color3	RGB関数の各引数[RGB(color1,color2,color3)]で、予約済み枠の配色に使用します。 それぞれ 0～255 の整数で設定します。 空は255の意味になり、全て空だと白色になります。

３－２．会議室情報

「Sheet2」は、会議室の情報一覧です。テーブル名は「Sheet2」です。

図３－２

各カラムの内容は以下の通りです。

room（必須）	会議室名。シートの横軸として貼られる名称です。
capa	収容人数。シート上の会議室名にマウスを当てると表示される内容です。
bihin1 bihin2	備品など。シート上の会議室名にマウスを当てると表示される内容です。

このテーブル（表）で「room」は、横軸である「会議室名」として表示されるのと同時に、予約データとしてデータベースに保存される時の会議室名としても使われます。ですので、名称を変更してしまうと以前のデータが取り出せなくなりますので、変更には注意が必要です。

また「capa」「bihin1」「bihin2」は、会議室名にマウスを当てた時に出るコメントに表示される内容になります。空欄（Null)でも構いません。

３－３．予約データ

「Sheet3」は、予約情報を保存しておく表です。テーブル名は「Sheet3」です。

図３－３

この表は予約データを記録しておく表であり、全ての操作はマクロが行います。

yno	予約番号。予約した順の追番（１～）
room	予約した会議室（Sheet2のroom列 ）
Kdate	予約日
Kstart	予約開始時刻
Kend	予約終了時刻
empno	予約者の社員番号（Sheet1のempno列 ）
title	会議名称
del	削除･修正ボタンを押すと｢1｣が記録され、検索対象から除外される

尚、図３－３の各セルの左肩に緑色の三角印が付いていますが、これは「書式が文字列に設定されているセルに数値が入力された」事を意味します。ですので文字列として数値が書き込まれていることになりますので、マクロ内では型変換を行いながら値を取り出す必要があります。

４．マクロの概要

今回アプリのマクロ構造は少し複雑ですので、まずはざっとした流れを説明します。
起動時に使うマクロ（操作時使用のPublic配列の準備とアプリの準備）と、データ操作時に使うマクロは、ある程度きれいに分かれていますので別々に説明します。
（図４－１と図４－２で共通で使われているプロシージャは、①～④です。）

４－１．起動時（準備段階）

図４－１上の矢印で、青線は「プログラムの流れ」、赤線は「データ書き込み等の作業自体」を示しています。

図４－１

最初にExcelブックを立ち上げると「Workbook_Open」イベント（図４－１　右上）が発生します。そこからスタートし順に動作させることで、システムの準備が整います。

まず最初に「⑤　データベースに繋がるか」の確認をします。もしサーバーのトラブル等でデータに繋がらなかった場合は、何の作業もできませんのでシステムを終了することになります。

データベースに繋がっていることが確認できたら、「⑥　全ユーザー情報」「⑦　操作しているPCのユーザー名」「⑧　会議室情報」を取得して、Public変数・配列にデータを保存します。

その後、登録等を行うためのダイアログ（UserForm１）を起動させます。
UserForm１起動前には、既に取得済みの⑥と⑦のデータを使って、予約データを操作できる担当者名をリストに入れます。
同時にスクロールバーの設定も行い、立ち上げ時は今日のデータが表示されるように、「シート上に今日の日付を記入」します。

UserForm１が立ち上がった後は、Sheet１の予約画面（前回保存時のデータが残っている事を想定）を消去し、今日の日付の会議予定を表示します。

これで会議予約の準備が完了になります。

４－２．操作時

図４－２での矢印線は、操作の種類によるプログラムの流れを表しています。
青線は「会議室予約」、赤線は「予約を修正・削除」、点線は「スクロールバーで日付を変更」した時の流れです。


図４－２

「予約」の場合は、まず「⑬　他の会議予定とダブっていないか」をチェックします。チェックし問題無ければ「⑩　データベースに登録」します。登録の後、その「③　予約データをシート上に表示」します。

「修正・削除」の場合は、修正・削除しようとしている「⑫　データが自分のものかをチェック」し、自分のものであれば「⑪　データベースからデータを削除」します。
その後、一旦「④　画面を全消去」し、データベースから「②　表示日のデータを呼出し」て「③　予約データをシート上に表示」します。

「日付を変更」した場合は「④　画面を全消去」し、データベースから「②　表示日のデータを呼出し」て「③　予約データをシート上に表示」します。

５．プログラム

５－１．ユーザーフォーム

５－１－１．フォーム画面

ユーザーが操作をするダイアログは、図３－６のようにしました。

図５－１

固定文字（「日付」「使用者」「～」など）に使っているLabel・FrameのCaptionや、CommandButtonのCaptionは、フォーム作成時にプロパティ変更済みですが、マクロでデータを処理する各コントロールのプロパティは標準のままです。（Initializeイベントプロシージャでプロパティを設定しています）

なお、右上の使用者欄はPCユーザー名（＝個人名）なので、通常では対象は１名です。ですので、当初はTextBoxやLabelで対応できるだろうとは思いました。
しかし「共有のPCを皆で使っている」という環境を考えた場合、「全員分の氏名をListBoxに並べ、その中から自分の氏名を選び会議室を予約する」こともできるべき　と考え、今回は「ListBox」コントロールを使うことにしました。

５－１－２．フォームコード

５－１－２－１．UserFormの起動時イベントプロシージャ

「UserForm_Initialize」イベントプロシージャは、フォームの起動指示があるとまず最初に実行されるプロシージャです。

1. '==========　⇩①　UserFormの起動時イベントプロシージャ　============
2. Private Sub UserForm_Initialize()　　　　　 'Userformの起動時（画面表示の前段階）
3. 　Call list_make　　　　　　　　　　　　　　 '操作担当者のリストを作成
4. 　Me.ScrollBar1.Max = CLng(Date) + 365　　 '←1年後まで予約可
5. 　Me.ScrollBar1.Value = CLng(Date)　　　　 '←最初は今日の予約表を表示
6. 　Me.ScrollBar1.Min = CLng(Date) - 365　　 '←1年前のﾃﾞｰﾀまで確認可
7. 　Me.ScrollBar1.LargeChange = 7　　　　　　 '←ﾊﾞｰの間をｸﾘｯｸすると±1週間
8. 　Me.ScrollBar1.SmallChange = 1　　　　　　 '←ﾊﾞｰの端をｸﾘｯｸすると±1日
9. 　Me.Frame1.Visible = False　　　　　　　　 '←修正･削除ﾃﾞｰﾀの情報は、最初は表示させない
10. End Sub

図５－２

３行目の「Call list_make」は、図５－８のプロシージャを動かすものです。
ダイアログ右上のListBoxに「会議室予約が可能な担当者のリストを作る」ことを目的としています。

４～６行目は、日付を変更するスクロールバーに上下限・現在値を設定するものです。
ここでスクロールバーの既定のMAX値は「32767(Integer型のMAX値)」、MIN値はゼロ（Value値も最小のMin値と同じゼロ）であることに注意して下さい。
シリアル値 32767 は、日付で言うと「1989/9/16」になります。つまり作ったばかりのスクロールバーを日付として使用する場合には、初期段階では「1899/12/31～1989/9/16」なのです。（日付は「1」からスタートし、1900/1/1が基準となります）

では、この標準のスクロールバーに対し、Max値・Min値・Value値を与える順番を図５－３で考えてみます。

図５－３

①が初期段階です。まず②のようにValue値に「今日の値（2020年だと43000台）」を設定しようとすると、MAX値を超えてしまいますのでエラーが発生します。
よって③のように、まず「MAX値」の設定が必要です。（ここでは、Max値は43000＋365、Min値は43000-365 としました）
次の段階は２通りあります。１つ目として、まず④のようにMin値を動かし（この場合、Value値は一緒についていきます。）、最後に⑥のようにValue値を設定する方法。
２つ目としては、⑤のように先にValue値を設定し、後からMin値を設定する方法です。

「どんな順番でもイイじゃないか」とお思いでしょうが大きく違います。Value値が変化した回数は、③→④→⑥の方法では２回、③→⑤→⑦の方は１回です。
ScrollBar1_ChangeはValue値が変化した時にイベント発生します。そのScrollBar1_Changeの中に「画面を全消去」＋「Value値の日付で会議予定を表示」のコードが書かれているのですから、２回イベントが発生すれば「画面を全消去」＋「Value値の日付で会議予定を表示」を２回実行するのです。しかも最初に表示するのは１年前の会議予定です。
無駄なデータベースとの通信、無駄な画面更新をさせないためにも、値の設定順序にも配慮が必要と考えます。

（ちなみに、Min値としてMax値よりも大きな値を設定することも可能です。MinとMaxが逆転した状態でUserFormを表示させると、MaxとMinの役割が逆転したままで動き続けます。）

７～８行目はスクロールバーを動かす速さで使い勝手を考えて決めるべきです。しかし「１日分の移動」をスクロールバーの中央のつまみで調整するのは結構苦労すると思うので、両端クリックで１日移動ができるように「SmallChange = 1」とするのが良いと思います。

９行目の「Me.Frame1.Visible = False」は「Frame1を非表示にする」という意味です。Frame1という１つのコントロールを非表示にするだけでなく「Frame1の枠内に配置した５個のLabelも非表示」になります。

５－１－２－２．UserFormの表示直後イベントプロシージャ

フォームが表示された後（Initializeイベントの次）に発生するイベントがLayoutイベントです。

11. '==========　⇩②　UserForm表示後のイベントプロシージャ　============
12. Private Sub UserForm_Layout() 　　　　　　　　　　　　　　 'Userformが画面に表示された時(日付が今日を表示した時)
13. 　Call Sheet1.Worksheet_SelectionChange(Selection) 　　　 '現在のセル位置情報をUserformに送る
14. End Sub

図５－４

フォーム表示後にやるべきことは、起動時のセル位置（＝保存時のセル位置）から会議室、開始･終了時刻を計算しダイアログ上に表示することです。
今回のシステムでは、セルの位置はワークシートのSelectionChangeイベントで取得し、そのセル位置が示す会議室・開始時刻・終了時刻の値をダイアログ上に書き込んでいます。しかし起動直後はセル位置を動かしていないためイベントが働かず、ダイアログ上の表示はLabel1などとUserForm1を作ったばかりの状態のままとなり、セル位置を正しく反映できていない状態になります。
その状態を避けるために、強制的に「SelectionChange」を動かしています。「どのシートのSelectionChangeプロシージャ」かを明確にする為に「Sheet1」を先頭につけ、選択範囲である引数には「Selection」を設定します。

５－１－２－３．「予約実行」ボタンを押した時のプロシージャ

ダイアログの「予約実行」ボタンを押すと、図５－５のプロシージャが動きます。

15. '==========　⇩③　「予約実行」ボタンをクリックした時のプロシージャ　===============
16. Private Sub CommandButton1_Click()
17. 　Dim R As Range
18. 　Dim Empno As Long
19. 　Dim k As Long　　　　　　 '←重なっている予定件数を表す
21. 　If Me.ListBox1.ListIndex = -1 Then
22. 　　MsgBox "予約者が選択されていないか" & vbCrLf & _
23. 　　　　　"あなたのPCユーザーが名簿に登録されていません"
24. 　　Exit Sub
25. 　End If
26. 　Empno = CLng(Me.ListBox1.BoundValue)
28. 　 '//セル位置のチェック
29. 　If Me.Label6.Caption = "" Or Me.Label5.Caption = "" Or _
30. 　　　　Me.Label1.Caption = "" Or Me.Label2.Caption = "" Then
31. 　　MsgBox "予約位置を選択して下さい。"
32. 　　Exit Sub
33. 　End If
35. 　 '//会議予定の重なりをﾁｪｯｸ(会議室・日付・開始時間・終了時間で検索)
36. 　k = k_Check(Me.Label6.Caption, _
37. 　　　　　　　CDate(Me.Label5.Caption), _
38. 　　　　　　　CDate(Me.Label1.Caption), _
39. 　　　　　　　CDate(Me.Label2.Caption))　　　　　　 '←重なっている会議数が返る
40. 　If k > 0 Then
41. 　　MsgBox "予定が重なっています。"
42. 　　Exit Sub
43. 　End If
45. 　 '予約実施(会議室・日付・開始時刻・終了時刻・社員番号・会議名称)
46. 　Call k_Yoyaku(Me.Label6.Caption, _
47. 　　　　　　　　CDate(Me.Label5.Caption), _
48. 　　　　　　　　CDate(Me.Label1.Caption), _
49. 　　　　　　　　CDate(Me.Label2.Caption), _
50. 　　　　　　　　CLng(Empno), _
51. 　　　　　　　　Me.TextBox1.Value)
53. 　Application.ScreenUpdating = False
54. 　 '予約表示(会議室・日付・開始時刻・終了時刻・社員番号・会議名称)
55. 　Call Block_make(Me.Label6.Caption, _
56. 　　　　　　　　　　CDate(Me.Label5.Caption), _
57. 　　　　　　　　　　CDate(Me.Label1.Caption), _
58. 　　　　　　　　　　CDate(Me.Label2.Caption), _
59. 　　　　　　　　　　CLng(Empno), _
60. 　　　　　　　　　　Me.TextBox1.Value)
62. 　Application.ScreenUpdating = True
63. End Sub

図５－５

２１～２５行目は、予約者の名前が入っているリストが選択されていない場合の処置です。「担当者が決まっていないので予約登録が出来ない」という意味ですが、そうなる場面としては２つ考えられます。
１つは①リストボックスにデータ（部署・氏名）は並んでいるが選択状態にない場合、もう１つは②リストボックスに何もデータが無い場合です。
①の場合は「List_Make」プロシージャ内で１番目を選択状態にするようにしていますので、外部マクロから操作されない限りあり得ないと思います。
②の場面は、アプリを起動したＰＣのユーザー名がデータベースの名簿に登録されていない場合に発生します。この場合は管理者によるユーザー登録が必要となります。

２１行目の「Me.ListBox1.ListIndex = -1」は「リストを選択していない」事を表しています。リストを選択していなければ、コメントを出してSUBプロシージャを終了します。
ちなみにリストの一番上を選択していたとすると「ListBox1.ListIndex」の値はゼロになります。（リストボックスはゼロからスタートします。）

２６行目で、リスト内で選択しているユーザーの「社員番号」を取得し、変数Empnoに代入します。
ここで、右辺「CLng(Me.ListBox1.BoundValue)」の中の「BoundValue」ですが、リストボックス内の「BoundColumn」プロパティとして設定した列番号の値を受け取ることが出来ます。

通常は「Me.ListBox1.Text」などの様なコードで受け取りますが、リストボックスが１列のデータであるなら、何ら問題ありません（.Text は１列目の）。しかし今回のリストボックスには「社員番号」「部署」「氏名」のデータが３列で並べられていますので、「何番目のデータを取得すれば良いのか」という問題が生じます。
それを解消するのが「BoundColumn」プロパティを設定し、「BoundValue」（または「Value」）で値を受け取る方法です。

例えば、３列データの真ん中の列であれば「BoundColumn = 2」を設定しておき、「BoundValue」で値を取得出来ます。
今回アプリでは１列目に社員番号があり、選んだ行の１列目の社員番号を受け取りますので「Me.ListBox1.Text」で取得しても「BoundValue」と同じデータを受け取ることになりますが、リストボックスの設計の自由度が上がると思いますので紹介いたしました。

２９～３３行目は、予約しようとしているセル位置が正しいかを判断しています。「会議室」「開始時刻」「終了時刻」がUserForm1の「Label５」「Label１」「Label２」に表示されていますので、その値（.Caption）が空で無いかを確認しています。
どれか一つでも空だったら（＝正しい位置をセル選択していない）、コメントを出してSUBプロシージャを終了します。

３６～３９行目は、会議室の重なりをチェックしています。
関数としては図５－３９の関数「k_Check(Room As String, d As Date, s As Double, e As Double)」を使います。関数の戻り値には、重なっている会議予定の数が返ってきます。与える４つの引数には、下表の値を与えます。

引数	内容	フォーム上のコントロールと値
Romm	会議室	Label6.Caption
d	日付	Label5.Caption
s	開始時刻	Label1.Caption
e	終了時刻	Label2.Caption

４０行目で、戻り値「k」が１以上であれば「選択範囲の中に別の会議予定が入っている」ことを意味しますので、その場合にはコメントを出してSUBプロシージャを終了します。

ここまでが「正しく選択できているか」のチェックです。
４６～５１行目は、データベースに対して予約データを書き込むコードです。
書込みには、図５－３６「k_Yoyaku(Room As String, d As Date, s As Double, e As Double, Empno As Long, Title As String)」のプロシージャを使います。
与える６つの引数には、下表の値を与えます。

引数	内容	フォーム上のコントロールと値
Romm	会議室	Label6.Caption
d	日付	Label5.Caption
s	開始時刻	Label1.Caption
e	終了時刻	Label2.Caption
Empno	社員番号	Empno（26行目で取得した値：ListBoxの選択した社員番号）
Title	会議名称	TextBox1.value

５５～６０行目では、予約した会議をSheet１に表示させています。
表示には、図５－２９の「Block_make(myRoom As String, d As Date, s As Single, e As Single, Empno As Long, Title As String)」プロシージャを使います。
与える６つの引数は「k_Yoyaku」と同じです。

５－１－２－４．「修正・削除」ボタンを押した時のプロシージャ

ダイアログの「修正・削除」ボタンを押すと、図５－６のプロシージャが動きます。

64. '==========　⇩④　「修正・削除」ボタンを押した時のプロシージャ　==============
65. Private Sub CommandButton2_Click()　　 '「修正」ボタン
66. 　Dim my As Variant
67. 　Dim Empno As Long　　　　　　　　　　 '←担当者の社員番号を表す
69. 　If Me.ListBox1.ListIndex = -1 Then
70. 　　MsgBox "予約者をリストから選択して下さい"
71. 　　Exit Sub
72. 　End If
73. 　Empno = CLng(Me.ListBox1.BoundValue)
75. 　If Me.Label6.Caption = "" Or Me.Label5.Caption = "" Or _
76. 　　　　　Me.Label1.Caption = "" Or Me.Label2.Caption = "" Then
77. 　　MsgBox "現在の予約を選択して下さい。"
78. 　　Exit Sub
79. 　End If
81. 　 '//選択しているのが、自分が作ったものか。自分のものの場合、ﾃﾞｰﾀの追番が返ってくる
82. 　my = my_Check(Me.Label6.Caption, CDate(Me.Label5.Caption), _
83. 　　　　　　　　　CDate(Me.Label1.Caption), CDate(Me.Label2.Caption), CLng(Empno))
84. 　If IsEmpty(my(0, 0)) = True Then
85. 　　MsgBox "選択予定が正しく無いか、又は" & vbCrLf & "あなたの取った予定ではありません。"
86. 　　Exit Sub
87. 　End If
89. 　 '//選択した予定の項目を「会議名称(TextBox1)」他にコピーする
90. 　Me.TextBox1.Value = my(1, 0)　　　　　　　　 '会議名称
91. 　Me.Label12.Caption = Me.Label5.Caption　　　 '開催日
92. 　Me.Label13.Caption = Me.Label6.Caption　　　 '会議室
93. 　Me.Label14.Caption = Me.Label1.Caption　　　 '開始時間
94. 　Me.Label15.Caption = Me.Label2.Caption　　　 '終了時間
95. 　Me.Frame1.Visible = True
97. 　 '//選択した予定を削除
98. 　Call k_Del(CLng(my(0, 0)))
99. 　 '//画面の再表示
100. 　Call ScrollBar1_Change
101. End Sub

図５－６

６９～７９行目は、図５－５の２１～３３行目と同じです。
　　６９～７２行目は、リストボックスで選択されているかをチェック。
　　７３行目はリストボックスから社員番号を取得。
　　７５～７９行目は、正しい場所がセル選択されているかをチェック。

８２～８３行目は、「選択している会議室予約が、自分が予約したものか否か」をチェックしています。
チェック関数としては、図５－３８「my_Check(Room As String, d As Date, s As Double, e As Double, Empno As Long)」を使用します。
引数として下記の5個を渡し、自分（＝Empno)が予約したものであれば、予約のyno（ユニークな予約番号）が返ってきます。

引数	内容	フォーム上のコントロールと値
Romm	会議室	Label6.Caption
d	日付	Label5.Caption
s	開始時刻	Label1.Caption
e	終了時刻	Label2.Caption
Empno	社員番号	７３行目で取得したEmpno

８４行目は、その戻り値が空（＝検索した結果、該当したレコードが無かった）だった場合に８５行目でコメントを表示し、Subプロシージャを抜けます。

戻り値があった場合は「修正削除するデータは自分が予約したもの」になりますので、削除前に「削除するデータの情報をダイアログに転写」します。それが９０～９４行目になります。

ちなみに９０行目で転写している「会議名称」ですが、「my(1,0)」としています。これはmy_Check関数がユニーク番号と同時に、会議名称を返してきているからです。（図５－３８のコードを確認下さい）
他の方法として、選択しているセル範囲には「部署／氏名／会議名称」が書き込まれているのですから、「テキストを分解して会議名称を取り出す」方法もあります。どちらにしようか迷いましたが、「テキストを取り出すと処理時間がかかる」のと「SQL文で工夫した方がコードが楽そう」な為、今回の方法を取りました。

Frame1は、図５－２のInitializeの９行目で非表示にしてありますが、９５行目で表示することにより、修正削除したデータ情報を表示するようにしています。

ちなみに「削除」はデータを削除するだけで問題ないのですが、「修正」の動作をどうしようかと悩みました。 そこで動作的にはデータを削除することとし、「削除前の情報を残して、それを確認しながら再設定する」こととしました。 感覚的に合わない方もおられるかもしれませんが、ご了承下さい。

９８行目はデータ削除です。８２行目の戻り値「my(0,0)」が予約データのyno（ユニークな予約番号）ですので、そのynoを引数にし、図５－３７ の「k_Del(yno As Long)」を使ってデータ削除します。

この段階では、データベースからデータは消えましたが、予約画面の表示は残ったままです。
ですので、１００行目でScrollBar1_Changeイベントプロシージャを呼び出し、「画面全消去」→「予約表示」をすることで削除した予約を消しています。

５－１－２－５．「本日」ボタンを押した時のプロシージャ

102. '==========　⇩⑤　本日」ボタンを押した時　============
103. Private Sub CommandButton3_Click()
104. 　Me.ScrollBar1.Value = Date　　　　　　　 '日付を本日に戻す
105. End Sub

図５－７

日付を変更するにはダイアログのスクロールバーを動かしますが、動かしすぎると「今日(付近)に戻るのが大変」になります。そこで「今日に戻るボタン」を設置しました。
１０４行目でスクロールバーのValue値に今日の日付のシリアル値Dateを設定します。
この操作によりScrollBar1_Changeイベントプロシージャが働き、「画面全消去」→「今日の予約表示」という動作をします。

５－１－２－６．リストボックスの内容を作るプロシージャ

図５－８は、図５－２のUserForm_Initializeの３行目から呼び出されるプロシージャです。

106. '==========　⇩⑥　リストボックスの内容を作る　============
107. Private Sub list_make()
108. 　Dim i As Long　　　　　　　　　　　　　　　 '←カウンタ変数（名簿の人数ｰ1を表す）
110. 　Me.ListBox1.ColumnCount = 3　　　　　　　　 '3列表示にする
111. 　Me.ListBox1.BoundColumn = 1　　　　　　　　 '1列目のEmpnoで受け取る
112. 　Me.ListBox1.ColumnWidths = "15;35;30"　　　 '各列の幅を設定
114. 　For i = 0 To UBound(User, 2)
115. 　　If User(3, i) = Uname Then　　　　　 '登録されているPCのﾕｰｻﾞ名だけ表示(ｻﾝﾌﾟﾙﾌｧｲﾙはｺﾒﾝﾄｱｳﾄしています)
116. 　　　Me.ListBox1.AddItem User(0, i)
117. 　　　Me.ListBox1.List(i, 1) = User(1, i)
118. 　　　Me.ListBox1.List(i, 2) = User(2, i)
119. 　　End If　　　　　　　　　　　　　　　 '(ｻﾝﾌﾟﾙﾌｧｲﾙはｺﾒﾝﾄｱｳﾄしています)
120. 　Next i
121. 　 '//// 先頭のリストを選択状態にする
122. 　If Me.ListBox1.ListCount > 0 Then Me.ListBox1.Selected(0) = True
123. End Sub

図５－８

１１０～１１２行目はリストボックスの枠組みを作成しています。
まずリストボックスに何を表示するかですが、今回は「社員番号」「部署」「氏名」の３つとしました。
ですので１１０行目で３列表示にします。

次に、リストのどの行を選択したかを判断するのに、どの列を使うかを考えます。
このリストの中で、ユニーク（その値を指定するだけで、ただ一つのデータが選択できる）なのは「社員番号」です。ですので１列目の社員番号の「１」をBoundColumnに設定します。

最後に各データがちゃんと表示されるように、各列の幅を決めます。
広く取れば良いわけではありません。今回の場合ListBoxの幅（Width）は「173.5」ですので、例えば Column.Widthsに "60;60;60" を設定してしまうと図５ーcの右側の様に、リストボックスの横方向のスライドバーが出てしまいます。
（図５ーcの左側は、"15;35;30" です。各列の文字数により調整してみて下さい。）

図５－９

各列の幅の合計値がListBox.Widthより少し小さくなるように設定するのが良いようです。
また Column.Widthsに「-1 」を設定するとExcel側で自動割り振りにしてくれますが、最小列幅があるようで「ListBox.Width が小さい場合にはスライドバーが出て」しまいます。
また、例えば「１列目の社員番号を見せたくない」のであれば、列幅をゼロにして下さい。

１１４～１２０行目でリストボックスにデータを書き込んでいます。
書込みの基本部分は１１６～１１８行目です。まず１１６行目のAddItemで「リストの行を作りながら、１列目に社員番号を書き込」みます。
AddItemでリストの行を作成したあと、１１７行目で２列目のデータを書込み、１１８行目で３列目のデータを書込んでいます。
尚、リストは行数も列数もゼロから数えます。

１１４行目は、書き込むデータをFor～Nextで回す部分で、回す回数は「ゼロ ～ Ubound(User,2)」で「Ubound(User,2)」はユーザ一覧の人数－１（ゼロから数えている為）になります。

１１５行目は「If User(3, i) = Uname Then」となっています。図３－１のＤ列（UserName）と図５－２１で取得するUname（操作しているPCのユーザー名）を比較し、合致している名前のみをリストボックスに載せています。
前に書いた「共有PCで皆で会議予約をする」場合には、共有PCに置くファイルには以下の修正をして下さい。
　●この１１５行目・１１９行目をコメントアウトする。この場合、全員がリストに載る。
　●１１５行目の右辺Unameの代わりに「"Common"」とかにすると同時に、名簿側も「"Common"」にする。
なお、最下段のサンプルファイルでは、１１５行目・１１９行目はコメントアウトしています。

１２２行目は「リストボックスの項目が１つ以上あれば」「一番上の項目を選択状態」にするという意味です。
このコードは必須ではなく、選択していなければ図５－５の２１～２５行目で中断します。しかし、リストボックス内には通常は１名分しか記述していないため、いちいちユーザーに選択させないための配慮と考えています。

５－１－２－７．スクロールバー（日付）を動かした時のプロシージャ

ダイアログ上の日付変更スクロールバーを動かした時のプロシージャです。
バーを動かしValue値が変わった時にイベント発生するとともに、Min値を再設定するのにつられてValue値が変更された際もイベントが発生します。

124. '==========　⇩⑦　スクロールバーのValue値が変化した時　============
125. Private Sub ScrollBar1_Change() '←日付が変更された場合
126. 　Sheet1.Range(DATE_CELL).Value = ScrollBar1.Value　　　　　　　 '←ｼｰﾄの日付を修正
127. 　Me.Label5.Caption = Format(ScrollBar1.Value, "yyyy/mm/dd")　　 '←UserFormの日付を修正
129. 　Application.ScreenUpdating = False
130. 　　Call Screen_Clear
131. 　　Call Reserve_Call(ScrollBar1.Value)
132. 　Application.ScreenUpdating = True
133. End Sub

図５－１０

１２６行目は、その変更されたValue値を「Sheet1.Range(DATE_CELL).Value」に書き込んでいます。「DATE_CELL」はPublic定数で図５－１５の中で設定（今回の場合は「Ｃ２セル」）されています。

１２７行目は、ダイアログのLabel５にスクロールバーの値を書き込んでいます。単純に書き込むとシリアル値になりますので、右辺の式「Format(ScrollBar1.Value, "yyyy/mm/dd")」で、日付形式に変更して書き込みます。

１３０行目では、図５－３５のプロシージャ「Screen_Clear」を呼び出して、画面を全消去します。
１３１行目では、図５－２７のプロシージャ「Reserve_Call」に表示日付を引数として渡し、予約状況を表示します。
その「画面消去＋更新」を、１２９行目（Application.ScreenUpdating = False）と１３２行目（= True）で挟み込み、画面更新時の処理時間を短縮させています。

５－１－２－８．ダイアログ右上の×印で終了した時のプロシージャ

ダイアログの右上×印をクリックした時に発生するイベントプロシージャが図５－１１です。

134. '==========　⇩⑧　システム終了のイベント　============
135. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
136. 　Call App_Exit　　　　 '動作ﾁｪｯｸ完了したらApp_Exit内の「End」を外して下さい。
137. End Sub

図５－１１

１３６行目は、図５－４２の「App_Exit」プロシージャを呼び出して、システムを終了させます。
なお、一番下に添付したサンプルファイルでは「App_Exit」プロシージャ内にEndステートメントを追加しています。動作チェックが完了した後、「App_Exit」プロシージャ内のEndステートメントを外して下さい。

また、本システムには「システムの終了ボタン」を設定していないので、ダイアログの×印で終了させています。 もし新たに終了ボタンを設置するのであれば、そのClickイベントプロシージャに「Call App_Exit」を記述して下さい。

この「UserForm_QueryClose」イベントには第二引数に「CloseMode」が設定されており、この値を調べればイベントが何によって呼ばれたかが分かります（下の表）。
更に細かい処理をする場合に活用して下さい。

引数 CloseMode の値	意味
0	[×]ボタンで閉じようとした
1	Unloadステートメントなどコードで閉じようとした
2	Windowsが終了した
3	タスクマネージャによって閉じられた

５－２．Workbookのコード

ファイル（Book)を開くと発生するイベントが、図５－１２の「Workbook_Open」イベントプロシージャです。

138. '==========　⇩⑨　ブックを開いた時のイベント　============
139. Private Sub Workbook_Open()
140. 　Call Start　　 'bookを開くと標準ﾓｼﾞｭｰﾙの「Start」ﾌﾟﾛｼｰｼﾞｬを実行
141. End Sub

図５－１２

１４０行目は、標準モジュールに記述した図５－１６の「Start」プロシージャを呼び出しています。

５－３．WorkSheet（Sheet1）

Sheet1は、会議室予約状況が見える作業シートです。その状況をユーザーが確認しながら会議を予約したり修正･削除したりする際、セルを範囲選択しますが、その時に図５－１３の「SelectionChange」イベントが発生します。

142. '==========　⇩⑩　シート上の選択範囲を変更した時のイベントプロシージャ　==============
143. Public Sub Worksheet_SelectionChange(ByVal Target As Range)
144. 　With Target.Areas(1)
145. 　　　 '//開始時刻をUserform1に転記
146. 　　UserForm1.Label1 = Format(Cells(.Row, START_COL).Value, "hh:mm")
147. 　　　 '//終了時刻をUserform1に転記
148. 　　UserForm1.Label2 = Format(Cells(.Item(.Count).Row, START_COL).Value + (INTERVAL_MINUT/60/ 24), "hh:mm")
149. 　　　 '//会議室をUserform1に転記
150. 　　UserForm1.Label6 = Cells(Room_START_ROW, .Column).Value
151. 　End With
152. End Sub

図５－１３

選択範囲から、「開始時刻」「終了時刻」「会議室名」をダイアログであるUserForm1の各Labelに書き込みます。
選択範囲とそれぞれの値の関係を図５－１４で説明します。

図５－１４

１４６行目は「開始時刻」です。
「行位置」は選択範囲（複数セルを選択している際は左上セル）の行位置、「列位置」は定数START_COL（図５－１５で設定した値：今回は２）のセルの値を時間型（"hh:mm"）に直してUserForm1のLabel1に書き込みます。
図５－１４で言えば行位置は９、列位置は２で、Ｂ９セルの「10:00」を開始時刻として書き込むことになります。

対象が「10:00」の場合、セル上で見えている値は「10:00」であっても、保存されている値（シリアル値）としては「0.416666666666667」です（24時間＝1）。ここで使用しているFormat(値, "hh:mm")は、その小数点の値を時間型（"hh:mm"）の文字列に変換するものです。

１４８行目は「終了時刻」です。
選択範囲の個数はTarget.Countで得られ、一番下のセルはTarget.Item(Target.Count)となります。その行位置と定数START_COLの列位置を使って、図５－１４ならば11:30を算出します。
しかし、図５－１４のセル範囲の終了時刻は11:30ではなく「12:00終了」ですよね。ですので、もう一つ下のセルの時刻にするために、時刻の間隔である30分（数式では INTERVAL_MINUT/60/24 ）を足しています。

「１つ下のセルを参照したら？」と思う方もいるでしょう。式なら「Cells(.Item(.Count).Row +1, START_COL).Value」です。
しかし、会議終了時刻を１日の最後の時刻（今回のシステムなら20:00）にしたい時はどうでしょう。その１つ下のセルには何もありませんので、終了時刻は「空文字」となってしまいます。
と言って20:30まで表示しておけば、そこまでは会議可能範囲だとユーザーに思われてしまいますので、この手はＮＧです。
（ユーザーには見えない様に、最下段の文字には背景色と同じ文字色を使う方法はあるかもしれませんが）

１５０行目は「会議室名」です。
選択範囲の列位置と定数Room_START_ROWの行位置から会議室名を取得します。

５－４．標準モジュール

５－４－１．定数・変数宣言部

153. '==========　⇩⑪　Public定数・変数の宣言　============
154. Public Const DATE_CELL As String = "C2"　　　　　　 '日付のセル位置
155. Public Const START_ROW As Integer = 4　　　　　　　 '時刻の先頭行
156. Public Const START_COL As Integer = 2　　　　　　　 '時刻の先頭列
157. Public Const Room_START_ROW As Integer = 3　　　　　 '会議室の先頭行
158. Public Const ROOM_START_COL As Integer = 3　　　　　 '会議室の先頭列
159. Public Const START_TIME As Double = 7.5　　　　　　 '7:30ｽﾀｰﾄの意味。8:00の場合は8
160. Public Const END_TIME As Double = 20　　　　　　　　 '20:00までの意味。
161. Public Const INTERVAL_MINUT As Integer = 30　　　　 '30分単位の意味
162. Public Const FILE_NAME As String = "¥¥SERVER¥EXCEL¥K_Data.xlsx"　　　　 'ﾃﾞｰﾀﾌｧｲﾙのﾌﾙﾊﾟｽ(ｻｰﾊﾞｰの場合)
163. 'Public Const FILE_NAME As String = "C:¥Users¥USER¥Desktop¥K_Data.xlsx"　 '（自PCの場合）
165. Public User As Variant　　　　 'ユーザー名簿データの宣言（配列）
166. Public Room As Variant　　　　 '会議室データの宣言（配列）
167. Public Uname As String　　　　 'PCユーザー名変数

図５－１５

１５４～１５８行目は、日付・時刻・会議室名の位置を既定しています。「時刻」の書込み数は１５９～１６１行目の定数で決まりますので先頭の行と列、「会議室名」はデータベースに記載されている数量で決まりますので先頭の行と列を既定します。

１５９～１６１行目は、時刻列（今回アプリではB4～B29セル）の先頭時刻・最終時刻・間隔です。シートへの記入のみに使用しており、「選択セル位置から時刻を算出、等には使用していません」。
時刻表記を数値ではなくグラフィック文字等を使う場合は、定数から算出することになります。
また、１６１行目の時刻の間隔を「時間単位」ではなく「分単位」にしたのは、例えば２０分単位にしたい場合は「0.3333･･･」になってしまうからです。そういうことが無い場合には「時間単位」の方が分かり易いかもしれません。

１６２～１６３行目は、データファイルの置き場・ファイル名のフルパスです。（１６３行目は参考の場所です）
一番下のサンプルファイルを試す場合は、データファイルのコピー先をフルパスで定数FILE_NAMEに設定してください。

１６５行目は、データファイルからユーザー名簿（データファイルの「Sheet1」）を起動時に読み込み、マクロ実行中は配列データとして保持しておくものです。
１６６行目は、データファイルから会議室情報（データファイルの「Sheet2」）を起動時に読み込み、マクロ実行中は配列データとして保持しておくものです。
１６７行目は、Excelファイルを起動しているＰＣのＰＣユーザー名を保持する変数です。

５－４－２．アプリ準備プログラム

ファイル（Book）を開いた時の Workbook_Openイベントプロシージャから呼ばれるプロシージャです。
内容としては、ほとんどが各機能を持ったプロシージャ呼出しです。

168. '==========　⇩⑫　アプリの準備をする　============
169. Public Sub Start()
170. 　Call DataFile_Check 　　　　 'Dataファイルに繋がるかﾁｪｯｸ
171. 　Sheet1.Unprotect　　　　　　 'ｼｰﾄ保護を解除（解除しないとｺﾒﾝﾄ作成時にｴﾗｰ発生）
172. 　Call All_User_Call　　　　　 'ﾕｰｻﾞｰ名簿を取得
173. 　Call This_User_Call　　　　 'このPCのﾕｰｻﾞｰ名を取得（全名簿に情報を書き込む方法も良さそう）
174. 　'Call ToDay_Call　　　　　　 '日付欄の書式を整える（毎回新品のｼｰﾄ上にﾃﾞｰﾀを書き込む時）
175. 　'Call Time_Call　　　　　　 '縦の時間軸を記入し書式を整える(ｻｰﾊﾞｰ側でﾃﾞｰﾀを持った場合)
176. 　Call All_Room_Call　　　　 '会議室を横列に表示させる
178. 　Sheet1.Protect UserInterfaceOnly:=True　　　 'ｼｰﾄ保護(ﾏｸﾛでの変更は許可)
179. 　UserForm1.Show 0　　　　　　　　　　　　　　　　　 'ﾀﾞｲｱﾛｸﾞをﾓｰﾄﾞﾚｽで表示
180. End Sub

図５－１６

１７０行目は、図５－１７の「DataFile_Check」プロシージャを呼び出し、図５－１５の定数FILE_NAMEのファイルが存在するかを確認しています。このファイルが存在しない（サーバー停止など）場合にはアプリを終了させます。

１７１行目はシート保護の解除です。
これは１７８行目でのシート保護に対する解除ですが、しかし「マクロでの変更は可」なのに何故解除が必要か、です。
「Addを使用するオブジェクト系はダメ」とのコメントをしているサイトもありますが、確かにAddを使用する「コメント追加」や「入力規制追加」はシート保護（UserInterfaceOnly:=True）をしていると出来ませんでした。理由は良く分かりませんが、とにかく「コメント作成時はシート保護解除」が必要のようです。
今回のアプリでは、１７６行目の「All_Room_Call」プロシージャでコメントを追加している工程がありますので、シート保護を解除してから作業を行っています。

１７２行目は、図５－１９の「All_User_Call」プロシージャを呼び出し、データベースからユーザー名簿情報をPublic配列Userに代入しています。
１７３行目は、図５－２１の「This_User_Call」プロシージャを呼び出し、アプリを操作しているＰＣユーザー名を取得し、変数Unameに代入します。
ユーザー名簿情報の中にも「ＰＣユーザー名」の項目があり、氏名等に対応するPCユーザー名が登録されています。つまり、アプリを操作している人（＝PCユーザー）が誰なのか（＝ユーザー名簿の氏名）が分かる訳です。

１７４行目は、図５－２３の「ToDay_Call」を呼び出し、日付欄（定数DATE_CELLのセル位置）の書式設定をします。
将来、細工（彩色、Mergeなど）をする際の改造容易化を考え別プロシージャにしていますが、現状ではセル書式設定の単純作業なので、ブック保存時に書式も保存されることから、コメントアウトしています。

１７５行目は、図５－２６の「Time_Call」プロシージャを呼び出し、Sheet1の縦列の時刻表示をするものです。
ただし、現状では時刻表示の開始・終了時刻、間隔、表示位置の値は図５－１５の定数で指示していますので、毎回書き換える必要はありません。ですので参考プロシージャとしてコメントアウトしてあります。
今後その指示値をサーバー側で調整するようなシステムになった際には使用する必要が出てくるかもしれません。

１７６行目は、図５－２４の「All_Room_Call」プロシージャを呼び出し、データベースから会議室簿情報をPublic配列Roomに代入しています。
配列代入後、Sheet1の横列として会議室の列記と書式変更をし、セルへのコメント（会議室情報の見える化）を追加しています。

１７８行目はSheet1に対してシート保護（Protectメソッド）をかけています。パラメータとして「UserInterfaceOnly:=True」を設定していますので「ユーザー操作は禁止するが、マクロからの操作は許可する」ことになります。
しかし、前にも紹介しましたが「コメント追加」や「入力規制追加」は対象外となりますので、１７１行目でUnprotectしてから準備作業をしています。

以上の準備作業が済みましたら、１７９行目でダイアログのUserForm1をモードレスで表示させます。
モードレス表示は、Sheet1上で必要な会議時刻を範囲選択するために必須です。

５－４－３．データファイルの存在確認

181. '==========　⇩⑬　データファイルの存在チェック　============
182. Sub DataFile_Check()
183. 　Dim Fso　As Object
184. 　Set Fso = CreateObject("Scripting.FileSystemObject")
185. 　If Fso.FileExists(FILE_NAME) = False Then
186. 　　MsgBox "DATAファイルが見つかりません。" & vbCrLf & "管理者へ連絡下さい。"
187. 　　Set Fso = Nothing
188. 　　Call App_Exit　　　 'Dataﾌｧｲﾙを配置し､動作ﾁｪｯｸ完了したら、ｻﾝﾌﾟﾙﾌｧｲﾙのApp_Exitプロシージャ内の
189. 　End If　　　　　　　　 '「End」ステートメントを外して下さい。
190. 　Set Fso = Nothing
191. End Sub

図５－１７

このプロシージャは、データベースファイルの存在をチェックしています。
チェックの方法は、このプロシージャで使用している「FileSystemObjectオブジェクトのFileExists」を使う方法と、「Dir関数」を使う方法があります。

その２つの方法について図５－１８に整理しました。
整理の仕方として、引数に与えるフルパスを「共有フォルダ名」＋「フォルダ名」＋「ファイル名」で表した時、「どの部分が誤り」だったら、どんな値が戻るかを表したものです。（「共有フォルダ名」とは相手サーバーが共有化しているフォルダ名）
「正」が正しいスペル、「誤」が誤ったスペルです。上流が誤っていれば下流側も誤っていることですから「不問」と記しました。またフォルダを指定している場合にはファイル名を「無」と記し、「¥」で文字列を終了させています。

共有ﾌｫﾙﾀﾞ名 ＋ ﾌｫﾙﾀﾞ名 ＋ ﾌｧｲﾙ名				FileSystemObjectオブジェクト のFileExists（今回）	Dir関数
①	正	正	正	True	ﾌｧｲﾙ名
②	正	正	誤	False	""
③	正	誤	不問	False	""
④	誤	不問	不問	False	実行時ｴﾗｰ52
⑤	正	正	無	False	ﾌｫﾙﾀﾞ内の1個目のﾌｧｲﾙ名（対象が無い時は""）
⑥	正	誤	無	False	""
⑦	誤	不問	無	False	実行時ｴﾗｰ52

図５－１８

Dir関数の方が戻り値が多様ですので原因を究明するには適していますが、今回の目的は「データベースファイルにつなげられるか」なので①または④です。単純にTrue・Falseで判別できるため「FileExistsメソッド」を使用しました。

１８４行目は「Scripting.FileSystemObject」オブジェクトを起動。
１８５行目でデータベースファイル名（＝定数FILE_NAME）が存在するか否かを調べ、不明（戻り値：False）ならば１８６行目でコメントを出して１８８行目でアプリ終了（＝App_Exitプロシージャの呼出し）させています。

１８７行目および１９０行目の「Set Fso = Nothing」は、１８４行目で参照したオブジェクトを解除するもので、データベースファイル有無に関わらず実行するようにしています。（「１８７行目はアプリ終了」時の解除）
１９１行目のEnd Subで、参照は自動解除するはずですが、ここでは明示的に解除するようにしています。

なお、データベースファイル名のミス等が原因でプロシージャ「App_Exit」で自動終了してしまう際には、起動時にESCキーを連打することにより、どこかでマクロを中断するはずです。また最下段のサンプルファイルでは、１８８行目の「Call App_Exit」で呼び出すApp_Exitプロシージャ内に「End」ステートメントを追加しています。

５－４－４．ユーザー名簿の呼び出し

データベースファイルから「ユーザー名簿」を呼出し、その情報をUser配列に格納するのが図５－１９です。

192. '==========　⇩⑭　ユーザー情報を配列Userに代入　============
193. Sub All_User_Call() 'ﾕｰｻﾞｰ名簿を取得しPublic配列に代入
194. 　User = SQL_exec("select * from [sheet1$] ")
195. End Sub

図５－１９

１９４行目の「SQL_exec」は、図５－４３の「SQL_exec(Sql As String, Optional ReadOnly As Boolean = True)」関数です。引数にSQL文を与え、戻り値としてその結果を２次元配列で返す関数です。
引数は２つで、第一引数がSQL文。第二引数（省略可能）は「INSERT文やUPDATE文でデータベースを書き換える時にFalseを指定」します。
その戻り値の形ですが、図５－２０のように「どんな結果が返ってきても二次元配列」となるようにしてあります。これは、その戻り値を処理するコードを簡略化するためです。詳細は図５－４３で説明します。


図５－２０

１９４行目のSQL文は「 select * from [sheet1$] 」で、「テーブル sheet1 から全て（*印）のレコード（＝行）を取り出す」という意味になります。
取り出したデータは、配列として「変数 User 」に代入されます。変数 User は、Public変数として図５－１５でVariant型で宣言されています。Variant型は「全ての型に変化できる型」ですので、配列型にも変化できます。

５－４－５．操作しているPCのユーザー名を調べる

アプリを操作している「ＰＣユーザー名」を取得するのが図５－Ｐです。

196. '==========　⇩⑮　操作しているPCのユーザー名を取得　============
197. Sub This_User_Call()
198. 　Dim WshNet As Object
199. 　Set WshNet = CreateObject("WScript.Network")　　 'WshNetworkオブジェクトを生成
200. 　Uname = WshNet.UserName　　　　　　　　　　　　 'PCのﾕｰｻﾞ名を取得。
201. End Sub

図５－２１

１９９行目で、「WScript.Networkオブジェクト」を参照させ、２００行目で「UserName」プロパティでＰＣユーザー名を取得します。取得した値は、Public変数のUname（String型）に文字列として代入されます。

この「ＰＣユーザー名」は、Excelの情報欄やWindowsのユーザー情報で得られる名前です（図５－２２では「USER」）。

図５－２２

また、ＰＣユーザー名では社内管理できない場合は、「コンピュータ名」「ドメイン名」も使用できます。
２００行目の右辺を以下に変更して取得して下さい。
　コンピュータ名：WshNet.ComputerName
　ド メ イ ン 名　：WshNet.UserDomain

他に特定する手段としては「MACアドレス」「IPアドレス」等が考えられますが、その方法についてはここでは割愛いたします。別なサイトを参照して下さい。
また、ＰＣユーザー名は大文字小文字は別扱いですので、データベースファイルに記載する際には正確に記述して下さい。

５－４－６．日付欄の書式設定

日付欄の位置は、図５－１５のPublic定数 DATE_CELLで指示され、そのセルの書式を設定するのが図５－２３のプロシージャです。

202. '==========　⇩⑯　日付欄の書式設定　============
203. Sub ToDay_Call()
204. 　With Sheet1.Range(DATE_CELL)　　　　　　 '←日付を記入するセル(Public定数)
205. 　　.HorizontalAlignment = xlCenter　　　　 '←中央揃え
206. 　　.NumberFormatLocal = "yyyy/mm/dd(aaa)"　 '←曜日付きの書式
207. 　End With
208. End Sub

図５－２３

このプロシージャは、呼出しを中止しています。（図５－１６でコメントアウト済み）
当初はこのプロシージャでDATE_CELLのセルに今日の日付を記入し、ついでに書式を設定していたのです。しかし、「ScrollBar1_Changeイベント（図５－１０）」で「スクロールバーのValue値に今日の値を設定すると同時にDATE_CELLのセルに今日の値を記入」することになり、今ではこのプロシージャでは書式を整えるだけのものになってしまいました。
毎回起動するたびに新品のシートに日付・時刻などを記入するのでなければ、Excelブックに書式は残りますのでこのプロシージャは特に必要はないと思っています。

５－４－７．会議室情報を取得し、シートに書き込み

データベースファイルから会議室の情報を取得しPublic配列Roomに代入後、作業シートに会議室情報を記入するのが図５－２４の前半（⑰）です。記入前に以前のデータを消去するのが後半（⑱）です。

209. '==========　⇩⑰　会議室情報取得＋会議室名シート書込み　============
210. Sub All_Room_Call()
211. 　Dim i As Long　　　　　 '←カウンタ変数（Roomの数-1を表す）
212. 　Call Room_Del　　　　　 '←会議室の記述と書式を削除
214. 　Room = SQL_exec("select * from [sheet2$] ")　　　 '←ﾃﾞｰﾀﾍﾞｰｽﾌｧｲﾙから会議室情報取得
215. 　For i = 0 To UBound(Room, 2)
216. 　　With Sheet1.Cells(Room_START_ROW, i + ROOM_START_COL)
217. 　　　.Value = Room(0, i)　　　　　　　　　　　　　 '←文字を記入
218. 　　　.HorizontalAlignment = xlCenter　　　　　　　 '←文字位置を中央揃えに
219. 　　　.Font.Color = RGB(255, 0, 0)　　　　　　　　 '←文字色を赤に
220. 　　　.Font.Bold = True　　　　　　　　　　　　　　 '←太文字に
221. 　　　.Font.Italic = True　　　　　　　　　　　　　 '←斜文字に
222. 　　　.AddComment　　　　　　　　　　　　　　　　 '←コメントを追加
223. 　　　.Comment.Text Text:="会議室名：" & Room(0, i) & Chr(10) & _
224. 　　　　　　　　　　　　　　"収容人数：" & Room(1, i) & Chr(10) & _
225. 　　　　　　　　　　　　　　"備品１：" & Room(2, i) & Chr(10) & _
226. 　　　　　　　　　　　　　　"備品２：" & Room(3, i)
227. 　　　.Comment.Shape.Width = 150　　　　　　　　　 '←コメント幅を150ポイントに
228. 　　　.EntireColumn.ColumnWidth = 25　　　　　　　 '←列幅を25ポイントに
229. 　　End With
230. 　Next i
231. End Sub
232. '==========　⇩⑱　会議室の値・書式削除　============
233. Sub Room_Del()
234. 　Dim col As Long　　　　 '←Roomを表示してある最大列
235. 　Dim i As Long
237. 　 '//会議室の列数を計算
238. 　With Sheet1.Cells(Room_START_ROW, ROOM_START_COL).CurrentRegion
239. 　　col = .Column + .Columns.Count - ROOM_START_COL
240. 　End With
242. 　For i = 0 To col - 1
243. 　　With Sheet1.Cells(Room_START_ROW, ROOM_START_COL + i)
244. 　　　.Value = ""　　　　　　　　　　　　　 '←文字を削除
245. 　　　.HorizontalAlignment = xlGeneral　　 '←文字位置を標準に
246. 　　　.Font.ColorIndex = xlAutomatic　　　 '←文字色を自動に
247. 　　　.Font.Bold = False　　　　　　　　　 '←太文字を解除
248. 　　　.Font.Italic = False　　　　　　　　 '←斜文字を解除
249. 　　　On Error Resume Next　　　　　　　　 '←ｺﾒﾝﾄが無い時は無視
250. 　　　　.Comment.Delete　　　　　　　　　 '←ｺﾒﾝﾄを削除
251. 　　　On Error GoTo 0
252. 　　　.EntireColumn.ColumnWidth = 8.38
253. 　　End With
254. 　Next i
255. End Sub

図５－２４

２１２行目は、以前の会議室名の文字・コメント情報などを削除するもので、２３３行目の「Room_Del」を呼び出しています。内容については、後に説明します。

２１４行目は、データベースファイルから会議室情報を取得し、Public配列のRoomに代入しています。ここで使われているSQL文「 select * from [sheet2$] 」は「Sheet2テーブルから全てのデータを取り出す」という意味です。
その取得結果を変数Roomに代入しています。変数Roomは図５－１５でVariant型で宣言されており、SQL_exe関数の戻り値は二次元配列（図５－２０）ですので、Room変数も配列に変化します。

２１５行目・２３０行目のFor～Nextで、「配列Roomのレコード数」分を回しています。
回しているのは２１６行目の書込み位置で、行位置が「Room_START_ROW」、列位置が「i + ROOM_START_COL」でiがゼロから始まって会議室数分になります。列位置を増加させていますので、横方向に並べていくことになります。

書き込む内容は２１７～２２８行目になります。
２１７行目が「Room(0,i)」で、会議室情報データ（図５－２５）の先頭（データ番号はゼロから始まります）なので「会議室名」になります。

図５－２５

２１８行目で、文字を横方向の中央揃えにします。
２１９行目で、文字色を赤色にします。色はRGB(0～255,0～255,0～255)で表しています。
２２０行目で、太文字にします。
２２１行目で、イタリック体（斜め文字）にします。

２２２行目は、コメントを追加し、２２３～２２６行目で、そのコメントに文字列を与えています。
与える文字列は、「会議室名」「収容人数」「備品１」「備品２」の順に、改行（Chr(10)）でつないでいます。
尚、改行として使っている「Chr(10)」は定数「vbLf」ですが、「vbCrLf」「vbNNewLine」でも同じ結果が得られます。また今回とは異なりますがMsgBoxの引数ではChr(13)（＝定数vbCr)でも改行の意味になります。
統一するのであれば、Ｏ／Ｓによらない「vbNNewLine」が良いかと思います。

２２７行目では、そのコメントの吹き出しの横幅を設定しています。表示内容が正しく表示されるように調整して下さい。
２２８行目は、列幅を設定します。シート上の予約情報は「部署／氏名／会議名称」を表示させていますので、それに合わせて列幅を決めてください。


２３３～２５５行目は、会議室名のセル内容を削除するものです。
内容は、２１０～２３１行目の「All_Room_Call」の会議室情報記入のほぼ逆です。

２３８行目は、会議室名の先頭セルに対して「CurrentRegion」で範囲指定をしています。これは「今までは４つの会議室だったけど、これからは３つ」みたいな事を想定し、隣り合っている会議室名は全て消し書式も消去してから、新たな会議室名を記入しています。
２３９行目は、「会議室名を何個削除するか」を計算、その個数分だけ２４２行目でFor～Nextで回しています。
２４３～２５３行目は、その各セルに対して、文字消去＋書式の初期化を行っています。
その中の２５０行目の「コメント削除」を挟んで「On Error Resume Next」でエラー処理をしています。これは「もしコメントが無かった」ことを想定しています。コメントが無かった場合には実行時エラーが発生しますので、その対策となっています。

なお「毎回、書式まで削除しなくても」とも思います。最低限の「文字列・コメント・列幅」くらいの削除でも見かけ上は問題ないかもしれません。

５－４－８．時刻軸（縦軸）の消去・記入

縦軸の時刻軸を消去・記入するのが図５－２６です。このプロシージャは図５－１６でコメントアウトされていますが、毎回書き換える必要は無いため、通常は不要と考えています。

256. '==========　⇩⑲　縦軸の時刻を記入　============
257. Sub Time_Call()
258. 　Dim R As Long　　　 '←カウンタ変数（行数相当）
259. 　Dim T As Single　　 '←カウンタ変数（時刻相当）
260. 　Call Time_Del　　　 '←現在の時刻を削除
262. 　For T = START_TIME To END_TIME Step INTERVAL_MINUT / 60
263. 　　Sheet1.Cells(START_ROW + R, START_COL) = T / 24
264. 　　R = R + 1
265. 　Next T
267. 　With Sheet1.Cells(START_ROW, START_COL).Resize(R, 1)
268. 　　.NumberFormatLocal = "h:mm"　　　　 'ﾌｫｰﾏｯﾄを時分にする
269. 　　.Font.Size = 9　　　　　　　　　　　 'ﾌｫﾝﾄを小さくする(上寄りを強調する為)
270. 　　.HorizontalAlignment = xlCenter　　 '横方向で中央寄せ
271. 　　.VerticalAlignment = xlTop　　　　　 '縦方向で上寄せ
272. 　End With
273. End Sub
274. '==========　⇩⑳　縦軸の時刻を消去　============
275. Sub Time_Del()
276. 　Dim ro As Long　　　　 '←Timeを表示してある最大行
277. 　Dim i As Long　　　　 '←カウンタ変数（時間を記入してあるセル数相当）
278. 　With Sheet1.Cells(START_ROW, START_COL).CurrentRegion
279. 　　ro = .Row + .Rows.Count - START_ROW
280. 　End With
282. 　For i = 0 To ro - 1
283. 　　With Sheet1.Cells(START_ROW + i, START_COL)
284. 　　　.Value = ""
285. 　　　.NumberFormatLocal = "G/標準"
286. 　　　.HorizontalAlignment = xlGeneral
287. 　　　.Font.Size = 11
288. 　　　.VerticalAlignment = xlCenter
289. 　　End With
290. 　Next i
291. End Sub

図５－２６

２６０行目で、まずは時刻消去（２７５行目以降）を行っています。詳細は下で紹介します。
２６２・２６５行目のFor～Nextで時刻値の書込みを行っています。

２６２行目は、記入時刻を24時間単位で回しており、繰り返す値は、開始時刻「START_TIME」から終了時刻「END_TIME」で記入間隔は「INTERVAL_MINUT / 60」で指示しています。INTERVAL_MINUTは分単位ですので24時間単位に変換するため60で割っています。

２６３行目の左辺は書き込む位置を指示します。列位置はPublic変数の「START_COL」で、行位置は「START_ROW」を先頭に２６４行目の「R = R + 1」で、一つずつ下に移動していきます。
右辺は時刻を２４時間単位から１／２４単位（＝24時間で１）に変換しています。

２６７～２７２行目は、記入した時刻値の書式を整えています。
２６７行目は、時刻の先頭セル「Sheet1.Cells(START_ROW, START_COL)」を基準に、下に「R」個だけサイズを大きくした範囲を示しています。この時の「R」は、２６２～２６５行目のFor～Nextで繰り返した回数「R」です。つまりは「時刻値の個数」になります。

その「時刻を記入した範囲」に対して、
２６８行目で、時刻値を時分表示に変更（例：0.4375 → 10:30）します。
２６９行目で、フォントサイズを少し小さ目の9に設定。
２７０行目で、横方向で中央寄せ表示にし、
２７１行目で、縦方向で上寄せ表示にしています。

この書式は好みの問題なのですが、私の意図としては「セルの境界線に時刻が表示してある」様に見せたかったのです。例えばＣ９セルを範囲選定した場合、「自分は10:00～10:30を指定したんだ」と直感で分かる事が重要と考えたのです。
もし縦方向で中央寄せであったなら「10:00を指定した。30分単位だから10:30までかな？」と頭で計算をしなくてはならないかもしれません。

この縦軸時刻の消去・記入は、記入位置も開始終了もこのファイルのPublic定数で決めているので毎回書き直す必要は無いと考え、通常は使用しないことにしました。

５－４－９．予約データを呼び出す

日付を指定し、その日の予約情報を取得するプロシージャです。プロシージャ内でそのデータを書き出す「Block_make」プロシージャを呼び出しています。

292. '==========　⇩(21)　日付を指定し会議予約情報を呼び出す　============
293. Sub Reserve_Call(d As Date)
294. 　Dim Reserve As Variant　　　 '予約一覧を代入する配列
295. 　Dim i As Long　　　　　　　　 'カウンタ変数（予約数）
296. 　 '//日付指定で予約情報を取得する。削除した(del列に1が入っている)ものは含まず
297. 　Reserve = SQL_exec("select * from [sheet3$] where Kdate='" & d & "' and del is Null")
299. 　If IsEmpty(Reserve(0, 0)) = True Then Exit Sub　　　 'ﾃﾞｰﾀが1つもなかったら処理終了
301. 　For i = 0 To UBound(Reserve, 2)　　　 '←予定を1つずつ作成する指示を出す
302. 　　Call Block_make(CStr(Reserve(1, i)), _
303. 　　　　　　　　　CDate(Reserve(2, i)), _
304. 　　　　　　　　　CSng(Reserve(3, i)), _
305. 　　　　　　　　　CSng(Reserve(4, i)), _
306. 　　　　　　　　　CLng(Reserve(5, i)), _
307. 　　　　　　　　　CStr(Reserve(6, i)))
308. 　Next i
309. End Sub

図５－２７

２９７行目で、データベースファイルから会議予約情報を取得しています。
使っているSQL文は「"select * from [sheet3$] where Kdate='" & d & "' and del is Null"」です。式内の「d」はこの「Reserve_Call」プロシージャの引数「d」で、「表示したい日付」です。
ですのでSQL文の意味は、「会議予約情報テーブル（＝Sheet3）から日付列（＝Kdate）が指示した日付（＝d）のものを全て取り出せ（＝select *）。但し削除したもの以外（＝del is Null）」となります。

取り出したデータは、二次元配列として変数Reseerveに代入されます。データが一つもなければ「Reseerve（0,0）」は空で返ってきますので、もし空だったらプロシージャを抜ける（＝データを記入するBlock_makeプロシージャを呼び出さない）ようにします（２９９行目）。

取得したデータが１つでもあれば、３０１行目のFor～Nextで３０２～３０７行目をデータ数の分だけ繰り返します。
配列Reseerveには、図５－２８の順序でデータが入っています。

図５－２８

そのデータを使って「会議予定のブロックを作成」するのがBlock_makeプロシージャです。
図５－２９で見る通り、渡す引数は「会議室名」「日付」「開始時刻」「終了時刻」「社員番号」「会議名称」の順です。

図５－２８と照らし合わせると、配列はゼロからスタートしているので渡すインデックスは「１(room)」「２(Kdate)」「３(Kstart)」「４(Kend)」「５(empno)」「６(title)」になります。
ですので、Block_makeに渡す引数の順序は、３０２～３０７行目の通りになります。

５－４－１０．予約データを１つ１つのブロックにしてシートに表示する

図５－２９のプロシージャは、会議室情報を元に作業シート上に予約状況を表示するものです。

310. '==========　⇩(22)　予約データをシート上に表示　============
311. Sub Block_make(myRoom As String, d As Date, s As Single, e As Single, Empno As Long, Title As String)
312. 　Dim Select_Col As Long　　　　　　　 'ﾌﾞﾛｯｸ化する列
313. 　Dim Select_RowS As Long　　　　　　 'ﾌﾞﾛｯｸ化する行開始位置
314. 　Dim Select_RowE As Long　　　　　　 'ﾌﾞﾛｯｸ化する行終了位置
315. 　Dim Select_Range As Range　　　　　 '選択(しているはずの)範囲
316. 　Dim Room_No As Long　　　　　　　　 '会議室のカウンタ変数
317. 　Dim U_Empno As Long　　　　　　　　 'Empnoのカウンタ変数
319. 　 '//本来日付は不要。何かのｴﾗｰを防止するため、引数の日付と画面の日付のﾁｪｯｸ
320. 　If Sheet1.Range(DATE_CELL).Value = d Then
322. 　　 '//列の割り出し
323. 　　For Room_No = 0 To UBound(Room, 2)
324. 　　　If Sheet1.Cells(Room_START_ROW, Room_No + ROOM_START_COL) = myRoom Then Exit For
325. 　　Next Room_No
326. 　　Select_Col = Room_No + ROOM_START_COL
328. 　　 '//行の割り出し。e(終了時刻)は1つ先の枠の値を使用している為、-1している。
329. 　　Select_RowS = (s * 24 - START_TIME) / (INTERVAL_MINUT / 60) + START_ROW
330. 　　Select_RowE = (e * 24 - START_TIME) / (INTERVAL_MINUT / 60) + START_ROW - 1
332. 　　 '//割り出した行列の値を使用して選択(しているはずの)範囲を特定する。
333. 　　Set Select_Range = Range(Sheet1.Cells(Select_RowS, Select_Col), Sheet1.Cells(Select_RowE, Select_Col))
335. 　　Select_Range.Merge　　　　　 'セル結合する
337. 　　 '//作成社員は社員名簿(配列:User)の中の何番目かを探索
338. 　　For U_Empno = LBound(User, 2) To UBound(User, 2)
339. 　　　If User(0, U_Empno) = Empno Then Exit For
340. 　　Next U_Empno
342. 　　With Select_Range
343. 　　　 '//登録されている色番号で色をつける
344. 　　　.Interior.Color = RGB(IIf(User(5, U_Empno) = "", 255, User(5, U_Empno)), _
345. 　　　　　　　　　　　　　　IIf(User(6, U_Empno) = "", 255, User(6, U_Empno)), _
346. 　　　　　　　　　　　　　　IIf(User(7, U_Empno) = "", 255, User(7, U_Empno)))
347. 　　　 '//部署・氏名・会議名を記入
348. 　　　.Value = User(1, U_Empno) & " / " & User(2, U_Empno) & " / " & Title
349. 　　　 '//文字は上揃えにする
350. 　　　.VerticalAlignment = xlTop
351. 　　　 '.WrapText = True　　　 '←文字列折り返しを設定すると、行高さが膨らんで整然としなくなる。
352. 　　　.Borders.LineStyle = xlContinuous　　　 '←周囲に枠線を入れる
353. 　　End With
354. 　End If
355. End Sub

図５－２９

３２０行目は、作業シート上の日付と引数d（日付）を比較し、合っていればそれ以降のコードを実行するものです。
万一「予約実行ボタン」を押した時、システムがデータベースとの通信に手間取っている間にユーザーがスクロールバーを操作してしまった場合を考え、If分で選別しています。
そういったエラーが無い場合は、今回のシステムではシート上には１日分しか表示しませんし、データベースファイルの検索（Reserve_Callプロシージャ）も日付１日分での検索になりますので、If文は必ずTrueになります。
今後、シート上に２日分の予約表を表示するような改造の際には、この引数の日付（d）を使用して分岐するようにします。

３２３～３２６行目は、予約状況を記入する列を割り出しています。
第一引数「myRoom」に会議室名が渡されますので、作業シートの横列の会議室名と見比べて合致しているところでFor文を抜けるのが３２３～３２５行目です。
その抜け出した時のカウント変数「Room_No」を使って、３２６行目で列位置を計算し「Select_Col」に代入します。

３２９行目は引数「s（開始時刻）」を使用して、それに相当する行位置を計算します。
「s」は１日を１とした時の小数点の値です。それに24を掛けることで、図５－３０の①の様に例えば 8:30 であれば 8.5となります。

図５－３０

その値からSTART_TIME（開始時刻：図５－３０では7.5 ）を引き②、INTERVVAL_MINUTを時間に変換した値（図５－３０では0.5 ）で割ると、「START_TIME から何セル離れているか」が得られます。③
よって、その値にSTART_ROWを足すと、引数「s」の行位置が計算できるのです。

３３０行目は引数「e（終了時刻）」を使った行位置計算ですが、３２９行目と違うのは最後の「ー１」です。
図５－３０で見ると、引数は「10:00相当」ですから９行目になるのですが、セル範囲としては点線枠である８行目にする必要があるので「－１」するのです。
（そうしないと、その後10:00から会議室を使いたい人が使えませんので）

列と開始行・終了行の位置が決まったら、その囲われる範囲を３３３行目で計算し、変数Select_Rangeに代入します。 「面倒な計算をせずに、範囲選択であるSelectionを使えば良いのに」と思われるかもしれません。１つはCtrlキーなどを使って複数の分割範囲を選択している時には、誤った範囲として計算してしまうことです。しかしこれは「Selection.Areas(1)」などとすれば済みます。
最大の理由は「(1)範囲選定して予約した時にシート上に表示する時」と「(2)データベースから呼んできたデータを使ってシート上に表示する時」とに「同じプロシージャを使う」ことです。
実行する回数としては(2)の方が圧倒的に多いことも、表示プロシージャを２種類作らず統一したかった理由です。
３３５行目では、その選択範囲相当を「セル結合」します。

３３８～３４０行目は「引数のEmpnoは、名簿（配列User）の中で何番目か」を探しています。合致したらForを抜けて、その抜けた時のカウント変数U_Empnoをその後の「セルの彩色、氏名・部署名記入」時に使用します。

なお、退職等で社員番号が見つからなかった場合には、For～Nextが全て回ってしまい、配列の最後に載っている社員情報が表示されてしまうことになります。
対応としては、以下のような方法が考えられます。
　１）この名簿だけは退職後１年は情報を残しておく。
　２）名簿の一番最後のレコードは「氏名：退職者」等としておく。･･･しかし、これでは連絡が取れない
　３）For～Nextが回り切ったら、既定値で「氏名：不明」等としておく。･･･これも、連絡不能
　４）退職者の予約データ（Sheet3）を調べ、後継者の社員番号に変更する

３４２行目以降は、セル結合（Merge）したセル範囲への彩色・テキスト入力なのです。
まず３４４～３４６行目では、背景色を設定しています。色は、データベースの名簿（図５－３１）のＦ～Ｈ列のcolor1～3で設定されているものを使用します。
色はRGBの組み合わせで表されますが、「R・G・B」をそれぞれ「color1・color2・color3」カラムに割り当て、0～255の値で設定します。カラムの番号としては、ゼロ始まりですので、「color1・color2・color3」は「５・６・７」になるため「User(5,U_Empno)などとしています。

図５－３１

なお、名簿「データベースファイルのSheet1」の「color1・color2・color3」カラムが空だった場合には、RGB("","","")となり、エラーとなります。空文字（""）はVal関数を使えば数値のゼロになりますので、３４４～３４６行目は、
「.Interior.Color = RGB(Val(User(5, U_Empno),Val(User(6, U_Empno),Val(User(7, U_Empno) 」
と簡単になりますが、RGB(0,0,0)は黒です。
「背景色が黒だと、文字色を変えないと・・・」と思い、３４４～３４６行目の様に「空文字の時は255」とし、白の背景色にすることにしてあります。

３４８行目は、社員番号から導き出した「部署名（dept）」「氏名（name）」「会議名称（引数Title）」を「／」でつないで、セルに記入しています。

３５０行目は文字位置を上寄せにし、ブロックの中で一番上に表示されるようにしてあります。
３５１行目はコメントアウトしてありますが、文字列の折り返し設定です。「WrapText = True」を設定をすると、文字列が入りきらない場合には図５－３２のように行高さが膨らんでしまいます。
反対に横に会議予定がないと文字がはみ出しますし、横に予定が入っていれば文字が隠れて見えない事になります。

図５－３２

会議室の数が少ない場合は、列幅を充分に広げたり、文字数に従って都度列幅を修正したりする手はありますが、セルを選択すれば数式バーに内容が表示されますので、それで充分と判断し文字折り返しはしておりません。

３５２行目は予約のブロック外周に枠線を引いています。これは、会議予約の範囲を明確にし、見易くしているつもりです。

実はこの「.Borders.LineStyle = xlContinuous」は「外枠線を引いているわけでは無い」のです。もちろん１つのセルを対象としてこのコードを使用すれば外枠線を引いていることになるのですが、複数セル範囲に対して実行すると図５－３３の左側の様に「範囲内の全ての罫線（斜め線以外）に格子状に線が引かれる」ことになるのです。

図５－３３

しかし適用範囲をセル結合（Merge）していると、範囲内の内側の罫線部分は見えなくなりますので図５－３３の右側のように「外枠線を引いている様に見える」ことになります。その証拠に、結合を解除すると図５－３３の左側の様になります。

また「.BorderAround xlContinuous」という書き方もあります。これこそ「外枠線を引く」コードなのですが、残念ながらExcel2010以降でしか使用できません。
また、マクロ記録をしてみると、図５－３４のような感じのコードが生成されます。

• With Select_Range
• 　.Borders(xlEdgeLeft).LineStyle = xlContinuous
• 　.Borders(xlEdgeLeft).Weight = xlThin
• 　.Borders(xlEdgeTop).LineStyle = xlContinuous
• 　.Borders(xlEdgeTop).Weight = xlThin
• 　.Borders(xlEdgeBottom).LineStyle = xlContinuous
• 　.Borders(xlEdgeBottom).Weight = xlThin
• 　.Borders(xlEdgeRight).LineStyle = xlContinuous
• 　.Borders(xlEdgeRight).Weight = xlThin
• End With

図５－３４

もちろんこれでも外枠線は引けます。「LineStyle」「Weight」のどちらか片方のみの行だけでも同じ結果が得られますが、コードが煩雑というか読む気を無くすというか・・・外枠線を引くのでしたら、工夫をしてみるのも良いと思います。

５－４－１１．作業シートの予約状況記入範囲を消去

予約状況を作業シート上に表示する前に、図５－３５で不要の予約状況を消去する必要があります。

356. '==========　⇩(23)　会議予定を表示した部分の消去　============
357. Sub Screen_Clear()
358. 　Dim Last_Row As Long　　　　　 '←消去画面の最右端
359. 　Dim Last_Col As Long　　　　　 '←消去画面の最下端
361. 　Last_Row = START_ROW + (END_TIME - START_TIME) / (INTERVAL_MINUT / 60)
362. 　Last_Col = ROOM_START_COL + UBound(Room, 2)
364. 　With Range(Sheet1.Cells(START_ROW, ROOM_START_COL), Sheet1.Cells(Last_Row, Last_Col))
365. 　　.UnMerge　　　　　　　　　　 '←ｾﾙ結合を解除
366. 　　.ClearContents　　　　　　　　 '←文字を削除
367. 　　.Interior.Pattern = xlNone　　　 '←色を解除
368. 　　.Borders.LineStyle = xlNone　　 '←罫線を削除
369. 　　 '.VerticalAlignment = xlCenter　　 '←上揃えを解除
370. 　End With
371. End Sub

図５－３５

３６１～３６２行目で、範囲を特定するための計算をしています。消去範囲の左上セル位置は、Public定数（行＝START_ROW、列＝ROOM_START_COL ）で決まっていますので、右下セル位置（ここでは、行＝Last_Row、列＝Last_Col　としてます）を求めていきます。
３６１行目の「(END_TIME - START_TIME) / (INTERVAL_MINUT / 60)」は、図５－３０と同じ考え方で、START_ROWから何セル離れているか、を計算しています。
３６２行目は、会議室情報が格納されている配列Roomの会議室数－１（UBound(Room, 2)）をROOM_START_COLに足す事で、右端の列位置が取得できます。

３６４行目以降では、左上セルと右下セルを囲む範囲に対するプロパティを変更しています。
３６５行目でセル結合を解除し、３６６行目で記入されている文字列を削除。
３６７行目で背景色を解除し、３６８行目で罫線を削除しています。

３６９行目の「.VerticalAlignment = xlCenter」は、文字列表示の上揃え解除のつもりですが、解除しなくても見かけ上も作業上も問題ありませんので、コメントアウトしてあります。

５－４－１２．会議室予約実行

ダイアログの「予約実行」ボタンを押した時に、データベースファイルに予約データを送付するのが図５－３６です。

372. '==========　⇩(24)　会議室予約実行のデータベース登録　============
373. Public Sub k_Yoyaku(Room As String, d As Date, s As Double, e As Double, Empno As Long, Title As String)
374. 　Dim Sql As String　　　 '←予約状況ﾃﾞｰﾀをInsertするSQL文を入れる変数
375. 　Dim yno As Variant　　　 '←Insert時に使用するﾃﾞｰﾀの追番
376. 　Dim Re As Variant　　　 '←insert実行の返り値（実際には返ってこない為、ダミー）
378. 　 '//現在のﾃﾞｰﾀの最大追番を取得。削除した行(del列が1のもの)も含む
379. 　yno = SQL_exec("select max(int(yno)) from [sheet3$]")
381. 　Sql = "insert into [sheet3$] " _
382. 　　　　　　& "values(" & Val(yno(0, 0)) + 1 _
383. 　　　　　　& ",'" & Room _
384. 　　　　　　& "','" & d _
385. 　　　　　　& "'," & s _
386. 　　　　　　& "," & e _
387. 　　　　　　& "," & Empno _
388. 　　　　　　& ",'" & Title _
389. 　　　　　　& "',NULL)"
391. 　Re = SQL_exec(Sql, False)
392. End Sub

図５－３６

３７９行目は、データベースファイルの「予約テーブル」に、いくつレコードが存在するかを調べています。
図５－２８で分かる様に、テーブルの先頭カラムは「yno」という追番（＝ユニークな番号）を入れていますので、新たにデータを挿入するには、新しく付ける「追番は何番か」を知る必要があるのです。
そのため、SQL文は「"select max(int(yno)) from [sheet3$]"」＝「Sheet3テーブルのyno列の値で、最大値を求めよ」という命令をしています。

他に考えられるSQL文としては「select count(*) from [sheet3$]」＝「Sheet3テーブルの列の数を求めよ」があります。今回システムでは、これでもOKです。
但し、これは「レコード削除が不可能なExcelデータベース」たから「ynoの最大値＝レコード数」が成り立つ訳で、通常のAccsess等のデータベースは「DELETE文でレコードが削除」できるので「ynoの最大値 ≠ レコード数」となります。

また「select max(int(yno))」という様に「ynoを整数にして（int）から最大値を求める」式にしています。
これを単純に「select max(yno)」としてしまうと間違った結果が出てしまいます。その理由は、テーブル上では「数字は文字列として保存（図５－２８参照）」されているからで、例えば文字列の「"9"」vs「"10"」を比較した場合だと、「0x39」vs「0x31 & 0x30」で「"9"」の方が大きいことになってしまうからです。
（ちなみに「int」は、通常のSQL言語の中には存在しないので、Excel以外では使用しないで下さい）

３８１～３８９行目は１行のコードで、SQL文を組み立てています。
SQL文にはInsert文（データの挿入）を使用し、挿入する値はValuesの後ろのカッコ内に、カンマで区切って羅列します。

Sheet3の予約テーブルのレコードは、図５－２８の様に
「yno（ユニークな番号）」「room（会議室名）」「Kdate（日付）」「Kstart（開始時刻）」「Kend（終了時刻）」「empno（社員番号）」「title（会議名称）」「del（レコード削除印）」の順で並んでいますので、その順にセットします。
　３８２行目：最新のynoとして、３７９行目で取得した最大yno（二次元配列ynoの（0,0）の値に１を足したもの
　３８３行目：会議室名として、引数のRoom
　３８４行目：日付として、引数のd
　３８５行目：開始時刻として、引数のs
　３８６行目：終了時刻として、引数のe
　３８７行目：社員番号として、引数のEmpno
　３８８行目：会議名称として、引数のTitle
　３８９行目：レコード削除印として、NULL

尚、３８９行目の様に「データを入れない場合」は「NULL」を指定します。文字列では無いので両端を「'（シングルクォーテーション）」で囲ってはいけません。尚、Excelの場合は ''（空文字）を指定してもNULLを入れた事になるようです。
また、テーブルの列数が８個あったら、Insert文のValues内にも8個の値を入れる必要がります。NULLだからと言って省略するとエラーが発生します。

また、通常のデータベースでは可能な「列名指定でのInsert文」は、Excelシートのデータベースでは出来ないようです。
たとえば「insert into [sheet3$] values(yno= 10 , room='X21' , Kdate='2020/1/1',････」 の様なSQL文は、Excelシートのデータベースではエラーが出てしまいますので注意が必要です。

組み立てたSQL文を変数Sqlに代入し、３９１行目で関数SQL_execに渡し、データベースファイルへデータを書き込みます。
SQL_exec関数の第二引数に「False」を指定していますが、これは「読み込みでは無い」という意味のFalseです。InsertやUpdateを使用するときには、第二引数にFalseを設定します。

５－４－１３．予約の修正・削除

ダイアログの「修正・削除」ボタンを押した時に、データベースファイルの予約データを書き換えるのが図５－３７です。

393. '==========　⇩(25)　予約の修正・削除のデータベース登録　============
394. Public Sub k_Del(yno As Long) '
395. 　Dim d As Variant
396. 　d = SQL_exec("update [sheet3$] set del = 1 where clng(yno) = " & yno, False)
397. End Sub

図５－３７

３９６行目で使用しているSQLは、Update文（データの変更）です。対象のテーブルはupdateの後ろの「テーブルSheet3（予約データ）」で、変更する列名と変更値を「＝（イコール）」で結び、変更範囲をその後のWhere句で絞り込みます。

このプロシージャの引数にはyno（社員番号）が渡されてきます。SQL文のWhere句は「where clng(yno) = 引数yno」ですので、yno（ユニーク番号）でレコードをひとつに絞ることになります。
その１つに絞ったレコードの「del列を 1 に変更」（set del = 1 ）します。 SQL_exec関数の第二引数に「False」を指定していますが、これは「読み込みでは無い」という意味のFalseです。InsertやUpdateを使用するときには、第二引数にFalseを設定します。

また、SQL_execは関数ですので、変数dで戻り値を受け取っている形にしていますが、「読み込みでは無い」場合には「SQL_exec関数プロシージャは戻り値が無い」ので、変数dはダミー的に使用しています。

５－４－１４．選択した予約が、自分が予約したものか否かを調べる

「修正・削除」ボタンを押した時、操作している社員以外の者が登録した予約を勝手に修正削除されないように、選択した予約が、操作している者が予約したものか否かを調べるのが図５－３８です。

398. '==========　⇩(26)　選択した予約が、自分が予約したものか否かを調べる　============
399. Public Function my_Check(Room As String, d As Date, s As Double, e As Double, Empno As Long) As Variant()
400. 　Dim Sql As String
401. 　Dim Re As Variant
402. 　Sql = "select yno,title from [sheet3$] " _
403. 　　　　　& "where room='" & Room _
404. 　　　　　& "' and cdate(Kdate) ='" & d _
405. 　　　　　& "' and cdbl(Kstart) = " & s _
406. 　　　　　& " and cdbl(Kend) = " & e _
407. 　　　　　& " and clng(empno) =" & Empno _
408. 　　　　　& " and del is Null"
410. 　my_Check = SQL_exec(Sql)
411. End Function

図５－３８

プロシージャの引数としては「room（会議室名）」「Kdate（日付）」「Kstart（開始時刻）」「Kend（終了時刻）」「empno（社員番号）」の５つが渡されます。その５つが全て合致する予約データの「yno（ユニーク番号）」及びその「title（会議名称）」を取り出しています。

SQL文は４０２～４０８行目でSQL文を組み立てており、「全て合致」ということから上記５つの引数をWhere句の中で「and」を使ってつないでいます。
最後の「and del is Null」は、del列（データ削除した時に、1のフラグをUPDATEする）に何も入っていない（＝削除していない）レコードに対して検索をしています。

取り出す値は「Selct yno , title from ･･･」となっている様に、ユニーク番号のynoと会議名称のtitleの２つを取り出します。

組み立てたSQL文を４１０行目でSQL_exec関数に渡し、戻り値として「yno と title」を受け取り、my_Check関数としての戻り値として返しています。
ynoを返すのは「引数で渡した条件の予約は正しい」ことを意味しています。一方titleは、呼び出している側（図５－６）でダイアログのLabelに貼り付けるデータ用です。
このように、プロシージャを特殊な用途用に組み立ててしまうと汎用性が低くなってしまうので、もう少し工夫が必要かもしれません（ちょっと反省しています）。

５－４－１５．予約のダブりが無いかチェックするプロシージャ

「予約実行」ボタンを押した時、選択した範囲内に既に予約済みの会議が入っていたら「会議が重複」してしまいます。「その範囲に、何個の会議が含まれるか」という意味の探索をするのが図５－３９です。

412. '==========　⇩(27)　会議のダブりチェック　============
413. Public Function k_Check(Room As String, d As Date, s As Double, e As Double)
414. 　Dim Sql As String
415. 　Dim Re As Variant
416. 　Sql = "select count(*) from [sheet3$] " _
417. 　　　　　& "where room='" & Room _
418. 　　　　　& "' and cdate(Kdate)='" & d _
419. 　　　　　& "' and cdbl(Kstart) < " & e _
420. 　　　　　& " and cdbl(Kend) > " & s _
421. 　　　　　& " and del is Null"
423. 　Re = SQL_exec(Sql)
424. 　k_Check = Re(0, 0)
425. End Function

図５－３９

「重複していない」ことが知りたいことなので、重複している予約がゼロであれば重複していない事になります。
「重複している」を数式にするには、どうすれば良いでしょうか。
まず「日付」と「会議室」が合っている事が前提ですので、４１７行目「room」・４１８行目「Kdate」が引数値である必要があります。その上で、KstartとKendをどう絞り込むかを図５－４０で考えます。

図５－４０

図５－４０の左端のオレンジ範囲を「予約したい会議範囲」、水色範囲（①～⑥）を「既予約の会議」とします。
答えを言ってしまうと、「（オレンジ色のＳ ＜ 水色のKend）かつ（オレンジ色のＥ ＞ 水色のKstart）」が成立する水色範囲がオレンジ範囲と重なっている事になります。
ここで②と⑦に注目して「オレンジ色Ｓ＝水色Kend」や「オレンジ色Ｅ＝水色Kstart」をどう扱うかですが、例えば「前の会議の終了時刻が8:00で、次の会議の開始時刻が8:00」というのは成立しますので、先ほどの式の不等号「＜」に等号付き「≦」は不要ということになります。

もう一つの求め方ですが、まず「オレンジと重ならない条件」を考えます。図５－４０では①②⑦⑧です。
これを式で表すと「（オレンジ色のＳ ≧ 水色のKend）または（オレンジ色のＥ ≦ 水色のKstart）」となります。図５－４１のベン図で表すと、①または②ということになり、最終的には③の様になります。

図５－４１

この「オレンジと重ならない条件」の逆が「オレンジと重なる条件」ですが、これは図５－４１の⑥（オレンジと白が逆転）ということになります。この⑥は③を求めた式をひっくり返して、「①の逆＝④」ｘ「又は → かつ」ｘ「②の逆＝⑤」とすれば求まります。
当然ながら、上で出した「（オレンジ色のＳ ＜ 水色のKend）かつ（オレンジ色のＥ ＞ 水色のKstart）」と同じです。

よって、SQL文の４１９行目・４２０行目は「cdbl(Kend) > s」and「cdbl(Kstart) < e」となります。
４２１行目の「del is Null」は「削除されていないもの」を対象にする意味です。

このSQL文を「SQL_exec」関数に与え、返ってきた値（重なっている予約数）を「k_Check」関数プロシージャの戻り値として返します。

５－４－１６．アプリケーションの終了

ダイアログ右上の×印をクリック、又はデータベースファイルに繋がらない時にはアプリを終了します。その処理が図５－４２です。

426. '==========　⇩(28)　アプリ終了処理　============
427. Public Sub App_Exit()
428. 　Application.DisplayAlerts = False　　 '注意ﾀﾞｲｱﾛｸﾞを出さない設定
429. 　　ThisWorkbook.Save　　　　　　　　　　 'ﾌﾞｯｸを保存
430. 　　If (Workbooks.Count = 1) Then
431. 　　　Application.Quit　　　　　　　　　 ' 開いているブックが自身のみの場合はExcelを終了
432. 　　End If
433. 　　ThisWorkbook.Close　　　　　　　　　 'ﾌﾞｯｸを閉じる
434. 　Application.DisplayAlerts = True
435. End Sub

図５－４２

まず、アプリ終了時にブックを保存すべきか否かは状況により意見が分かれると思います。今回アプリの場合は、データはアプリ内には残しませんので基本的にブック保存の必要性はありません。
しかし自分のＰＣ内にアプリがある場合でしたら、別なワークシートにメモを残したい人もいるかもしれません。その場合にはブック保存しないといけません。

サンプルファイルでは、４２９行目でブックを保存させています。アプリ終了時にブックを保存するのであれば、注意ダイアログの停止（４２８行目の「Application.DisplayAlerts = False」）をする必要は無くなりますので、どちらか一方にする事も可能です。（このままでも正常に動きます）
４３０～４３２行目は、開いているブックがアプリだけ（Workbooks.Count = 1）だった場合には、４３１行目でアプリケーション（＝Excel本体）が終了します。
Excelが終了した場合は４３３行目以降は実行されませんが、開いているアプリが２つ以上開いていた（＝会議室予約アプリ以外にもファイルを開いていた）場合には、４３３行目の「ThisWorkbook.Close」で、このマクロのあるブックが閉じます。
最後に、４３４行目の「Application.DisplayAlerts = True」で、注意ダイアログが復活し残されたExcelブックには迷惑をかけない様にしています。

尚、サンプルファイルではApp_Exitプロシージャの先頭に「Endステートメント」を置いてあります。動作確認完了しましたら、Endステートメントを外して下さい。

５－４－１７．SQL文を受け取り、データベースファイルとやり取りする

今回、様々なプロシージャ内でデータベースのデータを授受していますが、実際のデータベースとのデータ授受は図５－４３の「SQL_exec」が担当しています。
データベースとの接続準備・接続・データ授受・切断には多数の手続きが必要であるため、専用の窓口として設けました。
また呼出し元のプロシージャの中での処理を簡単にすべく、戻り値は全て二次元配列に統一する処理を行い、また戻り値内のNull値も空文字に変換し、Excel処理に適する形に整える様にしました。

436. '==========　⇩(29)　SQL文でのデータベースとのやり取り（MSDASQLプロバイダ）　==================
437. Function SQL_exec(Sql As String, Optional ReadOnly As Boolean = True) As Variant()
438. 　Dim cn As Object　　　　　　　 '←ｺﾈｸｼｮﾝｵﾌﾞｼﾞｪｸﾄ変数
439. 　Dim rs As Object　　　　　　　 '←ﾚｺｰﾄﾞｾｯﾄｵﾌﾞｼﾞｪｸﾄ変数
440. 　Dim Temp As Variant
441. 　Dim cnt As Long
442. 　Dim i As Long , j As Long　　 '←得られた配列内のNullを空文字にする為のカウンタ変数
444. 　Set cn = CreateObject("ADODB.Connection")
445. 　Set rs = CreateObject("ADODB.Recordset")
447. 　cn.Provider = "MSDASQL"
448. 　cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
449. 　　　　　　　　　　　　　　& "DBQ=" & FILE_NAME & ";" _
450. 　　　　　　　　　　　　　　& "ReadOnly=" & ReadOnly & ";"
451. 　cn.Open
452. 　　rs.Open Sql, cn
453. 　　　If ReadOnly = True Then
454. 　　　　If rs.EOF = False Then
455. 　　　　　Temp = rs.GetRows　　　　　　　　 '←全行ﾃﾞｰﾀを配列にまとめて代入
457. 　　　　　For i = 0 To UBound(Temp, 1)　　　 '←配列内にNullが存在するとｴﾗｰが出る為空文字に変更
458. 　　　　　　For j = 0 To UBound(Temp, 2)
459. 　　　　　　　If IsNull(Temp(i, j)) Then Temp(i, j) = ""
460. 　　　　　　Next j
461. 　　　　　Next i
463. 　　　　Else　　　　　　　　　　　　　　　　 '←摘出ﾃﾞｰﾀが無いの場合
464. 　　　　　ReDim Temp(0 To 0, 0 To 0)　　　　 '←他の場合と同様に2次元配列を作る（各要素は空）
465. 　　　　End If
466. 　　　　SQL_exec = Temp　　　　　　　　　　 '←配列を戻り値とする
467. 　　　　rs.Close
468. 　　　End If
469. 　cn.Close
470. 　Set rs = Nothing
471. 　Set cn = Nothing
472. End Function

図５－４３

このプロシージャ内のデータベースファイルへの接続については「Excelシート上にDBを作りSQLを使ってデータを入出力する」も参照下さい。
またデータベースを扱うには、通常「Microsoft ActiveX Data Object X.X Library」を参照設定します（事前バインディング）が、今回は実行時バインディングで行っています。事前バインディングに改造する際には「オブジェクトの生成のやり方について」も参照下さい。

４４４行目で「ADODBのConectionクラスのオブジェクト」として、データベース接続用の「cn」を生成します。
また４４５行目で「ADODBのRecordsetクラスのオブジェクト」として、データ授受用の「rs」を生成します。
ここで「ADODB」の「ADO（AcriveX Data Objects）」とは、Microsoftが開発した外部データベースを操作する方法であり、上記の参照設定（Microsoft ActiveX Data Object X.X Library）を実行時に直接呼び出すイメージです。

４４７行目は、データベースにアクセスする為のコンポーネント（Provider）を設定します。今回は「MSDASQL」（Microsoft OLE DB Provider for ODBC）を設定しています。
Excelシートにアクセスするには、「ACE」（Office 12.0 Access Database Engine OLEDB Provider）も使用できますが、これについては図５－４６で紹介します。

データベースに接続するには、データソースに合わせた接続文字列を与える必要があります。４４８行目はこれに当たります。
Providerに「MSDASQL」を選択した時には、Driverとして「{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}」を、また449行目のDBQにはデータベースファイルのパス名＋ファイル名を与えます。今回はPublic定数FILE_NAME として設定していますので、FILE_NAMEを与えます。

４４８行目の「Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};」で、複数のExcel拡張子を指定しています。「以前の項」では「*.xls のみでも動く」ような書き方をしていましたが今回データベースファイルとしてExcel2016を使った時には動かず、Excel2007以降で求められている様に複数の拡張子を列記しました。

４５０行目のReadOnlyには「読み取り」の場合はTrueを「書込み」の場合にはFalseを設定します。True・Falseの設定のみでなく、「読み取り」と「書き込み」では少し処理内容が変わります。（４６７行目のところで説明します）
このSQL_exec関数プロシージャは、「読み取り」と「書き込み」の両方の機能を１つにしています。そのため分岐できるように、プロシージャの第二引数にTrueかFalseを渡してもらうようにしました。「読み取り」がTrueで「書き込み」はFalseです。（第二引数は任意とし、その際はTrueを選んだ事にしています）

４５１行目の「cn.Open」で、４４７～４５０行目の内容でデータベースへの接続をします。
４５２行目はデータベースに仕事をさせます。「Sql」は第一引数で受け取ったSQL文、「cn」は接続オブジェクトです。
４５３行目のIF文は第二引数のReadOnlyがTrueだった時、つまり「SQL文がSelect だった時」に４５４～４６８行目を実行します。ですので「Insert文、Update文」（＝ReadOnlyがFalse）の時には、４５２行目を実行後は４６９行目に飛びます。

なお、このプロシージャの第二引数は任意で、指定しない場合は自動的にTrueになるようにしてあります。これは「Select文が多い」ことも理由の一つですが、「データベースを書き換えるという意識を持つ」意味で、「書き換え時は第二引数必要」としています。

４５４行目の「rs.EOF = False」ですが、これは４５２行目を実行して得られた値が「EOF（End of File）＝これ以下には値が無い」では無い時に、４５５～４６１行目を実行し、EOFだった場合にElse以下の４６４行目を実行するという意味です。

そもそも、４５２行目の実行結果としては、以下の４種類が考えられます。

	rs.EOF=	rs.Fields.Count=	rs(0,0).Value
①Select文で､ 値が1つ以上得られる	False	1以上	値
②Select文で､ 得られた結果がNull	False	1	Null
③Select文で､ 何も得られず	True	1	｢削除されたか･･･｣のエラー文
④Insert, Update文	ー	0	ー

図５－４４

①はSelect文でいくつかの結果が得られる場合です。
②は、今回で言えば図５－３６の３７９行目「Select Max(yno) from ･･･」で、ynoの最大値を求めたいけど新品シートの為に対象となるレコードが無く、計算をするとNullになる場合です。
③は、今回で言えば図５－２７の２９７行目「Select * from ･･･」で、ある日の会議予定を表示しようとしても、その日は誰も予約していない為、対象レコードが無い場合です。
④は、データベースに対して追加・書き換えをするのみで、値が返ってくる必要が無い場合です。

この４種に対して異なる処理をするため、IF文・IF～ELSE文を使用し、①②は４５５～４６１行目、③は４６４行目、④は処理不要のために４６９行目に飛ぶようにしています。

では①②に必要な処理としては以下の通りです。
４５５行目では、「rs.Open Sql, cn」（４５２行目）で取り出した値をまとめて変数Tempに代入しています。取り出された値は二次元配列になっていますので、変数Tempも配列になります。

通常は図５－４５の様な感じで、「配列のサイズを１つ大きく」「値を代入」「カーソルを１つ動かす」を「ファイルの最後まで」繰り返しますが、「Temp = rs.GetRows」を使用すると、まとめて配列として代入できます。

• 　Do Until rs.EOF
• 　　cnt = cnt + 1
• 　　ReDim Preserve Temp(1 To rs.Fields.Count, 1 To cnt + 1)
• 　　For i = 1 To rs.Fields.Count
• 　　　If Not IsNull(rs(i - 1).Value) Then
• 　　　　Temp(i, cnt + 1) = rs(i - 1).Value
• 　　　End If
• 　　Next i
• 　　rs.movenext
• 　Loop

図５－４５

「 rs.GetRows 」を使用すると、図５－４５のコードより処理を高速化できますが、もちろんデメリットもあります。
１つ目は、PCのメモリ量により取得できる列数が制限されるため、大量のデータを取得する場合には注意が必要です。
２つ目は、まとめて配列として代入しますので、あとから配列の並べ替え・選別などの細工をしようとするとやっかいです。この点は図５－４５の手法の方が自由自在です。
３つ目は、レコードの方向が行列で逆になっていることです。このため図５－２０の左側のようにレコードが縦方向に並びます。これは図５－４５のコードでも「Redim で増やせるのは最終次元」であることから同等とも言えます。
４つ目として、配列のインデックスが必ずゼロから始まることも挙げられるかもしれません。

配列として全レコードを代入した後は、４５７～４６１行目で「配列内を全て調べて」「Nullを空文字に変更」してます。これは予約データに配色する工程（図５－２９）や、会議室にコメントをつける工程（図５－２４）で、値だと思って使用したものがNullだった場合にはエラーが発生するからです。

図５－２９や図５－２４などの必要となった時に「Nullを空文字・数値などに変換」する方法も確かにあります。しかしExcelシートのデータベースは元は普通のExcelシートですので、本格的RDBMSのような「is not Null規制」や「列の型指定」が出来ませんし、アクセス権限だって甘いです。
人の手で簡単に値が削除出来たり、悪意は無いにしろ間違えたりする事が考えられますので、今回は「Excelが扱えないNullは残しておかない」ことにし、４５９行目のNull→空文字変換をしました。

図５－４４の③（対象のレコードが無い）の場合は、最初の段階でrs.EOF＝Trueになります。ですので４６４行目の「ReDim Temp(0 To 0, 0 To 0)」で変数Tempを配列にします。
とくにTemp(0,0)に何か入れる必要はありません。配列として大きさを決定しておくだけでTemp(0,0)はEmptyになりますので、Excel中では「""（空文字）」の扱いが可能になります。

図５－４４の①②③の場合は４６６行目を実行し、データの入った二次元配列、または空の二次元配列をSQL_exec関数プロシージャの戻り値にします。
尚、④の場合には４６６行目を通過しませんので、戻り値としては「配列宣言をしただけ（次元も要素数も決まっていない）のVariant値」を返すことになります。

図５－４５のところで紹介したように、Redimで増やせるのは最高次元のみですので、レコードが増えていくと列が増えていく（横に延びていくイメージ）ことになります。
しかし、これはExcelのワークシートを扱っている人間の頭からすると逆のイメージで、「レコードは行が増える方向に延びていく」のが自然に感じます。
ですので、Redim等の途中の計算段階は目をつむるとして、結果を配列として渡す時には縦横をひっくりかえして「レコードを行にする」のに使用されるのが「Application.WorksheetFunction.Transpose（配列）」です。

「こんな便利な関数があるのに、今回は何故使わない？」のかですが、「Transpose関数」には以下の特徴があります。
　１）対象は二次元配列のみ。
　２）二次元配列だが片方の要素数が１つのものは、変換後１次元配列になる
　３）変換後のインデックスは、必ず１から始まる
　４）配列内にNullがあると縦横変換出来ない

例えば「Select * from ･･･」で２レコード取得した時には、「Transpose関数」で縦横変換した後も二次元配列のままですが、１レコードのみだった場合には縦横変換で１次元配列になってしまいます。０レコードの場合はカラム数も変わってしまいます。
そうなると、結果を受け取るプロシージャ側では、取得したレコード数が０か１か１より大きいかで分岐しなければならず、１次元配列、二次元配列がごっちゃになったコードになってしまいます。

ということで今回は「結果を受け取るプロシージャ側のコード単純化」の為に「受け取る結果は全て二次元配列」になるようにし、「レコードは列方向に並んでいるけど我慢する」ことにしました。
（カラム名が０行目になるように配列を組み立てることも試みましたが、なにせ「Transpose関数」はインデックスを１始まりにするので、「１レコード目は２行目から」となってしまい、これも気持ち悪く断念しました。）
「見ていて気持ち悪い」方もいると思いますがご了承下さい。

４６７行目は、４５２行目で「rs.Open」したレコードセットを「rs.Close」します。図５－４４の④は、この４６７行目も通過しませんが、Insert文やUpdate文には「rs.Close」は不要です。というか「rs.Closeをするとエラーが出ます」ので注意が必要です。

４６９行目は４５１行目で「cn.Open」した接続を「cn.Close」させます。これは必須で、接続させたままだとExcelも終了できなくなります。
４７０～４７１行目は４４４～４４５行目で生成したオブジェクトを開放します。

５－４－１８．データベースファイルとやり取り方法２

今回は、Providerに「MSDASQL」を設定（４４７行目）しましたが、他の多くのサイトでは「Microsoft.ACE.OLEDB」 が使われています。
Providerに「Microsoft.ACE.OLEDB」を使用したコードを紹介します。（機能は図５－４３と全く同じです）

473. '==========　⇩(30)　SQL文でのデータベースとのやり取り（ACEプロバイダ）　==================
474. Function SQL_exec2(Sql As String, Optional ReadOnly As Boolean = True) As Variant()
475. 　Dim cn As Object　　　　　　　 '←ｺﾈｸｼｮﾝｵﾌﾞｼﾞｪｸﾄ変数
476. 　Dim rs As Object　　　　　　　 '←ﾚｺｰﾄﾞｾｯﾄｵﾌﾞｼﾞｪｸﾄ変数
477. 　Dim Temp As Variant
478. 　Dim cnt As Long
479. 　Dim i As Long, j As Long　　　 '←得られた配列内のNullを空文字にする為のカウンタ変数
481. 　Set cn = CreateObject("ADODB.Connection")
482. 　Set rs = CreateObject("ADODB.Recordset")
484. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0;"
485. 　cn.ConnectionString = "Data Source=" & FILE_NAME & ";" _
486. 　　　　　　　　　　　　& "Extended Properties=""Excel 12.0;"""
488. 　cn.Open
489. 　　rs.Open Sql, cn
490. 　　　If ReadOnly = True Then
491. 　　　　If rs.EOF = False Then　　　　　 '１つ以上データがあったら
492. 　　　　　Temp = rs.GetRows　　　　　　　 '←全行ﾃﾞｰﾀを配列にまとめて代入
493. 　　　　　For i = 0 To UBound(Temp, 1)　　 '←配列内にNullが存在するとｴﾗｰが出る為空文字に変更
494. 　　　　　　For j = 0 To UBound(Temp, 2)
495. 　　　　　　　If IsNull(Temp(i, j)) Then Temp(i, j) = ""
496. 　　　　　　Next j
497. 　　　　　Next i
498. 　　　　Else　　　　　　　　　　　　　　　 '←摘出ﾃﾞｰﾀが無いの場合
499. 　　　　　ReDim Temp(0 To 0, 0 To 0)　　 '←他の場合と同様に2次元配列を作る（各要素は空）
500. 　　　　End If
501. 　　　　SQL_exec2 = Temp　　　　　　　　　 '←配列を戻り値とする
502. 　　　　rs.Close
503. 　　　End If
504. 　cn.Close
505. 　Set rs = Nothing
506. 　Set cn = Nothing
507. End Function

図５－４６

図５－４３と図５－４６の違いは、赤字の部分のみです。
　４８４行目のProvider値を「MSDASQL」から「Microsoft.ACE.OLEDB.12.0;」に変更。
　４８５～４８６行目の ConnectionString値を変更。
尚、Extended Propertiesの後ろの「Excel 12.0;」は、文字列として１つにまとめるため、二重引用符で囲みます。最後が三重引用符になっているのは、全体を囲っているからです。

６．最後に

複数人で使用するアプリの場合、使用頻度が低いのであれば、データと操作系を１つのブック内に収め、共有フォルダに置いて順番に使ってもらうのが、作る方としても楽です。
それよりも使用頻度が高くなってくると、単一ブックでは「いつも使用中で使えない」という不満が出てきます。その対策として「誰が使っているのか分かる様にする」とファイルの開けっ放しこそ少なくなりますが、不満は消えません。
そのような頻度のアプリには、今回のような「Excelのみで、簡単に複数人で使用できるアプリ」も候補の一つと思います。

データがどんどん溜まってくると恐らく処理速度が落ちてくるので、いずれ本格的RDBへの移行が必要になってくるとは思いますが、それまでの暫定的存在、またはアプリのデモみたいな使い方であれば、充分に使えると思います。

また、今回と似たシステムで「データの検索・挿入・更新の都度、別データブックを開き処理し閉じる」という方法もあります。直接ブックを扱うのでWorkBook_Openに多少時間はかかりますが、セルが見える状態なので「その方が得意」という方もおられると思います。
しかし特定の行（レコード）を取得するのに、「Select ･･･」というSQL文がExcelでも使えることを覚えておくと手法の範囲が広がるかと思います。


会議室予約システム（it-026.xlsm）←各担当者のPCに置く操作ファイル
データベースファイル（K_Data.xlsx）←サーバー等に置くデータファイル

サンプルファイルは単独では使えません。 まず２つのファイルをどこかにコピーし、その場所＋ファイル名を控えて下さい。 次に「会議室予約システム」ファイルの標準モジュールを開き、宣言部の定数FILE_NAME値を「データベースファイル」のフルパスに書き換えてください。 「会議室予約システム」ファイルを保存終了したのち、再立ち上げして下さい。 （保存終了せずに、マクロの「Start」を実行してもアプリが起動します） また、動作制限を緩和するために、標準モジュールの「App_Exit プロシージャ」、UserForm1の「list_make プロシージャ」は、コードを一部コメントアウトしています。

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