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


Dir痛い目_アイキャッチ
皆様こんにちは、ノグチです。

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

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

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

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

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

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

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

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

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

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

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

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

ステップ実行1

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

プロシージャエラー

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

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

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

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

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

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

これです。

ウォッチ式

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

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

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

サンプルファイル

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

Dir@ウォッチ式

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

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

今回の私のケースでは、

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

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

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

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

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

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

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

サンプルファイル

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

dir@イミディエイト

Dir関数が返してきたファイルが、更に次に移っているのがわかりますね。

そして、10行目のstrPath2 = dir()をステップ実行して変数strPath2の値を見てみると…

イミディエイト実行

 

この通り、dir関数が返してくるファイルがどんどん変わって、strPath2には条件に4番目にマッチするファイルパスが格納されています。

このように、Dir関数はVBAコード内、ウォッチ式、イミディエイトウインドウ問わず、Dir関数が実行されるたびに、次々に条件にマッチするファイルまたはフォルダを返してくるのです。

FileSystemObjectを使えばエラーは出ない

ちなみに、今回のエラーが発生する条件は下記の2つ。

  1. VBAコード内でDir関数を使っている
  2. ウォッチ式又はイミディエイトウインドウでDir関数を実行する

なので、FileSystemObjectを使えば今回のエラーを出すことなくコーディングができますよ。

エクセルVBAでテキストファイルを新規作成するFileSystemオブジェクトの使い方
エクセルVBAによるマクロの動作状況をログファイルに残す方法をお伝えしています。今回は、書き出し先のテキストファイルの有無を判定して、もし存在しなければ新規作成するという処理を追加していきます。

最後に

いかがでしたでしょうか。

今回は、私の横着に端を発するDir関数ドはまり体験談をご紹介しました。

そもそも、今回私が作成したコードでは、Dir関数で返されたファイルを変数に格納しているのだから、変数側の値をウォッチ式なりDebug.Printでチェックすべきだったのに、横着をして直接Dir関数をウォッチ式に指定してしまったがために今回の現象になってしまったわけです。

文中でも述べましたが、安易に直接関数をウォッチ式やイミディエイトウインドウで実行するのはいけませんね。

この私の体験が、皆様の糧となれば幸いです…

それでは、最後までお読みいただきありがとうございました!


The following two tabs change content below.

ノグチ

元製造子会社SE、ERPパッケージシステムコンサルタント(販売管理、購買管理、生産管理、設備管理他)。 業務システム保守・導入両方やった経験をお伝えすることで、少しでもシステムや手作業に悩まされる人が減ればいいなぁ、楽にお仕事してもらえたらいいなぁと日々夢見ております。

コメント