2021/07/07

先入先出の入出庫管理システム

• １．背景
• ２．システム概要
• 　２ー１．入庫処理
• 　２ー２．出庫処理
• 　２ー３．在庫確認
• ３．プログラムの流れ
• ４．ワークシート上の設定
• 　４ー１．入出庫データ（Sheet1）
• 　４ー２．棚データ（Sheet2）
• ５．標準モジュール１(Module1)
• 　５ー１．ボタンに対応した実行マクロ
• 　５ー２．入出庫データの取得
• 　５ー３．品名一覧を取得
• 　５ー４．空棚リストを取得
• ６．入庫ダイアログ(UserForm1)
• 　６ー１．フォーム上のコントロールの配置
• 　６ー２．フォームモジュール
• 　　６ー２－１．フォーム起動時の設定
• 　　　よりみち（ComboBoxのWidthとColumnWidthsの関係）
• 　　６ー２－２．品名リストの作成
• 　　６ー２－３．空棚番リストの作成
• 　　６ー２－４．品名リスト変更時の動作
• 　　６ー２－５．入庫数テキストの入力制限
• 　　６ー２－６．入庫数テキストのコピペ時の対応及び数値フォーマット整え
• 　　６ー２－７．入庫処理実行ほか
• ７．出庫ダイアログ(UserForm2)
• 　７ー１．フォーム上の配置
• 　７ー２．フォームモジュール
• 　　７ー２－１．フォーム起動時の設定
• 　　　よりみち（ListBoxのWidthとColumnWidthsの関係）
• 　　７ー２－２．在庫中の品名リストの作成
• 　　７ー２－３．出庫数用TextBoxの入力値処理
• 　　７ー２－４．品名リストの選択
• 　　７ー２－５．出庫リストの作成
• 　　７ー２－６．出庫完了処置
• 　　７ー２－７．キャンセル時
• ８．在庫リストダイアログ(UserForm3)
• 　８ー１．フォーム上の配置
• 　８ー２．フォームモジュール
• 　　８ー２－１．初期設定
• 　　８ー２－２．オプションボタンの動作
• 　　８ー２－３．在庫リストの作成・表示
• 　　８ー２－４．ダイアログの終了ボタン
• ９．最後に

１．背景

「先入先出（FIFO：First In First Outの略）」とは、先に仕入れたものから先に出庫する方法です。プログラミング用語では「キュー」と呼ばれるデータ構造です（逆の後入れ先出しは「スタック」と呼ばれます）。
この方法の目的は、保管している商品の品質が長期保管により劣化するのを防ぐものであり、特に期限がある商品の管理にとっては基本的な考え方だと思います。

今まで在庫管理のようなアプリ（「ExcelシートDBとSQLを使った倉庫管理システム」ほか）はいくつか紹介してきましたが、今回は先入先出法に特化した入出庫管理システムを紹介します。

２．システム概要

このシステムには、図２－１のように「３つのボタン（３つのダイアログ）」が必要です。一番下に添付したサンプルファイルでは、データシートであるSheet1上に配置していますが、ボタン専用の別シートでも別なダイアログ上にボタンを並べてもOKと思います。

図２－１

２ー１．入庫処理

「入庫」ボタンをクリックすると、図２－２のような「入庫処理のダイアログ」が起動しますので、入庫する「品名」を一番左のコンボボックスの中から選択をします。

図２－２

「品名」のリストは「入庫実績のある品名」をリスト化したものですが、もし新規の品名の場合は「リスト一番上の(新規)」を選択することで、図２－３のように表示されるInputBoxに新しい品名を記入することが出来ます。

図２－３

次に入庫数をテキストボックスに入力します。また図２－４のように、一番右のコンボボックスには「入庫可能な棚番リスト」が表示されますので、選択をします。

図２－４

なお、サンプルファイルでは棚の大小の情報をリストの備考として表示しました。各社ごとに棚の特性（容量以外では、棚の高さ、長さ、重さ、上段下段、精密部品用　など）は様々と思いますので、分かり易く選択し易い特性に変更して下さい。
また今回システムでは、以下のような棚の使い方を条件としてシステム構築しています。
　・棚に品物を入れるのは１回限り（全てを出庫して空になるまで、追加で品物を入れない）
　・異なる種類の品物を１つの棚に入れない

入庫項目の選択・入力が完了したら「入庫処理実行ボタン」をクリックすることで、図２－５のようにデータシートであるSheet1の最終行（図２－５のシートの10行目）に入庫データが書き込まれます（シート上のデータをユーザーが編集する必要は有りません）。

図２－５

入庫データとして書き込まれる値は、ダイアログ上の値以外に、入庫日（B列）、入庫の印（C列）、在庫数（G列）（入庫数と同じ値）を同時に記入します。

２ー２．出庫処理

「出庫」ボタンをクリックすると、図２－６のように出庫処理のダイアログが表示されます。
そのダイアログの品名のコンボボックス（一番左側）には「在庫している品名のリスト」が表示されますので、出庫する品名を選択①します。

図２－６

次に、出庫数を「個数」欄に入力②し、下の「検索」ボタンをクリック③することで、左側のリストボックスに「どの棚番から、何個を出庫すれば良いか④」が表示されます（図２－７）。

図２－７

この図２－７のダイアログ左側リストの通りに出庫を行ったら、図２－８のように「出庫完了」ボタンをクリック⑤します。すると、出庫データ（図２－８のシートの１２行目）が追記されると同時に、各棚のデータの在庫数を出庫数を引いた値に更新します（図２ー８のG10セル、G11セル）。なお出庫により空になった場合はセル値を削除（G10セル）します。

図２－８

なお出庫数が在庫数を超えていた場合は、図２－９のようにコメントが出ますので、出庫数を変更する等の対応をして下さい。

図２－９

２ー３．在庫確認

「在庫リスト」ボタンをクリックすると、図２－１０のように在庫リストが表示されます。

図２－１０

在庫リストは、初期は「棚番」順に並んでいますが、項目先頭のオプションボタンをONにすることで図２－１１のように「品名」順、「個数」順、「入庫日」順に並べ替えることが可能です。なお基本的な並び順は「日付順（＝上からの行順）」ですので、同じ値が続いている場合の第二並び順は日付順になります。

図２－１１

３．プログラムの流れ

今回システムは、基本的にはSheet1である入出庫データに「入庫データ」「出庫データ」を書き込んでいるだけです。その処理はそれぞれのUserForm内で行っていますので、それぞれのUserFormの説明時に内部の流れは説明していきます。
ここでは、標準モジュールのプロシージャと各UserFormのコントロールがどのようにつながっているかを、図３－１に簡単に示しました。

図３－１

４．ワークシート上の設定

入出庫データ（Sheet1）・棚番データ（Sheet2）とも、ユーザーが直接編集する必要はありませんので、シートを非表示にしたり、シート保護（マクロでの書込みは可にしておく）にしたりする必要はあると思います。

４ー１．入出庫データ（Sheet1）

入出庫データは図４－１のように、７列（A～G列）にデータを保存しています。１行目はタイトルを表示しています。

図４－１

Ｎo.	データのユニーク番号を１から連番で記入しています。但し、この列のデータはプログラムでは使用していません。
日付	入出庫した日付です。基本的には上の行からデータを追記していきますので、昇順で並びます。
入出	入庫データには｢入｣、出庫データには｢出｣と記入。人間がデータチェックする際に見易くしています。尚「出庫データ＝棚番無し」なので、この列は必須ではありません。
品名	入出庫した商品名。実際には「メーカー＋型式」「販売元＋品名」のように分割して管理する場面も多いと思いますが、 今回システムでは単純化しました。複数段の構造にする際には、品名選択工程を工夫する必要があります。
個数	入出庫した個数。今回は正の整数のみとしています。
棚番	入庫した際の棚番です。
在庫数	現時点で、その棚に残存している個数。入庫直後は入庫数と同じ値が入ります。

図４－２

なおG列の在庫数は無くても、１つ１つデータを辿って行けば「現時点での各棚の在庫数」や「空いている棚」は計算できるはずです。しかし、データ数が増えてきた時に毎回全データを再計算するのも大変だと思い、「在庫数」列を追加したシステムとしました。

４ー２．棚データ（Sheet2）

棚データは、図４－３のようにSheet2に作成しています。
今回はA列に棚番を記入し、またB列には棚の特性の１つとして「容量」を記入しています。

図４－３

棚の特性を増やすことは可能ですが、その際にはUserForm1のComboBox2の表示部分を変更する必要があります。

５．標準モジュール１(Module1)

標準モジュールには、起動ボタンから呼び出されるマクロ、及び「データシート（Sheet1、Sheet2）からデータを読み込む」プロシージャ他を置いています。

５ー１．ボタンに対応した実行マクロ

起動ボタンから呼び出されるマクロが、図５－１です。

1. '==========　⇩(1)　入庫ボタンのマクロ　============
2. Public Sub InStorage()
3. 　UserForm1.Show
4. End Sub
6. '==========　⇩(2)　出庫ボタンのマクロ　============
7. Public Sub OutStorage()
8. 　UserForm2.Show
9. End Sub
11. '==========　⇩(3)　在庫リストボタンのマクロ　============
12. Public Sub StorageList()
13. 　UserForm3.Show
14. End Sub

図５－１

「入庫」ボタンをクリックすると２～４行目が呼び出され、UserForm1が起動します。
「出庫」ボタンをクリックすると７～９行目が呼び出され、UserForm2が起動します。
「在庫リスト」ボタンをクリックすると１２～１４行目が呼び出され、UserForm3が起動します。
全て「モーダル」で起動していますので、複数のUserFormが同時に起動することは有りません。

５ー２．入出庫データの取得

図５－５の４９行目、図５－６の１０１行目、図６－１５の２４３行目、図７－１０の３３２行目、図７－１７の３９１行目、図８－４の４６５行目から呼び出される「DataRange関数」が図５－２です。
第一引数として「Erow（End row の意味）」、第二引数（任意）として「AL（All Dataの意味）」を受取り、入出庫データの配列を戻します。

引数Erowは、空の値を受取り「入出庫データの最終行位置」を呼出し側へ戻す引数です。
また引数ALにTrueを指定すると「入出庫の全データ」を配列として戻し、Falseを指定すると「在庫中のデータ＋α」を配列として戻します。（＋αは、在庫中では無いデータや出庫データも含んでいる　という意味）
なお引数ALはオプション引数ですので、無指定の場合はFalseとしています。

16. '==========　⇩(4)　入出庫データの取得関数　============
17. Public Function DataRange(Erow As Long, Optional AL As Boolean = False) As Variant
19. 　Dim Srow As Long 　　　　 '←データ開始行
20. 　Dim Sh As Worksheet 　　　 '←データシート
21. 　Set Sh = Sheet1
23. 　If AL = True Or Sh.Cells(2, 7).Value > 0 Then
24. 　　Srow = 2
25. 　Else
26. 　　Srow = Sh.Cells(1, 7).End(xlDown).Row
27. 　End If
29. 　Erow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
31. 　If Srow = Rows.Count Then
32. 　　DataRange = Empty
33. 　ElseIf Erow = 1 Then
34. 　　DataRange = Empty
35. 　Else
36. 　　DataRange = Range(Sh.Cells(Srow, 1), Sh.Cells(Erow, 7))
37. 　End If
39. End Function

図５－２

まず、この関数が返す値を図５－３で整理します。

図５－３

今回の入出庫データはSheet1のA列～G列です。引数ALにTrueを指定した場合には、データ先頭からの全データ範囲（図５－３の赤点線の範囲）を配列の形で戻します。また引数ALにFalseを指定（又は無指定）した場合には、G列にデータが存在する行から下の範囲（緑点線の範囲）を配列の形で戻します。
また引数Erowには、データの最終行位置（図５－３の場合だと「6」）を戻します。

このように引数ALを使って戻す範囲を変えるようにしたのは、データ取得範囲を必要最小限にしたかったからです。
今回システムでは、入出庫データから「入庫実績の品名」「使用中の棚番・品名」「各棚ごとの在庫数」を求めています。
「入庫実績の品名」を取得するには、先頭行（今回の場合２行目）から最終行までのデータを走査する必要がありますが、他の「使用中の棚番・品名」「各棚ごとの在庫数」を取得するには「在庫数に値が入っている範囲」のデータしか必要ではありません。しかも、データは下方に向かって追加されていきますので、「在庫数に値が入っている範囲」は下方にまとまっているはずです。

そこで引数ALを用意し、「使用中の棚番・品名」「各棚ごとの在庫数」を取得する場合には、G列にデータが存在する行から下の範囲のみを配列として戻すようにしました。なお、戻すデータ内には「全て出庫して空になった棚番のデータ」や「出庫の記録データ」も含まれている可能性がありますので、それらは「在庫数」行のデータ有無で仕訳けることとし、セル範囲を一発配列変換することで処理時間短縮を図っています。

２１行目の「Set Sh = Sheet1」では、「入出庫データのシート位置」を「Sheet1」に設定しています。今回「システム全体の起動ダイアログ」を設けなかったため、データシートの指定は各プロシージャ内で行っています。
ブックモジュールのThisWorkbook_Openにデータシートの位置を指定するコードを記入してもOKです。

２３～２７行目は、引数ALの値により「データ範囲の先頭行」を設定しています。
２３行目「If AL = True Or Sh.Cells(2, 7).Value > 0 Then」は、条件式を２つ「Or」で繋げています。
１つ目の条件式「AL = True」は「全データ」を示しますので、２４行目の「Srow = 2」で「データ範囲の先頭行を２行目」に設定します。
２つ目の条件式「Sh.Cells(2, 7).Value > 0」は、「AL = False」であっても「在庫数のあるデータが、２行目から存在する」場合です。この時も２４行目の「Srow = 2」で「データ範囲の先頭行を２行目」に設定する必要があります。

