Волшебный метод Match⁠⁠

В VBA Excel есть недодокументированный, но очень мощный и полезный метод Application.Match(). Есть метод-близнец WorksheetFunction.Match(). Работают они похожим образом, но возвращают разные результаты.

В VBA-коде лучше использовать именно Application.Match().

С помощью этого метода можно:

• искать элементы (один или сразу несколько) в одномерных диапазонах и массивах;

• сравнивать массивы и диапазоны на предмет наличия одинаковых элементов;

• осуществлять точный и неточный поиск.

Скорость работы Application.Match() значительно выше, чем у циклов, перебирающих элементы массивов или, тем более, ячейки листов Excel.

Отдельно необходимо отметить, что метод этот довольно известный, но, как правило, не используемый на полную мощность. Так, многим неизвестно - и в официальной справке этого нет - что можно искать не одно значение, а массив значений. Также многие продолжают использовать неудобный вариант WorksheetFunction.Match() вместо удобного Application.Match().

С помощью Application.Match() можно, например, организовать, эффективный поиск множества подразделов на листе за одну операцию при обработке таблиц. Это позволяет сильно экономить по времени поиска и по количеству строчек кода. Также довольно просто можно применить этот метод для операций пересечения и сравнения множеств.

Синтаксис:

Application.Match(<что ищем>, <где ищем>, <как ищем>)

<Что ищем> может быть:

• одно значение - переменная или константа, например «aaa», 123 или ValueToFind, причем различных типов - строка, число, дата, логическое.

  • текстовые значения могут содержать подстановочные знаки * (0+ любых символов),? (один любой символ). Их можно экранировать тильдой ~, если нужно найти именно * или ?

• одномерный массив Array(«aaa», 123)

• диапазон листа Excel (строка или столбец) Range("A1:A10"), Range("A1:F1")

<Где ищем> может быть:

• одномерный массив, содержащий значения различных типов, например, Array(«aaa», 123)

• диапазон листа Excel (строка или столбец) Range("A1:A10"), Range("A1:F1")

<Как ищем>:

• 0 - точно. Массив <Где ищем> может быть неупорядоченным (неотсортированным)

• -1 - поиск наименьшего значения. Массив <Где ищем> должен быть отсортирован по убыванию

• 1 - поиск наибольшего значения. Массив <Где ищем> должен быть отсортирован по возрастанию

Application.Match() возвращает:

• если <что ищем> – одно значение, то возвращается одно значение типа Double (индекс найденного значения в массиве, начиная с 1) или значение типа Error (если значение не найдено);

• если <что ищем> – массив из N элементов, то возвращается массив, содержащий N значений типа Double (индекс найденного значения в массиве, начиная с 1) или значений типа Error (если значение не найдено).

Особенности:

• если значение не найдено, возвращается значение ошибки #N/A [Error 2042] (метод WorksheetFunction.Match() в этом случае генерирует исключение)

• поиск текста производится всегда регистронезависимо, т.е. прописные и строчные буквы А и а считаются одинаковыми, и не зависит от установки Option Compare { Binary | Text }

• при успешном поиске Application.Match() возвращает для каждого искомого значения только первый индекс найденного элемента в <где ищем>. Т.е. если <что ищем> = 2,2 а в <где ищем> находится 2,1,2,2, то результатом будет массив 1,1 - обе двойки найдется в первом индексе.

• Индексы нумеруются с 1 независимо от установки Option Base { 0 | 1 }.

Примеры применения Application.Match()

1. Ищем несколько текстов, в т.ч. по маске, в массиве:

Данный код ищет строки «qqq» (три q или Q подряд) и «a?b» (строка из трех символов, начинающаяся на a или A, заканчивающаяся на b или B с любым символом посередине) в массиве arr, содержащем строки «AAA», «A5A», «Abb».

В результате работы кода в окне Immediate будет напечатано:

Этот результат означает, что строка "qqq" не найдена в массиве arr, а "a?b" соответствует третьему элементу "Abb".

2. Ищем значения из столбца A в столбце C:

Этот код ищет значения из ячеек колонки A листа Excel в колонке C листа Excel. Результат выводится в цикле с предварительной проверкой, найден ли очередной элемент или нет. Эта проверка производится с помощью функции IsNumeric(), которая возвращает Истину в том случае, если аргумент - число. В том случае, если значение не найдено, в результирующем массиве будет находиться значение ошибки (Error), и IsNumeric() вернет Ложь. Также это можно проверить с помощью функции IsError(), которая возвращает Истину, если аргумент является значением ошибки.