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

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

По суті, це одна змінна, яка може зберігати кілька значень.

Зміст уроку:

1. Одновимірні масиви в VBA
• Оголошення масивів в VBA
• Оператор Option Base в VBA
• Приклад одновимірного масиву в VBA
2. Багатовимірні масиви в VBA
• Оголошення багатовимірних масивів
• Приклад багатовимірного масиву в VBA
3. Визначення меж масиву
4. Статичні масиви в VBA
5. Динамічні масиви в VBA
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 елементів

При звичайному перевизначенні масиву вміст повністю знищується. Якщо ж при перевизначенні розмірності масиву необхідно зберегти дані, що вже існують у ньому, вкажіть необов'язкове ключове слово 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.