AL = False（在庫中データ）で、且つ「在庫数のあるデータが２行目より下」にある場合（２５行目）は、２６行目の「Srow = Sh.Cells(1, 7).End(xlDown).Row」で、G列（7列目）の１行目から下方向に飛んだ先の「在庫数の存在する最初のセル」の行位置をSrowとしています。

２９行目の「Erow = Sh.Cells(Rows.Count, 1).End(xlUp).Row」では「データ範囲の最終行」を設定しています。Excelとしての一番下の行位置「Cells(Rows.Count, 1)」から、上方向に飛んだ先の「No.列に値が入っているセル（＝データ最終行）」の行位置をErowとしています。
ここまでで、データの「先頭行（Srow）」と「最終行（Erow）」が求まりましたが、実際にはデータが無い場合があります。
例えば「システム使用開始前で、データが全く無い状態」や「全てを出庫してしまい、全商品の在庫が無い場合」です。その場合には、関数DataRangeとしては「Empty」を戻す　ということにしました。
Emptyを見つける方法については、図５－４のように「SrowとErowとの組合せ」で表を作って考えます。

図５－４

まず「SrowがExcelの一番下」まで行ってしまう状況とは、「在庫数に値が無い」状態です。つまり、入出庫データが一つも無い状態（初期状態）か、全商品の在庫が全く無い状態ですので、引数ALにTrueを指定されてもFalseを指定されても「Emptyを戻す」必要があります。
また「Erow＝１」というのは、入出庫データが一つも無い状態（初期状態）ですので、これも「Emptyを戻す」必要があります。

これをコードで表したのが３１～３７行目です。
３１行目の「If Srow = Rows.Count Then」は「SrowがExcelの一番下」を表しており、３２行目の「DataRange = Empty」で関数DataRangeの戻り値にEmptyを設定してます。
３３行目の「ElseIf Erow = 1 Then」は「入出庫データが一つも無い」ことを表し、３４行目の「DataRange = Empty」で同様に戻り値にEmptyを設定します。

残ったSrowとErowの組合せの時に、初めて３６行目「DataRange = Range(Sh.Cells(Srow, 1), Sh.Cells(Erow, 7))」で、行方向はSrowからErowまで、列方向はA列（１）～G列（７）までの範囲を関数DataRangeの戻り値にしています。

５ー３．品名一覧を取得

図６－７の１４９行目、図７－７の２８５行目から呼び出される「GoodsList関数」が図５－５です。
引数としてAL（入庫実績のある品名全て＝True、在庫のある品名のみ＝False）を受取ります。既定値はFalseです。この引数ALは、DataRange関数（図５－２）のAL引数と同じ意味です。
このGoodsList関数では、入出庫データから品名を取り出し「品名を昇順で並べた配列」として戻します。並べ替えにはSortedListオブジェクトを使用しています。

41. '==========　⇩(5)　品名一覧の取得関数　============
42. Public Function GoodsList(Optional AL As Boolean = False) As Variant
43. 　Dim Erow As Long 　　　　 '←(入出庫データの最終行位置)
44. 　Dim DR As Variant 　　　　 '←入出庫データ(DataRange)の配列
45. 　Dim buf As Variant 　　　　 '←品物を格納する配列
46. 　Dim SL As Object 　　　　　 '←SortedListオブジェクト
47. 　Dim i As Long 　　　　　　　 '←カウンタ変数(データの行数)
49. 　DR = DataRange(Erow, AL)
51. 　If Not IsEmpty(DR) Then
53. 　　Set SL = CreateObject("System.Collections.SortedList")
55. 　　For i = 1 To UBound(DR, 1)
56. 　　　If Not DR(i, 7) = "" Or AL = True Then
57. 　　　　If SL.containskey(DR(i, 4)) = False Then
58. 　　　　　SL.Add DR(i, 4), 0
59. 　　　　End If
60. 　　　End If
61. 　　Next i
63. 　　ReDim buf(0 To SL.Count - 1)
65. 　　For i = 0 To SL.Count - 1
66. 　　　buf(i) = SL.getkey(i)
67. 　　Next i
69. 　　Set SL = Nothing
70. 　End If
72. 　GoodsList = buf
74. End Function

図５－５

４９行目の「DR = DataRange(Erow, AL)」では、入出庫データを取得し変数DRに代入しています。
第一引数のErow（入出庫データの最終行位置）は、DataRange関数には必須ですので指定していますが、このGoodsList関数内では必要ありませんのでダミー的なものです。
また第二引数のALは、このGoodsList関数が受け取った引数ALをそのまま渡しています。

５１行目の「If Not IsEmpty(DR) Then」では、４９行目で取得した入出庫データ（変数DR）が「空では無い」場合に、５３～６９行目を実行します。
まず５３行目の「Set SL = CreateObject("System.Collections.SortedList")」で、SortedListオブジェクトを生成します。SortedListオブジェクトはCollectionやDictionaryと似ていますが、「セットしたキーの順序で自動的に並ぶ」のが特徴です。
今回、このSortedListを使って品名リストを作成していますので、「昇順に並んだ品名」の配列が戻り値になります。もし古い順、新しい順などで並べたい場合にはDictionary等を使用して下さい。

５５～６１行目では品名をSortedListオブジェクトSLに格納しています。
まず５５行目の「For i = 1 To UBound(DR, 1)」で配列DR内を１行ずつ調べて行きます。
SortedListオブジェクトに格納するのは「品名」ですが、引数ALによって「格納する種類が異なる」ので、仕訳けが必要となります。つまり以下のようなIf文にすれば良いことになります。
　・AL＝True　 →（入庫実績のある）品名全て　→　条件式は不要
　・AL＝False　→　在庫のある品名のみ　→　条件式「If Not DR(i, 7) = "" then ･･･」

これをコードにした一例が、５６行目の「If Not DR(i, 7) = "" Or AL = True Then」です。前半の「Not DR(i, 7) = ""」がAL=Falseの場合で、後半の「AL = True」がAL=Trueの場合です。この２つを「Or」で結ぶことで「AL=Falseの場合」は前半の条件式が成立する場合に５７～５９行目を実行し、「AL=Trueの場合」は後半の条件式が常に成立するため同じく５７～５９行目を実行することになります。

しかし何でもSortedListに格納できるわ訳では無く、「同じキーは格納できません」ので、５７行目の「If SL.containskey(DR(i, 4)) = False Then」で「同じキーが無い（False）」場合のみ、５８行目の「SL.Add DR(i, 4), 0」で「品名をSortedListに格納」しています。
また「SortedList.Add」の後ろに引数として「キー,値」を指定するのですが、今回必要なのは「キー（＝品名）」のみですので、値は何でもOKです。OKですが「何かを値として指定」する必要があるため、今回は「ゼロ」を値として指定しています。

６３行目の「ReDim buf(0 To SL.Count - 1)」では、関数GoodsListの戻り値にする配列（buf）の大きさを設定しています。大きさはゼロをスタートとしました。これは、この関数の戻り値である配列を使ってComboBoxのリスト（インデックスはゼロからスタート）に書き込むため、ゼロスタートで揃えた方が分かり易いだろう　という気持ちからです。
SortedListオブジェクトSLに格納された数は「SL.Count」で得られますので、ゼロ～「SL.Count－１」というインデックスになります。

６５～６７行目では、SLに格納された品名を配列bufに代入しています。
６５行目の「For i = 0 To SL.Count - 1」でカウンタ変数ｉをSortedListオブジェクトSLの数だけ回し、６６行目の「buf(i) = SL.getkey(i)」で「SLのキーを配列bufにコピー」します。
なお６５行目の「SL.Count -1」は「Ubound(buf,1)」でも同じことになります。なおUboundは最大インデックス番号を戻してきますので「-1」は不要となります。

７２行目の「GoodsList = buf」では、品名を格納した配列bufを関数GoodsListの戻り値に設定しています。
なお、５１行目の「If Not IsEmpty(DR) Then」で「入出庫データDRが空」だった場合には、変数bufは宣言したままですので既定値の「Empty」のままです。そのため関数GoodsListの戻り値もEmptyとなります。

５ー４．空棚リストを取得

図６－８の１６６行目から呼び出される「FreeRack関数」が図５－６です。
この関数内では、入出庫データの中から使用中の棚を見つけ、全棚番リストから差し引くことで「空の棚リスト」を作成しています。

76. '==========　⇩(6)　空いている棚リストを返す関数　============
77. Function FreeRack() As Variant
78. 　Dim Sh As Worksheet 　　 '←棚番リストのワークシート
79. 　Set Sh = Sheet2
81. 　Dim Dict As Object 　　 '←Dictionaryオブジェクト
82. 　Dim buf1 As Variant 　　 '←全ての棚データの配列
83. 　Dim buf2 As Variant 　　 '←空いている棚のデータ配列
84. 　Dim Erow As Long 　　　 '←棚データ・入出庫データ範囲の最終行位置
85. 　Dim DR As Variant 　　　 '←在庫がある入出庫データ(DataRange)の配列
86. 　Dim i As Long 　　　　　 '←カウンタ変数(データ範囲の行数)
88. 　Erow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
89. 　If Erow = 1 Then Exit Function
91. 　buf1 = Range(Sh.Cells(2, 1), Sh.Cells(Erow, 2))
93. 　Set Dict = CreateObject("Scripting.Dictionary")
95. 　For i = 1 To UBound(buf1, 1)
96. 　　If Dict.exists(buf1(i, 1)) = False Then
97. 　　　Dict.Add buf1(i, 1), buf1(i, 2)
98. 　　End If
99. 　Next i
101. 　DR = DataRange(Erow)
103. 　If IsEmpty(DR) = False Then
104. 　　For i = 1 To UBound(DR, 1)
105. 　　　If Not DR(i, 7) = "" Then
106. 　　　　Dict.Remove (DR(i, 6))
107. 　　　End If
108. 　　Next i
109. 　End If
111. 　If Dict.Count = 0 Then Exit Function
113. 　ReDim buf2(0 To Dict.Count - 1, 0 To 1)
115. 　For i = 0 To Dict.Count - 1
116. 　　buf2(i, 0) = Dict.keys()(i)
117. 　　buf2(i, 1) = Dict.items()(i)
118. 　Next i
120. 　FreeRack = buf2
122. 　Set Dict = Nothing
123. End Function

図５－６

本システムでは棚データをSheet2に置いていますので、７９行目の「Set Sh = Sheet2」でワークシートを設定させています。

８８行目の「Erow = Sh.Cells(Rows.Count, 1).End(xlUp).Row」は、棚データの最終行を取得し変数Erowに代入しています。
通常はSheet2に棚データが記入されているはずなので「Erowは2以上」になるはずです。しかし「棚データが記入されていない」または「７９行目のシート設定が間違っている」場合にはErow値は１になります。
ですので、８９行目の「If Erow = 1 Then Exit Function」のIf文が成立する時には、FreeRack関数を抜け出します。FreeRack関数は７７行目で「Variant型」としており、８９行目時点では戻り値に何も代入していないため、既定値であるEmpty値が戻り値になることになります。

９１行目の「buf1 = Range(Sh.Cells(2, 1), Sh.Cells(Erow, 2))」は、タイトル行を除いた棚データを配列buf1に代入します。尚、Variant型の変数にセル範囲を代入することで配列にする場合は、開始インデックスは「１」となります。

９３行目の「Set Dict = CreateObject("Scripting.Dictionary")」は、Dictionaryオブジェクトを生成しています。

９５～９９行目では、全ての棚番をDictionaryオブジェクトに登録しています。
９５行目の「For i = 1 To UBound(buf1, 1)」でカウンタ変数ｉを全棚の行数分だけ回し、９７行目の「Dict.Add buf1(i, 1), buf1(i, 2)」でDictionaryオブジェクトに登録をしています。この時、登録キーは「棚番＝ buf1(i, 1)」を、登録する値は「容量＝ buf1(i, 2)」になります。

なお、Dictionaryオブジェクトは同じキーを登録することが出来ませんので、９６行目の「If Dict.exists(buf1(i, 1)) = False Then」で「キーが登録済みで無い」ことを確認の上、登録をするようにしています。
棚番が重複することは無いとは思いますが、万一重複していた場合にはエラーが発生しますので、９６行目のIf文を付けています。

１０１行目の「DR = DataRange(Erow)」では、関数DataRangeを呼出し、配列の形で入出庫データを取得しています。ここで使っている引数Erow（データの最終行位置）は、このプロシージャ内では使用しないので、関数DataRangeを呼び出す為に使うダミー的な変数となります。また第二引数を指定していないため、「AL=False（使用中のデータ範囲）」を指定していることになります。
なおDataRange関数は、通常は「配列」が戻ってくるのですが、図５－４でも分かる様に「全商品を出庫した」または「システムを初めて使った」場合には「使用中の棚は存在しない状態」になり「Empty」が戻ってきます。

もし変数DRがEmptyの時には「全ての棚が空いている」のですから、「使用中の棚を差し引く」計算（１０４～１０８行目）は必要なくなります。ですので１０３行目の「If IsEmpty(DR) = False Then」で、「DRがEmptyで無い時」だけ「使用中の棚を差し引く」計算をさせています。

