Перейти к содержимому

VBA Excel. Массивы (одномерные, многомерные, динамические)

Массивы в VBA Excel: одномерные, многомерные и динамические. Объявление и использование массивов. Операторы Public, Dim и ReDim. Функции Array, LBound, UBound.

Массивы - это множества однотипных элементов, имеющих одно имя и отличающиеся друг от друга индексами. Они могут быть одномерными (линейными), многомерными и динамическими. Массивы в VBA Excel, как и другие переменные, объявляются с помощью операторов Dim и Public. Для изменения размерности массивов используется оператор ReDim.

  1. Одномерные массивы
  2. Многомерные массивы
  3. Динамические массивы
  4. Использование массивов
  5. Функции Array, LBound, UBound

Одномерные массивы

Объявление одномерных (линейных) массивов в VBA Excel:

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 аналогично объявлению одномерных массивов, но с добавлением размерностей дополнительных измерений через запятую:

'Массив двухмерный
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 объявляются без указания размерности:

Public Massiv1() As Integer
Dim Massiv2() As String

Такие массивы используются, когда заранее неизвестна размерность, которая определяется в процессе выполнения программы. Когда нужная размерность массива становится известна, она в VBA Excel переопределяется с помощью оператора ReDim:

Public Massiv1() As Integer
Dim Massiv2() As String
ReDim Massiv1(1 To 20)
ReDim Massiv2(3, 5, 4)

Переопределять размерность массивов в процессе работы программы можно неоднократно, причем не только динамических, но и массивов с заранее объявленной размерностью, которую можно изменять как по количеству измерений, так и по количеству элементов в измерении.

При переопределении размерности массивов в VBA Excel теряются значения их элементов. Чтобы сохранить значения, используйте оператор Preserve:

Dim Massiv1(1 To 9, 1 To 9) As String
ReDim Preserve Massiv1(1 To 25, 1 To 35)

Однако, если размерность массива в VBA Excel уменьшить, то значения, естественно, сохранятся не все.

Использование массивов

Приведу два примера, где не обойтись без массивов.

1. Как известно, функция Split возвращает одномерный массив подстрок, извлеченных из первоначальной строки с разделителями. Эти данные присваиваются заранее объявленному строковому (As String) одномерному динамическому массиву. Размерность устанавливается автоматически в зависимости от количества подстрок.

2. Данные в массивах обрабатываются значительно быстрее, чем в ячейках рабочего листа. Построчную обработку информации в таблице Excel можно наблюдать визуально по мерцаниям экрана, если его обновление (Application.ScreenUpdating) не отключено. Чтобы ускорить работу кода, можно значения из диапазона ячеек предварительно загрузить в динамический массив с помощью оператора присваивания (=). Размерность массива установится автоматически. После обработки данных в массиве кодом VBA полученные результаты выгружаются обратно на рабочий лист Excel. Обратите внимание, что загрузить значения в диапазон ячеек рабочего листа через оператор присваивания (=) можно только из двумерного массива.

Функции Array, LBound, UBound

Функция Array

Функция Array возвращает массив элементов типа Variant из первоначального списка элементов, перечисленных через запятую. Нумерация элементов в массиве начинается с нуля. Обратиться к элементу массива можно, указав в скобках его номер (индекс).

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 для определения размера массива.

Sub Test2()
Dim a(-2 To 53) As String
MsgBox "Наименьший индекс = " & LBound(a) & _
vbNewLine & "Наибольший индекс = " & UBound(a)
End Sub

Скопируйте код в модуль VBA Excel и запустите его на выполнение. Информационное сообщение MsgBox покажет значения наименьшего и наибольшего индекса переменной массива a.

VBA Excel. Массивы (одномерные, многомерные, динамические): 6 комментариев

  1. Артем

    Это все очень интересно и здорово, но хотелось бы еще узнать о заполнении массивов.
    Например один цикл поможет заполнить одномерный массив. Два цикла двумерный. Но это в случае когда значения рассчитываются.
    А если у меня есть двумерный массив с данными (постоянными) и мне надо его "ввести" в программу - как быть? Самый простой пример - календарь. В строках первый элемент месяц, второй элемент 1 число, третий элемент день недели, четвертое - последний день месяца, пятый - день недели последнего числа месяца.
    То есть на входе у меня 12 строк, 5 столбцов, массив 5х12.
    Теперь мне надо его заполнить, 60 значений. Цикл не подойдет, надо каждое вносить отдельно.
    Чисто теоретически можно представить массив как одномерный массив строк, каждый элемент которого состоит из одномерного массива столбцов. А вот как это присвоение оформить в ВБА?

  2. Артем

    Увы, не могу. Дело в том, что :
    1. Я пишу не в Екселе, а в Автокаде - там нет ячеек.
    2. Мои данные это по сути константы, мне придется их заносить в ячейки (если делать нечто подобное в Екселе), а затем считывать программно. При этом с листа (из ячеек) их надо прятать, что бы случайно никто не изменил. Поэтому данные необходимо вводить в теле программы в массив и далее с ними работать. В то же время необходимо сделать так, что бы при желании было легко добавить еще одну строку с данными.

  3. Евгений (автор статьи)

    Артем, я правильно понял, что вы вносите все данные в массив вручную и хотите сделать автоматическое добавление «пустых строк» в массив под новые данные?

  4. Артем

    Нет, вручную вношу до 50 "строк", вручную добавляю (при необходимости, а необходимость возникает не часто) по 1-15 строк.
    В общем на одном форуме подсказали несколько вариантов. Один из наиболее подходящих (для меня) оказался массив массивов. То есть создается массив, каждый элемент которого это тоже массив, содержащий наименование и далее параметры.
    При необходимости просто добавляю элемент массива (строку) с наименованием и параметрами и все.
    А вот как правильно обратится к каждому элементу массива и как работать с массивом массивов - вот это хорошо бы у вас в статье отобразить.

  5. Евгений (автор статьи)

    Артем, посмотрите еще вариант создания массива одномерных массивов с помощью пользовательских типов данных. Там есть и как заполнить, и как обратиться к каждому элементу.

Добавить комментарий

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора.