- Бърз лист с масив VBA
- Бързи примери за масив VBA
- Ползи от масив? - Скорост!
- Създаване / Деклариране на масив (Dim)
- Задайте стойности на масива
- Присвояване на диапазон на масив
- 2D / Многоизмерни масиви
- Примери за многомерни масиви
- Дължина / размер на масива
- Цикъл през масив
- Други задачи за масив
- Използване на масиви в Access 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 | да |
Вземете размер на масива | |
Изчистване на масива | |
Филтриращ масив | |
Транспониране на масив | |
Функция Връщане на масив | |
Премахване на дубликати | |