2020/10/19

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




1.背景

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

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

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

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

2.システム概要

2-1.今回システムの流れ

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

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

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

2-2.データの入出力方法

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

入出力の画面
図2-2

2-2-1.A:空棚確認

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

2-2-2.C:入庫登録

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

2-2-3.E:出庫登録

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

2-2-4.F:長期保管チェック

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

2-2-5.H:在庫品チェック

「H(在庫品チェック)」ボタンをクリックすると図2-7のように、現在在庫している棚情報が全て表示されます。
在庫品チェック出力画面
図2-7

2-2-6.J:入出庫リスト出力

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

3.RDB(リレーショナルデータベース)について

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

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

3-1.ExcelシートDBについて

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

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

Excelシート上のデータを操作するイメージとしては図3-2の様になります。ただし、手法的には様々なやり方があります。
データベースとExcelの関係
図3-2

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

また、データベース上のデータを操作するには「SQL(Structured Query Language:構造化問い合わせ言語)」を使用します。一般のデータベース(Oracle等)では以下のようなSQL文で操作します。「StSh」がテーブル名、「StNo」「Capa」が列名です。
 Select StNo,Capa from StSh where StNo = 3
しかし図3-1のように、一般的データベースでの「テーブル」の代わりにExcelでは「ワークシート」を使用しますので、Excelをデータベースとする場合にはテーブルの表し方が少し異なり、以下のようなSQL文となります。(シート名に$を追加し、両端を角カッコで囲む)
 Select StNo,Capa from [Sheet5$] where StNo = 3

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

3-2.SQLの考え方

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

3-2-1.テーブルの内部結合

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

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

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

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


内部結合の手法の2つ目は「where句で結合条件を与える」方法(図3-4)です。図3-3と結果は同じですが過程が異なります。
内部結合(Where句を使用する方法)
図3-4

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

3-2-2.テーブルの外部結合

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

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

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

「これだと、Select * from A と何が違うの?」と思われる方もいるかもしれません。違うのは「テーブルBの情報(列)も取り込める」ことです。図3-5のテーブルA・Bで言えば、テーブルBのNo列の情報が結合できるのです。


図3-5の外部結合を少し細工すると「一方のテーブルのみのデータを抽出」することが可能です。ベン図で表すと図3-6の左端です。
外部結合の方法2
図3-6

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

3-2-3.行(レコード)の絞り込み

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


①列内の値を比較するには、Excelと同様に「=」「>」「<」を使用します。
②条件式をつなげるときは、Excelと同様に「AND」や「OR」を使用します。 数値比較、複数条件
図3-7

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

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

3-2-4.計算と並べ替え

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

3-2-5.行挿入とデータ変更

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

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

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

4-1.入庫テーブル

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

4-2.出庫テーブル

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

4-3.倉庫テーブル

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

5.プログラムの流れ

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

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

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

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

6.プログラム内容1(ワークブックモジュール・ワークシート)

6-1.ワークブックモジュール

ブックを起動させた時に、データベースファイルの場所・ファイル名を変数FILE_NAMEに代入しているのが図6-1です。
今回サンプルファイルとして、1つのブック内に「データベース操作部」と「データベース部」の両方を収めている関係上、「データベースファイルは自分」であることを変数代入しているものです。
  1. '========== ⇩① ブック起動時にデータベースの場所・名前を設定 ====================
  2. Private Sub Workbook_Open()
  3.  FILE_NAME = ThisWorkbook.Path & "\" & ThisWorkbook.Name
  4. End Sub
図6-1

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

6-2.ワークシート上のボタンへのマクロ登録

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

7.プログラム内容2(標準モジュール)

7-1.定数・変数宣言

標準モジュールの先頭でブックプロジェクト全体で使用する定数(及び変数)を宣言しています。(図7-1)
  1. '========== ⇩② Public定数・変数の宣言 ====================
  2. '// Public Const FILE_NAME As String = "C:¥User¥USER¥excel¥it-041.xlsm"   '本当の台帳ファイル名
  3. Public FILE_NAME As String     '台帳ファイル(サンプルファイル用の変数宣言)
  4. Public Const InSh As String = " [sheet3$] "     '入庫台帳シート名
  5. Public Const OutSh As String = " [sheet4$] "     '出庫台帳シート名
  6. Public Const StSh As String = " [Sheet5$] "     '棚台帳シート名
図7-1

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

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

7-2.A:空棚確認

「A:空棚確認」ボタンを押した時に動作するプロシージャが図7-2です。
  1. '========== ⇩③ 「A:空棚確認」ボタンを押した時 ====================
  2. Sub SQL_A()
  3.  Dim SQL_01 As String
  4.  Dim SQL As String
  5.  SQL_01 = "Select i.StNo from" & InSh & " i " & " Left Outer Join " & OutSh & " u " & _
  6.         "on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null"
  7.  SQL = "Select StNo As 空棚番,Capa As 容量 from" & StSh & " where StNo not in (" & SQL_01 & ")"
  8.  Call OutPut(SQL)
  9. End Sub
