2020/10/19
2023/03/24訂正（行の絞り込み）

ExcelシートDBとSQLを使った倉庫管理システム

• １．背景
• ２．システム概要
• 　２－１．今回システムの流れ
• 　２－２．データの入出力方法
• 　　２－２－１．Ａ：空棚確認
• 　　２－２－２．Ｃ：入庫登録
• 　　２－２－３．Ｅ：出庫登録
• 　　２－２－４．Ｆ：長期保管チェック
• 　　２－２－５．Ｈ：在庫品チェック
• 　　２－２－６．Ｊ：入出庫リスト出力
• ３．RDB（リレーショナルデータベース）について
• 　３－１．ExcelシートDBについて
• 　３－２．SQLの考え方
• 　　３－２－１．テーブルの内部結合
• 　　３－２－２．テーブルの外部結合
• 　　３－２－３．行（レコード）の絞り込み
• 　　３－２－４．計算と並べ替え
• 　　３－２－５．行挿入とデータ変更
• ４．今回システムのデータテーブルとフィールド
• 　４－１．入庫テーブル
• 　４－２．出庫テーブル
• 　４－３．倉庫テーブル
• ５．プログラムの流れ
• ６．プログラム内容１（ワークブックモジュール・ワークシート）
• 　６－１．ワークブックモジュール
• 　６－２．ワークシート上のボタンへのマクロ登録
• ７．プログラム内容２（標準モジュール）
• 　７－１．定数・変数宣言
• 　７－２．Ａ：空棚確認
• 　７－３．Ｃ：入庫登録、Ｅ：出庫登録
• 　７－４．Ｆ：長期保管チェック
• 　７－５．Ｈ：在庫品チェック
• 　７－６．Ｊ：入出庫リスト出力
• 　７－７．シート上のデータクリア
• 　７－８．シートへのデータ書き出し
• 　７－９．データベース接続・データ取得
• ８．プログラム内容３（フォームモジュール）
• 　８－１．Ｃ：入庫登録
• 　　８－１－１．フォーム上のコントロールの配置
• 　　８－１－２．フォームのコード
• 　８－２．Ｅ：出庫登録
• 　　８－２－１．フォーム上のコントロールの配置
• 　　８－２－２．フォームのコード
• 　８－３．ExcelシートDBの懸念点
• ９．最後に

１．背景

「倉庫管理システム」と名のつくソフトウェアは、世の中に多く存在します。物流の世界には必須のものですが、基本は「入庫・在庫・出庫」を正確に管理することだと思います。
一方、社内の部課のような小さな単位でも、資料や参考部品・試作品などを管理する場合がありますが、そういう場面では前記のような大きなシステムでは無く、紙での管理だったり融通の利く「Excelによる自作のシステム」の方が、多いかもしれません。

今回は、「Excelシート上にDBを作り、SQLを使ってデータを入出力する」「ExcelシートDBを使った会議室予約システム」でも紹介した「ExcelシートDB」を使った倉庫管理システムを紹介します。

また、以下のシステムも参照下さい。
「共有資料の登録と閲覧ができるサーバーシステム」
「複数の備品を同時予約可能な貸出台帳」

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

２．システム概要

２－１．今回システムの流れ

まず、倉庫管理システムの流れを図２－１に整理しました。
今回の流れは、「依頼者が保有している荷物」はすぐに使わないため「一時的に倉庫に保管」してもらい、必要になったら「倉庫から出庫」する　というものを想定しています。
当然ながら倉庫の保管状況を定期的にチェックし、預けっぱなしを防止したり棚卸をしたりする必要があるはずなので、そのような機能も加えました。

図２－１

Ａ：まず荷物を保有している依頼者は、「自分の荷物が倉庫に入るか否かを調べる」為に「倉庫の空き状況を確認」する。
Ｂ：依頼者は保管先の棚の候補と共に荷物を倉庫へ搬入し、倉庫担当者または依頼者本人が荷物を棚に入れる。
Ｃ：荷物を入れた棚番、及びその他の情報を「入庫登録」する。
Ｄ：その後依頼者は、入庫した荷物が必要になったら倉庫へ行き、倉庫担当者または依頼者本人が荷物を棚から出す。
Ｅ：倉庫担当者または依頼者本人は、出庫した情報を「出庫登録」する。
Ｆ：倉庫担当者は、定期的に「長期保管品が無いかチェック」し、
Ｇ：長期保管品（倉庫に入れたのを忘れている、計画が延びている等）があることを担当者に連絡する。
Ｈ：倉庫担当者は、定期的に「現在の全在庫データ」を出し、荷物と照らし合わせて棚卸をする
Ｊ：倉庫の使用状況を把握するため「入出庫状況データを出力」し、上司などに報告する。

なお、Ｂ・Ｄ・Ｇのステップは、運搬・連絡のため本Excelシステムの対象外としています。
また、入出庫・保管に必要であろう「帳票の出力」や「バーコード・QRコードの表示」などは入っていません。

２－２．データの入出力方法

入出力画面は図２－２のように各ステップ（A～J）のボタンを配置しています。サンプルファイルではSheet2になります。
そのボタンをクリックすることで、シート上に検索結果が表示されたり、ダイアログ内に必要事項を入力しデータ登録したりします。
シートの１行目が薄緑色になっていますが、これは見易いように手動で背景色をつけているだけです。


図２－２

２－２－１．Ａ：空棚確認

まず「倉庫の空き状況を確認」するために、「Ａ(空棚確認)」ボタンをクリックすると、図２－３のように「空いている棚番号」と「棚の容量」が表示されます。今回は「容量（大きさ）」のみを棚の特性としていますが、使われる場面により特性が異なる（例えば、重量・精密品専用・短期用・部署専用など）と思います。

図２－３

２－２－２．Ｃ：入庫登録

「Ｃ(入庫登録)」ボタンをクリックすると図２－４のようなダイアログが表示され、棚番号をリストから選択し、品物・入庫者を入力します。
棚番号は、吹き出し図のように「現在、空棚になっている棚番号」の中から選択します。
尚、入庫番号（InNo)、入庫日（InDate)は自動計算されます。（入庫日＝今日の日付）

図２－４

２－２－３．Ｅ：出庫登録

「Ｅ(出庫登録)」ボタンをクリックすると図２－５のようなダイアログが表示され、入庫番号または棚番号のリストから選択し、出庫者を入力します。
入庫番号と現時点で埋まっている棚番号は１対１でつながりますので、どちらかを選択するともう一方は自動的に選択されるようになっています。また、出庫日（OutDate）は自動計算されます。（出庫日＝今日の日付）

図２－５

２－２－４．Ｆ：長期保管チェック

「Ｆ(長期保管チェック)」ボタンをクリックすると図２－６のようなダイアログが表示され、保管日数を数値入力し「OK」ボタンをクリックすることで、それ以上の期間保管している棚情報が表示されます。

図２－６

２－２－５．Ｈ：在庫品チェック

「Ｈ(在庫品チェック)」ボタンをクリックすると図２－７のように、現在在庫している棚情報が全て表示されます。

図２－７

２－２－６．Ｊ：入出庫リスト出力

「Ｊ(入出庫リスト)」ボタンをクリックすると図２－８のように、入庫と出庫の棚情報が全て表示されます。
尚、出庫日以降の情報が表示されているものが「出庫済み」で、それ以外は入庫したままを表します。

図２－８

３．RDB（リレーショナルデータベース）について

データベースには様々な種類があり、その中の１つがリレーショナルデータベースです。
今回のExcelシートDBもリレーショナルデータベースもどきであり、そのリレーショナルデータベースを操作するのがSQLです。
データベース上のデータはテーブルと言う「二次元の表」の様なものに収められています。テーブルはExcelのシートと似ていますが、データの特定方法はExcelとは異なります。Excelですと「列と行が決まれば、一意にデータが決まる」のですが、リレーショナルデータベースには「何行目のデータ」という考え方では無く、集合的な考え方でデータを扱います。

またデータベース上には複数の「テーブル」が存在し、お互いを連携させてユーザーが求めるデータを導き出します。ですのでテーブル同士をどう結びつけるか、またテーブルの中からどう必要なデータを取り出したり入れたりするかを実現させるために、SQLの知識が必要になります。

３－１．ExcelシートDBについて

ExcelにはADO（ActiveX Data Objects）というオブジェクトが用意されており、それを組み込むことで「外部データベース」にアクセスすることができます。
「外部データベース」としては一般的にはOracleやSQL Server・Accessなどが有名ですが、Excelも「外部データベース」として扱うことが可能です。一般的なデータベースと各部の呼び方は異なりますが、機能は図３－１のように似ていると分かります。

データベースの機能の呼び方

	表の集合体	二次元のデータを入れる表
一般的なRDB(リレーショナルデータベース)	データベース	テーブル
ExcelシートDB	ワークブック	ワークシート

図３－１

Excelシート上のデータを操作するイメージとしては図３－２の様になります。ただし、手法的には様々なやり方があります。

図３－２

まず一番最初にデータベースと接続「Connection（青い線）」する必要があります。
接続するにはに「接続するデータベースの種類（Provider）」と「データベースの場所・名前（Database）」を指定します。
接続できたら、その道を通ってデータ操作の指示「Command（緑色の線）」を行い、その結果「Recordset（赤い線）」を得たらマクロでシート上にデータを出力します。


また、Excelをデータベースとする場合には、一般データベースでは可能な「Delete」命令が使用できません。
その代替策として「削除マーク用のフィールド（列）を設ける」「Updateで削除行のデータをNullで置き換える」等の方法が考えられますが、後者の手法の場合には「Where StNo is not Null」のようにNullを除外しないと「空行も対象になってしまう」ので注意が必要です。

３－２．SQLの考え方

データベースに対する処理は全てSQLを使います。そのSQLはこの頁だけで説明できるようなものでは無く、他のSQL専用サイトや書籍での勉強が必要ですが、ここでは今回システムで使用する簡単なSQLについて以下で説明します。

３－２－１．テーブルの内部結合

リレーショナルデータベースでは、最小単位のデータの集まりが１つのテーブルになるように設計していきます。例えば今回で言えば「入庫データ」「出庫データ」「棚データ」を各１つ１つのテーブルに情報を分け、それぞれのテーブルが他のテーブルの１つのフィールド（列）で重なるようにします。
そうすることで、重なる列を使って小さなテーブルを大きなテーブルに結合することが可能になります。

その２つのテーブルを結合する方法の１つとして「内部結合」があります。ベン図で言うと図３－３の左端のように、テーブルの重なった部分を得ることのできる結合方法です。
内部結合の手法には２種類あり、１つ目は「inner join」を使う方法（図３－３）です。

図３－３

図３－３は、同種類のデータ（例えば、倉庫の棚番号）の列 St を持つテーブルAとテーブルBがあり、A,Bのどちらのテーブルにも存在するSt値を取り出しています。
「on」の後ろの「A.St = B.St」が結合条件になり、テーブルA側のSt値とテーブルB側のSt値が等しい行を結び付けます（図３－３の赤い結合線）。片方のテーブルにしかない値を持つ行は、内部結合の対象からは外れます。
なお「A.St」とは、「テーブルAのSt列」という意味になります。

得られた行は図３－３の一番右側ですが、「Select * from A ･･･」とSelectの後ろの取り出す列名を「*」で「全ての列」と指定していますので、テーブルAとテーブルBのそれぞれ２列全てが出力されています。
これを「Select A.St from A ･･･」とすれば、「テーブルAのSt列」だけが得られることになります。


内部結合の手法の２つ目は「where句で結合条件を与える」方法（図３－４）です。図３－３と結果は同じですが過程が異なります。

