続・エクセルを使った簡単なバーコード式在庫管理システムを作る

【前回の記事】

https://rabbitfoot.xyz/barcode-manage-system/

商品販売をしている方で、在庫管理にエクセルを利用している方って結構多いと思います。私自身も自作ツールを使って管理していますが、いざ在庫更新をしようと思った時に一々商品コードを手打ちして検索したり、リストから目視で探したりするのって結構面倒臭いんで...

前回からの続きです。

今回はソース部分を記述してツールを完成させます。

- 目次 -

1 シート側に記述するコード
2 標準モジュール側に記述するコード
3 ユーザーフォームの作成
4 作ってみた感想など
5 バーコードサンプル

シート側に記述するコード

前回記事で最初の方に書いた仕様の一つに、

「バーコードの読み取みと同時に一覧から商品を検索して表示する」

といったものがありましたね。まずはこれを実装します。

上図の”B3セル”部分の内容が変更された場合に自動で処理を走らせたいと言う事なので、「Worksheet_Change」という関数を使用します。

これはExcel側の機能として最初から用意されている関数で、ワークシートのセルがユーザーまたは外部リンクにより変更された時に、この中に書かれている処理が実行される形になります。

今回この入力受付用のシートに「main」と言う名前を付けているので、以下のプロジェクトエクスプローラーの中から「Sheet5(main)」と名前が付いているシートを選択します。

開いたら以下の処理を記述します。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Target, Range("B3")) Is Nothing) And Target.Value <> "" Then
        idProcesser Target.Value
    End If
End Sub

判定処理の内容は

「変更されたセルが”B3”　かつ　セルに何か文字が入力されたか」を判定しています。

両方の条件を満たしていた場合のみ、「idProcesser」という処理に入力されたコードを渡す様になっています。idProcesserの中身は次で作成していきます。

ちなみにWorksheet_Changeは標準モジュールに記述しても意味が無いので、必ずワークシート側に記述する様にしましょう。

標準モジュール側に記述するコード

ここで先程記述した「idProcesser」の中身を書いていきます。
※関数名は何でも良いので、分かりやすいものに変更して構いません。

主な内容は「受け取ったコードが既に登録済みであるかどうか」の判定ですね。

判定した結果『登録済み』であれば在庫更新を行い、

『未登録』であれば新規登録を行いバーコードの発行。という感じです。

解説する程のコード量でも無いと思うので、一気に全部載せてみます。

Option Explicit

Const SHT_MAIN = "main"
Const SHT_LIST = "list"
Const SHT_CODE = "barcode"

Enum リスト
    ID_ = 1
    品名_
    在庫_
End Enum

'==========================================================
'受け取ったIDの登録、在庫増減処理
'==========================================================
Public Sub idProcesser(id As String)
    Dim entryData As String
    
    If existID(id) Then
        update id
    Else
        entryData = UserForm1.makeData(id)
        entry entryData
        
        'バーコード発行値のセット
        ThisWorkbook.Worksheets(SHT_CODE).Range("A1").Value = "*" & id & "*"
        ThisWorkbook.Worksheets(SHT_CODE).Activate
    End If
End Sub

'==========================================================
'IDが登録済みかどうかを判定して返す True=存在する
'==========================================================
Function existID(id As String) As Boolean
    Dim findData As Object
    Dim result As Boolean

    Set findData = Nothing
    Set findData = ThisWorkbook.Worksheets(SHT_LIST). _
            Columns(リスト.ID_).Find(id, LookAt:=xlWhole)
    
    If Not (findData Is Nothing) Then result = True
    
    existID = result
End Function

'==========================================================
'新規登録
'==========================================================
Sub entry(allData As String)
    Dim row As Integer
    Dim tmpData As Variant
    
    tmpData = Split(allData, "$")
    
    With ThisWorkbook.Worksheets(SHT_LIST)
        row = .Cells(Rows.Count, リスト.ID_).End(xlUp).row + 1
        .Cells(row, リスト.ID_).Value = tmpData(0)
        .Cells(row, リスト.品名_).Value = tmpData(1)
        .Cells(row, リスト.在庫_).Value = tmpData(2)
    End With
End Sub

'==========================================================
'在庫更新
'==========================================================
Sub update(id As String)
    Dim findData As Object
    Dim name As String
    Dim stock As Integer
    Dim tmpData As Variant

    With ThisWorkbook.Worksheets(SHT_LIST)
        Set findData = Nothing
        Set findData = .Columns(リスト.ID_).Find(id, LookAt:=xlWhole)
        name = .Cells(findData.row, リスト.品名_).Value
        stock = .Cells(findData.row, リスト.在庫_).Value
        
        tmpData = Split(UserForm2.updateData(id, name, stock), "$")
        
        .Cells(findData.row, リスト.品名_).Value = tmpData(0)
        .Cells(findData.row, リスト.在庫_).Value = tmpData(1)
    End With
End Sub

とりあえず動く事だけを目標とした単純な内容です。

IDや品名に「$」が使われていたら正常に動かないという欠陥丸出しの設計ですが、その辺は他の記号に置き換えてもらえればいいかと・・・。

大まかな流れと考え方だけ分かってもらえれば良いと思います。

ちなみにバーコードの元になる値をシートにセットする際に、「”*” & id & “*”」と*（アスタリスク）で挟んでいる事に気付きましたか？

これはバーコードの始まりと終わりを表す為の記号となり、Code-39ではこの記号も含めてバーコードを出力しないと読み取ってくれませんので注意しましょう。

さて、上のコードを見て頂ければ気付くと思いますが、このコードだけではまだ完成ではありません。

「UserForm1.makeData(id)」という記述がある通り、ユーザーフォームも用意しています。

ということで登録・更新用のユーザーフォームが残っていますので、最後にそちらを作成しましょう。

ユーザーフォームの作成

必要なフォームは2つです。

・新規登録用

・在庫更新用

ですね。簡単に以下の様な感じで作りました。

登録フォームは入力中に「やっぱりIDを変更したい」と思う事もあると思いますので、全項目修正できる様にテキストボックスで作成しています。

更新フォームはIDを変更されると困りますのでラベル形式に変更。在庫数はスピンボタンを用意してマウス、キーボードどちらにも対応した感じですね。

毎回入出庫の個数が決まっているなら「○個増」とかのボタンを用意するのも良さそうです。

それぞれソースは以下の通り。

[登録フォーム側]

Public Function makeData(id As String) As String
    Dim result As String
    
    '受け取ったIDをセット
    idbox.Value = id
    
    Me.Show
    
    result = idbox.Value & "$" & namebox.Value & "$" & stockbox.Value
    makeData = result
End Function

Private Sub CommandButton1_Click()
    Unload Me
End Sub

[更新フォーム側]

Public Function updateData(id As String, name As String, stock As Integer) As String
    Dim result As String
    
    '受け取ったデータをセット
    idLabel.Caption = id
    namebox.Value = name
    stockbox.Value = stock
    
    Me.Show
    
    result = namebox.Value & "$" & stockbox.Value
    updateData = result
End Function

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub SpinButton1_SpinDown()
    Dim stock As Integer
    
    stock = stockbox.Value
    stock = stock - 1
    If stock < 0 Then stock = 0
    
    stockbox.Value = stock
End Sub

Private Sub SpinButton1_SpinUp()
    Dim stock As Integer
    
    stock = stockbox.Value
    stock = stock + 1
    
    stockbox.Value = stock
End Sub

こちらも解説する程の内容は無いですね。