【Excel VBA】会社で使える低コスト バーコード照合システム
【まえがき】
PCが普及し、スマホが普及し、
1次元のバーコード、QRコードによる照合システム化が進んできた昨今ですが、
どこの企業でもバーコード管理化が進んでいるか？と言われれば答えはＮｏ．です。
進んでいるのは基本的に大企業ばかりで、中小は依然として進んでいない、
というのが現状かと思います。
コスト云々は確かにありますが、一番の原因は運用の仕方がわからない。
事だと思います。
【２つの運用手段】
バーコード照合システムの運用の仕方は、私の独断と偏見ですが、
大きく２点に分かれる、と考えています。
１つ目は、
高額なハンディタイプのバーコードリーダーを使用するタイプのやり方。
無線通信安定していれば、どこでも読み取ったデータがやり取りできたり、
バーコードリーダー自体でOK/NG判定等の処理やビープ音が出せます。
より高額なものは画面のレイアウトから送受信設定まで編集でき、
C言語が習熟できていなくてもある程度プログラムを弄れるようになっています。
但し数万単位で買える安価なものは、システムが脆弱ですぐにフリーズ
してしまったり、データがなくなってしまうことがしばしばありますので、
自動的に保証手厚い10万以上のリーダーを買わなければ心もとないです。
２つ目は、
読み取り専用のバーコードリーダーを使用した方法です。
この方法はバーコードリーダーで読み取ったデータを有線や無線で
PCに飛ばすやり方です。
こちらの最大の弱点は、
単純に読み込むだけのバーコードリーダーなので、
ビープ音(エラー音)はPCから発しないといけない事と、
表示画面がないので読み取り結果はディスプレイを見なければならないことです。
つまり、PC/ディスプレイを見ながら照合を行う必要があり、範囲が限定的です。
(電子回路を組んでアンドン等に飛ばす等手段もありますが少し面倒かと)
しかし、この条件下であれば、ネットワーク回線なしの環境下で運用できますし、
購入しなければならないバーコードリーダーは
2，3000円のものでも十二分に機能を発揮してくれます。
そして、ほとんどの企業の社内帳票はExcelで管理してますので、
Excelに直接入力されるようにしまえば非常に合理的です。
こちらの手法で簡単なバーコード照合システムを作っていきたいと思います。
【STEP1】
それでは、まずは前準備としてプログラムの実行ボタンをつくりましょう。
開発タグから挿入ボタンを選択。
(※開発タグがない場合は、ファイル-オプションからセキュリティレベルを
下げましょう。)
続いてフォームコントロールの中からボタンを選択し、
どこでも良いのでセル内をドラッグしてみしょう。
するとこんな画面が表示されます。
何も気にせずOKボタンを押します。
ボタンが作成されました。
ボタンをクリックしてみましょう。
エラーがでました。当然ですね。
何も情報を入れてませんので空っぽですよというメッセージです。
【STEP2】
Find 関数を使用しないで照合プログラムを作成する
バーコードリーダーやＱＲコードリーダーの処理方法は単純です。
読み取ると同時に “文字を一瞬で入力した後、Ｅｎｔｅｒを押す”
というのがほとんどもリーダーの仕様です。
ですので、つくるプログラムは、入力した文字を押してエンターキーを押すと、
あらかじめ入れていたリストの文字と読み込んだ文字が一致するものを
探す処理を行い、一致/不一致した場合何らかの処理を行う、
というものを作れば良いわけです。
まずは基本からです。簡単な照合プログラムを作ってみましょう。
図のようにI列に読み込み対象、Ｊ列に照合対象と適当な言葉を入れました。
読み込み対象に入力されている文字“かきくけこ”が照合対象のリストにあれば、
対象をBold表示する、というプログラムをこれから作ってみます。
step1でつくったボタン１を右クリックしてマクロの登録という項目を選択します。
続いて新規作成をクリック。
プログラムの入力画面が開きました。
下記を入力していきます。
Sub ボタン1_Click()
Dim moji1 As String
Dim hensu1 As Integer
hensu1 = 3
Do Until Cells(hensu1, "J") = ""
If Cells(hensu1, "J") = Cells(3, "I") Then
Cells(hensu1, "J").Font.Bold = True
End If
hensu1 = hensu1 + 1
Loop
End Sub
------------------------------------------------------------------------------------------------
解説していきましょう。
Dim moji1 As String
・moji1 という文字を一時置きして格納するものを作ります、
と宣言しています。
Dim hensu1 As Integer
・moji1 という数値を一時置きして格納するものを作ります、
と宣言しています。
他にも色々な変数がありますが、複雑なプログラムでもなければ
String と Integer の２つだけおさえておけば大体問題ないと思います。
hensu1 = 3
・先ほど作成した数値を格納するためのhensu1は”３”とする。
Do Until Cells(hensu1, "J") = ""
～
hensu1=hensu1+1
Loop
・Do Until ～Loopの定型文です。かなりよく使う繰り返し演算の基本になりますので、
覚えておきましょう。
セルのhensu1行、Ｊ列が空白になるまでhensu1をhensu1+1を繰り返す。
これを入れておかないと永遠にLoopから抜け出せないのでエラーになってしまいます。
If Cells(hensu1, "J") = Cells(3, "I") Then
Cells(hensu1, "J").Font.Bold = True
End If
・IF ～ End If の定型文です。
もし、～だったら何々をする、です。
上記のDo Until とループの間に入れています。
上記でLoopしている間、セルhensu1行、”Ｊ”列のものがセル３行"I"列にあるのもと
一致しているときに、
Cells(hensu1, "J").Font.Bold = True
つまり、セルhyensu1行、"J"列のフォントのボールド化をTrue(有効)にする、
という内容になっています。
ここでは、(3,"I")と(4,"J")が"かきくけこ"で一致になりますので、
(4,"J")がボールド化されるというわけです。
実行してみましょう。
”かきくけこ”がボールド化されました。
【Excelでバーコードを作成する】
さて、これまでの作業で"検索"のプログラムができるようになりました。
これを応用すればバーコード照合プログラムが作れるようになるのですが、
その前に、バーコードリーダーによる照合プログラムは
一般的にどのように使われているのかを理解しておく必要があります。
また、Excel上でバーコード自体を作れるようにしておく事が重要です。
web上にはバーコードやQRコードの生成サイトは多数ありますが、
プログラム上でバーコードを作成できれば、
それだけ色々な応用が可能になってくる為です。
【バーコードフォントを手に入れる】
QRコードは2次元の為画像として扱いますが、
バーコードは１次元なので文字列として扱われます。
つまり、フォントになります。
バーコード１つにしても、web上には様々なフォントが存在しますが、
一般的なものは code39 のフォントですので、そちらを準備しましょう。
(以下、code39の場合で解説をしています。)
web上からcode39を入手したら、フォントフォルダへデータを入れましょう。
Cフォルダで Fonts と検索すればヒットします。
次に、Excelを開いていた場合は一度閉じましょう。
再度開くと取り込んだフォントが反映されます。
これでcode39のバーコードがフォントリストに入りました。
【code39の仕様】
code39の仕様については、専門のサイトに詳しく書かれていますが、
簡単に説明すると
*test* ← " * "で前後を挟む事で、その内部の文字列を読み取りますよ、
という規則になっており、この場合、読み込んだ情報は test のみになります。
" * "が前後になくてもバーコードフォントにできますが、
" * "は読み取りのキーになっている文字ですので全く読み取ってくれませんので
注意が必要です。
【バーコード照合の仕組み】
バーコード照合システムの簡単なフローを下にかきました。
一般的には先にマスターのバーコードを読み、照合先を読み込むと
一致しているか、ひも付きになっているか等のアクションを取る形になります。
VBAで作成するのは、図の青矢印部になります。
具体的にどのようなプログラムを実装しなければならないかというと
・読み込んだデータが一致していれば設定した処理を行う（OK/NG判定等）。
→上記のLoop関数で1個1個の照合を行う方法や、Find関数を用いた方法で検索、
条件分岐はIF関数を利用すれば作れます。
・読み取った記録を残す。
→いつ、何を読み取ったのかログを記録します。記録が残らなければ
本当に照合したのかしていないのかわかりません。
ここで懸念があります。
記録を残す処理自体はそれほど難しくはないのですが、
照合するバーコード同士が全く同じものである場合、
当たり前ですがどちらを読み込んでも同じ記録になります。
そうするといつ、何につけられているバーコードを読み込んだのか
記録に残りません。
全く同じバーコードじゃないと照合できないんだから当たり前では？
と思う方もいらっしゃると思いますが手はあります。
気になる方は以下、読み取り方法のパターンを参照ください。
【バーコード照合のパターン】
・運用パターン１
対象A＝対象B のパターンです。全く同じバーコード同士で
完全一致している文字列の場合OK処理を行う、というタイプです。
こちらは一番簡単で作りやすいのですが、先に説明した通り、
どちらを読み込んでも同じ文字列の為、
記録として残すには大きな欠陥が残る運用方法です。
・運用パターン２
対象A≠対象B のパターンです。パターン１と異なり、対象A,Bの文字列が
全く異なるタイプになります。
全く違うバーコードなのになぜ？と思う方もいらっしゃると思いますが、
Aをマスターとして読み込んだ場合、Bになれば一致というプログラムを
作ってしまえばいいのです。
例えば、あらかじめエクセルのセルに表を作成しておき、
エクセル1行J列にmasterという文字を登録、
隣の1行k列にsampleという文字列を入れておく。
この状態で Aのバーコードを読み込んだとき、
表（台帳）に登録されているかのエラー検知を行ったあと、
Bのバーコードを読み込むと、Bのバーコードの文字列が隣列のJ列の文字列と
一致しているかどうかの確認を行う、とういう仕組みにすればOKです。
・運用パターン3
対象A≠対象B のパターンです。運用パターン２同様に異なる文字同士なのですが、
頭の文字が１文字違うだけのものです。
この方法は、読み込んだバーコードデータがA、Bどちら由来のものなのかを
記録するために利用されたり、1番目にAを読み込まないとBの読み込みを
できないようにする為に利用されます。
（運用パターン２でも可能です。そちらのほうが厳格に設定/応用できます。）
こちらのパターンはAのバーコードを読み込んだとき、
記録としては Psample として残し、照合使用時には sample として
使用されます。その後、Bのバーコード読み取り時も同じ処理を行ったとき、
それぞれの文字が一致しているかを照合します。
こちらの方法のメリットはパターン２のようにリストを事前に作成しなくても
トレースが取れる運用ができる点です。
_________________________________________________________________________________
【２点照合サンプル】 上記運用パターン３
簡単な２点照合プログラムを作成してみました。
P_XXXXX vs. S_XXXXX にて部分一致を行い、
照合不一致の場合はエラーメッセージ。
一致した場合は履歴をセルに入れ込むプログラムです。
このプログラムがあれば
P_XXXXX(Xは任意)とS_XXXXX(Xは任意)の文字列からなるバーコード、QRコードにて
照合確認が可能です。
先に述べた通り、リーダーは"文字を入れてエンター"をするだけの道具なので
コードでは"文字を入れてエンターキーを押されたとき"から始まる照合プログラムを
作成するだけで良いです。
この処理は以下のコードを入れて実行します。
___________________________
Private Sub TextBox1(任意)_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
end If
end sub
____________________________
※KeyCode=13は世界共通でEnterキーに割り当てられている数字です。
細かい事はまず置いておき、コード入れる環境を作ります。
・ユーザーフォームの作成
エクセル上の任意のセルを使う方法でも構いませんが、
特定のセルをクリックしてから処理を行うのは億劫です。
この場合はユーザーフォームを作成し、タグをプログラムでコントロールするのが一般的だと思います。
よって、今回はユーザーフォームにて照合を行うようにしました。
開発画面からユーザーフォームを作成し、照合用に２つのTextBoxを入れ込みます。
上側がTextBox1、下側の項目がTextBox2となっていることをプロパティで確認。
プロパティにてTabStopがTrueになっているとEnter入力時に自動でタブ移動を
します。
自分でコントロールしたいのでTextBox2側のTabStopはFalseに設定しておきます。
次に、ユーザーフォーム呼び出しボタンを作成します。
※ファイル起動時に自動起動するように設定できますが、
今回はボタンクリックイベントで呼び出すようにします。
"開発"→"挿入"ボタンで"ボタン"を選択→任意のセルでドラッグしてボタンを設置。
マクロの登録ボタンでUserFomCall_Clickと命名します。
（タイピングミスでFormでなくFomとなってしまいましたが気にしない…）
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
Sub UserFomCall_Click()
UserForm1.Show
End Sub
________________________________________________________
ユーザーフォームを呼び出すコードは
ユーザーフォーム名.Show のみです。簡単ですね。
念のため、動作確認して次に進みます。
あとは
"UserForm1"→"右クリック"→"コードの表示" で以下のコードを入れ込みます。
少々長いですが、IF関数が多いだけなのでじっくり読み込めば理解できると思います。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
'ここに照合処理を入れ込む
If Len(TextBox1.Value) > 2 Then
If Left(TextBox1.Value, 1) = "P" Then
'TextBox2のプロパティ初期値は"TabStop"を"False"に設定しています。
TextBox2.SetFocus
'TextBox1のタブ移動制御。勝手にTextBox1へタブ移動させないようにします。
'（コードで制御する）
TextBox1.TabStop = False
Else
MsgBox "Pから始まるデータを入れてください"
TextBox1.Value = ""
End If
Else
MsgBox "文字数が不正です"
TextBox1.Value = ""
End If
End If
End Sub
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim P, S As String
'初期化
P = ""
S = ""
If KeyCode = 13 Then
'ここに照合処理を入れ込む
If Len(TextBox2.Value) > 2 Then
If Left(TextBox2.Value, 1) = "S" Then
P = Right(TextBox1, Len(TextBox1) - 1)
S = Right(TextBox2, Len(TextBox2) - 1)
If P = S Then
MsgBox "照合一致"
'履歴をセルに残すコードを呼び出します。
'このまま記述しても問題ありませんが、若干コードが読みづらくなるので別でコードを書いてます。
Call InputCell
Else
MsgBox "照合不一致"
End If
'正しい処理が完了したらTextBox1の値も空白(="")にする。
TextBox1.Value = ""
Else
MsgBox "Sから始まるデータを入れてください"
GoTo Esc
End If
'TextBox1の"TabStop"を"True"に設定にしてTextBox1へタブが戻るようにしています。
TextBox1.TabStop = True
Else
MsgBox "文字数が不正です"
TextBox2.Value = ""
End If
Esc:
'処理が終わったら値を空白（=""）にする。
TextBox2.Value = ""
End If
End Sub
Public Sub InputCell()
Dim LastRow As Integer
'F列の最終行を取得します。
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
'F列最終行にTextBox1の値を入れます。
Cells(LastRow + 1, "F") = TextBox1.Value
'G列最終行にTextBox2の値を入れます。
Cells(LastRow + 1, "G") = TextBox2.Value
'H列最終行に日付と時間を入れます。(Date と Time)
Cells(LastRow + 1, "H") = Date & "_" & Time
End Sub
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
これで完成です。
動作が完了したら、あとは有線or無線のリーダーをPCに繋ぐだけです。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
・急所
・If KeyCode = 13 Then ・・・
上記の通り、エンターキー(KeyCode)が押されたときのみ・・・
・If Len(TextBox2.Value) > 2 Then・・・
テキストボックスの文字数制限を設けています。
２文字以下はエラーにするようにしています。文字数指定は任意です。
・If Left(TextBox2.Value, 1) = "S" Then・・・
Left関数で１文字目のみ抜き取り、これが”S”の場合のみ処理を行うように
しています。これを入れる事で、"S"から始めるコード以外は
全く受け付けないようにしています。
・P = Right(TextBox1, Len(TextBox1) - 1)・・・
Right関数で右からTextBox1の文字列数-1の文字（Len関数）を
抜き取って変数Pに格納しています。
P_XXXXX と S_XXXXX での照合を行うのではなく
1文字目を抜き取った _XXXXX と _XXXXX で完全一致しているか否かの確認を
プログラム上で行う為です。
・LastRow = Cells(Rows.Count, "F").End(xlUp).Row・・・
よく使うコードです。入力セルの最終行を取得しています。
上記のコードでは空のセル（最終行＋１行）を指定し文字入力するために
行の変数を格納するために使用しています。
