【Excel VBA】入力規則でアプリケーション定義エラー: へなちょこOLのおもちゃ箱

【Excel VBA】入力規則でアプリケーション定義エラー

事件勃発

問題のコードはコチラ

Sub test()
　With Range("A1").Validation
　　 .Delete
.Add Type:=xlValidateList , _
　　 Operator:=xlEqual, _
　　 Formula1:=strFormula
　 End With
End Sub

.Addで出た
なんでーー???

同じモジュール内で、IMEのOn/OffとかxlValidateWholeNumber(指定した間の整数のみ)とかはエラー出ないのに。
それどころか、さっきはふつーに通ってたのに!

調査

とゆーかググっただけｗ

いろんなサイトやブログでの原因をまとめてみた

・シートの保護がかかっている
・すでに設定されている
・シートがActiveになっていない
・Formulaの式中のシート名が存在しない

うん。
どれも該当しない。
上記は全部イミディエイトで確認した。
わからん。

目下急ぎの仕事があったので、とりあえず放置。
んで翌日の朝回してみたら、エラー出なかった。

気のせいだったことにしておこうｗ

デバッグ

しばらくして、また回したら、嬉しくない再会。
2度と会いたくなかったんだけど。

仕方なくデバッグしてみる。

うんうん唸りながら3時間・・・。

ふと、strFormulaって何が返るんだ?って気になった。
他の人が作ったやつだし、エラー出ないときもあるから、strFormulaは絶対に合ってると思い込んでてちゃんと見てなかった。
COUNTIFだけども少々ややこしいんで、strFormulaをコピーしてセルに入れてみた。

ｱｯ・・・!

そりゃそーだorz

答え

画像でわかるかな?

Excelの仕様です。

列の表示をR1C1形式にしてるときに、A1形式で式を入れるとエラー吐きます。
逆も然り。
このせいですね。

strFormulaはA1形式で書いてます。
んで、へなちょこは基本的にR1C1です。
VBAとゆーよりかはExcelそのものがエラー出してるんですねぇ。

それならもうちょっとわかりやすいエラー出してよ。
MSさんは、アプリケーション定義エラーをVariantと定義しているのだろうか。
なんかよくわからんエラーはとりあえずアプリケーション定義エラーにしとけ!みたいな・・・。

対策

入力規則を設定する直前に、コレを入れてみた