WorksheetFunction.Matchで配列を指定した場合の制限と回避策
Excel VBAにおけるWorksheetFunctionオブジェクトは、Excelの強力な関数群をVBAコードから呼び出すための重要なブリッジです。その中でもMatch関数は、特定の値を検索し、その相対的な位置を取得するために頻繁に使用されます。通常、Match関数の第2引数にはセル範囲（Rangeオブジェクト）を指定しますが、メモリ上で完結する配列（Variant型の2次元配列など）を直接指定したいというニーズは、大規模なデータ処理において非常に一般的です。
しかし、VBAのWorksheetFunction.Match関数に配列を渡す際には、プロフェッショナルなエンジニアが必ず知っておくべき「重大な制限」が存在します。本記事では、この仕様の深層を掘り下げ、なぜエラーが発生するのか、そしてどのように実装すべきかについて詳述します。
Match関数と配列の互換性に関する技術的制約
結論から述べますと、WorksheetFunction.Match関数は、VBAのメモリ内に展開された「配列（Array）」を第2引数として直接受け取ることができません。
Excelのワークシート関数は、本来「セル範囲」を対象として設計されています。VBAで`Dim arr As Variant`として生成した配列をMatch関数に渡すと、実行時エラー「1004：WorksheetFunctionクラスのMatchプロパティを取得できません」が発生します。これは、Match関数が内部的にRangeオブジェクト特有のプロパティやメソッド、あるいはExcelの計算エンジンとの連携を期待しているためです。
多くのエンジニアが陥る罠は、`Range.Value`で取得した配列をそのまま関数に放り込んでしまうことです。`Range.Value`はVariant型の2次元配列を返しますが、これはVBAの標準的な配列構造であり、Excelのネイティブなワークシート関数が直接解釈できる「セル範囲」とはメモリ構造が異なります。
なぜ配列指定でエラーが発生するのか
Match関数が期待しているのは、インターフェースとして「Range」オブジェクトです。ワークシート上のデータは、Excelの計算エンジンが参照可能なメモリ空間（またはデータ構造）に紐付いています。一方で、VBAの配列は、VBAランタイムが管理するメモリ領域に存在します。
Match関数が配列を受け取れない理由は以下の通りです。
1. 型の不一致：WorksheetFunctionは、引数としてRangeオブジェクトを要求しますが、配列はRange型ではありません。
2. 計算エンジンの制約：Excelの計算エンジンは、Rangeオブジェクトの背後にある「セル参照」を追跡することで高速なインデックス検索を行っています。純粋なVBA配列にはこの参照情報が含まれていないため、検索処理が拒絶されます。
3. 2次元配列の扱い：VBAの配列は1次元、2次元、多次元を柔軟に扱えますが、Match関数は「1列または1行」の範囲に強く依存しています。配列の次元指定が曖昧な場合、検索ロジックが破綻します。
代替案：Application.Matchの使用と注意点
ここで重要なのが、`WorksheetFunction.Match`ではなく、`Application.Match`を使用するという手法です。
VBAには`WorksheetFunction`オブジェクトと`Application`オブジェクトの両方から関数を呼び出す方法があります。実は、`Application.Match`を使用した場合、配列を渡してもエラーにならず、検索結果（インデックス）を返すことが可能です。これは非常に強力なテクニックですが、ここには「エラーハンドリング」という別の壁が存在します。
`WorksheetFunction.Match`は、見つからない場合に「実行時エラー」を発生させますが、`Application.Match`は、見つからない場合に「エラー値（CVErr(xlErrNA)）」を返します。この違いを理解していないと、プログラムが予期せぬ挙動を示します。
サンプルコード：Application.Matchによる配列検索の実装
以下に、配列を検索対象とした場合の最適な実装例を示します。
Sub SearchInArrayExample()
Dim dataArray As Variant
Dim targetValue As String
Dim result As Variant
' 検索対象となるデータ（通常はシートから読み込むか、動的に生成）
dataArray = Array("Apple", "Banana", "Cherry", "Date")
targetValue = "Cherry"
' Application.Matchを使用して検索
' WorksheetFunctionではなくApplicationを使用するのがポイント
result = Application.Match(targetValue, dataArray, 0)
' 結果の判定
If IsError(result) Then
MsgBox "指定された値は見つかりませんでした。", vbExclamation
Else
MsgBox "値は " & result & " 番目に見つかりました。", vbInformation
End If
End Sub
実務におけるエンジニアへのアドバイス
実務で大量のデータを扱う際、毎回`Application.Match`を呼び出すのは効率的ではありません。特にループ内で何度も検索を行う場合、配列をそのまま検索するよりも、以下の戦略を検討すべきです。
1. 辞書オブジェクト（Scripting.Dictionary）の活用
検索が頻繁に発生する場合、配列をループで回してDictionaryに格納してください。Dictionaryの`Exists`メソッドや`Item`プロパティによる検索は、Match関数よりも圧倒的に高速かつコードが直感的です。
2. データのソート済み活用
もしデータがソートされているのであれば、バイナリサーチ（二分探索）を自前で実装する方が、Excelの関数に依存するよりもメモリ効率が良く、パフォーマンスも安定します。
3. エラーハンドリングの徹底
`Application.Match`を使用する場合、戻り値が数値かエラー値かを必ず`IsError`関数で判定してください。これを怠ると、後続の計算で型の不一致エラーを誘発し、デバッグが困難になります。
4. 2次元配列の平坦化
`Range.Value`で取得した2次元配列（1列分）を検索する場合、`Application.Index`を使用して1次元配列に変換してから`Application.Match`に渡すのが定石です。`Application.Index(arr, 0, 1)`のように記述することで、列を指定して1次元配列として抽出可能です。
まとめ
WorksheetFunction.Matchに配列を直接渡すことは、Excelの設計思想とVBAのメモリ管理の不一致により、成功しません。この制限を回避するためには、`Application.Match`を使用し、戻り値がエラー値である可能性を常に考慮したコーディングを行う必要があります。
しかし、プロのエンジニアとしては、単に関数の仕様を回避するだけでなく、「そもそもMatch関数を使うべきか」という視点も忘れてはいけません。高速な検索が求められるアプリケーションでは、`Scripting.Dictionary`オブジェクトを選択する方が、コードの保守性、可読性、そして実行速度の観点から見て、圧倒的に優れたソリューションとなります。
VBAの特性を深く理解し、適材適所でツールを選択することこそが、堅牢なシステムを構築するための第一歩です。今回の解説が、皆様のVBA開発における技術的知見の一助となれば幸いです。