図３－４

まず、途中までの「Select * from A,B」という部分では、テーブルAとテーブルBの全ての行の組み合わせを求めています。数学用語では「デカルト積」とか「直積」と呼ばれているようです。
そのデカルト積の表に対して「Where A.St = B.St」の抽出条件を与えますので、「A.St の値と B.St の値が同じ行（図３－４のオレンジ色に塗られた行）」だけが抽出されることになります。
SQL実行の途中で、データベース内部でデカルト積を実際に作るのかは分かりませんが、他サイトでは「行の多いテーブル同士の場合は無駄が多い」や「結合条件をWhereで与えるのは古い書き方」とのコメントがありますので、図３－３の「inner join ～ on」の記述法をお勧めします。

３－２－２．テーブルの外部結合

外部結合は「一方のテーブルのデータを抽出する方法」です。ベン図で表すと図３－５の左端のようになります。
その結合には「outer join ～ on」を使用します。

図３－５

まず外部結合では、２つのテーブルの内どちらを「軸のテーブル」にするかを決めます。例えば図３－５のベン図の場合、オレンジ色に塗ったテーブルAを軸にするのであれば「 A Left outer join B 」又は「B Right outer join A」とします。
つまり「Left」であれば「左側に記述したテーブル」、「Right」であれば「右側に記述したテーブル」が軸となります。

軸となるテーブルは全ての行が得られ、「onの後ろに続く結合条件」に従って軸では無いテーブルの行が軸テーブルの行に結合されます。図３－５では「on A.St = B.St」となっていますので、A･BテーブルのSt列の値を比較して同じであれば結合します（赤い結合線）
一方「on A.St = B.St」が成立しない行（図３－５ではテーブルAの２行目）については相手がいないわけですから、「値が未定」という意味の「Null」が入ります。

「これだと、Select * from A　と何が違うの？」と思われる方もいるかもしれません。違うのは「テーブルBの情報（列）も取り込める」ことです。図３－５のテーブルA･Bで言えば、テーブルBのNo列の情報が結合できるのです。


図３－５の外部結合を少し細工すると「一方のテーブルのみのデータを抽出」することが可能です。ベン図で表すと図３－６の左端です。

図３－６

図３－５で得られた結果の内、２番目のデータはテーブルBに含まれていませんのでテーブルBの情報がNullになっています。このNullであることを使い「Where B.St is Null 」を追加することで、「一方のテーブルのみのデータを抽出」することが出来ます。

３－２－３．行（レコード）の絞り込み

単一テーブルや結合したテーブルから行を絞り込むためには、Where句を使用し続けて条件を以下のように記述します。


①列内の値を比較するには、Excelと同様に「＝」「＞」「＜」を使用します。
②条件式をつなげるときは、Excelと同様に「AND」や「OR」を使用します。
図３－７

③列内の値を複数の値と比較するには、「in」を使用し、その後に続く「( )」内に列挙します。また、列挙の代わりに「値が得られるSQL文（サブクエリー、副問い合わせ）」を記述することも可能です。
④列内の値を複数の値と比較し、「どの要素も含んでいない」場合は「not in」を使用します。後ろに続く「( )」内は「in」の場合と同じです。

図３－８

⑤列内の値を上下限値から絞り込むには、「between 下限値 and 上限値」を使用します。
⑥列内の値に含まれる文字列から絞り込むには、「like」を使用します。「%」や「_（アンダースコア）」を使って前方一致や後方一致などを指定することも可能です。

図３－９

2023.03.24訂正 従来の⑥の絵の中のSQL文を「Select * from A where St like 'A'」と「%」を付け忘れたまま長い間掲載していました。この「like 'A'」だと、単に「'A'」というデータしか抽出できないことになり「A1」や「A2」は抽出対象外となってしまいます。 チェックが良き届かなかった私の単純なミスです。大変申し訳ありませんでした。

３－２－４．計算と並べ替え

⑦単一テーブルや結合したテーブルからWhere句を使って絞り込んだ列の値に対し、最大・最小などの様々な計算をした結果を出力する事が可能です。
⑧出力する際、その出力順序を指定することが可能です。Descを付けると逆順（降順）、Ascを付けるか又は何も付けないと正順（昇順）になります。

図３－１０

３－２－５．行挿入とデータ変更

⑨テーブルに新しい行としてデータを挿入するには「Insert into ～ values」を使用します。一般のデータベースでは列のデータ型を決めることが出来ますので、数値列に文字列を挿入しようとしても制限が掛かりますが、ExcelのシートDBでは混載は可能になってしまいます。データを挿入する前には、データ型のチェックが必要です。
⑩既存のデータを変更するには「Update ～ set」を使用します。
この際にWhere句を付け忘れたりすると、全データが書き換えられてしまいますので充分注意して下さい。
（Excelに備わっている「元に戻す」処理は使えません。）

図３－１１

４．今回システムのデータテーブルとフィールド

サンプルファイルではSheet２を操作用シートにし、Sheet3～Sheet5をデータテーブルにしています。本来は操作するExcelとは離し「データベースのみのExcelブック」としてファイルサーバー等に配置するものだと思います。

４－１．入庫テーブル

入庫テーブル（Sheet３）は、本システムのコード上「InSh」（In用のSheetのつもり）という変数で表されています。
フィールドとしては、「InNo（入庫番号）」「StNo（棚番号）」「InDate（入庫日）」「Goods（品物）」「InName（入庫者）」の５列としています。

図４－１

４－２．出庫テーブル

出庫テーブル（Sheet４）は、本システムのコード上「OutSh」という変数で表されています。
フィールドとしては、「InNo（入庫番号）」「OutDate（出庫日）」「OutName（出庫者）」の３列としています。
「InNo」は、入庫時の入庫番号を使用して情報を結び付けています。また、入庫した人と出庫に来た人が異なる可能性があるので、InNameとOutNameは別扱いにしています。

図４－２

４－３．倉庫テーブル

倉庫テーブル（Sheet５）は、本システムのコード上「StSh」（StはStorageの略のつもり）という変数で表されています。
フィールドとしては、「StNo（棚番号）」「Capa（容量）」の２列としています。
図２－３でも記述しましたが、今回は棚の特性としては「容量（大きさ）」のみにしましたが、状況に合わせて変更・追加して頂ければと思います。

図４－３

５．プログラムの流れ

今回のシステムのプログラムの流れは、図５－１のようになります。

図５－１

操作種類は「(A)空棚確認」「(C)入庫登録」「(E)出庫登録」「(F)長期保管チェック」「(H)在庫品チェック」「(J)入出庫リスト」の６種（シート上の文字削除の「クリアー」も含めると７種）あります。

ボタンの(Ｃ)と(Ｅ)はデータを挿入する「Insert系」です。まずダイアログを表示させ必要事項をユーザーに入力してもらい、入力完了後にSQL文を組み立て「DB呼出し・SQL実行」のプロシージャに渡し、データベースのテーブルにデータを挿入します。

他のボタンは、データを呼び出し表示するだけの「Select系」です。あらかじめ組み立てておいたSQL文を「DB呼出し・SQL実行」プロシージャに渡し、返ってきたデータを整形してからワークシートに書き出しています。（書き出し前に、ワークシート面はクリアします）
但し「(F)長期保管チェック」については、途中InputBoxで「許容保管日数の入力」を求めるようにしています。もし定まった日数があるのであれば固定値にしても良いと思います。

６．プログラム内容１（ワークブックモジュール・ワークシート）

６－１．ワークブックモジュール

ブックを起動させた時に、データベースファイルの場所・ファイル名を変数FILE_NAMEに代入しているのが図６－１です。
今回サンプルファイルとして、１つのブック内に「データベース操作部」と「データベース部」の両方を収めている関係上、「データベースファイルは自分」であることを変数代入しているものです。

1. '==========　⇩①　ブック起動時にデータベースの場所・名前を設定　====================
2. Private Sub Workbook_Open()
3. 　FILE_NAME = ThisWorkbook.Path & "\" & ThisWorkbook.Name
4. End Sub

図６－１

通常でしたら、図７－１の６行目のPublic Const で定数として「データベースExcelファイルの位置・ファイル名」を明記するのが正しいと思います。図７－１の６行目で定数宣言した際には、７行目の変数宣言を削除するのと同時に、ワークブックモジュール（図６－１の３行目）も削除して下さい。

６－２．ワークシート上のボタンへのマクロ登録

ワークシート上の７つのボタンコントロール「(A)空棚確認」「(C)入庫登録」「(E)出庫登録」「(F)長期保管チェック」「(H)在庫品チェック」「(J)入出庫リスト」「クリアー」を配置し、それぞれ、図６－２の如くマクロを登録します。

図６－２

７．プログラム内容２（標準モジュール）

７－１．定数・変数宣言

標準モジュールの先頭でブックプロジェクト全体で使用する定数（及び変数）を宣言しています。（図７－１）

5. '==========　⇩②　Public定数・変数の宣言　====================
6. '// Public Const FILE_NAME As String = "C:¥User¥USER¥excel¥it-041.xlsm"　　 '本当の台帳ファイル名
7. Public FILE_NAME As String　　　　 '台帳ファイル（サンプルファイル用の変数宣言）
9. Public Const InSh As String = " [sheet3$] "　　　　 '入庫台帳シート名
10. Public Const OutSh As String = " [sheet4$] "　　　　 '出庫台帳シート名
11. Public Const StSh As String = " [Sheet5$] "　　　　 '棚台帳シート名

図７－１

６行目は、図６－１のところでも説明した通り、本来のデータベースExcelファイルのパスを定数宣言する場所です。
サンプルファイルではデータベース部と操作部を一つのファイルにしている為、自身のファイルの場所をデータベース先とするために、７行目で変数宣言し図６－１の３行目で自分のパス名を代入しています。

９～１１行目は、今回使用している３つのテーブル（入庫、出庫、棚）が、Sheet３～Sheet５であることの定数宣言です。
データベースファイルを独立させた際にシート名を変更した時には定数名も変更が必要ですし、またデータベースをOracle等一般のデータベースにした際には、この定数に「Public Const InSh As String = " insh "」等と実際のテーブル名を宣言すれば、各SQL文を置き換える必要はありません。

７－２．Ａ：空棚確認

「Ａ：空棚確認」ボタンを押した時に動作するプロシージャが図７－２です。

12. '==========　⇩③　「Ａ：空棚確認」ボタンを押した時　====================
13. Sub SQL_A()
14. 　Dim SQL_01 As String
15. 　Dim SQL As String
17. 　SQL_01 = "Select i.StNo from" & InSh & " i " & " Left Outer Join " & OutSh & " u " & _
18. 　　　　　　　　"on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null"
19. 　SQL = "Select StNo As 空棚番,Capa As 容量 from" & StSh & " where StNo not in (" & SQL_01 & ")"
20. 　Call OutPut(SQL)
21. End Sub

図７－２

このプロシージャは「現在空いている棚」の計算が目的です。手持ちデータは「入庫した棚」「出庫した棚」「全ての棚」ですので、
現在空いている棚 ＝ 「全ての棚」－「現在入庫済の棚」＝「全ての棚」－（「入庫した棚」－「出庫した棚」） という計算式になります。
ですので、まず第一段階として「現在入庫済の棚」＝「入庫した棚」－「出庫した棚」　の計算を行います。

「現在入庫済の棚」を求めるSQL文が１７～１８行目になります。１７～１８行目をダブルクォーテーションや＆記号を削除し文字列だけにすると図７－３の式になります。
なお、テーブル名（InSh、OutSh）は変数のままにしてあり、式の上の赤矢印線と青矢印線は、各テーブルの別名（InShは i 、OutShは u ）を使って、各テーブルのフィールド（列名）を特定していることを示しています。

