Excel 2016 の VBA で QRコード生成 と スタックトレースの記録 をする話

Tweet

概要

ここには次の２つの話が書かれています。

• ExcelにVBAを用いて，手軽に，自由に，QRコード生成をできるようにした話

• github上のQRコード生成ライブラリを勉強するために，Excel VBAで関数の呼び出し関係を記録する，スタックトレースログを取れるようにした話

Microsoft Excel 2016 の VBA で 手軽に扱うことができる QRコード 生成をやった話

最近，個人的にExcel帳票上にQRコードを載せたいなあという機会がありました。

そして，帳票レイアウト的に，連結QRコードを用いて，既存帳票の空きスペースにQRコードを入れたほうが，かっこいい感じになりそうでした。（たまたま，読み取りに使えるバーコードリーダーが連結QRコード対応だったというのもあります）

まずは github から QRコード生成ライブラリを見つけてきました。

• yas78 / QRCodeLibVBA
• https://github.com/yas78/QRCodeLibVBA

このライブラリはほとんどそのまま使えそうでしたが，個人的には，画像としての出力のかわりに，Excelのセルに色を塗ってQRコードに見立てたい気持ちがありました。

そのため，このライブラリをフォークし，画像出力機能を削って，セルを使ってQRコードを表示する機能を付加したライブラリをつくりました。

• ahox / QRCode_ExcelVBA
• https://github.com/ahox/QRCode_ExcelVBA

これでできることは（オリジナルのライブラリでできることばかりですが）次の通りです。

• QRコード が作れます。
• Microsoft Excel 2016で使用できます。
• 数字・英数字・8ビットバイト・漢字モードに対応しています。（日本語が使えます）
• 文字コードが指定できます。
• バーコードコントロールのように255文字の最大文字数の制約がありません。
• 連結QRコード（分割QRコード）が作れます。
• ワークシート関数として使用します。
• 一般的なワークシート関数および条件付き書式と組み合わせてワークシート上へQRコードを表示できます。

具体的な使い方は github の README の通りですが，使用例が以下にありますので，これをそのままダウンロードして使うこともできます。

• https://github.com/ahox/QRCode_ExcelVBA/blob/master/bin/Release.xlsm
• ダウンロードリンク

なお，画像出力機能を削ったことで外部参照が減りましたが，ADODB.Stream だけは必要になります。

（日本語を文字コードを指定してバイト列に変換するために使用します。実行時バインディングで CreateObject("ADODB.Stream") としているので参照設定は不要です。）

それ以外はすべて純粋にExcel VBAで作られています。

ソースコードはモジュールが46個もあるので少し構えてしまいますが，以下に関数呼び出しのフローチャートと，各モジュール，各関数の説明書を作成しました。また，実際にコードを実行した際のStack Trace Log（呼出された関数名，引数，戻り値をすべて記録したもの）もいくつか載せてあるので，動作理解の一助となればと思います。（JIS X 0510と合わせて見てください）

• https://github.com/ahox/QRCode_ExcelVBA/blob/master/bin/Debug.xlsm
• ダウンロードリンク

簡単に動作概要を説明します。

このコードは，次のようにワークシート関数として呼び出すことで，黒色＝1，白色＝0，行区切り＝半角空白の文字列に変換された QRコード を返しますので，通常のExcel関数で各セルの0/1を抽出し，条件付き書式で白黒の色を付けて，二次元バーコードにするものです。

=QR(データ，エラー訂正のレベル，最大バージョン数，構造的連接の可否，文字コード，バージョン固定の要否)

最初に呼び出されるワークシート関数はWorksheetFunctionQR.basのPublic Function QRです。

この関数は38行目の

Set sbls = CreateSymbols(p_ecLevel, maxVer, allowStructuredAppend, charsetName, fixedSize)

で，Symbols.clsのインスタンスをFactory.bas経由で生成し

Call sbls.AppendText(data)

