Zbirka je objekt koji sadrži brojne slične stavke. Njima se lako može pristupiti i njima se može manipulirati, čak i ako se u zbirci nalazi veliki broj predmeta.
Već postoje ugrađene zbirke s Excelom VBA. Primjer je zbirka Sheets. Za svaki radni list u radnoj knjizi postoji stavka u zbirci Sheets.
Ugrađene zbirke imaju daleko više dostupnih svojstava i metoda, ali one nisu dostupne u vašim vlastitim zbirkama koje sami stvarate.
Na primjer, zbirku možete koristiti za dobivanje informacija o određenom radnom listu. Na primjer, možete vidjeti naziv radnog lista te je li vidljiv ili ne. Korištenjem petlje Za svaku, možete proći kroz svaki radni list u zbirci.
| 1234567 | Radni listovi za podtestove ()Dim Sh As Radni listZa svaki Sh In SheetsMsgBox Sh.NameMsgBox Sh.VisibleSljedeći ShKraj podm |
Također se možete obratiti određenom radnom listu u zbirci koristeći vrijednost indeksa ili stvarni naziv radnog lista:
| 12 | MsgBox listovi (1). NazivMsgBox listovi ("List1"). Naziv |
Kako se radni listovi dodaju ili brišu, tako se zbirka Tablica povećava ili smanjuje u veličini.
Imajte na umu da kod VBA zbirki broj indeksa počinje s 1, a ne s 0
Zbirke u odnosu na nizove
Nizovi i zbirke slični su po svojim funkcijama po tome što su obje metodologije koje omogućuju pohranu velike količine podataka na koje se zatim može lako uputiti pomoću koda. Međutim, oni imaju niz razlika u načinu rada:
- Nizovi su višedimenzionalni, dok su zbirke samo jedne dimenzije. Možete dimenzionirati niz s nekoliko dimenzija, npr.
| 1 | Dim MyArray (10, 2) kao niz |
Time se stvara niz od 10 redaka s 2 stupca, gotovo poput radnog lista. Zbirka je zapravo jedan stupac. Niz je koristan ako trebate pohraniti brojne stavke podataka koje se međusobno odnose, npr. ime i adresu. Naziv bi bio u prvoj dimenziji niza, a adresa u drugoj dimenziji.
- Kada popunite svoj niz, potreban vam je zasebni redak koda da biste unijeli vrijednost u svaki element niza. Da imate dvodimenzionalni niz, zapravo bi vam trebala 2 retka koda - jedan redak za adresiranje prvog stupca i jedan redak za adresiranje drugog stupca. S objektom Zbirka jednostavno koristite metodu Dodaj tako da se nova stavka samo dodaje u zbirku, a vrijednost indeksa automatski prilagođava.
- Ako trebate izbrisati stavku podataka, to je složenije u nizu. Vrijednosti elementa možete postaviti na praznu vrijednost, ali sam element i dalje postoji unutar niza. Ako za ponavljanje kroz niz koristite petlju For Next, petlja će vratiti praznu vrijednost, što će zahtijevati kodiranje kako bi se osiguralo da se prazna vrijednost zanemaruje. U zbirci koristite metode Dodaj ili Ukloni, a za sve indeksiranje i promjenu veličine automatski se brine. Uklonjena stavka potpuno nestaje. Nizovi su korisni za fiksnu veličinu podataka, ali zbirke su bolje za mjesta gdje se količina podataka može promijeniti.
- Zbirke su samo za čitanje, dok se vrijednosti polja mogu promijeniti pomoću VBA. S zbirkom biste morali prvo ukloniti vrijednost koju želite promijeniti, a zatim dodati novu promijenjenu vrijednost.
- U nizu možete koristiti samo jednu vrstu podataka za elemente koji se postavljaju pri dimenzioniranju niza. Međutim, u nizu možete koristiti prilagođene vrste podataka koje ste sami osmislili. Mogli biste imati vrlo kompliciranu strukturu polja koristeći prilagođeni tip podataka koji zauzvrat ima nekoliko prilagođenih vrsta podataka. U zbirku možete dodati korisničke vrste podataka za svaku stavku. Mogli biste imati numeričku vrijednost, datum ili niz - objekt zbirke će uzeti bilo koju vrstu podataka. Ako pokušate unijeti vrijednost niza u niz koji je dimenzioniran kao numerički, to će proizvesti poruku o pogrešci.
- Zbirke su općenito lakše koristiti od niza. U smislu kodiranja, kada kreirate objekt zbirke, on ima samo dvije metode (dodavanje i uklanjanje) i dva svojstva (brojanje i stavka), pa objekt nije kompliciran za programiranje.
- Zbirke mogu koristiti ključeve za lociranje podataka. Nizovi nemaju tu funkciju i zahtijevaju petlju koda za ponavljanje kroz niz radi pronalaženja određenih vrijednosti.
- Veličinu niza potrebno je definirati pri prvom stvaranju. Morate imati predodžbu o tome koliko će podataka pohraniti. Ako trebate povećati veličinu niza, možete upotrijebiti 'ReDim' za njegovu ponovnu veličinu, ali morate upotrijebiti ključnu riječ 'Očuvaj' ako ne želite izgubiti podatke koji se već drže u polju. Veličinu zbirke nije potrebno definirati. Samo raste i automatski se smanjuje kako se stavke dodaju ili uklanjaju.
Opseg objekta zbirke
Što se tiče opsega, objekt zbirke dostupan je samo dok je radna knjiga otvorena. Ne sprema se kada se spremi radna knjiga. Ako se radna knjiga ponovno otvori, zbirku je potrebno ponovno stvoriti pomoću VBA koda.
Ako želite da vaša zbirka bude dostupna cijelom kodu u vašem kodnom modulu, tada morate deklarirati objekt zbirke u odjeljku Declare na vrhu prozora modula
To će osigurati da sav vaš kôd unutar tog modula može pristupiti zbirci. Ako želite da bilo koji modul u vašoj radnoj knjizi pristupa zbirci, definirajte je kao globalni objekt
| 1 | Globalna zbirka MyCollection kao nova zbirka |
Stvaranje zbirke, dodavanje stavki i pristup stavkama
Jednostavan objekt zbirke može se stvoriti u VBA pomoću sljedećeg koda:
| 123456 | Sub CreateCollection ()Dim MyCollection kao nova kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Kraj podm |
Kôd dimenzionira novi objekt pod nazivom "MyCollection", a zatim sljedeći retci koda koriste metodu Add za dodavanje 3 nove vrijednosti.
Zatim možete koristiti kôd za ponavljanje zbirke za pristup vrijednostima
| 123 | Za svaku stavku u zbirci MyCollectionStavka msgBoxSljedeća stavka |
Također možete ponavljati svoju zbirku pomoću For For Loop:
| 123 | Za n = 1 Za MyCollection.CountMsgBox MyCollection (n)Dalje n |
Kôd dobiva veličinu zbirke pomoću svojstva Count, a zatim koristi ovu početnu vrijednost 1 za indeksiranje svake stavke
For Every Loop je brži od For Next petlje, ali radi samo u jednom smjeru (od niskog indeksa do visokog). Petlja For Next ima prednost što možete koristiti drugačiji smjer (od visokog indeksa do niskog), a također možete koristiti i metodu Step za promjenu prirasta. Ovo je korisno kada želite izbrisati nekoliko stavki jer ćete brisanje morati pokrenuti od kraja zbirke do početka jer će se indeks mijenjati kako se brisanja događaju.
Metoda Dodavanje u zbirci ima 3 izborna parametra - Ključ, Prije i Nakon
Pomoću parametara ‘Prije’ i ‘Poslije’ možete definirati položaj svoje nove stavke u odnosu na ostale koji su već u zbirci
To se postiže navođenjem broja indeksa za koji želite da vaša nova stavka bude relativna.
| 123456 | Sub CreateCollection ()Dim MyCollection kao nova kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"Kraj podm |
U ovom primjeru "Stavka2" je specificirano za dodavanje prije prve indeksirane stavke u zbirci (koja je "Stavka1"). Prilikom ponovnog kretanja kroz ovu zbirku pojavit će se prije svega "Stavka2", a zatim "Stavka1" i "Stavka3"
Kad navedete parametre "Prije" ili "Nakon", vrijednost indeksa automatski se prilagođava unutar zbirke tako da "Stavka2" postaje vrijednost indeksa 1, a "Stavka1" premještena na vrijednost indeksa 2
Također možete upotrijebiti parametar "ključ" za dodavanje referentne vrijednosti koju možete koristiti za identifikaciju stavke zbirke. Imajte na umu da vrijednost ključa mora biti niz i mora biti jedinstvena unutar zbirke.
| 1234567 | Sub CreateCollection ()Dim MyCollection kao nova kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection ("MyKey")Kraj podm |
Stavka "Stavka 2" dobila je vrijednost "Ključ" od "MyKey" tako da se možete pozivati na tu stavku koristeći vrijednost "MyKey" umjesto broja indeksa (2)
Imajte na umu da vrijednost "Ključ" mora biti vrijednost niza. To ne može biti bilo koja druga vrsta podataka. Imajte na umu da je zbirka samo za čitanje i da ne možete ažurirati vrijednost ključa nakon što je postavljena. Također, ne možete provjeriti postoji li vrijednost ključa za određenu stavku u zbirci niti pregledati vrijednost ključa što je pomalo nedostatak.
Parametar ‘Key’ ima dodatnu prednost što vaš kôd čini čitljivijim, osobito ako se predaje kolegi na podršku, a ne morate ponavljati cijelu zbirku da biste pronašli tu vrijednost. Zamislite da imate zbirku od 10.000 predmeta koliko bi bilo teško uputiti se na jednu određenu stavku!
Uklanjanje stavke iz zbirke
Za brisanje stavki iz zbirke možete koristiti metodu ‘Ukloni’.
| 1 | MyCollection.Remove (2) |
Nažalost, nije lako ako zbirka ima veliki broj stavki za izračun indeksa stavke koju želite izbrisati. Ovdje parametar ‘Key’ dobro dođe pri stvaranju zbirke
| 1 | MyCollection.Remove ("MyKey") |
Kad se stavka ukloni iz zbirke, vrijednosti indeksa automatski se poništavaju cijelom zbirkom. Ovdje je parametar "ključ" toliko koristan kada brišete nekoliko stavki odjednom. Na primjer, mogli biste izbrisati indeks stavke 105 i odmah indeks stavke 106 postaje indeks 105, a vrijednost indeksa svega iznad ove stavke pomaknuta je prema dolje. Ako koristite parametar Key, ne morate brinuti koja vrijednost indeksa treba biti uklonjena.
Da biste izbrisali sve stavke zbirke i stvorili novu zbirku, ponovno koristite izraz Dim koji stvara praznu zbirku.
| 1 | Dim MyCollection kao nova kolekcija |
Da biste potpuno uklonili stvarni objekt zbirke, možete postaviti objekt na ništa
| 1 | Postavi MyCollection = Ništa |
To je korisno ako vaš kôd zbirku više ne zahtijeva. Postavljanje objekta zbirke na ništa uklanja sve reference na njega i oslobađa memoriju koju je koristio. To može imati važne posljedice na brzinu izvršavanja vašeg koda, ako veliki objekt sjedi u memoriji koji više nije potreban.
Izbrojite broj predmeta u zbirci
Broj stavki u vašoj zbirci možete jednostavno saznati pomoću svojstva "Broj"
| 1 | MsgBox MyCollection.Count |
Ovo biste svojstvo upotrijebili da koristite For Next Loop za ponavljanje kroz zbirku jer će vam pružiti gornju granicu za broj indeksa.
Zbirka testova za određenu vrijednost
Možete ponavljati zbirku da biste potražili određenu vrijednost za stavku pomoću petlje Za svaku petlju
| 123456789101112 | Sub SearchCollection ()Dim MyCollection kao nova kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Za svaku stavku u zbirci MyCollectionAko je Item = "Item2" TadaStavka MsgBox & "Pronađeno"Završi akoSljedećiKraj podm |
Kôd stvara malu zbirku, a zatim ponavlja kroz nju tražeći stavku pod nazivom "item2". Ako se pronađe, prikazuje okvir s porukom da je pronašao određenu stavku
Jedan od nedostataka ove metodologije je što ne možete pristupiti vrijednosti indeksa ili vrijednosti ključa
Ako umjesto toga koristite For Next Loop, možete upotrijebiti brojač For Next za dobivanje vrijednosti indeksa, iako još uvijek ne možete dobiti vrijednost "Key"
| 123456789101112 | Sub SearchCollection ()Dim MyCollection kao nova kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Za n = 1 Za MyCollection.CountAko je MyCollection.Item (n) = "Item2" ZatimMsgBox MyCollection.Item (n) & "pronađeno na poziciji indeksa" & nZavrši akoDalje nKraj podm |
Brojač Za sljedeće (n) osigurat će položaj indeksa
Sortiranje zbirke
Ne postoji ugrađena funkcija za razvrstavanje zbirki, ali pomoću nekog "out of the box" razmišljanja kôd se može napisati za sortiranje pomoću Excel-ove funkcije razvrstavanja radnog lista. Ovaj kôd koristi prazan radni list pod nazivom "SortSheet" za stvarno sortiranje.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection kao nova kolekcijaDim Counter As Long'Izgradite zbirku nasumičnim stavkama narudžbeMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"‘Snimite broj stavki u zbirci za buduću uporabuBrojač = MyCollection.Count„Ponavljajte kroz zbirku kopirajući svaku stavku u uzastopnu ćeliju na„ SortSheet “(stupac A)Za n = 1 Za MyCollection.CountListovi ("SortSheet"). Ćelije (n, 1) = MyCollection (n)Dalje n'Aktivirajte tablicu za sortiranje i upotrijebite Excel rutinu sortiranja za sortiranje podataka u rastućem redoslijeduTablice ("SortSheet"). AktivirajteRaspon ("A1: A" & MyCollection.Count). OdaberiteActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sortiraj list"). Sortiraj.SortFields.Add2 ključ: = Raspon (_"A1: A5"), SortOn: = xlSortOnValues, Redoslijed: = xlAscending, DataOption: = _xlSortNormalS ActiveWorkbook.Worksheets ("SortSheet"). Sortiraj.Raspon raspona ("A1: A5").Header = xlPogađanje.MatchCase = Netačno.Orijentacija = xlTopToBottom.SortMethod = xlPinYin.Prijavi seZavrši s‘Izbrišite sve stavke u zbirci - imajte na umu da se ova For Next Loop izvodi obrnutim redoslijedomZa n = MyCollection.Obratite do 1 koraka -1MyCollection.Remove (n)Dalje n'Kopirajte vrijednosti ćelije natrag u prazan objekt prikupljanja pomoću pohranjene vrijednosti (Brojač) za' petlju 'Za n = 1 Za odbrojavanjeMyCollection.Add Sheets ("SortSheet"). Cells (n, 1) .VrijednostDalje n'Ponovite zbirku kako biste dokazali redoslijed u kojem se sada nalaze artikliZa svaku stavku u zbirci MyCollectionStavka msgBoxSljedeća stavka‘Očistite radni list (sortsheet) - ako je potrebno, izbrišite i njegaListovi ("SortSheet"). Raspon (ćelije (1, 1), ćelije (brojač, 1)). ObrišiKraj podm |
Ovaj kôd najprije stvara zbirku s stavkama dodanim nasumičnim redoslijedom. Zatim ih kopira u prvi stupac na radnom listu (SortSheet).
Code zatim koristi funkciju sortiranja programa Excel za sortiranje podataka u stupcu u rastućem redoslijedu. Kôd se također može promijeniti tako da se sortira prema opadajućem redoslijedu.
Zbirka se zatim isprazni iz podataka pomoću For Next Loop. Imajte na umu da se korak opcija koristi tako da se briše od kraja zbirke do početka. To je zato što se tijekom brisanja vrijednosti indeksa resetiraju, ako se očisti od početka, ne bi se očistio ispravno (indeks 2 postao bi indeks 1)
Konačno, pomoću druge For Next Loop, vrijednosti stavki se prenose natrag u praznu zbirku
Daljnja za svaku petlju dokazuje da je zbirka sada u dobrom uzlaznom redoslijedu.
Nažalost, ovo se ne odnosi na vrijednosti ključa koje su možda izvorno unesene, budući da se vrijednosti ključa ne mogu čitati
Prosljeđivanje zbirke pod -funkciji
Zbirka se može proslijediti pod -funkciji na isti način kao i bilo koji drugi parametar
| 1 | Funkcija MyFunction (ByRef MyCollection kao zbirka) |
Važno je proslijediti zbirku koristeći 'ByRef'. To znači da se koristi izvorna zbirka. Ako se zbirka prosljeđuje pomoću 'ByVal -a', tada se stvara kopija zbirke koja može imati nesretne posljedice
Ako se kopija stvori pomoću "ByVal -a", sve što mijenja zbirku unutar funkcije događa se samo na kopiji, a ne i na izvorniku. Na primjer, ako je unutar funkcije nova stavka dodana u zbirku, to se neće pojaviti u izvornoj zbirci, što će stvoriti grešku u vašem kodu.
Vraćanje zbirke iz funkcije
Zbirku možete vratiti iz funkcije na isti način kao i bilo koji objekt. Morate koristiti ključnu riječ Set
| 12345 | Sub ReturnFromFunction ()Dim MyCollection As CollectionPostavi MyCollection = PopulateCollectionMsgBox MyCollection.CountKraj podm |
Ovaj kôd stvara potprogram koji stvara objekt pod nazivom "MyCollection", a zatim koristi ključnu riječ "Set" za učinkovito pozivanje funkcije za popunjavanje te zbirke. Nakon što to učinite, prikazuje se okvir s porukom koji prikazuje broj od 2 stavke
| 1234567 | Funkcija PopulateCollection () kao zbirkaDim MyCollection kao nova kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"Postavi PopulateCollection = Moja zbirkaZavršna funkcija |
Funkcija PopulateCollection stvara novi objekt zbirke i popunjava ga s 2 stavke. Zatim prosljeđuje ovaj objekt natrag u objekt prikupljanja kreiran u izvornoj podrutini.
Pretvaranje zbirke u niz
Možda ćete svoju zbirku htjeti pretvoriti u niz. Možda ćete htjeti pohraniti podatke na kojima se mogu mijenjati i manipulirati njima. Ovaj kôd stvara malu zbirku, a zatim je prenosi u niz
Uočite da indeks zbirke počinje s 1, dok indeks niza počinje s 0. Dok zbirka ima 3 stavke, niz je potrebno dimenzionirati samo na 2 jer postoji element 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection kao nova kolekcijaDim MyArray (2) Kao nizMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Za n = 1 Za MyCollection.CountMyArray (n - 1) = MyCollection (n)Dalje nZa n = 0 do 2MsgBox MyArray (n)Dalje nKraj podm |
Pretvaranje niza u zbirku
Možda biste željeli pretvoriti niz u zbirku. Na primjer, možda ćete htjeti pristupiti podacima na brži i elegantniji način pomoću koda za dobivanje elementa niza.
Imajte na umu da će ovo funkcionirati samo za jednu dimenziju niza jer zbirka ima samo jednu dimenziju
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection kao nova kolekcijaDim MyArray (2) Kao nizMyArray (0) = "item1"MyArray (1) = "Stavka 2"MyArray (2) = "Stavka 3"Za n = 0 do 2MyCollection.Add MyArray (n)Dalje nZa svaku stavku u zbirci MyCollectionStavka msgBoxSljedeća stavkaKraj podm |
Ako ste ipak htjeli koristiti višedimenzionalni niz, mogli biste zajedno spojiti vrijednosti niza za svaki redak unutar niza pomoću znaka razdjelnika između dimenzija niza, tako da pri čitanju vrijednosti zbirke možete programski koristiti znak razgraničenja za izdvojiti vrijednosti.
Također možete premjestiti podatke u zbirku na temelju toga što se dodaje vrijednost prve dimenzije (indeks 1), a zatim se dodaje vrijednost sljedeće dimenzije (indeks 2) itd.
Da niz ima, recimo, 4 dimenzije, svaka četvrta vrijednost u zbirci bila bi novi skup vrijednosti.
Također biste mogli dodati vrijednosti niza koje ćete koristiti kao ključeve (pod uvjetom da su jedinstveni) što bi dodalo jednostavan način lociranja određenih podataka.
