Excelとバーコード

今回のお題は、

です。照合といえばそうですかね。

正直、Excelは難しい・・・。

先日

「患者1人に対して、薬剤1つの組み合わせがあり、それを簡易的に監査できないか？」

と聞かれまして。できれば今ある環境で済ませたいと。

Accessの方が作りやすいとは言ったものの、そんなもの無いといったところの方が多いですし。

では何があるかと言いますと、Excelです。ほぼどこの職場にもあると思いますので、「Excelでなんとかできないか？」と思うことありますよね？

Excelはできることが多すぎて、それだけテクニックも多いですからそれを全て覚えるなんて無理な話ですが、これほど簡単にそろう開発環境は他には無いと思いますし、立派な開発環境だと思うのは私だけでは無いはず。そして開発環境と言えば、そう。プログラミングです。

まあ、Excelでプログラミングまですると言う人はどう考えても少数派なのですが、こんな事できるらしいということを知っているのと知らないのとでは、その後の選択肢が大きく変わります。知らなければ調べようともしませんし。昔、パッ○マン作られたのを見たときは感動したもんです。さすがにあれは誰にでもは無理ですが、関数使って計算させたり、書式設定で色変えたりというのではなく、「ボタン」押したらどうにかなるという使い方です。

私の場合はAccessありきで、それをExcelでやるには？という考え方で今回も書いていますが、そうなるとあの「画面」をどう作るのか？ということになり、そこで使うのが「フォーム」です。Accessでは当たり前の機能ですが、その簡易版みたいなものがExcelでも使えます。ツールバーの中にある入力用のフォームではなく、マクロの挿入から自分で作る「フォーム」です。

では作成していきます。

まずはExcelをデータベースっぽく使いたいので一覧表を作成します。

この表を見たら、なんとなくやりたいことは分かるでしょうか？

ということなんですが、患者検索もバーコード使いたいですよね。ということで、

列Bは患者IDをバーコードにしただけなのですが、Excelは古いバージョンだとそのままバーコードの印刷はできません。

ではどうするかというと、CODE39のフォントを入れるだけです。検索すると、無料で公開して下さっている方がおられますのでそれをダウンロード、ファイル（.ttf）を右クリックするとインストールが選択できます。

あとはバーコードフォントが選べますので

上のようになります。ただし

となっています。これ大切です。それ以外の文字が含まれるとバーコードができませんので注意してください。

実際の列Bは上図のように

=IF(A2="","","*"&A2&"*")

と入力されています。

毎度おなじみIf文ですが、

「もし空白なら(空白)のまま、そうで無ければ患者IDの前後に"*"印を付けて表示しろ」

という意味です。なので通常のフォントで表示すると

こうなってます。

この「*」ですが、スタート/ストップキャラクタとかいうものだそうでcode39には必要です。なければバーコードリーダーで読み取れません。

最近のExcelにはバーコードコントロールがあるかもしれませんのでそれ使ってもOKです。

このバーコードを印刷して何かに貼っておけば、患者検索に使えるということなのですが、注意がありまして、

バーコードの前後に枠線とかあると、バーコードを認識しない場合があります。上の図もそのために中央寄せにして前後にわざわざ余裕を持たせています。

そのままの設定で医薬品のGS1を入力すると数値として認識されるため、頭の「0」が消えてしまいます。するとどうなるかと言いますと、表のデータは「104987896010923」となりますが、バーコードを読み取ると「0104987896010923」となりますので、当然「違う」となってしまいます。

利用するデータによってはこの「0」が消えてる物もありますので注意してください。

ここまでが下準備になります。

では監査プログラムを作ってみましょう。

今回は「音」を鳴らすという時点でプログラミングが必要になりますが、必要最低限「間違った場合」のみ鳴らすようにします。

合っていた場合と、間違っていた場合に鳴らす音を切り替えたい場合は、もう一手間かかりますので今回はパスします。

利用するバージョンによって表示させる方法は違いますが、Visual Basic Editorとか言われるものを開き、挿入からフォームを選択します。昔はツール→マクロの中にありましたが、最近は開発から開くのかな？

