前回、商品在庫画面の画面作成をおこないました。ここからプログラムを作成していきます。
この記事では、商品マスタの在庫更新プログラムを作成していきます。
VBA在庫管理システムVer01
在庫更新の準備作業
動的２次元配列変数について
ここまでVBAでのリストデータの取り扱いには、動的２次元配列変数を使用してきました。ここからは少し高度な使用方法が含まれます。
そこで、動的２次元配列変数について、下記の記事で解説していますので参考にしてみてください。
在庫数の取り扱いについて
商品在庫表には、現在在庫数と有効在庫数の２つの在庫数を表示させ、在庫数を監視する指標とします。
現在在庫数とは、棚卸数を基準に、前日までの出庫数(出庫済み数)を減算し、当日までの入庫数(入庫済み数)を加算した在庫数で、当日の使用可能在庫数となります。
有効在庫数とは、現在在庫数から当日以降の出庫数(出庫予定数)を減算した未来の在庫数です。有効在庫数はマイナス表示になる場合もあります。
発注Flagは、有効在庫数に発注残数:翌日以降の入庫数(入庫予定数)を加算した在庫数が、発注点に達した時に発行します。
現在在庫数・有効在庫数・発注Flagは、在庫データから下記の数量を取得すると、それぞれ計算で求められます。
棚卸日、棚卸数
出庫済み数、出庫予定数
入庫済み数、入庫予定数
発注点
このシステムでは、棚卸日・棚卸数・発注点はマスタデータとして登録されているので、出庫済み数・出庫予定数・入庫済み数・入庫予定数を算出すれば、現在在庫数・有効在庫数・発注Flagが求まります。
また、在庫の推移などは、下記の記事で解説していますので参考にしてみてください。
受払抽出シートの追加
データブック（在庫管理DATA.xlsx）に受払履歴抽出用のシートを追加します。
検索条件は３項目、入力しておきます。
商品ID 入出庫日 削除
※削除条件は初期値として０を入力しておきます。
抽出項目は４項目、入力しておきます。
入出庫ID 入出庫日 入出庫区分 入出庫数
在庫量や受払履歴の抽出には、エクセルのフィルターオプション機能を活用していきます。
VBAではエクセルの機能を使用する場合がありますが、エクセル上での動作状況を充分に理解する必要があります。
フィルターオプション機能をあまり使用したことの無い方は、下記の記事で解説していますので参考にしてみてください。
標準モジュールの作成
標準モジュールを追加し、オブジェクト名を在庫更新とします。
商品マスタの在庫データを更新するプロシージャはユーザーフォームの動作とは異なりますので、標準モジュールに作成していきます。
商品マスタの在庫更新
在庫更新
商品マスタの在庫データを更新するプロシージャをサブルーチンとして、標準モジュール 在庫更新に作成します。
Sub GetItemStockUpdate() Application.ScreenUpdating = False '作業ブックを開く Workbooks.Open データ位置 & "在庫管理DATA.xlsx" 'オブジェクト変数の取得 Dim wsItemMaster As Worksheet, wsItemInOut As Worksheet, wsStockExtraction As Worksheet Set wsItemMaster = Workbooks("在庫管理DATA.xlsx").Sheets("M_商品") Set wsItemInOut = Workbooks("在庫管理DATA.xlsx").Sheets("T_入出庫") Set wsStockExtraction = Workbooks("在庫管理DATA.xlsx").Sheets("受払抽出") '在庫更新 Dim Buf() As Variant Dim i As Long '最終行の取得 Dim wsItemMasterRow As Long wsItemMasterRow = wsItemMaster.Cells(Rows.Count, 1).End(xlUp).Row '変数の設定 ReDim Buf(wsItemMasterRow - 1, 6) As Variant With wsStockExtraction For i = 0 To wsItemMasterRow - 2 '抽出条件の設定 .Activate .Cells(2, 1).Value = wsItemMaster.Cells(i + 2, 1).Value .Cells(2, 2).Value = ">" & wsItemMaster.Cells(i + 2, 11).Value '受払抽出 wsItemInOut.Columns("A:H").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=.Range("A1:C2"), _ CopyToRange:=.Range("F1:I1") '出庫済み数 Buf(i, 0) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), " 出庫", .Range("G:G"), "<" & Date) '出庫予定数 Buf(i, 1) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), " 出庫", .Range("G:G"), ">=" & Date) '入庫済み数 Buf(i, 2) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), "<=" & Date) '出庫予定数 Buf(i, 3) = WorksheetFunction.SumIfs(.Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), ">" & Date) '現在在庫 Buf(i, 4) = wsItemMaster.Cells(i + 2, 12) - Buf(i, 0) + Buf(i, 2) '有効在庫 Buf(i, 5) = Buf(i, 4) - Buf(i, 1) '発注Flag If Buf(i, 5) + Buf(i, 3) <= wsItemMaster.Cells(i + 2, 9) Then Buf(i, 6) = "〇" Else Buf(i, 6) = "" End If Next End With '製品マスタへの書き込み wsItemMaster.Range("M2").Resize(UBound(Buf, 1) + 1, UBound(Buf, 2) + 1).Value = Buf '作業ブックを閉じる Workbooks("在庫管理DATA.xlsx").Close savechanges:=True Application.ScreenUpdating = True End Sub
@1. シートの描画を止める
Application.ScreenUpdatingをFalsに設定します。
@2. 変数への取り込み
データブックを開いた後、M_商品・T_入出庫・受払抽出シートと、M_商品の最終行を変数に格納します。
@3. 配列変数の設定
１レコードにつき、７個のデータが格納できるように設定します。
@4. 在庫データの取得
商品マスタデータを１行づつループしてデータを取得します。
① フィルターオプション機能で、商品IDを基準に、棚卸日より後の入出庫履歴を抽出します。
② 抽出データにSumIfs関数を利用して、出庫済み数・出庫予定数・入庫済み数・入庫予定数を集計し、配列変数に取り込みます。
③ 棚卸数を基準に、②で取得したデータを利用して、現在在庫数・有効在庫数を計算し、配列変数に取り込みます。
④ ②､③で取得したデータを利用して、発注Flagを発行し、配列変数に取り込みます。
@5. 製品マスタへの書き込み
製品マスタシートへの書き込みは、書き込みの起点となるセル位置("M2")から、UBound関数を使用した Resizeでセル範囲を拡張させて指定し、配列データを一括で書き込みます。
@6. ブックの立ち下げ
シートに在庫情報を書き込みましたので、ブックは保存して閉じます。
また、Application.ScreenUpdatingもTrue設定に戻します。
在庫更新のプログラムは、コードはシンプルですが、SumIfs関数を利用しているため実行速度は遅いです。
SumIfs関数は、フィルターした非表示部のデータを含め全行を走査しますのでデータが多くなると更に速度が低下します。そこで、フィルターオプション機能で必要データを抽出し、抽出データにSumIfs関数を利用しています。
ただし、フィルターオプション機能での別シートへの抽出も、xlFilterCopyのデータペーストが入るので、実際は実行速度が低下します。
結果、トレードオフの関係なので、バランスを取り、マスタ１件につき１回だけフィルターオプション機能を使用し、合計取得にはSumIfs関数を利用するプログラムとしています。
また、配列変数やDictionaryを利用すると実行速度は向上しますが、可読性は低下します。
この解説では、コードの可読性を優先し、速度低下はユーザビリティを向上させることで対応していきます。
在庫更新フォームの作成
在庫更新用に、ユーザーフォームを２つ作成していきます。
在庫更新選択画面
在庫更新選択用に、新たにユーザーフォームを作成します。
VBEを開き、[挿入]‐[ユーザーフォーム]を選択します。
プロジェクトエクスプローラーに[フォーム]‐[UserForm1]が追加され、コードウィンドウにユーザーフォームが表示されます。
プロパティウィンドウで詳細設定します。
※プロパティ設定後のユーザーフォーム
ラベルを利用してヘッダー・フッターを作成し、”在庫更新しますか？”のコメントを追加します。さらに、"は い"・"いいえ" のコマンドボタンを設置します。
※プロパティ設定後のユーザーフォーム
※VBA実行時の在庫更新選択画面
在庫更新画面
在庫更新用に、もう一つ、ユーザーフォームを作成します。
VBEを開き、[挿入]‐[ユーザーフォーム]を選択します。
プロジェクトエクスプローラーに[フォーム]‐[UserForm1]が追加され、コードウィンドウにユーザーフォームが表示されます。
プロパティウィンドウで詳細設定します。
ラベルを利用してヘッダー・フッターを作成し、”在庫更新中です。。”のコメントを追加します。
※プロパティ設定後のユーザーフォーム
※VBA実行時の在庫更新画面
在庫更新の実行
まず、在庫更新のプログラムは実行速度が遅いので、在庫更新選択画面を表示し、必要な時だけ実行できるようにします。
さらに、在庫更新実行時は在庫更新画面を表示させます。意外と、更新画面があるだけで待ち時間が短くなった気がします。これは、画面が移り変わるときに画面を認識する時間 や 画面の文字(在庫更新中です。。)を頭の中で読む時間 などが 純粋な待機時間を短く感じさせているのです。
では、コードを作成していきます。
メニュー画面の商品在庫ボタン
Private Sub btnItemStock_Click() frmItemStockSelect.show Unload Me End Sub
@1.在庫更新選択画面の表示
メニュー画面から直接在庫画面を開くのではなく、メニュー画面 → 在庫更新選択画面 → 在庫画面の順に画面遷移を変更します。
そこで、メニュー画面の商品在庫ボタンをクリックすると、在庫更新選択画面が開くようにコードを修正します。
選択画面の"はい"ボタン
Private Sub btnYes_Click() '更新表示開始 frmItemStockWait.show frmItemStockWait.Repaint '在庫更新 Call GetItemStockUpdate '在庫画面の表示 frmItemStock.show Unload Me '更新表示終了 Unload frmItemStockWait End Sub
@1.在庫更新画面の表示
プロシージャの実行途中に在庫更新画面を表示するときに、Showメソッドだけでは画面の画像表示はされません。Showメソッドでフォームを表示させた後、Repaintメソッドで画像を描画させるとプロシージャの実行途中でも画面表示可能です。
@2.在庫更新の実行
サブルーチンを実行します。
@3.在庫画面の表示
在庫画面を表示し、在庫更新選択画面・在庫更新画面を閉じます。
選択画面の"いいえ"ボタン
Private Sub btnNo_Click() '在庫画面の表示 frmItemStock.show Unload Me End Sub
@1.在庫画面の表示
在庫更新は実行せずに在庫画面を表示し、在庫更新選択画面を閉じます。
画面を表示させて、動作確認しましょう。
また、データ数を増やして実行速度も確認してみましょう。
次回は、商品在庫画面のマスタデータ取得のプログラムを作成していきます。
www.minizaiko.com
