VBA масиви

Във VBA, an Масив е единична променлива, която може да съдържа множество стойности. Мислете за масив като диапазон от клетки: всяка клетка може да съхранява стойност. Масивите могат да бъдат едномерни (помислете за една колона), двуизмерни (помислете за множество редове и колони) или многоизмерни. Стойностите на масива могат да бъдат достъпни чрез тяхната позиция (индекс номер) в масива.

Бърз лист с масив VBA

Масиви

ОписаниеVBA кодСъздайтеDim arr (1 до 3) като вариант
arr (1) = „един“
arr (2) = „две“
arr (3) = „три“Създаване от ExcelDim arr (1 до 3) като вариант
Dim cell As Range, i As Integer
i = LBound (arr)
За всяка клетка в обхвата („A1: A3“)
i = i + 1
arr (i) = cell.value
Следващата клеткаПрочетете всички елементиDim i as Long
За i = LBound (arr) Към UBound (arr)
MsgBox arr (i)
Следва iИзтриваИзтриване на обрМасив към низDim sName As String
sName = Присъединяване (arr, “:”)Увеличете размераReDim Preserve arr (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 ()Dim strNames (1 To 60000) As StringDim i As LongЗа i = 1 до 60000strNames (i) = Клетки (i, 1)Следва iEnd Sub

Тук създадохме масив, който може да съдържа 60 000 стойности, и бързо попълнихме масива от колона А на работен лист.

Ползи от масив? - Скорост!

Може да мислите за масиви, подобни на работни листове на Excel:

• Всяка клетка (или елемент в масив) може да съдържа собствена стойност
• Всяка клетка (или елемент в масив) може да бъде достъпна чрез позицията на реда и колоната.
  • Работен лист Ex. клетки (1,4) .value = „Ред 1, Колона 4“
  • Масив Ex. arrVar (1,4) = „Ред 1, Колона 4“

Така че защо да се занимавате с масиви? Защо просто не прочетете и запишете стойности директно в клетки в Excel? Една дума: Скорост!

Четенето / записването в Excel клетки е бавен процес. Работата с масиви е много по -бърза!

Създаване / Деклариране на масив (Dim)

Забележка: Масивите могат да имат множество „измерения“. За да опростим нещата, ще започнем само с работа с едномерни масиви. По-късно в урока ще ви запознаем с масиви с много измерения.

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

Статични масиви са масиви, които не могат да променят размера. Обратно, Динамични масиви може да променя размера. Те са декларирани малко по -различно. Първо, нека разгледаме статичните масиви.

Забележка: Ако вашият масив няма да се промени по размер, използвайте статичен масив.

Декларирането на променлива статичен масив е много подобно на декларирането на обикновена променлива, с изключение на това, че трябва да дефинирате размера на масива. Има няколко различни начина за задаване на размера на масив.

Можете изрично да декларирате началната и крайната позиция на масив:

123456789101112

Sub StaticArray1 ()'Създава масив с позиции 1,2,3,4Dim arrDemo1 (1 To 4) As String„Създава масив с позиции 4,5,6,7Dim arrDemo2 (4 To 7) As Long'Създава масив с позиции 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

Sub VariantArray ()Dim arrVar () Като вариант'Определете стойности (размер = 0,1,2,3)arrVar = Масив (1, 2, 3, 4)„Промяна на стойностите (Размер = 0,1,2,3,4)arrVar = Array ("1a", "2a", "3a", "4a", "5a")„Изходна позиция 4 („ 5а “)MsgBox arrVar (4)End Sub

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

При невариантни масиви трябва да дефинирате размера на масива, преди да присвоите стойности на масива. Процесът на създаване на масива обаче е малко по -различен:

1234567

Под DynamicArray1 ()Dim arrDemo1 () As String„Преоразмерява масива с позиции 1,2,3,4ReDim arrDemo1 (1 до 4)End Sub

Първо декларирате масива, подобен на статичния, с изключение на това, че пропускате размера на масива:

1	Dim arrDemo1 () As String

Сега, когато искате да зададете размера на масива, използвате ReDim команда за оразмеряване на масива:

12	„Преоразмерява масива с позиции 1,2,3,4ReDim arrDemo1 (1 до 4)

ReDim преоразмерява масива. Прочетете по -долу за разликата между ReDim и ReDim Preserve.

ReDim срещу ReDim Preserve

Когато използвате ReDim команда изчиствате всички съществуващи стойности от масива. Вместо това можете да използвате ReDim Preserve за запазване на стойностите на масива:

12	'Преоразмеряване на масива с позиции 1,2,3,4 (Запазване на съществуващите стойности)ReDim Preserve arrDemo1 (1 до 4)

Обявяване на опростени масиви

Може да се почувствате претоварени, след като прочетете всичко по -горе. За да опростим нещата, най -вече ще работим със статични масиви за останалата част от статията.

Задайте стойности на масива

Задаването на стойности на масива е много лесно.

Със статичен масив трябва да дефинирате всяка позиция на масива, една по една:

12345678

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

С Variant Array можете да дефинирате целия масив с един ред (практичен само за малки масиви):

123456

Sub ArrayExample_1Line ()Dim 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 програмиране | Code Generator работи за вас!

Присвояване на диапазон на масив

За да присвоите диапазон на масив, можете да използвате цикъл:

12345678

Sub RangeToArray ()Dim strNames (1 To 60000) As StringDim 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 програмиране | Code Generator работи за вас!

Пример за 1D масив

Тази процедура комбинира предишните примери на масиви в една процедура, демонстрирайки как можете да използвате масиви на практика.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 To 60000) As StringDim 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 ()Dim strNames (1 To 60000, 1 To 10) As StringDim 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 ()Dim strNames (1 To 60000, 1 To 10, 1 To 3) As StringDim i As Long, j As Long, k As Long„Присвояване на стойности на масивЗа k = 1 до 3За i = 1 до 60000За j = 1 до 10strNames (i, j, k) = Sheets ("Sheet" & k). Клетки (i, j).Следващ jСледва iСледващ к'Стойности на изходния масив за обхватЗа k = 1 до 3За i = 1 до 60000За j = 1 до 10Листове ("Изход" & k). Клетки (i, j). Стойност = strNames (i, j, k)Следващ jСледва iСледващ кEnd Sub