図7-2

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

「現在入庫済の棚」を求めるSQL文が17~18行目になります。17~18行目をダブルクォーテーションや&記号を削除し文字列だけにすると図7-3の式になります。
なお、テーブル名(InSh、OutSh)は変数のままにしてあり、式の上の赤矢印線と青矢印線は、各テーブルの別名(InShは i 、OutShは u )を使って、各テーブルのフィールド(列名)を特定していることを示しています。
現在入庫している棚番号を示すSQL文(外部結合を使う方法)
図7-3

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

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

また図7-4のように「not in」を使って、「入庫した棚」-「出庫した棚」を求めることも可能です。
(今回は割愛しますが、「not exists」を使っても求めることが出来ます。)
現在入庫している棚番号を示すSQL文(not in を使う方法)
図7-4

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

ただし「not in ( )」では、「カッコ内の緑色の式(サブクエリー)から1つでもNull値が出ると、全体のSQL文は空しか得られない」ことになるので注意が必要です。
今回システムでは出庫データのInNo列は埋まっていますが、例えば出庫データを後から削除(不要な行のデータを空行に上書き(Update)する)できる機能を追加したりすると、図7-4の緑色の式ではNullが入ってしまうことになります。
システム上に削除機能がなくても、手動でデータを削除する可能性もありますので、今回は
 図7-3では「Select i.StNo from ・・・ where u.InNo is Null and i.InNo is not Null
 図7-4では「Select InNo From OutSh where InNo is not Null
とNullを排除する式にしています。
なお、手作業で行削除(EntireRow.Delete 相当)でデータを消した場合には、Nullが残りません。

17~18行目のSQL-01で「現時点で入庫している棚番」が求まったら、次にその値を使って 「全ての棚」-「現在入庫の棚」を求めます。19行目のSQL文をダブルクォーテーションや&記号を削除し文字列だけにすると図7-5の式になります。(なお、テーブル名は変数のまま(StSh)にしてあります。)
空いている棚番号を示すSQL文
図7-5

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

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

20行目の「Call OutPut(SQL)」は、17~19行目で作成したSQL文をOutPutプロシージャに渡すことで、ActiveSheetに結果を表示するようになっています。詳細は図7-16で説明します。

7-3.C:入庫登録、E:出庫登録

入庫登録・出庫登録は、ユーザーの入力情報を元にデータベースのテーブルに行を追加(Insert)する工程です。その入力情報は今回ダイアログ上で選択・入力することにしていますので、入庫専用ダイアログ(UserForm1)・出庫専用ダイアログ(UserForm2)を起動させています。
  1. '========== ⇩④ 入庫登録 ====================
  2. Sub SQL_C()
  3.  UserForm1.Show
  4. End Sub
  5. '========== ⇩⑤ 出庫登録 ====================
  6. Sub SQL_E()
  7.  UserForm2.Show
  8. End Sub
図7-6

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

7-4.F:長期保管チェック

「F:長期保管チェック」ボタンを押した時に動作するプロシージャが図7-7です。
  1. '========== ⇩⑥ 長期保管チェック ====================
  2. Sub SQL_F()
  3.  Dim SQL As String
  4.  Dim Dy As Variant
  5.  Dy = Application.InputBox("何日超の在庫品を抽出しますか?", Type:=1)
  6.  If VarType(Dy) = vbBoolean Then Exit Sub
  7.  SQL = "Select InNo As 入庫番号, StNo As 棚番号,InDate As 入庫日, " & _
  8.      "Goods As 品物,InName As 入庫者,date()-InDate As 保管日数(日) " & _
  9.      "from" & InSh & _
  10.      " where InNo not in (select InNo from" & OutSh & "where InNo is not Null) and date()-InDate > " & Dy
  11.  Call OutPut(SQL)
  12. End Sub
図7-7

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

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

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

SQL文をダブルクォーテーションや&記号、別名を削除した文字列だけにすると図7-8の式になります。(なお、テーブル名は変数のまま(InSh、OutSh)にしてあります。)
期限超過している棚を示すSQL文
図7-8

まずカッコ内のサブクエリー「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」となっています。前側の5つは入庫テーブルInShに存在する列(フィールド)ですが、最後の「date()-InDate」は、InDate列と今日の日付との差、つまり保管日数になります。
Excelで6列目の各セルに「=TODAY()-C1」のような計算式を入れているイメージです。
出力の際には別名を付け、「入庫番号,棚番号,入庫日,品物,入庫者,保管日数(日)」という列名にしています。

この38~41行目のSQL文は「not in」を使用していますが、外部結合(Outer join)を使っても同じ結果が得られます(図7-9)。
  •  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
