Excel VBAでデバッグを行う

ExcelのVBAでプログラムのデバッグを行い開発を効率化する方法について紹介します。 Excel VBAでのブレイクポイントの作成と使い方、Run to cursor、変数の確認およびプログラム式のモニター、仮想的にREPL(対話実行)する裏技的方法についてそれぞれ確認していきます。

Excelで開発モードとVBAを有効化する

まずExcelでVBAエディタを開くために、Developer・開発モードが有効になっていることを確認します。

Developer・開発タブがない場合、開発モードを有効化します。

Excelのメニュー画面からoptions・オプションをクリック、設定を開きます。

設定オプション画面でカスタマイズリボン > デベロッパー・開発 > OK と進みます

この時点で上記画像のようなデベロッパー・開発タブが追加されているはずです。

開発タブのview codeをクリックしvbaエディタを開きます。

VBAでデバッグする

ではVBAスクリプトのデバッグの手順について紹介していきます。

例として、以下のサンプルコードをVBAスクリプトをして使用します。 VBAエディタを開き、画面左上のメニューより VBAプロジェクト・VBA project > ThisWorkbook・このワークブックへ進み、ワークブックのエディタを開きます。(上記画像参照)

Private Sub Workbook_Open()
    ' MsgBox "Workbook_Open"

    Dim data As String
    data = "hogehoge"
    data = "fugafuga"

    Dim dummy As Integer
    dummy = 0
End Sub

上記コードをコピーアンドペーストします。

上記のコードはF5キーで実行・Runできます。このコードは特に何か大きな変更を加えるものではなく、見ての通り変数を適当に定義してそのまま終了するだけのプログラムです。

Workbook_Open()関数はワークブックが新規に開かれた場合に自動的に実行されるプログラムを記述します。例えば上記コードの ' MsgBox "Workbook_Open" をコメントアウトし実行されるようにすると、上記のExcelファイルが次回開かれた際にメッセージボックスが表示されることになります。上記の通り、このプログラムはF5キーでも実行されます。

ブレイクポイント

ブレイクポイントを設定することで、その時点でコードの実行を一時停止し、その間に変数の値の確認やREPL(対話実行)が可能です。ブレイクポイントを任意の行に設定するには、行の左側の空白部分をクリックし赤い丸アイコンがトグルされることを確認します。(上記画像参照)

ブレイクポイントが設定された状態でF5キーを押しスクリプトを実行します。

Run to cursor

ブレイクポイントでプログラムが停止している状態で、実行をコンティニューした際には何もしなければその行から開始されることになりますが、コンティニューする行を指定してブレイクを解除することができます。こういった機能をRun to cursorまたはJump to cursorと呼びます。

Run to cursorではコードを上部に戻して実質的なループをすることも可能ですし、下部の数行を飛ばして実行といったこともできます。

Run to cursorでコンティニューする行を指定するには、ブレイクポイントの赤い丸アイコンと重なっているように見える右向きの黄色い矢印アイコンに注目します。この黄色い矢印アイコンをマウスでドラッグしながら上部下部に動かし、任意の行に設定します。なお、Run to cursorは一部のコード、例えば変数を定義するDim文などの上では設定できません。

変数の確認

ブレイクポイントでプログラムが停止している状態では、任意の変数の値を、変数の上にマウスカーソルを合わせることで変数値を確認できます。

変数のモニターおよびプログラム式・実行文のモニター・監視

画面下部のwatchesより、右クリックからadd watch、または画面上部のメニューのデバッグ・Debug > add watchより、ウォッチ・モニター、監視する変数やエクスプレッション、実行文のモニターが可能です。

これらはブレイクポイントでプログラムが停止している状態で、設定した変数やプログラムが実行された場合の値を出力を一覧します。

例えば上記画像では、data = "hogehoge" でブレイクポイントにて止まっているので、data変数には"hogehoge"が格納されているはずです。ウォッチに加えられているdataの列を見ると、想定通り"hogehoge"と表示されていることがわかります。

REPL(対話実行)

REPLとは対話的実行環境のことであり、プログラムをインタラクティブ・相互的に実行可能な環境を指します。

例えばVBAでブレイクポイントでプログラムが停止している状態で、VBAプログラム CreateObject("WScript.Shell").Environment("process").Item("windir") がどのような出力をするかを知りたいとします。これをJavaScriptのコンソールのように、プログラム一つ、一行ずつで実行することが、実質的に可能であるという話になります。

具体的な方法としては、watchesの任意のウォッチのプログラム・変数をダブルクリックし、編集可能状態にします。ここに任意のプログラムを入力しEnterキーなどで編集を完了すれば、即時にプログラムの実行結果が右側に出力されます。

これは例えば変数の定義をしたり変数に代入をしたり、制御文を含めた複雑な式やブロックなどは完全には動作せず、この点VScodeなどでのREPLデバッグとは異なる部分もありますが、それを加味しても十分に利用価値の高い機能であると言えます。

まとめ

本記事では、ExcelのVBAを使用してプログラムのデバッグを行う方法について詳しく紹介しました。以下は記事の主要なポイントをまとめたものです。

開発モードとVBAを有効化: Excel内でVBAエディタを使用するために、開発モードとVBAエディタを有効にする必要があります。これを行うために、Developer（開発者）タブを有効化する手順を説明しました。
ブレイクポイントの設定: ブレイクポイントを設定することで、プログラムの実行を一時停止できます。特定の行にブレイクポイントを設定し、コードの実行を一時停止して詳細なデバッグを行いました。
Run to Cursor: ブレイクポイントで一時停止した後、Run to Cursorを使用して特定の行に移動し、プログラムの実行を再開できる方法を説明しました。
変数の確認: ブレイクポイントでプログラムが一時停止した際、変数の値を確認する方法を紹介しました。カーソルを変数に合わせることで、その変数の値を表示できます。
変数のモニターとプログラム式のモニター: ウォッチ（監視）機能を使用して、特定の変数やプログラム式の値を監視する方法を説明しました。これにより、プログラムの実行中に変数の変化を追跡できます。
REPL（対話実行）: 対話的な実行環境でVBAプログラムを実行できる方法を紹介しました。この機能を使用して、プログラムの一部を対話的にテストできることを示しました。

これらのデバッグテクニックを駆使することで、Excel VBAプログラムのエラーを特定し修正し、効率的な開発を進めることができるはずです。