Objekt ArrayList sličan je objektu Collection, ali ima daleko više metoda i svojstava, pa stoga i daleko veću fleksibilnost sa programskog gledišta.
Objekt zbirke ima samo dvije metode (dodavanje, uklanjanje) i dva svojstva (broj, stavka), dok popis polja ima mnogo više. Također, objekt Collection samo je za čitanje. Nakon dodavanja vrijednosti indeksirana vrijednost se ne može promijeniti, dok je na popisu polja moguće uređivanje.
Mnoge metode popisa polja koriste parametre. Za razliku od mnogih standardnih VBA metoda, nijedan od ovih parametara nije izborni. Također, neke od metoda i svojstava ne pišu uvijek velika slova kada se unesu na isti način kao u Excel VBA. Međutim, oni još uvijek rade.
Objekt ArrayList proširuje se i sužava u veličini prema broju stavki koje sadrži. Ne treba ga dimenzionirati prije uporabe poput niza.
Popis niza je jednodimenzionalan (isti kao objekt Zbirka), a zadana vrsta podataka je Variant, što znači da će prihvatiti bilo koju vrstu podataka, bilo da su to numerički, tekstualni ili datumski.
Na mnogo načina Array List rješava brojne nedostatke objekta Collection. Svakako je daleko fleksibilniji u onome što može učiniti.
Objekt Array List nije dio standardne VBA knjižnice. Možete ga koristiti u Excel VBA kodu pomoću kasnog ili ranog vezivanja
| 1234 | Sub LateBindingExample ()Zatamni MyList kao objektPostavi MyList = CreateObject ("System.Collections.ArrayList")Kraj podm |
| 123 | Sub EarlyBindingExample ()Zatamni MyList kao novi ArrayListKraj podm |
Da biste koristili primjer ranog vezivanja, prvo morate unijeti referencu u VBA na datoteku "mscorlib.tlb"
To činite odabirom ‘Alati | Reference ‘iz prozora uređivača Visual Basic (VBE). Pojavit će se skočni prozor sa svim dostupnim referencama. Pomaknite se dolje do "mscorlib.dll" i označite okvir pored njega. Pritisnite U redu i ta je knjižnica sada dio vašeg projekta:
Jedan od velikih nedostataka objekta Array List je to što nema 'Intellisense'. Obično, gdje koristite objekt u VBA-i, poput raspona, vidjet ćete skočni popis svih dostupnih svojstava i metoda. To ne dobivate pomoću objekta Array List, a ponekad je potrebna pažljiva provjera kako biste bili sigurni da ste ispravno napisali metodu ili svojstvo.
Također, ako pritisnete F2 u prozoru VBE i pretražite "arraylist", ništa se neće prikazati, što programerima nije od velike pomoći.
Vaš kôd će se izvesti znatno brže s ranim vezanjem, jer je sav unaprijed sastavljen. S kasnim vezanjem, objekt se mora sastaviti dok se kôd izvodi
Distribucija vaše Excel aplikacije koja sadrži popis polja
Kao što je već istaknuto, objekt ArrayList nije dio Excel VBA. To znači da bilo koji od vaših kolega kojima distribuirate aplikaciju mora imati pristup datoteci "mscorlib.tlb"
Ova datoteka se obično nalazi na:
C: \ Windows \ Microsoft.NET \ Framework \ v4.0.30319
Moglo bi biti vrijedno napisati neki kôd (pomoću Dir metode) da provjerite postoji li ova datoteka kada korisnik učita aplikaciju tako da doživi "meko slijetanje" ako nije pronađeno. Ako nije prisutan, a kôd radi, pojavit će se pogreške.
Također, korisnik mora imati instaliranu ispravnu .Net Framework verziju. Čak i ako korisnik ima noviju verziju, V3.5 mora biti instaliran inače vaša aplikacija neće raditi
Opseg objekta popisa polja
Što se tiče opsega, objekt Array List dostupan je samo dok je radna knjiga otvorena. Ne sprema se kada se spremi radna knjiga. Ako se radna knjiga ponovno otvori, tada je objekt Array List potrebno ponovno stvoriti pomoću VBA koda.
Ako želite da vaš popis polja bude dostupan cijelom kodu u vašem kodnom modulu, tada morate prijaviti objekt Popis niza u odjeljku Deklaracija na samom vrhu prozora modula
To će osigurati da sav vaš kôd unutar tog modula može pristupiti popisu niza. Ako želite da bilo koji modul u vašoj radnoj knjizi pristupa objektu Array List, tada ga definirajte kao globalni objekt
| 1 | Globalna zbirka MyCollection kao novi popis array |
Popunjavanje i čitanje s vašeg popisa polja
Najosnovnija radnja koju želite poduzeti je stvoriti popis polja, unijeti neke podatke u nju i zatim dokazati da se podaci mogu čitati. Svi primjeri koda u ovom članku pretpostavljaju da koristite rano vezanje i dodali ste "mscorlib.tlb" VBA referencama, kako je gore opisano
| 123456789101112 | Sub ArrayListExample ()'Izradi novi objekt popisa poljaZatamni MyList kao novi ArrayList„Dodavanje stavke na popisMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"'Ponovite popis nizova kako biste dokazali vrijednostiZa N = 0 na MyList.Count - 1Moj popis MsgBox (N)Sljedeći N.Kraj podm |
Ovaj primjer stvara novi objekt ArrayList, popunjava ga s 3 stavke i ponavlja kroz popis koji prikazuje svaku stavku.
Imajte na umu da indeks ArrayList počinje s 0, a ne s 1, pa morate oduzeti 1 od vrijednosti Count
Također možete koristiti petlju "Za … Svaki" za čitanje vrijednosti:
| 123456789101112 | Sub ArrayListExample ()'Izradi novi objekt popisa poljaZatamni MyList kao novi ArrayList'Dodajte stavke na popisMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"'Ponovite popis nizova kako biste dokazali vrijednostiZa svakoga na mojoj listiMsgBox ISljedeći jaKraj podm |
Uređivanje i mijenjanje stavki na popisu polja
Velika prednost popisa polja u odnosu na zbirku je ta što se stavke na popisu mogu uređivati i mijenjati unutar vašeg koda. Objekt Collection samo se čita, dok se objekt Array List čita / piše
| 123456789101112131415 | Sub ArrayListExample ()'Izradi novi objekt popisa poljaZatamni MyList kao novi ArrayList„Dodavanje stavke na popisMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3""Promijenite stavku 1 iz" Stavka 2 "u" Promijenjeno "MyList (1) = "Promijenjeno"‘Ponovite popis nizova kako biste dokazali da je promjena uspjelaZa svakoga na mojoj listi„Imena za prikaz opcijaMsgBox ISljedeći jaKraj podm |
U ovom primjeru druga stavka, "Stavka2" je promijenjena u vrijednost "Promijenjeno" (zapamtite da indeks počinje na 0). Kada se iteracija pokrene na kraju koda, prikazati će se nova vrijednost
Dodavanje niza vrijednosti na popis polja
Možete unijeti vrijednosti u svoj popis polja pomoću polja koje sadrži popis tih vrijednosti ili upućivanja na vrijednosti ćelija na radnom listu
| 123456789101112131415161718 | SubAdArrayExample ()‘Stvori objekt popisa poljaZatamni MyList kao novi ArrayList‘Ponavljati kroz vrijednosti niza dodajući ih na popis poljaZa svaki v nizu ("A1", "A2", "A3")'Dodajte svaku vrijednost niza na popisMyList.Add vSljedeći‘Ponavljajte kroz vrijednosti niza s referencama radnog lista dodajući ih na popis poljaZa svaki v In Array (raspon ("A5"). Vrijednost, raspon ("A6"). Vrijednost)MyList.Add vSljedeći'Ponovite popis nizova kako biste dokazali vrijednostiZa N = 0 na MyList.Count - 1‘Prikaz stavke popisaMsgBox MyList.Item (N)Sljedeći N.Kraj podm |
Čitanje / dohvaćanje niza stavki s popisa polja
Korištenjem metode GetRange na popisu nizova možete odrediti niz uzastopnih stavki koje ćete dohvatiti. Dva potrebna parametra su početni položaj indeksa i broj stavki koje je potrebno dohvatiti. Kôd popunjava drugi objekt Array List sa podskupom stavki koje se zatim mogu zasebno čitati.
| 123456789101112131415161718 | PodčitavanjeRangeExample ()„Definiranje objekataZatamni MyList kao novi ArrayList, MyList1 kao objekt'Dodajte stavke objektu' MyList 'MyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item6"MyList.Add "Item4"MyList.Add "Item7"'Snimite 4 stavke na' MyList 'počevši od indeksne pozicije 2Postavi MyList1 = MyList.GetRange (2, 4)'Ponovite objekt' MyList1 'za prikaz podskupa stavkiZa svakoga na mojoj listi1„Imena za prikaz opcijaMsgBox ISljedeći jaKraj podm |
Traženje stavki unutar popisa polja
Možete provjeriti je li imenovana stavka na vašem popisu pomoću metode ‘Sadrži’. Ovo će vratiti true ili false
| 1 | MsgBox MyList.Contains ("Item2") |
Stvarnu poziciju indeksa možete pronaći i pomoću metode 'IndexOf'. Morate navesti početni indeks za pretraživanje (obično 0). Povratna vrijednost je indeks prve instance pronađene stavke. Zatim možete koristiti petlju za promjenu početne točke na sljedeću vrijednost indeksa kako biste pronašli daljnje instance ako postoji nekoliko dupliciranih vrijednosti.
Ako vrijednost nije pronađena, vraća se vrijednost -1
Ovaj primjer demonstrira korištenje stavke "Sadrži", stavka nije pronađena i petlja kroz popis niza kako bi se pronašao položaj svih dupliciranih stavki:
| 1234567891011121314151617181920212223242526 | Sub SearchListExample ()'Definirajte popis polja i varijableDim MyList kao novi ArrayList, Sp kao cijeli broj, pozicija kao cijeli broj'Dodajte nove stavke, uključujući duplikatMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"‘Test za to da je“ Stavka2 ”na popisu - vraća TrueMsgBox MyList.Contains ("Item2")‘Dohvati indeks nepostojeće vrijednosti -vraća -1MsgBox MyList.IndexOf ("Stavka", 0)‘Postavite početni položaj pretraživanja na nuluSp = 0"Ponovite popis da biste dobili sve pozicije" stavke 1 "Čini"Dobijte indeksni položaj sljedeće" stavke1 "na temelju pozicije u varijabli" Sp "Pos = MyList.IndexOf ("Item1", Sp)"Ako se ne pronađu daljnje instance" stavke1 ", izađite iz petljeAko je Pos = -1, tada izađite iz Do‘Prikažite sljedeću pronađenu instancu i položaj indeksaMsgBox MyList (Pos) & "at index" & Pos‘Dodajte 1 posljednjoj pronađenoj vrijednosti indeksa - to sada postaje nova početna pozicija za sljedeće pretraživanjeSp = Pos + 1PetljaKraj podm |
Napominjemo da se u tekstu za pretraživanje koriste velika i mala slova te da se zamjenske kartice ne prihvaćaju.
Umetanje i uklanjanje stavki
Ako ne želite dodati svoje stavke na kraj popisa, možete ih umetnuti na određeno mjesto indeksa tako da se nova stavka nalazi na sredini popisa. Indeksni brojevi automatski će se prilagoditi za sljedeće stavke.
| 123456789101112131415 | Sub InsertExample ()‘Definiraj objekt popisa poljaZatamni MyList kao novi ArrayList'Dodajte stavke na popis poljaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"„Umetnite„ Stavka 6 “na položaj indeksa 2MyList.Insert 2, "Item6"‘Ponovite stavke na popisu polja kako biste prikazali novi redoslijed i položaj indeksaZa N = 0 na MyList.Count - 1MsgBox MyList (N) & "Index" & NSljedeći N.Kraj podm |
U ovom primjeru, "Stavka6" dodaje se na popis na poziciji indeksa 2, tako da se "stavka3" koja je bila na poziciji indeksa 2 sada pomiče na poziciju indeksa 3
Pojedinačna stavka može se ukloniti metodom ‘Ukloni’.
| 1 | MyList.Remove "Stavka" |
Imajte na umu da nema greške ako naziv stavke nije pronađen. Svi sljedeći indeksni brojevi bit će promijenjeni kako bi odgovarali uklanjanju.
Ako znate indeksni položaj stavke, možete koristiti metodu ‘RemoveAt’, npr.
| 1 | MyList.RemoveAt 2 |
Imajte na umu da će, ako je dani indeksni položaj veći od broja stavki na popisu polja, biti vraćena pogreška.
Raspon vrijednosti možete ukloniti s popisa pomoću metode ‘RemoveRange’. Parametri su početni indeks, a zatim broj stavki za uklanjanje, npr.
| 1 | MyList.RemoveRange 3, 2 |
Imajte na umu da ćete dobiti pogrešku u kodu ako je broj stavki pomaknut od početne vrijednosti veći od broja stavki na popisu polja.
I u metodama 'RemoveAt' i 'RemoveRange' bilo bi poželjno provjeriti jesu li navedeni indeksni brojevi veći od ukupnog broja stavki na popisu matrica kako bi se uhvatile moguće greške. Svojstvo 'Count' dat će ukupan broj stavki na popisu polja.
| 12345678910111213141516171819202122232425 | Sub RemoveExample ()‘Definiraj objekt popisa poljaZatamni MyList kao novi ArrayList'Dodajte stavke na popis poljaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"MyList.Add "Item4"MyList.Add "Item5"„Umetnite„ Stavka 6 “na položaj indeksa 2MyList.Insert 2, "Item6"'Ukloni' stavku 2 'MyList.Remove "Item2"‘Ukloni‘ stavku ’ - to ne postoji na popisu niza, ali ne griješiMyList.Remove "Stavka"‘Uklonite stavku na mjestu indeksa 2MyList.RemoveAt 2‘Uklonite 2 uzastopne stavke počevši od indeksne pozicije 2MyList.RemoveRange 3, 2‘Ponovite popis polja kako biste pokazali što je preostalo i u kojem se indeksnom položaju sada nalaziZa N = 0 na MyList.Count - 1MsgBox MyList (N) & "Index" & NSljedeći N.Kraj podm |
Imajte na umu da ako koristite "RemoveAt" za uklanjanje stavke na određenom mjestu, čim se ta stavka ukloni, mijenjaju se svi sljedeći položaji indeksa. Ako imate više uklanjanja pomoću indeksnog položaja, onda je dobra ideja započeti s najvećim brojem indeksa i zakoračiti prema dolje do nule, tako da ćete uvijek uklanjati ispravnu stavku. Na ovaj način nećete imati problema
Sortiranje popisa polja
Još jedna velika prednost u odnosu na zbirku je ta što stavke možete sortirati prema uzlaznom ili silaznom redoslijedu.
Objekt Array List jedini je objekt u Excelu VBA s metodom razvrstavanja. Način razvrstavanja je vrlo brz i to može biti važan faktor pri korištenju popisa polja.
U objektu zbirke bilo je potrebno neko razmišljanje "izvan kutije" za sortiranje svih stavki, ali s popisom polja to je vrlo jednostavno.
Metoda ‘Sortiraj’ sortira se uzlaznim redoslijedom, a metoda ‘Obrni’ sortira se silazno.
| 12345678910111213141516171819202122 | Sub ArrayListExample ()‘Napravi objekt Popisa nizaZatamni MyList kao novi ArrayList‘Dodajte stavke nerazvrstanim redoslijedomMyList.Add "Item1"MyList.Add "Item3"MyList.Add "Item2"‘Poredajte stavke uzlaznim redoslijedomMyList.Sort‘Ponovite stavke kako biste prikazali rastući redoslijedZa svakoga na mojoj listi‘Prikažite naziv stavkeMsgBox ISljedeći ja'Poredajte stavke prema opadajućem redoslijeduMyList.Reverse‘Ponovite stavke kako biste prikazali opadajući redoslijedZa svakoga na mojoj listi‘Prikažite naziv stavkeMsgBox ISljedeći jaKraj podm |
Kloniranje popisa niza
Popis niza ima mogućnost stvaranja klona ili kopije sebe. Ovo je korisno ako korisnik unese izmjene u stavke pomoću prednjeg kraja i vašeg VBA koda, ali morate kopiju stavki zadržati u izvornom stanju kao sigurnosnu kopiju.
To bi korisniku moglo omogućiti značajku "Poništi". Možda su izvršili izmjene i žele se vratiti na izvorni popis.
| 123456789101112131415 | Podklonski primjer ()‘Definirajte dva objekta - popis polja i objektZatamni MyList kao novi ArrayList, MyList1 kao objekt'Napuni prvi objekt stavkamaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"'Kopirajte moju listu na moju listu1Postavi MyList1 = MyList.Clone'Ponovite MyList1 kako biste dokazali kloniranjeZa svakoga na mojoj listi1‘Prikažite naziv stavkeMsgBox ISljedeći jaKraj podm |
'MyList1' sada sadrži sve stavke iz 'MyList' istim redoslijedom
Kopiranje popisa niza u konvencionalni objekt VBA niza
Možete koristiti jednostavnu metodu za kopiranje popisa niza u uobičajeni VBA niz:
| 123456789101112131415 | Primjer podmare ()'Stvorite objekt popisa polja i standardni objekt nizaDim MyList As New ArrayList, NewArray As Variant'Popuni popis polja stavkamaMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"'Kopirajte popis polja u novi nizNewArray = MyList.ToArray‘Iteracija kroz novi niz - imajte na umu da broj popisa polja pruža maksimalni indeksZa N = 0 na MyList.Count - 1‘Prikažite naziv stavkeMsgBox NewArray (N)Sljedeći N.Kraj podm |
Kopiranje niza popisa u raspon radnog lista
Popis polja možete kopirati na određeni radni list i referencu ćelije bez potrebe za ponavljanjem popisa polja. Morate navesti samo prvu referencu ćelije
| 123456789101112131415 | Primjer pod raspona ()'Izradi novi objekt popisa poljaZatamni MyList kao novi ArrayList'Dodajte stavke na popisMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Jasan ciljne listListovi ("List1"). UsedRange.Clear‘Kopirajte stavke u nizListovi ("List1"). Raspon ("A1"). Promijeni veličinu (1, MyList.Count) .Value = MyList.toArray‘Kopirajte stavke u stupacListovi ("List 1"). Raspon ("A5"). Promijeni veličinu (MyList.Count, 1) .Vrijednost = _Radni listFunction.Transpose (MyList.toArray)Kraj podm |
Ispraznite sve stavke s popisa polja
Postoji jednostavna funkcija (Clear) za potpuno brisanje popisa polja
| 1234567891011121314 | Sub ClearListExample ()'Izradi objekt popisa poljaZatamni MyList kao novi ArrayList‘Dodajte nove stavkeMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Prikaži broj stavkiMsgBox MyList.Count‘Obriši sve stavkeMyList.Clear'Pokažite broj stavki kako biste dokazali da je jasno funkcioniraloMsgBox MyList.CountKraj podm |
Ovaj primjer stvara stavke na popisu polja, a zatim briše popis polja. Okviri poruka dokazuju prije i poslije broj stavki na popisu polja.
Sažetak metoda metoda popisa niza za Excel VBA
| Zadatak | Parametri | Primjeri |
| Dodavanje / uređivanje stavke | Vrijednost | MyList.Add "Item1" |
| MyList (4) = “Stavka 2” | ||
| Klonirajte popis polja | Nijedan | Dim MyList As Object |
| Postavi MyList2 = MyList.Clone | ||
| Kopiraj u Array | Nijedan | Priguši MyArray kao varijantu |
| MyArray = MyList.ToArray | ||
| Kopiraj u raspon radnog lista (redak) | Nijedan | Listovi ("List1"). Raspon ("A1"). Promijeni veličinu (1, MyList.Count) .Value = MyList.ToArray |
| Kopiraj u raspon radnog lista (stupac) | Nijedan | Listovi ("List 1"). Raspon ("A3"). Promijeni veličinu (MyList.Count, 1) .Varue = WorksheetFunction.Transpose (MyList.ToArray) |
| Stvoriti | "System.Collections.ArrayList" | Dim MyList As Object |
| Postavi MyList = CreateObject ("System.Collections.ArrayList") | ||
| Proglasiti | N/A | Dim MyList As Object |
| Pronađite / provjerite postoji li stavka | Stavka za pronaći | MyList.Contains ("Stavka2") |
| Pronađite poziciju stavke na ArrayList -u | 1. Stavka za pronaći. | Indeks zatamnjenjaNe tako dugo |
| 2. Položaj s kojeg možete započeti pretraživanje. | IndexNo = MyList.IndexOf ("Stavka 3", 0) | |
| IndexNo = MyList.IndexOf ("Stavka 5", 3) | ||
| Dobijte broj stavki | Nijedan | MsgBox MyList.Count |
| Umetnite stavku | 1. Indeks - položaj za umetanje. | MyList.Insert 0, "Item5" |
| 2 Vrijednost - objekt ili vrijednost za umetanje. | MyList.Insert 4, "Stavka 7" | |
| Pročitajte stavku | Indeks - dugi cijeli broj | MsgBox MyList.Item (0) |
| MsgBox MyList.Item (4) | ||
| Pročitajte zadnju dodanu stavku | Indeks - dugi cijeli broj | MsgBox MyList.Item (list.Count - 1) |
| Prvo pročitajte stavku | Indeks - dugi cijeli broj | MsgBox MyList.Item (0) |
| Pročitajte sve stavke (za svaku) | N/A | Zatamnjeni element kao varijanta |
| Za svaki element u MyList | ||
| MsgBox element | ||
| Sljedeći element | ||
| Pročitajte sve stavke (za) | Indeks - dugi cijeli broj | Dim i As Long |
| Za i = 0 na MyList.Count - 1 | ||
| MsgBox i | ||
| Sljedeći i | ||
| Uklonite sve stavke | Nijedan | MyList.Clear |
| Uklonite stavku na mjestu | Indeksna pozicija na kojoj se nalazi stavka | MyList.RemoveAt 5 |
| Uklonite stavku po imenu | Stavka za uklanjanje s popisa ArrayList | MyList.Remove "Item3" |
| Uklonite niz stavki | 1. Indeks - početna pozicija. | MyList.RemoveRange 4,3 |
| 2. Brojanje - broj stavki za uklanjanje. | ||
| Poredaj opadajućim redoslijedom | Nijedan | MyList.Reverse |
| Poredajte uzlaznim redoslijedom | Ne | MyList.Sort |