図7-9

7-5.H:在庫品チェック

「H:在庫品チェック」ボタンを押した時に動作するプロシージャが図7-10です。
倉庫には定期的な棚卸が必須ですので、この機能を設定しました。
  1. '========== ⇩⑦ 在庫品チェック・棚卸台帳 ====================
  2. Sub SQL_H()
  3.  Dim SQL As String
  4.  SQL = "Select InNo As 入庫番号,StNo As 棚番号,InDate As 入庫日,Goods As 品物,InName As 入庫者" & _
  5.     " from " & InSh & _
  6.     "where InNo not in (select InNo from" & OutSh & " where InNo is not Null)"
  7.  Call OutPut(SQL)
  8. End Sub
図7-10

47~49行目のSQL文は「現在入庫の棚番号を取得」の図7-4とほぼ一緒で、図7-11のようになります。

在庫品の情報を得るSQL文(not inを使う方法)
図7-11

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

もちろん「現在入庫の棚番号を取得」には、外部結合によるSQL文も考えられ、図7-12のようになります。
在庫品の情報を得るSQL文(外部結合を使う方法)
図7-12

7-6.J:入出庫リスト出力

「J:入出庫リスト」ボタンを押した時に動作するプロシージャが図7-13です。
  1. '========== ⇩⑧ 入出庫リスト出力 ====================
  2. Sub SQL_J()
  3.  Dim SQL As String
  4.  SQL = "Select i.InNo As 入庫番号,i.StNo As 棚番号,i.InDate As 入庫日,i.Goods As 品物," & _
  5.     "i.InName As 入庫者,u.OutDate As 出庫日,u.OutName As 出庫者 " & _
  6.     " from " & InSh & " i " & "left outer join " & OutSh & " u " & "on i.InNo = u.InNo" & _
  7.     " order by i.InNo"
  8.  Call OutPut(SQL)
  9. End Sub
図7-13

56~59行目のSQL文は図7-14の様になり、外部結合した入庫テーブルInSh+出庫テーブルOutShに対し、全ての列を出力指定しています。なお入庫番号InNoを昇順で並び揃えています。
入出庫の全情報を得るSQL文(外部結合)
図7-14

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

7-7.シート上のデータクリア

シート上の「クリアー」ボタンを押した時、及び図7-16のプロシージャを通してワークシートにデータを書き出す時に動作するプロシージャが図7-15です。
  1. '========== ⇩⑨ シート上のデータクリア ====================
  2. Sub TableClear()
  3.  With Range("a1").CurrentRegion
  4.   .Value = ""
  5. '  .NumberFormatLocal = "G/標準"
  6.   .NumberFormat = "General"
  7.  End With
  8. End Sub
図7-15

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

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

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

7-8.シートへのデータ書き出し

各ボタンのプロシージャ内では、SQL文を作成しそれを引数渡しにして図7-16のOutPutプロシージャを呼び出しています。例えば図7-13の61行目の「Call OutPut(SQL)」です。
この実行により、SQL文の実行結果をワークシートに書き出しています。
  1. '========== ⇩⑩ シートへのデータ書き出し ====================
  2. Sub OutPut(SQL As String)
  3.  Dim temp As Variant
  4.  Dim Fld As Variant
  5.  temp = SQL_exec(SQL, Fld)
  6.  Call TableClear
  7.  Range("a1").Resize(1, UBound(Fld, 1) + 1) = Fld
  8.  Range("a2").Resize(UBound(temp, 2) + 1, UBound(temp, 1) + 1) = Application.WorksheetFunction.Transpose(temp)
  9. End Sub
図7-16

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

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

78行目は、TableClearプロシージャ(図7-15)を呼び出してワークシート上を綺麗にします。
79行目は、A1セルを基準に列名の配列Fldを貼り付けます。列名の配列は、ゼロから始まる1次元配列として戻すようにしていますので、Resizeでセル範囲調整をする際は「+1」をしています。

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

7-9.データベース接続・データ取得

図7-16の76行目、及びデータを挿入するフォームモジュールから実行されるのが図7-17のSQL_exec関数です。
SQL文で得られる「データ配列を戻す」ことが出発点になっているのでFunctionプロシージャにしています。
  1. '========== ⇩⑪ データベース接続・データ取得 ====================
  2. Function SQL_exec(SQL As String, Fld As Variant, Optional ReadOnly As Boolean = True) As Variant()
  3.  Dim cn As Object   '←コネクションオブジェクト変数
  4.  Dim rs As Object   '←レコードセットオブジェクト変数
  5.  Dim temp As Variant
  6.  Dim cnt As Long
  7.  Dim i As Long, j As Long   '←得られた配列内のNullを空文字にする為のカウンタ変数
  8.  Set cn = CreateObject("ADODB.Connection")
  9.  Set rs = CreateObject("ADODB.Recordset")
  10.  cn.Provider = "MSDASQL"
  11.  cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
  12.             & "DBQ=" & FILE_NAME & ";" _
  13.             & "ReadOnly=" & ReadOnly & ";"
  14. ' cn.Provider = "Microsoft.ACE.OLEDB.12.0"
  15. ' cn.ConnectionString = "Data Source=" & FILE_NAME & ";" _
  16. '            & "Extended Properties=""Excel 12.0;"""
  17.  cn.Open
  18.   If ReadOnly = True Then
  19.    rs.Open SQL, cn
  20.    If rs.EOF = False Then
  21.     temp = rs.GetRows   '←全行データを配列にまとめて代入
  22.     ReDim Fld(0 To UBound(temp, 1))
  23.     For i = 0 To UBound(temp, 1)   '←配列内にNullが存在するとエラーが出る為空文字に変更
  24.      Fld(i) = rs.Fields(i).Name
  25.      For j = 0 To UBound(temp, 2)
  26.       If IsNull(temp(i, j)) Then temp(i, j) = ""
  27.      Next j
  28.     Next i
  29.    Else '←摘出データが無いの場合
  30.     ReDim Fld(0 To 0)
  31.     ReDim temp(0 To 0, 0 To 0)   '←他の場合と同様に2次元配列を作る(各要素は空)
  32.    End If
  33.    SQL_exec = temp   '←配列を戻り値とする
  34.    rs.Close
  35.   Else
  36.    cn.Execute SQL
  37.   End If
  38.  cn.Close
  39.  Set rs = Nothing
  40.  Set cn = Nothing
  41. End Function
図7-17

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

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

データベースへの操作手順を図7-18にまとめました。ADOを使う場合には何種類かの方法があります。
SQL文と必要オブジェクト
図7-18

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

この6種類(Select系3種類+Insert・Update系3種類)を実際のコードにしたものを下記に示します。
SQL文がSelect文である場合には図7-19、SQL文がInsert文・Update文である時は図7-20です。
  • '========== ⇩(1) Select文を実行する方法1 ====================
  • 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文を実行する方法2 ====================
  • 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文を実行する方法3 ====================
  • 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
図7-19

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

