Excelマクロ(VBA)で作る管理システム②

Excel
スポンサーリンク

今回はExcelマクロを使った仕入管理システムについての2回目です。仕入データ入力について、コードとプロシージャについて解説していきたいと思います。
今回の記事は仕入管理システムだけでなく、顧客管理、売上管理、在庫管理への転用など汎用性の高いコードになっておりますので、ご自身が業務等に使えそうな部分だけでも是非参考にしていただけたらと思います。
(画面設定やブックの保護・解除などシステムとして動作制限をかけるコードについては省略しています。)
VBAコード全体は基本的なものが中心ですので、ご安心ください。それではまいります。

Excelマクロ(VBA)で作る管理システム①
マクロ(VBA)で仕入管理システムを作成する方法を紹介しています。使用しているコードやユーザーフォームは仕入だけではなく在庫管理や売上管理、顧客管理など汎用性の高いコードです。是非ご自身の業務に活用してください。
スポンサーリンク
スポンサーリンク

概要

完成図①
完成図②

シートは3つあります。

  1. 「商品マスタ」シート:商品データ(バーコード、商品名、取引先、仕入単価)を登録しておくシートです。
  2. 「DB」シート:仕入が発生した時に入力しておくシートです。マスタシートからデータ参照して日付、バーコード、取引先名、数量、単価を入力します。バーコードからマスタデータを検索可能です。 ※今回はここの部分の説明です。
  3. 「仕入帳」シート:月次仕入帳を作成します。DBシートで入力したデータから月次仕入帳を作成します。
スポンサーリンク

ユーザーフォームの解説「仕入フォーム」

今回は「DB」シート上で、使用するユーザーフォーム(仕入フォーム)について解説していきます。
ユーザーフォーム自体の説明は前回の記事をご覧ください。

スポンサーリンク

フォームコントロール

仕入商品入力フォーム

①③④⑤テキストボックス

①③④⑤はテキストボックスです。四角の中にデータを入力できます。一般的な入力欄です。

②スピンボタン

②の年と月と日はスピンボタンです。見た目の通り左右ボタン(アップとダウン)をクリックすると日付の値が増減します。

※年・月・日それぞれのオブジェクト名は、spnNen、spnTuki、spnHiに変更しています。

年のオブジェクト名

スピンボタンのコード

Sub サブ_日付増減(Tani As String, Zougen As Integer)'===日付テキストボックスの値を変える
    Dim Mydate As Date
    If IsDate(txt日付.Text) = True Then'日付テキストボックスの日付が日付データならば
        Mydate = DateAdd(Tani, Zougen, txt日付.Text)'DateAdd関数
        txt日付.Text = Format(Mydate, "yyyy/m/d")
    Else
        txt日付.Text = Format(Date, "yyyy/m/d")
    End If

End Sub
'===「年」スピンボタンをクリック===
Private Sub spnNen_SpinDown()
    サブ_日付増減 "yyyy", -1'日付増減プロシージャへ(引数:年の表示形式と-1)
End Sub
Private Sub spnNen_SpinUp()
    サブ_日付増減 "yyyy", 1'日付増減プロシージャへ(引数:年の表示形式と+1)
End Sub

'===「月」スピンボタンをクリック===
Private Sub spnTuki_SpinDown()'日付増減プロシージャへ(引数:月の表示形式と-1)
    サブ_日付増減 "m", -1
End Sub
Private Sub spnTuki_SpinUp()'日付増減プロシージャへ(引数:月の表示形式と+1)
    サブ_日付増減 "m", 1
End Sub

'===「日」スピンボタンをクリック===
Private Sub spnHi_SpinDown()'日付増減プロシージャへ(引数:日の表示形式と-1)
    サブ_日付増減 "d", -1
End Sub
Private Sub spnHi_SpinUp()
    サブ_日付増減 "d", 1'日付増減プロシージャへ(引数:日の表示形式と+1)
End Sub

'===初期設定(ユーザーフォームのイニシャライズ)
Private Sub UserForm_Initialize()
    txt日付.Text = Format(Date, "yyyy/mm/dd")'日付テキストボックスに今日の日付を設定
End Sub

コード全体の流れ

①ユーザーフォームイニシャライズ(初期設定)で日付テキストボックスに今日の日付を設定

②年月日のスピンボタンをクリックして引数(日付形式、増減値)を日付増減プロシージャに渡す

③日付増減プロシージャで受け取った引数(日付形式、増減値)からデータを更新する

日付増減プロシージャのDateAdd関数について