１０４行目の「For i = 1 To UBound(DR, 1)」で、カウンタ変数ｉを入出庫データの行数だけ回し、１０５行目の「If Not DR(i, 7) = "" Then」で「在庫数の列に値がある行」のときに限り、１０６行目の「Dict.Remove (DR(i, 6))」で「その棚番をキーとするDictionaryデータを削除」しています。
なお１０５行目のIf文の代わりに「If IsEmpty(DR(i, 7))=False Then」としても同じ結果が得られます。

棚データには存在しない棚番が、入出庫データの中で「使用中の棚」である場合は、Removeできませんのでエラーが出ます。通常では起こり難い不具合だと思いますが、棚データを「使っているのに削除する」ようなことが無いように注意が必要です。

１１１行目の「If Dict.Count = 0 Then Exit Function」では「Dictionaryデータがゼロだったら、関数を終了」する意味ですが、これは「保有する全ての棚に品物が入っている（＝空いている棚が１つも無い）」状態の時に発生します。いくら入庫しようとしても空いている棚が無いのですから入庫できないことになります。

と言って、余裕のありそうな棚に入れてしまうと、「商品の種類が混在した棚」が出来たり、同じ種類だとしても「在庫数がゼロでは無い複数の同じ棚番」が出来たりしてしまいます。どちらにしても手動でデータを作ることになってしまい、もうシステムとは呼べません。
もしやるとすれば、余裕のある棚の一部を区分け、新しい棚番をつけるくらいしか思いつきませんが、システム開始時に何がどのくらいのペースで入庫出庫するのかを予測しておくことが必要と思います。

１１３行目の「ReDim buf2(0 To Dict.Count - 1, 0 To 1)」では、FreeRack関数として戻す「配列buf2」の大きさを決めています。行方向はDictionaryデータの数（Dict.Count）、列方向は２列分（今回は、棚番号＋容量）です。関数の戻し値はComboBoxへ貼り付けることになるので、ComboBoxの行数の数え方と合わせて「インデックスはゼロから」にしています。

１１５～１１８行目では「使用中の棚番を除いた、使用していない棚番」の「配列」を作成しています。
１１５行目の「For i = 0 To Dict.Count - 1」はカウンタ変数ｉをDictionaryデータの数（Dict.Count）だけ回しています。「Dict.Count - 1」の代わりに、１１３行目で作ったbuf2のサイズを使って「For i = 0 To Ubound(buf2, 1)」でもOKです。
１１６行目の「buf2(i, 0) = Dict.keys()(i)」で、キーを配列buf2の１列目に代入し、１１７行目の「buf2(i, 1) = Dict.items()(i)」で値を配列buf2の２列目に代入しています。

１２０行目の「FreeRack = buf2」では、配列buf2を関数FreeRackの戻り値としています。
なお、今回は棚番に対する特性値が１つ（容量）としてプログラムを作りましたが、特性値が複数ある場合もあると思います。その時はUserForm3のMakeListBox（図８－４）のように、特性値を配列にしてから「Dict.Add キー（棚番）,特性値の配列」のようにしてDictionaryデータを作る必要があります。

６．入庫ダイアログ(UserForm1)

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

入庫用に、品名選択用のComboBox1、個数入力用のTextBox1、入庫棚選択用のComboBox2を図６－１のように配置します。また「棚が全て埋まってしまった」旨を伝えるLabel4、及び入庫処理実行とキャンセルボタン（CommandButton1,2）を配置します。
ボタンの表面文字の設定（Captionプロパティ）以外は、マクロ側から設定をしていきます。

図６－１

６ー２．フォームモジュール

６ー２－１．フォーム起動時の設定

入庫処理ダイアログ（UserForm1）の起動時の設定が図６－２です。

125. '==========　⇩(7)　フォーム１モジュール内変数宣言　============
126. Dim Sh As Worksheet 　　 '←入出庫データのワークシート
128. '==========　⇩(8)　フォーム１起動時設定　============
129. Private Sub UserForm_Initialize()
130. 　Set Sh = Sheet1
132. 　Me.Caption = "入庫処理"
133. 　Me.ComboBox2.ColumnCount = 2
134. 　Me.ComboBox2.ColumnWidths = (Me.ComboBox2.Width - 37) & ";30"
135. 　Me.TextBox1.IMEMode = fmIMEModeDisable
136. 　Me.TextBox1.TextAlign = fmTextAlignRight
137. 　Me.Label4.Caption = ""
138. 　Me.Label4.ForeColor = RGB(255, 0, 0)
140. 　Call MakeComboBox1
141. 　Call MakeComboBox2
142. End Sub

図６－２

１２６行目の「Dim Sh As Worksheet」は、UserForm1の中で共通して使用する変数Shの宣言をしています。変数への値代入は１３０行目の「Set Sh = Sheet1」で行っており、「入出庫データのワークシート」に「Sheet1」を設定しています。

１３２行目の「Me.Caption = "入庫処理"」は、ダイアログのタイトル部を「入庫処理」に変えています。
１３３行目の「Me.ComboBox2.ColumnCount = 2」は、入庫可能棚番リスト（ComboBox2）を２列指定にし「１列目に棚番」「２列目に棚の容量」を表示させることにします。
１３４行目の「Me.ComboBox2.ColumnWidths = (Me.ComboBox2.Width - 37) & ";30"」では、２列になっているComboBox2の「各列の幅」を設定しています。設定値は１列目が「Me.ComboBox2.Width - 37」、２列目が「30」です。単位はポイントです。
ちなみに、この値を適切に設定しないと、図６－３のようにリストに「横スクロールバー」が表示されてしまい、使いにくくなってしまいます。設定の詳細は、下の「よりみち」で紹介します。

図６－３

１３５行目の「Me.TextBox1.IMEMode = fmIMEModeDisable」では、個数を入力するTextBox1にカーソルを置いている時は「IME（日本語入力）をOFFにし、半角のみ」で入力させるようにしています。
１３６行目の「Me.TextBox1.TextAlign = fmTextAlignRight」では、TextBox1の表示を右寄せにして「数字らしく表示」させるようにしています。

１３７～１３８行目で設定をするLabel4は、入庫するための棚が無い場合に「空棚がありません」と表示させるものです。ユーザー側に注意を促し、余計な作業をさせないために設けました。
１３７行目の「Me.Label4.Caption = ""」は、初期段階ではLabel4を空欄にしておきます。また、１３８行目の「Me.Label4.ForeColor = RGB(255, 0, 0)」では、文字列が表示された際には「赤文字」で表示されるようにしています。

１４０行目の「Call MakeComboBox1」では、図６－７のMakeComboBox1プロシージャを呼び出し、品名リストを作成しています。
１４１行目の「Call MakeComboBox2」では、図６－８のMakeComboBox2プロシージャを呼び出し、空の棚番リストを作成しています。

今回はComboBoxの「.Width」と「.ColumnWidths」の関係について調べ、分かったことを紹介します。 初めに「ColumnWidthsを指定しない場合」について、まとめたのが図６－４になります。 図６－４ まず単列指定（「ColumnCount = 1」と指定、または指定無し）の場合には、ComboBox.Widthをいくつに指定しても「横スクロールバーは表示されない」ようです。また複数列の場合は、.Widthを「72 × 列数 + 2 以上」に設定することで、図６－４の一番右側のように横スクロールバー無しでリストが表示されます。 なお、単列の時にWidthの制限が無いのは、幅の狭いComboBoxの時でも▼マークをクリックすることで表示される「ドロップダウンリストの幅」が「72ポイント」になっている為かもしれません。 ColumnWidthsを指定する場合は、図６－５のような関係があるようです。つまり「73以下のWidthのComboBoxでは、ColumnWidthsの各列の合計値が72以下」であれば横スクロールバーは表示されません。 また「74以上のWidthのComboBoxでは、ColumnWidthsの各列の合計値が『Width - 2 』以下」の時に横スクロールバーが現れません。 図６－５ つまり１３４行目の「Me.ComboBox2.ColumnWidths = (Me.ComboBox2.Width - 37) & ";30"」では、ColumnWidthsの各列の合計値は「ComboBox2.Width - 7」ですので「横スクロールバーは表示されない」ことになります。 では「余裕の５ポイント」は何かというと、図６－６のように「縦スクロールバーで文字列が隠れないように」するためです。 図６－６ リスト数が少ない時は図６－６の上段のように「余裕代が有っても無くても、ほぼ同じ」なのですが、リスト数が多い場合は縦スクロールバーが現れますので、最終列（今回は『容量』の列）の文字がスクロールバーに被ってしまうことがあります。 今回はリストの各列の横方向表示位置（TextAlign）は「fmTextAlignCenter（中央揃え＝初期値）」ですので、文字数とリスト幅を見ながら余裕代を決める必要があります。 もしTextAlignが「fmTextAlignRight（右寄せ）」になっている場合は、最終列は必ず文字列が隠れてしまいます。そのような時にはUserForm2のListBox1の設定（図７－２の２７０～２７２行目）のように「ダミーの列を設定し、ある程度の幅を与える」ことで、スクロールバーで隠されることを防ぐことが出来ます。（詳細は「よりみち」を参照下さい） ComboBoxのWidthとColumnWidthsの関係には、図６－４より「72ポイント」という基準寸法があるようで、また図６－５より「72ポイントが変曲点」になっているようです。 72ポイントと聞いて「論理インチ（参照：シート上の図形の代替テキストを閲覧・編集）」を思い浮かべる方もいると思いますが、関係あるのか無いのか分かりません。

６ー２－２．品名リストの作成

図６－２の１４０行目から呼び出される「MakeComboBox1プロシージャ」が図６－７です。

144. '==========　⇩(9)　品名リストの作成　============
145. Private Sub MakeComboBox1()
146. 　Dim buf As Variant 　　 '←入庫実績のある全品名のリスト
147. 　Dim i As Long 　　　　 '←カウンタ変数（商品リストの行）
149. 　buf = GoodsList(True)
151. 　Me.ComboBox1.AddItem "(新規)"
153. 　If IsEmpty(buf) = False Then
154. 　　For i = 0 To UBound(buf, 1)
155. 　　　Me.ComboBox1.AddItem buf(i)
156. 　　Next i
157. 　End If
159. End Sub

図６－７

１４９行目「buf = GoodsList(True)」では、図５－５のGoodsList関数を呼び出しています。引数にTrueを指定していますので「入庫実績のある品名全て」を配列として受け取り、変数bufに代入します。
１５１～１５７行目でComboBox1のリストを作成しています。
１５１行目「Me.ComboBox1.AddItem "(新規)"」では、リストの先頭行に「（新規）」という項目を作成します。これは「実績のある品名以外が入庫」してきた時に、新たな品名を設定するためのものです。

１５３行目「If IsEmpty(buf) = False Then」で、１４９行目で受け取ったリストが配列（品名リストあり）だった時のみ、１５４～１５６行目のリスト作成処理を実行します。
まず１５４行目「For i = 0 To UBound(buf, 1)」でカウンタ変数ｉを配列の行数分だけ回し、１５５行目「Me.ComboBox1.AddItem buf(i)」で「リストに品名を追加」していきます。

１４９行目で受け取った値が空（Empty）だった時には、１５４～１５６行目のリスト作成処理を実行しないため、ComboBox1としては「（新規）のみのリスト」となります。

なお、今回はUserFormを閉じる方法として「Unload Me」を使用していますが、「Me.Hide」で閉じたい方もいると思います。その際には再表示の時にInitializeイベントは通過しませんのでActivateイベントに「Call MakeComboBox1」等を記載する事になります。
起動のたびにMakeComboBox1プロシージャを呼び出すのは今回と同じでも、HideではComboBoxのリストは残存していますので、このままだと重複したリストになってしまいます。
ですのでHideでフォームを閉じる際には、必ず１５０行目あたりに「Me.ComboBox1.Clear」のコードを入れて「リストを初期化した後、新しいリストを作る」ことを忘れないようにして下さい。

６ー２－３．空棚番リストの作成

図６－２の１４１行目から呼び出される「MakeComboBox2プロシージャ」が図６－８です。

161. '==========　⇩(10)　空棚番リストの作成　============
162. Private Sub MakeComboBox2()
163. 　Dim buf As Variant 　　 '←使用可能な棚リスト
164. 　Dim i As Long 　　　　 '←カウンタ変数（棚番リストの行）
166. 　buf = FreeRack
168. 　If IsEmpty(buf) = True Then
169. 　　Me.Label4.Caption = "空棚がありません"
170. 　　Exit Sub
171. 　End If
173. 　For i = 0 To UBound(buf, 1)
174. 　　Me.ComboBox2.AddItem ""
175. 　　　Me.ComboBox2.List(i, 0) = buf(i, 0)
176. 　　　Me.ComboBox2.List(i, 1) = buf(i, 1)
177. 　Next i
179. End Sub

図６－８

１６６行目「buf = FreeRack」は、図５－６のFreeRack関数を呼び出して現在使用可能な棚のリストの配列を取得し、変数bufに代入します。なお、「使用可能な棚が無い」場合にはEmptyを戻して来ます。

１６８～１７１行目では、「空いている棚が無い（bufがEmpty）」時にComboBox2のリストを作らず（＝作れず）Label4に空欄が無い旨を表示しています。
１６８行目「If IsEmpty(buf) = True Then」で、bufがEmptyか否かを調べ、Emptyだった場合には１６９行目「Me.Label4.Caption = "空棚がありません"」でLabel4にコメントを表示し、１７０行目「Exit Sub」で「リストを作らずに、MakeComboBox2プロシージャを抜ける」ようにしています。

