VBA Match関数の限界

https://excel-ubara.com/excelvba4/EXCEL226.html

によるとMatch関数の扱えるシート範囲は限られるようです。
自分ではVBAで要素が1からの連番である1次元配列に対して試してみましたが、
検索範囲を配列全体に設定し、検索値"34465"ではエラー、"34464"では検索できました。
この限界は上記リンク先と異なる結果ですが、参照情報が間違っているのでしょうか。

こちらではVBA上で配列を作成し、リンク先ではセルから取得している点が異なるものの、
Excel 2010までの制約(65535要素)よりも小さい34464になっているのはなにかMatch自体の仕様以外の要素がネックとなっているのでしょうか。
尚、当方はExcel2016です。

以下は試したときのコードです。

Public Sub test()
Dim a(1 To 100000) As Long, i As Long, b As Long
For i = 1 To 100000
a(i) = i
Next i

b = WorksheetFunction.Match(34465, a, 0)
End Sub



ご教授くださると幸いです。

質問者からの補足コメント

• ワークシート関数はすべてVBAから呼び出しています。
  
  VlookupもMatchと同じく検索値"34465"ではエラー、"34464"では検索できました。
  配列の中身を検索した場合の話です。
  
  配列をワークシートに書き込んでからMatchを使うと100,000まで、Vlookpuでは1,000,000以上に対応する様でした。
  
  もはや配列での上限はワークシートでのそれよりも仕様上厳しい、どうしても配列でMatchを使いたければ、Matchをループさせて検索を連続させるしかない、と言ったところでしょうか。

    補足日時：2020/01/08 13:22

No.1 ベストアンサー 回答者： WindFaller 回答日時： 2020/01/11 17:39

こんにちは。

> もはや配列での上限はワークシートでのそれよりも仕様上厳しい、どうしても配列でMatchを使いたければ、Matchをループさせて検索を連続させるしかない、

自己完結されてしまうと、返事のつけようがないのですが、それは、この際、放っておきます。なぜなら、解決方法は、ワークシート関数だけとは限らないからです。(例：Dictinaryオブジェクト)

https://excel-ubara.com/excelvba4/EXCEL226.html
》 Excel2013以降では、この制限はなくなっていることが確認できました。
こちらは、Excel 2016(32bit) ですが、エラーは出ますね。(ここのサイトはあまり信用しないほうが良いでしょう)

'//
Public Sub test_A()
　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
　　　Exit Sub
　　End If
　Next
　On Error GoTo 0
End Sub

MsgBox ->34465 エラー値

'// 予め、A列に数値が入っています。
Public Sub test_B()
　Dim a(1 To 100000) As Long, i As Long, b As Long, j As Long
　For i = 1 To 100000
　　a(i) = Cells(i, 1).Value
　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
　　　Exit Sub
　　End If
　Next
　On Error GoTo 0
End Sub

MsgBox ->34464 エラー値

この回答へのお礼

ご返答ありがとうございます。

ご教授くださった方法と同様のことを試していたのですが、Match使わずに配列全体を1行ずつ調べていった方が断然速かったです。なぜだ

UTF8のテキスト(xml)を変数に読み込んだあと、行ごとにSplit、ループでMatchの上限行数ごとに配列化、それをさらに配列にまとめたもの(2段階配列)をつくり、Matchの検索範囲に
親配列(i)　←親配列(i)(1)~親配列(i)(34464)
を指定、ワイルドカード含む検索、見つからなければ
i = i + 1

https://www.banana-juice.com/VBA/Performance/Ran …
によれば配列内を検索する際に、For nextはワークシート関数よりもFindよりもFor eachよりも遅いことになっていましたが、

いま手元で試すと、一次元配列上を検索対象とすると逆転してループの方がMatchより速いです。
(ワークシート上を対象とすると断然For nextよりMatchが速いです)

リンク先とはExcelのバージョンなどが異なるのかもしれませんが、いずれにしろ、初めに確認すればよかった。

実はワイルドカード検索をしてその行数を取得する関係で、DictionaryもCollectionもFilter関数もFindメソッドも使えませんでした。

尚、行ごとに配列定数にしてワークシート上のMatchに渡すとメモリ不足、Matchの34464制限撤廃のためにxmlをワークシートに読み込むと読み込みが遅すぎる(UTF8だからか)‥‥。同じ理由でフィルターやスライサーも断念。

(レコードセットならワークシートを経由せずにフィルターをかけられるか‥‥)

お礼日時：2020/01/11 21:26

No.2 回答者： WindFaller 回答日時： 2020/01/17 13:12

こちらの当面の目的は、「〇〇○○の神髄」とやらの内容の間違いを確認することでしたから、その目的は果たしたと思っていましたが、検索については、ADOやら、サーチ・アルゴリズムなどがありますが、ワイルドカードやそれに準じたものを使うということなら、固定したリストをソートするか、使う前に、ソートを必要とするかをしなくてはならないのだと思います。

回答者の中で、二分木法をつかう人もいましたが、それが該当するか分かりません。

それはやってみないとなんとも言えませんが。

この回答へのお礼

ご返答ありがとうございます。

おっしゃる通り当初の質問の範囲から逸脱してしまったようでした。
No.1の時点で十分なご回答を頂いておりました。重ねてお礼申し上げます。

お礼日時：2020/01/23 20:52