ExcelVBA の Range.Find 関数について、完全にハマったので備忘録としてまとめます。
結論からいうと Range.Find は Excel の「Ctrl+F（検索）」ダイアログの設定をそのまま使い回している という仕様が原因でした。
事象：1回目は動くのに、2回目が動かない
ある処理で Range.Find を複数回使っていました。
すると、1回目の一連の処理は正常に動くのに、2回目の実行で突然見つからなくなるという謎の現象が発生。
- コードは正しい
- シートも変えていない
なのに「2回目だけ」検索にヒットしない。
しかも、人によって動く動かないがあって・・・
この時点で意味不明でした。
原因：Range.Find は Ctrl+F の検索条件を引き継ぐ
実は Range.Find は Excel ワークシート上の Ctrl+F の設定を内部で使っているショートカット だったのです。
つまり、
LookAtSearchOrderMatchCaseSearchFormat
などの検索条件が 前回の検索の状態のまま残る。
今回のケースでは、一連の処理の最後だけ LookAt:=xlWhole を指定していました。
他の .Find では LookAt を指定していません。
その結果、
- 最後の
.FindがxlWholeで終わる - Ctrl+F の検索条件が「完全一致」のまま残る
- 次の実行時、
LookAt未指定の.Findは 前回設定のまま（xlWhole） - 部分一致すべき検索がヒットしない
- 謎のエラー発生
という流れ。
これに気づくまでに 約6時間溶かしました。
補足：Excel を再起動すると検索条件は初期化される
Range.Find が引き継ぐCtrl+Fの検索条件は Excel アプリケーション全体で共有されている設定 のため、
Excel を一度終了すると検索条件は初期値にリセットされます。
そのため、
- 「昨日は動いたのに今日は動く」
- 「Excel を閉じて開き直したら直る」
といった挙動も、この仕様で説明できます。
実行のたびに挙動が変わるように見える場合は、
“Excel を閉じたかどうか” も要確認ポイント になります。
きっかけ：先輩の一言
別部署の先輩が昔、
「
.Findが変な動きしたら Ctrl+F の設定疑え」
と言っていたのを思い出して試したところ…
完全一致設定が残っていました。
教訓：Range.Find を使うときは引数を明示
対策は単純で、必要な引数を毎回すべて明示的に指定すること。
Set r = Cells.Find(
What:="対象", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False _
)
特にLookAtは本当に罠です。
余談：2万行のレガシーVBAに潜む.Findの地雷
今回触っていたのは 2万行超えのレガシー VBA でしたが、.Find 以外にもなかなか強烈でした。
- 意味のない変数名が大量（
m,l,k,x1,x2……） - グローバル変数がそこら中で使い回されている
- コメントはほぼ皆無で、処理意図がコードからしか読み取れない
- If や For が入り乱れた 5重ネスト の処理が平然と存在
- 流れ制御は
GoToでジャンプしまくり -
Option Explicitもなく、変数定義の強制すらされていない
こういう環境だと、Range.Find のような「状態を引きずる関数」が混ざった瞬間に、
バグの原因特定コストが一気に跳ね上がる ことを痛感しました。
まとめ
-
Range.FindはCtrl+Fの検索条件を引き継ぐ - 引数未指定は 前回設定がそのまま適用される
- 「1回目だけ動く」はほぼコレ
- 毎回
LookAtなどを明示すること
同じ罠にハマる人が救われますように。