VBA-Урок 13.1. Использование массивов (Arrays)

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

Почему используются массивы?

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

Вторая причина, чтобы использовать массивы - это их скорость. Занимает значительно меньше времени на считывание данных из массивов, чем из таблиц (созданных из ячеек) на рабочем листе Excel.

Итак, здесь есть пример, который покажет это нагдяно...

На первом рабочем листе ("DS") есть набор данных: 5000 строк на 3 столбца:

На втором листе вы найдете итоговую таблицу, которая учитывает все ответы "Да" ("YES") в разрезе лет и клиентов:

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

Без использования массивов, это бы заняло Excel 131.44 секунды , чтобы выполнить эту процедуру:

Но для первого сохранения данных (с рабочего листа "DS") в массив и затем, осуществление таких же расчетов (используя массивы вместо набора данных с рабочего листа "DS"), займет лишь 1.74 секунды :

Если мы решим оптимизировать нашу процедуру, сохраняя в массиве только данные, содержащие ответы "YES" (которых примерно 3/4 от всех данных), то это бы заняло лишь 1.02 секунды :

Это хороший пример того, как использование массивов позволяет выполнять процедуру быстрее в 128 раз. Результат нашей оптимизации был бы даже лучшим, если бы мы работали с многими наборами данных одновременно.

Мы вернемся к деталям нашего примера в конце урока.

Декларирование массивов

Ниже есть несколько примеров декларирования массивов (если первые два не ясны вам, перечитайте это ):

'Пример декларирования одномерного массива
Dim array1 (4)

'Пример декларирования двумерного массива
Dim array2 (6, 1)

'Пример декларирования динамического массива 
Dim array3()

Если вы не имеете возможности ввести фиксированные значения при декларировании массивов (так как они зависят от, например, размера набора данных), оставьте скобки пустыми.

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

Хранение данных в массиве

Давайте попробуем сохранить некоторые данные в массиве:

Мы хотим сохранить 11х1 значений в этом случае, поэтому нам необходимо создать одномерный массив:

'Декларирование
Dim array_example(10)

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

Каждый элемент в массиве сейчас получит свое значение:

'Сохранение значений в массив
array_example(0) = Range("A2")
array_example(1) = Range("A3")
array_example(2) = Range("A4")
array_example(3) = Range("A5")
array_example(4) = Range("A6")
array_example(5) = Range("A7")
array_example(6) = Range("A8")
array_example(7) = Range("A9")
array_example(8) = Range("A10")
array_example(9) = Range("A11")
array_example(10) = Range("A12")

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

Далее пример, в котором мы используем array_example (8):

Sub example ()
     'Декларирование
     Dim array_example (10)
     
     'Сохранение значений в массив
     array_example(0) = Range("A2")
     array_example(1) = Range("A3")
     array_example(2) = Range("A4")
     array_example(3) = Range("A5")
     array_example(4) = Range("A6")
     array_example(5) = Range("A7")
     array_example(6) = Range("A8")
     array_example(7) = Range("A9")
     array_example(8) = Range("A10")
     array_example(9) = Range("A11")
     array_example(10) = Range("A12")
     
     'Тест 1
     MsgBox array_example(8) '=> возвращает: 02.04.2016
    
     'Изменение одного из значений
     array_example (8) = Year (array_example (8))
     
     'Тест 2
     MsgBox array_example (8) '=> возвращает : 2016
End Sub

Цикл For был бы лучшим вариантом, чтобы сохранить массив быстрее:

'Декларирование
Dim array_example (10)

'Сохранение значений в массив
For i = 0 To 10
     array_example(i) = Range("A" & i + 2)
Next

Статьи по теме: