WorksheetFunction.Matchで配列を指定した場合の制限について

最終更新日：2013-06-07

WorksheetFunctionでMatchを使いデータ検索する事は良くあります。

この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。

以下のようなVBAコードになります。

Sub sample1()
　　Dim i As Long
　　Dim ary As Variant
　　ary = Range("A1:A10000")
　　・・・
　　i = WorksheetFunction.Match("***", ary, 0)
　　・・・
End Sub

これは正しく動作します。

ただし、WorksheetFunction.Matchで配列を使うと、処理速度は遅くなります。

直接Rangeを指定した方が、はるかに処理速度は速くなります。

とはいえ、数万件の検索を１回実行するだけなら、体感速度はほとんど変わらないでしょう。

そこで、どうしてもこのような記述になる場合があります。

しかし、この場合、配列の要素数に制限があります。

Sub sample2()
　　Dim i As Long
　　Dim ary As Variant
　　ary = Range("A1:A70000")
　　・・・
　　i = WorksheetFunction.Match("***", ary, 0)
　　・・・
End Sub

これはエラーとなります。

具体的には、配列の要素数が、

65,536

これが限界値となっています。

さすがに、めったにあることではありません。

しかし、この情報が、ほとんどどこにも出ていない事が問題です。

以前、このエラー原因を問われた時に、いろいろ調べたのですが、どこにも見当たりませんでした。

と言う事で、自身の備忘録もかねて、ここに掲載しておきます。

WorksheetFunction.Matchで配列を指定した場合の制限について

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

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

ExcelマクロVBA技術解説
WorksheetFunction.Matchで配列を指定した場合の制限について