VBA-Урок 13.2. Використання масивів (Arrays) (Продовження)

Для того, щоби зберегти більше одного стовпця даних, нам необхідна інша розмірність масиву. Наприклад:

Збереження даних в двовимірний масив:

'Декларування
Dim array_example(10, 2) '11 x 3 "визначений" масив

'Збереження даних в масиві
For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
Next

Далі є кілька прикладів роботи з цими значеннями:

MsgBox array_example(0, 0)  '=> повертає: 03.11.2026
MsgBox array_example(0, 1)  '=> повертає: 24
MsgBox array_example(9, 2)  '=> повертає: NO
MsgBox array_example(10, 2) '=> повертає: YES

Динамічний масив

Давайте уявимо на хвилинку, що нам потрібно оновлювати дані в нашому масиві на регулярній основі, і тому ми не можемо присвоїти фіксовані значення під час декларування...

Щоби дізнатися номер рядка останньої непорожньої комірки, чи іншими словами, останній рядок нашої бази даних, ми використаємо наступну формулу:

last_row = Range("A1").End(xlDown).Row

Excel не приймає змінні в декларації.

Замвсть цього, задекларуємо динамічний масив (використовуючи порожні дужки), потім визначимо його розмір використовуючи Redim:

Dim array_example()
ReDim array_example(last_row - 2, 2)

Використовуючи наступну процедуру, ви можете зберегти усі рядки вашого набору даних (таблиці) в нашому масиві:

Dim array_example()
ReDim array_example(last_row - 2, 2)

Ubound

В попередньому прикладі, останній номер в нашому масиві був last_row - 2:

For i = 0 To last_row - 2

Інший спосіб, щоби визначити останній номер в нашому масиві, міг би бути через використання Ubound:

For i = 0 To UBound(array_example)

Ця функція повертає найбільший номер в масиві для вибраного виміру (перший вимір є по-замовчуванню).

Далі є кілька прикладів, що прояснять це детальніше:

Sub example()
     Dim array_example(10, 2)
     
     MsgBox UBound(array_example) '=> повертає : 10
     MsgBox UBound(array_example, 1) '=> повертає : 10
     MsgBox UBound(array_example, 2) '=> повертає : 2
End Sub

Збереження даних в діапазоні елементів масиву

Є можливим заповнити масив значеннями з діапазону комірок на робочому аркуші навіть без використання циклу:

'Декларування
Dim array_example(10, 2) '11 x 3 "визначений" масив

'Збереження даних в масиві
For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
Next

Попередній код може бути ефективно замінений цим:

'Декларування
Dim array_example()

'Збереження даних в масиві
array_example = Range("A2:C12").Value

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

Якщо ви збережете дані у вашому масиві в цей спосіб, перший номер буде 1, а не 0, що може спричинити непорозуміння...

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

Але цей другий метод є досить корисним, якщо вам потрібно зберегти весь вміст великого набору даних, тому що це швидше, ніж циклом (заощаджує приблизно 0,2 секунд на кожні 15 000 записів).

Масив (Array)

Але якщо вам потрібно створити масив, який має "фіксований" вміст.

Одним з рішень могло би бути прописати значення стрічка за стрічкою:

Dim en(5)

en(0) = "IF"
en(1) = "VLOOKUP"
en(2) = "SUM"
en(3) = "COUNT"
en(4) = "ISNUMBER"
en(5) = "MID"

На щастя, ви можете спростити цей код, використовуючи масив (Array):

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")

Ось демонстрація використання функції Replace (це допоможе вам зрозуміти наступний приклад):

Sub replace_example()
     Dim var_translate As String

     'Текстова стрічка для цього прикладу
     var_translate = "Hello World !"
     
     'Заміна "World" на "you" в текстовій стрічці
     var_translate = Replace(var_translate, "World", "you")

     'Стрічка після заміни
     MsgBox var_translate '=> returns "Hello you !"
End Sub

Тепер, якщо ми хочемо замінити ряд значень іншим набором даних, використання масивів та Array функції буде надзвичайно корисним:

Sub translate() 'Спрощений приклад перекладу формул з англійської на французьку
     Dim var_translate As String

     'Текстова стрічка для цього прикладу
     var_translate = "Formula to translate : SUM(IF(ISNUMBER(A1:E1),A1:E1,0))"
     
     'Два набори значень
     en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
     fr = Array("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
     
     'Заміна "SI" на "IF", "RECHERVEV" на "VLOOKUP" і т.д.
     For i = 0 To UBound(en)
         var_translate = Replace(var_translate, en(i), fr(i))
     Next

     'Стрічка після заміни
     MsgBox var_translate '=> повертає "Formula to translate : SOMME(SI(ESTNUM(A1:E1),A1:E1,0))"
End Sub

Поділ (Split)

Функція Split дозволяє нам перетворити символьну стрічку в масив.

Щоби перетворити стрічку у масив, зробіть наступне:

variable = "IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID"

Використайте функцію Split та зазначте розділювач:

en = Split(variable, "/")

Масив en поверне наступні значення:

MsgBox en(0) '=> повертає : IF
MsgBox en(1) '=> повертає : VLOOKUP
MsgBox en(2) '=> повертає : SUM
MsgBox en(3) '=> повертає : COUNT
MsgBox en(4) '=> повертає : ISNUMBER
MsgBox en(5) '=> повертає : MID

Наступні 3 масиви також повернуть такі ж самі значення:

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
en = Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")
en = Split("IF VLOOKUP SUM COUNT ISNUMBER MID", " ")

Наступний приклад повертає третє значення в стрічці:

MsgBox Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")(2) '=> повертає : SUM

Зворотньою до Split є функція Join.

Ця функція збирає значення масиву в стрічку.

MsgBox Join(Array(1, 2, 3, 4, 5), "") '=> повертає : 12345

Статті по темі:

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