商品出庫画面のマスタデータ取得 [VBA在庫管理システムVer01#18]

前回、商品出庫画面の画面作成をおこないました。ここからプログラムを作成していきます。

この記事では、商品出庫画面のマスタデータ取得のプログラムを作成していきます。

VBA在庫管理システムVer01

コードの検索と置換

検索と置換機能は、検索範囲を３段階に設定できますので、活用してみましょう。

1. カレント プロシージャ

現在カーソルのあるプロシージャの 、Sub() ～ End Sub までの範囲。

2. カレント モジュール

プロジェクトエクスプローラーの、現在選択されているオブジェクトの範囲。

3. カレント プロジェクト

プロジェクトエクスプローラーに表示される、すべてのオブジェクトの範囲。

コードは、商品入庫画面からプロシージャ単位でコピペし、検索と置換機能を使用して Input → Output ・入庫 → 出庫に置換すると時短になります。

商品マスタのマスタデータ取得

変数の設定、データの取り込みまでは、商品マスタ画面と同一コードです。
データの格納部分で、コンボボックスの設定が異なります。

変数の設定

商品マスタデータの取り込み用変数の設定をしていきます。
VBEでfrmItenOutputを選択し、宣言セクションにデータ取得用の配列変数と列挙型変数をPrivateで宣言します。

Option Explicit

'データ取得用配列変数
Private aryItemMaster() As Variant

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

@1. 列挙型変数の宣言
列挙型変数は、1つの変数の要素毎に定数が設定できます。要素毎の定数は最初に定数を定義すると、２つ目以降は自動的に１づつ加算された定数が定義されます。削除項のように、直接定数を定義することもできます。

今回使用する変数では、下記の様に定数が定義されています。
wsItemMasterColumns.M商品ID = 1
wsItemMasterColumns.M商品名称 = 2
wsItemMasterColumns.M登録日 = 3
･･･
wsItemMasterColumns.M棚卸数 = 12
wsItemMasterColumns.M削除 = 20

これは、商品マスタシートの列数をあらわしたものであり、VBAコードでシートの列番号を確認する手間が大きく省け、マジックナンバー対策ともなります。

また、シートの構造が変更された場合でも、列挙型変数を修正するだけで、プロシージャのコードの変更は少なく済むというメリットがあります。
　

データの取り込み

商品マスタデータを配列変数 aryItemMaster() に取り込むプロシージャをサブルーチンとして作成します。

Sub GetItemMaster()

    Application.ScreenUpdating = False

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

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

'商品名称を昇順で並べ替え
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("B1"), order1:=xlAscending, Header:=xlYes

'変数の設定
Dim i As Long, j As Long
ReDim aryItemMaster(wsItemMasterRow - 2, 9) As Variant
j = 0

'配列変数への書込み
For i = 2 To wsItemMasterRow
    If wsItemMaster.Cells(i, wsItemMasterColumns.M削除) = 0 Then
        aryItemMaster(j, 0) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品ID)
        aryItemMaster(j, 1) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品名称)
        aryItemMaster(j, 2) = wsItemMaster.Cells(i, wsItemMasterColumns.M登録日)
        aryItemMaster(j, 3) = wsItemMaster.Cells(i, wsItemMasterColumns.M更新日)
        aryItemMaster(j, 4) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注先)
        aryItemMaster(j, 5) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注単位)
        aryItemMaster(j, 6) = wsItemMaster.Cells(i, wsItemMasterColumns.M梱包数)
        aryItemMaster(j, 7) = wsItemMaster.Cells(i, wsItemMasterColumns.M最大在庫)
        aryItemMaster(j, 8) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注点)
        aryItemMaster(j, 9) = wsItemMaster.Cells(i, wsItemMasterColumns.M棚位置)
        j = j + 1
    End If
Next

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

    Application.ScreenUpdating = True

End Sub

@1. シートの描画を止める
Application.ScreenUpdatingでFalsを設定すると、エクセルシートの動作が止められ、画面のちらつきを抑制できます。
また、シートの操作はコードの実行速度を大きく低下させますので、シートを扱うときは速度対策として有効です。

@2. 変数への取り込み
データブックを開いた後、M_商品シートとその最終行を変数に格納します。

@3. シートの並び替え
商品名称で検索性を持たせるため、商品名称昇順で並び替えます。

@4. 配列変数への取り込み
配列変数の要素数を確定させたあと、For〜Next構文でデータを取り込みます。
データは論理削除の構造なので、削除フラグが立っているものは取り込みません。

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

データの格納

商品マスタデータを商品名称コンボボックスに設定するため、UserForm_Initialize プロシージャにコードを追加します。
また、各コンボボックスの初期設定もおこないます。

Private Sub UserForm_Initialize()

'----サイズ調整---------------------------------------------------------------
    Me.Zoom = Me.Zoom * ((画面高さ * 高さ割合) / Me.Height)
    Me.Width = Me.Width * ((画面高さ * 高さ割合) / Me.Height)
    Me.Height = Me.Height * ((画面高さ * 高さ割合) / Me.Height)
'-----------------------------------------------------------------------------

'商品マスタ情報の取得
Call GetItemMaster

'商品名称コンボボックスの設定
With cmbItemName
    .ColumnCount = 10
    .ColumnWidths = "0;200;0;0;0;0;0;0;0;0"

    .List = aryItemMaster()
End With

'商品IDコンボボックスの設定
Dim i As Long

For i = 0 To cmbItemName.ListCount - 1
    cmbItemID.AddItem cmbItemName.List(i, 0)
Next

'出庫内訳コンボボックスの設定
With cmbOutputDetail
    .AddItem "通常出庫"
    .AddItem "NG出庫"
    .AddItem "在庫調整"
End With

'修正用出庫内訳コンボボックスの設定
With cmbCorrectOutputDetail
    .AddItem "通常出庫"
    .AddItem "NG出庫"
    .AddItem "在庫調整"
End With

End Sub

@1. 商品マスタデータの取り込み
GetItemMasterを呼び出し、商品マスタデータを配列変数aryItemMaster()へ格納します。

@2. 商品名称コンボボックスの設定
商品名称コンボボックスに配列変数aryItemMaster()を取り込み、ColumnWidthsで表示設定をします。

@3. 商品IDコンボボックスの設定
商品名称コンボの商品IDフィールドのみ、取り込みます。
商品名称コンボと商品IDコンボは、リスト内容は異なりますが、ListIndexが同一のリスト構造でデータが格納されます。

@4. 出庫内訳コンボボックスの設定
入力支援用にコンボボックスのリストをAdditemメソッドで設定します。
修正ボックスも同様に設定します。

@5. コンボボックスの初期化
商品マスタ画面では、コンボボックスの設定時にClearメソッドで内部リストを消去しましたが、商品出庫画面ではClearメソッドを入れてありません。
UserForm_Initializeプロシージャは初期設定ですので、コンボボックスには内部リストが無い状態なので、Clearメソッドは要りません。ただし、商品マスタ画面のようにプロシージャを再利用する場合には、内部リストの重複を避けるため、Clearメソッドを入れておきます。

次回は商品出庫画面の商品検索・出庫入力部分を作成します。
www.minizaiko.com