Для того, щоби зберегти більше одного стовпця даних, нам необхідна інша розмірність масиву. Наприклад:
Збереження даних в двовимірний масив:
'Декларування
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)
В попередньому прикладі, останній номер в нашому масиві був 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 записів).
Але якщо вам потрібно створити масив, який має "фіксований" вміст.
Одним з рішень могло би бути прописати значення стрічка за стрічкою:
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 дозволяє нам поділити символьну стрічку на частини та записати отримані значення в масив.
Щоби перетворити стрічку у масив, зробіть наступне:
variable = "IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID"
Використайте VBA функцію 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 є VBA функція Join.
Ця функція збирає значення масиву в стрічку.
MsgBox Join(Array(1, 2, 3, 4, 5), "") '=> повертає : 12345