次に、SQL文(Select文)をデータベース側に伝えるのには、以下3通りあります。
(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文を実行する方法1 ====================
  • 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文を実行する方法2 ====================
  • 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文を実行する方法3(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
図7-20

「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)の方法を採用しました。

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

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

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

93~96行目では、データベース接続方法などの設定をします。
93行目でプロバイダーとして「MSDASQL」を使用し、接続文字列(ConnectionString)は94~96の様に記述します。

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

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

SQL文を実行する時の流れ
図7-21

まず102行目の「cn.Open」で、データベースファイルに接続したのち、103行目でSQL文がSelect系なのかInsert・Update系なのかを分けています。
今回は引数であるReadOnlyで仕訳け、「Select系=引数ReadOnlyがTrue(既定値をTrueにしたため、第三引数が無ければTrue)」である場合は104~119行目を実行し、「Insert・Update系=引数ReadOnlyがFalse」の場合は121行目を実行します。

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

「Select系」では、104行目の「rs.Open SQL, cn」で、引数で得られたSQL文を実行します。
Select文を実行すると、なんらかの「データが得られた場合」と何も「データが得られなかった場合」があります。データが得られる場合は「rs.EOF(End of File)」がFalseとなりますので、106~113行目を実行します。
逆にデータが得られない場合は「rs.EOF」がTrueとなりますので、115~116行目を実行します。

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

108行目のFor~Nextでは、変数tempの行(=データベースの列)をiで回し、110行目のFor~Nextでは、変数tempの列(=データベースの行)をjで回しています。そして111行目で「データがNullだった場合に、長さゼロの文字列に置換」しています。
このNullを置換する作業の必要性ですが、図7-16の80行目でTranspose関数で行列を逆転していますが、逆転の際に「配列の中にNullが存在するとエラーが発生」するために、事前に置換しておく必要があるのです。
その中間の109行目では、列名を取得し配列Fldに代入しています。

108~112行目のIf文が二重になってる部分での2つの配列への処理は、例えば5列2行のデータがSQLで得られたとすると図7-22のように「配列Fldへの名前の代入」→「配列tempのNullチェック」→ ・・・ と赤丸数字の順番で処理が行われていきます。
Select文を実行する時の流れ
図7-22


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

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

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

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

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

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

8.プログラム内容3(フォームモジュール)

8-1.C:入庫登録

「C:入庫登録」ボタンをクリックすることで、図7-6の23~25行目が呼び出されます。その中の24行目の「UserForm1.Show」により、入庫登録のフォームが呼び出され、入庫情報を入庫テーブル(InSh)に保存していきます。
入庫情報として、空棚番号を含め複数の情報を入れる必要がありますので、専用のダイアログを使ってユーザーに入力してもらうこととしました。

8-1-1.フォーム上のコントロールの配置

フォームの配置は図8-1のようにしました。
入庫登録用フォーム内のコントロールの配置
図8-1

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

8-1-2.フォームのコード

8-1-2-1.フォーム表示時の準備
フォーム起動時(Initializeイベント)、フォーム表示時(Activateイベント)のコードは図8-2です。
  1. '========== ⇩⑫ フォーム起動時 ====================
  2. Private Sub UserForm_Initialize()
  3.  Me.Caption = "入庫登録"
  4.  Me.CommandButton1.Caption = "登録実行"
  5.  Me.CommandButton2.Caption = "キャンセル"
  6. End Sub
  7. '========== ⇩⑬ フォーム表示時 ====================
  8. Private Sub UserForm_Activate()
  9.  Me.Label6.Caption = maxInNo
  10.  Call makeStList
  11.  Me.Label7.Caption = Date
  12. End Sub
図8-2

フォーム初回起動時(Initialize)には、ダイアログ名・各ボタンに文字を表示させています。
フォーム表示時(Activate)には、ユーザーが入力する「品物(Goods)」「入庫者(InName)」以外の項目を表示させます。
まずLabel6には、入庫登録時の入庫番号(仮)を関数maxInNo(図8-3)で計算して表示させます。(137行目)
ComboBoxには、makeStListプロシージャ(図8-4)を呼び出して空棚番をリスト表示させます。(138行目)
Label7には、入庫登録する日付(=今日の日付)をDate関数を使って表示させます。(139行目)

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

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

8-1-2-2.最新の入庫番号取得
図8-2の137行目、及び図8-6の180行目から呼び出される「最新入庫番号」を計算するmaxInNo関数が図8-3です。
  1. '========== ⇩⑭ 最新の入庫番号取得 ====================
  2. Function maxInNo() As Long
  3.  Dim SQL As String
  4.  Dim temp As Variant
  5.  Dim Fld As Variant
  6.  SQL = "Select max(InNo) from " & InSh
  7.  temp = SQL_exec(SQL, Fld)
  8.  maxInNo = Val(temp(0, 0)) + 1
  9. End Function
図8-3

147行目のSQL文は、図3-10の⑦と同じ形で「InShテーブル(入庫テーブル)のInNo列の最大値」を求めるものです。入庫情報を入力する際は常にこのプロシージャで「InNoの最大値+1」を取得しますので、入庫テーブルのInNo列には1から順に数値が並ぶことになります。

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

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

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

8-1-2-3.空棚リストをComboBoxに設定
図8-2の138行目から呼び出されるのが、図8-4のmakeStListプロシージャです。
  1. '========== ⇩ ⑮空棚リストをComboBoxに設定 ====================
  2. Sub makeStList()
  3.  Dim SQL_01 As String
  4.  Dim SQL As String
  5.  Dim temp As Variant
  6.  Dim Fld As Variant
  7.  Dim i As Long
  8.  SQL_01 = "Select i.StNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
  9.        "on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null"
  10.  SQL = "Select StNo from" & StSh & " where StNo not in (" & SQL_01 & ")"
  11.  temp = SQL_exec(SQL, Fld)
  12.  Me.ComboBox1.Clear
  13.  For i = 0 To UBound(temp, 2)
  14.   Me.ComboBox1.AddItem temp(0, i)
  15.  Next i
  16. End Sub
図8-4

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

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

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

8-1-2-4.ボタンによる登録実行及びキャンセル
入庫データの各項目を選択・入力後、「登録」ボタンをクリックすると図8-6のCommandButton1_Clickイベントが発生します。また「キャンセル」ボタンをクリックすると、CommandButton2_Clickイベントが発生します。
  1. '========== ⇩ ⑯登録実行ボタン ====================
  2. Private Sub CommandButton1_Click()
  3.  Dim SQL As String
  4.  Dim Fld As Variant
  5.  If listCheck = False Then
  6.   MsgBox "記入されていない項目があります"
  7.   Exit Sub
  8.  End If
  9.  SQL = "Insert into " & InSh & " (InNo,StNo,InDate,Goods,InName) values(" & _
  10.          maxInNo & "," & _
  11.          " ' " & Me.ComboBox1.List(Me.ComboBox1.ListIndex) & " '," & _
  12.          " ' " & Me.Label7.Caption & " '," & _
  13.          " ' " & Me.TextBox1.Value & " '," & _
  14.          " ' " & Me.TextBox2.Value & " ')"
  15.  Call SQL_exec(SQL, Fld, False)
  16.  Me.Hide
  17. End Sub
  18. '========== ⇩ ⑰キャンセルボタン ====================
  19. Private Sub CommandButton2_Click()
  20.  Me.Hide
  21. End Sub
図8-6

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

全項目が入力・選択されている時は179行目以降に進み、まず179~184行目でSQL文を作成します。
Insert文で挿入するデータは、Values句のカッコ内に並べます。
 InNo  :maxInNo関数(図8-3 の関数プロシージャ)を実行し、最新のMax番号+1の数値を設定します。
 StNo  :ComboBoxでユーザーが選択した棚番を設定します。
 InDate :ダイアログ表示時に設定した日付をLabel7から取得します。Date関数で最新日付を取得してもOKです。
 Goods :ユーザーが入力した品物名を設定します。
 InName :ユーザーが入力した入庫担当者を設定します。

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

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

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

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

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

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

8-1-2-5.入力項目のチェック
入庫登録をする際にダイアログ上で各項目を入力・選択しますが、全ての項目を入力・選択したかをチェックした後データベースにアクセスします。図8-6の174行目でそのチェックをしていますが、そこから呼び出されるのが図8-7です。
  1. '========== ⇩ ⑱ダイアログの必須入力項目に値が入っているか ====================
  2. Function listCheck()
  3.  listCheck = (Not Me.ComboBox1.ListIndex = -1) _
  4.         And (Not Me.TextBox1.Value = "") _
  5.         And (Not Me.TextBox2.Value = "")
  6. End Function
図8-7

図8-7のlistCheck関数は、3つの項目のチェックを行っています。
1つ目は197行目の「棚番号を選択したか」です。
ComboBoxを選択していない時は、ListIndex値は「-1」になる(一度選んでも、その文字列をDelteキーで削除した時も同じです)ので、棚番号を選択していない時は「Me.ComboBox1.ListIndex = -1」の式が成立(=True)します。
チェック式は「Not Me.ComboBox1.ListIndex = -1」ですので、逆転し「棚番号を選択していない時は、False」になります。

2つ目は198行目のTextBox1(商品)です。
入力していない状態では、TextBox1.Valueは長さゼロの文字列("")となります。ですので「TextBox1.Value = ""」の部分が成立(=True)します。チェック式は「Not Me.TextBox1.Value = ""」ですので、逆転し「入力していない時は、False」になります。
3つ目は199行目のTextBox2(入庫者)で、TextBox1の時と全く同じです。

197~199行目は、上記の3つの判断式を「And」演算子で結合しています。つまり3つの理論積を求めているわけで、簡単に言うと「Falseはゼロ、Trueは1」として「3つの掛け算をする」ことになります。
これを表にしてみると図8-8のようになります。
3項目全てがTrueにならないと全体がTrueにならない表
図8-8

3つを掛け算するのですから、その中に「1つでもゼロ」があったら「掛け算の結果はゼロ」になります。ですので「全てがTrue(=入力されている)の時のみTrue」になります。
listCheck関数プロシージャとしては、そのTrueかFalseを戻り値として返します。


8-2.E:出庫登録

「E:出庫登録」ボタンをクリックすることで、図7-6の27~29行目が呼び出されます。その中の28行目の「UserForm2.Show」により、出庫登録のフォームが呼び出され、出庫情報を出庫テーブル(OutSh)に保存していきます。
出庫の情報としては、入庫番号を含め複数の情報を入れる必要がありますので、専用のダイアログを使ってユーザーに入力してもらうこととしました。
また、出庫品が入っていた棚番号からも出庫が可能なように工夫をしました。

8-2-1.フォーム上のコントロールの配置

フォームの配置は図8-9のようにしました。
出庫登録用フォーム内のコントロールの配置
図8-9

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

8-2-2.フォームのコード

8-2-2-1.フォーム表示時の準備
UserForm2のフォーム内で共通に使用する変数の宣言、およびフォーム起動時(Initializeイベント)、フォーム表示時(Activateイベント)のコードは図8-10です。
  1. '========== ⇩⑲ UserForm2 フォームモジュール内変数の宣言 ====================
  2. Dim ComboBoxFlag As Boolean
  3. '========== ⇩⑳ フォーム起動時 ====================
  4. Private Sub UserForm_Initialize()
  5.  Me.Caption = "出庫登録"
  6.  Me.CommandButton1.Caption = "登録実行"
  7.  Me.CommandButton2.Caption = "キャンセル"
  8. End Sub
  9. '========== ⇩(21) フォーム表示時 ====================
  10. Private Sub UserForm_Activate()
  11.  Call makeStList
  12.  Call makeInList
  13.  Me.Label5.Caption = Date
  14. End Sub
図8-10

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

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

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

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

205~209行目のInitializeイベントでは、フォームの名前、及び2つのボタンの表示を行っています。

212~216行目のActiveイベントでは、ComboBox1の棚リスト、及びComboBox2の入庫番号リストのリスト作成を行い、215行目では今日の日付をLabel5に表示させ「出庫日」のデータとしています。

8-2-2-2.使用中棚番号リストの作成
図8-10の213行目から呼び出されるmakeStListプロシージャが、図8-13です。
Activeイベントから呼ばれるので、表示の都度(「E:出庫登録」ボタンを押すたび)実行されます。
  1. '========== ⇩(22) 使用中棚番号リストの作成 ====================
  2. Sub makeStList()
  3.  Dim SQL As String
  4.  Dim temp As Variant
  5.  Dim Fld As Variant
  6.  Dim i As Long
  7.  SQL = "Select i.StNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
  8.          "on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null order by i.StNo"
  9.  temp = SQL_exec(SQL, Fld)
  10.  ComboBoxFlag = True
  11.   Me.ComboBox1.Clear
  12.  ComboBoxFlag = False
  13.  For i = 0 To UBound(temp, 2)
  14.   Me.ComboBox1.AddItem temp(0, i)
  15.  Next i
  16. End Sub
図8-13

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

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

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

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

8-2-2-3.入庫番号リストの作成
図8-10の214行目から呼び出されるmakeInListプロシージャが、図8-14です。
Activeイベントから呼ばれるので、表示の都度(「E:出庫登録」ボタンを押すたび)実行されます。
  1. '========== ⇩(23) 入庫番号リストの作成 ====================
  2. Sub makeInList()
  3.  Dim SQL As String
  4.  Dim temp As Variant
  5.  Dim Fld As Variant
  6.  Dim i As Long
  7.  SQL = "Select i.InNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
  8.       "on i.InNo = u.InNo where u.InNo is Null and i.InNo is not Null order by i.InNo"
  9.  temp = SQL_exec(SQL, Fld)
  10.  ComboBoxFlag = True
  11.   Me.ComboBox2.Clear
  12.  ComboBoxFlag = False
  13.  For i = 0 To UBound(temp, 2)
  14.   Me.ComboBox2.AddItem temp(0, i)
  15.  Next i
  16. End Sub
図8-14

244~245行目のSQL文は、makeStListプロシージャのSQL文(図8-13の225~226行目)とほぼ同じで、異なるのは棚番号が入庫番号に替わっている事だけです。(Selectの後の列指定と、Order by句の i.StNo → i.InNo に替わっている)
このSQL文を引数にしSQL_execを実行することで、「現在使用中の入庫番号のリスト」が配列として取得できます。(246行目)

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

251~253行目では、ComboBox2に入庫番号のリストを作成します。246行目で受け取った配列tempは「二次元配列」ですので、リストにする入庫番号はtemp(0,i)という二次元の形で指定する必要があります。

8-2-2-4.ComboBox1の選択変更時
棚番号のリスト(ComboBox1)の選択を変更した場合には、図8-15のChangeイベントが発生します。
  1. '========== ⇩(24) 棚番号リストの選択変更時 ====================
  2. Private Sub ComboBox1_Change()
  3.  If ComboBoxFlag = True Then Exit Sub
  4.  Dim selectStNo As String
  5.  Dim i As Long
  6.  Dim SQL As String
  7.  Dim temp As Variant
  8.  Dim Fld As Variant
  9.  If ComboBox1.ListIndex = -1 Then
  10.   ComboBox2.ListIndex = -1
  11.  Else
  12.   selectStNo = ComboBox1.List(ComboBox1.ListIndex)
  13.   SQL = "Select i.InNo from" & InSh & " i " & " Left outer join " & OutSh & " u " & _
  14.        " on i.InNo = u.InNo where u.InNo is Null and i.StNo='" & selectStNo & "'"
  15.   temp = SQL_exec(SQL, Fld)
  16.   For i = 0 To ComboBox2.ListCount - 1
  17.    If ComboBox2.List(i) = CStr(temp(0, 0)) Then
  18.     ComboBoxFlag = True
  19.      ComboBox2.ListIndex = i
  20.     ComboBoxFlag = False
  21.     Exit For
  22.    End If
  23.   Next i
  24.  End If
  25. End Sub
図8-15

257行目の「If ComboBoxFlag = True Then Exit Sub」は、「ユーザーがComboBoxを操作してリストの選択を変更した時にはChangeイベントのコードを実行」し、「マクロでリスト操作した時はChangeイベントをスルー」するものです。
そのために図8-13の229行目、図8-14の248行目、及び図8-15の275行目、図8-18の302行目で「ComboBoxFlag = True」とフラグを立ててから、その次の行でリストの操作をしています。

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

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

今回の狙いの1つである「棚番号~入庫番号の連動」は、例えば図8-16の①ように「棚番号(StNo)をユーザーが選択したら、それに合う入庫番号(InNo)をマクロが選択」する動きにしています。しかしComboBoxを使っていると②のように「選択したものを削除」することも可能となるので、①と同じプログラムでは「空の棚番号からは入庫番号を見つけ出せず」にエラーが発生してしまいます。
ComboBoxのChangeイベントで対応する内容は2種類ある
図8-16

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

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

269~270行目のSQL文は図8-17の様になっています。入庫テーブルに対し外部結合で出庫テーブルを結合し、現在入庫している行だけを残すために「u.InNo is Null」を条件に入れます(薄いオレンジ色の行)。
その上で「i.StNo = selectNo」の条件で、StNoがComboBox1の選択値である行に絞り込み(濃いオレンジ色の行)、その行の入庫番号(i.InNo)を取得します。
棚番号から入庫番号を得るSQL文
図8-17

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

8-2-2-5.ComboBox2の選択変更時
入庫番号のリスト(ComboBox2)の選択を変更した場合には、図8-18のChangeイベントが発生します。
  1. '========== ⇩(25) 入庫番号リストの選択変更時 ====================
  2. Private Sub ComboBox2_Change()
  3.  If ComboBoxFlag = True Then Exit Sub
  4.  Dim selectInNo As String
  5.  Dim i As Long
  6.  Dim SQL As String
  7.  Dim temp As Variant
  8.  Dim Fld As Variant
  9.  If ComboBox2.ListIndex = -1 Then
  10.   ComboBox1.ListIndex = -1
  11.  Else
  12.   selectInNo = ComboBox2.List(ComboBox2.ListIndex)
  13.   SQL = "Select StNo from" & InSh & "where InNo=" & selectInNo
  14.   temp = SQL_exec(SQL, Fld)
  15.   For i = 0 To ComboBox1.ListCount - 1
  16.    If ComboBox1.List(i) = CStr(temp(0, 0)) Then
  17.     ComboBoxFlag = True
  18.     ComboBox1.ListIndex = i
  19.     ComboBoxFlag = False
  20.     Exit For
  21.    End If
  22.   Next i
  23.  End If
  24. End Sub
図8-18

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

1)は、ComboBox1とComboBox2との値のやりとりが逆転しているだけなので、理解いただけると思います。
2)は、変数名を変更しなくても問題は発生しないのですが、「名は体を表す」で「選択しているInNo」という意味で名付けました。
3)は、図8-19で説明します。
今回の入庫テーブルInShの入庫番号列(InNo)はユニークな列(同じ値は無い+Nullは無い)としていますので、入庫番号が決まれば一意に棚番号が決まることになります。
ですので、入庫テーブル(InSh)単体に対して「where InNo= selectInNo」という条件を与えることで、対応する棚番号が得られます。
入庫番号から棚番号を得るSQL文
図8-19

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