bufがEmptyでは無い（＝配列のデータが有る）場合は、１７３～１７７行目でリストを作成します。
１７３行目「For i = 0 To UBound(buf, 1)」で、カウンタ変数ｉを配列bufの行数分だけ回します。
ComboBox2は今回２列表示にしていますので「AddItemで値を直接追加できない」ので、１７４行目「Me.ComboBox2.AddItem ""」でまず空のリスト行を作成します。
空のリスト行を作ったあとで、１７５行目「Me.ComboBox2.List(i, 0) = buf(i, 0)」で「１列目（インデックスはゼロ）に棚番」を代入し、１７６行目「Me.ComboBox2.List(i, 1) = buf(i, 1)」で「２列目（インデックスは１）に容量」を代入します。

６ー２－４．品名リスト変更時の動作

品名のリスト（ComboBox1）を選択した時に発生するイベントプロシージャが図６－９です。

181. '==========　⇩(11)　品名リスト変更時　============
182. Private Sub ComboBox1_Change()
183. 　Dim Ans As String 　　 '←InputBoxの戻り値
185. 　If ComboBox1.ListIndex = 0 Then
186. 　　Ans = InputBox("新しい品名を入力してください")
188. 　　If StrPtr(Ans) = 0 Then
189. 　　　ComboBox1.ListIndex = -1
190. 　　ElseIf Ans = "" Then
191. 　　　MsgBox "商品名が無効です"
192. 　　　ComboBox1.ListIndex = -1
193. 　　Else
194. 　　　Me.ComboBox1.AddItem Ans
195. 　　　Me.ComboBox1.ListIndex = Me.ComboBox1.ListCount - 1
196. 　　End If
198. 　End If
199. End Sub

図６－９

１８５行目「If ComboBox1.ListIndex = 0 Then」では、ComboBox1のリストから選択したのが「ListIndex=0の場合」のみ、１８６～１９６行目を実行しています。「ListIndex=0」とは「（新規）」となっている項目です。
それ以外の項目（ListIndexが１以上の、入力実績のある品名の内の１つ）を選択した時には、選択した品名がComboBox1に表示される事になります。

１８６行目「Ans = InputBox("新しい品名を入力してください")」では、図２－３のようなInputBox関数が表示され、（既存には無い）新しい品名を入力するように促されます。
InputBox関数には文字列を入れることになりますが、ユーザーがどのような対応をするかによって、InputBox関数が戻す値、及び判定方法は以下のように異なります。

No	対応	戻り値	判別方法1	判別方法2
①	文字列を入力し、OKﾎﾞﾀﾝをｸﾘｯｸ	入力した文字列	StrPtr(Ans) = 0以外	Not Ans=""
②	何も入力せずに、OKﾎﾞﾀﾝをｸﾘｯｸ	長さｾﾞﾛの文字列	StrPtr(Ans) = 0以外	Ans=""
③	ｷｬﾝｾﾙﾎﾞﾀﾝをｸﾘｯｸ	値ｾﾞﾛの文字列	StrPtr(Ans) = 0	Ans=""
④	右上×印をｸﾘｯｸ

図６－１０

図６－１０の判定法１，２を使い、１８８～１９６行目では、ユーザーがどの様に操作したかを判断していきます。
まず１８８行目「StrPtr(Ans) = 0 Then」は図６－１０の③④の場合を表し、「（ダイアログ閉じるを含め）キャンセルをしたのだから、初期状態にする」ために、１８９行目「ComboBox1.ListIndex = -1」で「品名を選択していない状態」にしています。
次に１９０行目「ElseIf Ans = "" Then」では、③④は除かれているため②の状態のことになります。その時には１９１行目「MsgBox "商品名が無効です"」でコメントを出し、１９２行目「ComboBox1.ListIndex = -1」で選択前の状態にしています。

最後に残ったのが「ユーザーが正しく新品名を入力した状態（図６－１０の①）」と判断し、１９４行目「Me.ComboBox1.AddItem Ans」で、ComboBox1のリストの一番下に「ユーザーが入力した新品名」を追加し、１９５行目「Me.ComboBox1.ListIndex = Me.ComboBox1.ListCount - 1」で、追加した項目を選択状態にします。

なおリスト中に既に品名があるのに、新規入力として同じ名前を入れてしまった場合は、リスト内では品名が重複することになり、新しく追加した方の品名を選択している状態になります。見掛けは異常ですが、入力処理的には「旧リストから品名を選択した状態と同じ」ことになります。

６ー２－５．入庫数テキストの入力制限

TextBox1は個数入力用です。ですので「数値のみ受け付けるテキストボックス」に設定します。値をキー入力した場合の処理が図６－１１です。

201. '==========　⇩(12)　TextBoxの入力文字制限　============
202. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
204. 　If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then
205. 　　KeyAscii = 0
206. 　End If
208. End Sub

図６－１１

TextBox1にフォーカスがある状態で、キーボードのキーが押された時に発生するのが「KeyPressイベント」で、引数として「KeyAscii」が渡されます。「KeyAscii」は「キーが押されたときに入力された文字のコード」で、図６－１２にその一部を示します。

文字		…	/	0	1	2	3	4	5	6	7	8	9	:	…
ASCII コード	10進数	…	47	48	49	50	51	52	53	54	55	56	57	58	…
	16進数	…	0x2F	0x30	0x31	0x32	0x33	0x34	0x35	0x36	0x37	0x38	0x39	0x3A	…

図６－１２

今回は押されたキーを入庫数として使うため、数字だけを拾い上げます。２０４行目「If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then」で、数値以外は２０５行目「KeyAscii = 0」を実行するようにしています。
この「KeyAscii = 0」を実行すると「テキストボックスへの入力を無効」にします。
今回は「個数」ですので「－（マイナス）」や「.（小数点のピリオド）」も無効にしましたが、マイナスや小数点を許可するテキストボックスが必要な場合は、同様に条件式を作る必要があります。

なお、２０４行目ではChr関数を使って「ASCIIコードを文字列に変換」していますが、直接的に「If KeyAscii < 48 Or KeyAscii > 57 Then」でもOKです（コード番号よりは、文字列の方が間違いが少ないと考えました）。

また、図６－２の１３５行目で「Me.TextBox1.IMEMode = fmIMEModeDisable」と日本語IMEをOFFにしていますが、このコードが無いと「全角で入力が出来る」ことになります。「全角で入力」するとKeyPressイベントが発生しないので、全角の数字やひらがなが入力できてしまいます。
しかし、TextBox内で全角を確定すると図６－１３のChangeイベントが発生しますので、その中で「数値に変換」されることになります。
だからと言って「fmIMEModeDisable」設定が無駄かと言うと、そんなことは無いと思います。「日本語で入力する部分には全角に」「数値で入力する部分には半角に」なる方が、ユーザーには親切だと思います。

６ー２－６．入庫数テキストのコピペ時の対応及び数値フォーマット整え

テキストボックス上で「キーで数量を入力」するのであれば、KeyPressイベントプロシージャで用が足ります。しかし、どこかの文字列をコピー後テキストボックス内でペーストすることも可能です。例えば、メール上の入庫個数をコピぺするような場合です。
しかし、この場合は「キーを押していないのでKeyPressイベントが発生しない」ので「どんな文字でも入力が出来てしまう」ことになります。
「ユーザーがキーボードから入力してくれる、又はコピペでも正しい数値を入力してくれる事を期待する」のは自由ですが、万一0～9以外の文字入力を許してしまうと、入庫数・在庫数に（マイナスや小数点も含んだ）数値以外が入ることになってしまい、後日必ずエラーが発生します。ここは、絶対に0～9以外の文字以外は許さないことが必要です。

そのためのコードが図６－１３であり、また「数値のフォーマット」も同時に行っています。

210. '==========　⇩(13)　入庫数テキストのコピペ時の対応及び数値フォーマット　============
211. Private Sub TextBox1_Change()
213. 　If IsNumeric(Me.TextBox1.Text) = False Then
214. 　　Me.TextBox1.Text = Val(Me.TextBox1.Text)
215. 　End If
217. 　If Clng(Me.TextBox1.Text) < 0 Then
218. 　　Me.TextBox1.Text = -1 * Clng(Me.TextBox1.Text)
219. 　End If
221. 　Me.TextBox1.Text = Format(Me.TextBox1.Text, "#,##0")
222. End Sub

図６－１３

まず、どのような文字の種類がコピペされるか、また判別する方法などを図６－１４に整理してみました。

図６－１４

この中で(1)～(5)は「数値」ですので、IsNumeric関数で選り分け(6)～(8)のような「文字列を含んだ数値を除外する」ことが可能です。
しかしIsNumeric関数では「マイナス値もOK」となってしまいますので、Val関数②・CLng関数③・CDbl関数④・Sgn関数⑤などで「数値としてマイナスか否か」を判断しても良いですし、また「文字列＜０」と直接マイナスか否かを判別式にしたとしても、Excel内で勝手に数値に変換してから比較するようなのでOKのようです。但しExcelの機能に頼らずに「数値として判断」する方が正しい手法と思います。

また(6)のように「数値の後ろに文字列が付いてしまったもの」を「前半の数値だけでも生かしてあげる」という考え方もあります。その際にはVal関数②で「前半の数値部分だけ取り出す」ことが可能です。
しかし、(7)のように「数値の中に、桁区切りのカンマが入っている」場合には、Val関数では「カンマの前までしか、数値として取り出せない」ことになります。もちろん文字列を先頭から走査していくことで、桁区切りカンマを含む数値を取り出すことは可能ですが、単一の関数での処理としては制限があることに注意が必要です。

また今回は個数専用ですので、入力・貼り付けた数値に対して「桁区切りカンマを付けてフォーマットを整える」場合は、Format関数⑥を使って文字列を加工します。Format関数で加工する書式を「#,##0」とすることで、３桁ごとにカンマが入ります。

以上の考え方を盛り込んだのがChangeイベントプロシージャ（図６－１３）です。
２１３行目「If IsNumeric(Me.TextBox1.Text) = False Then」は、IsNumeric関数を使って「数値以外の文字列が付いているもの」を選別しています。図６－１４だと(6)～(8)の文字列ですが、それらは２１４行目「Me.TextBox1.Text = Val(Me.TextBox1.Text)」で、先頭方向から数値を取り出しています。

次に２１７行目「If Clng(Me.TextBox1.Text) < 0 Then」では、数値がマイナスだった場合には、２１８行目「Me.TextBox1.Text = -1 * Clng(Me.TextBox1.Text)」で「－１を掛けて」プラスの値に変換しています。
なお、２１７行目に達した状態では、２１３～２１５行目で「数値」にしていますので、(6)～(8)×③のような「エラーは発生しない」状態になっています。

最後に、２２１行目「Me.TextBox1.Text = Format(Me.TextBox1.Text, "#,##0")」では、「正の数値」にした値を「桁区切りのカンマ」を付けると同時に、Format関数の書式が「#,##0」と、小数点が無いために「整数」に変換されます。
（今回は必要ありませんが、もし小数点３桁固定で表示するには「#,##0.000」等とする必要があります）

６ー２－７．入庫処理実行ほか

ダイアログ上の「入庫処理実行」ボタンをクリックすると、図６－１５が呼び出されます。

224. '==========　⇩(14)　入庫処理実行ボタン　============
225. Private Sub CommandButton1_Click()
226. 　Dim Erow As Long 　　　 '←入出庫データの最終行位置
228. 　If Me.ComboBox1.ListIndex <= 0 Then
229. 　　MsgBox "品名を選択または新設定して下さい"
230. 　　Exit Sub
231. 　End If
233. 　If Val(Me.TextBox1.Value) = 0 Then
234. 　　MsgBox "個数を整数で入力して下さい。"
235. 　　Exit Sub
236. 　End If
238. 　If Me.ComboBox2.ListIndex = -1 Then
239. 　　MsgBox "棚番を選択して下さい"
240. 　　Exit Sub
241. 　End If
243. 　Call DataRange(Erow)
245. 　Sh.Cells(Erow + 1, 1) = Erow
246. 　Sh.Cells(Erow + 1, 2) = Date
247. 　Sh.Cells(Erow + 1, 3) = "入"
248. 　Sh.Cells(Erow + 1, 4) = Me.ComboBox1.Text
249. 　Sh.Cells(Erow + 1, 5) = Me.TextBox1.Value
250. 　Sh.Cells(Erow + 1, 6) = Me.ComboBox2.Text
251. 　Sh.Cells(Erow + 1, 7) = Me.TextBox1.Value
253. 　Unload Me
254. End Sub
256. '==========　⇩(15)　キャンセルボタン　============
257. Private Sub CommandButton2_Click()
258. 　Unload Me
259. End Sub

図６－１５

２２８～２４１行目は、品名用ComboBox1、個数用TextBox1、棚番用ComboBox2の選択・入力状況をチェックしています。

まず２２８行目「If Me.ComboBox1.ListIndex <= 0 Then」では、品名用ComboBox1が以下の状態の時に、２２９～２３０行目を実行します。
　・何も選択していない（ListIndex = -1）
　・（新規）を選択している（ListIndex = 0）（実際にはInputBox関数が表示されてしまうので、あり得ない）
　・何かをコピーして、ComboBox1のテキストボックス部に貼付けた状態（ListIndex = -1）
２２９行目「MsgBox "品名を選択または新設定して下さい"」でコメントを出し、２３０行目「Exit Sub」で入庫処理を中断します。

