Zbirka je predmet, ki vsebuje številne podobne predmete. Do njih je enostavno dostopati in z njimi upravljati, tudi če je v zbirki veliko predmetov.
Z Excelom VBA že obstajajo vgrajene zbirke. Primer je zbirka Sheets. Za vsak delovni list v delovnem zvezku obstaja element v zbirki Sheets.
Vgrajene zbirke imajo na voljo veliko več lastnosti in metod, ki pa niso na voljo v vaših zbirkah, ki jih ustvarite.
Z zbirko lahko na primer pridobite informacije o določenem delovnem listu. Na primer, lahko vidite ime delovnega lista in tudi, ali je vidno ali ne. Z uporabo zanke For Each lahko potujete po vsakem delovnem listu v zbirki.
| 1234567 | Delovni listi podtesta ()Dim Sh kot delovni listZa vsak Sh In SheetsMsgBox Sh.NameMsgBox Sh.VisibleNaslednji ShEnd Sub |
Prav tako lahko naslovite določen delovni list v zbirki z uporabo vrednosti indeksa ali dejanskega imena delovnega lista:
| 12 | Listi MsgBox (1). ImeListi MsgBox ("List1"). Ime |
Ko se delovni listi dodajajo ali brišejo, se zbirka Preglednic povečuje ali zmanjšuje.
Upoštevajte, da se pri zbirkah VBA indeksna številka začne z 1 in ne z 0
Zbirke proti nizom
Nizovi in zbirke so po svojih funkcijah podobni, saj sta obe metodologiji, ki omogoča shranjevanje velike količine podatkov, na katere se nato lahko enostavno sklicujete s kodo. Imajo pa številne razlike v načinu delovanja:
- Nizovi so večdimenzionalni, zbirke pa so samo enorazsežne. Polje lahko dimenzionirate z več dimenzijami, npr.
| 1 | Dim MyArray (10, 2) kot niz |
Tako se ustvari niz 10 vrstic z 2 stolpcema, skoraj kot delovni list. Zbirka je dejansko en sam stolpec. Niz je uporaben, če morate shraniti številne podatke, ki se med seboj nanašajo, npr. ime in naslov. Ime bi bilo v prvi dimenziji matrike, naslov pa v drugi dimenziji.
- Ko napolnite polje, potrebujete ločeno vrstico kode, da vnesete vrednost v vsak element matrike. Če bi imeli dvodimenzionalno matriko, bi dejansko potrebovali 2 vrstici kode - eno vrstico za naslov prvega stolpca in eno vrstico za naslov drugega stolpca. S predmetom Zbiranje preprosto uporabite metodo Dodaj, tako da se novi element samo doda v zbirko, vrednost indeksa pa se samodejno prilagodi.
- Če morate izbrisati element podatkov, je v matriki bolj zapleteno. Vrednosti elementa lahko nastavite na prazno vrednost, vendar sam element še vedno obstaja v matriki. Če za ponavljanje po matriki uporabljate zanko For Next, bo zanka vrnila prazno vrednost, ki bo potrebovala kodiranje, da se prepriča, da se prazna vrednost ne upošteva. V zbirki uporabljate metode Dodaj ali Odstrani, za vse indeksiranje in spreminjanje velikosti pa se samodejno poskrbi. Odstranjeni predmet popolnoma izgine. Nizovi so uporabni za fiksno velikost podatkov, vendar so zbirke boljše za tiste, kjer se lahko količina podatkov spremeni.
- Zbirke so samo za branje, medtem ko je mogoče vrednosti matrike spremeniti z uporabo VBA. Z zbirko bi morali najprej odstraniti vrednost, ki jo želite spremeniti, in nato dodati novo spremenjeno vrednost.
- V matriki lahko za elemente uporabite samo en podatkovni tip, ki je nastavljen, ko matriko dimenzionirate. V matriki pa lahko uporabite vrste podatkov po meri, ki ste jih sami oblikovali. Lahko bi imeli zelo zapleteno strukturo matrike z uporabo podatkovnega tipa po meri, ki ima pod seboj več podatkovnih tipov po meri. V zbirki lahko za vsako postavko dodate podatkovne vrste uporabe. Lahko imate številsko vrednost, datum ali niz - predmet zbiranja bo prevzel kateri koli tip podatkov. Če bi v niz, ki je bil dimenzioniran kot številčen, poskušali vnesti vrednost niza, bi nastalo sporočilo o napaki.
- Zbirke so na splošno lažje za uporabo kot matrike. Kar zadeva kodiranje, ko ustvarite predmet zbiranja, ima le dva načina (dodajanje in odstranjevanje) in dve lastnosti (štetje in element), zato predmet nikakor ni zapleten za programiranje.
- Zbirke lahko uporabljajo ključe za iskanje podatkov. Nizki nimajo te funkcije in zahtevajo zanko za ponavljanje po matriki za iskanje določenih vrednosti.
- Velikost matrike je treba določiti, ko je prvič ustvarjena. Vedeti morate, koliko podatkov bo shranilo. Če morate povečati velikost matrike, jo lahko uporabite za spremembo velikosti z možnostjo »ReDim«, vendar morate uporabiti ključno besedo »Ohrani«, če ne želite izgubiti podatkov, ki so že shranjeni v matriki. Velikosti zbirke ni treba določiti. Ko dodajate ali odstranjujete predmete, samo raste in se skrči.
Področje uporabe zbirnega predmeta
Glede na obseg je predmet zbiranja na voljo le, ko je delovni zvezek odprt. Ko se shrani delovni zvezek, se ne shrani. Če se delovni zvezek znova odpre, je treba zbirko znova ustvariti s kodo VBA.
Če želite, da je vaša zbirka na voljo vsem kodam v vašem kodnem modulu, morate predmet zbiranja prijaviti v razdelku Razglasi na vrhu okna modula.
To bo zagotovilo, da lahko vsa vaša koda v tem modulu dostopa do zbirke. Če želite, da kateri koli modul v vašem delovnem zvezku dostopa do zbirke, jo definirajte kot globalni objekt
| 1 | Globalna zbirka MyCollection kot nova zbirka |
Ustvarjanje zbirke, dodajanje elementov in dostop do elementov
Preprost zbirni objekt lahko ustvarite v VBA z naslednjo kodo:
| 123456 | Sub CreateCollection ()Dim MyCollection kot nova zbirkaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"End Sub |
Koda meri nov objekt, imenovan "MyCollection", nato pa naslednje vrstice kode uporabljajo metodo Add za dodajanje 3 novih vrednosti.
Nato lahko uporabite kodo za ponovitev po zbirki za dostop do vrednosti
| 123 | Za vsak predmet v zbirki MyCollectionMsgBox elementNaslednji element |
Po zbirki lahko tudi pregledujete z naslednjo zanko:
| 123 | Za n = 1 Za MyCollection.CountMsgBox MyCollection (n)Naprej n |
Koda dobi velikost zbirke z uporabo lastnosti Count in nato uporabi to začetno vrednost 1 za indeksiranje vsakega elementa
For zanka zanj je hitrejša od zanke for next, vendar deluje samo v eno smer (od nizkega indeksa do visokega). Prednost zanke Za naslednjo zanko je, da lahko uporabite drugo smer (od visokega indeksa do nizkega), za spremembo prirasta pa lahko uporabite tudi metodo Step. To je uporabno, če želite izbrisati več elementov, saj boste morali brisanje zagnati od konca zbirke do začetka, saj se bo indeks med brisanjem spreminjal.
Način Dodajanje v zbirki ima 3 izbirne parametre - Ključ, Pred in Po
S parametri „Pred“ in „Po“ lahko določite položaj svojega novega predmeta glede na ostale, ki so že v zbirki
To naredite tako, da določite številko indeksa, na katero želite, da je vaša nova postavka relativna.
| 123456 | Sub CreateCollection ()Dim MyCollection kot nova zbirkaMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"End Sub |
V tem primeru je bilo določeno, da se pred prvo indeksirano postavko v zbirki (ki je "postavka 1") doda "element 2". Ko boste to zbirko prebrali, se bo najprej prikazal element „Element2“, nato pa „Item1“ in „Item3“
Ko podate parameter "Pred" ali "Po", se vrednost indeksa samodejno prilagodi v zbirki, tako da "postavka2" postane vrednost indeksa 1, "postavka 1" pa vrednost 2;
S parametrom 'Key' lahko dodate tudi referenčno vrednost, ki jo lahko uporabite za identifikacijo postavke zbirke. Upoštevajte, da mora biti vrednost ključa niz in edinstven v zbirki.
| 1234567 | Sub CreateCollection ()Dim MyCollection kot nova zbirkaMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")End Sub |
"Element2" je dobil vrednost "Key" za "MyKey", tako da se lahko na ta element sklicujete z vrednostjo "MyKey" namesto številke indeksa (2)
Upoštevajte, da mora biti vrednost "Key" vrednost niza. Ne more biti nobena druga vrsta podatkov. Upoštevajte, da je zbirka samo za branje in vrednosti ključa ne morete posodobiti, ko je nastavljena. Prav tako ne morete preveriti, ali obstaja vrednost ključa za določeno postavko v zbirki, ali si ogledati vrednosti ključa, kar je malo pomanjkljivost.
Parameter »ključ« ima dodatno prednost, da naredi vašo kodo bolj berljivo, še posebej, če jo predate kolegu v podporo in vam za iskanje te vrednosti ni treba ponavljati celotne zbirke. Predstavljajte si, če bi imeli zbirko 10.000 predmetov, kako težko bi se sklicevali na en poseben predmet!
Odstranjevanje predmeta iz zbirke
Za brisanje predmetov iz zbirke lahko uporabite metodo »Odstrani«.
| 1 | MyCollection.Remove (2) |
Na žalost ni enostavno, če ima zbirka veliko število elementov, da bi določili indeks predmeta, ki ga želite izbrisati. Tu je pri ustvarjanju zbirke uporaben parameter ‘Ključ’
| 1 | MyCollection.Remove (»MyKey«) |
Ko element odstranimo iz zbirke, se indeksne vrednosti samodejno ponastavijo do konca zbirke. Tukaj je parameter 'Ključ' tako uporaben, če izbrišete več elementov hkrati. Na primer, lahko izbrišete indeks 105 elementa in v trenutku indeks 106 postane indeks 105, vrednost indeksa vsega nad tem elementom pa se premakne navzdol. Če uporabljate parameter Key, vam ni treba skrbeti, katero vrednost indeksa je treba odstraniti.
Če želite izbrisati vse postavke zbirke in ustvariti novo zbirko, znova uporabite stavek Dim, ki ustvari prazno zbirko.
| 1 | Dim MyCollection kot nova zbirka |
Če želite v celoti odstraniti dejanski predmet zbiranja, ga lahko nastavite na nič
| 1 | Nastavi MyCollection = Nič |
To je uporabno, če zbirka po kodi ne zahteva več. Če objekt zbirke nastavite na nič, odstranite vse sklice nanj in sprostite pomnilnik, ki ga je uporabljal. To ima lahko pomembne posledice za hitrost izvajanja kode, če v pomnilniku sedi velik predmet, ki ni več potreben.
Preštejte število predmetov v zbirki
Število predmetov v zbirki lahko preprosto ugotovite z lastnostjo »Štej«
| 1 | MsgBox MyCollection.Count |
To lastnost bi uporabili, če bi za ponovitev po zbirki uporabljali For Next Loop, saj vam bo zagotovila zgornjo mejo za številko indeksa.
Testna zbirka za določeno vrednost
Po zbirki lahko ponavljate iskanje določene vrednosti za element z uporabo zanke za vsako zanko
| 123456789101112 | Sub SearchCollection ()Dim MyCollection kot nova zbirkaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Za vsak predmet v zbirki MyCollectionČe je Item = "Item2" PotemElement sporočila & "Najdeno"Konec ČeNaslednjiEnd Sub |
Koda ustvari majhno zbirko, nato pa po njej poišče element, imenovan "item2". Če ga najdete, prikaže polje s sporočilom, da je našel določeno postavko
Ena od pomanjkljivosti te metodologije je, da ne morete dostopati do vrednosti indeksa ali vrednosti ključa
Če namesto tega uporabite zanko For Next Loop, lahko uporabite števec For Next, da dobite vrednost indeksa, čeprav še vedno ne morete dobiti vrednosti "Key"
| 123456789101112 | Sub SearchCollection ()Dim MyCollection kot nova zbirkaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Za n = 1 Za MyCollection.CountČe je MyCollection.Item (n) = "Item2" PotemMsgBox MyCollection.Item (n) & "Najdeno na mestu indeksa" & nKonec ČeNaprej nEnd Sub |
Števec For Next (n) bo zagotovil položaj indeksa
Razvrščanje zbirke
Ni vgrajene funkcije za razvrščanje zbirke, vendar lahko z nekaj razmišljanja "izven škatle" kodo napišete za razvrščanje z uporabo funkcije razvrščanja Excelovega delovnega lista. Ta koda za prazno razvrščanje uporablja prazen delovni list, imenovan 'SortSheet'.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection kot nova zbirkaDim Counter As Long„Zgradite zbirko z naključnimi predmeti naročilaMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3""Zajemite število predmetov v zbirki za prihodnjo uporaboŠtevec = MyCollection.Count"Ponovite zbirko, kopirajte vsak element v zaporedno celico v" SortSheet "(stolpec A)Za n = 1 Za MyCollection.CountListi ("SortSheet"). Celice (n, 1) = MyCollection (n)Naprej n"Aktivirajte sortirnico in uporabite Excelovo rutino razvrščanja, da podatke razvrstite po naraščajočem vrstnem reduListi ("SortSheet"). AktivirajteObseg ("A1: A" & MyCollection.Count). IzberiteActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Vrstni red: = xlAscending, DataOption: = _xlSortNormalZ ActiveWorkbook.Worksheets ("SortSheet"). RazvrstiObseg .SetRange ("A1: A5").Header = xlGuess.MatchCase = Neresnično.Orientation = xlTopToBottom.SortMethod = xlPinYin.UveljaviKončaj s"Izbrišite vse elemente v zbirki - upoštevajte, da se ta vrstica For Next Loop izvaja v obratnem vrstnem reduZa n = MyCollection. Odštej do 1 koraka -1MyCollection.Remove (n)Naprej n"Kopirajte vrednosti celic nazaj v prazen objekt zbiranja s shranjeno vrednostjo (števec) za zanko"Za n = 1 Za števecMyCollection.Add Sheets ("SortSheet"). Celice (n, 1) .VrednostNaprej n"Ponovite zbirko, da dokažete vrstni red, v katerem so predmetiZa vsak predmet v zbirki MyCollectionMsgBox elementNaslednji element"Počisti delovni list (sortirni list) - če je potrebno, ga tudi izbrišiteListi ("SortSheet"). Obseg (celice (1, 1), celice (števec, 1)). PočistiEnd Sub |
Ta koda najprej ustvari zbirko z elementi, dodanimi v naključnem vrstnem redu. Nato jih kopira v prvi stolpec na delovnem listu (SortSheet).
Code nato uporabi funkcijo razvrščanja v Excelu za razvrščanje podatkov v stolpcu po naraščajočem vrstnem redu. Kodo je mogoče spremeniti tudi tako, da se razvrsti po padajočem vrstnem redu.
Zbirka se nato izprazni iz podatkov z zanko For Next Loop. Upoštevajte, da je možnost koraka uporabljena tako, da se počisti od konca zbirke do začetka. To je zato, ker se med brisanjem vrednosti indeksa ponastavijo, če se počisti od začetka, se ne bi pravilno počistil (indeks 2 bi postal indeks 1)
Nazadnje, z uporabo drugega For Next Loop, se vrednosti postavk prenesejo nazaj v prazno zbirko
Nadaljnja za vsako zanko dokazuje, da je zbirka zdaj v dobrem naraščajočem vrstnem redu.
Na žalost to ne obravnava nobenih vrednosti ključa, ki so bile morda prvotno vnesene, saj vrednosti ključev ni mogoče prebrati
Prenos zbirke na pod / funkcijo
Zbirko je mogoče posredovati pomožni ali funkciji na enak način kot kateri koli drug parameter
| 1 | Funkcija MyFunction (ByRef MyCollection kot zbirka) |
Pomembno je, da zbirko posredujete z „ByRef“. To pomeni, da se uporablja izvirna zbirka. Če se zbirka posreduje z uporabo »ByVal«, se s tem ustvari kopija zbirke, ki ima lahko žalostne posledice
Če je kopija ustvarjena z uporabo »ByVal«, se vse, kar spremeni zbirko znotraj funkcije, zgodi samo na kopiji in ne na izvirniku. Na primer, če je v funkciji v zbirko dodan nov element, se to ne bo pojavilo v izvirni zbirki, kar bo ustvarilo napako v vaši kodi.
Vračanje zbirke iz funkcije
Zbirko iz funkcije lahko vrnete na enak način kot vrnitev katerega koli predmeta. Uporabiti morate ključno besedo Set
| 12345 | Sub ReturnFromFunction ()Dim MyCollection As CollectionNastavi MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Ta koda ustvari podprogram, ki ustvari predmet, imenovan "MyCollection", nato pa uporabi ključno besedo "Set" za učinkovit klic funkcije, da se ta zbirka zapolni. Ko to storite, se prikaže okno s sporočilom, ki prikazuje število 2 elementov
| 1234567 | Funkcija PopulateCollection () kot zbirkaDim MyCollection kot nova zbirkaMyCollection.Add "Item1"MyCollection.Add "Item2"Nastavi PopulateCollection = Moja zbirkaKončana funkcija |
Funkcija PopulateCollection ustvari nov zbirni objekt in ga naseli z 2 elementi. Nato ta predmet pošlje nazaj v zbirni objekt, ustvarjen v prvotni podprogramu.
Pretvarjanje zbirke v niz
Zbirko boste morda želeli pretvoriti v matriko. Morda boste želeli shraniti podatke, kjer jih je mogoče spremeniti in z njimi upravljati. Ta koda ustvari majhno zbirko in jo nato prenese v matriko
Upoštevajte, da se indeks zbirke začne pri 1, indeks matrike pa pri 0. Medtem ko ima zbirka 3 elemente, je treba matriko dimenzionirati le na 2, ker obstaja element 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection kot nova zbirkaDim MyArray (2) Kot nizMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Za n = 1 Za MyCollection.CountMyArray (n - 1) = MyCollection (n)Naprej nZa n = 0 do 2MsgBox MyArray (n)Naprej nEnd Sub |
Pretvarjanje niza v zbirko
Morda boste želeli matriko pretvoriti v zbirko. Na primer, morda boste želeli dostopati do podatkov na hitrejši in elegantnejši način, kot da s kodo dobite element matrike.
Upoštevajte, da bo to delovalo samo za eno dimenzijo matrike, ker ima zbirka samo eno dimenzijo
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection kot nova zbirkaDim MyArray (2) Kot nizMyArray (0) = "element1"MyArray (1) = "Element2"MyArray (2) = "Element3"Za n = 0 do 2MyCollection.Add MyArray (n)Naprej nZa vsak predmet v zbirki MyCollectionMsgBox elementNaslednji elementEnd Sub |
Če ste vseeno želeli uporabiti večdimenzionalno matriko, lahko vrednosti matrike združite skupaj za vsako vrstico v nizu z uporabo razmejevalnika med dimenzijami matrike, tako da lahko pri branju vrednosti zbirke programsko uporabite znak razmejevalnika, da ločite vrednosti.
Podatke lahko premaknete tudi v zbirko na podlagi dodane vrednosti prve dimenzije (indeks 1), nato pa se doda naslednja vrednost razsežnosti (indeks 2) itd.
Če bi imelo polje, recimo, 4 dimenzije, bi bila vsaka četrta vrednost v zbirki nov niz vrednosti.
Dodate lahko tudi vrednosti matrike, ki jih uporabite kot ključe (pod pogojem, da so edinstvene), kar bi dodalo enostaven način iskanja določenih podatkov.
