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

最終更新日：2013-06-07

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

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

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

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

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

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

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

同じテーマ「マクロVBA技術解説」の記事

速度比較決定版【Range,Cells,Do,For,ForEach】

何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いかとか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。

エクセルVBAのパフォーマンス・処理速度に関するレポート

ExcelのVBAは遅い・重いと良く言われることが多いようですが、VBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。

VBAのFindメソッドの使い方には注意が必要です

vbafindでの検索が極めて多く、Findメソッドは検索からの流入ではトップクラスです、アクセス解析で分かった事ですが正直少し戸惑っています。なぜなら私はFindメソッドをほとんど使いません、Match関数や配列を使って処理したほうが高速かつ確実に動作するからです。

WorksheetFunction.Matchで配列を指定した場合の制限について
マクロVBAの高速化・速度対策の具体的手順と検証

マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。

動的2次元配列の次元を入れ替えてシートへ出力（Transpose）

動的配列を使い様々な処理をした後にシートへ出力しようとしたとき縦横が違っている為そのまま出力できませんそもそも動的配列の要素数をRedimで変更できるのは最下位の次元のみになります。2次元配列の場合ReDimmyArray(2,10) ReDimmyArray(2,11) これはOKですが

大量データで処理時間がかかる関数の対処方法（SumIf）

大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、そういう場合でも、多くの場合は何らかの対策があるものです、個別のロジックの記述でこれに対応する一つの有効なマクロVBAコ－ドについて解説します。以下の例で解説します。

大量データにおける処理方法の速度王決定戦

VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。

遅い文字列結合を最速処理する方法について

VBAは遅い… よく聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型（可変長文字列）についての基礎知識が必要です。

大量VlookupをVBAで高速に処理する方法について

大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。

Withステートメントの実行速度と注意点

マクロVBAにおいてWithステートメントはとても重要です、可読性（読みやすさ、理解しやすさ）、実行速度においては、その役割はとても大きいものになります。本記事では、Withステートメントを使うか使わないかでのマクロ実行速度の差に焦点を絞って検証します。

学びたい講座がきっとみつかる：AI・データサイエンスなど最先端のIT技術からマクロVBA講座まで

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜ExcelマクロVBA入門
4.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
5.変数とデータ型（Dim）｜ExcelマクロVBA入門
6.繰り返し処理（For Next)｜ExcelマクロVBA入門
7.マクロって何？VBAって何？｜ExcelマクロVBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.ひらがな⇔カタカナの変換｜エクセル基本操作
10.空白セルを正しく判定する方法（IsEmpty,IsError,HasFormula）｜VBA技術解説

トップ >

マクロVBA >

マクロVBA技術解説 >

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

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

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

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