図７－３

求めたいのは「入庫した棚」－「出庫した棚」ですので、図３－６の③のベン図相当になります。③と少しだけ異なるのは、「入庫しなければ出庫もできない」ことから出庫だけのデータは存在せず、OutShテーブルのみの部分に値が存在しないことです。
ということで、図３－６の③のベン図のSQLである「Left Outer join」＋「Where ～ is Null」を使用します。
このSQL文は「Select i.StNo」となっていることから、「現時点で入庫している棚番」が得られます。

尚、テーブル名が「InSh i」などとなっているのは、「テーブルInShを i という別名に置き換える」という意味です。２つのテーブルに同名のフィールド名がある場合には、どちらのフィールドを扱っているか分かるように必ずテーブル名を付けて区別しますが、長いテーブル名の場合はコードを短縮できます。

また図７－４のように「not in」を使って、「入庫した棚」－「出庫した棚」を求めることも可能です。
（今回は割愛しますが、「not exists」を使っても求めることが出来ます。）

図７－４

先に「出庫したInNo」を求めておき（カッコ内の緑色部分）、入庫データから「出庫したInNo」を取り除く　という方法で、図３－８の④で示した関係です。

１７～１８行目のSQL-01で「現時点で入庫している棚番」が求まったら、次にその値を使って　「全ての棚」－「現在入庫の棚」を求めます。１９行目のSQL文をダブルクォーテーションや＆記号を削除し文字列だけにすると図７－５の式になります。（なお、テーブル名は変数のまま（StSh）にしてあります。）

図７－５

図７－５のベン図自体は、図７－３のベン図と似ているように思われるかもしれません。しかし図７－３は「列と列の比較」、一方図７－５は「列と値の集まりの比較」です。ですので「Outer join ～Where ～is Null」は使えません。
「値の集まり」と比較する場合には、図３－８の③や④を使用することになります。

今回の場合は「現在入庫している棚番号　の内、どの番号も含んでいない棚番号」を求めますので「not in ( )」を使い、図７－５のSQL文になります。
なお、出力するフィールドは「Select StNo As 空棚番,Capa As 容量 」となっているのは、ワークシート上に結果を出力する際に、フィールドが何を表しているのか分かり易くするために、「フィールド名 As 別名」を使って別名で表示しています。
当然ですが、「StNoが棚番号」と充分認知できるのであれば「Select StNo,Capa from ･･･」でも構いませんし、また棚番だけが必要な場合は「Select StNo from ･･･」と棚番のフィールド名だけでもOKです。

２０行目の「Call OutPut(SQL)」は、１７～１９行目で作成したSQL文をOutPutプロシージャに渡すことで、ActiveSheetに結果を表示するようになっています。詳細は図７－１６で説明します。

７－３．Ｃ：入庫登録、Ｅ：出庫登録

入庫登録・出庫登録は、ユーザーの入力情報を元にデータベースのテーブルに行を追加（Insert）する工程です。その入力情報は今回ダイアログ上で選択・入力することにしていますので、入庫専用ダイアログ（UserForm1）・出庫専用ダイアログ（UserForm2）を起動させています。

22. '==========　⇩④　入庫登録　====================
23. Sub SQL_C()
24. 　UserForm1.Show
25. End Sub
26. '==========　⇩⑤　出庫登録　====================
27. Sub SQL_E()
28. 　UserForm2.Show
29. End Sub

図７－６

UserForm内での動作については「8.プログラム内容3（フォームモジュール）」の項で説明します。

７－４．Ｆ：長期保管チェック

「Ｆ：長期保管チェック」ボタンを押した時に動作するプロシージャが図７－７です。

30. '==========　⇩⑥　長期保管チェック　====================
31. Sub SQL_F()
32. 　Dim SQL As String
33. 　Dim Dy As Variant
35. 　Dy = Application.InputBox("何日超の在庫品を抽出しますか？", Type:=1)
36. 　If VarType(Dy) = vbBoolean Then Exit Sub
38. 　SQL = "Select InNo As 入庫番号, StNo As 棚番号,InDate As 入庫日, " & _
39. 　　　　　"Goods As 品物,InName As 入庫者,date()-InDate As 保管日数（日） " & _
40. 　　　　　"from" & InSh & _
41. 　　　　　" where InNo not in (select InNo from" & OutSh & "where InNo is not Null) and date()-InDate > " & Dy
42. 　Call OutPut(SQL)
43. End Sub

図７－７

３５行目では、基準となる保管日数をInputBoxメソッドで取得しています。引数に「Type := 1」を指定していますので、数値以外は受け付けないようにしています。取得した値は変数Dyに代入します。

３６行目では、ユーザーがＯＫボタンをクリックしたのか、キャンセルボタンをクリックしたのかを判別しています。
InputBoxメソッドは、キャンセルをクリックするとFalseを返します。そこで戻り値を受取った変数Dyの型を調べ、Boolean型であるならプロシージャを終了します。
その変数Dyは、３３行目で「Dim Dy As Variant」とVariant型で宣言していますが、もし「数値を受け取るのだから、Dim Dy As Long　と宣言」したりすると、キャンセルをクリックしInputBoxとしてはFalseを戻しても、受ける側の変数DyがLong型なので「False → ゼロ」として受け取ってしまい、「キャンセルなのか、ゼロを入力したのか」が判別できなくなってしまいます。
ですので、InputBoxメソッドの値を受け取る変数は、Variant型で宣言する必要があります。

３８～４１行目は１つのSQL文です。３８～３９行目の部分は出力するフィールド名で、出力後に分かり易くする為に全てに「Asで別名」をつけています。
別名には、半角のアンダースコア「 _ 」や全角は使用できますが、半角のスペースやカッコ、カギカッコなどは使用できません。
３９行目の最後の部分で使っているカッコも全角です。
同時に、長いSQL文は　＆を使って文字列の結合をする場合もあるかと思いますが、つないだ後で「前後の文字がくっつかないように、文字列を囲むダブルクォーテーションの前後には、必ず半角スペースを入れる」ことをお勧めします。

SQL文をダブルクォーテーションや＆記号、別名を削除した文字列だけにすると図７－８の式になります。（なお、テーブル名は変数のまま（InSh、OutSh）にしてあります。）

図７－８

まずカッコ内のサブクエリー「select InNo from OutSh where InNo is not Null」で「出庫テーブルのInNo」を取得し、入庫テーブルのInNoと比較をします。比較の方法が「not in」ですので、「出庫のInNo に含まれない入庫のInNo」つまり「出庫していないInNo」の行を抽出します。
なお「where InNo is not Null」を入れることで、もし出庫テーブル内に空行があった場合でも空行を対象外にし、カッコ内のサブクエリーとしてNullを出さない様にしています。

where句にはもう一つ「date()-InDate > Dy」の条件式があります。
「date()」は「今日の日付」を得る関数ですので、「date()-InDate」は「入庫日から今日までの保管日数」です。
この値をInputBoxで得た基準在庫日数（変数Dy）と比較し、超えている行を抽出することになります。

尚「date()」と似たような関数としては、「now()･･･今日の日付＋現在の時刻」「time()･･･現在の時刻」が使用できますが、今回システムでは時刻には着目していないため「date()」を使っています。

行が抽出できたところで、今度は出力です。Selectの後の選択列リストは「InNo,StNo,InDate,Goods,InName,date()-InDate」となっています。前側の５つは入庫テーブルInShに存在する列（フィールド）ですが、最後の「date()-InDate」は、InDate列と今日の日付との差、つまり保管日数になります。
Excelで６列目の各セルに「=TODAY()-C1」のような計算式を入れているイメージです。
出力の際には別名を付け、「入庫番号,棚番号,入庫日,品物,入庫者,保管日数（日）」という列名にしています。

この３８～４１行目のSQL文は「not in」を使用していますが、外部結合（Outer join）を使っても同じ結果が得られます（図７－９）。

• 　SQL = "Select i.InNo As 入庫番号, i.StNo As 棚番号,InDate As 入庫日, " & _
• 　　　　　"i.Goods As 品物,i.InName As 入庫者,date()-i.InDate As 保管日数（日） " & _
• 　　　　　"from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
• 　　　　　" on i.InNo = u.InNo " & _
• 　　　　　" where u.InNo is Null and date()-i.InDate > " & Dy

図７－９

７－５．Ｈ：在庫品チェック

「Ｈ：在庫品チェック」ボタンを押した時に動作するプロシージャが図７－１０です。
倉庫には定期的な棚卸が必須ですので、この機能を設定しました。

44. '==========　⇩⑦　在庫品チェック・棚卸台帳　====================
45. Sub SQL_H()
46. 　Dim SQL As String
47. 　SQL = "Select InNo As 入庫番号,StNo As 棚番号,InDate As 入庫日,Goods As 品物,InName As 入庫者" & _
48. 　　　　" from " & InSh & _
49. 　　　　"where InNo not in (select InNo from" & OutSh & " where InNo is not Null)"
51. 　Call OutPut(SQL)
52. End Sub

図７－１０

４７～４９行目のSQL文は「現在入庫の棚番号を取得」の図７－４とほぼ一緒で、図７－１１のようになります。


図７－１１

図７－４の時は棚番号だけを得れば良かったのでSelect以下の「選択列リスト」はStNoのみでした。しかし今回は入庫棚の全情報を得るために、図７－１１のSQL文では入庫テーブルの全列名を選択列リストにしています。

もちろん「現在入庫の棚番号を取得」には、外部結合によるSQL文も考えられ、図７－１２のようになります。

図７－１２

７－６．Ｊ：入出庫リスト出力

「Ｊ：入出庫リスト」ボタンを押した時に動作するプロシージャが図７－１３です。

53. '==========　⇩⑧　入出庫リスト出力　====================
54. Sub SQL_J()
55. 　Dim SQL As String
56. 　SQL = "Select i.InNo As 入庫番号,i.StNo As 棚番号,i.InDate As 入庫日,i.Goods As 品物," & _
57. 　　　　"i.InName As 入庫者,u.OutDate As 出庫日,u.OutName As 出庫者 " & _
58. 　　　　" from " & InSh & " i " & "left outer join " & OutSh & " u " & "on i.InNo = u.InNo" & _
59. 　　　　" order by i.InNo"
61. 　Call OutPut(SQL)
62. End Sub

図７－１３

５６～５９行目のSQL文は図７－１４の様になり、外部結合した入庫テーブルInSh＋出庫テーブルOutShに対し、全ての列を出力指定しています。なお入庫番号InNoを昇順で並び揃えています。

図７－１４

並び順を揃えている「Order by句」は図３－１０の⑧でも説明しましたが、「Ascを付けると昇順（正順）」「Descを付けると降順（逆順）」となります。なお既定値はAscのため、何も付けないと昇順（正順）になります。
また、図７－１４では並び順を指定しているのはi.InNoの１つだけですが、複数指定することも可能です。その場合は、先頭ほど並び順の優先度が高くなります。
今回、入庫データを入庫テーブルInShに挿入する時には１から順に挿入していきますので、元々昇順でテーブルが出来上がっています。ですのでExcelシートDBを使っている限りは「Order by句」が無くても、たぶん昇順で結果が得られるとは思います。
しかし一般のリレーショナルデータベースでは順序は保障されませんので、それに倣って「Order by句」を使用しています。
（もし将来データベースをExcelから一般のデータベースに変更した時でも、SQL文がそのまま使えます。）