で，データを付け加えています。AppendTextでは，Symbolsの現在の符号化モードに基づき，追加する文字に応じて符号化モードの切り替えが必要かどうか確認を行います。（Symbols.clsの123行目から152行目あたり。）そして，必要に応じて，モードの設定（m_currSymbol.TrySetEncodingMode），最大バージョンにデータが入りきらずモードの設定が失敗する場合はシンボルの追加（Call Add）を行って，文字の追加（154行目：m_currSymbol.TryAppend(c) ）をします。ここでは，QRコードの符号化ルールに基づいてそれぞれのモードに対応するエンコーダーで符号化するまでしかしておらず，実際のQRコードのデータブロックやエラー訂正ブロックは作っていません。それを行うのは，WorksheetFunctionQR.basの41行目から44行目の

    Dim sbl As Variant
    For Each sbl In sbls
        QR = QR & sbl.GetString() & " "
    Next

Symbol.clsのGetStringの中で呼び出されている，Symbol.clsのGetModuleMatrixになります。

ここで，FinderPattern.Place，Separator.Place，TimingPattern.Place，Symbol.PlaceSymbolChar，RemainderBit.Place，Masking.Apply，QuietZone.Placeの順にQRコードが組み立てられていきます。

なお，Symbol.PlaceSymbolCharの中で呼び出されるSymbol.GetEncodingRegionBytesにて，BuildDataBlockとBuildErrorCorrectionBlockによりデータブロックとエラー訂正ブロックが作られます。（そして，BuildDataBlockの中では，BitSequence.clsを使って WriteStructuredAppendHeader，WriteSegments，WriteTerminator，WritePaddingBits，WritePadCodewords によりデータブロックが作られていきます。）

また，Masking.Applyの中では（当然，すべてのパターンでマスクしてペナルティスコアの小さいものでマスキングを行う他に） VersionInfo.Place も行われています。

Microsoft Excel 2016 の VBA で VBE をコードから操作して スタックトレースの記録をやった話

さて，私はこのQRコード生成ライブラリから画像出力機能を削るために，当該コードの動作理解をするにあたって，モジュールが（オリジナルでは）64個もあったことから，一旦プロシージャの呼出履歴を全部記録して書き出して見てみたいなぁと思いました。

そこで，VBAでスタックトレースする手軽な方法がないか調べてみましたが，結局はコードに変更を加えないと難しいという感じでした。

しかたがないので，VBE (Visual Basic Editor) を操るコードを書いて，既存のすべてのプロシージャに対して，スタックトレースに必要な関数呼び出しのコードを必要に応じて追加したり，削除したりできるものを作ってみました。

• ahox/StackTraceLib_VBA
• https://github.com/ahox/StackTraceLib_VBA

これでできることは次の通りです。

• すべてのモジュールのすべてのプロシージャに対して，プロシージャの呼び出し関係を記録するコードを追加および削除することができます。
  • 特定のモジュールを対象から除外することができます。（#Const NO_TRACE = 1 の宣言が必要）
• 記録したプロシージャの呼び出し関係は，ワークシートへ出力し，呼び出しの階層がツリー状になるように表示されます。（各階層でツリーを閉じたり開いたりすることができます）
• 呼び出し関係をコード実行途中で確認できるようにするために，イミディエイトウィンドウへの出力を合わせて行うようにすることもできます。（#Const DEBUG_PRINT_MODE = 1 の定義が必要）
• 実行時のオーバーヘッド増加による応答なしを防ぐために，極端に呼び出し回数が多いプロシージャがある場合には，実行を止めてユーザーに知らせます。

このライブラリはモジュールが３つしかない単純なもので次のような構成です。

StackTraceLog.cls

これはスタックトレースの記録を取るためのクラスモジュールです。

単純に呼出の階層レベル，モジュール名，プロシージャ名，引数リスト，戻り値をパブリックなクラス変数として持つだけのクラスです。

StackTrace.bas

これがメインのモジュールです。いくつかの関数・サブルーチンがあります。また，スタックトレースを記録するためのモジュール変数があります。

モジュール変数

• Private StackLevel As Long
  • 現在の呼出の階層レベルを保持する変数です。
• Private DebugTrace As Collection
  • StackTraceLogオブジェクトのコレクションを使ってスタックトレースを記録します。