Дължина / размер на масива

Досега ви запознахме с различните типове масиви и ви научихме как да декларирате масивите и да получавате/задавате стойности на масиви. След това ще се съсредоточим върху други необходими теми за работа с масиви.

VBA програмиране | Code Generator работи за вас!

UBound и LBound функции

Първата стъпка към получаване на дължината / размера на масива е използването на функциите UBound и LBound за получаване на горните и долните граници на масива:

123456

Sub UBoundLBound ()Dim strNames (1 To 4) As StringMsgBox 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 ()Dim strNames (1 To 4) As StringDim 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 ()Dim strNames (1 To 4) As StringDim i As LongstrNames (1) = "Shelly"strNames (2) = "Стив"strNames (3) = "Neema"strNames (4) = "Хосе"За i = LBound (strNames) Към UBound (strNames)MsgBox strNames (i)Следва iEnd Sub

За всеки цикъл на масива

Вторият метод е с For For Loop. Това преминава през всеки елемент от масива:

12345678910111213

Sub ArrayExample_Loop3 ()Dim strNames (1 To 4) As StringЗатъмнен елементstrNames (1) = "Shelly"strNames (2) = "Стив"strNames (3) = "Neema"strNames (4) = "Хосе"За всеки елемент в strNamesMsgBox елементСледващ елементEnd Sub

Цикълът за всеки масив ще работи с многоизмерни масиви в допълнение към едномерни масиви.

VBA програмиране | Code Generator работи за вас!

Цикличен 2D масив