７－７．シート上のデータクリア

シート上の「クリアー」ボタンを押した時、及び図７－１６のプロシージャを通してワークシートにデータを書き出す時に動作するプロシージャが図７－１５です。

63. '==========　⇩⑨　シート上のデータクリア　====================
64. Sub TableClear()
65. 　With Range("a1").CurrentRegion
66. 　　.Value = ""
67. '　　.NumberFormatLocal = "G/標準"
68. 　　.NumberFormat = "General"
69. 　End With
70. End Sub

図７－１５

今回ワークシート上への書き出しは、「タイトルを１行目」に書き出し（図７－１６の７９行目）、「データを２行目から」書き出す（図７－１６の８０行目）としました（＝タイトルとデータの間に空行は存在しない）。また、データは入庫番号や棚番号など「Nullを含まないデータ」が必ず出力されるようにしました。
ですので、タイトルとデータは必ずつながっている事になるので、タイトル先頭の「A1セルを基準にCurrentRegionで範囲を指定」すればワークシートに書き出されたデータを全て範囲指定できるため、CurrentRegionを使用しています。
６６行目では、そのCurrentRegionでの範囲に対して、データを削除（長さゼロの空文字列を書き込む）しています。

これが「タイトルとデータの間に空行が存在する」や「どのデータ列にもNullを含む可能性がある」場合には、途中で文字列セルの連続が途切れてしまうためCurrentRegionは使用できません。と言ってNullがある可能性があるのであれば、下の方からEnd(xlUp)で最終行を求めることもできず、「SpecialCells(xlCellTypeLastCell)」や「UsedRange」を使用する必要があります。

６８行目では、セル表示形式を標準に戻します。
標準に戻す理由は、例えば前に貼り付けたデータの中に日付データ列があった場合にはセルの表示形式が自動的に日付形式になっているため、新しく貼り付けるデータの型がそれに引きずられて異常な値に見えるのを防ぐためです。
なお表示形式を変更するには、「NumberFormat」プロパティを使用する方法と「NumberFormatLocal」プロパティを使用する方法があります。双方とも同じようなプロパティ値を設定できるのですが、表示形式を標準にする場合は「NumberFormat = "General"」「NumberFormatLocal = "G/標準"」と異なります。
内容は同じですが、６７行目か６８行目のどちらかを使用して下さい。

７－８．シートへのデータ書き出し

各ボタンのプロシージャ内では、SQL文を作成しそれを引数渡しにして図７－１６のOutPutプロシージャを呼び出しています。例えば図７－１３の６１行目の「Call OutPut(SQL)」です。
この実行により、SQL文の実行結果をワークシートに書き出しています。

71. '==========　⇩⑩　シートへのデータ書き出し　====================
72. Sub OutPut(SQL As String)
73. 　Dim temp As Variant
74. 　Dim Fld As Variant
76. 　temp = SQL_exec(SQL, Fld)
78. 　Call TableClear
79. 　Range("a1").Resize(1, UBound(Fld, 1) + 1) = Fld
80. 　Range("a2").Resize(UBound(temp, 2) + 1, UBound(temp, 1) + 1) = Application.WorksheetFunction.Transpose(temp)
81. End Sub

図７－１６

７３～７４行目で変数宣言をしています。変数tempは「SQL実行により得られたデータ配列」、変数Fld（Fieldの意味）は「得られたデータの列名の一次元配列」です。
本当は、列名とデータが一緒になった配列として受け取るようにしたかったのですが、データ取得をするSQL_execプロシージャ（図７－１７）の改造範囲を小さくするために列名とデータを分ける事にしました。

７６行目では、引数として受け取ったSQL文（引数SQL）と戻ってくる列名配列用の変数Fldを引数にして、SQL_execプロシージャ（図７－１７）を呼び出します。
SQL_execからはデータ配列が戻されるので、変数tempとして受け取ります。またデータが戻された時には、引数Fldに列名が代入されています。

７８行目は、TableClearプロシージャ（図７－１５）を呼び出してワークシート上を綺麗にします。
７９行目は、A1セルを基準に列名の配列Fldを貼り付けます。列名の配列は、ゼロから始まる１次元配列として戻すようにしていますので、Resizeでセル範囲調整をする際は「＋１」をしています。

８０行目は列名の１つ下のA2セルを基準に、データ配列であるtempを貼り付けています。配列tempもゼロから始まる２次元配列ですのでResizeでは「行・列とも＋１」をします。
またtemp配列は、行・列が逆転して取得されますので、Transpose関数で行・列が逆転させてから貼付けます。

７－９．データベース接続・データ取得

図７－１６の７６行目、及びデータを挿入するフォームモジュールから実行されるのが図７－１７のSQL_exec関数です。
SQL文で得られる「データ配列を戻す」ことが出発点になっているのでFunctionプロシージャにしています。

82. '==========　⇩⑪　データベース接続・データ取得　====================
83. Function SQL_exec(SQL As String, Fld As Variant, Optional ReadOnly As Boolean = True) As Variant()
84. 　Dim cn As Object　　 '←ｺﾈｸｼｮﾝｵﾌﾞｼﾞｪｸﾄ変数
85. 　Dim rs As Object　　 '←ﾚｺｰﾄﾞｾｯﾄｵﾌﾞｼﾞｪｸﾄ変数
86. 　Dim temp As Variant
87. 　Dim cnt As Long
88. 　Dim i As Long, j As Long　　 '←得られた配列内のNullを空文字にする為のカウンタ変数
90. 　Set cn = CreateObject("ADODB.Connection")
91. 　Set rs = CreateObject("ADODB.Recordset")
93. 　cn.Provider = "MSDASQL"
94. 　cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
95. 　　　　　　　　　　　　& "DBQ=" & FILE_NAME & ";" _
96. 　　　　　　　　　　　　& "ReadOnly=" & ReadOnly & ";"
98. '　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
99. '　cn.ConnectionString = "Data Source=" & FILE_NAME & ";" _
100. '　　　　　　　　　　　　& "Extended Properties=""Excel 12.0;"""
102. 　cn.Open
103. 　　If ReadOnly = True Then
104. 　　　rs.Open SQL, cn
105. 　　　If rs.EOF = False Then
106. 　　　　temp = rs.GetRows　　 '←全行ﾃﾞｰﾀを配列にまとめて代入
107. 　　　　ReDim Fld(0 To UBound(temp, 1))
108. 　　　　For i = 0 To UBound(temp, 1)　　 '←配列内にNullが存在するとｴﾗｰが出る為空文字に変更
109. 　　　　　Fld(i) = rs.Fields(i).Name
110. 　　　　　For j = 0 To UBound(temp, 2)
111. 　　　　　　If IsNull(temp(i, j)) Then temp(i, j) = ""
112. 　　　　　Next j
113. 　　　　Next i
114. 　　　Else 　 '←摘出ﾃﾞｰﾀが無いの場合
115. 　　　　ReDim Fld(0 To 0)
116. 　　　　ReDim temp(0 To 0, 0 To 0)　　 '←他の場合と同様に2次元配列を作る（各要素は空）
117. 　　　End If
118. 　　　SQL_exec = temp　　 '←配列を戻り値とする
119. 　　　rs.Close
120. 　　Else
121. 　　　cn.Execute SQL
122. 　　End If
123. 　cn.Close
125. 　Set rs = Nothing
126. 　Set cn = Nothing
127. End Function

図７－１７

このFunctionプロシージャは、「ExcelシートDBを使った会議室予約システム」で紹介したプロシージャを改造したものです。元のプロシージャは「SQL文で得られる配列データ」を戻り値として戻す内容でしたが、改造後は引数Fld（Fieldの略）として「得られる配列データの列名」を配列として戻すようにしています。
また、Insert･Update文の実行方法について、コードの見直しを行っています。（今回システムではUpdate命令は使用していませんが、使用することも可能ですので、以下「Insert･Update」と記載しています。）

コードの説明の前に、SQL文の実行に必要なADOオブジェクトと、SQL文の種類（Select・Insert・Update）によるオブジェクトの使い方の違いについても説明します。（全てを網羅しているかは分かりませんが、私の分かる範囲でします）

データベースへの操作手順を図７－１８にまとめました。ADOを使う場合には何種類かの方法があります。

図７－１８

まずデータベースに接続（〇印）した後、データベースに対してSQL文を実行（●印）します。そのあと、Select文の場合にはデータが返ってきますので、そのデータ操作（□印）をする必要があります。
一方Insert・Update文の場合はデータベースの値を変更・挿入することで完了し、データベースからデータが返ってくる訳ではありません。ですのでデータ操作（□印）は必要ありませんが、SQLの実行内容を確認（■印）することが可能です。
しかし、図７－１８のパターン６（一番右側）は、実行内容を確認することが出来ないため、この方法はMicrosoftは非推奨としています。

この６種類（Select系３種類＋Insert・Update系３種類）を実際のコードにしたものを下記に示します。
SQL文がSelect文である場合には図７－１９、SQL文がInsert文・Update文である時は図７－２０です。

• '==========　⇩(1)　Select文を実行する方法１　====================
• Dim cn As Object
• Dim rs As Object
• Set cn = CreateObject("ADODB.Connection")
• cn.Provider = プロバイダ指定
• cn.ConnectionString = ドライバーやデータベースファイルの指定
• cn.Open
• 　Set rs = cn.Execute(SQL文)
• 　　（rsオブジェクトを使ってデータ処理）
• cn.Close
• Set cn = Nothing
• Set rs = Nothing
• '==========　⇩(2)　Select文を実行する方法２　====================
• Dim cn As Object
• Dim rs As Object
• Dim cmd As Object
• Set cn = CreateObject("ADODB.Connection")
• Set cmd = CreateObject("ADODB.Command")
• cn.Provider = プロバイダ指定
• cn.ConnectionString = ドライバーやデータベースファイルの指定
• cn.Open
• 　cmd.activeconnection = cn
• 　cmd.CommandText = SQL文
• 　Set rs = cmd.Execute
• 　　（rsオブジェクトを使ってデータ処理）
• cn.Close
• Set cn = Nothing
• Set rs = Nothing
• Set cmd = Nothing
• '==========　⇩(3)　Select文を実行する方法３　====================
• Dim cn As Object
• Dim rs As Object
• Set cn = CreateObject("ADODB.Connection")
• Set rs = CreateObject("ADODB.Recordset")
• cn.Provider = プロバイダ指定
• cn.ConnectionString = ドライバーやデータベースファイルの指定
• cn.Open
• 　rs.Open SQL文, cn
• 　　（rsオブジェクトを使ってデータ処理）
• 　rs.Close
• cn.Close
• Set cn = Nothing
• Set rs = Nothing

図７－１９

まず、データベースにつなげるには、ADODBの「Connectionオブジェクト」をOpenします。これはSQL文が何であっても必要です。
接続時に「cn.Open」で接続を開始し、作業を全て完了した時点で「cn.Close」で接続を終了します。

次に、SQL文（Select文）をデータベース側に伝えるのには、以下３通りあります。
(1)　ConnectionオブジェクトのExecuteメソッドを使用し、引数にSQL文を添付する方法。
(2)　CommandオブジェクトのCommandTextプロパティにSQL文を設定し、その後Executeメソッドを実行する方法。
(3)　RecordSetオブジェクトのOpenメソッド実行時に、引数としてSQL文を添付する方法。

