Массивы в VBA. Урок №14

Массив – это набор элементов одинакового типа, имеющих общее имя и отличающихся друг от друга индексом. Массивы могут быть одномерными (линейными), многомерными и динамическими. Используя массив, можно работать с некоторым набором однотипных данных как с единым целым.

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

Содержание урока:

1. Одномерные массивы в VBA
• Объявление массивов в VBA
• Оператор Option Base в VBA
• Пример одномерного массива в VBA
2. Многомерные массивы в VBA
• Объявление многомерных массивов
• Пример многомерного массива в VBA
3. Определение границ массива
4. Статические массивы
5. Динамические массивы
6. Очистка и удаление массивов

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

Например, у нас есть список дней недели. Это может быть массив, потому что элементы этого списка (дни недели) имеют одинаковый тип. И вот если мы дадим этому списку имя, например Week, то мы и получим массив.

Каждый день недели в этом списке – это элемент массива. При этом, каждый день недели имеет свой собственный порядковый номер (так называемый индекс).

0. Sunday;
1. Monday;
2. Tuesday;
3. Wednesday;
4. Thursday;
5. Friday;
6. Saturday.

Обратите внимание, что нумерация в массивах начинается не с единицы, а с нуля.

И вот, если мы хотим обратиться к какому-то из этих дней недели, например к дню, который у нас находится под индексом 1 (Monday), то нам достаточно указать имя массива и индекс 1 – Week(1).

Объявление массивов в VBA

Sub Массивы()
Dim Week
    Week = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    MsgBox Week(1)
End Sub

Результат: Monday

Массив из примера выше, который представляет собой простой список данных, называется одномерным. Заключенное в скобки число, стоящее рядом с именем массива, называется индексом элемента данного массива.

Как я уже сказал, в языке VBA, нумерация элементов в массиве начинается с нуля. Но можно изменить начальный индекс элементов массива с помощью оператора Option Base. Этот оператор может иметь два значения – 0, или 1. Но поскольку по умолчанию нумерация элементов массива начинается с нуля, то Option Base 0 не указывают.

Оператор Option Base указывают в модуле только один раз, и он обязательно должен предшествовать объявлению массивов.

Оператор Option Base в VBA

Option Base 1
Sub Массивы()
Dim Week
    Week = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    MsgBox Week(1)
End Sub

Результат: Sunday

Пример одномерного массива в VBA

Для закрепления темы, давайте рассмотрим ещё один пример работы с одномерными массивами в VBA Excel.

Например, у нас есть книга Excel с одним листом, который мы переименуем в Arrays (что в переводе с английского означает массивы) и заполним первый столбец этого листа днями недели:

1. Sunday;
2. Monday;
3. Tuesday;
4. Wednesday;
5. Thursday;
6. Friday;
7. Saturday.

Рисунок 1. Одномерные массивы в VBA Excel

Этот список является одномерным массивом и обратиться в элементам этого массива мы можем следующим образом:

Sub oneDimArrs()

    Dim oneDimArr(1 To 7) As String
    
    oneDimArr(1) = Worksheets("Arrays").Cells(1, 1)
    oneDimArr(2) = Worksheets("Arrays").Cells(2, 1)
    oneDimArr(3) = Worksheets("Arrays").Cells(3, 1)
    oneDimArr(4) = Worksheets("Arrays").Cells(4, 1)
    oneDimArr(5) = Worksheets("Arrays").Cells(5, 1)
    oneDimArr(6) = Worksheets("Arrays").Cells(6, 1)
    oneDimArr(7) = Worksheets("Arrays").Cells(7, 1)
    
    MsgBox oneDimArr(2)
End Sub

Результат: Monday

Многомерные массивы в VBA

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

Например, Dim Matrix (1 to 3, 1 to 3) – это матрица размером 3х3 элементов, состоящая из трёх строк и трёх столбцов.

В Visual Basic For Applications допускается объявлять массивы с максимум 60 размерностями. Но, как показывает практика, в программировании чаще всего используются одно- и двухмерные массивы.

Объявление многомерных массивов

Sub MultidimensionalArrays()

'Массив двумерный
Dim arr1(3, 6) As Integer
'Массив трехмерный
Dim arr2(1 To 6, 1 To 8, 1 To 5) As String
'Массив четырехмерный
Dim arr3(9, 9, 9, 9) As Date

End Sub

Пример многомерного массива в VBA

Давайте рассмотрим пример двумерного массива в VBA Excel. Создадим книгу Excel и переименуем Лист1 в Arrays. Далее заполним первые три строки и колонки буквами английского алфавита: A, B, C, D, E, F, G, H, I.

Рисунок 2. Многомерные массивы в VBA Excel

Теперь давайте обратимся к элементам данного массива и выведем какой-нибудь из них на экран в сообщении.

Sub MultidimensionalArrays()

Dim twoDimArr(1 To 3, 1 To 3) As String 'объявляем двумерный массив, группа значений в котором идентифицируется по двум признакам Rows - Columns (Строки - Колонки)

twoDimArr(1, 1) = Worksheets("Arrays").Cells(1, 1)
twoDimArr(1, 2) = Worksheets("Arrays").Cells(1, 2)
twoDimArr(1, 3) = Worksheets("Arrays").Cells(1, 3)
twoDimArr(2, 1) = Worksheets("Arrays").Cells(2, 1)
twoDimArr(2, 2) = Worksheets("Arrays").Cells(2, 2)
twoDimArr(2, 3) = Worksheets("Arrays").Cells(2, 3)
twoDimArr(3, 1) = Worksheets("Arrays").Cells(3, 1)
twoDimArr(3, 2) = Worksheets("Arrays").Cells(3, 2)
twoDimArr(3, 3) = Worksheets("Arrays").Cells(3, 3)

MsgBox twoDimArr(2, 2)

End Sub

Результат: E

Пример выше является достаточно громоздким и его можно использовать только тогда, когда у нас небольшое количество элементов (до 3-ох, 4-ох). А вот если у нас большое количество элементов, то для сохранения своего времени, лучше написать код, который пройдётся по указанным нами ячейкам и сохранит их значение в массив.

Sub twoDimArrays()

Dim twoDimArr(1 To 3, 1 To 3) As String

Dim cellChecked As Range

For Each cellChecked In Worksheets("Arrays").Range("A1:C3")
    twoDimArr(cellChecked.Row, cellChecked.Column) = cellChecked
Next cellChecked

    MsgBox twoDimArr(2, 2)
    
End Sub

Результат: E

В данном примере, мы с помощью цикла проходимся по ячейкам от A1 до C3 (слева направо) и выводим на экран сообщение в котором хранится значение строки 2, столбца 2.

Согласитесь, такая запись кода значительно проще и занимает меньше места.

Определение границ массива

Иногда, возникает необходимость определить нижнюю и верхнюю границы индекса массива. Для этого в VBA предусмотрены 2 функции: LBound и UBound.

Первая возвращает нижнюю границу индекса, вторая - верхнюю.

Синтаксис

LBound(arrayname, [ dimension ])
UBound(arrayname, [ dimension ])

где,

• arrayname – имя массива. Это обязательный аргумент.
• dimension – размер. Это необязательный аргумент. Используйте 1 для первого измерения, 2 для второго и так далее. Если измерение опущено, предполагается 1.

Для одномерных массивов, dimension указывать не обязательно. А вот если массив окажется многомерным, и вы не укажете dimension, то возникнет ошибка.

Поэтому, если вы точно не знаете, с каким массивом имеете дело, но необходимо узнать его первую размерность, то лучше использовать вариант UBound(arrTemp,1), а не UBound(arrTemp). Вариант UBound(arrTemp) вызовет ошибку, если массив окажется многомерным.

Пример определения границ массива

Sub testArrs()
Dim arrStart(0 To 3) 'одномерный массив
MsgBox LBound(arrStart) 'определяем нижнюю границу индекса (результат будет 0)
MsgBox UBound(arrStart) 'определяем верхнюю границу индекса (результат будет 3)
End Sub