次に２３３行目「If Val(Me.TextBox1.Value) = 0 Then」では、個数用TextBox1に正の整数が入っているか否かを調べています。このIf文では「TextBox1に何も入っていない」または「TextBox1にゼロが入っている」ときに成立します。
例えば何も入っていない状態は「Me.TextBox1.Value = ""」となりますが、その「長さゼロの文字列」をVal関数の引数に入れると「数値のゼロ」が戻ります。TextBox1にゼロが入っている時はもちろん「数値のゼロ」が戻りますので、「何も入っていないか、ゼロの時」に２３４行目「MsgBox "個数を整数で入力して下さい。"」でコメントを出し、２３５行目「Exit Sub」で入庫処理を中断します。

最後に２３８行目「If Me.ComboBox2.ListIndex = -1 Then」で、棚番用ComboBox2が選択状態をチェックしています。
ComboBox2が選択されていない時には、もちろん「ListIndex = -1」ですが、それ以外にComboBox2のテキストボックス部に直接「埋まっている棚番」または「存在しない棚番」などを入力（コピペも含む）した場合も「ListIndex = -1」になります。
その場合には２３９行目「MsgBox "棚番を選択して下さい"」でコメントを出し、２４０行目「Exit Sub」で入庫処理を中断します。

各選択・入力項目のチェックが完了したら、２４３行目「Call DataRange(Erow)」で図５－２のDataRange関数を呼び出し、入出庫データの最終行位置Erowを取得します。DataRange関数の戻り値であるデータ配列は、今回必要無いので「Callステートメント」で呼び出しています。なお、例えばデータが空（Empty）の場合でも、引数Erowは行位置（データが空の場合はErow=1）を数値として返してくれます。

２４５～２５１行目は、「入庫データ」をデータシートに書き込んでいます。書き込むシートShは、図６－２の１３０行目の「Set Sh = Sheet1」で「Sheet1」にセットされています。参考にSheet1を再掲します。

図６－１６

２４５行目「Sh.Cells(Erow + 1, 1) = Erow」では、１列目（A列）に「No.」を入力しています。タイトル行が１行なので、引数として得たErowはそのままNo.にできます。
２４６行目「Sh.Cells(Erow + 1, 2) = Date」では、２列目（B列）に「本日の日付」を入力しています。
２４７行目「Sh.Cells(Erow + 1, 3) = "入"」では、３列目（C列）には「入・出」を記入します。今回は「入力処理」のため「入」を入力しています。

２４８行目「Sh.Cells(Erow + 1, 4) = Me.ComboBox1.Text」では、４列目（D列）に品名を入力しています。品名はダイアログのComboBox1に表示されていますので、それを直接指定しています。
２４９行目「Sh.Cells(Erow + 1, 5) = Me.TextBox1.Value」では、５列目（E列）に個数を入力しています。個数はダイアログのTextBox1に入力されている値です。
２５０行目「Sh.Cells(Erow + 1, 6) = Me.ComboBox2.Text」では、６列目（F列）に棚番を入力しています。棚番はダイアログのComboBox2に表示されていますので、それを直接指定しています。
２５１行目「Sh.Cells(Erow + 1, 7) = Me.TextBox1.Value」では、７列目（G列）に在庫数を入力しています。在庫数は、入庫データを記入した時点では「＝入庫数」ですので、ダイアログのTextBox1の値を入力しています。

２５３行目「Unload Me」で、ダイアログを閉じています。

ダイアログのキャンセルボタンをクリックした時には「CommandButton2_Click」イベントが発生しますので、２５８行目「Unload Me」で、ダイアログを閉じています。

７．出庫ダイアログ(UserForm2)

７ー１．フォーム上の配置

出庫処理のダイアログ上のコントロール配置は図７－１のようにしました。

図７－１

出庫可能な品名をリストにするComboBox1、出庫する個数を入力するTextBox1、出庫リスト（どの棚から何個出庫するのかを指示）のListBox1を配置します。
また、品名と個数を選択・入力後に出庫リストを表示させるための「検索ボタン」、出庫処理（入出庫リストへのデータ書き出し等）を行うための「出庫完了ボタン」、出庫処理を中断する「キャンセルボタン」を配置します。

ボタン表面の文字列は事前にCaptionプロパティを書き換えてあります。また出庫リストのListBox1は４列表示をさせますが、その設定はマクロ側から行っています。

７ー２．フォームモジュール

７ー２－１．フォーム起動時の設定

出庫処理ダイアログ（UserForm2）の起動時の設定が図７－２です。

261. '==========　⇩(16)　モジュール内変数の宣言　============
262. Dim Sh As Worksheet 　　 '←入出庫データのワークシート
264. '==========　⇩(17)　フォーム起動時の設定　============
265. Private Sub UserForm_Initialize()
266. 　Set Sh = Sheet1
268. 　Me.Caption = "出庫処理"
270. 　Me.ListBox1.TextAlign = fmTextAlignRight
271. 　Me.ListBox1.ColumnCount = 4
272. 　Me.ListBox1.ColumnWidths = (Me.ListBox1.Width - 53) & ";30;0;20"
274. 　Me.TextBox1.IMEMode = fmIMEModeDisable
275. 　Me.TextBox1.TextAlign = fmTextAlignRight
277. 　Call MakeComboBox3
278. End Sub

図７－２

２６２行目「Dim Sh As Worksheet」では、UserForm1の中で共通して使用する変数Shの宣言をしています。変数への値代入は２６６行目「Set Sh = Sheet1」で行っており、「入出庫データのワークシート」に「Sheet1」を設定しています。

２６８行目「Me.Caption = "出庫処理"」は、ダイアログのタイトル部を「出庫処理」に変えています。
２７０行目「Me.ListBox1.TextAlign = fmTextAlignRight」では、ListBox1の各列の値を「右寄せ」表示にしています。TextAlignは、図７－３のように３種の設定が出来ます。
今回このListBoxでは、２列目の「出庫数」を数値として表示させますので、数値らしく「右寄せ」に設定します。しかし各列独自に設定は出来ませんので、１列目の「出庫棚番」も右寄せになってしまいます。

値	定数	内容
1	fmTextAlignLeft	左寄せ(既定値)
2	fmTextAlignCenter	中央揃え
3	fmTextAlignRight	右寄せ

図７－３

２７１行目「Me.ListBox1.ColumnCount = 4」は、ListBox1を４列に設定しています。「１列目＝出庫棚番」「２列目＝出庫数」までが値を表示している列です。３列目は制御を簡単にするため「データの行位置」を入れますが、非表示にさせます。
また４列目はダミーの表示列で、「リストが多い場合に表示される『縦スクロールバー』により２列目の出庫数が隠れてしまうのを防止」させています。
２７２行目「Me.ListBox1.ColumnWidths = (Me.ListBox1.Width - 53) & ";30;0;20"」では、例えばListBox1の幅（Width）を100とすると「１列目＝47、２列目＝30、３列目＝0、４列目＝20」に設定しています。
１列目は出庫棚番ですので、貴社の棚番の桁数により調整して下さい。また２列目は出個数ですので、状況に合わせて下さい。
３行目には「データの行位置」が入りますが、ユーザーには関係ありませんので「幅＝ゼロ」とし非表示にしています。また４列目は「出庫のリストが増えた時に、縦スクロールバーで２列目が隠れるのを防止」するため、「縦スクロールバーとほぼ同じ幅」の20ポイントを割り当てています。設定の意味・詳細については、下の「よりみち」で紹介します。

２７４行目「Me.TextBox1.IMEMode = fmIMEModeDisable」は、出庫数のTextBox1にフォーカスを当てた時には日本語IMEを切り、「半角のみの入力」にしています。
２７５行目「Me.TextBox1.TextAlign = fmTextAlignRight」では、出庫数は数値ですので「数値らしく右寄せ表示」に設定しています。

２７７行目「Call MakeComboBox3」では、図７－７を呼び出し、ComboBox1に品名選択リストを作成させています。

まず、１つ目の「よりみち」と同様に、ListBoxについて「横スクロールバーを表示させない」ための幅について調べました。 ColumnWidths有無での単列・複数列についてまとめたのが図７－４です。 図７－４（ListBoxの上の１０個の四角は、一辺が10ポイントで描画してあり、長さの参考にしています） ComboBoxと異なりListBoxは表示列幅が固定されていますので、ColumnWidths設定無し＋単列（１列）の場合でも「ListBox幅が小さ過ぎると、横スクロールバーが表示」されてしまいます。ListBox幅を広げていくと、「75（ポイント）」で横スクロールバーが消えます。 複数列も含めて、横スクロールバーが表示されない条件は「ListBox.Width >= (ListBox.ColumnCount × 72) + 3 」と分かります。ComboBoxの時は「2」でしたが、ListBoxでは「3」となるようです。 また、ColumnWidthsを設定している場合（図７－４の下段）でも、「ListBox.Width >= (ColumnWidthsの合計値) + 3 」の関係が成り立ちます。 一方、ListBoxのTextAlignプロパティ（図７－３参照）でリスト中の文字列の位置を調整できますが、このTextAlign値とColumnWidthsの関係を調べたのが図７－５です。 図７－５ 上段は「ColumnWidthsをListBoxのWidthに目一杯広げている」ため、文字列は各列の中で左寄り・中央揃え・右寄せとなるのは予測が付くと思います。 それに対して下段は「ColumnWidthsをListBoxのWidthに対して、余りを持たせている」状態です。注目は、中央揃え・右寄せの時が「ColumnWidthsを目一杯広げている」時と同じだということです。どうも「ListBoxの最終列には、ColumnWidthsの余裕分も割り当てる」ようなのです。 このことで困るのは「中央揃え・右寄せしている時に、リスト数が多くて縦スクロールバーが表示」された時に、「最終列の文字列が縦スクロールバーで隠れてしまう」ことだと思います。今回も最終表示列が出庫数で数値列であるため「右寄せ」にしたいのですが、単純に最終表示列のColumnWidthsを少なくしても「縦スクロールバー分のスペースは確保できない」ことになります。 そこで「縦スクロールバーの分の列を、あらかじめ確保する」のが図７－６の方法です。 図７－６ 本来、ListBoxの各列には値を入れるものだと思いますが、余分に１列を確保①し、その列には値を入れない②ことで「リスト数が多い場合でも最終列の文字列が縦スクロールバーで隠れない③」ようにしています。 なお今回システムの出庫ダイアログでは、ListBox1の値表示列は１列目・２列目ですが、３列目にデータの行位置の値を非表示で持たせていますので、４列目を「縦スクロールバーのスペース分」としています。

７ー２－２．在庫中の品名リストの作成

図７－２の２７７行目から呼び出されるMakeComboBox3プロシージャが、図７－７です。

280. '==========　⇩(18)　在庫中の品名リストの作成　============
281. Private Sub MakeComboBox3()
282. 　Dim buf As Variant 　　　 '←在庫している商品の配列
283. 　Dim i As Long 　　　　　 '←カウンタ変数（在庫している商品の種類）
285. 　buf = GoodsList(False)
287. 　If IsEmpty(buf) = True Then Exit Sub
289. 　For i = 0 To UBound(buf, 1)
290. 　　Me.ComboBox1.AddItem buf(i)
291. 　Next i
293. End Sub

図７－７

２８５行目「buf = GoodsList(False)」では、図５－５のGoodsList関数プロシージャを呼び出し、変数bufに代入しています。引数に「False」を指定しているため、「在庫している品名のみ」を配列の形で取得しています。なお、引数はオプションにしているので、Falseを付けず「buf = GoodsList」としても同じ結果が得られます。
なお在庫が全くなかった場合は、Emptyが変数bufに入ります。

２８７行目「If IsEmpty(buf) = True Then Exit Sub」では、「在庫が全くなかった」場合に、このプロシージャを抜け出しますので、「２８９～２９１行目で作られるはずのリスト」が作られずに「リストの無いComboBox1」になります。

２８９～２９１行目ではComboBox1のリストを作成します。２８７行目でbufがEmptyの場合は取り除かれていますので、ここを実行するのは「bufは、品名が格納された配列」となります。
２８９行目「For i = 0 To UBound(buf, 1)」では、カウンタ変数ｉを配列bufの行数分だけ回します。リストはインデックス＝ゼロからスタートしますので、「i = 0」から始めています。
２９０行目「Me.ComboBox1.AddItem buf(i)」では、ComboBox1に１行ずつリストを作っています。今回のComboBox1は「１列のみ」のリストですので、直接AddItemに品名「buf(i)」を与えています。

７ー２－３．出庫数用TextBoxの入力値処理

TextBox1は出庫数を入力する場所ですので、キーボードから入力する際には「数字のみを許可」し、またコピペで値を貼り付ける際には「正の整数（またはゼロ）」になるように変換をします。機能的には、UserForm1の入庫数TextBox1とほぼ同等です。

295. '==========　⇩(19)　TextBoxの入力文字制限　============
296. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
297. 　If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then
298. 　　KeyAscii = 0
299. 　End If
300. End Sub
302. '==========　⇩(20)　出庫数テキストのコピペ時の対応及び数値フォーマット　============
303. Private Sub TextBox1_Change()
305. 　If IsNumeric(Me.TextBox1.Text) = False Then
306. 　　Me.TextBox1.Text = Val(Me.TextBox1.Text)
307. 　End If
309. 　If CLng(Me.TextBox1.Text) < 0 Then
310. 　　Me.TextBox1.Text = -1 * CLng(Me.TextBox1.Text)
311. 　End If
313. 　Me.TextBox1.Text = Format(Me.TextBox1.Text, "#,##0")
315. 　Me.ListBox1.Clear
316. End Sub

図７－８

２９６～３００行目は、キーボードから出庫数を入力する際の表示文字列の制限機能です。
詳しい説明は「入庫数テキストの入力制限」を参照下さい。

３０３～３１６行目は、テキストをコピペでTextBoxに貼り付けた時に、正の整数（及びゼロ）に値を調整するものです。
詳しい説明は「入庫数テキストのコピペ時の対応及び数値フォーマット整え」を参照下さい。

但し、３１５行目「Me.ListBox1.Clear」は、出庫用TextBox特有のコードです。
今回の出庫処理ダイアログには、ユーザーが選択・入力した「品名」＋「出庫数」のデータを使って、「先入先出法による、出庫すべき棚番号＋出庫数」をListBox1に表示する機能があります。ユーザーは、この表に従って各棚から指示された個数を出庫することになります。
しかしListBoxへの出力は「検索」ボタンを通して処理しているため、リスト出力後に「出庫数を変更」されてしまうと、「出庫数～出庫リスト」の整合性が取れなくなってしまいます。

そこで「ユーザーが出庫数を変更した」時には、出庫リスト（ListBox1）をクリアし、関係を断ち切っています。

７ー２－４．品名リストの選択

品名リストであるComboBox1を選択し直した時に発生するイベントプロシージャが図７－９です。
図７－８の３１５行目と同様に、ユーザーが「品名を再選択した」時に出庫リスト（ListBox1）をクリアし、関係を断ち切るものです。

318. '==========　⇩(21)　品名リストの選択　============
319. Private Sub ComboBox1_Change()
320. 　Me.ListBox1.Clear
321. End Sub

図７－９

７ー２－５．出庫リストの作成

出庫処理ダイアログの「検索」ボタン（CommandButton1）をクリックした時のイベントプロシージャが図７－１０です。
ユーザーが選択・入力した「出庫品名」と「出庫数」から「出庫すべき棚番＋出庫数」を出庫リスト（ListBox1）に出力します。

323. '==========　⇩(22)　出庫リストの作成　============
324. Private Sub CommandButton1_Click()
325. 　Dim OUTnum As Long 　　　 '←出庫残数（あと何個出庫するかの数）
326. 　Dim buf As Variant 　　　　 '←入出庫データの配列
327. 　Dim i As Long 　　　　　　 '←カウンタ変数(データ配列の行数)
328. 　Dim j As Long 　　　　　　 '←カウンタ変数(出庫ﾘｽﾄの行数)
329. 　Dim Erow As Long 　　　　 '←入出庫データの最終行位置
330. 　Dim Ans As Long 　　　　　 '←出庫数不足時の処理内容
332. 　buf = DataRange(Erow)
334. 　If Me.ComboBox1.ListIndex = -1 Then
335. 　　MsgBox "品名を選択して下さい"
336. 　　Exit Sub
337. 　End If
339. 　OUTnum = Val(Me.TextBox1.Value)
341. 　If OUTnum = 0 Then
342. 　　MsgBox "出庫数を入力して下さい"
343. 　　Exit Sub
344. 　End If
346. 　Me.ListBox1.Clear
348. 　For i = 1 To UBound(buf, 1)
349. 　　If Me.ComboBox1.Text = buf(i, 4) And buf(i, 7) > 0 Then
350. 　　　Me.ListBox1.AddItem ""
351. 　　　Me.ListBox1.List(j, 0) = buf(i, 6)
352. 　　　Me.ListBox1.List(j, 2) = Erow - UBound(buf, 1) + i
354. 　　　If OUTnum >= buf(i, 7) Then
355. 　　　　Me.ListBox1.List(j, 1) = buf(i, 7)
356. 　　　Else
357. 　　　　Me.ListBox1.List(j, 1) = OUTnum
358. 　　　End If
360. 　　　OUTnum = OUTnum - buf(i, 7)
361. 　　　If OUTnum <= 0 Then Exit For
363. 　　　j = j + 1
364. 　　End If
365. 　Next i
367. 　Select Case OUTnum
368. 　　Case Is > 0
369. 　　　Ans = MsgBox("在庫不足で、出庫数に足りません" & vbNewLine & _
370. 　　　　　　"出庫数を" & Me.TextBox1.Value - OUTnum & "個に減らしますか", vbYesNo)
372. 　　　If Ans = 6 Then
373. 　　　　Me.TextBox1.Value = Me.TextBox1.Value - OUTnum
374. 　　　　Call CommandButton1_Click
375. 　　　Else
376. 　　　　Me.TextBox1.Value = 0
377. 　　　End If
378. 　End Select
380. End Sub

図７－１０

３３２行目「buf = DataRange(Erow)」では、入出庫データの配列を取得し、変数bufに代入しています。第一引数Erowでは、データの最終行位置を受け取ります。また第二引数は省略しているため「在庫数のある入出庫データ」が取得できます。
なお、もし出庫するものが無い場合は、変数bufにはEmptyが入ることになります。

３３４～３３７行目では、ユーザーが品名を選択しているかを確認しています。
３３４行目「If Me.ComboBox1.ListIndex = -1 Then」で、ComboBox1の選択位置を取得し「何も選択していない状態（ListIndex = -1）」の時に、３３５行目「MsgBox "品名を選択して下さい"」でコメントを出し、３３６行目「Exit Sub」でプロシージャを抜け出しています。

３３９行目「OUTnum = Val(Me.TextBox1.Value)」では、出庫個数が入力されたTextBox1の「数値」を取得し、変数OUTnumに代入しています。
ここでVal関数を使ってTextBox1の値を取得していますので、TextBox1の値が「何も入っていない状態（長さゼロの文字列）」および「ゼロ値が入っている状態」の両方が「ゼロ」となります。

３４１～３４４行目は、TextBoxが「何も入っていない」または「ゼロが入っている」状態の時の処理をしています。
３４１行目「If OUTnum = 0 Then」で、TextBox1が「何も入っていない」か「ゼロ」の場合に、３４２行目「MsgBox "出庫数を入力して下さい"」でコメントを出し、３４３行目「Exit Sub」でプロシージャを抜け出しています。

３４６行目「Me.ListBox1.Clear」では、出庫リストであるListBox1をクリアしています。
「Me.ListBox1.Clear」というコードは、品名ComboBox1を変更（図７－９）した時、及び出庫数TextBox1を変更（図７－８）した時に実行されていますので、検索条件を変更された時には常にClearされています。しかし「同じ条件で、繰り返し『検索』ボタンを押された時には重複してリスト行が作られますので、それを防ぐために３４６行目でもClearしています。
なお、出庫リストの書き込み行を指定するカウンタ変数ｊは、検索ボタンをクリックするたびに初期化されますので、３４６行目でClearしなくても、出庫リストの表示データは変わらず「空白行のみが増えていく」現象になります。

３４８～３６５行目では、先頭の「For i = 1 To UBound(buf, 1)」（３４８行目）でカウンタ変数ｉを入出庫データ行数分だけ回して、「出庫すべき棚番と個数」を拾い上げていきます。流れが分かり難いので、図７－１１にフローチャートを示しました。


図７－１１

この中で「出庫が必要な個数」を表している変数OUTnumがポイントです。
最初は「ユーザーが出庫したい個数」ですが、入出庫データの各行を走査している最中は「棚の在庫数と比較しながら、在庫数を全て出庫して良いか」の判断となります。また、全ての入出庫データを走査し終えた段階でもOUTnumが残っている場合は「希望出庫数だけ在庫が無い」ことを示していることになります。

３４９行目「If Me.ComboBox1.Text = buf(i, 4) And buf(i, 7) > 0 Then」では、３４８行目のFor～Nextで回している入出庫データの対象行が、「品名がユーザー指定」且つ「在庫数がある」か否かを調べています。全体をFor～Nextで回していますので、入出庫データの上側（＝先入れの棚）から調べていることになり、IF文がヒットすれば、その行から出庫すれば「先出し」したことになります。

３５０行目「Me.ListBox1.AddItem ""」では、まず空のリストを作成します。これは「ListBox1が複数列リスト」だからです。
３５１行目「Me.ListBox1.List(j, 0) = buf(i, 6)」では、棚番（６列目）をリストの１列目（インデックスはゼロ番）に代入します。
３５２行目「Me.ListBox1.List(j, 2) = Erow - UBound(buf, 1) + i」では、入出庫リストの現在の行位置を３列目（インデックスは2番）に代入します。行位置は直接入出庫データから取得できませんが、最終行位置（Erow）と入出庫データの全体行数（ UBound(buf, 1)）、及びFor～Nextで回しているカウンタ変数ｉの値から算出しています。
なお、入出庫データの１行目の「No」に「１を足した値」を使用しても良いと思います（この場合は、ユーザーが入出庫データを手動で書き換えない事が前提です）。

３５４～３５８行目では、出庫数をリストに書き込んでいますが、出庫する数は「その棚の在庫数全て」であるかどうかは状況によります。例えば、ユーザーが100個の出庫を望んでいるとして整理したのが図７－１２です。

例：出庫希望数(OUTnum)が100個の場合

OUTnum	棚の在庫数	その棚の出庫数	棚の新･在庫数	OUTnum繰越数
100	① 200	100	100	0
	② 100	100	0	0
	③ 50	50	0	50

図７－１２

走査した棚に①のように200個の在庫数があった場合には、ユーザー希望の100個は「その棚だけで確保できる」ことになります。また100個だけ出庫すれば良いので、その棚の在庫数は100個に減るはずですし、次の棚を探しに行く必要は無くなる（OUTnum繰越数＝ゼロ）ことになります。
一方、100個の在庫数だった場合②には、その棚の在庫数全てを出庫するので、出庫してしまえば「その棚は空に」なり、また次の棚を探す必要も無い（OUTnum繰越数＝ゼロ）ことになります。
50個の在庫数だった場合③には、その棚全部を出庫しても「まだ50個足らない」状態ですので、「その棚は空に」なります。またOUTnum繰越数は50個となり、次の棚を探しに行く必要があることが分かります。

ということで、３５４行目「If OUTnum >= buf(i, 7) Then」では、OUTnum値が在庫数以上の場合（図７－１２の②③）には３５５行目「Me.ListBox1.List(j, 1) = buf(i, 7)」で「在庫数全て」を出庫リストの出庫数にします。
一方、それ以外（図７－１２の①）の場合は、３５７行目「Me.ListBox1.List(j, 1) = OUTnum」で、OUTnum値を出庫リストの出庫数にします。

３６０行目「OUTnum = OUTnum - buf(i, 7)」では、OUTnum値から在庫数を引き、OUTnumを「更に出庫が必要な数」に変更します。
３６１行目「If OUTnum <= 0 Then Exit For」では、３６０行目で計算した「新たなOUTnumがゼロ以下」になったら「更なる出庫は必要無し」と判断できるので、入出庫データを回しているFor～Nextから抜け出します。

３６３行目「j = j + 1」では、出庫リストの行を１つ下に下げています。

３４８～３６５行目のFor～Nextで「入出力データを全て走査しても、OUTnumが残ってしまった」場合、「ユーザーが希望した出庫数よりも、在庫数の方が少なかった」ということになります。この時には「ユーザーに、在庫が不足している旨を伝える」必要がありますので、３６７～３７８行目で処理を行います。
３６７行目「Select Case OUTnum」で、For～Nextを抜け出した後のOUTnumの値を調べています。
３６８行目「Case Is > 0」で、OUTnumの値がゼロ超（整数なので、１以上ということになる）の場合に３６９～３７８行目を実行します。なお、３６７～３６８行目をまとめて、IF文にしてもOKです。

まず、３６９～３７０行目「Ans = MsgBox("在庫不足で、出庫数に足りません" & vbNewLine & "出庫数を" & Me.TextBox1.Value - OUTnum & "個に減らしますか", vbYesNo)」で、図７－１３のようなメッセージボックスを表示させます。

図７－１３

メッセージボックス上には「何個だったら出庫できる」かを表示させ、その最大出庫数で「はい」なのか、それ以外「いいえ」なのかをユーザーに選択してもらいます。
MsgBoxの第二引数には、ボタンの種類を指定（図７－１４参照）が出来、今回は「vbYesNo」にしていますので、「はい」「いいえ」の２つのボタンが表示されることになります。

MsgBoxの第二引数

定数	値	内容
vbOKonly	0	｢OK｣ボタンのみ表示（既定値）
vbOKCancel	1	｢OK｣｢キャンセル｣ボタンを表示
vbAbortRetryignore	2	｢中止｣｢再試行｣｢無視｣ボタンを表示
vbYesNoCancel	3	｢はい｣｢いいえ｣｢キャンセル｣ボタンを表示
vbYesNo	4	｢はい｣｢いいえ｣ボタンを表示
vbretryCancel	5	｢再試行｣｢キャンセル｣ボタンを表示

図７－１４

ユーザーがMsgBoxのどのボタンをクリックしたかは、図７－１５の様にその戻り値で知ることが出来ます。今回MsgBoxに作ったボタンは「はい」「いいえ」ですので、はいをクリックした場合は「6」、いいえの場合は「7」が戻ります。

MsgBoxの戻り値

定数	値	内容
vbOK	1	｢OK｣ボタンが選択された
vbCancel	2	｢キャンセル｣ボタンが選択された
vbAbort	3	｢中止｣ボタンが選択された
vbRetry	4	｢再試行｣ボタンが選択された
vbIgnore	5	｢無視｣ボタンが選択された
vbYes	6	｢はい｣ボタンが選択された
vbNo	7	｢いいえ｣ボタンが選択された

図７－１５

３７２行目「If Ans = 6 Then」で何をクリックしたかを調べ、「はい」の場合は３７３行目「Me.TextBox1.Value = Me.TextBox1.Value - OUTnum」で、TextBox1の「出庫数」の値を「Me.TextBox1.Value - OUTnum（最大出庫数）」に再設定します。
TextBoxの値を書き換えると、図７－８の３１５行目「Me.ListBox1.Clear」によりListBox1はクリアされてしまいます。ですので、再度「検索ボタン（CommandButton1）」をマクロ側からクリックし、ListBox1に出庫リストを表示させています。
但し、ListBox1の出庫リストは「最大出庫数」の時のデータとなっていますので、ユーザー側から見ると「出庫リストは変更されていない」ように見えます。

「いいえ」の場合（３７５行目の「Else」）は、３７６行目「Me.TextBox1.Value = 0」で出庫数のTextBox1にゼロを入れます。ここで「Me.TextBox1.Value = ""」と「長さゼロの文字列」を指定しても、TextBox1には「0」が表示されます。
これは、TextBox1のChangeイベント（図７－８）で「値が変更された」時には３０６行目の「Me.TextBox1.Text = Val(Me.TextBox1.Text)」で「数値にされてしまう」ためです。

TextBox1の値がゼロに変更されるとTextBox1のChangeイベントが発生し、図７－８の３１５行目「Me.ListBox1.Clear」が実行され、出庫リストListBox1をクリアすることで、出庫数と出庫リストとの関係を断ち切っています。

７ー２－６．出庫完了処置

ダイアログの「出庫完了」ボタンをクリックした時に実行されるのが、図７－１６です。出庫リスト（ListBox1）のデータに従って、入出庫データへの追記、及び出庫した棚の在庫数修正をします。

381. '==========　⇩(23)　出庫完了処置ボタン押下時　============
382. Private Sub CommandButton2_Click()
383. 　Dim Erow As Long 　　　 '←入出庫データの最終行位置
384. 　Dim i As Long 　　　　　 '←カウンタ変数（出庫リストの行位置）
386. 　If Me.ListBox1.ListCount = 0 Then
387. 　　MsgBox "検索していないか、又は出庫するものがありません"
388. 　　Exit Sub
389. 　End If
391. 　Call DataRange(Erow)
393. 　Sh.Cells(Erow + 1, 1) = Erow
394. 　Sh.Cells(Erow + 1, 2) = Date
395. 　Sh.Cells(Erow + 1, 3) = "出"
396. 　Sh.Cells(Erow + 1, 4) = Me.ComboBox1.Text
397. 　Sh.Cells(Erow + 1, 5) = Me.TextBox1.Value
399. 　For i = 0 To Me.ListBox1.ListCount - 1
400. 　　Sh.Cells(Me.ListBox1.List(i, 2), 7) = Sh.Cells(Me.ListBox1.List(i, 2), 7) - Me.ListBox1.List(i, 1)
402. 　　If Sh.Cells(Me.ListBox1.List(i, 2), 7) = 0 Then
403. 　　　Sh.Cells(Me.ListBox1.List(i, 2), 7) = ""
404. 　　End If
406. 　Next i
408. 　Unload Me
409. End Sub

図７－１６

３８６～３８９行目では、出庫リストにデータがあるかをチェックしています。
３８６行目「If Me.ListBox1.ListCount = 0 Then」では、出庫リスト（ListBox1）の行数を調べ、ゼロ（＝出庫リストが無い）の場合に、３８７～３８８行目を実行します。
３８７行目「MsgBox "検索していないか、又は出庫するものがありません"」ではユーザーにコメントを出し、３８８行目「Exit Sub」でプロシージャを抜け出しています。

３９１行目「Call DataRange(Erow)」では、関数DataRange（図５－２）を呼出し、第一引数であるErow（入出庫データの最終行位置）を取得しています。関数DataRangeは「入出庫データの配列」を戻す関数ですが、このCommandButton2_Clickイベントプロシージャ内では戻り値は必要無いため、Callで呼び出しています。

３９３～３９７行目では、出庫データを「入出庫データ最終行（Erow）＋１」行目に書き込みます。入出庫データへの書き込み位置を図７－１７に示します。

図７－１７

３９３行目「Sh.Cells(Erow + 1, 1) = Erow」は、１列目（A列）に「データNo.」を記入します。
３９４行目「Sh.Cells(Erow + 1, 2) = Date」は、２列目（B列）に、今日の「日付」を記入します。
３９５行目「Sh.Cells(Erow + 1, 3) = "出"」は、３列目（C列）に、出庫である印「出」を記入します。
３９６行目「Sh.Cells(Erow + 1, 4) = Me.ComboBox1.Text」は、４列目（D列）に、「品名」を記入します。
３９７行目「Sh.Cells(Erow + 1, 5) = Me.TextBox1.Value」は、５列目（E列）に、「出庫数」を記入します。

３９９～４０６行目は、出庫リストを見ながら、出庫した棚の在庫数の修正を行っています。
３９９行目「For i = 0 To Me.ListBox1.ListCount - 1」では、カウンタ変数ｉをListBox1の行数分だけ回しています。
ListBox1の「3列目（インデックスは2）」には、「入出庫データの行位置」を格納していますので、その行位置を使用して４００行目「Sh.Cells(Me.ListBox1.List(i, 2), 7) = Sh.Cells(Me.ListBox1.List(i, 2), 7) - Me.ListBox1.List(i, 1)」では、対象行の在庫数（Sh.Cells(Me.ListBox1.List(i, 2), 7)）から出庫数（Me.ListBox1.List(i, 1)）を引いて、その残りを新たな在庫数としています。

なお、４００行目のコードでは「棚が空になっても、在庫数は『0』と表示」されてしまい、DataRange関数（図５－２）の２６行目で「在庫数の入っている行から下をデータ範囲」とする意味が薄れてしまいます（せっかくEnd(xlDown)で空白セルを飛ばしているのに、ゼロで引っ掛かってしまう）。
ですので「ゼロ値になった在庫数は""（長さゼロの文字列）とする」コードが、４０２～４０４行目です。

４０２行目「If Sh.Cells(Me.ListBox1.List(i, 2), 7) = 0 Then」では、「在庫数のセルがゼロ」である場合、４０３行目「Sh.Cells(Me.ListBox1.List(i, 2), 7) = ""」で「在庫数のセルを""（長さゼロの文字列）」に置き換えています。

出庫処理を全て完了した時点で、最後に４０８行目「Unload Me」でダイアログを閉じます。

７ー２－７．キャンセル時

ダイアログの「キャンセル」ボタンをクリックした時に呼び出されるのが図７－１８です。

411. '==========　⇩(24)　キャンセルボタン押下時　============
412. Private Sub CommandButton3_Click()
413. 　Dim Ans As Long 　　　　　 '←MsgBoxへの回答内容
415. 　If Not Me.ListBox1.ListCount = 0 Then
416. 　　Ans = MsgBox("出庫実行せずに閉じて良いですか", vbYesNo)
417. 　　If Ans = 7 Then Exit Sub
418. 　End If
420. 　Unload Me
421. End Sub

図７－１８

４１５行目「If Not Me.ListBox1.ListCount = 0 Then」では、出庫リスト（ListBox1）にデータが有るか否か（ユーザーが品名と出庫数を選択・入力後に「検索ボタン」をクリックしたか否か）を、ListCount（リストの行数）で判断しています。
「Not」を付けていますので、「出庫リストが有る」場合に４１６～４１７行目が実行されます。

４１６行目「Ans = MsgBox("出庫実行せずに閉じて良いですか", vbYesNo)」では、MsgBox関数の第二引数に「vbYesNo」を設定していますので、図７－１９のような「はい」「いいえ」のボタンのあるMsgBoxが表示されたます。

図７－１９

このMsgBoxを作った意図は、通常作業だと
「品名・出庫数を入力」→「検索ボタンをクリックし、出庫リストを出す」→「出庫リストを見ながら、実際に出庫作業」→「出庫完了ボタンをクリック」→「ダイアログ消える」
という流れを想定しているのですが、キャンセルボタンで「出庫リストを出したのにダイアログを閉じる」のは、ユーザーが「出庫リストを確認しただけ」なのか「出庫完了ボタンを押し忘れた」のかが、プログラム側からは判別出来ないと考えたからです。

ですので、そのメッセージボックスに対して「いいえ」をクリック（MsgBoxの戻り値＝「7」）した時には、４１７行目「If Ans = 7 Then Exit Sub」で、出庫処理ダイアログを閉じずに出庫データを保持させています。
一方「はい」をクリックした時には「出庫リストが表示されているにも関わらず、分かっていて閉じる」と判断し、次のコードへ進み、４２０行目「Unload Me」でダイアログを閉じています。
MsgBoxのボタンの種類、戻り値については、図７－１４・図７－１５を参照下さい。

８．在庫リストダイアログ(UserForm3)

８ー１．フォーム上の配置

在庫リストを表示するUserForm3上のコントロールの配置は図８－１のようにしました。リストは、棚番他すべての項目での並び替えを可能としました。

図８－１

フォーム上には、リストを表示するListBox1を中央に配置し、その上に「タイトル」兼「項目並び替え」のためのオプションボタンを配置しました。また、一番下に「閉じるボタン」を置きましたが、フォーム右上の×印で終了させても良いと思います。

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

８ー２－１．初期設定

フォーム起動時に実行されるInitializeイベントプロシージャが図８－２です。

423. '==========　⇩(25)　在庫リストフォームの初期設定　============
424. Private Sub UserForm_Initialize()
426. 　Me.Caption = "在庫リスト"
427. 　Me.ListBox1.ColumnCount = 5
428. 　Me.ListBox1.ColumnWidths = "40;60;40;60;1"
429. 　Me.ListBox1.TextAlign = fmTextAlignRight
431. 　Me.OptionButton1.Value = True
432. 　Call OptionButton1_Click
434. End Sub

図８－２

４２６行目「Me.Caption = "在庫リスト"」では、ダイアログのタイトルを書き込んでいます。
４２７行目「Me.ListBox1.ColumnCount = 5」では、ListBox1を５列設定にしています。表示は４列（棚番、品名、個数、入庫日）で、５列目はリスト数が多かった場合の「縦スクロール」で表示値（今回は、４列目の入庫日）が隠れないようにするための「ダミー列」です。詳しくは「よりみち」を参照下さい。

５列分の表示列幅を決めているのが４２８行目「Me.ListBox1.ColumnWidths = "40;60;40;60;1"」です。1列目＝40（ポイント）、2列目＝60、3列目＝40、4列目＝60です。５列目は「1ポイント」の設定になっていますが、実際には13ポイント分の幅があります。
ColumnWidthsの合計値は「201ポイント」ですが、一方ListBox1の幅（Width）は216ポイントあります（マクロからの設定では無く、コントロールを作成した時の寸法）。「よりみち」でも説明したように「ListBox.Width >= ColumnWidths合計値 + 3」であり、且つ「ColumnWidthsの余裕分は最終列に割り当てられる」ため、「設定の1ポイント＋割り当て分の12ポイント」で、計13ポイントの幅となります。
（設定値を0にしてしまうと「非表示列」となってしまいます。その際には余裕分は表示列に加算されることになります。）

４２９行目「Me.ListBox1.TextAlign = fmTextAlignRight」は、ListBox1の全ての列を「右寄せ」に設定しています。これは「数値」である「個数の列」の表示を揃えるためです。

４３１行目「Me.OptionButton1.Value = True」で、４つのオプションボタンの内、まず１番目（棚番）をONにしています。
そして、４３２行目「Call OptionButton1_Click」を実行することで、「棚番ボタンをクリックし、棚番順でリストを表示」させています。
なお、もし初期状態で「４つのオプションボタンのValue値が全てFalse」または「棚番以外のボタンのどれか１つがValue=True」であれば、４３１行目の「Me.OptionButton1.Value = True」で「オプションボタン１が選択される」ことになりClickイベントが発生するので、４３２行目でわざわざオプションボタンをクリックするマクロを実行させなくても、リストは作成されることになります。
しかし、もし初期状態で「オプションボタン１がValue=True」であった場合は、「オプションボタン１を選択状態に変えた」ことにならないのでClickイベントが発生しません。
初期状態がどうなっているか分からない場合には、あえて「Clickイベントを実行」させる方が安全だと思います。

８ー２－２．オプションボタンの動作

ダイアログ上の４つのオプションボタンを選択した場合に動作するイベントプロシージャが、図８－３です。

436. '==========　⇩(26)　棚番順　============
437. Private Sub OptionButton1_Click()
438. 　Call MakeListBox(6)
439. End Sub
440. '==========　⇩(27)　品名順　============
441. Private Sub OptionButton2_Click()
442. 　Call MakeListBox(4)
443. End Sub
444. '==========　⇩(28)　個数順　============
445. Private Sub OptionButton3_Click()
446. 　Call MakeListBox(7)
447. End Sub
448. '==========　⇩(29)　入庫日順　============
449. Private Sub OptionButton4_Click()
450. 　Call MakeListBox(2)
451. End Sub

図８－３

４つのオプションボタンを選択することで、それぞれのClickイベントが発生し、その中の「Call MakeListBox(＊)」で、図８－４のMakeListBoxプロシージャが実行され「それぞれの順序での在庫リスト」が表示されます。
MakeListBoxプロシージャの引数には、下の通り「入出庫データの列番号」を指定します。
棚番＝6列目（F列）、品名＝4列目（D列）、個数（＝在庫数）＝7列目（G列）、入庫日＝2列目（B列）

８ー２－３．在庫リストの作成・表示