最後に、Select文ではデータを受取って処理する必要があります。その処理は全て「RecordSetオブジェクト」での作業になります。
(1)では、ConnectionオブジェクトのExecuteメソッドでSQL文を渡していますが、その戻り値がRecordSetオブジェクトになりますので、それをSetで受け取り処理を行います。
(2)でも、CommandオブジェクトのExecuteメソッドの戻り値がRecordSetオブジェクトになりますので、それをSetで受け取り処理を行います。
(3)はRecordSetオブジェクトで直接開いていますので、そのままRecordSetオブジェクトで処理をします。

なおRecordSetオブジェクトでの処理が終了した後、(3)ではカーソルを開いているので、RecordSetをCloseさせます。
また、オブジェクトが不要になったら、「Set オブジェクト名 = Nothing」で解放します。

以上のSelect文の処理に対し「Insert・Update文での処理」は、以下になります。

• '==========　⇩(4)　Insert・Update文を実行する方法１　====================
• Dim cn As Object
• Set cn = CreateObject("ADODB.Connection")
• cn.Provider = プロバイダ指定
• cn.ConnectionString = ドライバーやデータベースファイルの指定
• cn.Open
• 　cn.Execute SQL文
• cn.Close
• Set cn = Nothing
• '==========　⇩(5)　Insert・Update文を実行する方法２　====================
• Dim cn As Object
• Dim cmd As Object
• Set cn = CreateObject("ADODB.Connection")
• Set cmd = CreateObject("ADODB.Command")
• cn.Provider = プロバイダ指定
• cn.ConnectionString = ドライバーやデータベースファイルの指定
• cn.Open
• 　cmd.activeconnection = cn
• 　cmd.CommandText = SQL文
• 　cmd.Execute
• cn.Close
• Set cn = Nothing
• Set cmd = Nothing
• '==========　⇩(6)　Insert・Update文を実行する方法３（Microsoftでは「非推奨」）　============
• Dim cn As Object
• Dim rs As Object
• Set cn = CreateObject("ADODB.Connection")
• Set rs = CreateObject("ADODB.Recordset")
• cn.Provider = プロバイダ指定
• cn.ConnectionString = ドライバーやデータベースファイルの指定
• cn.Open
• 　rs.Open SQL文, cn
• cn.Close
• Set cn = Nothing
• Set rs = Nothing

図７－２０

「Insert・Update文」では「取得したデータの処理が不要」になります。ですので(4)(5)で、Connectionオブジェクト・CommandオブジェクトのExecuteメソッドの戻り値をRecordSetオブジェクトとして受け取る必要はありません。
但し、SQL処理内容の確認を行うためにはSelect文の時の様に、SetでRecordSetオブジェクトとして受け取り処理をします。

なお、(6)のRecordSetオブジェクトをOpenさせて「Insert・Update文」などデータを受取らない処理をすると、実行直後に自動的にRecordSetが閉じてしまいます。ですので「OpenしたのだからCloseしないと」と考えて「rs.Close」のコードを入れるとエラーとなります。
Openメソッドでは戻り値を受取れませんので、Microsoftに言わせれば「呼出しが成功したかどうかを判断する簡単な方法がない」ために、RecordSetオブジェクトの「OpenメソッドのSource引数を使用して、レコードを返さないアクションクエリを実行することはお勧めできません」としています。

実のところ私は、(6)の方法でInsert文などをずっと実行してきました。また「ExcelシートDBを使った会議室予約システム」でも(6)の方法を紹介したのですが、これを機に改めたいと思います。
と言う訳で今回サンプルファイルでは、Select文に対しては(3)の方法を、またInsert文等に対しては(4)の方法を採用しました。

では、以上のように複数の手法があることを踏まえた上で、図７－１７のコードを先頭から説明していきます。

まず８３行目の、SQL_exec関数の引数についてです。
第一引数の「SQL」は、実行するSQL文をString型として受け取ります。
第二引数の「Fld」は、今回新設した「列名を収めた配列」を呼出し元に返しています。（呼出し元では、宣言だけした変数Fldを引数として、このFunctionプロシージャに渡す形になります。）
第三引数「ReadOnly」は、データを受取るだけのSelect文の時にはTrueに、テーブルにデータを新規挿入（Insert文）、変更（Update文）する時はFalseが設定されます。（SQL文を解析して自動的に振り分ける方法もあるかとは思いますが、今回は手動でTrue・Falseを設定しています）

９０～９１行目で、ADODBのオブジェクトを生成します。
９０行目は接続のオブジェクトを、９１行目はRecordSetオブジェクトを生成します。
RecordSetとは「SQLコマンドを実行したことで返された結果を操作する」ものなので、SQL文が「Select」の場合は必要ですが、「Insert、Update」の時には不要です。
ですので、９０行目の「cn（Connection）」は必須ですが、９１行目の「rs（RecordSet）」は「SQL文がSelect文と分かってからオブジェクト生成」してもＯＫです（１０３行目と１０４行目の間で実行）。
「変数宣言やオブジェクト生成は、必要になる直前で実行」を推奨する書籍もありますが、私は「宣言や生成は、エリアを決めてまとめて行った方が見易い」と感じているため、Insert文・Update文では無駄になることを承知で９１行目でRecordSetオブジェクトを生成しています。

９３～９６行目では、データベース接続方法などの設定をします。
９３行目でプロバイダーとして「MSDASQL」を使用し、接続文字列（ConnectionString）は９４～９６の様に記述します。

一方、プロバイダ―として「Microsoft.ACE.OLEDB」を使用する方法もあります。
その場合は接続文字列とセットで、見え消しで表示してある９８～１００行目の様に記述します。

１０２行目以降は、実際にデータベースに接続しSQLを実行する部分です。If文とFor～Next文でネストが少し深くなっているので、図７－２１のようにエリア毎に分けて考えます。


図７－２１

まず１０２行目の「cn.Open」で、データベースファイルに接続したのち、１０３行目でSQL文がSelect系なのかInsert・Update系なのかを分けています。
今回は引数であるReadOnlyで仕訳け、「Select系＝引数ReadOnlyがTrue（既定値をTrueにしたため、第三引数が無ければTrue）」である場合は１０４～１１９行目を実行し、「Insert・Update系＝引数ReadOnlyがFalse」の場合は１２１行目を実行します。

他のサイトでも紹介されていますが、SQL文の先頭の文字列から自動的に仕訳けるようにした方がプロシージャとしての汎用性はアップすると思います。

「Select系」では、１０４行目の「rs.Open SQL, cn」で、引数で得られたSQL文を実行します。
Select文を実行すると、なんらかの「データが得られた場合」と何も「データが得られなかった場合」があります。データが得られる場合は「rs.EOF（End of File）」がFalseとなりますので、１０６～１１３行目を実行します。
逆にデータが得られない場合は「rs.EOF」がTrueとなりますので、１１５～１１６行目を実行します。

データがある場合は、１０６行目で全データをまとめて変数tempに代入します。この際、データの行と列は逆転していますし、且つデータがNullの部分についてはNull値が入力されています。また、配列のインデックスはゼロから始まっています。
尚、今回は全データをまとめて取り込みましたが、Do～Loopで１行ずつ取り込む方法もあります（Do～Loopを使って説明しているサイトの方が多い気がします）。
Do～Loopを使うと、配列のインデックスを設計者が自由に出来ますし、行・列の入替えや列同士の入替えも自由になります。また、その場でNullを他の文字列に置き換えたりも出来る利点もあります。
逆に今回の「temp = rs.GetRows」のメリットは、コードがシンプルになるくらいで、あるサイトによると処理速度もGetRowsの方がかえって遅いくらいの様です。もし処理速度が気になるようでしたら、Do～Loopも試してみて下さい。

１０８行目のFor～Nextでは、変数tempの行（＝データベースの列）をｉで回し、１１０行目のFor～Nextでは、変数tempの列（＝データベースの行）をｊで回しています。そして１１１行目で「データがNullだった場合に、長さゼロの文字列に置換」しています。
このNullを置換する作業の必要性ですが、図７－１６の８０行目でTranspose関数で行列を逆転していますが、逆転の際に「配列の中にNullが存在するとエラーが発生」するために、事前に置換しておく必要があるのです。
その中間の１０９行目では、列名を取得し配列Fldに代入しています。

１０８～１１２行目のIf文が二重になってる部分での２つの配列への処理は、例えば５列２行のデータがSQLで得られたとすると図７－２２のように「配列Fldへの名前の代入」→「配列tempのNullチェック」→　･･･　と赤丸数字の順番で処理が行われていきます。

図７－２２


一方、SQL文でデータが無かった場合は、１１５～１１６行目で列名用の配列Fld、データ用の配列tempとも最小サイズの配列をRedimで作成します。Variant型で宣言していますので、配列の中には「Emptyが入っている」という事になります。

１０５～１１７行目のIf文を抜け出し、１１８行目ではデータを入れていた配列tempをプロシージャの戻り値とするために、配列tempをSQL_execに代入します。
なお「配列tempを介せずに、直接SQL_execにデータを入れたら？」と考える方もいるかもしれませんが、今回の場合うまくいきません。SQL_execに書き込んでいるだけでしたら良いのですが、１１１行目でNull値の判定で、自身であるSQL_execの値を呼び出してしまう事になり永久ループとなってしまいます。

データが取り込めたら１１９行目でRecordSetを閉じます。

一方「Insert・Update系」の時は引数ReadOnly=Falseですので、１２１行目の「cn.Execute SQL」で引数で得られたSQL文を実行します。
作業が完了したら、１２３行目でConnectionを閉じます。

今回システムは、ネットワークを経由したファイルサーバー等に存在するデータベースファイルと接続しますので、「ネットワークが遮断されている」「ファイルが移動された」「ファイル名が変更された」などがあると「cn.Open」で接続エラーが発生します。
またSQL文に異常がある場合には、１０４行目の「rs.Open」や１２１行目の「cn.Execute」でエラーが発生します。

しかし今回システムには、エラー処理が組み込まれていません。
SQL文のエラーは事前に試行を繰り返すことで少なくは出来ますが、接続エラーは回避しきれません。ですので図７－１７の１０２行目の「cn.Open」の直前に「On Error Resume Next」を入れ、エラーが発生したら「Functionプロシージャとしてエラー情報を返す」構造にする方法が考えられます。
それと同時にFunctionプロシージャを呼び出す側でも「エラー情報を受けてメッセージを出し、マクロ終了させる」処理が必要になります。
今回は、その工程を盛り込むとコードが非常に読み難くなると判断し、エラー処理は割愛しました。ご了承下さい。

８．プログラム内容３（フォームモジュール）

８－１．Ｃ：入庫登録

「Ｃ：入庫登録」ボタンをクリックすることで、図７－６の２３～２５行目が呼び出されます。その中の２４行目の「UserForm1.Show」により、入庫登録のフォームが呼び出され、入庫情報を入庫テーブル（InSh）に保存していきます。
入庫情報として、空棚番号を含め複数の情報を入れる必要がありますので、専用のダイアログを使ってユーザーに入力してもらうこととしました。

８－１－１．フォーム上のコントロールの配置

フォームの配置は図８－１のようにしました。

図８－１

入庫情報は、ダイアログの上から「入庫番号（InNo）」「棚番号（ShNo）」「入庫日（InDate）」「品物（Goods）」「入庫者（InName）」を並べています。これは入庫テーブルのフィールド順です。
なお、入庫番号・入庫日は自動的に計算した結果をLabelに表示しています。

８－１－２．フォームのコード

８－１－２－１．フォーム表示時の準備

フォーム起動時（Initializeイベント）、フォーム表示時（Activateイベント）のコードは図８－２です。