日付増減プロシージャ内のMydate = DateAdd(Tani, Zougen, txt日付.Text)についての解説です。

引数の解説

DateAdd(時間間隔(日付形式)、増減させる日付・時間(増減値)元になる日付・時間)

よって、日付テキストボックスのデータの時間間隔引数(yyyyまたはmまたはd)を増減引数(+1または-1)させます。

年・月・日のスピンボタン

スピンボタンには、SpinUpイベントとSpinDownイベントがあります。下のコードで言うと、SpinUpをクリックすると、サブ_日付増減プロシージャに引数”yyyy”と1が渡されます。その引数が先に説明したプロシージャ内のDateAdd関数に渡されて、①日付テキストボックスの年が変更されるわけです。

Private Sub spnNen_SpinUp()
    サブ_日付増減 "yyyy", 1
End Sub

⑥リストボックスと⑦コマンドボタン

まず、⑦コマンドボタンの検索ボタンについて解説します。

検索ボタンの機能

③バーコードテキストボックスに入力した数字を含むまたは一致する商品マスタのレコードを「商品マスタ」シートから検索して⑥リストボックスに表示する。

検索ボタンのコード

Private Sub cmb検索_Click()
    Dim data As Variant
    Dim str As String
    Dim i As Long
    Dim Code As String
'解説①
    str = txtバーコード.Text 'バーコード入力欄の内容を変数に格納
    
    If str = "" Then 'バーコード欄が空欄ならば
        MsgBox "バーコードを入力してください"
        Exit Sub
    End If
    
    str = StrConv(str, vbNarrow) '文字列変数を半角に変換
'解説② 
    With Sheets("商品マスタ") '商品マスタシートのフィルタ解除
        If .AutoFilterMode Then
            .Range("A1").AutoFilter
        End If
        data = .Range("A5").CurrentRegion '商品マスタシートのデータベースを配列に格納
    End With
    
    Application.ScreenUpdating = False
'解説③    
    With Sheets("検索") '検索シートに配列を格納
         .Cells.Clear
         .Range("A1", .Cells(UBound(data), UBound(data, 2))) = data
         
         '昇順に並べ替え
         .Range("A1").CurrentRegion.Sort key1:=.Range("A1"), order1:=xlAscending, Header:=xlYes
  
 '解説④  
        'A列のバーコードとバーコード欄を照合して、含まれていなければ削除のループ
        For i = UBound(data) To 2 Step -1
            Code = StrConv(.Cells(i, 1), vbNarrow) '半角に変換
             
            If InStr(Code, str) = 0 Then '検索バーの数字がリストの数字になければ
                .Cells(i, 1).EntireRow.Delete '行の削除
            End If
        Next i
        Erase data
        data = .Range("A1").CurrentRegion '検索シートの削除済みデータを配列変数に格納
        
    End With
  '解説⑤  
    With ListBox1 '配列変数をリストボックスに反映
        .ColumnCount = -1
        .List = data
        .SetFocus
    End With
    
    Application.ScreenUpdating = True
    
End Sub


コード全体の流れ

  1. 「商品マスタ」シートのデータをすべて「検索」シートに格納
  2. 「検索」シートに格納したすべてのデータを並び替える
  3. バーコードテキストボックス内のデータと照合して一致または部分一致しないレコードは削除する
  4. 残ったデータ(一致するデータ)をリストボックスに表示する
検索ボタンの機能

解説①

str = txtバーコード.Text 'バーコード入力欄のデータを変数に格納

バーコードテキストボックス内のデータをstr変数(ストリング変数)に格納しておきます。

解説②

With Sheets("商品マスタ") '商品マスタシートのフィルタ解除
        If .AutoFilterMode Then
            .Range("A1").AutoFilter
        End If
        data = .Range("A5").CurrentRegion '商品マスタシートのデータベースを配列に格納
    End With

一旦、「商品マスタ」シートにフィルタがかかっていると、全てのデータを変数格納できないのでフィルタ解除します。そして「商品マスタ」シートにあるデータをdata変数(バリアント)に格納します。

CurrntRegionプロパティについての説明は以前の記事をご覧ください。

VBAにおけるCurrentRegionの活用
VBAのCurrentRegionについてCurrentRegionでの行、列の取得や行数、列数、最終行、最終列の取得、それらを踏まえた活用事例を紹介しています。また、静的配列、動的配列についても解説しています。

解説③

