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

VBA Excel. Массивы

Массивы в 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.