VBA-Урок 13.1. Використання масивів (Arrays)

Масиви (Arrays) дуже часто використовуються в програмуванні, в тому числі і в Excel VBA.

Масив - це, по суті, одна змінна з багатьма комірками для зберігання значень, тоді як типова змінна має лише одну комірку для зберігання, в якій вона може зберігати лише одне значення.

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

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

Чому використовуються масиви?

Уявіть, що ви намагаєтесь написати процедуру, в якій вам буде необхідно зберігати до 500 значень. Якщо ви муситимете створити 500 окремих змінних, то це буде дуже важко. В середині масиву, зберігати та працювати з цими значеннями буде значно легше.

Друга причина, щоби використовувати масиви - це є їхня швидкість. Зчитування даних з масивів займає значно менше часу, ніж з таблиць (створених з комірок) на робочому аркуші Excel.

Приклад використання масивів

Отож, тут є приклад, що покаже наглядну перевагу використання VBA масивів в Excel.

На першому робочому аркуші ("DS") є набір даних: 5000 рядків на 3 стовпця:

На другому аркуші ви знайдете підсумкову таблицю, яка враховує усі відповіді "Так" ("YES") в розрізі років та клієнтів:

В даному випадку, процедура використовуватиме цикл, щоби обробити набір даних і записати кількість відповідей "Так" для кожного року і кожного номера клієнта, а потім введе ці дані до відповідних комірок.

Без використання масивів, це б зайняло для Excel 131.44 секунди, щоби виконати цю процедуру:

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

Якщо ж ми вирішимо оптимізувати нашу процедуру, зберігаючи в масиві лише дані, що містять відповіді "YES" (яких є приблизно 3/4 від усіх даних), то це б зайняло лише 1.02 секунди:

Це є хороший приклад того, як використання масивів дозволяє виконувати процедуру швидше в 128 раз. Результат нашої оптимізації був би навіть кращим, якби ми працювали з багатьма наборами даних одночасно.

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

Декларування масивів

Нижче є кілька прикладів декларування масивів в VBA (якщо перші два приклади не зрозумілі вам, перечитайте це):

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

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

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

Якщо ви немаєте змоги ввести фіксовані значення під час декларування масивів (тому що вони залежать від, наприклад, розміру набору даних), залиште дужки порожніми.

Ви не мусите декларувати тип даних (string, long, тощо), хоча в багатьох випадках це сповільнюватиме виконання вашої процедури.

Зберігання даних в масиві

Давайте спробуємо зберегти деякі дані в масиві:

Ми хочемо зберегти 11х1 значень в цьому випадку, отож нам необхідно створити одновимірний масив:

 'Declaration
 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