• Private Counter As Object
  • 呼出元のモジュール名.プロシージャ名をキーとしたDictionaryへPushStackTraceプロシージャの呼出回数を記録します。

関数・サブルーチン

• Sub WriteStackTrace
  • 記録したスタックトレースをワークシートへ出力するサブルーチンです。
• Sub PushStackTrace
  • デバッグ対象のコードにて，プロシージャの開始時に呼出して，呼出の階層レベルを１増やすとともに，モジュール名，プロシージャ名，引数リストを記録するためのサブルーチンです。
  • 同一呼出元のプロシージャから１万回呼び出された場合は，一旦コードの実行を停止し，あまりに呼び出し回数が多いのでプログラムが応答なしとなる恐れがある旨をユーザーに知らせます。
• Sub PopStackTrace
  • デバッグ対象のコードにて，プロシージャの終了時に呼出して，呼出の階層レベルを１減らすとともに，プロシージャが戻り値を持つ場合に戻り値を記録するためのサブルーチンです。
• Private Function ArgsToString
  • 引数名と引数の値をParamArrayで受け取ると，引数リストを文字列で返す関数です。
• Private Function PrintArrayBounds
  • 引数または戻り値が配列の場合に，配列の要素数を記録できるようにするために，引数として配列を受け取ると，その要素数を文字列で返す関数です。
• Sub EnableDEBUGMODE
  • すべてのモジュールへ #Const DEBUG_MODE = 1 を定義し，Push/PopStackTraceの呼出を有効化するサブルーチンです。
• Sub DisableDEBUGMODE
  • すべてのモジュールから #Const DEBUG_MODE = 1 を削除し，Push/PopStackTraceの呼出を無効化するサブルーチンです。
• Sub AddStackTrace
  • すべてのモジュールのすべてのサブルーチンの開始行へPushStackTraceの呼出コードを追加し，終了行へPopStackTraceの呼出コードを追加するサブルーチンです。
  • モジュールの1～9行目へ #Const NO_TRACE = 1 が宣言されている場合は，そのモジュールには呼出コードの追加を行いません。
• Sub RemoveStackTrace
  • AddStackTraceで追加したコードを削除するサブルーチンです。
• Private Function GetProcedureArguments
  • AddStackTraceでPushStackTraceの呼出コードを追加する際に，当該サブルーチンの持つ引数リストを取得するための関数です。

Sheet_DebugTrace.cls

スタックトレースの記録を出力する先のワークシートにおいて，マウスクリックにて以下の階層を閉じたり開いたりするためのモジュールです。（ Worksheet_FollowHyperlink で実現しています。）

適当に作ったので汎用的に利用可能にするためには色々と問題があるかもしれませんが，とりあえずQRCodeLibで使えるようにするために，次の点は考慮されています。

• QRCodeLibでは，Values.IsDark関数が何万回も呼び出され，スタックトレースの記録関数が当該関数から呼び出しされる状態では，とても動作が重たくなる＆記録が冗長となって見にくかったので，同一関数から１万回以上呼び出されたら一旦実行を停止して，使用者に当該コードのトレース要否の判断を仰ぐようにしました。
• ワークシート関数から呼び出しすると，ワークシートに直接結果を書き出す処理は実行できないので，一旦，コレクションに記録してから，WriteStackTraceマクロを実行すると書き出されるようにしました。
• 一旦コレクションに記録してから…とすると，完走しないマクロのデバッグに使えないので，#Const DEBUG_PRINT_MODE = 1 の宣言により，イミディエイトウィンドウに直接出力することもできるようにしました。
• はじめは，Sub/Functionのすぐ下の行と，End Sub/End Functionのすぐ上の行に呼出コードを挿入していましたが，途中でExit Subがあると（呼出の階層レベルが）バグるので，Exit Subのところにも入るようにしました。（１行形式If文の中にExit Subがある場合もあって少しやっかいでした）

こうして，実用的なQRコード生成のライブラリの中身を追いかけたことで，QRコードに対する良い勉強になりました。QRコード生成ライブラリをMITライセンスで上げてくださったyas78さんに感謝いたします。