Можете също да използвате функциите UBound и LBound, за да преминете през многоизмерен масив. В този пример ще преминем през 2D масив. Забележете, че функциите UBound и LBound ви позволяват да посочите коя величина на масива да намерите горните и долните граници (1 за първото измерение, 2 за второто измерение).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Dim strNames (1 To 4, 1 To 2) As StringDim 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 ()Dim strNames (1 To 4) As StringDim 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 програмиране | Code Generator работи за вас!

Премахване на дубликати

В един момент може да искате да премахнете дубликати от масив. За съжаление, VBA няма вградена функция за това. Ние обаче написахме функция за премахване на дубликати от масив (твърде дълго е за включване в този урок, но посетете връзката, за да научите повече).

Филтър

Функцията VBA Filter ви позволява да филтрирате масив. Това става чрез създаване на нов масив само с филтрираните стойности. По -долу е кратък пример, но не забравяйте да прочетете статията за още примери за различни нужди.

1234567891011121314

Sub Filter_Match ()„Определете масивDim strNames As VariantstrNames = Array („Стив Смит“, „Шанън Смит“, „Райън Джонсън“)„Филтриращ масивЗатъмнете strSubNames като вариантstrSubNames = Филтър (strNames, "Smith")„Брой филтрирани масивиMsgBox "Намерено" & UBound (strSubNames) - LBound (strSubNames) + 1 & "имена."End Sub

Функция IsArray

Можете да тествате дали променлива е масив, използвайки функцията IsArray:

123456789101112

Sub IsArrayEx ()'Създава масив с позиции 1,2,3Dim arrDemo1 (3) As String'Създава обикновена низова променливаDim str As StringMsgBox IsArray (arrDemo1)MsgBox IsArray (str)End Sub

Присъединете се към Array

Можете бързо да се "присъедините" към цял масив заедно с функцията за присъединяване:

123456789101112

Sub Array_Join ()Dim strNames (1 To 4) As StringЗатъмнете joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Стив"strNames (3) = "Neema"strNames (4) = "Хосе"joinNames = Присъединете се (strNames, ",")MsgBox joinNamesEnd Sub

VBA програмиране | Code Generator работи за вас!

Разделете низа в масив

Функцията VBA Split ще раздели низ от текст на масив, съдържащ стойности от оригиналния низ. Нека разгледаме един пример:

123456789

Sub Array_Split ()Dim Names () As StringDim joinedNames As StringjoinNames = "Шели, Стив, Нема, Хосе"Имена = Разделяне (съединени имена, ",")Имена на MsgBox (1)End Sub

Тук разделяме този низ от текст „Shelly, Steve, Nema, Jose“ на масив (размер 4), използвайки разделител на запетая (, ”).

Const Array

Масив не мога да бъде деклариран като константа във VBA. Можете обаче да заобиколите това, като създадете функция, която да се използва като масив:

123456789

'Определете ConstantArrayФункция ConstantArray ()ConstantArray = Array (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 програмиране | Code Generator работи за вас!

Функция Връщане на масив

Често задаван въпрос от разработчиците на VBA е как да създадат функция, която връща масив. Мисля, че повечето от трудностите се решават с помощта на Variant Arrays. Написахме статия по темата: Масив за връщане на VBA функция.

Използване на масиви в Access VBA

Повечето от горните примери за масив работят точно по същия начин в Access VBA, както в Excel VBA. Основната разлика е, че когато искате да попълните масив, използвайки данни на Access, ще трябва да преминете през обекта RecordSet, а не в обекта Range.

1234567891011121314151617181920212223

Sub RangeToArrayAccess ()На грешка възобновяване следващоDim strNames () Като StringDim i As LongDim iCount As LongDim dbs като база данниDim rst As RecordsetЗадайте 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	да
Вземете размер на масива
Изчистване на масива
Филтриращ масив
Транспониране на масив
Функция Връщане на масив
Премахване на дубликати