128. '==========　⇩⑫　フォーム起動時　====================
129. Private Sub UserForm_Initialize()
130. 　Me.Caption = "入庫登録"
131. 　Me.CommandButton1.Caption = "登録実行"
132. 　Me.CommandButton2.Caption = "キャンセル"
133. End Sub
135. '==========　⇩⑬　フォーム表示時　====================
136. Private Sub UserForm_Activate()
137. 　Me.Label6.Caption = maxInNo
138. 　Call makeStList
139. 　Me.Label7.Caption = Date
140. End Sub

図８－２

フォーム初回起動時（Initialize）には、ダイアログ名・各ボタンに文字を表示させています。
フォーム表示時（Activate）には、ユーザーが入力する「品物（Goods）」「入庫者（InName）」以外の項目を表示させます。
まずLabel６には、入庫登録時の入庫番号（仮）を関数maxInNo（図８－３）で計算して表示させます。（１３７行目）
ComboBoxには、makeStListプロシージャ（図８－４）を呼び出して空棚番をリスト表示させます。（１３８行目）
Label７には、入庫登録する日付（＝今日の日付）をDate関数を使って表示させます。（１３９行目）

なお、本システムでは同時に複数のユーザーが入庫登録作業が可能であるため、Label６の入庫番号は「仮の入庫番号」とし、入庫処理を行う直前に正式な入庫番号を取得するようにしています。

またTextBox１とTextBox２は、Activeイベント時に初期化していません。ですので、前回入庫時の入庫情報（品物と入庫担当）をそのまま表示させています。これは「同じものを複数登録」する場合には便利と考えた仕様ですが、「毎回違うもの、違う担当者」が入庫する回数が多いのであれば、手動で削除する手間が増えてしまいます。
状況に合わせて、「Me.TextBox1=""」「Me.TextBox2=""」を追加するか否かを判断して下さい。

８－１－２－２．最新の入庫番号取得

図８－２の１３７行目、及び図８－６の１８０行目から呼び出される「最新入庫番号」を計算するmaxInNo関数が図８－３です。

141. '==========　⇩⑭　最新の入庫番号取得　====================
142. Function maxInNo() As Long
143. 　Dim SQL As String
144. 　Dim temp As Variant
145. 　Dim Fld As Variant
147. 　SQL = "Select max(InNo) from " & InSh
148. 　temp = SQL_exec(SQL, Fld)
149. 　maxInNo = Val(temp(0, 0)) + 1
150. End Function

図８－３

１４７行目のSQL文は、図３－１０の⑦と同じ形で「InShテーブル（入庫テーブル）のInNo列の最大値」を求めるものです。入庫情報を入力する際は常にこのプロシージャで「InNoの最大値＋１」を取得しますので、入庫テーブルのInNo列には１から順に数値が並ぶことになります。

１４８行目で、SQL文をSQL_exec（図７－１７）へ渡し、戻り値としてInNoの最大値を取得します。なおSQL文と共に渡している第二引数のFldは、SQL_execから「列名の配列」を受け取っていますが今回は使用しないので無視します。しかし渡さないといけないので１４５行目で変数宣言だけはしています。

SQL_execから戻り値として受け取った「InNoの最大値」は、二次元配列の（０，０）の位置に収められています。ですので１４９行目ではtemp(0, 0)として取り出し、１を足して関数プロシージャの戻り値とします。

なお、temp(0, 0)には「数値に見えるもの」が入っていることは確かですが、「数値の形をした文字列」かもしれませんので「Val関数」で数値に変更しています。
もし「数値の形をした文字列」のままであっても、「数値の文字列　＋　数値」の計算結果は勝手に数値にしてくれますので問題はないのですが、型を意識しながら計算する事は大切と考え「Val関数」を使用しています。

８－１－２－３．空棚リストをComboBoxに設定

図８－２の１３８行目から呼び出されるのが、図８－４のmakeStListプロシージャです。

151. '==========　⇩　⑮空棚リストをComboBoxに設定　====================
152. Sub makeStList()
153. 　Dim SQL_01 As String
154. 　Dim SQL As String
155. 　Dim temp As Variant
156. 　Dim Fld As Variant
157. 　Dim i As Long
159. 　SQL_01 = "Select i.StNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
160. 　　　　　　　"on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null"
161. 　SQL = "Select StNo from" & StSh & " where StNo not in (" & SQL_01 & ")"
162. 　temp = SQL_exec(SQL, Fld)
164. 　Me.ComboBox1.Clear
165. 　For i = 0 To UBound(temp, 2)
166. 　　Me.ComboBox1.AddItem temp(0, i)
167. 　Next i
168. End Sub

図８－４

１５９～１６１行目のSQL-01とSQLは、「Ａ：空棚確認」ボタンを押した時のSQL文（図７－２の１７～１９行目）とほぼ同じで、入庫テーブルInShと出庫テーブルOutShから現在入庫している棚を割り出し、全棚番号テーブルから差し引いています。
（図７－２の１７～１９行目と異なっているのは、棚容量（Capa列）が無いことと、別名を指定していない事です。）
１６２行目では、そのSQL文を「SQL_exec関数プロシージャ」に渡し、戻り値として「空棚の配列」を変数tempが受け取ります。
第二引数のFldには列名が配列の形で返ってきますが、今回は使用しませんので無視します。

１６４行目では、フォームのCombobox１の従来のリストをクリアします。前回のリストが残ったままだと、その下に続けてリストが作られてしまいますので必須です。
「空棚データ」である配列tempは図８－５のようになっており（ここでは５つの空棚があったとしています）、１６５行目では「UBound(temp,2)」で、データの最大インデックス番号を調べFor～Nextで回しています。

図８－５

１６６行目では、ComboBoxに空棚データを１つ１つ追加しています。「temp(0,i)」となっているのは、配列tempは必ず二次元配列として値を戻してくるようにしてありますので、１列だけのデータでも二次元配列として添字を記述する必要があります。

８－１－２－４．ボタンによる登録実行及びキャンセル

入庫データの各項目を選択・入力後、「登録」ボタンをクリックすると図８－６のCommandButton１_Clickイベントが発生します。また「キャンセル」ボタンをクリックすると、CommandButton２_Clickイベントが発生します。

169. '==========　⇩　⑯登録実行ボタン　====================
170. Private Sub CommandButton1_Click()
171. 　Dim SQL As String
172. 　Dim Fld As Variant
174. 　If listCheck = False Then
175. 　　MsgBox "記入されていない項目があります"
176. 　　Exit Sub
177. 　End If
179. 　SQL = "Insert into " & InSh & " (InNo,StNo,InDate,Goods,InName) values(" & _
180. 　　　　　　　　　maxInNo & "," & _
181. 　　　　　　　　　" ' " & Me.ComboBox1.List(Me.ComboBox1.ListIndex) & " '," & _
182. 　　　　　　　　　" ' " & Me.Label7.Caption & " '," & _
183. 　　　　　　　　　" ' " & Me.TextBox1.Value & " '," & _
184. 　　　　　　　　　" ' " & Me.TextBox2.Value & " ')"
186. 　Call SQL_exec(SQL, Fld, False)
188. 　Me.Hide
189. End Sub
191. '==========　⇩　⑰キャンセルボタン　====================
192. Private Sub CommandButton2_Click()
193. 　Me.Hide
194. End Sub

図８－６

１７４～１７７行目では、ダイアログの各項目を全て入力したか（ComboBoxについては、棚を選択したか）をlistCheck関数（図８－７）で調べ、未選択・未入力項目があれば入庫登録を中止します。
listCheck関数は、全て入力・選択されていればTrueを、１つでも入力・選択が漏れていればFalseを返してくるようにしていますので、その戻り値を受けて「False」であれば１７５行目でコメントを出し、１７６行目でSubプロシージャを抜け出します。
なお、listCheck関数はTrueかFalseのBoolean型を返してくるので、１７４行目の「If listCheck = False Then」の代わりに「If Not listCheck Then」としても同じ動きになります。

全項目が入力・選択されている時は１７９行目以降に進み、まず１７９～１８４行目でSQL文を作成します。
Insert文で挿入するデータは、Values句のカッコ内に並べます。
　InNo 　：maxInNo関数（図８－３ の関数プロシージャ）を実行し、最新のMax番号＋１の数値を設定します。
　StNo 　：ComboBoxでユーザーが選択した棚番を設定します。
　InDate ：ダイアログ表示時に設定した日付をLabel７から取得します。Date関数で最新日付を取得してもＯＫです。
　Goods　：ユーザーが入力した品物名を設定します。
　InName ：ユーザーが入力した入庫担当者を設定します。

なおComboBoxは「Me.ComboBox1.ListIndex」で上から何番目（一番上の項目がゼロ）を選択しているかが分かり、その値を「Me.ComboBox1.List( )」のカッコ内に入れることで、どの値を選択したかが分かります。
また、InNo以外は全て文字列として入力しますので、各値の両側をシングルｸｫｰﾃｰｼｮﾝ（ ' ）で囲みます。

またInsert文では、テーブル名の後ろに別名（エイリアス）を付けるとエラーになりますので、注意が必要です。

完成したSQL文を引数にして、１８６行目でSQL_exec関数プロシージャ（図７－１７）を呼び出します。この時、第三引数にFalseを設定することで「データベースから値を受取らないInsertやUpdateのSQL文」の実行が出来ます。

第二引数への変数Fld（列名の配列が得られる）は必要ですが、ここでは使用しないので無視します。
またSQL_execは関数プロシージャですので「Callで呼び出すのは変だ」と思われる方もいると思います。（私もすごく気になります）
しかしFunctionプロシージャは「値は返すが、戻り値は受け取っても受け取らなくても良い」ので「Call呼出しでもＯＫ」です。
なお気になる方は「temp = SQL_exec(SQL, Fld, False)」等とダミー変数で受け取るコードにする方法も良いと思います。

入庫テーブルへの書込みが完了したら、１８８行目でダイアログを閉じます（Hideなので、実際は隠すだけです）。

一方「キャンセル」ボタンをクリックした時は、何もせずに１９３行目でダイアログを閉じます。

８－１－２－５．入力項目のチェック

入庫登録をする際にダイアログ上で各項目を入力・選択しますが、全ての項目を入力・選択したかをチェックした後データベースにアクセスします。図８－６の１７４行目でそのチェックをしていますが、そこから呼び出されるのが図８－７です。

195. '==========　⇩　⑱ダイアログの必須入力項目に値が入っているか　====================
196. Function listCheck()
197. 　listCheck = (Not Me.ComboBox1.ListIndex = -1) _
198. 　　　　　　　　And (Not Me.TextBox1.Value = "") _
199. 　　　　　　　　And (Not Me.TextBox2.Value = "")
200. End Function

図８－７

８－２．Ｅ：出庫登録

「Ｅ：出庫登録」ボタンをクリックすることで、図７－６の２７～２９行目が呼び出されます。その中の２８行目の「UserForm２.Show」により、出庫登録のフォームが呼び出され、出庫情報を出庫テーブル（OutSh）に保存していきます。
出庫の情報としては、入庫番号を含め複数の情報を入れる必要がありますので、専用のダイアログを使ってユーザーに入力してもらうこととしました。
また、出庫品が入っていた棚番号からも出庫が可能なように工夫をしました。

８－２－１．フォーム上のコントロールの配置

フォームの配置は図８－９のようにしました。

図８－９

出庫のために入力・選択する項目は、ダイアログの上から「棚番号（StNo）」「入庫番号（InNo）」「出庫日（OutDate）」「出庫者（OutName）」が並んでいます。なお出庫登録に必要な情報は、入庫番号以下の３つです。
先頭の棚番号ですが、ここには現在入庫している棚番号をリスト化されており、棚番号を選択すると自動的に入庫番号が選択されるようにしています。また逆に入庫番号を選択すると自動的に棚番号が選択されるようにしています。
なお、出庫日は自動的に計算した結果をLabel５に表示しています。

