Kolekce je předmět, který obsahuje řadu podobných položek. K nim lze snadno přistupovat a manipulovat s nimi, i když je ve sbírce velké množství položek.
V aplikaci Excel VBA již existují předdefinované kolekce. Příkladem je kolekce Sheets. Pro každý list v sešitu existuje položka v kolekci Tabulky.
Integrované kolekce mají k dispozici mnohem více vlastností a metod, ale tyto nejsou k dispozici ve vašich vlastních kolekcích, které vytvoříte.
Kolekci můžete například použít k získání informací o konkrétním listu. Můžete například vidět název listu a také to, zda je viditelný nebo ne. Pomocí smyčky For Each můžete iterovat každý list v kolekci.
| 1234567 | Dílčí testovací listy ()Pracovní list Dim Sh AsZa každý Sh In SheetsMsgBox Sh. JménoMsgBox Sh. ViditelnéDalší ShEnd Sub |
Můžete také adresovat konkrétní list v kolekci pomocí hodnoty indexu nebo skutečného názvu listu:
| 12 | Listy MsgBox (1). NázevTabulky MsgBox („Sheet1“). Jméno |
Jak jsou listy přidávány nebo odstraňovány, kolekce listů se zvětšuje nebo zmenšuje.
Všimněte si, že u sbírek VBA indexové číslo začíná 1 ne 0
Sbírky versus pole
Pole a kolekce mají podobné funkce v tom, že jde o metodiky, které umožňují uložení velkého množství dat, na která lze poté snadno odkazovat pomocí kódu. Mají však řadu rozdílů ve způsobu fungování:
- Pole jsou vícerozměrná, zatímco kolekce jsou pouze jednorozměrné. Pole můžete dimenzovat několika dimenzemi, např.
| 1 | Dim MyArray (10, 2) jako řetězec |
Tím se vytvoří pole 10 řádků se 2 sloupci, téměř jako list. Kolekce je ve skutečnosti jediným sloupcem. Pole je užitečné, pokud potřebujete uložit několik položek dat, které se navzájem týkají, např. jméno a adresu. Název by byl v první dimenzi pole a adresa ve druhé dimenzi.
- Když naplníte své pole, potřebujete samostatný řádek kódu pro vložení hodnoty do každého prvku pole. Pokud byste měli dvourozměrné pole, ve skutečnosti byste potřebovali 2 řádky kódu - jeden řádek pro adresování prvního sloupce a jeden řádek pro adresování druhého sloupce. U objektu Collection jednoduše použijete metodu Add, aby se nová položka právě přidala do kolekce a hodnota indexu se automaticky upravila tak, aby vyhovovala.
- Pokud potřebujete odstranit položku dat, pak je to v poli složitější. Hodnoty prvku můžete nastavit na prázdnou hodnotu, ale samotný prvek v poli stále existuje. Pokud k procházení pole používáte smyčku For Next, smyčka vrátí prázdnou hodnotu, která bude vyžadovat kódování, aby se zajistilo, že bude prázdná hodnota ignorována. V kolekci používáte metody Přidat nebo Odebrat a o veškeré indexování a změnu velikosti se automaticky postará. Odstraněná položka zcela zmizí. Pole jsou užitečná pro pevnou velikost dat, ale kolekce jsou lepší tam, kde se množství dat může změnit.
- Sbírky jsou jen pro čtení, zatímco hodnoty polí lze změnit pomocí VBA. U kolekce byste museli nejprve odebrat hodnotu, kterou chcete změnit, a poté přidat novou změněnou hodnotu.
- V poli můžete pro prvky použít pouze jeden datový typ, který je nastaven při dimenzování pole. V poli však můžete použít vlastní datové typy, které jste sami navrhli. Mohli byste mít velmi komplikovanou strukturu pole pomocí vlastního datového typu, který má pod sebou několik vlastních datových typů. V kolekci můžete pro každou položku přidat datové typy použití. Můžete mít číselnou hodnotu, datum nebo řetězec - objekt kolekce převezme jakýkoli datový typ. Pokud byste se pokusili vložit hodnotu řetězce do pole, které bylo dimenzováno jako číselné, vygenerovalo by to chybovou zprávu.
- Sbírky se obecně používají snáze než pole. Pokud jde o kódování, když vytvoříte objekt kolekce, má pouze dvě metody (Přidat a Odebrat) a dvě vlastnosti (Počet a Položka), takže objekt není nijak složité programovat.
- Sbírky mohou k vyhledání dat používat klíče. Pole nemají tuto funkci a vyžadují smyčkový kód k iteraci pole k nalezení konkrétních hodnot.
- Velikost pole je třeba definovat při jeho prvním vytvoření. Musíte mít představu o tom, kolik dat bude ukládat. Pokud potřebujete zvětšit velikost pole, můžete použít „ReDim“ k jeho změně velikosti, ale pokud nechcete přijít o data již uložená v poli, musíte použít klíčové slovo „Zachovat“. Velikost kolekce není nutné definovat. Jak se položky přidávají nebo odebírají, automaticky se zvětšují a zmenšují.
Rozsah předmětu sbírky
Pokud jde o rozsah, objekt kolekce je k dispozici pouze tehdy, když je sešit otevřený. Při uložení sešitu se neuloží. Pokud sešit znovu otevřete, je třeba kolekci znovu vytvořit pomocí kódu VBA.
Pokud chcete, aby vaše kolekce byla k dispozici pro veškerý kód v modulu kódu, musíte deklarovat objekt kolekce v sekci Deklarace v horní části okna modulu
Tím zajistíte, že ke kolekci bude mít přístup celý váš kód v rámci tohoto modulu. Pokud chcete, aby jakýkoli modul v sešitu měl přístup ke kolekci, definujte ji jako globální objekt
| 1 | Globální kolekce MyCollection jako nová kolekce |
Vytváření kolekce, přidávání položek a přístup k položkám
Jednoduchý objekt kolekce lze vytvořit ve VBA pomocí následujícího kódu:
| 123456 | Sub CreateCollection ()Dim MyCollection jako nová kolekceMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"End Sub |
Kód dimenzuje nový objekt s názvem „MyCollection“ a poté následující řádky kódu použijí metodu Add k přidání 3 nových hodnot.
K přístupu k hodnotám pak můžete použít kód k iteraci vaší kolekce
| 123 | Pro každou položku v MyCollectionPoložka MsgBoxDalší položka |
Svou sbírku můžete také iterovat pomocí smyčky For Next Loop:
| 123 | Pro n = 1 do MyCollection.CountMsgBox MyCollection (n)Další n |
Kód získá velikost kolekce pomocí vlastnosti Count a poté použije tuto počáteční hodnotu 1 k indexování každé položky
Smyčka For Each Loop je rychlejší než smyčka For Next, ale funguje pouze v jednom směru (od nízkého indexu po vysoký). For For Loop má tu výhodu, že můžete použít jiný směr (vysoký index na nízký) a můžete také použít metodu Step pro změnu přírůstku. To je užitečné, když chcete odstranit několik položek, protože budete muset spustit odstranění od konce kolekce do začátku, protože index se bude měnit, jak bude mazání probíhat.
Metoda Add v kolekci má 3 volitelné parametry - Key, Before a After
Pomocí parametrů „Před“ a „Po“ můžete definovat polohu vaší nové položky vzhledem k ostatním, které již jsou ve sbírce
To se provádí zadáním indexového čísla, ke kterému má být vaše nová položka relativní.
| 123456 | Sub CreateCollection ()Dim MyCollection jako nová kolekceMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"End Sub |
V tomto příkladu bylo uvedeno, že ‘Item2’ bude přidáno před první indexovanou položku v kolekci (což je ‘Item1’). Když procházíte touto kolekcí, zobrazí se nejprve „Item2“, poté „Item1“ a „Item3“
Když zadáte parametr „Před“ nebo „Po“, hodnota indexu se v rámci kolekce automaticky upraví tak, že z položky „Item2“ se stane hodnota indexu 1 a položka „Item1“ se přesune na hodnotu indexu 2
Pomocí parametru „Klíč“ můžete také přidat referenční hodnotu, kterou můžete použít k identifikaci sbírkové položky. Všimněte si, že hodnota klíče musí být řetězec a musí být v rámci kolekce jedinečná.
| 1234567 | Sub CreateCollection ()Dim MyCollection jako nová kolekceMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")End Sub |
Položce „Item2“ byla přidělena „klíčová“ hodnota „MyKey“, takže na tuto položku můžete odkazovat pomocí hodnoty „MyKey“ namísto indexového čísla (2)
Hodnota „Klíč“ musí být řetězcová hodnota. Nemůže to být žádný jiný datový typ. Všimněte si, že kolekce je jen pro čtení a hodnotu klíče nelze aktualizovat, jakmile byla nastavena. Také nemůžete zkontrolovat, zda pro konkrétní položku v kolekci existuje hodnota klíče, ani zobrazit hodnotu klíče, což je trochu nevýhoda.
Parametr „Klíč“ má další výhodu v tom, že je váš kód čitelnější, zvláště pokud je předáván kolegovi na podporu, a nemusíte tuto hodnotu iterovat celou kolekcí. Představte si, že byste měli sbírku 10 000 položek, jak těžké by bylo odkazovat na jednu konkrétní položku!
Odebrání položky ze sbírky
K odstranění položek ze své sbírky můžete použít metodu „Odebrat“.
| 1 | MyCollection.Remove (2) |
Bohužel není snadné, pokud má kolekce velký počet položek, aby se vypracoval rejstřík položky, kterou chcete odstranit. Zde se parametr „Klíč“ hodí při vytváření kolekce
| 1 | MyCollection.Remove („MyKey“) |
Když je položka odebrána ze sbírky, hodnoty indexu se automaticky resetují po celou dobu kolekce. Zde je parametr „Klíč“ tak užitečný, když odstraňujete několik položek najednou. Můžete například odstranit index 105 položky a okamžitě se z indexu 106 106 stane index 105 a vše nad touto položkou má hodnotu indexu přesunutou dolů. Pokud použijete parametr Key, není třeba se starat o to, kterou hodnotu indexu je třeba odebrat.
Chcete -li odstranit všechny položky kolekce a vytvořit novou kolekci, použijte znovu příkaz Dim, který vytvoří prázdnou kolekci.
| 1 | Dim MyCollection jako nová kolekce |
Chcete -li úplně odebrat skutečný objekt kolekce, můžete objekt nastavit na nic
| 1 | Nastavit MyCollection = Nic |
To je užitečné, pokud již váš kód vaši kolekci nevyžaduje. Nastavením objektu kolekce na nic se odstraní veškerý odkaz na něj a uvolní se paměť, kterou používal. To může mít důležité důsledky pro rychlost provádění vašeho kódu, pokud v paměti již sedí velký objekt.
Spočítejte počet položek ve sbírce
Počet položek ve své sbírce můžete snadno zjistit pomocí vlastnosti „Počet“
| 1 | MsgBox MyCollection.Count |
Tuto vlastnost byste použili, pokud byste pro další smyčku používali iteraci prostřednictvím kolekce, protože vám poskytne horní limit pro indexové číslo.
Testujte kolekci na konkrétní hodnotu
Můžete iterovat prostřednictvím kolekce a hledat konkrétní hodnotu položky pomocí pro každou smyčku
| 123456789101112 | Kolekce dílčích vyhledávání ()Dim MyCollection jako nová kolekceMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pro každou položku v MyCollectionPokud Item = "Item2" PakPoložka MsgBox a „Nalezeno“Konec IfdalšíEnd Sub |
Kód vytvoří malou sbírku a poté ji iteruje hledáním položky s názvem ‘item2’. Pokud je nalezen, zobrazí se okno se zprávou, že našel konkrétní položku
Jednou z nevýhod této metodiky je, že nemůžete přistupovat k hodnotě indexu nebo hodnotě klíče
Pokud místo toho použijete smyčku For Next, můžete k získání hodnoty indexu použít čítač For Next, přestože stále nemůžete získat hodnotu „Key“
| 123456789101112 | Kolekce dílčích vyhledávání ()Dim MyCollection jako nová kolekceMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pro n = 1 do MyCollection.CountIf MyCollection.Item (n) = "Item2" ThenMsgBox MyCollection.Item (n) & "nalezeno na pozici indexu" & nKonec IfDalší nEnd Sub |
Počítadlo For Next (n) poskytne pozici indexu
Třídění sbírky
Neexistuje žádná vestavěná funkce pro třídění kolekce, ale pomocí myšlení „po vybalení z krabice“ lze napsat kód, který provede třídění, a to pomocí funkce řazení v listu aplikace Excel. Tento kód používá k samotnému třídění prázdný list s názvem „SortSheet“.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Kolekce podtřídy ()Dim MyCollection jako nová kolekceDim Counter jako dlouhý„Vytvořte sbírku s položkami náhodného pořadíMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"„Zachyťte počet položek ve sbírce pro budoucí použitíPočitadlo = MyCollection.Count„Opakujte procházením kolekce kopírováním každé položky do po sobě jdoucích buněk v„ SortSheet “(sloupec A)Pro n = 1 do MyCollection.CountListy ("SortSheet"). Buňky (n, 1) = MyCollection (n)Další n„Aktivujte třídicí list a použijte třídící rutinu aplikace Excel k seřazení dat vzestupněTabulky („SortSheet“). AktivujteRozsah ("A1: A" & MyCollection.Count). VyberteActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Klíč Sort.SortFields.Add2: = Rozsah (_"A1: A5"), SortOn: = xlSortOnValues, Objednávka: = xlAscending, DataOption: = _xlSortNormalS ActiveWorkbook.Worksheets ("SortSheet"). TříditRozsah SetRange ("A1: A5").Header = xlHádej.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplikovatKonec s„Odstraňte všechny položky ve sbírce - všimněte si, že tato smyčka For Next Loop běží v opačném pořadíPro n = MyCollection. Počítejte s 1 krokem -1MyCollection.Remove (n)Další n‘Zkopírujte hodnoty buňky zpět do prázdného sběrného objektu pomocí uložené hodnoty (čítače) pro smyčku‘Pro n = 1 do čítačeMyCollection.Add Sheets ("SortSheet"). Cells (n, 1) .ValueDalší n„Prohlédněte si sbírku, abyste dokázali, v jakém pořadí jsou položky nyníPro každou položku v MyCollectionPoložka MsgBoxDalší položka„Vymažte list (třídicí list) - v případě potřeby jej také odstraňteListy („SortSheet“). Rozsah (buňky (1, 1), buňky (čítač, 1)). VymazatEnd Sub |
Tento kód nejprve vytvoří kolekci s položkami přidanými v náhodném pořadí. Potom je zkopíruje do prvního sloupce na listu (SortSheet).
Kód poté pomocí funkce řazení v Excelu seřadí data ve sloupci vzestupně. Kód lze také upravit tak, aby byl seřazen sestupně.
Kolekce se poté vyprázdní dat pomocí smyčky For Next. Všimněte si toho, že možnost step se používá tak, aby se vymazala od konce kolekce do začátku. Důvodem je, že jak se vymaže, hodnoty indexu se resetují, pokud by se vymazaly od začátku, nevymaže se správně (index 2 by se stal indexem 1)
Nakonec pomocí jiného For Next Loop se hodnoty položek přenesou zpět do prázdné kolekce
Další pro každou smyčku dokazuje, že kolekce je nyní v dobrém vzestupném pořadí.
Bohužel se tím nezabývá žádné klíčové hodnoty, které mohly být původně zadány, protože klíčové hodnoty nelze přečíst
Předání sbírky dílčí / funkci
Kolekci lze předat dílčí nebo funkci stejným způsobem jako jakýkoli jiný parametr
| 1 | Funkce MyFunction (ByRef MyCollection jako kolekce) |
Je důležité předat sbírku pomocí „ByRef“. To znamená, že je použita původní kolekce. Pokud je kolekce předána pomocí „ByVal“, vytvoří se kopie kolekce, která může mít neblahé důsledky
Pokud je kopie vytvořena pomocí „ByVal“, pak se cokoli, co změní kolekci v rámci funkce, stane pouze na kopii, nikoli na originálu. Pokud je například do funkce přidána nová položka do kolekce, tato položka se v původní kolekci neobjeví, což vytvoří chybu ve vašem kódu.
Vrácení kolekce z funkce
Kolekci z funkce můžete vrátit stejným způsobem jako vrácení jakéhokoli objektu. Musíte použít klíčové slovo Nastavit
| 12345 | Dílčí návratová funkce ()Dim MyCollection jako kolekceNastavit MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Tento kód vytvoří dílčí rutinu, která vytvoří objekt s názvem „MyCollection“ a poté pomocí klíčového slova „Set“ efektivně zavolá funkci k naplnění této kolekce. Jakmile to bude hotové, zobrazí se okno se zprávou, které zobrazí počet 2 položek
| 1234567 | Funkce PopulateCollection () jako kolekceDim MyCollection jako nová kolekceMyCollection.Add "Item1"MyCollection.Add "Item2"Nastavit PopulateCollection = MyCollectionKoncová funkce |
Funkce PopulateCollection vytvoří nový sbírkový objekt a naplní jej 2 položkami. Poté předá tento objekt zpět do objektu kolekce vytvořeného v původní dílčí rutině.
Převod kolekce na pole
Možná budete chtít svou sbírku převést na pole. Možná budete chtít data uložit, kde je lze měnit a manipulovat s nimi. Tento kód vytvoří malou kolekci a poté ji přenese do pole
Všimněte si, že index kolekce začíná na 1, zatímco index pole začíná na 0. Zatímco kolekce obsahuje 3 položky, pole musí být dimenzováno pouze na 2, protože existuje prvek 0
| 1234567891011121314151617 | Dílčí ConvertCollectionToArray ()Dim MyCollection jako nová kolekceDim MyArray (2) jako řetězecMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pro n = 1 do MyCollection.CountMyArray (n - 1) = MyCollection (n)Další nPro n = 0 až 2MsgBox MyArray (n)Další nEnd Sub |
Převod pole na kolekci
Možná budete chtít převést pole na kolekci. Můžete například chtít přistupovat k datům rychleji a elegantněji než pomocí kódu k získání prvku pole.
Mějte na paměti, že to bude fungovat pouze pro jednu dimenzi pole, protože kolekce má pouze jednu dimenzi
| 123456789101112131415 | Dílčí ConvertArrayIntoCollection ()Dim MyCollection jako nová kolekceDim MyArray (2) jako řetězecMyArray (0) = "item1"MyArray (1) = "Item2"MyArray (2) = "Item3"Pro n = 0 až 2MyCollection.Add MyArray (n)Další nPro každou položku v MyCollectionPoložka MsgBoxDalší položkaEnd Sub |
Pokud byste chtěli použít vícerozměrné pole, mohli byste hodnoty pole spojit dohromady pro každý řádek v poli pomocí oddělovače mezi rozměry pole, takže při čtení hodnoty kolekce byste mohli programově použít znak oddělovače k oddělte hodnoty.
Data můžete také přesunout do kolekce na základě přidání hodnoty první dimenze (index 1) a poté přidání hodnoty další dimenze (index 2) atd.
Pokud by pole mělo, řekněme, 4 dimenze, každá čtvrtá hodnota v kolekci by byla novou sadou hodnot.
Můžete také přidat hodnoty pole, které chcete použít jako klíče (za předpokladu, že jsou jedinečné), což by přidalo snadný způsob lokalizace konkrétních dat.
