Поиск в массиве в vba excel
Обновлено: 21.11.2024
VBA ArrayList — гораздо лучшая альтернатива встроенной коллекции VBA. Он содержит гораздо более богатые функции, такие как сортировка, преобразование в массив, удаление всех элементов и т. д.
Ознакомьтесь с кратким руководством, чтобы узнать, что делает ArrayList. В оставшейся части этого поста приводятся примеры использования ArrayList.
Краткое руководство по VBA ArrayList
| Задача | Метод | Параметры | Примеры | Доступ к элементу | Элемент | индекс — длинное целое | value = list.Item(0) value = list.Item (3) |
|---|---|---|---|
| Доступ к элементу, добавленному последним | Элемент | index - длинное целое | value = list .Item(list.Count - 1) |
| Доступ к элементу, добавленному первым | Item | index - long integer | value = list.Item(0) |
| Доступ ко всем элементам (для каждого) | Н/Д | N /A | Тусклый элемент как вариант Для каждого элемента Во фруктах Элемент Debug.Print Следующий элемент |
| Доступ ко всем элементам (For) | Item | index - long integer | Dim i As Long For i = 0 To list.Count - 1 Debug.Print list.item(i) Далее |
| Добавить элемент | Добавить | объект или значение | list.Add "Apple" list.Add "Pear" |
| Copy Array Список в другой ArrayList | Клонировать | Нет | Затемнить список2 как объект Set list2 = list.Clone | Копировать в массив | ToArray | Нет | Затемнить arr как вариант arr = list.ToArray |
| Копировать в диапазон (строку) | ToArray | Нет | Sheet1.Range( "A1" ).Resize(1 , list.Count).Value = list.ToArray |
| Копировать в диапазон (столбец) | ToArray | Нет | Sheet1.Range("A3").Resize(list.Count, 1).Value = WorksheetFunction.Transpose(list.ToArray) |
| Создать | CreateObject | "System.Collections.ArrayList" | Затемнить список как объект Set list = CreateObject("System.Collections.ArrayList" ) |
| Объявить | Н/Д | Н/Д | Затемнить список как объект |
| Найти — проверить, существует ли элемент | Содержит | элемент для поиска | list.Contains("Apple") |
| Поиск позиции элемента в список массивов | IndexOf | 1. Предмет для поиска. 2. Позиция для начала поиска. | Затемнить индекс As Long ' Искать с 0 position index = fruit.IndexOf("Pear", 0) |
| Получить количество элементов | Количество | Нет | totalElements = list.Count |
| Вставить | 1. Индекс - позиция для вставки. 2 Value - объект или значение для вставки. | list.Insert 0, "Peach" ' Первый list.Insert 1, "Banana" ' Второй список. Insert list.Count, "Orange" ' Last | |
| Удалить все элементы | Очистить | Нет | list.Clear |
| Удалить элемент в позиции | RemoveAt | Индекс — позиция, в которой элемент is | list.RemoveAt 0 |
| Удалить элемент по имени | Удалить | Item - элемент для удалить из списка ArrayList | list.Remove "Apple" |
| Удалить диапазон элементов | RemoveRange<тд >1. Индекс - стартовая позиция. 2. Count - количество элементов для удаления. | list.RemoveRange 1,3 | |
| Обратить список | Обратить | Нет | list.Reverse |
| Сортировать по возрастанию | Сортировать | Нет | list.Sort |
Описание
Загрузить исходный код
Объявление и создание списка массивов VBA
Как и все внешние библиотеки, мы можем создать ArrayList, используя раннее и позднее связывание.
Поздняя привязка
Мы используем CreateObject для создания ArrayList с использованием поздней привязки:
Недостаток позднего связывания заключается в том, что у нас нет доступа к Intellisense. Преимущество в том, что его лучше использовать при распространении приложения VBA пользователю.
Раннее связывание
Обновление от 12 ноября 2019 г. Intellisense в настоящее время не работает для ArrayList.
Раннее связывание позволяет использовать Intellisense, чтобы увидеть, что доступно для использования. Мы должны сначала добавить библиотеку типов в качестве ссылки, а затем выбрать ее из списка ссылок. Для этого мы можем использовать следующие шаги:
Теперь вы можете использовать следующий код для объявления ArrayList с использованием раннего связывания:
Ошибка автоматизации VBA ArrayList
Вы можете столкнуться с ошибкой выполнения VB "-2146232576 Automation Error" при попытке заставить ArrayList работать. Или иногда ваш код работал долгое время и вдруг появляется эта ошибка.
Добавление элементов в список массивов VBA
Добавление элементов в ArrayList очень похоже на то, как мы добавляем их в коллекцию. Мы используем метод Добавить:
Чтение ArrayList
Мы читаем ArrayList аналогично коллекции VBA, за исключением того, что мы читаем от нуля до Count-1, а не от единицы до Count.
Примечание. Мы будем использовать эту подпрограмму PrintToImmediateWindow в следующих примерах, чтобы показать содержимое массива после различных операций.
Мы можем использовать цикл For Each с VBA ArrayList так же, как мы используем его с коллекцией:
Вы можете скачать все примеры кода вверху этого поста.
Сортировка
Сортировка отсортирует список VBA ArrayList в порядке возрастания.
Для сортировки по убыванию просто используйте функцию "Обратить" после сортировки.
В следующем коде показан пример сортировки в порядке возрастания и убывания:
Клонирование списка массивов VBA
Мы можем создать копию ArrayList с помощью метода Clone. Это создает новую копию ArrayList.
Это не то же самое, что присваивание переменной, когда обе переменные указывают на один и тот же ArrayList, например
Мы используем Clone следующим образом:
Копирование из списка VBA ArrayList в массив
Мы можем скопировать из ArrayList в массив в одну строку, используя метод ToArray:
Вы можете скачать все примеры кода вверху этого поста.
Запись напрямую в диапазон
Одним из самых больших преимуществ ArrayList является то, что мы можем записывать содержимое непосредственно в диапазон.
Приведенный ниже код записывает содержимое как в строку, так и в столбец:
Массив в список массивов VBA (1D)
Как мы видели, существует встроенная функция ToArray, которая копирует из списка ArrayList в массив.
Если мы хотим скопировать из массива в ArrayList, нам нужно создать собственную функцию, которую я сделал ниже. Поскольку мы читаем элементы по одному, это может быть немного медленнее, если у нас много данных:
Вы можете использовать это так:
Удалить все элементы из ArrayList
Мы можем удалить все элементы из ArrayList с помощью функции Clear:
Вы можете скачать все примеры кода вверху этого поста.
Что дальше?
Бесплатный учебник по VBA Если вы новичок в VBA или хотите отточить свои навыки работы с VBA, почему бы не попробовать The Ultimate VBA Tutorial.
Соответствующее обучение: получите полный доступ к обучающим веб-семинарам Excel VBA и ко всем учебным пособиям.
(ПРИМЕЧАНИЕ. Планируете создать приложение VBA или управлять им? Узнайте, как создать 10 приложений Excel VBA с нуля.)
Если ваш массив содержит менее 100 000 элементов, однократный цикл очень эффективен.
Какой именно метод вы используете, зависит от следующих вопросов:
1) Является ли массив одномерным или многомерным .
2) Вы хотите провести точное или частичное совпадение.
3) Нужно ли вам знать позицию элемента, если он существует.
4) Сколько поисковых запросов вам нужно сделать, возможно, стоит отсортировать массив.
5) Как часто меняются данные, возможно, вам придется продолжать пересортировку.
Одномерный массив — точное совпадение
Если вы используете Excel VBA, вы можете использовать функцию ПОИСКПОЗ в Excel.
Вы можете использовать функцию ФИЛЬТР, чтобы узнать, существует ли элемент в одномерном массиве.
Эта функция возвращает массив любых элементов, содержащих заданную текстовую строку.
Эта функция принимает строку массив, текстовая строка и возвращает одномерный массив, содержащий все элементы, соответствующие строке поиска.
Этот метод не различает полные совпадения и совпадения подстроки.
Этот метод не работает с числовыми массивами, поскольку все числа должны быть сначала неявно преобразованы в текст
Вы можете использовать функцию INSTR после преобразования массива в конкатенацию строк.
Этот метод также использует функцию JOIN для возврата текстовой строки, содержащей элементы массива (String).
Можно пройтись по массиву, используя цикл For-Each или цикл For-Next.
Одномерный массив — частичное совпадение
Двумерный массив — частичное совпадение
Эта функция предполагает, что одномерный массив отсчитывается от нуля.
Объект коллекции
Также можно использовать объект "Коллекция", хотя для заполнения коллекции необходимо пройтись по массиву в цикле.
Это может быть хорошим решением, если вам нужно выполнить несколько поисков в одном и том же наборе данных.
Объект словаря
Также можно использовать объект Dictionary, хотя для этого потребуется ссылка на библиотеку Microsoft Scripting Runtime.
Вернуть позицию элемента в массиве с помощью этой функции из библиотеки кода VBA. Эта UDF — отличный инструмент для работы с массивами, содержащими неизвестные элементы.
Многие рекомендуют использовать функцию листа Index для возврата позиции в массиве VBA, но я обнаружил, что производительность этой функции довольно низкая. Хотя она перебирает каждый элемент вашего массива, я предпочитаю эту функцию.
VBA Поиск индекса в массиве
Создавайте мощные макросы с помощью нашего бесплатного комплекта разработчика VBA
Подобные уроки могут быть сложными. Вот почему мы создали наш бесплатный комплект разработчика VBA и нашу большую книгу макросов Excel VBA в дополнение к этому руководству. Возьмите их ниже, и вы сразу же сможете писать мощные макросы.
Перебор элементов в массиве до тех пор, пока не будет найдено совпадение, является важным навыком, но с массивами можно делать гораздо больше. Чтобы по-настоящему научиться работать с массивами, вам потребуется копия нашей подробной памятки VBA по массивам, содержащей более 20 предварительно созданных макросов и десятки советов, призванных облегчить вам работу с массивами.
Как использовать функцию WhereInArray
Пользовательская функция WhereInArray принимает два аргумента:
- arr1 — массив, который вы хотите найти
- vFind – значение, которое нужно найти в массиве.
Эта функция возвращает позицию элемента в вашем массиве. Если функция не может найти элемент в вашем массиве, она возвращает нулевую строку. Это важно отметить, потому что если вы попытаетесь присвоить эту функцию целочисленному или длинному типу данных, а элемент отсутствует в вашем массиве, вы получите сообщение об ошибке.
Давайте рассмотрим несколько примеров. Мы рассмотрим пару хороших демонстраций и одну плохую демонстрацию.
В этом примере выполняется попытка найти строку «кошка» в массиве a . «Кот» хранится в первой позиции индекса (0, 1, 2), поэтому функция возвращает значение 1.
В этом примере мы попытаемся найти слово «мяу» в массиве a . Обратите внимание, что «мяу» отсутствует в массиве. Когда мы ищем его, функция WhereInArray возвращает значение Null.
Приведенный выше пример работает только потому, что переменная i была объявлена как Variant. Если бы он был определен как тип Long или Integer, возникла бы ошибка времени выполнения 94, «Недопустимое использование значения Null».
Вот что я имею в виду. В этой демонстрации мы запустим тот же макрос, но объявим переменную i как целое число.
Как я и обещал, вы получите ошибку во время выполнения.
Почему вариант?
Почему я предпочитаю выводить свою индексную позицию как вариант, а не как длинное или целое число? Я делаю это, чтобы использовать эту функцию двумя разными способами.
Если мне все равно, где находится элемент в массиве, но я хочу знать, находится ли элемент в массиве, я могу проверить наличие Null с помощью функции IsNull, чтобы узнать, находится ли интересующее меня значение где-нибудь в моем массиве. В этом отношении он работает как моя пользовательская функция IsInArray.
Взгляните на этот пример, чтобы понять, что я имею в виду:
Подпишитесь на нашу серию электронных писем VBA Insiders, чтобы получить дополнительные советы, приемы и тактики VBA, используя форму ниже. Присоединившись, поделитесь этой статьей в Twitter и Facebook.
Готовы делать больше с VBA?
Мы собрали гигантский PDF-файл с более чем 300 готовыми макросами и хотим, чтобы вы получили его бесплатно. Введите свой адрес электронной почты ниже, и мы вышлем вам копию вместе с нашим комплектом разработчика VBA, полным советов, приемов и ярлыков VBA.
Прежде чем мы начнем, я хочу сообщить вам, что мы разработали набор шпаргалок по VBA, чтобы вам было проще писать лучшие макросы. Мы включили более 200 советов и 140 примеров макросов, поэтому в них есть все, что вам нужно знать, чтобы стать лучшим программистом VBA.
Можно объявить массив для работы с набором значений одного типа данных. Массив — это отдельная переменная с множеством ячеек для хранения значений, в то время как типичная переменная имеет только одну ячейку для хранения, в которой она может хранить только одно значение. Обращайтесь к массиву в целом, если вы хотите обратиться ко всем содержащимся в нем значениям, или вы можете обратиться к его отдельным элементам.
Например, для хранения ежедневных расходов на каждый день года можно объявить одну переменную массива с 365 элементами вместо объявления 365 переменных. Каждый элемент массива содержит одно значение. Следующий оператор объявляет переменную массива с 365 элементами. По умолчанию массив индексируется, начиная с нуля, поэтому верхняя граница массива равна 364, а не 365.
Чтобы установить значение отдельного элемента, укажите индекс элемента. В следующем примере каждому элементу массива присваивается начальное значение 20.
Изменение нижней границы
Используйте оператор Option Base в верхней части модуля, чтобы изменить индекс первого элемента по умолчанию с 0 на 1. В следующем примере оператор Option Base изменяет индекс для первого элемента, а оператор Dim объявляет переменная массива с 365 элементами.
Вы также можете явно задать нижнюю границу массива с помощью предложения To, как показано в следующем примере.
Сохранение значений Variant в массивах
Существует два способа создания массивов значений Variant. Один из способов — объявить массив данных типа Variant, как показано в следующем примере:
Другой способ — присвоить массив, возвращаемый функцией Array, переменной Variant, как показано в следующем примере.
Вы идентифицируете элементы в массиве значений Variant по индексу, независимо от того, какой метод вы используете для создания массива. Например, к любому из предыдущих примеров можно добавить следующее утверждение.
Использование многомерных массивов
В Visual Basic можно объявлять массивы, содержащие до 60 измерений. Например, следующий оператор объявляет двумерный массив 5 на 10.
Если вы думаете о массиве как о матрице, первый аргумент представляет строки, а второй аргумент представляет столбцы.
Использовать вложенные For. Операторы Next для обработки многомерных массивов. Следующая процедура заполняет двумерный массив значениями Single.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.
Читайте также: