【エクセルVBA】ウォッチ式にDir関数を入力したがためにドハマりした話

皆様こんにちは、ノグチです。

VBAでツールを作成するにあたって、ファイルの読み込みや保存の処理は頻繁に必要になりますよね。

その時に活躍してくれるのが、Dir関数。

しかし、どの関数でもそうなのですが、このDir関数も仕組みを知って正しく使用しなければ、痛い目を見ることも…

そしてそれは関数だけでなく、コーディングの強い味方であるウォッチ式やイミディエイトウインドウでの使い方も同様のこと。

今回は、私がこのDir関数とウォッチ式の使い方を誤ったがために痛い目を見たお話です。

Dir関数の基本的な説明については、下記記事をご覧くださいね。

エクセルVBAでフォルダ内のファイル一覧を表示するDir関数の使い方

エクセルVBAでファイルを開く作業を自動化する方法についてお伝えしています。今回はエクセルVBAでフォルダ内のファイル一覧を出力するDir関数の使い方です。ワイルドカード一つで便利に使えます。

Dir関数がデバッグモードのときだけエラーになる！
ウォッチ式でDir関数が「実行」されていた
Debug.print でもDir関数は「実行」される
FileSystemObjectを使えばエラーは出ない
最後に

Dir関数がデバッグモードのときだけエラーになる！

まずはこちらのコードをご覧ください。

Sub Dir_Test()
Dim strPath As String
Dim i As Long

strPath = Dir("C:\Work\file" & "\" & "*file*" & ".txt")
Do While strPath <> ""
  i = i + 1
  strPath = Dir()
Loop

MsgBox i & "件のファイルが見つかりました。"

End Sub

あるフォルダにあるファイルの件数を数えて、メッセージボックスにファイル件数を表示するというコードです。

実際にこのコードを実行いただければわかるのですが、こちらのコード、F5キーで実行すると何のエラーもなく処理が終了するんです。

しかし、デバッグモードでステップ実行してみると…

この通り、「プロシージャの呼び出し、または引数が不正です」というエラーになってしまうのです。

「デバッグモードだけDirの動作が違うのか？」「実はOfficeのバグなのか？」

この現象にあった当初は原因がさっぱりわからず、こんなことを悶々と考え、MSDNのドキュメントを検索したりしていたのですが、ふと思い当たったのです。

「Dir関数は、実行するたびに指定フォルダ内で条件にマッチする次のパスを返してくるはず。その実行ってもしかして、ウォッチ式も含むのでは？」

そして検証してみたところ、ビンゴだったのです。

ウォッチ式でDir関数が「実行」されていた

原因は、先ほどお見せした、VBAのエディター画面の中にありました。

これです。

そう、やはりウォッチ式。

なんと、ウォッチ式のdir（）の値が、「StrPath = Dir(“…..”)」のコードの次の行から、ステップ実行するたびに変わっていっていたのです。

試しに、このようにファイルを5つ用意して、ウォッチ式とコードのdir関数の動きを確認してみます。

そして先のコードで、ウォッチ式に指定されたdir関数で取得した値と、その一つ前のステップでdir関数で値を取得して、変数に格納した値を比べてみると…

この通り、変数に1つめのファイルを格納して、次のステップに移った途端、ウォッチ式のDir()の返り値が次のファイルになっています。

つまり、Dir関数はVBAコードやウォッチ式問わず、実行される度に対象になるファイルが変わっていく、ということだったのです。

今回の私のケースでは、

ウォッチ式のDir関数によって対象となるファイルが次々に変わる
本来パスを取得してほしかった、VBAコードのloop分の中にあるstrPath = Dir()に処理が移る頃には、条件にマッチする最後のファイルまで到達
「もうこれ以上条件にマッチするファイルはありませんよ。」ということでエラー

という状態になっていたのでした。

Debug.print でもDir関数は「実行」される

ウォッチ式で実行されるなら、イミディエイトウインドウも同じなのでは…

ということで、イミディエイトウインドウでもDir関数は都度実行されるのかも検証してみました。

サンプルコードがこちら。

sub dirTest()

dim strPath1 as string
dim strPath2 as string

strPath1 = dir("C:\Work\file" & "\" & "*file*" & ".txt")

debug.print dir()

strPath2 = dir()

end sub

strPath1に格納したパスの同じ階層には、指定したパスのファイルも含めてこの通りテキストファイルが5つあります。

まずは10行目のstrPath2 = dir()でstrPath2に値を格納する前に、イミディエイトウインドウでdebug.print dir()を実行します。