Колекцията е обект, който съдържа редица подобни елементи. Те могат лесно да бъдат достъпни и манипулирани, дори ако в колекцията има голям брой елементи.
Вече има вградени колекции с Excel VBA. Пример за това е колекцията Sheets. За всеки работен лист в работна книга има елемент в колекцията Sheets.
Вградените колекции имат много повече налични свойства и методи, но те не са налични във вашите собствени колекции, които създавате.
Например, можете да използвате колекцията, за да получите информация за конкретен работен лист. Например можете да видите името на работния лист, както и дали той се вижда или не. Като използвате цикъл „За всеки“, можете да преглеждате всеки работен лист в колекцията.
| 1234567 | Sub TestWorksheets ()Dim Sh като работен листЗа всеки Sh In SheetsMsgBox Sh.NameMsgBox Sh.VisibleСледващ ШEnd Sub |
Можете също да адресирате конкретен работен лист в колекцията, като използвате стойността на индекса или действителното име на работния лист:
| 12 | MsgBox листове (1). ИмеMsgBox Sheets ("Sheet1"). Име |
Тъй като работните листове се добавят или изтриват, колекцията от листове нараства или намалява по размер.
Имайте предвид, че при VBA колекциите индексният номер започва с 1, а не с 0
Колекции срещу масиви
Масивите и колекциите са сходни по своите функции, тъй като и двете са методологии, които позволяват да се съхранява голямо количество данни, които след това могат лесно да се препращат с помощта на код. Те обаче имат редица разлики в начина на работа:
- Масивите са многоизмерни, докато колекциите са само едноизмерни. Можете да оразмерите масив с няколко измерения, напр.
| 1 | Dim MyArray (10, 2) As String |
Това създава масив от 10 реда с 2 колони, почти като работен лист. Една колекция е всъщност една колона. Масивът е полезен, ако трябва да съхранявате редица елементи от данни, които са свързани помежду си, напр. име и адрес. Името ще бъде в първото измерение на масива, а адресът във второто измерение.
- Когато попълвате вашия масив, имате нужда от отделен ред код, за да поставите стойност във всеки елемент от масива. Ако имате двуизмерен масив, всъщност ще ви трябват 2 реда код - един ред за адресиране на първата колона и един ред за адресиране на втората колона. С обекта Колекция просто използвате метода Добавяне, така че новият елемент просто да бъде добавен в колекцията и стойността на индекса автоматично да се регулира според него.
- Ако трябва да изтриете елемент от данни, тогава е по -сложно в масива. Можете да зададете стойностите на елемент на празна стойност, но самият елемент все още съществува в масива. Ако използвате цикъл For Next за повторение през масива, цикълът ще върне празна стойност, която ще се нуждае от кодиране, за да се увери, че празната стойност е игнорирана. В колекция използвате методите за добавяне или премахване и автоматично се грижат за цялото индексиране и преоразмеряване. Изтритият елемент изчезва напълно. Масивите са полезни за фиксиран размер на данните, но колекциите са по -добри там, където количеството данни може да се промени.
- Колекциите са само за четене, докато стойностите на масива могат да се променят с помощта на VBA. С колекция ще трябва първо да премахнете стойността, която да се промени, и след това да добавите новата променена стойност.
- В масив можете да използвате само един тип данни за елементите, който е зададен, когато оразмерявате масива. В масива обаче можете да използвате персонализирани типове данни, които сами сте проектирали. Можете да имате много сложна структура от масиви, използвайки персонализиран тип данни, който от своя страна има няколко персонализирани типа данни под него. В колекция можете да добавяте използвани типове данни за всеки елемент. Можете да имате числова стойност, дата или низ - обектът за събиране ще вземе всеки тип данни. Ако се опитате да поставите низова стойност в масив, който е оразмерен като числов, това ще доведе до съобщение за грешка.
- Колекциите обикновено са по -лесни за използване от масивите. По отношение на кодирането, когато създавате обект на колекция, той има само два метода (Добавяне и премахване) и две свойства (Брой и елемент), така че обектът в никакъв случай не е сложен за програмиране.
- Колекциите могат да използват ключове за намиране на данни. Масивите нямат тази функция и изискват цикличен код за повторение през масива за намиране на конкретни стойности.
- Размерът на масив трябва да бъде определен при първото му създаване. Трябва да имате представа колко данни ще съхранява. Ако трябва да увеличите размера на масива, можете да използвате „ReDim“, за да го преоразмерите, но трябва да използвате ключовата дума „Запазване“, ако не искате да загубите данните, които вече се съхраняват в масива. Не е необходимо да се определя размер на колекция. Той просто расте и се свива автоматично, когато елементите се добавят или премахват.
Обхват на обект на колекция
По отношение на обхвата обектът за събиране е достъпен само докато работната книга е отворена. Той не се запазва, когато работната книга е запазена. Ако работната книга е отворена отново, колекцията трябва да бъде създадена отново с помощта на VBA код.
Ако искате вашата колекция да бъде достъпна за целия код във вашия кодов модул, тогава трябва да декларирате обекта за събиране в секцията Declare в горната част на прозореца на модула
Това ще гарантира, че целият ви код в този модул има достъп до колекцията. Ако искате някой модул във вашата работна книга да има достъп до колекцията, тогава я дефинирайте като глобален обект
| 1 | Global MyCollection като нова колекция |
Създаване на колекция, добавяне на елементи и достъп до елементи
Прост обект за събиране може да бъде създаден във VBA, като използвате следния код:
| 123456 | Sub CreateCollection ()Dim MyCollection като нова колекцияMyCollection.Add „Item1“MyCollection.Add „Item2“MyCollection.Add „Item3“End Sub |
Кодът измерва нов обект, наречен „MyCollection“, а след това следващите редове код използват метода Add за добавяне на 3 нови стойности.
След това можете да използвате код, за да повторите колекцията си за достъп до стойностите
| 123 | За всеки елемент в MyCollectionMsgBox елементСледващ елемент |
Можете също така да преглеждате колекцията си, като използвате For Next Loop:
| 123 | За n = 1 Към MyCollection.CountMsgBox MyCollection (n)Следващ n |
Кодът получава размера на колекцията, като използва свойството Count и след това използва тази начална стойност 1 за индексиране на всеки елемент
Цикълът For For Loop е по -бърз от цикъла For Next, но работи само в една посока (от нисък индекс до висок). За следващия цикъл има предимството, че можете да използвате различна посока (висок индекс към нисък) и можете също да използвате метода Step, за да промените увеличението. Това е полезно, когато искате да изтриете няколко елемента, тъй като ще трябва да стартирате изтриването от края на колекцията до началото, тъй като индексът ще се промени с изтриването.
Методът Добавяне в колекция има 3 незадължителни параметъра - Ключ, Преди и След
Можете да използвате параметрите „Преди“ и „След“, за да определите позицията на новия си елемент спрямо останалите вече в колекцията
Това се прави, като посочите номера на индекса, към който искате новата ви позиция да бъде относителна.
| 123456 | Sub CreateCollection ()Dim MyCollection като нова колекцияMyCollection.Add „Item1“MyCollection.Add "Item2",, 1MyCollection.Add „Item3“End Sub |
В този пример „Item2“ е посочено да се добави преди първия индексиран елемент в колекцията (който е „Item1“). Когато повторите тази колекция, първо ще се появи „Item2“, последван от „Item1“ и „Item3“
Когато посочите параметър „Преди“ или „След“, стойността на индекса се коригира автоматично в рамките на колекцията, така че „Позиция2“ да се превърне в стойност на индекса 1, а „Позиция1“ да се премести в стойност на индекс 2
Можете също да използвате параметъра „Key“, за да добавите референтна стойност, която можете да използвате, за да идентифицирате елемента от колекцията. Обърнете внимание, че стойността на ключ трябва да бъде низ и трябва да бъде уникална в рамките на колекцията.
| 1234567 | Sub CreateCollection ()Dim MyCollection като нова колекцияMyCollection.Add „Item1“MyCollection.Add "Item2", "MyKey"MyCollection.Add „Item3“MsgBox MyCollection ("MyKey")End Sub |
На „Item2“ е дадена стойност „Key“ на „MyKey“, така че можете да се обърнете към този елемент, като използвате стойността на „MyKey“ вместо номера на индекса (2)
Обърнете внимание, че стойността „Key“ трябва да бъде низ. Не може да бъде друг тип данни. Имайте предвид, че колекцията е само за четене и не можете да актуализирате стойността на ключа, след като е зададена. Също така не можете да проверите дали съществува стойност на ключ за конкретен елемент от колекцията или да прегледате стойността на ключа, което е малко недостатък.
Параметърът „Key“ има допълнителното предимство, което прави кода ви по -четим, особено ако се предава на колега за поддръжка и не е нужно да преглеждате цялата колекция, за да намерите тази стойност. Представете си, ако имате колекция от 10 000 артикула колко трудно би било да се позовавате на един конкретен артикул!
Премахване на елемент от колекция
Можете да използвате метода „Премахване“, за да изтриете елементи от колекцията си.
| 1 | MyCollection.Remove (2) |
За съжаление, не е лесно, ако колекцията има голям брой елементи, за да изчисли индекса на елемента, който искате да изтриете. Тук параметърът „Ключ“ е полезен при създаването на колекцията
| 1 | MyCollection.Remove („MyKey“) |
Когато елемент е премахнат от колекция, стойностите на индекса се нулират автоматично през цялата колекция. Тук параметърът „ключ“ е толкова полезен, когато изтривате няколко елемента наведнъж. Например, можете да изтриете индекс 105 на елемент и незабавно индекс 106 на елемент става индекс 105 и всичко над този елемент има преместена стойност на индекса надолу. Ако използвате параметъра Key, няма нужда да се притеснявате коя стойност на индекса трябва да бъде премахната.
За да изтриете всички елементи от колекцията и да създадете нова колекция, отново използвате оператора Dim, който създава празна колекция.
| 1 | Dim MyCollection като нова колекция |
За да премахнете напълно действителния обект на колекция, можете да зададете обекта на нищо
| 1 | Задайте MyCollection = Нищо |
Това е полезно, ако колекцията вече не се изисква от вашия код. Задаването на обекта на колекция на нищо премахва всички препратки към него и освобождава паметта, която е използвал. Това може да има важни последици за скоростта на изпълнение на вашия код, ако в паметта седи голям обект, който вече не е необходим.
Пребройте броя на артикулите в колекция
Можете лесно да разберете броя на елементите във вашата колекция, като използвате свойството „Count“
| 1 | MsgBox MyCollection.Count |
Бихте използвали това свойство, ако сте използвали For Next Loop за повторение на колекцията, тъй като тя ще ви предостави горната граница за номера на индекса.
Тестова колекция за конкретна стойност
Можете да повторите през колекция, за да търсите конкретна стойност за елемент, като използвате за всеки цикъл
| 123456789101112 | Sub SearchCollection ()Dim MyCollection като нова колекцияMyCollection.Add „Item1“MyCollection.Add „Item2“MyCollection.Add „Item3“За всеки елемент в MyCollectionАко Item = "Item2" ТогаваMsgBox Item & "Found"Край АкоСледващияEnd Sub |
Кодът създава малка колекция и след това я преглежда, търсейки елемент, наречен „item2“. Ако е намерен, той показва поле за съобщение, че е намерил конкретния елемент
Един от недостатъците на тази методология е, че нямате достъп до стойността на индекса или стойността на ключа
Ако вместо това използвате For Next Loop, можете да използвате брояча For Next, за да получите стойността на индекса, въпреки че все още не можете да получите стойността „Key“
| 123456789101112 | Sub SearchCollection ()Dim MyCollection като нова колекцияMyCollection.Add „Item1“MyCollection.Add „Item2“MyCollection.Add „Item3“За n = 1 Към MyCollection.CountАко MyCollection.Item (n) = "Item2" ТогаваMsgBox MyCollection.Item (n) & "намерено на позицията на индекса" & nКрай АкоСледващ nEnd Sub |
Броячът For Next (n) ще предостави позицията на индекса
Сортиране на колекция
Няма вградена функционалност за сортиране на колекция, но използвайки някакво „нестандартно“ мислене, кодът може да бъде написан, за да се извърши сортиране, като се използва функцията за сортиране на работния лист на Excel. Този код използва празен работен лист, наречен „SortSheet“, за да извърши действителното сортиране.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection като нова колекцияDim Counter As Long„Изградете колекция с елементи от произволна поръчкаMyCollection.Add "Item5"MyCollection.Add „Item2“MyCollection.Add „Item4“MyCollection.Add „Item1“MyCollection.Add „Item3“„Уловете броя на предметите в колекция за бъдеща употребаБрояч = MyCollection.Count„Итерация през колекцията, копиране на всеки елемент в последователна клетка в„ SortSheet “(колона А)За n = 1 Към MyCollection.CountЛистове ("SortSheet"). Клетки (n, 1) = MyCollection (n)Следващ n„Активирайте таблицата за сортиране и използвайте програмата за сортиране на Excel, за да сортирате данните във възходящ редТаблици („SortSheet“). АктивирайтеДиапазон ("A1: A" & MyCollection.Count). ИзберетеActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Ред: = xlAscending, DataOption: = _xlSortNormalС ActiveWorkbook.Worksheets ("SortSheet"). СортиранеДиапазон .SetRange ("A1: A5").Header = xlGuess.MatchCase = False.Ориентация = xlTopToBottom.SortMethod = xlPinYin.ПриложиКрай с„Изтрийте всички елементи от колекцията - имайте предвид, че този For Next Loop работи в обратен редЗа n = MyCollection.Count To 1 Step -1MyCollection.Remove (n)Следващ n„Копирайте стойностите на клетката обратно в празния обект за събиране, като използвате съхранената стойност (Counter) за цикъла„За n = 1 Към броячMyCollection.Add Sheets ("SortSheet"). Клетки (n, 1).Следващ n„Повторете колекцията, за да докажете реда, в който артикулите са в моментаЗа всеки елемент в MyCollectionMsgBox елементСледващ елемент„Изчистете работния лист (сортировъчен лист) - ако е необходимо, изтрийте и негоЛистове ("SortSheet"). Обхват (клетки (1, 1), клетки (брояч, 1)). ИзчистванеEnd Sub |
Този код първо създава колекция с елементите, добавени в произволен ред. След това ги копира в първата колона на работен лист (SortSheet).
След това Code използва функцията за сортиране на Excel, за да сортира данните в колоната във възходящ ред. Кодът също може да бъде променен, за да сортира в низходящ ред.
След това колекцията се изпразва от данни с помощта на For Next Loop. Обърнете внимание, че опцията стъпка се използва така, че да изчиства от края на колекцията до началото. Това е така, защото докато се изчиства, стойностите на индекса се нулират, ако се изчисти от самото начало, няма да се изчисти правилно (индекс 2 ще стане индекс 1)
Накрая, използвайки друг For Next Loop, стойностите на елементите се прехвърлят обратно в празната колекция
Допълнително за всеки цикъл доказва, че сега колекцията е в добър възходящ ред.
За съжаление, това не се отнася за никакви ключови стойности, които може да са били въведени първоначално, тъй като ключовите стойности не могат да бъдат прочетени
Предаване на колекция към под / функция
Колекция може да бъде предадена на под -функция или функция по същия начин като всеки друг параметър
| 1 | Функция MyFunction (ByRef MyCollection като колекция) |
Важно е да предадете колекцията с помощта на „ByRef“. Това означава, че се използва оригиналната колекция. Ако колекцията се предава с помощта на „ByVal“, това създава копие на колекцията, което може да има неприятни последици
Ако копие е създадено с помощта на „ByVal“, тогава всичко, което променя колекцията във функцията, се случва само върху копието, а не върху оригинала. Например, ако в рамките на функцията се добави нов елемент към колекцията, това няма да се появи в оригиналната колекция, което ще създаде грешка в кода ви.
Връщане на колекция от функция
Можете да върнете колекция от функция по същия начин като връщане на всеки обект. Трябва да използвате ключовата дума Set
| 12345 | Sub ReturnFromFunction ()Dim MyCollection As CollectionЗадайте MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Този код създава подпрограма, която създава обект, наречен „MyCollection“ и след това използва ключовата дума „Set“ за ефективно извикване на функцията за попълване на тази колекция. След като това е направено, се показва поле със съобщение, което показва броя на 2 елемента
| 1234567 | Функция PopulateCollection () като колекцияDim MyCollection като нова колекцияMyCollection.Add „Item1“MyCollection.Add „Item2“Задайте PopulateCollection = MyCollectionКрайна функция |
Функцията PopulateCollection създава нов обект на колекция и го попълва с 2 елемента. След това той предава този обект обратно на обекта за събиране, създаден в оригиналната подпрограма.
Преобразуване на колекция в масив
Може да искате да преобразувате колекцията си в масив. Може да искате да съхранявате данните, където могат да бъдат променени и манипулирани. Този код създава малка колекция и след това я прехвърля в масив
Забележете, че индексът на колекцията започва от 1, докато индексът на масива започва от 0. Докато колекцията има 3 елемента, масивът трябва само да бъде оразмерен на 2, защото има елемент 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection като нова колекцияDim MyArray (2) As StringMyCollection.Add „Item1“MyCollection.Add „Item2“MyCollection.Add „Item3“За n = 1 Към MyCollection.CountMyArray (n - 1) = MyCollection (n)Следващ nЗа n = 0 до 2MsgBox MyArray (n)Следващ nEnd Sub |
Конвертиране на масив в колекция
Може да искате да конвертирате масив в колекция. Например, може да искате да получите достъп до данните по по -бърз и елегантен начин, като използвате код, за да получите елемент от масив.
Имайте предвид, че това ще работи само за едно измерение на масива, защото колекцията има само едно измерение
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection като нова колекцияDim MyArray (2) Като низMyArray (0) = "item1"MyArray (1) = "Елемент2"MyArray (2) = "Елемент3"За n = 0 до 2MyCollection.Add MyArray (n)Следващ nЗа всеки елемент в MyCollectionMsgBox елементСледващ елементEnd Sub |
Ако все пак искате да използвате многоизмерен масив, можете да комбинирате стойностите на масива заедно за всеки ред в масива, като използвате знак за разделител между размерите на масива, така че когато четете стойността на колекцията, можете да използвате програмно символа на разделителя, за да отделете стойностите.
Можете също така да преместите данните в колекцията въз основа на това, че се добавя стойността на първото измерение (индекс 1) и след това се добавя следващата стойност на измерението (индекс 2) и т.н.
Ако масивът имаше, да речем, 4 измерения, всяка четвърта стойност в колекцията би била нов набор от стойности.
Можете също така да добавите стойности на масиви, които да използвате като ключове (при условие, че са уникални), което би добавило лесен начин за намиране на конкретни данни.
