当WEBサイトではバーコード読み取りツールとしてWEBカメラとPython（と申しわけ程度にExcel）を使ってさまざまなシステムを作成してきました。
個人の環境ではハードウェアのバーコードリーダーを所持している人は極少数であるため、このほうが導入が簡単だと思われますが、会社で使用するとなると、PCにカメラがついていなかったり、Pythonのインストールが難しかったりで逆にハードルが高い可能性があります。
そこで今回はExcelとハードウェアのバーコードリーダーを使って在庫管理システムを構築します。
デモ動画
実際のうごきを動画にしましたのでご覧ください。音がでます。最初に出庫モード、途中で入庫モードに切り換えています。在庫の増減にご注目ください。
システム概要
バーコードリーダーは私のなかでは結構な値段がする装置という認識でしたが、今やAmazonで2,000円も出せば買えます。実際に使ってみて性能には全く問題ありません。ご想像の通り、低価格帯で覇権をにぎるチャイナメーカー製ですが、どうやったらこの値段で利益が出せるのかナゾです。
さておき、バーコードリーダーはPCからみると単なるキーボードです。読み取った値をキーボード入力としてPCへ送っています。人間が紙にかいてある数字を目で見て、それをキーボードで入力していくのと全く同じことを超速でやっています。人間の目の変わりがスキャナーということです。
このシステムではバーコードリーダーからのキー入力にVBAが介入し、シート上のどの商品の在庫を増減させるかを制御しています。
使用方法
バーコードリーダーはSuffixとしてEnterキーを送出できるものが必要です。よほどの粗悪品でない限りSuffix付与機能は搭載されており、標準でEnterキーになっています。確認するにはシートの何もないセルを選択してバーコードリーダーで適当なバーコードをいくつか読みます。入力が上書きされずに別個のセルに入っていけばSuffixはEnterキーに設定されています。
１．このページのダウンロードボタンからzipファイルを入手し、展開します。
２．展開したExcelファイルの在庫シートにJANコード、商品名、在庫入力します。
在庫一覧はテーブルになっています。テーブル内へ入力してください。サンプルデータが入力されていますので参考にしてください。
３．モードを出庫または入庫に切り換えます。
４．バーコードリーダーを接続します。
５．読み取り開始ボタンを押します。
バーコードリーダー読み取り中ウィンドウが表示されます。バーコードリーダー使用中はこのウィンドウを閉じないでください。ここに読み取ったコード値が表示されます。
読み取り中ウィンドウを表示する前にバーコードを読むと、現在アクティブなセルに読み取り値が入力されてしまいます。せっかく登録した商品名や在庫数がコード値で上書きされる可能性がありますのでご注意ください。
６．商品のバーコードを読み取ります。
７．モードに応じて読み取ったコードの在庫が増減します。
一覧にないコードを読み取ると、ウィンドウが次のような表示になり何も起こりません。
８．ログシートにログが記録されます。
商品名はXLOOKUP関数で在庫一覧から参照しています。最初の1個を読み取るまでは1行目のサンプルデータは消さないでおくと、次に読み取ったログに関数式が自動で反映されます。
９．バーコード読み取りが終わったら読み取り中ウィンドウを閉じます。
制御コードを読み取ることで、バーコードリーダーからモード変更や読み取り終了ができます。
制御コードシートを印刷して適当なところへ掲示するなどしてお使いください。
解説
システム概要に書いてあるとおり、バーコードリーダーはキーボードです。スキャナーで読み取った値をキーボード入力としてPCへ送ってきます。
Excel上で使いたい場合、キーボード＝バーコードリーダーからの入力は、なうカーソルがあるところにしか入らないという、普通に考えたら当然のことが問題になります。Excelシートのなうカーソルなセル（アクティブセル）に勝手にバーコードの値が入ってしまっては困るのです。バーコードを読み取ったときにアクティブセルがしかるべき場所であることを保証してあげなければなりません。
これにはワークシートのイベントを利用することができますが、リーダー以外の通常のキーボード入力にもイベント処理が介入することになり無駄です。そこで別の方向でアプローチします。バーコードリーダー使用中はキーボード入力がシートには一切出力されないようにします。キー入力の内容を確認し、適切な処理のあとはじめてシートへ反映してやります。
この操作を実現するにはVBAのUserFormコントロールが最適です。UserFormをShowModalプロパティをTrueにして表示すると、UserFormを閉じない限りキーボード入力がUserFormに送られます。バーコードリーダーからの入力をすべてUserFormで取得できます。
で、今回使用するUserFormは次のような構成になっています。実際にうごかすときは不要なコントロールは見えないようにウィンドウ外へ追いやっています。
①のテキストボックス（txtBuffer）には、タブオーダーを一番最初に設定してあります。よってFormを開いた直後に①txtBufferへカーソルが移動し、キーボード入力=バーコードリーダー読み取り値が①txtBufferへ入力される状態になります。
バーコードリーダーから読み値のキー入力が送出されるとtxtBufferに入力されていきます。読み値に続けてSuffixでEnterキーが入力されると①txtBuffer_BeforeUpdateイベントが発生します。イベントリスナーへコードを書いておきバーコードリーダー入力値に対応したシートへの処理を実行します。
タブオーダーは次は②のテキストボックス（txtResetDummy）へ設定してあります。故に①でEnterキーが押されると、②へカーソルが移動し②txtResetDummy_Enterイベントが発生します。リスナーへ
１．①txtBufferの値をクリア
２．①txtBufferへカーソルを移動
という処理を書いておきます。これにより①txtBufferが空の状態となり次のバーコードリーダーからの入力を待ち受けます。なお①txtBufferの値をクリアした時点でもBeforeUpdateが走ってしまうので空文字の場合は何もしないというSelect Case文を入れておきます。
以上のしくみでバーコードリーダーからの入力を唯一①txtBufferだけが受け取るという制御をしています。
なお②は入力には使わないことがわかりやすいように背景色を変えているだけで、使用不可に設定しているわけではありません。
UserFormコントロールへ書いてあるソースコードです。
Option Explicit
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
'Private Declare PtrSafe Function BeepAPI Lib "kernel32.dll" Alias "Beep" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
Const DATA_TABLE_SHEET_NAME As String = "在庫"
Const MODE_STATE_CELL_ADDRESS As String = "e1"
Const LOG_TABLE_SHEET_NAME As String = "ログ"
Const EXIT_CODE As String = "99999995"
Const TOGGLE_MODE_CODE As String = "88888880"
Public modeStateCell As Range
Private Sub txtBuffer_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim result As Boolean
Dim code As String
code = txtBuffer.Value
Select Case code
Case ""
Exit Sub
Case EXIT_CODE
Unload frmReading
Case TOGGLE_MODE_CODE
If modeStateCell.Value = "出庫" Then
modeStateCell.Value = "入庫"
Else
modeStateCell.Value = "出庫"
End If
Exit Sub
Case Else
result = updateStock(code)
End Select
If result Then
lblCode.Caption = txtBuffer.Value
lblCode.ForeColor = vbBlack
Call logging(code, modeStateCell.Value)
Else
lblCode.Caption = "登録なし"
lblCode.ForeColor = vbRed
End If
End Sub
Private Sub txtResetDummy_Enter()
txtBuffer.Value = ""
txtBuffer.SetFocus
End Sub
Private Sub UserForm_Initialize()
Set modeStateCell = Worksheets(DATA_TABLE_SHEET_NAME).Range(MODE_STATE_CELL_ADDRESS)
End Sub
Private Function updateStock(code As String) As Boolean
Dim mode As String
mode = modeStateCell.Value
Dim stock As Long
Dim tb As ListObject
Set tb = Worksheets(DATA_TABLE_SHEET_NAME).ListObjects(1)
'コード検索
Dim tgt As Range
Set tgt = tb.ListColumns("JANコード").DataBodyRange.Find(code)
If Not tgt Is Nothing Then
'テーブルにコードがある場合
Dim row As Long
row = tgt.row
stock = tb.ListColumns("在庫").Range(row).Value
'対象行へスクロール
ActiveWindow.ScrollRow = row
With tb.ListColumns("在庫").Range(row)
If mode = "出庫" Then
.Value = stock - 1
Else
.Value = stock + 1
End If
.Interior.ColorIndex = 6
'BeepAPI 2000, 300
Sleep 300
.Interior.ColorIndex = 0
updateStock = True
End With
Else
'テーブルにコードがない場合
'BeepAPI 200, 300
updateStock = False
End If
End Function
Private Sub logging(code As String, mode As String)
With Worksheets(LOG_TABLE_SHEET_NAME).ListObjects(1)
Dim newRow As ListRow
Set newRow = .ListRows.Add
Dim row As Long
row = newRow.Index + 1
Dim cnt As Long
If mode = "出庫" Then
cnt = -1
Else
cnt = 1
End If
.ListColumns("JANコード").Range(row).Value = code
.ListColumns("数量").Range(row).Value = cnt
.ListColumns("日時").Range(row).Value = Now
End With
End Sub今回の最重要ミッションはリーダーの入力をどうやっつけるかにつきるので、UserFormのしくみがすべてといっても過言ではなく、他には特に難しい処理はしていません。テーブルをListObjectとして取得して検索、書き換え、追記をしています。ListObjectまわりの半分くらいはBingAI先生にコーディングしていただきました。念のためリファレンスも確認しましたので大丈夫だと思います。
BeepAPIは音を鳴らします。私の環境ではリーダー本体から鳴る仕様だったのでコメントアウトしてあります。必要に応じお使いください。
デモ動画では見やすさを優先して対象行へのスクロールを無効にしています。
たぶん全員がやらかすと思いますが、読み取り開始ボタンを押す前にバーコードリーダーで読んでしまいあさってのセルにコード値が入る事故防止のために、在庫シートのWorksheet_ActivateとWorkbook_Openイベントにアクティブセルをテーブルから外すコードを入れています。
Private Sub Workbook_Open()
Worksheets(1).Range("h1").Select
End Sub
Private Sub Worksheet_Activate()
Range("h1").Select
End Subテーブルの列名はハードコーディングなので、変更、追加する場合はコード書き換えが必要です。シート名やモード設定セル番地、制御コードは最初に定数で定義しているので変更する場合は書き換えが必要です。
その他、ブックを開いた時点で読み取りウィンドウを出したり、在庫下限警報を追加したり、ご自由に改造してお使いください。
ファイルを入手する
まとめ
バーコードリーダーが思いのほか安価で手に入るので、キーボードとして認識される宿命上、使い勝手は決して良いとは言えませんが、工夫しだいではありだなと。カメラとPythonでやっていたネタも純Excel版で作ろうかな。バーコードリーダーとPythonのシステムも考えたいです。ラズパイでそれ専用機にしてしまうのも面白そうです。
おわり。
