知らずに落ちると抜け出せなくなるVBAの落とし穴です。
- Dir関数が、フォルダ内の全ファイルを処理していないのに途中で止まる。
- 逆にDir関数がいつまでたっても""にならず、無限ループする
- Dir関数が「プロシージャの呼び出し、または引数が不正です。」エラーを出す。
あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。
一刻も早く問題を解決したい人へ3行で説明
まず基本として、
' Dir関数でフォルダ内の全ファイルをループする 処理するブック名 = Dir(第1の条件) Do While 処理するブック名 <> "" ' ループ内で別の条件の第2Dirを使う 第2のブック名 = Dir(第2の条件) 処理するブック名 = Dir() ' ← こいつが第2Dirを見てしまいループが崩れる Loop
これはDirの仕様上やってはいけないことですが、
' 同じようにDir関数によるループ中に 処理するブック名 = Dir(第1の条件) Do While 処理するブック名 <> "" ' たとえ使われたDirが別の関数であったとしても Call 別のプロシージャ 処理するブック名 = Dir() ' ← やっぱりこいつは第2Dirを見てしまう Loop Sub 別のプロシージャ() 第2のブック名 = Dir(第2の条件) ' ←犯人 End Sub
こっちもダメだから気をつけないといけません。
というお話です。
解決策は、「どっちかを FileSystemObject で書く」です。
お急ぎでしたら詳しい解説は飛ばして解決策へどうぞ
詳しい解説
Dir関数によるループ処理のサンプル
まずはDir関数で一番メジャーなループ処理である、
「フォルダ内のすべてのブックを処理」をサンプルに書きます。
Sub 指定フォルダ内の売上データを集計する(Path指定フォルダ As String) ' くだもの売上データ(○○).xlsx に該当するブックをDirで取得 Dim 対象ブック名 As String 対象ブック名 = Dir(Path指定フォルダ * "\くだもの売上データ(*).xlsx") ' Dirが""を返すまでループ Do While 対象ブック名 <> "" ' ブックを開く Dim wb対象ブック As Workbook Set wb対象ブック = Workbooks.Open(Path指定フォルダ & "\" & 対象ブック名) ' ■ 開いたブックを引数に渡して集計プロシージャへ Call くだもの売上データを集計シートに追加する(wb対象ブック) ' ブックを閉じる wb対象ブック.Close False 対象ブック名 = Dir() ' 次のブックへ Loop End Sub
こんな風に■の部分を別のプロシージャにすると、
- ループに関するコード
- ループ対象1つ1つの処理に関するコード
が分かれて読みやすくなりますね。
発生するバグ
「売上データを集計シートに追加する」プロシージャの中身に、
こんなコードがあったとします。
' 同じ日付のやさい売上データがあれば、同時に処理する If Dir(Path指定フォルダ & "\やさい売上データ(" & 売上日 & ").xlsx") <> "" Then ～もう一つのファイルを開いてデータをくっつける処理～ End If
一緒に処理をすべきブックがないかを探していますね。
Dir関数を使って。
もう何が言いたいかはお分かりかと思いますが、
ここでDirを使ったせいで、元のループプロシージャの
対象ブック名 = Dir() ' 次のブックへ Loop
この部分が、集計プロシージャの中のDirの続きを探すようになってしまいます。
*のないDirに2個目のファイルはありませんので、ループは止まってしまいますね。
このエラーは、もっとわかりやすく1つの関数で、
対象ファイル名 = Dir(第1の条件) Do While Dirの結果 <> "" 第2のブック名 = Dir(第2の条件) 対象ファイル名 = Dir() Loop
こんな感じで書かれていると、
明らかにヤバい使い方してると気付けます。
しかし「関数の中で使ってもダメ」っていうのは、
分かっていても見落とす可能性がありますので気をつけましょう。
ちなみに、同じ日のやさい売上データがない場合は
集計プロシージャの時点でDirが""を返しますが、
続けて処理されるループプロシージャのDirでは、
「プロシージャの呼び出し、または引数が不正です。」エラーが出ます。
""を返したDirをもう一度呼ぶと、このエラーが出るのは覚えておきましょう。
このエラーが2回目以降のDirで出たら、
Do While 対象ブック名 <> ""
こういう""かどうかの判定を書き忘れていないか、
そうでなければまず今回のバグが原因です。
解決策
どちらかを「FileSystemObject」を使った処理に書き換えます。
※ 「FileSystemObject」は説明すると長いので、ここでは解決法のみ書きます。
※ 「FileSystemObject」は以下「FSO」と表記します。
中で使ったDir関数がただのファイル存在チェックの場合
この場合は、存在チェックの部分
' 同じ日付のやさい売上データがあれば、同時に処理する If Dir(Path指定フォルダ & "\やさい売上データ(" & 売上日 & ").xlsx") <> "" Then ～もう一つのファイルを開いてデータをくっつける処理～ End If
これをFSOを使って、こう書き換えることができます。
' 同じ日付のやさい売上データがあれば、同時に処理する Dim FSO Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FileExists(Path指定フォルダ & "\やさい売上データ(" & 売上日 & ").xlsx") Then ～もう一つのファイルを開いてデータをくっつける処理～ End If
が、
わざわざ毎回書き替えるのは面倒だし、忘れてDir関数使ってしまったら元も子もありません。
ということで、「FSO」を使った自作関数を作ってしまって、
フォルダ・ブックの存在チェックは、その関数でやるという手もあります。
その関数がこちら
Function Isフォルダが存在する(Path判定フォルダ As String) As Boolean Isフォルダが存在する = CreateObject("Scripting.FileSystemObject").FolderExists(Path判定フォルダ) End Function Function Isブックが存在する(判定ブック名 As String, Path対象フォルダ As String) As Boolean Isブックが存在する = CreateObject("Scripting.FileSystemObject").FileExists(Path対象フォルダ & "\" & 判定ブック名) End Function
コピペすればそのまま使えます。
今回の問題の箇所をこの関数で書き替えると、↓こちらになります。
' 同じ日付のやさい売上データがあれば、同時に処理する If Isブックが存在する(Path指定フォルダ & "\やさい売上データ(" & 売上日 & ").xlsx") Then ～もう一つのファイルを開いてデータをくっつける処理～ End If
関数化したことで、
- 読みやすい。見ただけで内容が分かる。
- 呼ぶだけで使えるので、FSOのしくみやプロパティ名を覚える必要がない。
- この関数を呼んでおけば、本記事のバグは起きない。
ようになりましたね。
たった1行を関数にするのは最初は不思議かもしれませんが、
こういう簡単な処理こそ、実は関数化のメリットが大きいです。
関数の作成は躊躇しないようにしましょう。
中で使ったDirでもループなどの処理を行っている場合
この場合は「対処法はこれ」のような特効薬はないです。
FSOでループ部分を書き換えたり、
処理の構造自体を変えたり、
いろいろ考えてやるしかないですね。
もしまだFSOを知らず、Dir関数しか知らない方がこの問題に遭遇している場合は、
これを機にFSOの勉強をおすすめします。
慣れればFSOの方がはるかに読みやすく、オブジェクトを扱う練習にもなりますし、
なによりDirを入れ子にするレベルのマクロを書く必要が生じている時点で、
今後FSOから逃げ切れるとは思えないような気がします。
頑張ってください。
