先日Twitterで教えて頂いたInStr関数の名前付き引数の不思議な挙動について情報を整理してみた。
きっかけ
先日こんなツイートを拝見しました。
VBAラボ【超難しいけどいつか躓く話】
— Excelラボとみ君 (@Excel_labotomi) 2019年8月16日
・VBAでは、該当の文字列を含むか判定するときInstr（イン・ストリング）関数を使うんだけど「名前付き引数」がうまく機能しない問題がある.
・機能させるには、
→Vba.Instrと記述し始めること
→Start:= という引数を必ず指定すること
の２つを満たせば良い pic.twitter.com/D6oETL9cW3
・VBAでは、該当の文字列を含むか判定するときInstr（イン・ストリング）関数を使うんだけど「名前付き引数」がうまく機能しない問題がある.
・機能させるには、 →Vba.Instrと記述し始めること →Start:= という引数を必ず指定すること の２つを満たせば良い
私はInStrで名前付き引数を指定したことがなくて、全く知りませんでした・・・。
あまりにも不思議なのでちょっと調べてみたのですが、Google検索では同じような事象が全く見つかりません。
同じことで困る人がいるかも知れないので、徹底的に調査してネットの海に書き残すことにしました。
InStrについて
VBAのInStr関数は、つい先日の記事でも使いましたが、文字列中の文字列の開始位置を取得する関数です。
「見つからなかった場合0を返す。」という仕様のため「文字列中に文字列が存在するか」という単純な目的でもよく使用されます。（私はLike演算子派ですが←どうでも良い）
If InStr(1, "Excelは素晴らしい", "x") > 0 Then MsgBox "xは存在する！" End If
また、「文字列」と書いているように、単一ではない文字列の検索やUNICODE文字でも対応しているため、比較的使いやすい関数ではあります。
しかし、前述の通り名前付き引数に問題があるのだという。
更に簡単な調査によれば、引数の指定方法によって良くわからない挙動をするらしい。
原因調査に入る前に、まずは基本的な仕様を確認してみる。
オブジェクトブラウザでのInStr
F2キーでオブジェクトブラウザを開いてInStrを調べてみると、VBA.Strings.InStrというInStrが1件だけ見つかった。
Function InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])
全部、角カッコで囲われているが、省略可能引数なのだろうか。
（※そのようなことは有りませんでした。）
Microsoft.comでのInStr
InStr([ start ], string1, string2, [ compare ])
| 引数 | 説明 |
|---|---|
| start | 省略可能。 それぞれの検索の開始位置を設定する数値式です。 省略すると、最初の文字の位置から検索が start されます。 start に Null が含まれる場合、エラーが発生します。 compareが指定されている場合は、start引数が必要です。 |
| string1 | 必須です。 検索元の文字列式です。 |
| string2 | 必須です。 検索場所となる文字列式を指定します。 |
| compare | 省略可能です。 文字列比較の種類を指定します。 compare が Nullの場合、エラーが発生します。 compare が省略された場合、Option Compare 設定は比較のタイプを決定します。 ロケール固有のルールで比較するために有効な LCID (LocaleID) を指定します。 |
この表によれば、先頭のstartと末尾のcompareが省略可能。だそうです。
これなら実際の動きと辻褄が合います。
Compareの仕様は以下の通り。
| 定数 | 値 | 説明 |
|---|---|---|
| vbBinaryCompare | 0 | バイナリモードで比較する（全角/半角、大文字/小文字、ひらがな/カタカナを区別する） |
| vbTextCompare | 1 | テキストモードで比較する（全角/半角、大文字/小文字、ひらがな/カタカナを区別しない） |
| vbDatabaseCompare | 2 | Accessの場合のみ有効。データベースに格納されている設定に基づいて比較する。（既定値はテキストモード） |
どこかで読んだのですが、vbUseCompareOption = -1（Option Compareステートメントの設定を使用して比較）は、実際には定義されていないし動かないらしいです。
※以下、脳内再生。適当に読み流してください。
でも、ちょっとまった！ VBAって前方の引数省略できたっけ？
記載通りに受け取ると、前半は前半、後半は後半で省略可能ということになるけれど・・・。
VBAで作成できる省略可能引数
Optionalは、後半の引数しか省略可能に設定できないが、VBA自体はCとかC++で開発されているので言語仕様に縛られないのだろうか？
上記ヘルプの例文には、実際に前方省略している例が含まれていることから、正式なものだと考えて間違いなさそうだ。（コメント部分：日本語訳）
Dim SearchString, SearchChar, MyPos SearchString ="XXpXXpXXPXXP" ' 検索する文字列。 SearchChar = "P" ' 「P」を検索します。 '位置4から始まるテキスト比較。6を返します。 MyPos = Instr(4, SearchString, SearchChar, 1) '位置1から始まるバイナリ比較。9を返します。 MyPos = Instr(1, SearchString, SearchChar, 0) '比較はデフォルトでバイナリです（最後の引数は省略されます）。 MyPos = Instr(SearchString, SearchChar) ' 9を返します。 MyPos = Instr(1, SearchString, "W") ' 0を返します。
ところで、以下の部分のバイナリってどういう意味だろう？
' Comparison is binary by default (last argument is omitted).
'比較はデフォルトでバイナリです（最後の引数は省略されます）。
結局、徹底的に調べた結果、
前方省略時は最後の引数は省略しなければならず、デフォルトの
vbBinaryCompareが使用される。と訳すべきではないかと予想。
という疑問はさておき、ひとまず前半の省略はできるのだ。という事を受け入れて、実際のところどうなのか調査していきます。
InStrの不思議な挙動
とりあえず、何がOKで何がNGなのか・・・。
良くわからない動きが多いので、想定される組み合わせを片っ端から試してみました。
基本的な使用例
まず、以下の２つは基本形なので、動いて当然だ。
'OK ?InStr(2, "abcde", "cd") '=3 ?InStr(2, "abcde", "cd", vbBinaryCompare) '=3
そして、前半を省略する記法も問題なく動いた。
'OK ?InStr("abcde", "cd") '=3
しかし、前半を省略して後半のみ記述する、ということは認められなかった。
'NG 実行時エラー 型が一致しません ?InStr("abcde", "cd", vbBinaryCompare)
引数が「文字列、文字列、数値」になるので、「型を見て判断している」ならば、このようなことにはならないはず。
本来の「数値、文字列、文字列」になっていないからこそのエラー。
つまり「引数の数を見て判断している」のだと思われる。
省略記法
Optionalな省略可能引数では、「引数の枠は設けるが何も書かない」という選択肢もある。
例えば以下のResize(,10)のように後半の引数は指定したいが、前半の引数はデフォルト値に委ねたい場合などに使う。
'A1:J1を出力 ?Range("A1").Resize(, 10).Address(0, 0)
この記法をInStrに使用した場合どうなるかというと・・・。全て構文エラーとなった。
'コンパイルエラー 構文エラー 赤字 ?InStr(, "abcdef", "c") 'String1は省略可能引数ではないので構文エラー ?InStr(1, ,"c" ) ?InStr(, ,"c" ) '末尾の省略は普通の関数でも構文エラー ?InStr(1, "abcdef", )
また、後述するVBA.InStrというライブラリ名を付けて呼び出す方法をとった場合は、実行時エラーとなった。
'実行時エラー プロシージャの呼び出し、または引数が不正です。 ?VBA.InStr(, "abcdef", "c")
というわけで、InStrのStartは「省略可能引数を空欄にすることは出来ない」と考える必要がありそうだ。
危険な暗黙的な変換
この他にも暗黙的な変換により、実行時エラーにはならないが、意図したとおりに動かない書き方が考えられる。
'引数が2つなら、String1,String2で解釈する ?InStr(2, "abcde") '=0 ?InStr(12345, "3") '=3
これは、第一引数の「数値が暗黙的に文字列と解釈されている」と思われる。
ここから考えても「引数の数を見て判断している」という線が有力ではないだろうか。
さらに、もう一つ。文字列が3回続いた場合はどうなるか。
'引数が3つなら、Start,String1,String2で解釈する '第一引数は数値化されるため、文字列を指定してもキャスト可能ならエラーにはならない ?InStr("2", "abcdef", "c") '3 ?InStr("4", "abcdef", "c") '0 'キャスト出来ない場合は「型が一致しません」となる ?InStr("a12345", "10305", vbBinaryCompare)
これは第一引数の「文字列が数値として解釈されている」が、これは対して怖く無いと思う。
さらに、さらに、もうひとつ。
'引数が3つなら、Start,String1,String2で解釈する '第三引数は文字列化されるため、数値を指定してもエラーにはならない ?InStr("1", "10305", vbBinaryCompare) '2 ?InStr(1, "10305", vbBinaryCompare) '2 ?InStr(1, "10305", vbTextCompare) '1
おさらいすると、vbBinaryCompare=0、vbTextCompare=1である。
つまり、以下の様に「定数が文字列として解釈されている」のである。
?InStr(1, "10305", "0") '2 ?InStr(1, "10305", "0") '2 ?InStr(1, "10305", "1") '1
中途半端な知識で省略記法を使うと、痛い目を見るかもしれない。
名前付き引数によるコンパイルエラー
ここでようやく発端のツイートの話で、InStrの引数に名前を指定すると、問答無用でモジュールレベルのコンパイルエラーになります。（名前付き引数ではなかったのか・・・？）*1
'構文エラー（赤字） ?InStr(String1:="abcde", String2:="cd") ?InStr(Start:=2, String1:="abcde", String2:="cd") ?InStr(String1:="abcde", String2:="cd", Compare:=vbBinaryCompare) ?InStr(Start:=2,String1:="abcde", String2:="cd", Compare:=vbBinaryCompare)
--------------------------- Microsoft Visual Basic for Applications --------------------------- コンパイル エラー: 構文エラー --------------------------- OK ヘルプ ---------------------------
以下のように記載すると、プロシージャレベルのコンパイルエラーになります。*2
?InStr(2, String1:="abcde", String2:="cd") ?InStr(2, "abcde", String2:="cd")
--------------------------- Microsoft Visual Basic for Applications --------------------------- コンパイル エラー: オブジェクトは名前付き引数をサポートしていません。 --------------------------- OK ヘルプ ---------------------------
ここでエラーの原因としてString2:=が選択状態に変化する。
これまた不思議な現象で、下記の様に記入して
?InStr(2, String1:="abcde", "cd")
カーソルが離れるとこうなる。
?InStr(2, String1:="abcde", String1:="cd")
当然、上記と同じエラーで、String1:=が選択状態に変化する。
名前付き引数のエラーを回避
なぜか、InStrにプロジェクト名、ライブラリ名を付けると、名前を付けてもコンパイルエラーが出なくなる。
?VBA.InStr(Start:=2, String1:="abcde", String2:="cd") ?Strings.InStr(Start:=2, String1:="abcde", String2:="cd") ?VBA.Strings.InStr(Start:=2, String1:="abcde", String2:="cd")
ところが、コンパイルは出来ても、先頭の省略可能なはずの引数Startの省略は認められない。
'実行時エラー '5' : プロシージャの呼び出し、または引数が不正です。 ?VBA.InStr(String1:="abcde", String2:="cd") ?VBA.InStr(String1:="abcde", String2:="cd", Compare:=vbBinaryCompare) 'OK ?VBA.InStr(Start:=2, String1:="abcde", String2:="cd") ?VBA.InStr(2, String1:="abcde", String2:="cd") ?VBA.InStr(2, "abcde", String2:="cd") ?VBA.InStr(Start:=2, String1:="abcde", String2:="cd", Compare:=vbBinaryCompare) 3 '構文エラー ?VBA.InStr(Start:=2, "abcde", String2:="cd")
もしかしたら、InStrは２種類あるのかもしれない。
そして、もし２つが別物なら、Array関数と同じようにOption Compareによる挙動が異なるかもしれない。
もしかしたらvbUseCompareOption =-1が使えないというのも、片方限定だったりして？（要調査）
カーソルを合わせると実行結果が表示される
デバッグ中にInStrにカーソルを合わせると、何故か実行結果が表示される。
結果が表示されるのは演算子の場合だけだと思っていた。
普通の関数でこのようなことは起こらない。
InStrには何か深い秘密が隠されているのではないだろうか。
私にはこの謎が解けなかった。
同じ名前で二種類ある関数
余談だが、ライブラリ名を付けた場合と省略した場合で、挙動が変わる同名の関数がいくつかある。
Len関数とLenB関数Array関数InputBox関数
Len関数とLenB関数
本記事を投稿してから、いつもお世話になってるimihitさんにLenとLenBも二種類あることを教えていただいた。
例えば、ユーザー定義型Typeの大きさを計測しようとすると、VBA.の有無で挙動が異なる。
Type MyType AAA As Long BBB As Long End Type Sub Lenにも二種類ある() Dim t As MyType 'typeを入れられる Debug.Print Len(t) Debug.Print LenB(t) 'typeを入れられない ' Debug.Print VBA.Len(t) ' Debug.Print VBA.LenB(t) ' Debug.Print Strings.Len(t) ' Debug.Print Strings.LenB(t) ' Debug.Print VBA.Strings.Len(t) ' Debug.Print VBA.Strings.LenB(t) End Sub
エラーはVariant型変数にType型変数を代入しようとしたときに出るエラーメッセージで、モジュールレベルのコンパイルエラーが起こる。
'--------------------------- 'Microsoft Visual Basic for Applications '--------------------------- 'コンパイル エラー: ' 'パブリック オブジェクト モジュールで定義されたユーザー定義型に限り、変数に割り当てることができ、実行時バインディングの関数に渡すことができます。 '--------------------------- 'OK ヘルプ '---------------------------
元々VariantにはTypeは入れられないので、エラーそのものはおかしくない。
でもライブラリ名を付けた事で、このエラーが出るようになるということは
VBA.Lenは、内部的にVariantに代入してから計測しているLenはVariantに代入せずにいきなり計測している
と考えられないだろうか。
だから何という話だが、LenはInStrで似ているところが他にもある。
- Stringsライブラリに含まれている
- カーソルを合わせると実行結果が表示される
よって、私は同一の起源を持つのではないかと結論づけた。
やはりGoogle検索では資料が見つけられないので、根拠となる資料は無い。
Array関数
下記のページで言われているように、Variant配列を生成するArray関数には２種類ある。
インストラクターのネタ帳 - VBAのArray関数は2つあるのではないかという話
一般的なのは、ただのArray関数。
オブジェクトブラウザには存在しない不思議な関数だったりする。
'これはOptionBase の影響を受ける為、最初の要素は0か1か分からない。 arr = Array(1,2,3)
もう一つがオブジェクトブラウザでは隠しオブジェクトとなっている関数。
さり気なく「アンダーバーから始まる特殊な名称のオブジェクト」の記述方法も紹介しておく。
'常に要素は0から arr = VBA.Array(1,2,3) arr = VBA.[_HiddenModule].Array(1, 2, 3)
関連ツイート
あーそうでした。https://t.co/rzBmqlT13T
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年8月16日
Array ← OptionBase1の影響を受ける
と
VBA.Array ← 常に要素は0から
ってありましたよね
VBA.=Strings.=VBA.Strings. で、 無印InStrだけ仲間はずれという結果なので、実は普段使っているInStrは実はイレギュラーな存在なのかも・・・しれません。
Input関数
下記のページで言われているように、機能の全く違うInputBoxが二種類ある。
インストラクターのネタ帳 - InputBox関数とAppication.InputBoxメソッドはまったくの別物ですよ
まずは、省略表記の場合のInputBox関数
InputBox("InputBox関数") VBA.InputBox("InputBox関数") Interaction.InputBox("InputBox関数") VBA.Interaction.InputBox("InputBox関数")
そして、もう一つがExcel.Application配下のInputBoxメソッド
こちらはExcelでしか使用できないが、色々と高度な事ができる強い味方。
Application.InputBox("InputBoxメソッド") Excel.Application.InputBox("InputBoxメソッド")
ただ、コレはオブジェクトプロパティで二種類あることがはっきり分かるので、InStrと同じ性質とは考えられない。
関連ツイート
InputBoxですと、オブジェクトブラウザで
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年8月16日
Excel.Application.InputBox
VBA.Interaction.InputBox
という二種類が確認できるのですが、本件のInStrはそういったものが無いんですよね。
挙動に違いがあるようには見えませんし、「別物」と言って良いものかどうかは疑問です。
参考資料
私のブログ・ツイートでは、コンパイルエラーを種類に応じて2種類の呼び分けをしている。
これらの違いについて、いつか執筆したいと思っているが、とりあえず簡単にメモしておく。
完全オリジナルなので、他では通用しないと言うことは頭に留めて置いて欲しい。
（広まる分には一向に構わないが）
モジュールレベルコンパイルエラー
いわゆる
- コードが赤字になる
- 「プロジェクトのコンパイル」が通らない
やつである。
本記事で登場した
'構文エラー ?InStr(String1:="abcde", String2:="cd")
なんかの、赤字になるやつが代表的だろう。
モジュールがコンパイル出来なくなるので「モジュールレベル」と呼んでいるが、ある意味「パブリックレベル」なんじゃないかと最近迷い始ている。
「プロジェクトのコンパイル」さえ実施すれば検知できるので、あまり怖くない。
プロシージャレベルコンパイルエラー
要するに
- 「モジュールレベルではないコンパイルエラー」
なのだが、こちらはテスト実行して初めて検知できる
- 「実行時エラーのようなコンパイルエラー」
である。
どういうことかと言うと、本記事で登場した
'オブジェクトは名前付き引数をサポートしていません。 ?InStr(2, String1:="abcde", String2:="cd")
のようなコードで、「プログラムがプロシージャに流入して初めて検知できる」という恐ろしいコンパイルエラーである。
事故を防ぐには全てのプロシージャに1回は流入するようなテストコードを書いて、定期的に巡回させる他無いと思われる。
まとめ
以上の結果をまとめると、重要なのは以下の2つ
- InStrは引数の数を見て判断している
- 名前付きで引数を使うには
VBA.InStrとしなければならない
なお、InStrとVBA.InStrが完全に別物かは分からないが私の結論です。
ただ、使う側からしてみたら別物だと考えるべきです。たまたま同じ実装なだけかもしれません。
具体的には
InStr([ start ], string1, string2, [ compare ])
Startは省略可能。ただしInStr(Start,String1,String2)はできる。InStr(String1,String2)はできる。InStr(,String1,String2)はできない。←イレギュラー
Compareは省略可能。ただしInStr(Start,String1,String2,Compare)はできる。InStr(String1,String2,Compare)はできない。（型に関わらずInStr(Start,String1,String2)として解釈される。）
- 名前付き引数を使うには癖がある。
InStr(Start:=2, String1:="abcde", String2:="cd")はできない。VBA.InStr(Start:=2, String1:="abcde", String2:="cd")はできる。
一つ気になるのが、前半の省略可能引数を持つ関数はInStr以外に存在しないのか？ということです。
同じような関数を、今までに見た覚えがありません。
もしご存知の方がいたら教えていただけると幸いです。
最後に、話題になってから随分経ちましたが、ようやく記事にすることが出来ました。
情報を提供して下さった各位には感謝を。
もしInStrで困っている人がいたら教えてあげてください。
以上
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)
