- Краткое описание массива VBA
- Быстрые примеры массивов VBA
- Преимущества массива? - Скорость!
- Создать / объявить массив (Dim)
- Установить значения массива
- Назначить диапазон для массива
- 2D / многомерные массивы
- Примеры многомерных массивов
- Длина / размер массива
- Цикл через массив
- Другие задачи с массивами
- Использование массивов в Access VBA
В VBA Множество - это единственная переменная, которая может содержать несколько значений. Думайте о массиве как о диапазоне ячеек: каждая ячейка может хранить значение. Массивы могут быть одномерными (представьте себе один столбец), двумерными (представьте себе несколько строк и столбцов) или многомерными. Доступ к значениям массива можно получить по их положению (номеру индекса) в массиве.
Краткое описание массива VBA
Массивы
ОписаниеКод VBAСоздаватьDim arr (от 1 до 3) как вариантarr (1) = «один»
arr (2) = «два»
arr (3) = «три»Создать из ExcelDim arr (от 1 до 3) как вариант
Тусклая ячейка как диапазон, я как целое число
i = LBound (обр.)
Для каждой ячейки в диапазоне («A1: A3»)
я = я + 1
arr (i) = значение ячейки
Следующая ячейкаПрочитать всеТусклый я как долго
Для i = LBound (arr) To UBound (arr)
MsgBox arr (i)
Далее яСтеретьСтереть обр.Массив в строкуDim sName As String
sName = Присоединиться (arr, «:»)Увеличить размерReDim Preserve arr (от 0 до 100)Установить значениеarr (1) = 22
Быстрые примеры массивов VBA
Давайте рассмотрим полный пример, прежде чем углубляться в детали:
| 12345678910 | Sub ArrayExample ()Dim strNames (от 1 до 4) как StringstrNames (1) = "Шелли"strNames (2) = "Стив"strNames (3) = "Нима"strNames (4) = "Хосе"msgbox strNames (3)Конец подписки |
Здесь мы создали одномерный массив строк: strNames с размером четыре (может содержать четыре значения) и присвоили четыре значения. Наконец, мы отображаем 3-е значение в окне сообщения.
В этом случае преимущество использования массива невелико: требуется только одно объявление переменной вместо четырех.
Однако давайте посмотрим на пример, который покажет истинную мощь массива:
| 12345678 | Sub ArrayExample2 ()Dim strNames (от 1 до 60000) как строкаТусклый я до тех пор, покаДля i = от 1 до 60000strNames (i) = Ячейки (i, 1). ЗначениеДалее яКонец подписки |
Здесь мы создали массив, который может содержать 60 000 значений, и быстро заполнили массив из столбца A рабочего листа.
Преимущества массива? - Скорость!
Вы можете подумать о массивах, похожих на листы Excel:
- Каждая ячейка (или элемент в массиве) может содержать собственное значение
- Доступ к каждой ячейке (или элементу в массиве) можно получить по ее позиции в строке и столбце.
- Рабочий лист Ex. ячейки (1,4) .value = «Строка 1, столбец 4»
- Массив Ex. arrVar (1,4) = «Строка 1, столбец 4»
Так зачем возиться с массивами? Почему бы просто не читать и записывать значения непосредственно в ячейки Excel? Одно слово: Скорость!
Чтение / запись в ячейки Excel - медленный процесс. Работать с массивами стало намного быстрее!
Создать / объявить массив (Dim)
Примечание. Массивы могут иметь несколько «измерений». Для простоты мы начнем с работы только с одномерными массивами. Позже в этом руководстве мы познакомим вас с многомерными массивами.
Статический массив
Статические массивы - это массивы, размер которых не меняется. Наоборот, Динамические массивы можно изменить размер. Заявлены они несколько иначе. Во-первых, давайте посмотрим на статические массивы.
Примечание. Если размер вашего массива не изменится, используйте статический массив.
Объявление статической переменной массива очень похоже на объявление обычной переменной, за исключением того, что вы должны определить размер массива. Есть несколько разных способов установить размер массива.
Вы можете явно объявить начальную и конечную позиции массива:
| 123456789101112 | Sub StaticArray1 ()'Создает массив с позициями 1,2,3,4Dim arrDemo1 (от 1 до 4) как строка'Создает массив с позициями 4,5,6,7Dim arrDemo2 (от 4 до 7) до тех пор, пока'Создает массив с позициями 0,1,2,3Dim arrDemo3 (от 0 до 3) до тех пор, покаКонец подписки |
Или вы можете ввести только размер массива:
| 123456 | Sub StaticArray2 ()'Создает массив с позициями 0,1,2,3Dim arrDemo1 (3) как строкаКонец подписки |
Важный! Обратите внимание, что по умолчанию массивы начинаются с позиции 0. Итак Тусклый arrDemo1 (3) создает массив с позициями 0,1,2,3.
Вы можете объявить Вариант База 1 в верхней части вашего модуля, чтобы вместо этого массив начинался с позиции 1:
| 12345678 | Вариант База 1Sub StaticArray3 ()'Создает массив с позициями 1,2,3Dim arrDemo1 (3) как строкаКонец подписки |
Однако я считаю, что гораздо проще (и менее запутанно) просто явно объявить начальную и конечную позиции массивов.
Устали искать примеры кода VBA? Попробуйте AutoMacro!
Динамический массив
Динамические массивы - это массивы, размер которых можно изменять (или размер которых не нужно определять).
Есть два способа объявить динамический массив.
Варианты массивов
Первый способ объявить динамический массив - задать для массива тип Вариант.
| 1 | Dim arrVar () как вариант |
С Вариант Арраy, вам не нужно определять размер массива. Размер изменится автоматически. Просто помните, что массив начинается с позиции 0 (если вы не добавите Option Base 1 в верхнюю часть вашего модуля)
| 12345678910111213 | Sub VariantArray ()Dim arrVar () как вариант'Определить значения (Размер = 0,1,2,3)arrVar = массив (1, 2, 3, 4)'Изменить значения (Размер = 0,1,2,3,4)arrVar = Array («1a», «2a», «3a», «4a», «5a»)'Выходная позиция 4 ("5a")MsgBox arrVar (4)Конец подписки |
Невариантные динамические массивы
В случае безвариантных массивов вы должны определить размер массива перед присвоением значений массиву. Однако процесс создания массива немного отличается:
| 1234567 | Sub DynamicArray1 ()Dim arrDemo1 () как строка'Изменяет размер массива с позициями 1,2,3,4ReDim arrDemo1 (от 1 до 4)Конец подписки |
Сначала вы объявляете массив, аналогичный статическому массиву, за исключением того, что вы опускаете размер массива:
| 1 | Dim arrDemo1 () как строка |
Теперь, когда вы хотите установить размер массива, вы используете ReDim команда для изменения размера массива:
| 12 | 'Изменяет размер массива с позициями 1,2,3,4ReDim arrDemo1 (от 1 до 4) |
ReDim изменяет размер массива. Прочтите ниже, чтобы узнать о различиях между ReDim и ReDim Preserve.
ReDim против ReDim Preserve
Когда вы используете ReDim вы очищаете все существующие значения из массива. Вместо этого вы можете использовать ReDim Preserve чтобы сохранить значения массива:
| 12 | 'Изменяет размер массива с позициями 1,2,3,4 (с сохранением существующих значений)ReDim Сохранить arrDemo1 (от 1 до 4) |
Объявление массивов упрощенным
Вы можете быть ошеломлены, прочитав все вышесказанное. Чтобы не усложнять задачу, до конца статьи мы в основном будем работать со статическими массивами.
Установить значения массива
Установить значения массива очень просто.
В статическом массиве вы должны определять каждую позицию массива по очереди:
| 12345678 | Sub ArrayExample ()Dim strNames (от 1 до 4) как строкаstrNames (1) = "Шелли"strNames (2) = "Стив"strNames (3) = "Нима"strNames (4) = "Хосе"Конец подписки |
С помощью Variant Array вы можете определить весь массив одной строкой (практично только для небольших массивов):
| 123456 | Sub ArrayExample_1Line ()Dim strNames () как вариантstrNames = Array («Шелли», «Стив», «Нима», «Хосе»)Конец подписки |
Если вы попытаетесь определить значение для несуществующего расположения массива, вы получите сообщение об ошибке «Нижний индекс вне диапазона»:
| 1 | strNames (5) = "Шеннон" |
В разделе «Назначить диапазон для массива» ниже мы покажем вам, как использовать цикл для быстрого назначения большого количества значений массивам.
Получить значение массива
Таким же образом можно получить значения массива. В приведенном ниже примере мы запишем значения массива в ячейки:
| 1234 | Диапазон ("A1"). Значение = strNames (1)Диапазон ("A2"). Значение = strNames (2)Диапазон ("A3"). Значение = strNames (3)Диапазон ("A4"). Значение = strNames (4) |
Программирование на VBA | Генератор кода действительно работает для вас!
Назначить диапазон для массива
Чтобы назначить диапазон массиву, вы можете использовать цикл:
| 12345678 | Под RangeToArray ()Dim strNames (от 1 до 60000) как строкаТусклый я до тех пор, покаДля i = от 1 до 60000strNames (i) = Ячейки (i, 1). ЗначениеДалее яКонец подписки |
Это будет проходить через ячейки A1: A60000, присваивая значения ячеек массиву.
Выходной массив в диапазон
Или вы можете использовать цикл для присвоения массива диапазону:
| 123 | Для i = от 1 до 60000Ячейки (i, 1) .Value = strNames (i)Далее я |
Это будет делать наоборот: присвоить значения массива ячейкам A1: A60000
2D / многомерные массивы
До сих пор мы работали исключительно с одномерными (1D) массивами. Однако массивы могут иметь до 32 измерений.
Представьте себе одномерный массив как одну строку или столбец ячеек Excel, двумерный массив как весь рабочий лист Excel с несколькими строками и столбцами, а трехмерный массив похож на всю книгу, содержащую несколько листов, каждый из которых содержит несколько строк и столбцов (вы также можно представить себе трехмерный массив как кубик Рубика).
Примеры многомерных массивов
Теперь продемонстрируем примеры работы с массивами разной размерности.
Программирование на VBA | Генератор кода действительно работает для вас!
Пример 1D массива
Эта процедура объединяет предыдущие примеры массивов в одну процедуру, демонстрируя, как вы можете использовать массивы на практике.
| 1234567891011121314 | ПодмассивEx_1d ()Dim strNames (от 1 до 60000) как строкаТусклый я до тех пор, пока'Присвоить значения массивуДля i = от 1 до 60000strNames (i) = Ячейки (i, 1). ЗначениеДалее я'Выводить значения массива в диапазонДля i = от 1 до 60000Таблицы ("Вывод"). Ячейки (i, 1) .Value = strNames (i)Далее яКонец подписки |
Пример 2D-массива
Эта процедура содержит пример 2D-массива:
| 123456789101112131415161718 | ПодмассивEx_2d ()Dim strNames (от 1 до 60000, от 1 до 10) как строкаDim i до тех пор, j до тех пор'Присвоить значения массивуДля i = от 1 до 60000Для j = от 1 до 10strNames (i, j) = Ячейки (i, j). ЗначениеСледующий jДалее я'Выводить значения массива в диапазонДля i = от 1 до 60000Для j = от 1 до 10Таблицы ("Вывод"). Ячейки (i, j) .Value = strNames (i, j)Следующий jДалее яКонец подписки |
Пример 3D-массива
Эта процедура содержит пример 3D-массива для работы с несколькими листами:
| 12345678910111213141516171819202122 | ПодмассивEx_3d ()Dim strNames (от 1 до 60000, от 1 до 10, от 1 до 3) как строкаDim i до тех пор, j до тех пор, k до тех пор'Присвоить значения массивуДля k = от 1 до 3Для i = от 1 до 60000Для j = от 1 до 10strNames (i, j, k) = Sheets ("Sheet" & k) .Cells (i, j) .ValueСледующий jДалее яСледующие k'Выводить значения массива в диапазонДля k = от 1 до 3Для i = от 1 до 60000Для j = от 1 до 10Sheets ("Вывод" & k) .Cells (i, j) .Value = strNames (i, j, k)Следующий jДалее яСледующие kКонец подписки |
Длина / размер массива
До сих пор мы познакомили вас с различными типами массивов и научили, как объявлять массивы и получать / устанавливать значения массивов. Далее мы остановимся на других необходимых темах для работы с массивами.
Программирование на VBA | Генератор кода действительно работает для вас!
Функции UBound и LBound
Первым шагом к получению длины / размера массива является использование функций UBound и LBound для получения верхней и нижней границ массива:
| 123456 | Sub UBoundLBound ()Dim strNames (от 1 до 4) как строкаMsgBox UBound (strNames)MsgBox LBound (strNames)Конец подписки |
Вычитание двух (и прибавление 1) даст вам длину:
| 1 | GetArrLength = UBound (strNames) - LBound (strNames) + 1 |
Функция длины массива
Вот функция для получения длины одномерного массива:
| 1234567 | Открытая функция GetArrLength (как вариант) до тех пор, покаЕсли IsEmpty (a), тоGetArrLength = 0ЕщеGetArrLength = UBound (a) - LBound (a) + 1Конец, еслиКонечная функция |
Нужно рассчитать размер 2D-массива? Ознакомьтесь с нашим руководством: Расчет размера массива.
Цикл через массив
Есть два способа перебрать массив. Первый проходит по целым числам, соответствующим числовым позициям массива. Если вы знаете размер массива, вы можете указать его напрямую:
| 12345678910111213 | ПодмассивExample_Loop1 ()Dim strNames (от 1 до 4) как строкаТусклый я до тех пор, покаstrNames (1) = "Шелли"strNames (2) = "Стив"strNames (3) = "Нима"strNames (4) = "Хосе"Для i = от 1 до 4MsgBox strNames (i)Далее яКонец подписки |
Однако, если вы не знаете размер массива (если массив динамический), вы можете использовать функции LBound и UBound из предыдущего раздела:
| 12345678910111213 | ПодмассивExample_Loop2 ()Dim strNames (от 1 до 4) как строкаТусклый я до тех пор, покаstrNames (1) = "Шелли"strNames (2) = "Стив"strNames (3) = "Нима"strNames (4) = "Хосе"Для i = LBound (strNames) To UBound (strNames)MsgBox strNames (i)Далее яКонец подписки |
Для каждого цикла массива
Второй метод - это цикл для каждого. Это проходит по каждому элементу в массиве:
| 12345678910111213 | ПодмассивExample_Loop3 ()Dim strNames (от 1 до 4) как строкаТусклый элементstrNames (1) = "Шелли"strNames (2) = "Стив"strNames (3) = "Нима"strNames (4) = "Хосе"Для каждого элемента в strNamesЭлемент MsgBoxСледующий пунктКонец подписки |
Цикл For Each Array Loop будет работать с многомерными массивами в дополнение к одномерным массивам.
Программирование на VBA | Генератор кода действительно работает для вас!
Цикл через 2D-массив
Вы также можете использовать функции UBound и LBound для циклического просмотра многомерного массива. В этом примере мы переберем 2D-массив. Обратите внимание, что функции UBound и LBound позволяют указать, в каком измерении массива найти верхнюю и нижнюю границы (1 для первого измерения, 2 для второго измерения).
| 1234567891011121314151617181920 | ПодмассивExample_Loop4 ()Dim strNames (от 1 до 4, от 1 до 2) как строкаDim i до тех пор, j до тех порstrNames (1, 1) = "Шелли"strNames (2, 1) = "Стив"strNames (3, 1) = "Нима"strNames (4, 1) = "Хосе"strNames (1, 2) = "Шелби"strNames (2, 2) = "Стивен"strNames (3, 2) = "Немо"strNames (4, 2) = "Джесси"Для j = LBound (strNames, 2) To UBound (strNames, 2)Для i = LBound (strNames, 1) To UBound (strNames, 1)MsgBox strNames (i, j)Далее яСледующий jКонец подписки |
Другие задачи с массивами
Очистить массив
Чтобы очистить весь массив, используйте оператор Erase:
| 1 | Стереть strNames |
Пример использования:
| 12345678910 | Sub ArrayExample ()Dim strNames (от 1 до 4) как строкаstrNames (1) = "Шелли"strNames (2) = "Стив"strNames (3) = "Нима"strNames (4) = "Хосе"Стереть strNamesКонец подписки |
В качестве альтернативы вы также можете ReDim массива, чтобы изменить его размер, очистив часть массива:
| 1 | ReDim strNames (от 1 до 2) |
Это изменяет размер массива до размера 2, удаляя позиции 3 и 4.
Подсчет массива
Вы можете подсчитать количество позиций в каждом измерении массива с помощью функций UBound и LBound (обсуждаемых выше).
Вы также можете подсчитать количество введенных элементов (или элементов, отвечающих определенным критериям), просматривая массив в цикле.
В этом примере выполняется цикл по массиву объектов и подсчитывается количество непустых строк, найденных в массиве:
| 123456789101112131415 | Sub ArrayLoopandCount ()Dim strNames (от 1 до 4) как строкаDim i As Long, n As LongstrNames (1) = "Шелли"strNames (2) = "Стив"Для i = LBound (strNames) To UBound (strNames)Если strNames (i) "" Тогдап = п + 1Конец, еслиДалее яMsgBox n & "обнаружены непустые значения".Конец подписки |
Программирование на VBA | Генератор кода действительно работает для вас!
Удалить дубликаты
В какой-то момент вы можете захотеть удалить дубликаты из массива. К сожалению, в VBA нет встроенной функции для этого. Однако мы написали функцию для удаления дубликатов из массива (это слишком долго, чтобы включать в это руководство, но перейдите по ссылке, чтобы узнать больше).
Фильтр
Функция фильтра VBA позволяет фильтровать массив. Это достигается путем создания нового массива только с отфильтрованными значениями. Ниже приведен краткий пример, но обязательно прочтите статью, чтобы найти больше примеров для различных нужд.
| 1234567891011121314 | Sub Filter_Match ()'Определить массивDim strNames как вариантstrNames = Array («Стив Смит», «Шеннон Смит», «Райан Джонсон»)'Массив фильтровDim strSubNames как вариантstrSubNames = Фильтр (strNames, «Смит»)'Подсчет фильтрованного массиваMsgBox «Найдено» и UBound (strSubNames) - LBound (strSubNames) + 1 & «имена».Конец подписки |
Функция IsArray
Вы можете проверить, является ли переменная массивом, используя функцию IsArray:
| 123456789101112 | Подложка IsArrayEx ()'Создает массив с позициями 1,2,3Dim arrDemo1 (3) как строка'Создает обычную строковую переменнуюDim str As StringMsgBox IsArray (arrDemo1)MsgBox IsArray (str)Конец подписки |
Присоединиться к массиву
Вы можете быстро «объединить» весь массив с помощью функции объединения:
| 123456789101112 | Sub Array_Join ()Dim strNames (от 1 до 4) как строкаDim joinNames как строкаstrNames (1) = "Шелли"strNames (2) = "Стив"strNames (3) = "Нима"strNames (4) = "Хосе"joinNames = Присоединиться (strNames, ",")MsgBox joinNamesКонец подписки |
Программирование на VBA | Генератор кода действительно работает для вас!
Разделить строку на массив
Функция разделения VBA разбивает строку текста на массив, содержащий значения из исходной строки. Давайте посмотрим на пример:
| 123456789 | Подмассив_сплит ()Тусклые имена () в виде строкиDim connectedNames As StringjoinNames = "Шелли, Стив, Нема, Хосе"Names = Split (connectedNames, ",")Имена MsgBox (1)Конец подписки |
Здесь мы разбиваем эту строку текста «Шелли, Стив, Нема, Хосе» на массив (размер 4), используя разделитель запятой (, »).
Const Массив
Массив не мочь быть объявленным как константа в VBA. Однако вы можете обойти это, создав функцию для использования в качестве массива:
| 123456789 | 'Определить ConstantArrayФункция ConstantArray ()ConstantArray = Массив (4, 12, 21, 100, 5)Конечная функция'Получить значение ConstantArraySub RetrieveValues ()MsgBox ConstantArray (3)Конец подписки |
Копировать массив
Нет встроенного способа скопировать массив с помощью VBA. Вместо этого вам нужно будет использовать цикл для присвоения значений из одного массива другому.
| 12345678910111213141516171819 | Sub CopyArray ()Dim Arr1 (от 1 до 100) до тех пор, покаDim Arr2 (от 1 до 100) по всей длинеТусклый я до тех пор, пока'Создать массив1Для i = от 1 до 100Arr1 (i) = iДалее я'CopyArray1 в Array2Для i = от 1 до 100Arr2 (i) = Arr1 (i)Далее яMsgBox Arr2 (74)Конец подписки |
Транспонировать
Нет встроенной функции VBA, позволяющей транспонировать массив. Однако мы написали функцию для транспонирования 2D-массива. Прочтите статью, чтобы узнать больше.
Программирование на VBA | Генератор кода действительно работает для вас!
Массив возврата функции
Распространенный вопрос разработчиков VBA - как создать функцию, возвращающую массив. Я думаю, что большинство трудностей решаются с помощью Variant Arrays. Мы написали статью на тему: Массив, возвращаемый функцией VBA.
Использование массивов в Access VBA
Большинство приведенных выше примеров массивов работают в Access VBA точно так же, как и в Excel VBA. Одно из основных различий заключается в том, что когда вы хотите заполнить массив с помощью данных Access, вам нужно будет выполнить цикл через объект RecordSet, а не объект Range.
| 1234567891011121314151617181920212223 | Sub RangeToArrayAccess ()При ошибке Возобновить ДалееDim strNames () как строкаТусклый я до тех пор, покаDim iCount, покаDim dbs как база данныхDim rst As RecordsetУстановите dbs = CurrentDbУстановите rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)С первой.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 в iCount)Для i = 1 в iCountstrNames (i) = rst.Fields ("ClientName").MoveNextДалее яКонец ссначала закрытьУстановить rst = ничегоУстановить dbs = NothingКонец подписки |
| Учебники по массивам | |
|---|---|
| Мега-руководство по массиву | да |
| Получить размер массива | |
| Очистить массив | |
| Массив фильтров | |
| Транспонировать массив | |
| Массив возврата функции | |
| Удалить дубликаты | |
