Масиви VBA

У VBA, an Масив - це одна змінна, яка може містити кілька значень. Подумайте про масив як про діапазон комірок: кожна клітинка може зберігати значення. Масиви можуть бути одновимірними (подумайте про один стовпець), двовимірними (думайте про кілька рядків і стовпців) або багатовимірними. До значень масиву можна отримати доступ за їх позицією (індексним номером) у масиві.

Швидкий аркуш масиву VBA

Масиви

ОписКод VBAСтворитиDim arr (1 To 3) As Variant
arr (1) = "один"
arr (2) = “два”
arr (3) = "три"Створення з ExcelDim arr (1 To 3) As Variant
Яскрава клітинка як діапазон, i як ціле число
i = LBound (обр.)
Для кожної клітинки в діапазоні ("A1: A3")
i = i + 1
arr (i) = комірка. значення
Наступна клітинаПрочитайте всі пунктиDim i as Long
Для i = LBound (обр.) До UBound (обр.)
MsgBox arr (i)
Далі iСтертиСтерти обрМасив до рядкаDim sName As String
sName = Приєднатися (arr, “:”)Збільшити розмірReDim Preserve обр. (Від 0 до 100)Встановити значенняarr (1) = 22

Короткі приклади масиву VBA

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

12345678910

Sub ArrayExample ()Затемнити strNames (від 1 до 4) як StringstrNames (1) = "Shelly"strNames (2) = "Стів"strNames (3) = "Neema"strNames (4) = "Хосе"msgbox strNames (3)End Sub

Тут ми створили одновимірний рядковий масив: strNames розміром чотири (може містити чотири значення) і призначили чотири значення. Нарешті ми відображаємо третє значення у вікні повідомлень.

У цьому випадку користь від використання масиву невелика: потрібно лише одне оголошення змінної замість чотирьох.

Однак давайте розглянемо приклад, який покаже справжню силу масиву:

12345678

Sub ArrayExample2 ()Затемнити strNames (від 1 до 60000) як рядокDim i As LongДля i = 1 до 60000strNames (i) = Осередки (i, 1). ЗначенняДалі iEnd Sub

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

Переваги масиву? - Швидкість!

Ви можете подумати про масиви, подібні до робочих аркушів Excel:

• Кожна клітинка (або елемент у масиві) може містити власне значення
• До кожної комірки (або елемента в масиві) можна отримати доступ за допомогою позиції рядка та стовпця.
  • Робочий лист Впр. клітинки (1,4) .value = “Рядок 1, стовпець 4”
  • Масив Ex. arrVar (1,4) = “Рядок 1, стовпець 4”

Тож навіщо турбуватися про масиви? Чому б не просто прочитати та записати значення безпосередньо в клітинки в Excel? Одне слово: Швидкість!

Читання / запис у клітинки Excel - це повільний процес. Робота з масивами набагато швидше!

Створення / оголошення масиву (затемнення)

Примітка. Масиви можуть мати кілька "розмірів". Щоб було простіше, ми почнемо лише з роботи з одновимірними масивами. Далі у посібнику ми познайомимо вас із багатовимірними масивами.

Статичний масив

Статичні масиви це масиви, які не можуть змінювати розмір. І навпаки, Динамічні масиви може змінювати розмір. Вони оголошені дещо інакше. Спочатку розглянемо статичні масиви.

Примітка: Якщо розмір вашого масиву не змінюється, використовуйте статичний масив.

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

Ви можете явно оголосити початкове та кінцеве положення масиву:

123456789101112

Sub StaticArray1 ()'Створює масив з позиціями 1,2,3,4Dim arrDemo1 (1 до 4) як рядок'Створює масив з позиціями 4,5,6,7Dim arrDemo2 (від 4 до 7) як довго'Створює масив з позиціями 0,1,2,3Dim arrDemo3 (0 до 3) як довгоEnd Sub

Або ви можете ввести лише розмір масиву:

123456

Sub StaticArray2 ()'Створює масив з позиціями 0,1,2,3Dim arrDemo1 (3) As StringEnd Sub

