ous">

小さな在庫管理

在庫管理導入からシステム作成まで詳細解説！

トップ > システムVer01 > 棚卸機能作成 棚卸実行　[VBA在庫管理システムVer01#33]

棚卸機能作成 棚卸実行　[VBA在庫管理システムVer01#33]

システムVer01 VBA

前回、棚卸作業の前半部分を解説しました。

この記事では、棚卸作業の後半部分を解説します。


VBA在庫管理システムVer01

棚卸の流れ

システムの棚卸は、下記の作業フローで進めます。

1. 棚卸集計表の出力
　　商品在庫画面から出力
2. 実棚の計量
　　棚卸集計表用紙への記入
3. 棚卸集計表への入力
　　棚卸集計表シートへの入力
4. 棚卸実行
　　システム棚卸の実行

フローに沿って、システムの追加機能などを解説していきます。

この記事では４の、棚卸実行について解説します。
　

棚卸実行の準備

出力ボタンの作成

ここまで、メニュー画面は初期状態のままでボタンが並んでいるだけでしたが、フレームを利用し画面を整えます。
画像を参考にして、メニュー画面を修正してみて下さい。

※プロパティ設定後のユーザーフォーム

メニュー画面に棚卸実行ボタンを追加します。

システム管理のフレームを作成して、棚卸実行のコマンドボタンを追加し、プロパティウィンドウで詳細設定します。

※プロパティ設定後のユーザーフォーム

　　

標準モジュールの作成

標準モジュールを追加し、オブジェクト名を棚卸実行とします。

棚卸を実行するプロシージャはユーザーフォームの動作とは異なりますので、標準モジュールに作成していきます。
　

システム棚卸の実行

変数の設定

商品マスタデータの取り込み用変数の設定をしていきます。
VBEで標準モジュール 棚卸実行を選択し、宣言セクションに列挙型変数をPrivateで宣言します。

Option Explicit

'wsItemMasterの項目列設定
Private Enum wsItemMasterColumns
    M商品ID = 1
    M商品名称
    M登録日
    M更新日
    M発注先
    M発注単位
    M梱包数
    M最大在庫
    M発注点
    M棚位置
    M棚卸日
    M棚卸数
    M出庫済み
    M出庫予定
    M入庫済み
    M入庫予定
    M現在在庫
    M有効在庫
    M発注Flag
    M削除
End Enum

@1. 列挙型変数の宣言
標準モジュール 棚卸実行では、商品マスタシートの全ての列を列挙型変数で宣言します。
　

棚卸実行

商品マスタの棚卸データを更新するプロシージャをサブルーチンとして、標準モジュール 棚卸実行に作成します。

Sub GetItemInventoryUpdate()

'棚卸実行選択画面の非表示
Unload frmItemInventorySelect
    
'インプットボックスを設定し、棚卸日を取得
Dim myMsg As String, myTitle As String, 棚卸日 As String
    myMsg = "棚卸日を入力してください" & vbCr & "(例:2023/03/31）"
    myTitle = "棚卸日入力"
    棚卸日 = Application.InputBox(prompt:=myMsg, Title:=myTitle, Default:=Format(Date, "yyyy/mm/dd"), Type:=2)

'インプットボックスの入力値を判定
If 棚卸日 = "False" Then
    Exit Sub
End If

    Application.ScreenUpdating = False

'棚卸集計データの取得---------------------------------------------------------------------------------------------------
    
'ファイル名の格納
Dim FileName As String
FileName = データ位置 & Format(棚卸日, "yyyymmdd") & "棚卸集計表.xlsx"

'ファイルの存在判定
If Dir(FileName) = "" Then
    'プログラム終了
    frmMainMenu.Show
    MsgBox "ファイルが見つかりません。", vbCritical, "確認"
    Exit Sub
Else
    '作業ブックを開く
    Workbooks.Open FileName
End If

'オブジェクト変数の取得
Dim wsInventoryReport As Worksheet
Set wsInventoryReport = Workbooks(Format(棚卸日, "yyyymmdd") & "棚卸集計表.xlsx").Sheets("棚卸集計表")

'棚卸集計データの取り込み
Dim aryInventory() As Variant
Dim i As Long

'最終行の取得
Dim wsInventoryReportRow As Long
wsInventoryReportRow = wsInventoryReport.Cells(Rows.Count, 1).End(xlUp).Row

'変数の設定
ReDim aryInventory(wsInventoryReportRow - 4, 1) As Variant

'変数への取り込み
For i = 5 To wsInventoryReportRow
    aryInventory(i - 5, 0) = wsInventoryReport.Cells(i, 1)
    aryInventory(i - 5, 1) = wsInventoryReport.Cells(i, 8)
Next

'作業ブックを閉じる
Workbooks(Format(棚卸日, "yyyymmdd") & "棚卸集計表.xlsx").Close savechanges:=False

'棚卸集計データの書込---------------------------------------------------------------------------------------------------

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理DATA.xlsx"
    
'オブジェクト変数の取得
Dim wsItemMaster As Worksheet
Set wsItemMaster = Workbooks("在庫管理DATA.xlsx").Sheets("M_商品")

'最終行の取得
Dim wsItemMasterRow As Long
wsItemMaster.Activate
wsItemMasterRow = wsItemMaster.Cells(Rows.Count, 1).End(xlUp).Row

'棚卸集計データの書き込み
Dim j As Long

For i = 2 To wsItemMasterRow
    For j = 0 To UBound(aryInventory, 1)
        If wsItemMaster.Cells(i, M商品ID) = aryInventory(j, 0) Then
            wsItemMaster.Cells(i, M棚卸日) = Format(棚卸日, "yyyy/mm/dd")
            wsItemMaster.Cells(i, M棚卸数) = aryInventory(j, 1)
            Exit For
        End If
    Next
Next

'作業ブックを閉じる
Workbooks("在庫管理DATA.xlsx").Close savechanges:= True

    Application.ScreenUpdating = True

'棚卸実行完了画面の表示
frmItemInventoryUpdate.Show

End Sub

@1. ユーザーフォームの消去
棚卸実行選択画面をとじます。

@2. 棚卸日の取得
インプットボックスの機能を利用し、ユーザーに棚卸日を入力して貰います。

@3. 入力値の判定
インプットボックスのキャンセルが押された場合は、プロシージャーを抜けます。

A.データの取得：棚卸集計表シートからデータを配列変数に取得します。

@4. ファイル名の格納
変数 棚卸日 を利用し、棚卸集計表のファイル名を成形し、変数 FileName に格納します。

@5. ファイルの存在確認
Dir関数を使用し、ファイルが存在するか判定します。
ファイルがない場合はプロシージャーを抜け、ファイルが有ればファイルを開きます。

@6. 配列変数の設定
集計シートの最終行を取得し、配列変数の要素数を確定させたあと、For〜Next構文でデータを取り込みます。

@7. ブックの立ち下げ
シートを操作してますので、初期化するため、ブックは保存せずに閉じます。

B.データの書込：配列変数に格納した棚卸集計表データを、商品マスタシートに書き込みます。

@8. 作業ブックを開く
データブックを開き、商品マスタシートを変数に格納します。
このとき、最終行も取得します。

@9. 棚卸集計データの書込み
商品マスタシートをループして、１レコードづつ書き込んでいきます。
商品マスタシートの商品IDと配列変数の商品IDが合致したら、棚卸日と棚卸数を書き込んでいきます。

@10. ブックの立ち下げ
シートにデータを書き込みましたので、ブックは保存して閉じます。

@11. 終了フラグ
棚卸実行完了画面を表示して、処理は終了です。
　

棚卸実行フォームの作成

棚卸実行用に、ユーザーフォームを２つ作成していきます。
　

棚卸実行選択画面

棚卸実行選択用に、frmItemStockSelectをコピーして、新たにfrmItemInventorySelectを作成します。

※VBA実行時の棚卸実行選択画面

　

棚卸実行完了画面

棚卸実行完了後の終了フラグ用に、 frmItemInventoryReportをコピーして、新たにfrmItemInventoryUpdateを作成します。
　
※VBA実行時の棚卸実行完了画面

　

システム棚卸の実行

メニュー画面の棚卸実行ボタン
Private Sub btnInventoryUpdate_Click()

frmItemInventorySelect.Show

End Sub

@1. 棚卸実行選択画面の表示
直接、棚卸実行をおこなうのではなく、メニュー画面 → 棚卸実行選択画面 の画面遷移を作成します。
システム全体の更新となるので、２段階の選択にして、後戻りできるようにします。
　

選択画面の"はい"ボタン
Private Sub btnYes_Click()

'棚卸実行
Call GetItemInventoryUpdate

End Sub

@1. システム棚卸の実行
サブルーチンを実行します。
　

選択画面の"いいえ"ボタン
Private Sub btnNo_Click()

'フォームを閉じる
Unload Me

End Sub

@1. 棚卸選択画面の非表示
棚卸は実行せずに、棚卸実行選択画面を閉じます。
　

実行画面の閉じるボタン
Private Sub btnClose_Click()

'フォームを閉じる
Unload Me

End Sub

@1. 棚卸実行画面の非表示
棚卸実行画面を閉じます。


画面を表示させて、動作確認しましょう。
動作確認時は、データブックをコピーしバックアップをとることを心がけましょう。

　

システムの棚卸機能ができました。
次回はメニュー画面の追加機能を作成していきます。
www.minizaiko.com

MIFDUP

コメントを書く