8-2-2-6.ボタンのクリック時
出庫テーブルにデータを登録するために各項目を入力後、「登録実行」ボタンをクリックすると図8-20のCommandButton1_Clickイベントが発生します。また「キャンセル」ボタンをクリックするとCommandButton2_Clickイベントが発生します。
  1. '========== ⇩(26) 登録実行ボタン ====================
  2. Private Sub CommandButton1_Click()
  3.  Dim SQL As String
  4.  Dim Fld As Variant
  5.  If listCheck = False Then
  6.   MsgBox "記入されていない項目があります"
  7.   Exit Sub
  8.  End If
  9.  SQL = "Insert into " & OutSh & " (InNo,OutDate,OutName) values( " & _
  10.       " ' " & Me.ComboBox2.List(Me.ComboBox2.ListIndex) & " ' , " & _
  11.       " ' " & Me.Label5.Caption & " ' , " & _
  12.       " ' " & Me.TextBox1.Value & " ' )"
  13.  Call SQL_exec(SQL, Fld, False)
  14.  Me.Hide
  15. End Sub
  16. '========== ⇩(27) キャンセルボタン ====================
  17. Private Sub CommandButton2_Click()
  18.  Me.Hide
  19. End Sub
図8-20

315~318行目では、ダイアログの各項目を全て入力したか(ComboBoxについては選択したか)をlistCheck関数(図8-21)で調べます。
listCheck関数は、全て入力・選択されていればTrueを、1つでも入力・選択が漏れていればFalseを返してくるようにしていますので、その戻り値を受けて「False」であれば316行目でコメントを出し、317行目でSubプロシージャを抜け出します。

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

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

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

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

8-2-2-7.入力項目のチェック
出庫登録をする際にダイアログ上で各項目を入力・選択しますが、全ての項目を入力・選択したかをチェックした後データベースにアクセスします。図8-20の315行目でそのチェックをしていますが、そこから呼び出されるのが図8-21です。
  1. '========== ⇩(28) ダイアログの必須入力項目に値が入っているか ====================
  2. Function listCheck()
  3.  listCheck = (Not Me.ComboBox2.ListIndex = -1) And (Not Me.TextBox1.Value = "")
  4. End Function
図8-21

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

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

8-3.ExcelシートDBの懸念点

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

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

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

9.最後に

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

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

一方で今回の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月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。