図８－３の各オプションボタンのClickイベントから呼び出されるのが図８－４です。引数として、入出庫データの列番号（KeyCol）を指定します。

453. '==========　⇩(30)　在庫リスト作成・表示　============
454. Private Sub MakeListBox(KeyCol As Long)
455. 　Dim buf1 As Variant 　　　　　 '←入出庫データの配列(在庫有)
456. 　Dim buf2 As Variant 　　　　　 '←並び替えキー単位で集合させた在庫データ配列
457. 　Dim Erow As Long 　　　　　　 '←入出庫データの最終行位置
458. 　Dim i As Long 　　　　　　　　 '←カウンタ変数(入出庫データ行数ほか)
459. 　Dim j As Long 　　　　　　　　 '←カウンタ変数（在庫リストの列数）
460. 　Dim k As Long 　　　　　　　　 '←カウンタ変数（キーの数）
461. 　Dim m As Long 　　　　　　　　 '←カウンタ変数（在庫リストの行数）
462. 　Dim SL As Object 　　　　　　 '←SortedListオブジェクト
463. 　Dim DT As Variant 　　　　　　 '←在庫データ(1行分)の配列
465. 　buf1 = DataRange(Erow)
467. 　Me.ListBox1.Clear
468. 　If IsEmpty(buf1) = True Then Exit Sub
470. 　Set SL = CreateObject("System.Collections.SortedList")
472. 　For i = 1 To UBound(buf1, 1)
473. 　　If Not buf1(i, 7) = "" Then
475. 　　　ReDim DT(1 To 1, 1 To 4)
476. 　　　DT(1, 1) = buf1(i, 6)
477. 　　　DT(1, 2) = buf1(i, 4)
478. 　　　DT(1, 3) = buf1(i, 7)
479. 　　　DT(1, 4) = buf1(i, 2)
481. 　　　If SL.containskey(buf1(i, KeyCol)) = False Then
482. 　　　　SL.Add buf1(i, KeyCol), DT
483. 　　　Else
484. 　　　　buf2 = WorksheetFunction.Transpose(SL(buf1(i, KeyCol)))
485. 　　　　ReDim Preserve buf2(1 To UBound(buf2, 1), 1 To UBound(buf2, 2) + 1)
487. 　　　　For j = 1 To UBound(buf2, 1)
488. 　　　　　buf2(j, UBound(buf2, 2)) = DT(1, j)
489. 　　　　Next j
491. 　　　　SL(buf1(i, KeyCol)) = WorksheetFunction.Transpose(buf2)
492. 　　　End If
494. 　　End If
495. 　Next i
497. 　m = 0
498. 　For i = 0 To SL.Count - 1
499. 　　For k = 1 To UBound(SL.getbyindex(i), 1)
501. 　　　Me.ListBox1.AddItem ""
502. 　　　For j = 1 To 4
503. 　　　　Me.ListBox1.List(m, j - 1) = SL.getbyindex(i)(k, j)
504. 　　　Next j
505. 　　　m = m + 1
507. 　　Next k
508. 　Next i
510. 　Set SL = Nothing
511. End Sub

図８－４

まず、在庫リスト作成の流れを図８－５に示します。

図８－５

全体としては「入出庫データ」の各行を走査していき、在庫リストですので「在庫数が残っている行」のみを抽出していきます。そして在庫リストの対象行が見つかったら、「棚番、品名、個数、入庫日」のデータを配列（DT）にしておきます。

次にSortedList（SL）での処理に入っていきます。
まずプロシージャの引数として「SortedList（SL）のKeyとなる項目の列位置（プログラム内ではKeyCol）」を受取っていますので、対象行のデータ（棚番、品名、個数、入庫日）の中からKey名が決まります。そして、既存（先頭の場合は、まだSLデータが存在しないのですがエラーは出ません）のSLデータ内に「そのKey名が存在するか」を確認します。

Key名が存在しない場合（No側）は、SLデータとしてKey名＋事前に作成した配列DT を追加します。
Key名が存在する場合（Yes側）は、Keyが重複してしまうためSLデータを追加できませんので、値（配列）だけを取り出し「配列サイズを大きく」したのち、事前に作成した配列DTのデータを「増やした枠に挿入」し、最後にSLデータの値として更新します。

この作業を入出庫データの行数だけ繰り返すことで、Key名＋「棚番、品名、個数、入庫日のデータ配列（この中のどれかが、Key名となっている）」のSLデータが出来上がります。
図８－５にはそれ以降の流れが書かれていませんが、出来合ったSLデータはKey順に並んでいますので、SLデータ順に取り出し、且つ値（配列）を順々にリストに書き出していけば、「在庫リスト」が完成します。

それではコードを見ていきます。
４６５行目「buf1 = DataRange(Erow)」では、入出庫データを取得し変数buf1に代入しています。DataRange関数の第二引数を省略しているため、「在庫のある行より下側」のデータとなります。
もし在庫が１つも無い場合にはEmptyが戻りますので、今回在庫リストを作成する必要がなく、４６８行目「If IsEmpty(buf1) = True Then Exit Sub」でプロシージャを抜け出します。

４６７行目「Me.ListBox1.Clear」では既存のListBox1のデータをクリアしています。これはダイアログ表示後にオプションボタンを選択されるたびに「リストが増える」ことを防止しています。
プログラム中ではListBox1の行位置を制御しているのは、変数m（４６１行目で宣言）で、もしクリアしなくてもプロシージャを実行するたびに４９７行目「m = 0」で初期化しているため「データの重複は発生しない」のですが、「空白行が、どんどん増える」ような現象となるため防止しています。

４７０行目「Set SL = CreateObject("System.Collections.SortedList")」では、SortedListオブジェクトを生成し「SL」としています。SortedListオブジェクトは、キーと値（配列もOK）を保持し、キーの重複はNGです。またキーの値の昇順で自動的に並びます。

４７２～４９５行目では、入出庫データの各行を４７２行目「For i = 1 To UBound(buf1, 1)」で回し、４７３行目「If Not buf1(i, 7) = "" Then」で在庫数の残っている行だけを処理していきます。

４７５～４７９行目では、「棚番、品名、個数、入庫日のデータ配列」を作成しています。
まず、４７５行目「ReDim DT(1 To 1, 1 To 4)」で「２次元配列」を作成しています。SLデータを最初に作る（４８２行目のAddメソッド）段階では、このDT配列は1次元配列で問題無いのですが、後に４８５行目のReDimで２次元配列に変わる可能性があります。そして最終的にListBox1にデータを書きだす段階（５０３行目）で「２次元配列と１次元配列が混在している」と、コードが複雑になるために「最初から２次元配列に統一」しています。

４７６行目「DT(1, 1) = buf1(i, 6)」では、配列DTの１要素目に「棚番」のデータを代入しています。４７７～４７９行目も同様に、「品名」「個数」「入庫日」のデータを代入しています。

４８１行目「If SL.containskey(buf1(i, KeyCol)) = False Then」では、SLデータ内に同じキーのデータの存在有無をチェックしています。キーはプロシージャの引数「KeyCol」の列位置の項目ですので、「buf1(i, KeyCol)」となります。
同じキーが存在しない（False）のであれば４８２行目でSLデータを追加、存在すれば４８４～４９１行目で、その存在するSLデータの値（配列）に今回のデータ（DT）を加える処理をします。

まず同じキーが存在しない場合は、４８２行目「SL.Add buf1(i, KeyCol), DT」でデータを追加します。追加する際のキーは、引数KeyColの列位置の項目「buf1(i, KeyCol)」であり、値の方は４７５～４７９行目で作成した配列DTです。

同じキーが存在した場合の流れは、図８－６の様になります。

図８－６

まず、４８４行目「buf2 = WorksheetFunction.Transpose(SL(buf1(i, KeyCol)))」では、SLデータの内、キーが「buf1(i, KeyCol)」であるデータの「値」を受け取り、変数buf2に代入しています。

これを別のコードで「buf2 = WorksheetFunction.Transpose(SL.getbyindex(SL.indexofkey(buf1(i, KeyCol))))」としてもOKです。内容的には「getbyindexメソッド」の引数に「インデックス番号」を指定し値を取り出す方法ですが、インデックス番号は直接分からないので「indexofkeyメソッド」の引数に「キー」を指定することで、インデックス番号を求めています。

なお、値（配列）のサイズを増やす方向は「最終次元のみ」です。今回の場合「列方向」のみが可能ですので、値を取り出す時にTranspose関数を使って「行列逆転」をします。

取り出した配列buf2（図８－６の②）を４８５行目「ReDim Preserve buf2(1 To UBound(buf2, 1), 1 To UBound(buf2, 2) + 1)」で「列方向に１列追加（図８－６の③）」します。値は保ったままにするため「Preserveオプション」を指定します。

４８７～４８９行目では、増やした配列に対象行のデータを入れていきます。入れるデータは、４７５～４７９行目で配列DTにしてありますので、それを使います。
４８７行目「For j = 1 To UBound(buf2, 1)」で、カウンタ変数ｊを「配列buf2を行方向」に動かしていきます。そして４８８行目「buf2(j, UBound(buf2, 2)) = DT(1, j)」で、buf2は行方向に移動していきますが、代入元であるDTは「列方向」に移動しながら代入をします。これは配列buf2と配列DTとで行・列が入れ違っているためです。

４９１行目「SL(buf1(i, KeyCol)) = WorksheetFunction.Transpose(buf2)」では、もとのSLデータに値を更新しています。 この時も配列の行・列を反転する必要があるのでTranspose関数を使用しています。

４９７行目以降で、完成したSLデータを順にListBox1に出力することで、各項目順に並んだリストを作ります。
まず４９７行目「m = 0」では、ListBox1の行を制御するカウンタ変数mを初期化（＝ゼロ）しています。

４９８～５０８行目は、三重のFor～Nextになっています。
一番外側のFor～Nextは「For i = 0 To SL.Count - 1」（４９８行目）で、カウンタ変数ｉを「SLデータの数（SL.Count）」分だけ回します。SLデータのインデックスに合わせ、ゼロから始めています。
真ん中のFor～Nextは「For k = 1 To UBound(SL.getbyindex(i), 1)」（４９９行目）で、カウンタ変数ｋを「SLデータ内の値である配列の行数」分だけ回します。ここでの配列は４７５行目のReDim、および４８５行目のReDimを１からスタートさせています（Transposeを通すと、１からのスタートに強制的になるため）ので、カウンタ変数ｋも１からにしています。
一番内側のFor～Nextは、「For j = 1 To 4」（５０２行目）で、カウンタ変数ｊを配列の列数（棚番、品名、個数、入庫日）だけ回しています。配列の列も１からスタートしていますので、ｊも１スタートです。

ListBox1は４列表示（５列目は縦スクロールバー用のダミー列）ですので、列を埋めていく一番内側のFor～Nextの一つ外側の５０１行目で「Me.ListBox1.AddItem ""」と「新しいリスト行」を作成しています。
その上で、５０３行目「Me.ListBox1.List(m, j - 1) = SL.getbyindex(i)(k, j)」で各列に「棚番、品名、個数、入庫日」のデータを書き込んでいます。なお、ListBox1のインデックスはゼロ始まりですので、列位置は「j-1」となります。
列方向の書き込みが終了したら、５０５行目「m = m + 1」でリスト行を１つ下げます。

なおTranspose関数を通過する時に、日付は「yyyy/mm/dd」→「yyyy/m/d」の表示に変換されてしまうようです。例えば「2021/07/01」→「2021/7/1」のような感じになるようです。
ですので、オプションボタンの選択によっては「日付が重複するデータ」の場合は「yyyy/m/d」、「日付が重複しないデータ」は 「yyyy/mm/dd」の表示形式になります（Excelの環境により違うかもしれません）。

混在を避けるにはFormat関数などを使えば良いと思いますが、今回は面倒なので揃えることはしていません。
また、SLデータの値である配列を「最初から行・列をひっくり返した状態にする」方法も考えられます。そうすればTranspose関数を使用せずに済みますので、日付の表示形式が混在する不具合もなくなりそうです。
但し「配列の行列が逆転」しているのは、配列加工の際には受け入れるとして、保存状態での逆転は好きではありません。

８ー２－４．ダイアログの終了ボタン

ダイアログの「閉じる」ボタンをクリックした時に呼び出されるClickイベントプロシージャが図８－７です。
５１５行目「Unload Me」で、単純にダイアログを終了させています。なお、ダイアログの右上×印をクリックしても、同じく「Unload Me」相当の動作となります。

513. '==========　⇩(31)　在庫リストダイアログの終了　============
514. Private Sub CommandButton1_Click()
515. 　Unload Me
516. End Sub

図８－７

９．最後に

今回システムでは、仕入れ時の価格データなどは項目に入れませんでした。しかし「先入れ先出し」法では、仕入れ時の価格から出庫時価格（売値）を計算するのも重要なことだと思いますので、必要でしたら入庫時に「単価」を入力できるようにしておき、出庫時にその単価から売値を計算する仕様に改造するのも良いと思います。

今までも同じなのですが、「完成した！」と思ったExcelのコードをエディタに貼り付け、１コード・１ワードについて１つずつ細かく説明をしていると、思わぬエラーや見逃している条件、別なコードの書き方の存在に気が付きます。今回の項でも少なくとも２０箇所は有ったと思います。
主原因は私の未熟さ・大雑把さなのでしょうが、自分の作ったものを一度人に説明してみると完成度が上がる　というのも事実かと思います。


先入先出の入出庫管理システム（it-061.xlsm）

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