ExcelVBAはクライアントの端末上で動くプログラムです。
VBAのエンジニアになって長いですが、サーバーで動くプログラムと比較して明らかに面倒なのが「特定のクライアント端末でのみ発生するエラー」が多いことです。
個人や小さなチーム内でVBAプログラムを使用するだけであればよいのですが、多くのクライアントユーザーに配布する案件になると必ずと言っていいほど個別の相性問題が発生します。
そんな時、どうしたら良いのかまとめてみました。
想定されるエラーは全てハンドリングする
想定内のエラーであれば全てハンドリングして、専用のエラーメッセージを返却するようにします。
わかり易い例でいうと、ファイルを読み込む処理が発生した場合には、必ずファイルがないことを想定して専用のメッセージを挟みます。
メッセージ処理は面倒ではありますが、ユーザーからエラーメッセージの情報をもらえるだけで解決につながることが多いので、必ず仕込みたいですね。
本記事は、「エラーが発生した行数」を出力する方法にフォーカスしたいので、想定されるエラーのハンドリング方法は下記記事にまとめました。
想定外のエラーの場合は、エラーの行数を表示する
開発時には絶対思いつかないような相性の問題が発生することもあります。
自分の経験ですとユーザーの「mscomctl.ocx」がぶっ壊れていたとかいう事例もありました。こういった物は事前にキャッチできるわけがないので、個別に対応するしか有りません。
残念ながらVBAにはエラーが発生した際にスタックトレース的なものを出力する機能がないため、エラーが発生した行数を確認するのが面倒です。
公式な関数ではないため、あまり情報がないのですが、Erl関数を使用することでエラーが発生した行数を表示することが可能です。
テスト用コード
Sub test()
On Error GoTo ErrProc
Dim i As Integer
5: i = 100 / 10 'この行はエラーにならない
6: i = 100 / 0 '0で割るのでエラーが発生する
MsgBox "エラーは発生しませんでした"
Exit Sub
ErrProc:
MsgBox Err.Description & vbLf & "エラー行:" & Erl & "行目"
End Sub
実行結果

ただ、この関数は正確に言うとエラーが発生した行数ではなく、最後に通ったラベルの行数を表示しているだけです。
そのため、「全ステップにラベルを仕込む」という作業をしなければ正確なエラー行数を表示することは出来ません。
自分がよく実施する保守手段としては
- ユーザーからエラー連絡を受ける
- 全ステップにラベルを仕込んだVBAツールを作る
- ユーザーにそのVBAツールを実行してもらい、何行目でエラーになったかを確認する
という流れになります。
上記の2の手順が結構面倒なので、すぐ出来る手順をメモしておきたいと思います。
エラーの行数のラベルを前ステップに仕込む
コードをExcelに貼って、Excel関数によってラベルを仕込もうと思うのですが、面倒な点として
- Excelはタブ区切りなのでVBAコードをExcelに貼るとレイアウトが崩れる
- 末尾が”_”で終わる場合は1行として扱う必要がある
- 元々ラベルが付いている部分にラベルを設定できない
- FunctionやSubの行にラベルはつけられない
という問題があるので、そこをクリアできるように作業します。
実施する手順について以下に記載します。
1.VBAのコードをメモ帳にコピーする

2.タブを半角スペース4つに置換する
3.手順2で置換したソースをExcelに取り込む
普通にExcelに貼るとうまく行かない場合があるので、テキストをインポートします。(=とかダブルクォーテーションがあると文字列として表示されない)
手順2で作成したテキストを一旦保存した上で、「データ」タブのテキストまたはCSVから取り込みを行います。

区切り文字はデフォルトでカンマになっていますが、ソース内にカンマがあると厄介なので区切り文字はタブにして、「読み込み」をクリックします。

4.貼り付けたVBAコードの隣の行に以下の式を入力します。
=IF(AND(LEFT(TRIM(A2),8)<>"Function",LEFT(TRIM(A2),3)<>"Sub",RIGHT(TRIM(A1),1)<>"_",RIGHT(TRIM(A2),1)<>":"),ROW()-1&":"&A2,A2)
入力結果

5.全行にラベルを仕込めたので元のソースにコピペします
6.エラー処理がある箇所全てに以下のメッセージを追加します。
"エラー行:" & Erl & "行目"
これでエラーの行数がエラーメッセージに出るようになるので、ユーザーの実行しているVBAのエラーの解析が圧倒的に楽になるはずです。
本当はこんなことをしなくてもエラー行数くらい出してほしいところですね。
まとめ
- 想定されるエラーは全てハンドリングしよう
- ユーザー個別のエラーなど判別ができない場合はラベルを全行に仕込んでVBAを実行してもらおう
- ラベルを全行に仕込むのはExcel関数を使うと楽
はじめまして。 参考になりました。