Важливо! Зверніть увагу, що за замовчуванням масиви починаються з позиції 0. Отже Dim arrDemo1 (3) створює масив з позиціями 0,1,2,3.

Ви можете заявити База варіантів 1 у верхній частині вашого модуля, щоб масив починався з позиції 1:

12345678

База варіантів 1Sub StaticArray3 ()'Створює масив з позиціями 1,2,3Dim arrDemo1 (3) As StringEnd Sub

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

Втомилися від пошуку прикладів коду VBA? Спробуйте AutoMacro!

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

Динамічні масиви - це масиви, розмір яких можна змінити (або розмір яких не потрібно визначати).

Існує два способи оголошення динамічного масиву.

Варіантні масиви

Перший спосіб оголосити динамічний масив - це встановити тип масиву Варіант.

1	Dim arrVar () як варіант

З Варіант Array, вам не потрібно визначати розмір масиву. Розмір буде автоматично регулюватися. Просто пам’ятайте, що масив починається з позиції 0 (якщо ви не додасте Option Base 1 у верхній частині вашого модуля)

12345678910111213

Додатковий варіант ()Dim arrVar () як варіант'Визначте значення (розмір = 0,1,2,3)arrVar = Масив (1, 2, 3, 4)'Змінити значення (розмір = 0,1,2,3,4)arrVar = Масив ("1a", "2a", "3a", "4a", "5a")'Вихідне положення 4 ("5а")MsgBox arrVar (4)End Sub

Неваріантні динамічні масиви

З неваріантними масивами ви повинні визначити розмір масиву, перш ніж призначати значення масиву. Однак процес створення масиву дещо відрізняється:

1234567

Додатковий DynamicArray1 ()Dim arrDemo1 () як рядок'Змінює розмір масиву з позиціями 1,2,3,4ReDim arrDemo1 (1 - 4)End Sub

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

1	Dim arrDemo1 () як рядок

Тепер, коли ви хочете встановити розмір масиву, ви використовуєте ReDim команда розміру масиву:

12	'Змінює розмір масиву з позиціями 1,2,3,4ReDim arrDemo1 (1 - 4)

ReDim змінює розмір масиву. Про різницю між ReDim та ReDim Preserve читайте нижче.

ReDim проти ReDim Preserve

Коли ви використовуєте ReDim команда видалити всі наявні значення з масиву. Замість цього можна використовувати Заповідник ReDim щоб зберегти значення масиву:

12	'Зміна розміру масиву з позиціями 1,2,3,4 (збереження існуючих значень)Збереження ReDim arrDemo1 (1 - 4)

Оголошення спрощених масивів

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

Встановіть значення масиву

Встановити значення масиву дуже легко.

У статичному масиві ви повинні визначати кожну позицію масиву по одному:

12345678

Sub ArrayExample ()Затемнити strNames (від 1 до 4) як StringstrNames (1) = "Shelly"strNames (2) = "Стів"strNames (3) = "Neema"strNames (4) = "Хосе"End Sub

За допомогою масиву варіантів можна визначити весь масив одним рядком (практично для невеликих масивів):

123456

Sub ArrayExample_1Line ()Затемнити strNames () як варіантstrNames = Array ("Shelly", "Steve", "Neema", "Jose")End Sub

Якщо ви спробуєте визначити значення для розташування масиву, яке не існує, ви отримаєте помилку Subscript Out of Range:

1	strNames (5) = "Шеннон"

У розділі "Призначити діапазон масиву" Нижче ми покажемо вам, як за допомогою циклу швидко призначати велику кількість значень масивам.

Отримати значення масиву

Ви можете отримати значення масиву так само. У наведеному нижче прикладі ми запишемо значення масиву в клітинки:

1234	Діапазон ("A1"). Значення = strNames (1)Діапазон ("A2"). Значення = strNames (2)Діапазон ("A3"). Значення = strNames (3)Діапазон ("A4"). Значення = strNames (4)

Програмування VBA | Генератор коду працює для вас!

Призначити діапазон масиву

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

12345678

ПіддиапазонToArray ()Затемнити strNames (від 1 до 60000) як рядокDim i As LongДля i = 1 до 60000strNames (i) = Осередки (i, 1). ЗначенняДалі iEnd Sub

Це дасть цикл через клітинки A1: A60000, призначаючи значення клітинки масиву.

Вихідний масив в діапазон

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

123	Для i = 1 до 60000Клітинки (i, 1). Значення = strNames (i)Далі i

Це зробить зворотне: призначте значення масиву клітинкам A1: A60000

2D / багатовимірні масиви

Поки що ми працювали виключно з одновимірними (1D) масивами. Однак масиви можуть мати до 32 вимірів.

Подумайте про 1D -масив як один рядок або стовпець клітинок Excel, 2D -масив, як цілий робочий аркуш Excel з кількома рядками та стовпцями, а 3D -масив - як ціла книга, що містить кілька аркушів, кожен з яких містить кілька рядків та стовпців (Ви також міг уявити собі 3D -масив як Кубик Рубіка).

Приклади багатовимірних масивів

Тепер продемонструємо приклади роботи з масивами різних розмірів.

Програмування VBA | Генератор коду працює для вас!

Приклад одновимірного масиву

Ця процедура об’єднує попередні приклади масивів в одну процедуру, демонструючи, як можна використовувати масиви на практиці.

1234567891011121314

Sub ArrayEx_1d ()Затемнити strNames (від 1 до 60000) як рядокDim i As Long'Призначте значення масивуДля i = 1 до 60000strNames (i) = Осередки (i, 1). ЗначенняДалі i'Вихідні значення масиву для діапазонуДля i = 1 до 60000Аркуші ("Вивід"). Клітинки (i, 1). Значення = strNames (i)Далі iEnd Sub

Приклад 2D -масиву

Ця процедура містить приклад 2D -масиву:

123456789101112131415161718

Sub ArrayEx_2d ()Затемнити прізвища (від 1 до 60000, від 1 до 10) як рядокDim i As Long, j As Long'Призначте значення масивуДля i = 1 до 60000Для j = 1-10strNames (i, j) = комірки (i, j)Далі jДалі i'Вихідні значення масиву для діапазонуДля i = 1 до 60000Для j = 1-10Аркуші ("Вивід"). Клітинки (i, j). Значення = strNames (i, j)Далі jДалі iEnd Sub

Приклад 3D -масиву

Ця процедура містить приклад 3D -масиву для роботи з кількома аркушами:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Затемнити прізвища (від 1 до 60000, від 1 до 10, від 1 до 3) як рядокDim i As Long, j As Long, k As Long'Призначте значення масивуДля k = 1 до 3Для i = 1 до 60000Для j = 1-10strNames (i, j, k) = Sheets ("Sheet" & k). Cell (i, j).Далі jДалі iДалі k'Вихідні значення масиву для діапазонуДля k = 1 до 3Для i = 1 до 60000Для j = 1-10Аркуші ("Вивід" & k). Клітинки (i, j). Значення = strNames (i, j, k)Далі jДалі iДалі kEnd Sub

Довжина / розмір масиву

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

Програмування VBA | Генератор коду працює для вас!

UBound і LBound функції

Першим кроком до отримання довжини / розміру масиву є використання функцій UBound і LBound для отримання верхньої та нижньої меж масиву:

123456

Sub UBoundLBound ()Затемнити strNames (від 1 до 4) як рядокMsgBox UBound (strNames)MsgBox LBound (strNames)End Sub

Відняття двох (і додавання 1) дасть вам довжину:

1	GetArrLength = UBound (strNames) - LBound (strNames) + 1

Функція Довжина масиву

Ось функція для отримання довжини одновимірного масиву:

1234567

Загальнодоступна функція GetArrLength (як варіант) до тих пірЯкщо IsEmpty (а) ТодіGetArrLength = 0ІнакшеGetArrLength = UBound (a) - LBound (a) + 1Закінчити ЯкщоФункція завершення

Потрібно обчислити розмір 2D -масиву? Перегляньте наш підручник: Розрахунок розміру масиву.

Цикл через масив

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

12345678910111213

Sub ArrayExample_Loop1 ()Затемнити strNames (від 1 до 4) як рядокDim i As LongstrNames (1) = "Shelly"strNames (2) = "Стів"strNames (3) = "Neema"strNames (4) = "Хосе"Для i = 1 до 4MsgBox strNames (i)Далі iEnd Sub

Однак, якщо ви не знаєте розмір масиву (якщо він динамічний), ви можете скористатися функціями LBound та UBound з попереднього розділу:

12345678910111213

Sub ArrayExample_Loop2 ()Затемнити strNames (від 1 до 4) як рядокDim i As LongstrNames (1) = "Shelly"strNames (2) = "Стів"strNames (3) = "Neema"strNames (4) = "Хосе"Для i = LBound (strNames) До UBound (strNames)MsgBox strNames (i)Далі iEnd Sub

Для кожного циклу масиву

Другий спосіб - з циклом For Each. Це перебирає кожен елемент у масиві:

12345678910111213

Sub ArrayExample_Loop3 ()Затемнити strNames (від 1 до 4) як рядокЗатемнений елементstrNames (1) = "Shelly"strNames (2) = "Стів"strNames (3) = "Neema"strNames (4) = "Хосе"Для кожного елемента в strNamesПункт MsgBoxНаступний пунктEnd Sub

Цикл For Each Array Loop працюватиме з багатовимірними масивами на додаток до одномірних масивів.

Програмування VBA | Генератор коду працює для вас!

Циклічний 2D -масив

Ви також можете використовувати функції UBound і LBound для циклічного перегляду багатовимірного масиву. У цьому прикладі ми розглянемо 2D -масив. Зверніть увагу, що функції UBound і LBound дозволяють вказати, який розмір масиву знайти верхню та нижню межі (1 для першого виміру, 2 для другого виміру).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Затемнити strNames (1 до 4, 1 до 2) як рядокDim i As Long, j As LongstrNames (1, 1) = "Shelly"strNames (2, 1) = "Стів"strNames (3, 1) = "Neema"strNames (4, 1) = "Хосе"strNames (1, 2) = "Шелбі"strNames (2, 2) = "Стівен"strNames (3, 2) = "Немо"strNames (4, 2) = "Джессі"Для j = LBound (strNames, 2) До UBound (strNames, 2)Для i = LBound (strNames, 1) Для UBound (strNames, 1)MsgBox strNames (i, j)Далі iДалі jEnd Sub

Інші завдання з масиву

Очистити масив

Щоб очистити весь масив, використовуйте оператор Erase:

1	Стерти імена strNames

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

12345678910

Sub ArrayExample ()Затемнити strNames (від 1 до 4) як StringstrNames (1) = "Shelly"strNames (2) = "Стів"strNames (3) = "Neema"strNames (4) = "Хосе"Стерти імена strNamesEnd Sub

Крім того, ви також можете змінити розмір масиву, щоб змінити його розмір, очистивши частину масиву:

1	ReDim strNames (від 1 до 2)

Це змінює розмір масиву до розміру 2, видаляючи позиції 3 і 4.

Граф Масив

Ви можете підрахувати кількість позицій у кожному вимірі масиву за допомогою функцій UBound та LBound (обговорювалося вище).

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

У цьому прикладі буде прокручуватись масив об’єктів і підраховувати кількість непустих рядків, знайдених у масиві:

123456789101112131415

Sub ArrayLoopandCount ()Затемнити strNames (від 1 до 4) як рядокDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Стів"Для i = LBound (strNames) До UBound (strNames)Якщо strNames (i) "" Тодіn = n + 1Закінчити ЯкщоДалі iMsgBox n & "Знайдено не порожні значення."End Sub

Програмування VBA | Генератор коду працює для вас!

Видаліть дублікати

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

Фільтр

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

1234567891011121314

Додатковий фільтр_відповідності ()'Визначте масивЗатемнити strNames як варіантstrNames = Array ("Стів Сміт", "Шеннон Сміт", "Райан Джонсон")'Масив фільтрівЗатемнити strSubNames як варіантstrSubNames = Фільтр (strNames, "Сміт")"Порахувати відфільтрований масивMsgBox "Знайдено" & UBound (strSubNames) - LBound (strSubNames) + 1 & "імена".End Sub

Функція IsArray

Ви можете перевірити, чи є змінна масивом, використовуючи функцію IsArray:

123456789101112

Sub IsArrayEx ()'Створює масив з позиціями 1,2,3Dim arrDemo1 (3) As String'Створює звичайну змінну рядкаDim str як рядокMsgBox IsArray (arrDemo1)MsgBox IsArray (str)End Sub

Приєднуйтесь до Array

Ви можете швидко "приєднатися" до цілого масиву разом із функцією Join:

123456789101112

Sub Array_Join ()Затемнити strNames (від 1 до 4) як рядокЗатемнити joinNames як рядокstrNames (1) = "Shelly"strNames (2) = "Стів"strNames (3) = "Neema"strNames (4) = "Хосе"joinNames = Приєднатися (strNames, ",")MsgBox joinNamesEnd Sub

Програмування VBA | Генератор коду працює для вас!

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

Функція VBA Split розділить рядок тексту на масив, що містить значення з вихідного рядка. Розглянемо приклад:

123456789

Sub Array_Split ()Dim Names () як рядокDim joinedNames As StringjoinNames = "Шеллі, Стів, Нема, Хосе"Імена = Розділити (приєднаніНазви, ",")Імена MsgBox (1)End Sub

Тут ми розбиваємо цей рядок тексту «Шеллі, Стів, Нема, Хосе» на масив (розмір 4) за допомогою роздільника комами (, »).

Const Array

Масив не може оголошується як константа у VBA. Однак ви можете обійти це, створивши функцію для використання як масиву:

123456789

'Визначте ConstantArrayФункція ConstantArray ()ConstantArray = Масив (4, 12, 21, 100, 5)Функція завершення'Отримати значення ConstantArraySub RetrieveValues ​​()MsgBox ConstantArray (3)End Sub

Копіювати масив

Немає вбудованого способу копіювання масиву за допомогою VBA. Замість цього вам потрібно буде використовувати цикл для призначення значень з одного масиву іншому.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 до 100) як довгоDim Arr2 (1 до 100) як довгоDim i As Long'Створити масив1Для i = 1 до 100Arr1 (i) = iДалі i'CopyArray1 у Array2Для i = 1 до 100Arr2 (i) = Arr1 (i)Далі iMsgBox Arr2 (74)End Sub

Транспонувати

Немає вбудованої функції VBA, яка дозволяє транспонувати масив. Однак ми написали функцію транспонування 2D -масиву. Прочитайте статтю, щоб дізнатися більше.

Програмування VBA | Генератор коду працює для вас!

Масив повернення функції

Поширене питання у розробників VBA - як створити функцію, яка повертає масив. Я думаю, що більшість труднощів вирішується за допомогою варіантів масивів. Ми написали статтю на тему: Масив повернення функцій VBA.

Використання масивів у Access VBA

Більшість наведених вище прикладів масиву працюють точно так само в Access VBA, як і в Excel VBA. Єдина істотна відмінність полягає в тому, що коли ви хочете заповнити масив за допомогою даних Access, вам доведеться перебирати об'єкт RecordSet, а не об'єкт Range.

1234567891011121314151617181920212223

ПіддиапазонToArrayAccess ()Увімкнути Помилка Відновити ДаліDim strNames () Як рядокDim i As LongЗатемнюйте iCount як довгоDim dbs як база данихDim rst як записВстановити dbs = CurrentDbВстановити rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)З першим.Перевірити останній.Перейти спочаткуiCount = .RecordCountReDim strNames (1 до iCount)Для i = 1 до iCountstrNames (i) = rst.Fields ("ClientName").ПереміститиДаліДалі iЗакінчити зПерший. ЗакритиВстановити rst = нічогоВстановити dbs = нічогоEnd Sub

Посібники з масиву
Магазин Mega-Guide	так
Отримати розмір масиву
Очистити масив
Масив фільтрів
Транспонувати масив
Масив повернення функції
Видаліть дублікати