Script事業案内エクセル以外ホームマクロVBAマクロVBA技術解説WorksheetFunction.Matchで配列を指定した場合の制限について最終更新日:2019-09-04WorksheetFunction.Matchで配列を指定した場合の制限についてWorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。Subこれは正しく動作します。WorksheetFunction.Matchで配列を使うと、処理速度は遅くなります。Rangeを指定した方が、はるかに処理速度は速くなります。いえ、数万件の検索を1回実行するだけなら、体感速度はほとんど変わらないでしょう。そこで、どうしてもこのような記述になる場合があります。この場合、配列の要素数に制限があります。Subこれはエラーとなります。具体的には、配列の要素数が、65,536これが限界値となっています。めったにあることではありません。この情報が、ほとんどどこにも出ていない事が問題です。以前、このエラー原因を問われた時に、いろいろ調べたのですが、どこにも見当たりませんでした。Excel2010当時に書いたものですが、最近(2019年9月)以下のメールをいただきました。「以前は確かにエラーだったが、最近確認したら問題なく検索できた」ということでした。そこで、改めて確認しましたところ、Excel2013以降では、この制限はなくなっていることが確認できました。Excel2010では変わらずにエラーとなります。Excel2010もまだまだ健在ではありますが、マクロを共有する範囲のPCにおいてExcel2013以降しか存在しないのであれば、この制限は気にしなくても良いという事になります。そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。エクセルVBAのパフォーマンス・処理速度に関するレポートExcelのVBAは遅い・重いと良く言われることが多いようですが、VBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。VBAのFindメソッドの使い方には注意が必要ですvbafindでの検索が極めて多く、Findメソッドは検索からの流入ではトップクラスです、アクセス解析で分かった事ですが正直少し戸惑っています。私はFindメソッドをほとんど使いません、Match関数や配列を使って処理したほうが高速かつ確実に動作するからです。WorksheetFunction.Matchで配列を指定した場合の制限についてマクロVBAの高速化・速度対策の具体的手順と検証マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。以下はぜひお読みください。動的2次元配列の次元を入れ替えてシートへ出力(Transpose)動的配列を使い様々な処理をした後にシートへ出力しようとしたとき、縦横が違っている為そのまま出力できません、そもそも、動的配列の要素数をRedimで変更できるのは、最下位の次元のみになります。大量データで処理時間がかかる関数の対処方法(SumIf)大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、そういう場合でも、多くの場合は何らかの対策があるものです、個別のロジックの記述でこれに対応する一つの有効なマクロVBAコ-ドについて解説します。以下の例で解説します。そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型(可変長文字列)についての基礎知識が必要です。大量VlookupをVBAで高速に処理する方法について大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。シートに関数を入れる場合は、以下を参照してください。以下の表で検証します。Withステートメントの実行速度と注意点マクロVBAにおいてWithステートメントはとても重要です、可読性(読みやすさ、理解しやすさ)、実行速度においては、その役割はとても大きいものになります。本記事では、Withステートメントを使うか使わないかでのマクロ実行速度の差に焦点を絞って検証します。Next)|VBA入門8.セルに文字を入れるとは(Range,Value)|VBA入門9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説10.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技ホームマクロVBAマクロVBA技術解説WorksheetFunction.Matchで配列を指定した場合の制限についてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。GASスプレッドシート入門スプレッドシート応用GAS入門GAS応用ホームマクロVBAマクロVBA技術解説WorksheetFunction.Matchで配列を指定した場合の制限についてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。