８－２－２．フォームのコード

８－２－２－１．フォーム表示時の準備

UserForm2のフォーム内で共通に使用する変数の宣言、およびフォーム起動時（Initializeイベント）、フォーム表示時（Activateイベント）のコードは図８－１０です。

201. '==========　⇩⑲　UserForm2 フォームモジュール内変数の宣言　====================
202. Dim ComboBoxFlag As Boolean
204. '==========　⇩⑳　フォーム起動時　====================
205. Private Sub UserForm_Initialize()
206. 　Me.Caption = "出庫登録"
207. 　Me.CommandButton1.Caption = "登録実行"
208. 　Me.CommandButton2.Caption = "キャンセル"
209. End Sub
211. '==========　⇩(21)　フォーム表示時　====================
212. Private Sub UserForm_Activate()
213. 　Call makeStList
214. 　Call makeInList
215. 　Me.Label5.Caption = Date
216. End Sub

図８－１０

この出庫登録ダイアログでは、「棚番号」「入庫番号」のどちらからでもデータを特定できるようにしています。ですので「棚番号を選択したら、入庫番号が自動的に選択」され、逆に「入庫番号を選択したら、棚番号が自動的に選択」されます。
「棚番号」「入庫番号」はComboBox１とComboBox２でリスト化され、それぞれにChangeイベントプロシージャを設けており、イベントが実行されることで相手のリストを選択し直しています。

しかし、これだと少し困ったことが発生します。
１つは図８－１１のように、例えばComboBox１でユーザーが選択すると、もう一方のComboBox２のリストを自動選択します。するとComboBox２の選択値をユーザーが変更したと判断されComboBox２のChangeイベントが発生し、ComboBox１のリストを自動選択し直します。
①②（赤い実線の流れ）は必要ですが、③④（赤い点線の流れ）は不要で、しかもSQLを無駄に実行しています。

図８－１１

もう１つは図８－１２のように、フォームを再表示する時です。
出庫登録が完了したりキャンセルをクリックした後は、ダイアログはHide（隠すだけ）されます。その後再度ダイアログを起動した時にはデータベースの状況が変わっている可能性がありますので、ComboBoxのリストは全て消去してから作り直します。
このリスト消去した時にもChangeイベントが発生しますので、⑥⑦（赤い点線の流れ）の不要な動作が発生します。

図８－１２

これらを防ぐためには「マクロからリスト選択」及び「リストを消去」する時には「Changeイベントを動かさない」ようにしたいのですが、どうしてもイベントは発生してしまいます。（フォームのイベントは、EnableEventsではストップ出来ません）
そこで、Changeイベントを発生させたく無い時は「変数ComboBoxFlag」のフラグを立てる（＝Trueにする）ことにし、Changeイベント側にも「変数ComboBoxFlag」のフラグが立っている時はイベントをすぐに抜け出すようにしています。
その「変数ComboBoxFlag」を２０２行目で宣言しています。

２０５～２０９行目のInitializeイベントでは、フォームの名前、及び２つのボタンの表示を行っています。

２１２～２１６行目のActiveイベントでは、ComboBox１の棚リスト、及びComboBox２の入庫番号リストのリスト作成を行い、２１５行目では今日の日付をLabel５に表示させ「出庫日」のデータとしています。

８－２－２－２．使用中棚番号リストの作成

図８－１０の２１３行目から呼び出されるmakeStListプロシージャが、図８－１３です。
Activeイベントから呼ばれるので、表示の都度（「Ｅ：出庫登録」ボタンを押すたび）実行されます。

217. '==========　⇩(22)　使用中棚番号リストの作成　====================
218. Sub makeStList()
220. 　Dim SQL As String
221. 　Dim temp As Variant
222. 　Dim Fld As Variant
223. 　Dim i As Long
225. 　SQL = "Select i.StNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
226. 　　　　　　　　　"on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null order by i.StNo"
227. 　temp = SQL_exec(SQL, Fld)
229. 　ComboBoxFlag = True
230. 　　Me.ComboBox1.Clear
231. 　ComboBoxFlag = False
232. 　For i = 0 To UBound(temp, 2)
233. 　　Me.ComboBox1.AddItem temp(0, i)
234. 　Next i
235. End Sub

図８－１３

２２５～２２６行目のSQL文は、図７－２の「SQL_01」と同じです。但し、今回はユーザーがリスト選択し易いように「order by i.StNo」で棚番を昇順で並べ替えを行っています。
このSQL文を引数にしSQL_execを実行することで、「現在使用中の棚番のリスト」が配列として取得できます。（２２７行目）

２２９行目の「ComboBoxFlag = True」は、図８－１２で説明した「マクロによる変更でChangeイベントを発生させない」ために、フラグを立てています。
２３０行目でComboBoxのリストをクリアするのは、前回起動した時ComboBoxに作ったリストを消さないと、その下に続けてリストを作ってしまうのを防ぐためです。そして、前回起動時のリストで何かを選択した状態（ListIndexが-1以外）からリストをClearすると、「選択を変更した」と判断されChangeイベントが発生します。
（初回起動時や前回起動していてもリストのどれも選択していない状態では、Clearメソッドを実行してもChangeイベントは発生しません。）

もし２３０行目の「Me.ComboBox1.Clear」でComboBox１のChangeイベントが発生した時には、図８－１５の２５７行目の「If ComboBoxFlag = True Then Exit Sub」ですぐにChangeイベントプロシージャを抜け出し、２３１行目にコードが移ることになります。
２３１行目では、役目を終えた変数ComboBoxFlagのフラグを寝かせます。

２３２～２３４行目は、ComboBoxに棚番号をリスト化します。２２７行目で受け取った配列tempは「二次元配列」ですので、リストにする棚番号はtemp(0,i)という二次元の形で指定する必要があります。

８－２－２－３．入庫番号リストの作成

図８－１０の２１４行目から呼び出されるmakeInListプロシージャが、図８－１４です。
Activeイベントから呼ばれるので、表示の都度（「Ｅ：出庫登録」ボタンを押すたび）実行されます。

236. '==========　⇩(23)　入庫番号リストの作成　====================
237. Sub makeInList()
239. 　Dim SQL As String
240. 　Dim temp As Variant
241. 　Dim Fld As Variant
242. 　Dim i As Long
244. 　SQL = "Select i.InNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
245. 　　　　　　"on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null order by i.InNo"
246. 　temp = SQL_exec(SQL, Fld)
248. 　ComboBoxFlag = True
249. 　　Me.ComboBox2.Clear
250. 　ComboBoxFlag = False
251. 　For i = 0 To UBound(temp, 2)
252. 　　Me.ComboBox2.AddItem temp(0, i)
253. 　Next i
254. End Sub

図８－１４

２４４～２４５行目のSQL文は、makeStListプロシージャのSQL文（図８－１３の２２５～２２６行目）とほぼ同じで、異なるのは棚番号が入庫番号に替わっている事だけです。（Selectの後の列指定と、Order by句の i.StNo → i.InNo に替わっている）
このSQL文を引数にしSQL_execを実行することで、「現在使用中の入庫番号のリスト」が配列として取得できます。（２４６行目）

２４８行目の「ComboBoxFlag = True」は２２９行目と同じ意味で、「マクロによる変更でChangeイベントを発生させない」ようにフラグを立てています。
フラグを立てた後２４９行目でComboBox２のリストをクリアし、Changeイベントをスルーでき後、２５０行目でフラグを寝かせます。

２５１～２５３行目では、ComboBox２に入庫番号のリストを作成します。２４６行目で受け取った配列tempは「二次元配列」ですので、リストにする入庫番号はtemp(0,i)という二次元の形で指定する必要があります。

８－２－２－４．ComboBox１の選択変更時

棚番号のリスト（ComboBox１）の選択を変更した場合には、図８－１５のChangeイベントが発生します。

255. '==========　⇩(24)　棚番号リストの選択変更時　====================
256. Private Sub ComboBox1_Change()
257. 　If ComboBoxFlag = True Then Exit Sub
259. 　Dim selectStNo As String
260. 　Dim i As Long
261. 　Dim SQL As String
262. 　Dim temp As Variant
263. 　Dim Fld As Variant
265. 　If ComboBox1.ListIndex = -1 Then
266. 　　ComboBox2.ListIndex = -1
267. 　Else
268. 　　selectStNo = ComboBox1.List(ComboBox1.ListIndex)
269. 　　SQL = "Select i.InNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
270. 　　　　　　 " on i.InNo = u.InNo where u.InNo is Null and i.StNo='" & selectStNo & "'"
271. 　　temp = SQL_exec(SQL, Fld)
273. 　　For i = 0 To ComboBox2.ListCount - 1
274. 　　　If ComboBox2.List(i) = CStr(temp(0, 0)) Then
275. 　　　　ComboBoxFlag = True
276. 　　　　　ComboBox2.ListIndex = i
277. 　　　　ComboBoxFlag = False
278. 　　　　Exit For
279. 　　　End If
280. 　　Next i
281. 　End If
282. End Sub

図８－１５

２５７行目の「If ComboBoxFlag = True Then Exit Sub」は、「ユーザーがComboBoxを操作してリストの選択を変更した時にはChangeイベントのコードを実行」し、「マクロでリスト操作した時はChangeイベントをスルー」するものです。
そのために図８－１３の２２９行目、図８－１４の２４８行目、及び図８－１５の２７５行目、図８－１８の３０２行目で「ComboBoxFlag = True」とフラグを立ててから、その次の行でリストの操作をしています。

２６５行目の「If ComboBox1.ListIndex = -1 Then」について説明します。
ListBoxでもComboBoxでも、リストが作成された直後は「何も選択されていない状態」（もちろん、マクロで設定すれば選択している状態から始める事が可能）です。その後でユーザーがリストから選択するわけですが、ListBoxの場合は一度選択すると未選択状態に戻せません^*1が、ComboBoxでは選択した値をDelキー等で削除すれば未選択状態にすることが可能です。

*1：ListBoxでも、MultiSelectプロパティを既定の0-fmMultiSelectSingleではなく、1-fmMultiSelectMultiや2-fmMultiSelectExtendedにすれば、選択後でも未選択状態にすることが可能になります。

今回の狙いの１つである「棚番号～入庫番号の連動」は、例えば図８－１６の①ように「棚番号（StNo）をユーザーが選択したら、それに合う入庫番号（InNo）をマクロが選択」する動きにしています。しかしComboBoxを使っていると②のように「選択したものを削除」することも可能となるので、①と同じプログラムでは「空の棚番号からは入庫番号を見つけ出せず」にエラーが発生してしまいます。

図８－１６

ですので、「If ComboBox1.ListIndex = -1 Then」で「リストが選択状態か否か」で場合分けをし、選んでいない（＝削除した）時は２６６行目の「ComboBox2.ListIndex = -1」を実行し、入庫番号側も未選択の状態にしています。

選択状態の場合は、２６８～２８０行目を実行します。
まず２６８行目では、ComboBox１が何を選んでいるかを取得します。選んでいる項目は「リストの上から何番目（LinstIndex）」というインデックス値で取得します。番号はゼロから始まり、未選択の時は －１ になります。
そして、Listプロパティの引数にインデックス値を設定すると、選択している値を得ることが出来、その値を変数selectNoに代入します。

