Массивы в VBA Excel: одномерные, многомерные и динамические. Объявление и использование массивов. Операторы Public, Dim и ReDim. Функции Array, LBound, UBound.
Одномерные массивы
Объявление одномерных (линейных) статических массивов в VBA Excel:
1 2 |
Public Massiv1(9) As Integer Dim Massiv2(1 To 9) As String |
В первом случае публичный массив содержит 10 элементов от 0 до 9 (нижний индекс по умолчанию — 0, верхний индекс — 9), а во втором случае локальный массив содержит 9 элементов от 1 до 9.
По умолчанию VBA Excel считает в массивах нижним индексом нуль, но, при желании, можно сделать нижним индексом по умолчанию единицу, добавив в самом начале модуля объявление «Option Base 1».
Многомерные массивы
Объявление многомерных статических массивов в VBA Excel аналогично объявлению одномерных массивов, но с добавлением размерностей дополнительных измерений через запятую:
1 2 3 4 5 6 |
'Массив двухмерный Public Massiv1(3, 6) As Integer 'Массив трехмерный Dim Massiv2(1 To 6, 1 To 8, 1 To 5) As String 'Массив четырехмерный Dim Massiv3(9, 9, 9, 9) As Date |
Третий массив состоит из 10000 элементов — 10×10×10×10.
Динамические массивы
Динамические массивы в VBA Excel, в отличие от статических, объявляются без указания размерности:
1 2 |
Public Massiv1() As Integer Dim Massiv2() As String |
Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:
1 2 3 4 |
Public Massiv1() As Integer Dim Massiv2() As String ReDim Massiv1(1 To 20) ReDim Massiv2(3, 5, 4) |
При переопределении размерности массива вместо верхнего индекса можно использовать переменную:
1 2 3 |
Dim Massiv1() as Variant, x As Integer x = 20 ReDim Massiv1(1 To x) |
Переопределять размерность динамических массивов в процессе работы программы можно неоднократно, как по количеству измерений, так и по количеству элементов в измерении.
С помощью оператора ReDim невозможно изменить обычный массив, объявленный с заранее заданной размерностью. Попытка переопределить размерность такого массива вызовет ошибку компиляции с сообщением: Array already dimensioned (Массив уже измерен).
При переопределении размерности динамических массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:
1 2 3 4 5 |
Dim Massiv1() As String ----- операторы ----- ReDim Massiv1(5, 2, 3) ----- операторы ----- ReDim Preserve Massiv1(5, 2, 7) |
Переопределить с оператором Preserve можно только последнюю размерность динамического массива. Это недоработка разработчиков, которая сохранилась и в VBA Excel 2016. Без оператора Preserve можно переопределить все размерности.
Максимальный размер
Размер массива – это произведение длин всех его измерений. Он представляет собой общее количество элементов, содержащихся в данный момент в массиве.
По информации с сайта разработчиков, максимальный размер массивов зависит от операционной системы и доступного объема памяти. Использование массивов, размер которых превышает объем доступной оперативной памяти компьютера, приводит к снижению скорости, поскольку системе необходимо выполнять запись данных и чтение с диска.
Использование массивов
Приведу два примера, где не обойтись без массивов.
1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.
2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel. Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива.
Функции Array, LBound, UBound
Функция Array
Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).
1 2 3 4 5 6 |
Sub Test1() Dim a() As Variant a = Array("text", 25, "solo", 35.62, "stop") MsgBox a(0) & vbNewLine & a(1) & vbNewLine _ & a(2) & vbNewLine & a(3) & vbNewLine & a(4) End Sub |
Скопируйте код в модуль VBA Excel и запустите его на выполнение. Информационное сообщение MsgBox покажет значения массива, извлеченные по индексу.
Функция LBound
Функция LBound возвращает значение типа Long, равное наименьшему (нижнему) доступному индексу в указанном измерении массива.
Синтаксис:
LBound (arrayname[, dimension])
- arrayname — это имя переменной массива, является обязательным аргументом;
- dimension — это номер измерения массива, необязательный аргумент, по умолчанию принимает значение 1.
Наименьший индекс по-умолчанию может быть равен 0 или 1 в зависимости от настроек оператора Option Base. Нижняя граница архива, полученного с помощью функции Array, всегда равна 0.
При объявлении переменных массивов или переопределении их размерности наименьшие индексы могут быть любыми целыми числами, в том числе отрицательными.
Функция UBound
Функция UBound возвращает значение типа Long, равное наибольшему (верхнему) доступному индексу в указанном измерении массива.
Синтаксис:
UBound( arrayname[, dimension])
- arrayname — это имя переменной массива, является обязательным аргументом;
- dimension — это номер измерения массива, необязательный аргумент, по умолчанию принимает значение 1.
Функция UBound используется вместе с функцией LBound для определения размера массива.
1 2 3 4 5 |
Sub Test2() Dim a(-2 To 53) As String MsgBox "Наименьший индекс = " & LBound(a) & _ vbNewLine & "Наибольший индекс = " & UBound(a) End Sub |
Скопируйте код в модуль VBA Excel и запустите его на выполнение. Информационное сообщение MsgBox покажет значения наименьшего и наибольшего индекса переменной массива a.
Обход массива циклом
Обход одномерного массива циклом For… Next, в котором для определения границ массива используются функции UBound и LBound:
1 2 3 4 5 6 7 |
Sub Test3() Dim a() As Variant, i As Long a = Array("text", 25, "solo", 35.62, "stop") For i = LBound(a) To UBound(a) Debug.Print "a(" & i & ") = " & a(i) Next End Sub |
Результат работы цикла вы увидите в окне Immediate.
Очистка (обнуление) массивов
Первый способ
Очистить любой массив, статический или динамический, без использования цикла можно с помощью оператора Erase. Термин «обнуление» можно применить только к массиву числового типа.
1 2 3 4 5 6 7 8 |
Dim Massiv1(4, 3) As String, Massiv2() As Variant ----- операторы ----- 'переопределяем динамический массив ReDim Massiv2(2, 5, 3) ----- операторы ----- 'очищаем массивы Erase Massiv1 Erase Massiv2 |
Обратите внимание, что оба массива при таком способе очистки будут возвращены в исходное состояние, которое они имели сразу после объявления:
- статический Massiv1 сохранит размерность (4, 3);
- динамический Massiv2 не сохранит размерность ().
Второй способ
Динамический массив можно очистить (обнулить) без использования цикла с помощью оператора ReDim. Просто переопределите его с той же размерностью.
1 2 3 4 5 6 7 |
Dim Massiv() As Double ----- операторы ----- 'переопределяем массив ReDim Massiv(5, 6, 8) ----- операторы ----- 'очищаем массив ReDim Massiv(5, 6, 8) |
Это все очень интересно и здорово, но хотелось бы еще узнать о заполнении массивов.
Например один цикл поможет заполнить одномерный массив. Два цикла двумерный. Но это в случае когда значения рассчитываются.
А если у меня есть двумерный массив с данными (постоянными) и мне надо его «ввести» в программу — как быть? Самый простой пример — календарь. В строках первый элемент месяц, второй элемент 1 число, третий элемент день недели, четвертое — последний день месяца, пятый — день недели последнего числа месяца.
То есть на входе у меня 12 строк, 5 столбцов, массив 5х12.
Теперь мне надо его заполнить, 60 значений. Цикл не подойдет, надо каждое вносить отдельно.
Чисто теоретически можно представить массив как одномерный массив строк, каждый элемент которого состоит из одномерного массива столбцов. А вот как это присвоение оформить в ВБА?
Артем, вы можете использовать присвоение массиву значений из диапазона ячеек одной строкой кода.
Увы, не могу. Дело в том, что :
1. Я пишу не в Екселе, а в Автокаде — там нет ячеек.
2. Мои данные это по сути константы, мне придется их заносить в ячейки (если делать нечто подобное в Екселе), а затем считывать программно. При этом с листа (из ячеек) их надо прятать, что бы случайно никто не изменил. Поэтому данные необходимо вводить в теле программы в массив и далее с ними работать. В то же время необходимо сделать так, что бы при желании было легко добавить еще одну строку с данными.
Артем, я правильно понял, что вы вносите все данные в массив вручную и хотите сделать автоматическое добавление «пустых строк» в массив под новые данные?
Нет, вручную вношу до 50 «строк», вручную добавляю (при необходимости, а необходимость возникает не часто) по 1-15 строк.
В общем на одном форуме подсказали несколько вариантов. Один из наиболее подходящих (для меня) оказался массив массивов. То есть создается массив, каждый элемент которого это тоже массив, содержащий наименование и далее параметры.
При необходимости просто добавляю элемент массива (строку) с наименованием и параметрами и все.
А вот как правильно обратится к каждому элементу массива и как работать с массивом массивов — вот это хорошо бы у вас в статье отобразить.
Артем, посмотрите еще вариант создания массива одномерных массивов с помощью пользовательских типов данных. Там есть и как заполнить, и как обратиться к каждому элементу.
А как узнать размерность массива?
Иван, размерность массива (количество измерений) вы можете узнать с помощью пользовательской функции KolichestvoIzmereniy.
to Артем:
если массив в автокаде надо заполнять какими-то постоянными данными, то почему бы не хранить эти данные в отдельном файле (напр. txt или dbf) и считывать данные оттуда.
Msgbox не переваривает «;» когда ввожу & он пилит её на & amp и никак не учитывает, но стоит добавить в конец «;» чтобы получилось & всю строку выделяет красным!
Привет, Павел!
Это защита WordPress заменила амперсанд
&
на его html-код. Исправил.…
А как с помощью Array инициализировать, например двумерный массив.
В одном пособии увидел такой код.
Но VBA выводит ошибку
Андрей, у меня ваш способ записи двумерного массива в переменную работает:
Нумерация в измерениях массива начинается с единицы.
Евгений, есть такой вопрос. Сломал голову.
В коде есть строка, которая при разных условиях наполняется разными данными из ячеек сначала одного столбца, затем другого и т.д. следующем кодом:
Бывают случаи, что данных нет и строка остается пустой. Далее идет такой код :
arrNew = Split(str, "|")
, т.е. массив заполняется подстроками строки «str»Так вот, после наполнения массива пустой строкой,
Ubound(arrNew) = -1
, аLbound(arrNew) = 0
.Я не понимаю, как такое возможно, ведь по сути массив пустой?
Привет, Владимир!
Функция Split всегда возвращает массив. Массив
arrNew(0 to -1)
— это и есть пустой массив, без элементов. Если было быarrNew(0 to 0)
— это уже был бы массив с одним элементом.Добрый день, Евгений.
У меня были такие подозрения, но нигде не мог получить этому подтверждения. В справке по Split есть указание о возвращении пустого массива, но ничего не сказано, про его размерность. В Ubound также нет на это ссылок. Сам я — глубокий любитель в VBA. Поэтому разбираюсь со всеми задачами с помощью Уокенбаха и Интернета. Спасибо за разъяснения.
как и какую указать переменную для массива, если его размерность задается пользователем
машина ругается:
Constant expression required
P.S.
оператора ReDim — не решает проблему
Array already dimensioned
Добрый вечер!
У вас чередуются символы «c» в переменной «qc», то кириллицей, то латиницей. Проверяйте — ReDim работает:
всем и всегда указывал на кириллицу и сам же лохонулся …
подскажите еще один вопросик
Как вывести на Лист RND-массив в размере qr*qc ?
Нужно заполнить массив случайными числами и вставить на лист:
спасибо
Как вытащить массив из ComboBox? Имеется большой список организаций и хочу сделать автопоиск по ComboBox.
Но для этого нужно считать все данные в обычный массив. Вылетает ошибка: Выход за пределы диапазона ИЛИ несоответствие типов.
Добрый день, Владимир!
А
ComboBoxSpisokKontragent
— это имя листа или элемента управленияComboBox
?Это сам ComboBox. Список этот зависимый, поэтому не могу использовать диапазон с листа. Нужно именно из Бокса вытащить массив. Вот код заполнения Бокса
Сейчас всё работает, но очень большой список, нужен автопоиск.
Попробуйте так:
Будет создан двухмерный массив: первое измерение — строки, второе — столбец. Ваш список: Spisok(1, 1), Spisok(2, 1), Spisok(3, 1) и т.д.
Спасибо. Как я сказал, список зависимый и формируется из разных диапазонов по выбору OptionBatton. Поэтому завел глобальную переменную для сохранения пути диапазона и всё заработало!
Здравствуйте.
В начале статьи есть фраза:
"Вместо верхнего индекса можно использовать переменную".
Не могли бы Вы привести примерчик, а то у меня что-то не выходит. Заранее спасибо!
Добрый день, Сергей!
Спасибо за скорый ответ.
Но по контексту привлекший меня фразы, я подумал, что можно с помощью переменной задать верхнюю границу в Dim, т.е. для статического массива. Если такое невозможно, то, наверно, стоит какой-то поправить эту фразу, дабы не вводить в заблуждение таких неофитов, как я. Спасибо.
Спасибо, Сергей, за важное уточнение. Перенес эту информацию в параграф "Динамические массивы".
Массивы. Нумерация элементов.
С одномерным массивом всё понятно …
1-ый, 2-ой, 3-ий … N-ый элемент
как найти 12-ый элемент в многомерном массиве
напр. mass(7, 9) (массив начинается на А1)
нумерация слева — направо или сверху вниз ?
Первое измерение двумерного массива — строка, второе — столбец.
В диапазоне нумерация ячеек идет слева-направо построчно. В массивах такой нумерации нет.
Но посчитать, как в диапазоне, мы можем:
12/9=1 — одна целая строка, остаток — 3, который переносится на вторую строку. Получаем результат: 12-ый элемент находится на пересечении второй строки и третьего столбца, что соответствует элементу массива — mass(2, 3).
спасибо
Помогите, плиз. На присвоении d mas2(i) становится двумерным и выдает ошибку диапазона. Как получить доступ к значению?
Здравствуйте, Евгений!
Массив становится двумерным при присвоении ему значений диапазона в этой строке:
Нумерация строк начинается с единицы, столбец один — единица.
Евгений, здравствуйте!
Подскажите пожалуйста, как можно отсортировать (соединить) два массива с данными (данные могут повторяться в массивах, массивы могут иметь разную длину) так, чтобы был массив со всеми данными, но данные не повторялись. Данные могут быть как числовыми, так и строковыми.
‘ Задаем значения для первого массива
arr1 = Range("B3:B12") '347 349 т527 427 ц467 678 345 u234 235 346
‘Задаем значения для второго массива
arr2 = Range("D3:D12") '370 869 317 н862 590 347 615 ц467 427 345 u234 346
Я себе уже всю голову сломал ((( как получать эти массивы из разных книг и листов я разобрался, а вот как в VBA их собрать в один и убрать дубли не осилил.
Здравствуйте, Алексей!
Это можно сделать с помощью отбора уникальных значений из массивов в коллекцию и последующего копирования полученных данных из коллекции в новый массив:
Данные в примере копируются в одномерный массив, если нужен — двумерный, можно переопределить размерность нового массива так:
Заполняем его:
Евгений! Спасибо Вам большое за помощь!
Обсуждение закрыто.