「VBA Match関数の限界」についての誤解
ツイッターで出したVBAのお題です。
発端はエゴサーチからです。(笑)
「教えて！goo」で引用されていたのを見つけました。
あちこちで引用されているのは見かけることはあるのですが、以下ではよく言うディスられているような文章を見かけました。
引用が多くなれば批判的な意見が出てくるのは当然だし構わないと思います。
ですが、今回見たものは回答者の理解不足からきているものです。
お題のツイート
> ここのサイトはあまり信用しないほうが良いでしょう
と言われてしまった。
> こちらの当面の目的は、「〇〇○○の神髄」とやらの内容の間違いを確認することでしたから、その目的は果たしたと思っていましたが…
なかなか素晴らしい目的だな。
ここでの論点のずれはどこにあるでしようか?
※VBAの問題としては結構難問です。たぶん。
そこがどこなのか、説明できますか?
それぞれでのテストコード自体は正しいものです。
では、どこが違うのでしょうか?
という問題です。
比較すべきページ
「教えて！goo」の「VBA Match関数の限界」
当サイト「WorksheetFunction.Matchで配列を指定した場合の制限について」
WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。
解説のツイート
以下で一連の解説ツイートをしています。
https://twitter.com/yamaoka_ss/status/1283327042952421379
解説スタートします。
最初に問題のページの再確認から。
https://oshiete.goo.ne.jp/qa/11436912.html
問題点は、
・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
この検証も全てが絶対に間違っていないとは言いません。
今後のバージョンや環境によっても違った結果になる事もあるでしょう。
もし違った結果が出たというのなら、連絡をもらえれば、可能な限り徹底的に再検証します。
最初に問題のページの再確認から。
https://oshiete.goo.ne.jp/qa/11436912.html
WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。
https://excel-ubara.com/excelvba4/EXCEL226.htmlWorksheetFunctionで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次元配列です。
それが34465でエラーといっています。
Matchで正しく扱える1次元配列の上限は65536までです。
65536までの1次元配列なら正しく検索できます。
先の34465でエラーというのは、たまたま100000の時の結果でしかありません。
試しに70000や150000でやってみると良いでしょう。
滅茶苦茶な結果になってしまうのが分かるはずです。
配列が上限を超えてしまっているので、結果がどうなるか分かりません。
Dim i As Long
Dim ary As Variant
ary = Range("A1:A70000")
i = WorksheetFunction.Match("***", ary, 0)
この配列aryは2次元配列です。
これに対して2010までのMatchはエラーになってましたが、
2013からはエラーにはならず、正しく扱えるようになっています。
「型が一致しません」のエラーになります。
1次元、2次元に関わらずエラーになります。
2013以降では、このエラーが出なくなっています。
ただし1次元配列で正しく検索できるのは65536までの配列になります。
これを超える配列は意図しない結果になります。
「Excel2013以降では、この制限はなくなっていることが確認できました。」
と書いているものです。
2013以降では、はるかに大きな2次元配列を正しく扱えるようになっています。
ただし、1次元配列は65536までしか正しく扱えません。
100000の1次元配列をMatchでどの位置まで正しく取得できるかの検証をただけになります。
2010までなら「型が一致しません」のエラーになったので直ぐに分かったはずですが、
2013以降では、中途半端に検索できてしまうので気が付かなかったのでしょう。
と書いています。
「エラーは出ますね。」は良いでしょう。ご自身で確認した範囲での回答なのですから。
しかし、私の提示しているコードを試しもせずに、
(ここのサイトはあまり信用しないほうが良いでしょう)
このような姿勢は、結果として自身が損をすることになると思います。
Matchで扱える配列は34465までだと思いこんでしまっているのですから。
なにより、間違った回答をもらった質問者がかわいそうにさえ思えてきます。
さすがにこれではお話になりません、テスト経験不足でしょう。
かといって、この回答者の人格を否定などするつもりはありません。
あくまで、ここでの回答がそうだというだけの事です。
他では良い回答を沢山しているかもしれません。
https://excel-ubara.com/excelvba4/EXCEL_VBA_434.html
VBAでワークシート関数が使えるのはとても便利です。WorksheetFunctionのシート関数を使う事は多いですが、配列を引数に指定した場合は要素数に制限があります。この制限があることは、ワークシート関数だという事を考えれば仕方ないのかもしれません。
この検証も全てが絶対に間違っていないとは言いません。
今後のバージョンや環境によっても違った結果になる事もあるでしょう。
もし違った結果が出たというのなら、連絡をもらえれば、可能な限り徹底的に再検証します。
同じテーマ「ツイッター出題回答 」の記事
Variant仮引数にRange.Valueを配列で渡す方法
ツイッターで出したVBAのお題です。Variant型は、どんなデータ型も受け入れることができます。RangeオブジェクトのValueを配列で受け取るにはVariant変数が必要です。ただし、実引数にRangeオブジェクトを直接指定する場合には注意が必要です。
Variant仮引数のByRefとByValの挙動違いツイッターで出したVBAのお題です。Variant型は、どんなデータ型も入れることができてしまいます。具体的なデータ型の代わりに使用することで、より柔軟にVBA記述ができるようになります。ただしこの便利さゆえに、逆に注意しなければならない挙動もあります。
100桁の正の整数値の足し算ツイッターで出したVBAのお題です。数の単位に「無量大数」というものがあります。VBAでこの無量大数の足し算をするにはどうしたら良いでしょうか。そこで、100桁の正の整数の足し算をVBAで実現してみましょう。
「VBA Match関数の限界」についての誤解VBAで数値を漢数字に変換する方法
ツイッターで出したVBAのお題です。算用数字を漢数字に変換するVBAです。滅多に必要になるものではないのに、なぜこんな問題を出したかと言うと、最近シリーズで書き始めた「Excel将棋」で必要になったからです。
囲碁で相手の石を囲んで取るアルゴリズムツイッターで出したVBAのお題です。Excel囲碁を作っていて、相手の石を囲んで取れるかどうかの判定、相手の石を取るにはどうしたら良いかというもの。囲碁で相手の石をとる ここで、8二に黒を打てば、このように囲まれている白が取られます。
VBAで「3Lと5Lのバケツで4Lの水を作る」を解くツイッターでVBAのお題として出したものです。昔からよくある問題です。「3Lと5Lのバケツで4Lの水を作る」これをVBAを使って自動で求めてみようという事です。VBA問題：ツイートの記録 【VBA問題】 「3Lと5Lのバケツで4Lの水を作る」・2つの容器サイズは変えられるように引数で受け取る （3,5,
言語依存の関数を使用できるFormulaLocalツイッターでVBAのお題として出したものです。複数セルに一括で数式を入れるバ宇井の記述と、言語環境に依存する関数をセルに設定する場合のFormulaプロパティの使い方についての問題です。問題を出したツイート A1:A10セルに半角の英数文字が入っているので、これを全角で表示するためにB1:B10セルに数式をVBAで…
配列のUBoundがLBoundがより小さいことはあり得るかツイッターでVBAのお題として出したものです。配列の下限が上限より大きくなるような配列は存在するかの問題です。LBound(ary)>UBound(ary) この条件を満たすような配列は存在するか? 問題を出したツイート 配列の下限と上限を調べるにはLBound関数とUBound関数を使います。
コレクションの要素を削除する場合ツイッターで出したVBAのお題（投票）です。Collectionから要素を順に削除するVBAの正誤問題です。問題を出したツイート 【VBA問題】 DimcAsNewCollection Dimi Fori=1To100 c.Addi,CStr(i) Next Fori=1To100 c.Remove□ Next 四…
greeenはgreenに、greeeeeNをGReeeeNに変換ツイッターで出したエクセルの入力規則のお題です。「greeenはgreenに、greeeeeNやGReeeeeenはGReeeeNに直す」文字列操作のVBA問題です。問題を出したツイート 【VBA問題】 greenは緑です。
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。