２６９～２７０行目のSQL文は図８－１７の様になっています。入庫テーブルに対し外部結合で出庫テーブルを結合し、現在入庫している行だけを残すために「u.InNo is Null」を条件に入れます（薄いオレンジ色の行）。
その上で「i.StNo = selectNo」の条件で、StNoがComboBox1の選択値である行に絞り込み（濃いオレンジ色の行）、その行の入庫番号（i.InNo）を取得します。

図８－１７

２７１行目では上記で作ったSQL文をSQL_exec関数に渡し、入庫番号の入った配列を配列tempで受け取ります。
２７３～２８０行目のFor～Nextでは、ComboBox２の入庫番号リストを調べています。
まず２７４行目では、ComboBox２のリストの各データと２７１行目で得た入庫番号とを比較し、一致した場合には２７６行目でその行を選択します。
図８－１１の③のところで説明したように「マクロでComboBoxリストを選択した時は、Changeイベントをスルーする」ために、２７５行目で「ComboBoxFlag = True」とフラグを立て、ComboBox２のリストの選択が完了した２７７行目で「ComboBoxFlag = False」とフラグを寝かせています。
その時点では、まだFor～Nextの途中である場合もありますが、既にComboBox２の選択は完了していますので残りを調べる必要はありません。ですので２７８行目の「Exit For」でFor～Nextを抜け出し、処理を終了させます。

８－２－２－５．ComboBox２の選択変更時

入庫番号のリスト（ComboBox２）の選択を変更した場合には、図８－１８のChangeイベントが発生します。

283. '==========　⇩(25)　入庫番号リストの選択変更時　====================
284. Private Sub ComboBox2_Change()
285. 　If ComboBoxFlag = True Then Exit Sub
287. 　Dim selectInNo As String
288. 　Dim i As Long
289. 　Dim SQL As String
290. 　Dim temp As Variant
291. 　Dim Fld As Variant
293. 　If ComboBox2.ListIndex = -1 Then
294. 　　ComboBox1.ListIndex = -1
295. 　Else
296. 　　selectInNo = ComboBox2.List(ComboBox2.ListIndex)
297. 　　SQL = "Select StNo from" & InSh & "where InNo=" & selectInNo
298. 　　temp = SQL_exec(SQL, Fld)
300. 　　For i = 0 To ComboBox1.ListCount - 1
301. 　　　If ComboBox1.List(i) = CStr(temp(0, 0)) Then
302. 　　　　ComboBoxFlag = True
303. 　　　　ComboBox1.ListIndex = i
304. 　　　　ComboBoxFlag = False
305. 　　　　Exit For
306. 　　　End If
307. 　　Next i
308. 　End If
309. End Sub

図８－１８

コードとしては、ComboBox１のChangeイベントプロシージャと似ています。異なるのは以下の３点です。
　１）「ComboBox１ ⇔ ComboBox２」が入れ替わっている
　２）ComboBox２で選択している値を受取る変数がselectInNo（ComboBox１の時はselectStNo）
　３）SQL文が異なる

１）は、ComboBox１とComboBox２との値のやりとりが逆転しているだけなので、理解いただけると思います。
２）は、変数名を変更しなくても問題は発生しないのですが、「名は体を表す」で「選択しているInNo」という意味で名付けました。
３）は、図８－１９で説明します。
今回の入庫テーブルInShの入庫番号列（InNo）はユニークな列（同じ値は無い＋Nullは無い）としていますので、入庫番号が決まれば一意に棚番号が決まることになります。
ですので、入庫テーブル（InSh）単体に対して「where InNo= selectInNo」という条件を与えることで、対応する棚番号が得られます。

図８－１９

２９８行目では上記で作ったSQL文をSQL_exec関数に渡し、棚番号の入った配列を配列tempで受け取ります。
３００～３０７行目のFor～Nextでは、ComboBox１の入庫番号リストを調べています。
まず３０１行目では、ComboBox１のリストの各データと２９８行目で得た棚番号とを比較し、一致した場合には３０３行目でその行を選択します。
図８－１１の③のところで説明したように「マクロでComboBoxリストを選択した時は、Changeイベントをスルーする」ために、３０２行目で「ComboBoxFlag = True」とフラグを立て、ComboBox１のリストの選択が完了した３０４行目で「ComboBoxFlag = False」とフラグを寝かせています。
その時点では、まだFor～Nextの途中である場合もありますが、既にComboBox１の選択は完了していますので残りを調べる必要はありません。ですので３０５行目の「Exit For」でFor～Nextを抜け出し、処理を終了させます。

８－２－２－６．ボタンのクリック時

出庫テーブルにデータを登録するために各項目を入力後、「登録実行」ボタンをクリックすると図８－２０のCommandButton１_Clickイベントが発生します。また「キャンセル」ボタンをクリックするとCommandButton２_Clickイベントが発生します。

310. '==========　⇩(26)　登録実行ボタン　====================
311. Private Sub CommandButton1_Click()
312. 　Dim SQL As String
313. 　Dim Fld As Variant
315. 　If listCheck = False Then
316. 　　MsgBox "記入されていない項目があります"
317. 　　Exit Sub
318. 　End If
320. 　SQL = "Insert into " & OutSh & " (InNo,OutDate,OutName) values( " & _
321. 　　　　　　" ' " & Me.ComboBox2.List(Me.ComboBox2.ListIndex) & " ' , " & _
322. 　　　　　　" ' " & Me.Label5.Caption & " ' , " & _
323. 　　　　　　" ' " & Me.TextBox1.Value & " ' )"
325. 　Call SQL_exec(SQL, Fld, False)
327. 　Me.Hide
328. End Sub
330. '==========　⇩(27)　キャンセルボタン　====================
331. Private Sub CommandButton2_Click()
332. 　Me.Hide
333. End Sub

図８－２０

３１５～３１８行目では、ダイアログの各項目を全て入力したか（ComboBoxについては選択したか）をlistCheck関数（図８－２１）で調べます。
listCheck関数は、全て入力・選択されていればTrueを、１つでも入力・選択が漏れていればFalseを返してくるようにしていますので、その戻り値を受けて「False」であれば３１６行目でコメントを出し、３１７行目でSubプロシージャを抜け出します。

全項目が入力・選択されている時は３２０～３２３行目でSQL文を作成します。
Insert文で挿入するデータは、Values句のカッコ内に並べます。
　InNo 　：ComboBox２でユーザーが選択した入庫番号を指定します。
　OutDate ：ダイアログ表示時に設定した日付をLabel５から取得します。Date関数で最新日付を取得する方法でもＯＫです。
　OutName ：ユーザーが入力した出庫担当者を指定します。

なおComboBoxは「Me.ComboBox2.ListIndex」で上から何番目（一番上の項目がゼロ）を選択しているかが分かり、その値を「Me.ComboBox2.List( )」のカッコ内に入れることで、どの値を選択したかが分かります。
また、InNo以外は全て文字列として入力しますので、各値の両側をシングルｸｫｰﾃｰｼｮﾝ（ ' ）で囲みます。

完成したSQL文を引数にして、３２５行目でSQL_exec関数プロシージャ（図７－１７）を呼び出します。この時、第三引数にFalseを設定することで「データベースから値を受取らないInsertやUpdateのSQL文」の実行が出来ます。
第二引数への変数Fld（列名の配列が得られる）は必要ですが、ここでは使用しないので無視します。
出庫テーブルへの書込みが完了したら、３２７行目でダイアログを閉じます（Hideなので、実際は隠すだけです）。

一方「キャンセル」ボタンをクリックした時は、何もせずに３３２行目でダイアログを閉じます。

８－２－２－７．入力項目のチェック

出庫登録をする際にダイアログ上で各項目を入力・選択しますが、全ての項目を入力・選択したかをチェックした後データベースにアクセスします。図８－２０の３１５行目でそのチェックをしていますが、そこから呼び出されるのが図８－２１です。

334. '==========　⇩(28)　ダイアログの必須入力項目に値が入っているか　====================
335. Function listCheck()
336. 　listCheck = (Not Me.ComboBox2.ListIndex = -1)　And　(Not Me.TextBox1.Value = "")
337. End Function

図８－２１

図８－２１は、入庫登録の際の図８－７とほぼ同等で、出庫時としては２項目をチェックしています。
１つ目は「入力番号の選択」で、ComboBox2を選択していないときは「ListIndex = -1」となりますので、それをNotで逆転しています。
２つ目は「出庫者の入力」でTextBox1の値を調べ、長さゼロの文字列（" "）かを調べ、それをNotで逆転しています。

その２つの条件式を「And」演算子で結合し、両方ともTreであれば関数listCheckとして「True」を戻しています。
なお、ComboBox１（棚番号）が選択されているかを調べても良いのですが、ComboBox２と連動してますし、なにより出庫テーブルには挿入しないデータですので、listCheckの式には入れませんでした。

８－３．ExcelシートDBの懸念点

入庫テーブル・出庫テーブルともに「InNoはユニークなフィールド（列）」と説明しました。行を挿入する直前に最新のInNoを取得し、それに＋１をしてはいますが、本当にほぼ同時に作業された際にはInNoが重複する可能性は否定できません。
それは、ExcelのシートDBでは一般のDBにあるような「列に対するユニーク規制」がかけられないからです。

そのため、頻繁に入出庫をするような倉庫にはExcelシートDBでの対応は難しいかもしれません。
もし対策するとすれば、Insert命令の直後に再度最新InNoとその数（例えば別々な場所から２つ同じInNoのデータが挿入された場合、最新の番号が２つ出来ることになる）を調べ、もし複数個みつかったら挿入したデータを消してから再度挿入を実行する、のようなシステムが必要になりそうです。

入出庫が頻繁でない倉庫でもあり得るわけですから、定期的にテーブルをチェックする必要はあると思います。

９．最後に

以前、複数人で使う業務管理のシステムをExcelで作ったことがあります。データは同じブックの別シートに保存し、他の人が使っていない時に順番で使うような仕様でした。当初はデータ量が少なかったので軽快に動いていたのですが、ユーザーが多くなってくるに従い同時使用不可に不満が爆発、データ量の増大に伴いシステムが重くて開くのに時間がかかりすぎるなど、結局半年も持ちませんでした。
そこでデータはデータベース（その時はExcelデータベースではなく、Oracleを使いました）に移行し、ExcelからSQLで操作する仕様に大改造しました。頼る人もおらず書籍を見ながらトライアンドエラーの日々でした。
でも努力の結果、なんとか同時作業も可能な軽いシステムが完成。しかしシステムが再稼働し始めると次から次へと仕様追加要望が続き、その対応に常に追われる身になってしまいました。

しかしExcelにSQLを取り込むことで、かなりのシステムが作れる実感を私としては得ることができ、それを伝えたいこともあり、このサイトの一番最初の項目は「Excelシート上にDBを作り、SQLを使ってデータを入出力する」にしました。
今回のデータベースはExcelシート上に作成しましたが、Oracle等の一般のデータベースを使用する時には図７－１の定数の変更と図７－１７の９０～１００行目辺りを修正すれば動いてくれるはずです。

一方で今回のSQL文は「Outer join」方式と「not in ( )」方式が入り混じってしまいました。元々は「not in ( )」の方が感覚的に分かり易く良く使っていた気がしますが、今回まとめるに当たって調べ直したところ、「Outer join」が可能ならそれを使った方が良さそうと分かりました。且つ「Outer join」の方が処理速度も速いようです。
ただし本項はExcel内でのSQLを紹介するものであり、初めての方にも興味を持っていただくのが目的のため、分かり易い「not in ( )」方式も織り交ぜながら紹介しました。


ExcelシートDBとSQLを使った倉庫管理システム（it-041.xlsm）

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