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

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

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

VBA在庫管理システムVer01

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

変数の設定

商品マスタデータの取り込み用変数の設定をしていきます。
VBEでfrmItenMasterを選択し、宣言セクションにデータ取得用の配列変数と列挙型変数を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 ステートメントでデータを取り込みます。
データは論理削除の構造なので、削除フラグが立っているものは取り込みません。

※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
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "0;200;0;0;0;0;0;0;0;0"

    .List = aryItemMaster()
End With

'商品リストボックスの設定
With lstItemMaster
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "40;250;0;0;150;60;60;60;60;60"

    .List = aryItemMaster()
End With

End Sub

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

@2. 各コントロールの設定
商品名称コンボと商品リストに配列変数aryItemMaster()を取り込み、ColumnWidthsで表示設定をします。
商品名称コンボと商品リストは、表示状態は異なりますが、同一のリスト構造でデータが格納されます。

　
　
次回は商品マスタ画面の商品検索部分を作成します。
www.minizaiko.com