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


【前回の記事】


前回からの続きです。

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

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

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

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

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

上図の”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

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

作ってみた感想など

私自身ラベラーは持っていなかったので、通常のプリンターで紙へ出力してバーコードリーダーで読み取るという所までは試してみました。

試してみた感想としては、結構使えますねこれ。

まず第一にコードを入力する手間が省けるというのは大きいです。

一覧から選ぶという方法もありますが、取り扱い商品数が多ければ探すのが面倒になります。バーコードならそういった不便さが一気に解消できます。

そして第二に人的ミスを減らす事ができるという点です。

手動や目視での商品検索だと、どうしても類似した見た目や一文字違いのコードと間違えてしまう可能性がありますが、そのミスを大幅に減らす事が可能です。

アルバイトを雇われていたり、人に手伝ってもらっている方なら尚更効果があると思います。

まぁ実際に商品販売のプロであるお店がバーコードを使って管理をしているんですから、使えない訳が無いですね。

何となく可能性は感じられたので、後はアイディア次第ですかねー。

また何か思い付いたら紹介していきたいと思います。

今回作ったエクセルのサンプルはこちら↓

関連記事と広告

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

コメント

  1. 匿名さん より:

    はじめまして。中小企業で事務をやっている者です。平均年齢55歳前後の会社なのでPCに詳しい人が殆どいなく、自分で好き勝手に仕事ができる状態です。旧式(手書きでリスト→エクセル)で在庫管理をしています。
    正直、取り扱う品も多くないので、在庫管理のソフトの導入などに意義を見出せずに、自分でなにかできないかな・・と数年前から思っていたところに記事を拝見して、「これならつかえそう!!」と思いました。しかも無料で公開していただいて有難うございます。Accessは今だと本当に使っている人いないですね。。。私はこういう機能がほしいなと思ったことがAccessでできるので使っています。これもネットで「初心者のAccessの使い方」を読んで作っただけなので、こうしてネットでノウハウを公開してもらえると、今度はVBAを学んでみよう!と思えました。

    • 藤美れいん より:

      早速お役に立てた様で嬉しい限りです!

      ExcelやAccessにしてもそうですが、使いこなす事ができれば欲しいものは大体作れるんですよね。
      更にマクロ機能やVBAを覚えると、作り込み次第で市販ソフトと同等のものも作る事が可能です。
      最初は難しいとは思いますが、ぜひ覚えてみて下さい!

  2. Anchor より:

    はじめまして
    まずは感謝、感謝です。まさに助けに船でした

    最近、担当していた部署の在庫管理を整備することになり、とりあえずラベルプリンターを導入し、バーコードをはじめとする各必要項目をプリントし明示することにしました。
    なんとか在庫の増減もタイムリーに管理できないかと更なる欲が出、ここにたどり着いた次第です。
    最初そのまま利用できそうかな?と甘い考えでデータベースをくっつけてみましたが、どうしても手を加えたい項目がでてきて大変でしたが、なんとか使えるような状態になりました。
    バーコードもVBAも初心者でしたので、想像以上に大変でしたが、シート名の変更や表示列の変更、表示項目の追加など手を加えさせていただきました。
    1つ気づいた点がありましたので、報告します
    バーコード発行値のセットは*なしでOKです。代わりにオブジェクトのプロパティでCODE39でデータの確認というところを「スタート/ストップ文字の付加」にすれば*がついてきます。
    VBAのとても興味を持ちましたが、本の分厚さを見ただけでテンションが下がります。