With Sheets("検索") '検索シートに配列を格納
         .Cells.Clear
         .Range("A1", .Cells(UBound(data), UBound(data, 2))) = data
         
         '昇順に並べ替え
         .Range("A1").CurrentRegion.Sort key1:=.Range("A1"), order1:=xlAscending, Header:=xlYes

検索シート

「検索」シートを準備します。そこに先ほどのdata配列変数を入れます。

検索シートへのdata配列変数の格納範囲を表すセルは、Ubound関数を使用しています。

Ubound関数・・配列の最大インデックス(添え字)を取得します。          (例)data(10,12)の場合、Ubound(data)※1次元配列の最大インデックスを取得は「10」、Ubound(data,2)※2次元配列の最大インデックスを取得は「12」を返します。

Ubound関数でdata配列変数の1次元の最大インデックスと2次元の最大インデックスを取得します。それがセル範囲の行、列になります。

あとは、バーコードの列を昇順で並べ替えします。

解説④

'A列のバーコードとバーコード欄を照合して、含まれていなければ削除のループ
        For i = UBound(data) To 2 Step -1
            Code = StrConv(.Cells(i, 1), vbNarrow) '半角に変換
             
            If InStr(Code, str) = 0 Then '検索バーの数字がリストの数字になければ
                .Cells(i, 1).EntireRow.Delete '行の削除
            End If
        Next i
        Erase data
        data = .Range("A1").CurrentRegion '検索シートの削除済みデータを配列変数に格納

ForNextステートメントを使ってデータを下の行から1行づつ拾っていきます。(Step-1で一番下の行からループさせていくのは、行を削除した時に選択する行がずれないようにするためです。(上から削除していくと行番号が変わってしまう)これはVBAでの定型の手段です。)

InStr(インストリング関数)でセル内の1列目のバーコードデータと解説①で説明したstr変数のデータの一致を調べます。

Instr関数・・・Instr(検索対象文字列検索文字)で検索対象文字列の中の検索文字の位置を返す   (例)Instr(“ABCDE”、”B”)は2を返します。

上記のコードの場合、IF関数の条件分岐で”0”ならば(一致文字が無いならば)行ごと削除となります。

一致または部分一致がないデータ(行)を削除して、残ったデータをまたdata配列変数に格納します。

解説⑤

With ListBox1 '配列変数をリストボックスに反映
        .ColumnCount = -1'表示列数
        .List = data'参照範囲はdata配列変数
        .SetFocus
    End With

先ほどのdata配列変数を⑥リストボックスに反映させます。使用しているリストボックスのプロパティは以下になります。

ColumnCountプロパティ・・リストボックスに表示する列数を指定します。1列だけなら1、2列なら2と入力します。-1は全ての列数を表示します。

LIstプロパティ

リストボックスは配列形式で管理されています。リストボックス内の上から先頭行が「0」、左から先頭列が「0」となります。Listプロパティはそれらを操作できます。

リストボックス

上記のように表示させようとする場合

ListBox1.List(0,0) =”バーコード”

ListBox1.List(0,1) =”商品名”

・・・・

のようになります。

ただし、このように表示させようとする場合、注意点として、ListBox1.AddItem “”と事前に入力しておきます。

AddItemメソッドはリストボックスに値を追加するメソッドです。

上記のようなやり方で値を表示させたい場合は、AddItemメソッドで空データを登録しておくことでListプロパティのデータを表示できます。

ListBox1.AddItem “”                            ListBox1.List(0,0) =”バーコード”                      ListBox1.List(0,1) =”商品名”

ただ、今回はdata配列変数を使っているので、その場合は

ListBox1.List = dataで一括で表示させることができるというわけです。

SetForcusプロパティ

SetForcusプロパティ・・リストボックスを選択するという命令です。検索ボタンをクリックすると最後、リストボックスが選択される処理です。

検索ボタンコード引用・参考:YouTube「こいこい人工知能研究室」

【参考書籍】

スポンサーリンク

まとめ

次回、仕入フォームの続きを行います。今回表示したリストボックス内のデータから選択したデータの数量をテキストボックスに入力すると自動計算する方法をやっていきたいと思います。

次回もお楽しみに!!

Excelマクロ(VBA)で作る管理システム③
マクロ(VBA)で仕入管理システムを作成する方法の3回目です。ユーザーフォームのリストボックスの詳しい説明やワークシートへのデータの転記方法を解説しています。使用しているコードやユーザーフォームは仕入だけではなく在庫管理や売上管理、顧客管理など汎用性の高いコードです。是非ご自身の業務に活用してください。

コメント

タイトルとURLをコピーしました