Kolekcia je predmet, ktorý obsahuje množstvo podobných predmetov. K nim je možné ľahko pristupovať a manipulovať s nimi, aj keď sa v zbierke nachádza veľký počet položiek.
V programe Excel VBA už sú vstavané kolekcie. Príkladom je kolekcia Sheets. Pre každý pracovný list v zošite existuje položka v zbierke Tabuľky.
Vstavané kolekcie majú k dispozícii oveľa viac vlastností a metód, ktoré však nie sú k dispozícii vo vašich vlastných kolekciách, ktoré vytvoríte.
Kolekciu môžete napríklad použiť na získanie informácií o konkrétnom pracovnom hárku. Môžete napríklad vidieť názov pracovného hárka a tiež to, či je viditeľný alebo nie. Použitím cyklu For Each môžete iterovať každý pracovný hárok v zbierke.
| 1234567 | Vedľajšie testovacie pracovné hárky ()Pracovný list Dim Sh AsZa každý listMsgBox Sh.MenoMsgBox Sh.ViditeľnéĎalej ShKoniec pod |
Môžete tiež adresovať konkrétny pracovný hárok v kolekcii pomocou hodnoty indexu alebo skutočného názvu pracovného hárka:
| 12 | Hárky MsgBox (1). NázovTabuľky MsgBox („List1“). Názov |
Keď sa pracovné hárky pridávajú alebo odstraňujú, zbierka hárkov sa zväčšuje alebo zmenšuje.
Upozorňujeme, že pri zbierkach VBA začína indexové číslo 1, nie 0
Zbierky versus polia
Polia a zbierky majú podobné funkcie v tom, že sú to metodiky, ktoré umožňujú uložiť veľké množstvo údajov, na ktoré je potom možné ľahko odkazovať pomocou kódu. Majú však niekoľko rozdielov v spôsobe fungovania:
- Polia sú viacrozmerné, zatiaľ čo zbierky sú iba jednorozmerné. Pole môžete dimenzovať niekoľkými rozmermi, napr.
| 1 | Dim MyArray (10, 2) ako reťazec |
To vytvorí pole 10 riadkov s 2 stĺpcami, takmer ako pracovný hárok. Kolekcia je v skutočnosti jediným stĺpcom. Pole je užitočné, ak potrebujete uložiť niekoľko položiek údajov, ktoré navzájom súvisia, napr. meno a adresa. Názov by bol v prvej dimenzii poľa a adresa v druhej dimenzii.
- Keď naplníte svoje pole, budete potrebovať samostatný riadok kódu na vloženie hodnoty do každého prvku poľa. Ak by ste mali dvojrozmerné pole, v skutočnosti by ste potrebovali 2 riadky kódu - jeden riadok na adresovanie prvého stĺpca a jeden riadok na adresovanie druhého stĺpca. Pri objekte Collection jednoducho použijete metódu Add, aby sa nová položka práve pridala do zbierky a hodnota indexu sa automaticky upravila tak, aby vyhovovala.
- Ak potrebujete odstrániť položku údajov, potom je to v poli komplikovanejšie. Hodnoty prvku môžete nastaviť na prázdnu hodnotu, ale samotný prvok v poli stále existuje. Ak na opakovanie poľa použijete slučku For Next, slučka vráti prázdnu hodnotu, ktorá bude potrebovať kódovanie, aby sa zaistilo, že prázdna hodnota bude ignorovaná. V kolekcii používate metódy Pridať alebo Odstrániť a o všetky indexovanie a zmenu veľkosti sa automaticky postará. Odstránená položka úplne zmizne. Polia sú užitočné pre pevnú veľkosť údajov, ale zbierky sú lepšie tam, kde sa môže meniť množstvo údajov.
- Kolekcie sú len na čítanie, zatiaľ čo hodnoty polí je možné zmeniť pomocou VBA. Pri kolekcii by ste museli najskôr odstrániť hodnotu, ktorá sa má zmeniť, a potom pridať novú zmenenú hodnotu.
- V poli môžete pre prvky použiť iba jeden typ údajov, ktorý je nastavený pri dimenzovaní poľa. V poli však môžete použiť vlastné typy údajov, ktoré ste sami navrhli. Mohli by ste mať veľmi komplikovanú štruktúru polí pomocou vlastného dátového typu, ktorý zase má pod sebou niekoľko vlastných dátových typov. V zbierke môžete pre každú položku pridať dátové typy použitia. Môžete mať číselnú hodnotu, dátum alebo reťazec - objekt zbierky bude mať akýkoľvek typ údajov. Ak by ste sa pokúsili vložiť hodnotu reťazca do poľa, ktoré by bolo dimenzované ako číselné, zobrazilo by sa chybové hlásenie.
- Zbery sa vo všeobecnosti používajú jednoduchšie ako polia. Pokiaľ ide o kódovanie, keď vytvoríte objekt zbierky, má iba dve metódy (Pridať a Odstrániť) a dve vlastnosti (Počet a Položka), takže program nie je nijako zložité na programovanie.
- Zbierky môžu na vyhľadanie údajov použiť kľúče. Polia nemajú túto funkciu a vyžadujú cyklický kód na opakovanie poľa na nájdenie konkrétnych hodnôt.
- Pri prvom vytváraní poľa je potrebné definovať jeho veľkosť. Musíte mať predstavu o tom, koľko dát bude ukladať. Ak potrebujete zväčšiť pole, môžete ho zmeniť pomocou „ReDim“, ale ak nechcete prísť o údaje už uložené v poli, musíte použiť kľúčové slovo „zachovať“. Veľkosť kolekcie nie je potrebné definovať. Pri pridávaní alebo odstraňovaní položiek sa automaticky zväčšuje a zmenšuje.
Rozsah zbierkového predmetu
Pokiaľ ide o rozsah, predmet zbierky je k dispozícii iba vtedy, keď je zošit otvorený. Neuloží sa, keď je uložený zošit. Ak sa zošit znova otvorí, potom je potrebné kolekciu znova vytvoriť pomocou kódu VBA.
Ak chcete, aby bola vaša zbierka dostupná pre všetok kód vo vašom module kódu, musíte deklarovať objekt zbierky v sekcii Deklarovať v hornej časti okna modulu.
Tým sa zaistí, že všetok váš kód v rámci tohto modulu bude mať prístup k zbierke. Ak chcete, aby k modulu mal prístup akýkoľvek modul zošita, definujte ho ako globálny objekt
| 1 | Globálna zbierka MyCollection ako nová kolekcia |
Vytvorenie zbierky, pridávanie položiek a prístup k položkám
Jednoduchý objekt zbierky je možné vytvoriť vo VBA pomocou nasledujúceho kódu:
| 123456 | Sub CreateCollection ()Dim MyCollection ako nová kolekciaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Koniec pod |
Kód dimenzuje nový objekt s názvom „MyCollection“ a potom nasledujúce riadky kódu použijú metódu Add na pridanie 3 nových hodnôt.
Potom môžete použiť kód na iteráciu prostredníctvom svojej zbierky na prístup k hodnotám
| 123 | Pre každú položku v MyCollectionPoložka MsgBoxNasledujúca položka |
Svoju zbierku môžete tiež iterovať pomocou slučky For Next Loop:
| 123 | Pre n = 1 do MyCollection.CountMsgBox MyCollection (n)Ďalej n |
Kód získa veľkosť zbierky pomocou vlastnosti Count a potom použije túto počiatočnú hodnotu 1 na indexovanie každej položky
For For Loop je rýchlejší ako For Next loop, ale funguje iba v jednom smere (nízky index na vysoký). Slučka For Next Loop má výhodu v tom, že môžete použiť iný smer (vysoký index na nízky) a na zmenu prírastku môžete použiť aj metódu Step. Je to užitočné, keď chcete odstrániť niekoľko položiek, pretože budete musieť spustiť vymazanie od konca zbierky po začiatok, pretože index sa bude pri odstraňovaní meniť.
Metóda Add v kolekcii má 3 voliteľné parametre - Key, Before a After
Parametre „Pred“ a „Po“ môžete použiť na definovanie polohy vašej novej položky voči ostatným, ktoré už sú v kolekcii
To sa vykoná zadaním indexového čísla, ku ktorému má byť vaša nová položka relatívna.
| 123456 | Sub CreateCollection ()Dim MyCollection ako nová kolekciaMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"Koniec pod |
V tomto prípade bolo špecifikované, že „Item2“ sa má pridať pred prvú indexovanú položku v kolekcii (ktorou je „Item1“). Keď prechádzate touto zbierkou, zobrazí sa ako prvá položka „Item2“, za ktorou nasledujú „Item1“ a „Item3“
Keď zadáte parameter „Pred“ alebo „Po“, hodnota indexu sa v rámci kolekcie automaticky upraví tak, že z položky „Item2“ sa stane hodnota indexu 1 a položka „Item1“ sa presunie na hodnotu indexu 2.
Parameter „Kľúč“ môžete použiť aj na pridanie referenčnej hodnoty, ktorú môžete použiť na identifikáciu zbierkového predmetu. Upozorňujeme, že hodnota kľúča musí byť reťazec a musí byť v rámci kolekcie jedinečná.
| 1234567 | Sub CreateCollection ()Dim MyCollection ako nová kolekciaMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")Koniec pod |
Položke „Item2“ bola priradená „kľúčová“ hodnota „MyKey“, aby ste sa na ňu mohli odvolávať pomocou hodnoty „MyKey“ namiesto indexového čísla (2)
Upozorňujeme, že hodnota „Kľúč“ musí byť reťazcová hodnota. Nemôže to byť žiadny iný typ údajov. Upozorňujeme, že kolekcia je iba na čítanie a hodnotu kľúča nemôžete aktualizovať, keď bola nastavená. Tiež nemôžete skontrolovať, či pre konkrétnu položku v kolekcii existuje hodnota kľúča, alebo zobraziť hodnotu kľúča, ktorá je trochu nevýhodou.
Parameter „Kľúč“ má ďalšiu výhodu v tom, že je váš kód čitateľnejší, najmä ak ho odovzdávate kolegovi na podporu a na nájdenie tejto hodnoty nemusíte iterovať celú kolekciu. Predstavte si, že by ste mali zbierku 10 000 položiek, aké ťažké by bolo odkázať na jednu konkrétnu položku!
Odstránenie položky zo zbierky
Na odstránenie položiek zo svojej zbierky môžete použiť metódu „Odstrániť“.
| 1 | MyCollection.Remove (2) |
Bohužiaľ nie je jednoduché, ak má zbierka veľkého počtu položiek vypracovanie indexu položky, ktorú chcete odstrániť. Tu sa parameter „Kľúč“ hodí pri vytváraní kolekcie
| 1 | MyCollection.Remove („MyKey“) |
Keď je položka odstránená zo zbierky, hodnoty indexu sa automaticky resetujú po celej kolekcii. Tu je parameter „Kľúč“ taký užitočný, keď odstraňujete niekoľko položiek naraz. Môžete napríklad odstrániť index 105 položky a z indexu 106 položky sa okamžite stane index 105 a všetko nad touto položkou bude mať svoju indexovú hodnotu posunutú nadol. Ak použijete parameter Key, nie je potrebné sa zaoberať tým, ktorú hodnotu indexu je potrebné odstrániť.
Ak chcete odstrániť všetky položky kolekcie a vytvoriť novú kolekciu, znova použite príkaz Dim, ktorý vytvorí prázdnu kolekciu.
| 1 | Dim MyCollection ako nová kolekcia |
Ak chcete úplne odstrániť skutočný objekt zbierky, môžete objekt nastaviť na nič
| 1 | Nastaviť moju zbierku = nič |
Je to užitočné, ak váš kód kolekciu už nevyžaduje. Ak nič nezmeníte, objekt zbierky odstráni všetky odkazy naň a uvoľní pamäť, ktorú používal. To môže mať dôležité dôsledky na rýchlosť vykonávania vášho kódu, ak v pamäti už sedí veľký objekt.
Spočítajte počet položiek v zbierke
Počet položiek vo svojej zbierke môžete ľahko zistiť pomocou vlastnosti „Počet“
| 1 | MsgBox MyCollection.Count |
Túto vlastnosť by ste použili, ak by ste na opakovanie zbierky použili slučku For Next Loop, pretože vám poskytne hornú hranicu indexového čísla.
Testujte zbierku na konkrétnu hodnotu
Môžete iterovať prostredníctvom zbierky a vyhľadať konkrétnu hodnotu položky pomocou položky Pre každú slučku
| 123456789101112 | Kolekcia čiastkových vyhľadávaní ()Dim MyCollection ako nová kolekciaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pre každú položku v MyCollectionAk Item = "Item2" PotomPoložka MsgBox a „Nájdené“Koniec AkĎalšieKoniec pod |
Kód vytvorí malú zbierku a potom sa v nej iteruje a hľadá položku s názvom ‘item2’. Ak je nájdený, zobrazí sa okno so správou, že našiel konkrétnu položku
Jednou z nevýhod tejto metodiky je, že nemôžete získať prístup k hodnote indexu ani k hodnote kľúča
Ak namiesto toho použijete slučku For Next, môžete na získanie hodnoty indexu použiť počítadlo For Next, aj keď stále nemôžete získať hodnotu „Key“
| 123456789101112 | Kolekcia čiastkových vyhľadávaní ()Dim MyCollection ako nová kolekciaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pre n = 1 do MyCollection.CountAk MyCollection.Item (n) = "Item2" PotomMsgBox MyCollection.Item (n) & "nájdené na pozícii indexu" & nKoniec AkĎalej nKoniec pod |
Počítadlo For Next (n) poskytne pozíciu indexu
Triedenie zbierky
Neexistuje žiadna vstavaná funkcia na triedenie zbierky, ale pomocou myslenia „vybaleného z krabice“ je možné napísať kód na vykonanie triedenia pomocou funkcie triedenia pracovného hárka programu Excel. Tento kód používa na skutočné triedenie prázdny pracovný hárok s názvom „SortSheet“.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Podskupina zberu ()Dim MyCollection ako nová kolekciaDim Counter ako dlhý„Vytvorte zbierku s položkami náhodného poradiaMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"„Zachyťte počet zbieraných predmetov na budúce použitiePočítadlo = MyCollection.Count„Opakujte v kolekcii a skopírujte každú položku do po sebe idúcej bunky v„ SortSheet “(stĺpec A)Pre n = 1 do MyCollection.CountTabuľky ("SortSheet"). Bunky (n, 1) = Moja zbierka (n)Ďalej n„Aktivujte triediaci hárok a pomocou rutiny triedenia v Exceli zoraďte údaje vzostupneTabuľky („SortSheet“). AktivujteRozsah („A1: A“ & MyCollection.Count). VyberteActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Kľúč Sort.SortFields.Add2: = rozsah (_"A1: A5"), SortOn: = xlSortOnValues, Poradie: = xlAscending, DataOption: = _xlSortNormalS ActiveWorkbook.Worksheets ("SortSheet"). ZoradiťRozsah SetRange ("A1: A5").Header = xlHádaj.MatchCase = False.Orientácia = xlTopToBottom.Trieda = xlPinYin.PoužiťUkončiť s„Odstráňte všetky položky v zbierke - všimnite si, že táto slučka For Next Loop prebieha v opačnom poradíPre n = MyCollection. Počítajte s 1 krokom -1MyCollection.Remove (n)Ďalej n„Skopírujte hodnoty buniek späť do prázdneho zberného objektu pomocou uloženej hodnoty (počítadlo) pre slučku„Pre n = 1 do počítadlaMyCollection.Add Sheets ("SortSheet"). Bunky (n, 1). HodnotaĎalej n„Zopakujte zbierku, aby ste zistili, v akom stave sú položkyPre každú položku v MyCollectionPoložka MsgBoxNasledujúca položka„Vymažte pracovný hárok (triediaci hárok) - v prípade potreby ho tiež odstráňteTabuľky („SortSheet“). Rozsah (bunky (1, 1), bunky (počítadlo, 1)). VymazaťKoniec pod |
Tento kód najskôr vytvorí zbierku s položkami pridanými v náhodnom poradí. Potom ich skopíruje do prvého stĺpca na pracovnom hárku (SortSheet).
Kód potom pomocou funkcie zoradenia v Exceli zoradí údaje v stĺpci vzostupne. Kód je možné tiež upraviť tak, aby bol zoradený zostupne.
Zhromaždenie sa potom zbaví údajov pomocou slučky For Next Loop. Všimnite si toho, že možnosť step sa používa tak, že sa vymaže od konca zbierky do začiatku. Je to preto, že ako sa vymaže, hodnoty indexu sa vynulujú, ak by sa vymazali od začiatku, nevymazali by sa správne (z indexu 2 by sa stal index 1)
Nakoniec pomocou ďalšej položky For Next Loop sa hodnoty položiek prenesú späť do prázdnej zbierky
A For For Loop dokazuje, že zbierka je teraz v dobrom vzostupnom poradí.
Toto sa bohužiaľ nezaoberá akýmikoľvek kľúčovými hodnotami, ktoré mohli byť pôvodne zadané, pretože kľúčové hodnoty nie je možné načítať
Odovzdanie zbierky podfunkcii / funkcii
Kolekciu je možné odovzdať podskupine alebo funkcii rovnakým spôsobom ako akýkoľvek iný parameter
| 1 | Funkcia MyFunction (ByRef MyCollection ako kolekcia) |
Je dôležité odovzdať zbierku pomocou programu „ByRef“. To znamená, že je použitá pôvodná zbierka. Ak je zbierka odovzdaná pomocou „ByVal“, potom sa vytvorí kópia zbierky, ktorá môže mať nešťastné dôsledky
Ak je kópia vytvorená pomocou „ByVal“, čokoľvek, čo zmení kolekciu v rámci funkcie, sa stane iba na kópii, a nie na origináli. Ak je napríklad v rámci funkcie do zbierky pridaná nová položka, táto položka sa v pôvodnej kolekcii nezobrazí, čím sa vo vašom kóde vytvorí chyba.
Vrátenie zbierky z funkcie
Kolekciu z funkcie môžete vrátiť rovnakým spôsobom ako vrátenie akéhokoľvek objektu. Musíte použiť kľúčové slovo Nastaviť
| 12345 | Sub ReturnFromFunction ()Dim MyCollection ako kolekciaNastaviť MyCollection = PopulateCollectionMsgBox MyCollection.CountKoniec pod |
Tento kód vytvorí čiastkovú rutinu, ktorá vytvorí objekt s názvom „MyCollection“ a potom pomocou kľúčového slova „Set“ efektívne zavolá funkciu na naplnenie tejto zbierky. Hneď ako to urobíte, zobrazí sa okno so správou, v ktorej sa zobrazí počet 2 položiek
| 1234567 | Funkcia PopulateCollection () ako kolekciaDim MyCollection ako nová kolekciaMyCollection.Add "Item1"MyCollection.Add "Item2"Nastaviť PopulateCollection = MyCollectionKoncová funkcia |
Funkcia PopulateCollection vytvorí nový zbierkový objekt a naplní ho 2 položkami. Potom tento objekt odošle späť do zberného objektu vytvoreného v pôvodnej čiastkovej rutine.
Konverzia zbierky na pole
Možno budete chcieť svoju zbierku previesť na pole. Možno budete chcieť uložiť údaje tam, kde ich je možné meniť a manipulovať s nimi. Tento kód vytvorí malú zbierku a potom ju prenesie do poľa
Všimnite si, že index zbierky začína na 1, zatiaľ čo index poľa začína na 0. Zatiaľ čo kolekcia obsahuje 3 položky, pole musí byť dimenzované iba na 2, pretože existuje prvok 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection ako nová kolekciaDim MyArray (2) ako reťazecMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pre n = 1 do MyCollection.CountMyArray (n - 1) = MyCollection (n)Ďalej nPre n = 0 až 2MsgBox MyArray (n)Ďalej nKoniec pod |
Konvertovanie poľa na zbierku
Možno budete chcieť previesť pole na kolekciu. Môžete napríklad chcieť pristupovať k údajom rýchlejšie a elegantnejšie ako pomocou kódu na získanie prvku poľa.
Majte na pamäti, že to bude fungovať iba pre jednu dimenziu poľa, pretože kolekcia má iba jednu dimenziu
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection ako nová kolekciaDim MyArray (2) ako reťazecMyArray (0) = "item1"MyArray (1) = "Položka2"MyArray (2) = "Položka3"Pre n = 0 až 2MyCollection.Add MyArray (n)Ďalej nPre každú položku v MyCollectionPoložka MsgBoxNasledujúca položkaKoniec pod |
Ak by ste chceli použiť viacrozmerné pole, mohli by ste hodnoty polí zoskupiť spoločne pre každý riadok v poli pomocou oddeľovača medzi rozmermi poľa, takže pri čítaní hodnoty kolekcie by ste mohli programovo použiť oddeľovač na oddeľte hodnoty.
Údaje môžete tiež presunúť do zbierky na základe toho, že sa pridá hodnota prvej dimenzie (index 1) a potom sa pridá hodnota ďalšej dimenzie (index 2) atď.
Ak by pole malo povedzme 4 rozmery, každá štvrtá hodnota v kolekcii by bola novou sadou hodnôt.
Môžete tiež pridať hodnoty poľa, ktoré sa použijú ako kľúče (za predpokladu, že sú jedinečné), čo by pridalo ľahký spôsob lokalizácie konkrétnych údajov.