Если вы ошибётесь с указанием правильного индекса массива, то возникнет ошибка периода исполнения Run-time error '9': Subscript out of range. Эта же ошибка возникнет, если вы в функции LBound / UBound укажете несуществующую размерность массива (например, 3 для двумерного массива).

Статические массивы в VBA

Язык VBA поддерживает два типа массивов – статические и динамические.

Статическими называют такие массивы, размерность которых была указана непосредственно при их объявлении. В этом случае размер массива остается фиксированным на протяжении всего выполнения программы. Для объявления массивов используются те же операторы Dim и Public, что и при объявлении обычной переменной, причем с теми же правилами определения их области действия. Объявить массив фиксированного размера можно, указав в скобках после его имени конкретные значения для каждого его измерения.

'Одномерный массив состоящий из 31 элемента
Dim Arr1(30) As Integer
'Двумерный массив из 11 строк по 21 элементу в каждой строке
Dim Arr2(10, 20) As Integer

В этом примере первый оператор объявляет одномерный массив Arr1 из 31 целого числа. Поскольку мы уже знаем, что в языке VBA по умолчанию нумерация индексов начинается с нуля, то элементы массива Arr1 будут пронумерованы от 0 до 30. Второй оператор объявляет двухмерный массив Arr2, который будет содержать 11 строк по 21 целому числу, причем индексы элементов этого массива будут изменяться следующим образом: первый – от 0 до 10, а второй – от 0 до 20.

Динамические массивы в VBA

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

При объявлении динамического массива его размерность (индексы) в скобках после имени массива не указывается.

Dim Arr1 () As String

Объявлять динамический массив целесообразно в следующих случаях:

1. когда требуемый размер массива неизвестен до момента выполнения программы;
2. если заранее известно, что в ходе выполнения программы размер массива будет меняться;
3. если при выполнении программы после завершения использования массива необходимо освободить занимаемую им память.

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

Dim Arr1()
Dim Arr2()
Dim Arr3()
…
ReDim Arr1(5) '1 измерение, 6 элементов
ReDim Arr2(5 To 11) '1 измерение, 7 элементов
ReDim Arr3(1 To 6, 1 To 10) '2 измерения, 60 элементов

При обычном переопределении массива его содержимое полностью уничтожается. Если же при переопределении размерности массива необходимо сохранить уже существующие в нем данные, укажите необязательное ключевое словo Preserve.

Dim Arr1() As Single
…
ReDim Arr1(1 To 10, 1 To 20)
…
ReDim Preserve Arr1(1 To 10, 1 To 50)

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

Следует отметить, что при использовании ключевого слова Preserve имеет место несколько ограничений:

1. При сокращении размеров массива данные, оказавшиеся за пределами его новых размеров, будут потеряны.
2. Размерность массива (количество измерений) нельзя изменить.
3. В многомерных массивах можно менять размер только последнего измерения.

Очистка и удаление массивов при помощи Erase

Используя оператор Erase можно выполнить очистку статических массивов или удаление динамических.

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

Или же может понадобиться очистить все значения в статическом массиве, устанавливая числовые значения на 0, а строковые - на пустые строки.

Вот для этого и существует оператор Erase.

Erase My_Array

Чтобы вновь использовать удаленный динамический массив, его следует переопределить с помощью оператора ReDim.

Для статических массивов действия оператора Erase зависит от конкретного типа элементов данного массива.

1. Для любого числового типа оператор Erase записывает во все элементы массива значения нуль.
2. Для строкового типа с переменной длиной строки оператор Erase помещает во все элементы массива строку нулевой длинны, а строки фиксированной длинны полностью заполняются символами пробелов.
3. Для типа Variant оператор Erase устанавливает все элементы массива на Empty.
4. Для типа Object оператор Erase устанавливает элементы массива на Nothing.
5. Для пользовательского типа оператор Erase устанавливает числовые типы на 0, строковые – на строки нулевой длины, Variant – на Empty, a Object – на Nothing.