はじめにお断りしておきますと、VBAだけでは不可能です。たぶん。
そんな無謀なことをを考えてしまったのなら（私もですが）、まずはExcel至上主義からの改宗をオススメいたします。今ならPython万能教はいかがでしょうか？楽しい未来が待っています。
VBAには荷が重すぎるカメラ制御
VBAではバーコードの扱いは何とかなるにしても、カメラは絶望的です。VBAの主旨から完全に逸脱する領域なので、標準ライブラリでサポートされているはずもなく。
世界は広いのでVBAから使えるカメラ制御DLLがあるのかもしれませんが、あったとしても私の技量では扱える気がしないので早々にあきらめ、Pythonでやります（Python万能教へようこそ）。
Pythonでのカメラ制御はおそるべき簡単さです。マジでビビります。
作戦としては、Pythonでカメラ制御とバーコード（QRコード）のデコードをやらせて、結果をファイルへ出力、VBAからそのファイルを読み取りExcelシートへ反映します。
PythonからExcelを直で操作する術もありますが、今回はExcelは管理表として主に見る目的で使う関係でPythonには触られたくないのでこうします。
Pythonには超絶余裕のカメラ制御
Pythonでのバーコード読み取り方法をインターネッツで探すとたくさんやり方が出てきますが、私が採用したのは次の方法です。
カメラ制御にopencv-pythonを使います。インストールします。
pip install opencv-pythonバーコードのデコードにpyzbarを使います。インストールします。
pip install pyzbarはい。
これでもうPythonがカメラを制御してバーコード（QRコード）を写すとデータを読み取れるようになっています！簡単すぎでしょ？どーなってんの？Python万能教へようこそ！
なんかエラーが出ちゃったかたはGoogle先生を頼ってください。私も複数環境に導入してみましたが一部の環境では最初うまくいかなくて先生に聞いて解決しました。だいたいはpyzbarに関連するエラーで、公式ページに記載があるこの現象に該当します。
WindowsでpyzbarをインポートするときにImportErrorが表示される場合は、Visual Studio2013用のVisualC再頒布可能パッケージが必要になる可能性があります。64ビットPythonを使用する場合はvcredist_x64.exeを、32ビットPythonを使用する場合はvcredist_x86.exeをインストールします。
https://pypi.org/project/pyzbar/
https://pypi.org/project/pyzbar/の下の方にダウンロードページへのリンクがあります。
実際にテストするには、次のサイトにコピペですぐ使えるイカしたサンプルコードがあるので試してみてください。ここまででエラーがなければ
5.OpenCVを使った読み取り（カッコいい）
のコードをコピペしてきて実行しましょう。
カメラが起動するので、その辺にころがっているバーコードを写すと
ちゃんと認識しています。カッコよく枠も出ます。Qキーを入力すれば終了できます。
蔵書管理システムを作る
このサンプルコードを元にして、今回は物品管理のプロトタイプとして私の本棚の本のバーコードを読むとExcelの蔵書テーブルの該当書籍にチェックマークがついていくシステムを構築します。
Excelの蔵書テーブルはこのようにしました。
カメラ制御、バーコード読み取り、ファイル書き出しを担うPythonスクリプトは次のようにしました。
import cv2
import winsound
from pyzbar.pyzbar import decode
cap = cv2.VideoCapture(1)
font = cv2.FONT_HERSHEY_SIMPLEX
BUF_FILE_PATH = r'C:\Users\Ore\Desktop\buf.txt'
barcodes = []
while cap.isOpened():
ret, frame = cap.read()
if ret:
d = decode(frame)
if d:
for barcode in d:
barcodeData = barcode.data.decode('utf-8')
if barcodeData not in barcodes:
barcodes.append(barcodeData)
winsound.Beep(2000, 50)
font_color = (0, 0, 255)
with open(BUF_FILE_PATH, mode='a') as buf:
buf.write(barcodeData + '\n')
else:
font_color = (0, 154, 87)
x, y, w, h = barcode.rect
cv2.rectangle(frame, (x, y), (x + w, y + h), font_color, 2)
frame = cv2.putText(frame, barcodeData, (x, y - 10),
font, .5, font_color, 2, cv2.LINE_AA)
cv2.imshow('BARCODE READER', frame)
if cv2.waitKey(1) & 0xFF == ord('q'):
break
cap.release()
Python-VBA版解説
先のサンプルコードが主ですが、要所を解説していきます。
カメラは0～番号が振られています。以下のコードでカメラ番号を指定して、そのカメラの制御を取得しています。
cap = cv2.VideoCapture(1)だいたいノートだと0がインカメラで1がメインカメラになっていることが多いみたいです。私の環境では1がメインカメラなので1にしています。
それと、あらかじめbarcodesという空のリストを作っています。
barcodes = []これは読み取ったバーコードデータをキャッシュしておくためで、リアルタイム読み取り値とこのリストを比較し、リストになければappendとファイル書き出し、あれば書き出さないという処理にしています。
以降はopencv-pythonの一般的なカメラ映像処理ロジックなので、リファレンスを読んでいただくとして、追加している処理は、前述のバーコードデータのキャッシュとの比較処理で
if barcodeData not in barcodes:
barcodes.append(barcodeData)
winsound.Beep(2000, 50)
font_color = (0, 0, 255)
with open(BUF_FILE_PATH, mode='a') as buf:
buf.write(barcodeData + '\n')
else:
font_color = (0, 154, 87)
読み取り値（barcodeData）をキャッシュリスト（barcodes）と比較して
リストになければ、リストに追加して読み取り時の処理でバーコードに付ける枠の色を赤に設定して、ビープ音をならし、テキストファイルとして読み取り値を追加書き込みで出力します。
リストにあれば、枠の色を緑に設定するだけであとは何もしません。
これにより、はじめて読み取ったバーコードは赤枠になりbuf.txtとしてファイルに書き込まれ、すでに読み取っているバーコードは緑枠で表示されます。
次にExcel側です。
と、その前にbuf.txtをPythonスクリプトで指定したパスへあらかじめ作成しておきます。1行目はタイトルとして扱うので入力しておきます。
ここへ先のPythonスクリプトがバーコードデータを追記していくので、Excelで データ → データの取得と変換 → テキストまたはCSVから でこのテキストファイルをシートに取り込みます。
bufという新規シートに、このようにテーブルとして取り込まれます。
ためしにテキストファイルにデータを追加、保存して
Excelで データ → クエリと接続 → すべて更新 を実行すると
このように追加されていきます。
この更新作業をVBAで定期実行させればテキストファイルを自動でシートへ同期できますね。
ということで、テーブルを更新する処理を一定間隔で実行させるプロシージャを作ります。VBAを一定間隔で実行させる方法はインターネッツに例がたくさんあるので、それをそのまま使います。
Public tm As Date
Sub startTableRefresh()
Worksheets("buf").Range("a1").ListObject.QueryTable.Refresh
tm = Now + TimeValue("00:00:05")
Application.OnTime tm, "startTableRefresh"
End Sub
Sub stopTableRefresh()
On Error Resume Next
Application.OnTime tm, "startTableRefresh", Schedule:=False
End Sub
読んで字のごとくですが、startTableRefreshで自動更新が開始され、stopTableRefreshで自動更新を終了します。更新のインターバルは5秒にしています。
このままではbufシートにただデータが列記された状態なので、これを蔵書テーブルへ反映します。
蔵書テーブルのISBN列の値がbufシートのテーブルに存在するならOKに、存在しないならNGになるように関数をstock列に設定します。次のようにしました。
テーブルになっていると関数内で列名を指定するだけでよく、しかも最初の行だけ関数を入力すれば、残りの行は勝手に空気読み反映されて大変便利です。
条件付き書式でセルの色も変わるようにしておきます。
自動更新開始と同時にPythonスクリプトを実行してカメラを起動したいので、VBAからPythonスクリプトを実行するプロシージャを作ります。
Sub executePy()
Dim wss As Object
Set wss = CreateObject("WScript.Shell")
Dim cmd As String
cmd = "python C:\Users\Ore\Desktop\bc_cam.py"
wss.Run cmd, 0, False
End Sub
WScript.ShellのRunメソッドでPythonスクリプトファイルを実行しています。第三引数にFalseを指定するとコマンドはVBAとは非同期で実行されます。今回の用途ではTrueで同期実行だとカメラが起動したところで止まってしまい何もできなくなってしまうのでFalse一択です。
VBAを定期実行したままでExcelを終了すると良くないみたいなので、Workbook_BeforeCloseイベントで自動更新停止をさせます。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call stopTableRefresh
End Sub
メインプロシージャで自動更新開始とPythonスクリプト実行をコールするようにします。
Sub main()
Call executePy
Call startTableRefresh
End SubメインプロシージャをSTARTボタン、自動実行停止プロシージャをSTOPボタンに配置して完成です。
全体像は次のようになります。
Option Explicit
Public tm As Date
Sub startTableRefresh()
Worksheets("buf").Range("a1").ListObject.QueryTable.Refresh
tm = Now + TimeValue("00:00:05")
Application.OnTime tm, "startTableRefresh"
End Sub
Sub stopTableRefresh()
On Error Resume Next
Application.OnTime tm, "startTableRefresh", Schedule:=False
End Sub
Sub executePy()
Dim wss As Object
Set wss = CreateObject("WScript.Shell")
Dim cmd As String
cmd = "python C:\Users\Ore\Desktop\bc_cam.py"
wss.Run cmd, 0, False
End Sub
Sub main()
Call executePy
Call startTableRefresh
End SubVBA版デモ動画
実際にうごかしてみるとこんな感じです（動画・音がでます）。
5秒インターバルで更新のはずなのにシートへの反映が速すぎる気もして、Pythonと同時にファイルをつかんでいる疑惑は拭いきれませんが結果オーライです。
それ、VBAでやる必要なくね？
はい。ないです。ここまでの環境がそろっているならPythonだけでやった方がいいです。
悩みどころはVBA版ではExcelが全部やってくれていたUIをどうやるかですが、PythonにもGUIアプリを作成するモジュールがあり、一番有名どころを使ってみたのですが、Javaのそれを彷彿とさせる手続きだらけのコードでPythonらしさのかけらもなく「あ～、この分野はあまり力を入れられてないんだな」感がひしひしと伝わってきました。
ということで、今回はHTMLを使ってブラウザで表示させます。
システム概要は次のとおりです。
WEBサーバーは標準モジュールのhttp.serverを、データベースはSQLiteを使用します。
データベースはこのような構造です。
Pythonスクリプトはインターネッツから拾ったコードをちりばめてお送りします。
import cv2
import winsound
from pyzbar.pyzbar import decode
import sqlite3
import json
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
import threading
def cam_capture():
cap = cv2.VideoCapture(1)
font = cv2.FONT_HERSHEY_SIMPLEX
barcodes = []
global conn
while cap.isOpened():
ret, frame = cap.read()
if ret:
d = decode(frame)
if d:
for barcode in d:
barcodeData = barcode.data.decode('utf-8')
if barcodeData not in barcodes:
barcodes.append(barcodeData)
winsound.Beep(2000, 50)
font_color = (0, 0, 255)
cur = conn.cursor()
cur.execute(
'UPDATE bookshelf SET stock=True WHERE isbn = ?', (barcodeData,))
conn.commit()
cur.close()
else:
font_color = (0, 154, 87)
x, y, w, h = barcode.rect
cv2.rectangle(frame, (x, y), (x + w, y + h), font_color, 2)
frame = cv2.putText(frame, barcodeData, (x, y - 10),
font, .5, font_color, 2, cv2.LINE_AA)
cv2.imshow('BARCODE READER press q -> exit', frame)
if cv2.waitKey(1) & 0xFF == ord('q'):
break
cap.release()
class HttpHandler(BaseHTTPRequestHandler):
def do_GET(self):
with open('template.html', mode='r', encoding='utf-8') as html:
response_body = html.read()
self.send_response(200)
self.send_header('Content-type', 'text/html; charset=utf-8')
self.end_headers()
self.wfile.write(response_body.encode('utf-8'))
def do_POST(self):
global conn
cur = conn.cursor()
cur.execute('SELECT * FROM bookshelf')
rows = []
for r in cur.fetchall():
rows.append({'title': r['title'], 'isbn': r['isbn'], 'stock': r['stock']})
cur.close()
self.send_response(200)
self.send_header('Content-type', 'application/json')
self.end_headers()
response_body = json.dumps(rows)
self.wfile.write(response_body.encode('utf-8'))
def run_server():
global server
server.serve_forever()
server = ThreadingHTTPServer(('localhost', 8080), HttpHandler)
conn = sqlite3.connect(r'D:\bookshelf\book.sqlite', check_same_thread = False)
conn.row_factory = sqlite3.Row
httpd = threading.Thread(target=run_server)
capture = threading.Thread(target=cam_capture)
httpd.start()
capture.start()
capture.join()
conn.close()
server.shutdown()
我ながら美しくないコードですが、労力対効果の観点から妥協しました。
Python版解説
要点を解説しますと、まずWEBサーバーとカメラの2つのループが同時に走る必要があるので、threadingで同時進行させています。
run_server関数で起動したWEBサーバーへのリクエストはHttpHandlerインスタンスが捌きます。これはBaseHTTPRequestHandlerクラスを継承していて、GETリクエストにはdo_GET、POSTリクエストにはdo_POSTというように、それぞれのリクエストに対応したメソッドが実行されるようにできています。なのでこれらのメソッドをオーバーライドして自分の好きな処理をやらせることができます。
GETリクエストにはdo_GETメソッドで次のtemplate.htmlを読み込みレスポンスします。
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
BootstrapCDN読み込み（省略）
<script>
$(document).ready(function () {
setInterval(function (){
getJson()
},1000)
})
function getJson() {
$.ajax({
url: 'http://localhost:8080',
type: 'POST',
dataType: 'json',
}).then(
function (data) {
let elem = '<tr><th>タイトル</th><th>ISBN</th><th>在庫</th></tr>'
$.each(data, function (key, item) {
let bc
let stock
if(item.stock === 1){
bc = 'bg-success'
stock = 'OK'
}else{
bc = 'bg-danger'
stock = 'NG'
}
elem += '<tr>'
elem += '<td>' + item.title + '</td>'
elem += '<td>' + item.isbn + '</td>'
elem += '<td class="' + bc + '">' + stock + '</td>'
elem += '</tr>'
})
$('#bs').html(elem)
})
}
</script>
<title>本棚</title>
</head>
<body>
<div class="container">
<table class="table table-striped table-dark" id="bs">
</table>
</div>
</body>
</html>
スクリプトを実行してhttp://localhost:8080へブラウザでアクセスすると次のようなページが表示されます。
POSTリクエストにはdo_POSTメソッドでSQLiteデータベースへ問い合わせた結果をJSONに変換してレスポンスします。
template.htmlは仕込まれたJavaScriptによりAjaxで1秒インターバルでWEBサーバーへPOSTを送信し続けています。これによりデータベースのテーブルをSELECTした結果が1秒おきにHTMLのテーブルとして表示されます。BootstrapのCDNから読み込まれるjQueryはAjaxが使えないSlim版なので、そこだけ通常版のCDNに書き換える必要があります。
cam_capture関数はVBA版と同じうごきで、バーコードデータの出力先がテキストではなくデータベースになっただけです。新規データならデータベースをUPDATEしています。
なおsqlite3.connectの引数にcheck_same_thread = Falseを渡しておかないと、このような複数スレッドからcursorを取得しての操作ができないハマりポイントがあるのでご注意ください。
私がやってみた限りでは双方からの同時アクセスによる競合は発生しませんでしたが、SQLiteなので起こりえるのではないでしょうか。本気でやるならPostgreSQLとかにした方がいいでしょう。
WEBサーバーはserve_foreverメソッドで立ち上げたあと、shutdownメソッドを呼ばないと終了できないのですが、カメラと一緒に終わらせないといけないのであれこれ考えた結果、capture.start()でカメラのループを走らせたあとcapture.join()でcam_capture関数が終了した（＝Qキーでループを終わらせた）ことを検知できるので、そのあとにserver.shutdown()を呼んでいます。これに関係してserverはグローバルなスコープに置いています。もっとスマートにできそうですけどね･･･。
わざわざshutdownメソッドを呼ばなくてもスクリプトを終了させればサーバーもシャットダウンされる説がありますが、それだとexit code -1が返ってきて気持ち悪いのでこうしています。
Python版デモ動画
実際にうごかしてみるとこんな感じです（動画・音がでます）。
すでにお気づきかと思いますが、どう見てもバーコードの数より多く認識して（ビープ音がなって）います。キャッシュ配列を覗いてみると、あさっての数値が記録されていました。カメラにもよるかもしれないですが、解析性能はそれほど高くないようです。読み取ったデータが正しいかを検証するステップはあったほうがいいですね。QRコードの方が速く正確に読み取れます。
【宣伝】QRコードをたくさん作りたい場合は、こちらをご利用ください。
まとめ
PythonのUIはHTMLでも問題ないですね。表示するだけの用途であればなおさらです。個人的な感想ですがGUIモジュールを使うよりもHTMLの方がまだ直感的に作れました。
後から思いついたんですが、読み取りの度にいちいちDBへアクセスしなくても、最初にDBからSELECTしたあとは配列などで保持しておいて終了時にDBへ書き戻すようにしても良さそうですね。
それとWeb-Socketあたりで通信するようにできればもっとエレガントになりそうです。気が向いたらやってみたいです。
そして、改良版ができました。
追記
最近（2021.10）今までカメラを使うときに引っ張り出していたノートPCを新調しVAIOの30万円のフラッグシップモデルにしました。今までもVAIOの2in1タイプだったんですけどね。
がっ、なんとカメラがクソ画質過ぎてバーコードが読めなくなりました。残念ながら貧弱インカメラではなかなか厳しいようです。このグレードのノートPCでダメなら大半はダメですね。今まで普通に読めてたのは2in1のメインカメラだからでした。
ということで、ノートPCのインカメラ程度のおもちゃだと今回のネタが使えない可能性があるのでご注意ください。スマホをWEBカメラにする方法もあります。別記事で紹介していますのでご覧ください。
ところで、どうですこのVAIOのキーボード。
日本語配列かななし隠し刻印です。かっこよすぎ。実際にはこれよりもっと沈んだ色で少し離れると無刻印に見えます。これにしたくて別にそんな性能はいらないのに、最上位機種にして+5万くらい余分にかかりました。
ノートPCで日本語配列かななしキーボードが選べる時点で選択肢がほぼVAIO以外なくなるんですよね。かのデザインにステータスを振りまくっているMacでさえキーボードはかなありです。どう考えてもかななしのほうがスタイリッシュでスタバでドヤる層に需要があると思うので、なんならデフォルトでかななしにしたって誰も怒らないと思うのですが。
私はスタバに足を踏み入れる可能性はゼロですが、かななしキーボードのためだけにVAIOにしてます。
おわり。