分からなければ検索してください。

ここに4つテキストボックスを配置します。ラベルとサイズは好きに調整してください。

フォームの名前は「UserForm1」、テキストボックスの名前は上から順に、TextBox1～4となっていますが、左下赤丸部分で確認できますし、変更もできます。

でもExcelってやっぱりすごいですよね。ユーザー多いから、やりたいことを調べたらソースコードまですぐ出てくるし。

動きとしては「TextBox1」で患者番号のバーコードを読み取るとTextBox2～4に氏名、薬剤名、GS1がそれぞれ表示され、続けて薬剤のGS1を読み取り、間違っていたら音を鳴らす。って感じかな。

ではTextBox1部分をダブルクリックしてみます。

この

Private Sub TextBox1_Change()

End Sub

の間に色々書いていきます。TextBox1がオブジェクト名なので、バーコードを読み取るために作成したテキストボックス「TextBox1」がChange、つまり変更時に処理しますよってことなんですが、これも少し考えないといけないところもありますが今はこのまま進めます。

まず変数を宣言します。変数ってのは便利なメモ帳とでも思っておいてください。詳しく知りたければ他で調べて下さい。

Dim ABC As Range

ABCという名前のメモ帳作って、そこには範囲をメモしますよ。って感じです。オブジェクト型の変数なのだそうです。

とりあえず作った変数は後で使います。

今からTextBox1に入力した値によって処理を分けていくのですが、もしそれが空白だった場合、たとえば、読み取り前に、前回読み取った値を消したとかの場合は何もしたくありません。そこで、

If UserForm1.TextBox1 = "" Then

Else

End If

このように書きます。IfからEnd Ifまでがセットです。よくEnd Ifを書き忘れてエラーを起こすので、Ifを入力したらEnd Ifも先に入力しておきましょう。

もしUserForm1のTextBox1が空白ならという意味です。

続けて空白ではなかった場合の処理を考えてみましょう。

ここでTextBox1に入力される可能性があるのは「患者ID」のバーコードか「GS1コード」ということになりますが、上にも書いたように、まずは「患者ID」で検索、あればその患者のデータを表示、なければGS1を読み取っているはずなので、正しいか比較するということになります。

どこに入力するか間違わないように。そうではない場合なので、Elseの後です。

まずは

Range("J1").Value = TextBox1.Value

で読み取った値をSheetのどこでもいいので保存しています。下準備で作成した表にかぶらなければ（セルJ1）である必要はないです。

書き方はこういう物なのでコピペでもして下さい。左辺に右辺を代入しますので逆にしないように。

次に

Set ABC = Range("A2:A51").Find(What:=Range("J1").Value, LookAt:=xlWhole)

で、検索する範囲を指定し、さらにFindを使い、先程一時保存した患者IDがあるか検索しています。

これもこう書けばいいそうなので、コピペでいいと思いますが、検索する範囲と先程とりあえず保存した（セル）の値は自分で作成したものに合わせて下さい。

上の式だと、セルA2からA51の範囲でセルJ1と一致するセルの情報を取得してメモ帳ABCに保存しとけとなります。

そして、検索結果として見つかる場合と見つからない場合が出てくるはずですので、またIf文になるわけです。

If Not ABC Is Nothing Then

NotにNothingが重なってわかりにくいですが、これで「検索して一致するものがあった」場合となるそうです。

では見つかった場合は

Offset(, 1)で、1つ右のセルを指定できるそうなのですが、1つ右はバーコードの列なので飛ばして

TextBox2 = ABC.Offset(, 2).ValueでTextBox2には2つ右の値を

TextBox3 = ABC.Offset(, 3).ValueでTextBox3には3つ右の

TextBox4 = ABC.Offset(, 4).ValueでTextBox4 には4つ右の値をそれぞれ代入しなさいという命令になります。

ここまでできると一応動きますので

この再生ボタンみたいなの押して試してみましょう。

バーコード（TextBox1）部分にとりあえず手入力で患者IDを入力してみて、氏名、薬剤名、GS1は表示されましたか？

目的のデータが表示されたら、読み取った値は必要無いですから削除するコマンドを追加しておきます。

TextBox1 = ""で入力したバーコード部分の値を消して

Range("J1").Value = ""でシートに一時保存したはずのバーコードの値を消してます。

ここまでで、患者検索の仕組みはできあがりました。今度は薬剤の照合の仕組みを作っていきます。

読み取った値が登録していたGS1と違っていれば音を出す仕組みです。

今回は一番簡単な

「Beep」

で済ませます。

If文の中にIf文が入り込む形になってますので、どこからどこまでがどの処理なのかよく考えましょう。

If TextBox4 = TextBox1 Thenで「同じなら」です。

同じなら何もせずに患者IDの時と同様、

TextBox1 = ""

Range("J1").Value = ""

で読み取った値を削除する処理をしています。

違った場合は

Beep

で音を鳴らした後、これも同様に

TextBox1 = ""

Range("J1").Value = ""

で読み取った値を消します。

これで一応の処理はできあがったのですが、なんか動いているのか分かりにくいところもあるので、ついでに色も変わるようにすると、

色は

UserForm1.BackColor = RGB(255, 0, 0)

みたいにRGBで指定するそうです。

最後に今作ったフォームを起動するボタンを作っておきます。

コマンドボタンなのですが、これもExcelのバージョンによって表示のさせ方が違うので検索してみましょう。

私の使っているバージョンはかなり古いので、ツールバーからフォームを表示させ、ボタンを作成し

コードの編集をクリックして

UserForm1.Show

通常はこれで、できあがりです。WindowsのBeep音を聞き取りやすいものに変えてあげましょう。

外箱の2次元コードを読み取るために、バーコードリーダーも対応したものを購入し、さらに2次元コードを読み取るように設定した場合は、そのときだけ2次元コードは読まないように設定を変えてしまうか、Left関数あたりを使えばいいのではないかと思います。

上の方で、とりあえずそのまま作りますと言っていた部分にふれておきます。

これは、どのタイミングでプログラムを動かしますか？ということなのですが、初めは何も考えずにChangeで作って動かしてました。

正常に動くことを確認して、一度終了し、次に起動して試そうとしたところ、バーコード部分が入力を受け付けず、Beep音が鳴り、画面が「赤」に変わりました。

なんで？と思って調べていたら、入力が日本語入力から直接入力に変わっていました。

どういうことかというと、例えば患者IDが「1234」だとすると、日本語入力だと「1234」と入力してEnter押した時点でプログラムが動いていたのが、直接入力だと「1」が入力された瞬間、プログラムが動いてしまっていたからなんです。

Accessでバーコードを読み取る処理を作る場合はAfterUpdate使っていたので、Excelでもできないのかな？と思っていたらちゃんと選択できました。ですが、これにすると、フォーカスが移動するため、フォーカスを戻す処理を追加しなければ、バーコードを読む度にマウスやキーボードでTextBox1にカーソルを移動させなくてはいけなくなります。フォーカスを戻すのではなく、フォーカスを固定するって言った方がいいのかな？SetFocusで戻せばいいかと思ってたら戻らんし、なんでかなと思っていたらやはり書いてくれてる人がいました。

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Exitイベント、移動するときに

Cancel = True 

この処理を追加することで、TextBox1 からフォーカスが移動できなくしてしまうわけです。移動をキャンセルって言えばいいのかな。プログラムが動くタイミングを考えると「なるほど」です。更新後処理にSetFocus入れ込んでもダメなのは当前だなと。

Changeを使う場合はTextBox1にフォーカスが移動すると、強制的にIMEを日本語入力にしてしまうか、AfterUpdateにして、入力は直接入力にし、フォーカスを固定する仕組みを組み込むのか、お好みに合わせて使い分けましょう。

一行目の最後をChangeかAfterUpdate、好きな方を選び、合わせてIMEの設定をしておきましょう。

手を加えれば、注射のラベル印刷と監査にも使えるかも？しれません。

つづく