エクセル雑感
「VBA Match関数の限界」についての誤解

ExcelマクロVBAとエクセル関数についての私的雑感
最終更新日:2020-07-15

「VBA Match関数の限界」についての誤解


ツイッターで出したVBAのお題です。
発端はエゴサーチからです。(笑)
「教えて!goo」で引用されていたのを見つけました。
あちこちで引用されているのは見かけることはあるのですが、以下ではよく言うディスられているような文章を見かけました。
引用が多くなれば批判的な意見が出てくるのは当然だし構わないと思います。
ですが、今回見たものは回答者の理解不足からきているものです。


そこで、ツイッターで問題として出題のような形式にしてみました。

お題のツイート

VBA Match関数の限界

https://twitter.com/yamaoka_ss/status/1283049010618675202

たまにエゴサーチも良い。
> ここのサイトはあまり信用しないほうが良いでしょう
と言われてしまった。
> こちらの当面の目的は、「〇〇○○の神髄」とやらの内容の間違いを確認することでしたから、その目的は果たしたと思っていましたが…
なかなか素晴らしい目的だな。


VBA Match関数の限界

https://twitter.com/yamaoka_ss/status/1283051180931899393

では問題です。
ここでの論点のずれはどこにあるでしようか?
※VBAの問題としては結構難問です。たぶん。


VBA Match関数の限界

https://twitter.com/yamaoka_ss/status/1283057242598707200

確かに説明が言葉足らずな部分があります。
そこがどこなのか、説明できますか?

それぞれでのテストコード自体は正しいものです。
では、どこが違うのでしょうか?
という問題です。

比較すべきページ

「教えて!goo」の「VBA Match関数の限界」

当サイト「WorksheetFunction.Matchで配列を指定した場合の制限について」
WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。

解説のツイート

以下で一連の解説ツイートをしています。

https://twitter.com/yamaoka_ss/status/1283327042952421379


解説スタートします。
最初に問題のページの再確認から。
https://oshiete.goo.ne.jp/qa/11436912.html
WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。
https://excel-ubara.com/excelvba4/EXCEL226.html
WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。


問題点は、
・1次元配列と2次元配列の違い
・全ての要素に対して正しく検索できる配列上限は
・この上限を超えている配列内のどこまで正しく検索できるか
これらは、それぞれ別々の問題であり、別々の結果になります。
回答者は、この点が完全に分かっていないようです。


回答者のコードは、
Dim a(100000) As Long, i As Long, b As Long, j As Long
For i = 0 To 100000
a(i) = i
Next i
For j = 34400 To 65536
On Error Resume Next
b = WorksheetFunction.Match(j, a(), 0)
DoEvents
If Err.Number <> 0 Then
MsgBox b
この場合の配列aは1次元配列です。


配列a(1 To 100000)から正しく検索できる位置の上限を探してしまっています。
それが34465でエラーといっています。
Matchで正しく扱える1次元配列の上限は65536までです。
65536までの1次元配列なら正しく検索できます。


65536を超える配列は正しく検索できません。
先の34465でエラーというのは、たまたま100000の時の結果でしかありません。
試しに70000や150000でやってみると良いでしょう。
滅茶苦茶な結果になってしまうのが分かるはずです。
配列が上限を超えてしまっているので、結果がどうなるか分かりません。



対して私のVBAは、
Dim i As Long
Dim ary As Variant
ary = Range("A1:A70000")
i = WorksheetFunction.Match("***", ary, 0)
この配列aryは2次元配列です。
これに対して2010までのMatchはエラーになってましたが、
2013からはエラーにはならず、正しく扱えるようになっています。


2010では65536を超える配列の場合、
「型が一致しません」のエラーになります。
1次元、2次元に関わらずエラーになります。
2013以降では、このエラーが出なくなっています。
ただし1次元配列で正しく検索できるのは65536までの配列になります。
これを超える配列は意図しない結果になります。


記載したのは、2次元配列でのMatchについて、
「Excel2013以降では、この制限はなくなっていることが確認できました。」
と書いているものです。
2013以降では、はるかに大きな2次元配列を正しく扱えるようになっています。
ただし、1次元配列は65536までしか正しく扱えません。


先の回答者は、この点を理解していません。
100000の1次元配列をMatchでどの位置まで正しく取得できるかの検証をただけになります。
2010までなら「型が一致しません」のエラーになったので直ぐに分かったはずですが、
2013以降では、中途半端に検索できてしまうので気が付かなかったのでしょう。


そして「エラーは出ますね。」(ここのサイトはあまり信用しないほうが良いでしょう)
と書いています。
「エラーは出ますね。」は良いでしょう。ご自身で確認した範囲での回答なのですから。
しかし、私の提示しているコードを試しもせずに、
(ここのサイトはあまり信用しないほうが良いでしょう)


愚かしい事だとは思いますが、人が時に陥りやすい過ちでもあるでしょう。
このような姿勢は、結果として自身が損をすることになると思います。
Matchで扱える配列は34465までだと思いこんでしまっているのですから。
なにより、間違った回答をもらった質問者がかわいそうにさえ思えてきます。


記載内容と違うテストをしておいて、結果が違う信用できない。
さすがにこれではお話になりません、テスト経験不足でしょう。
かといって、この回答者の人格を否定などするつもりはありません。
あくまで、ここでの回答がそうだというだけの事です。
他では良い回答を沢山しているかもしれません。


「VBAでシート関数使用時の配列要素数制限」
https://excel-ubara.com/excelvba4/EXCEL_VBA_434.html
VBAでワークシート関数が使えるのはとても便利です。WorksheetFunctionのシート関数を使う事は多いですが、配列を引数に指定した場合は要素数に制限があります。この制限があることは、ワークシート関数だという事を考えれば仕方ないのかもしれません。

この検証も全てが絶対に間違っていないとは言いません。
今後のバージョンや環境によっても違った結果になる事もあるでしょう。
もし違った結果が出たというのなら、連絡をもらえれば、可能な限り徹底的に再検証します。




同じテーマ「エクセル雑感」の記事

【超難問】エクセル数式問題
IFステートメントの判定
日付の謎:IsDateとCDate
ツイッター投稿用に文字数と特定文字で区切る
マクロ記録での色のマイナス数値について
VBAのString型の最大文字数について
Variantの数値型と文字列型の比較
Variant仮引数にRange.Valueを配列で渡す方法
Variant仮引数のByRefとByValの挙動違い
100桁の正の整数値の足し算
「VBA Match関数の限界」についての誤解


新着記事NEW ・・・新着記事一覧を見る

多階層フォルダ(ディレクトリ)の作成|VBAサンプル集(7月31日)
VBAのインデントについて|VBA技術解説(7月16日)
「VBA Match関数の限界」についての誤解|エクセル雑感(7月15日)
省略可能なVariant引数の参照不可をラップ関数で利用|VBA技術解説(7月12日)
100桁の正の整数値の足し算|エクセル雑感(7月9日)
LSetとユーザー定義型のコピー(100桁の足し算)|VBA技術解説(7月9日)
Variant仮引数のByRefとByValの挙動違い|エクセル雑感(7月5日)
Variant仮引数にRange.Valueを配列で渡す方法|エクセル雑感(7月5日)
Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