Et ArrayList -objekt ligner et samlingsobjekt, men det har langt flere metoder og egenskaper, og derfor langt større fleksibilitet fra et programmeringssynspunkt.
Et samlingsobjekt har bare to metoder (Legg til, fjern) og to egenskaper (telle, element), mens en matriseliste har mange flere. Samlingsobjektet er også skrivebeskyttet. Når verdier er lagt til, kan den indekserte verdien ikke endres, mens redigering er mulig på en matrilliste.
Mange av Array List -metodene bruker parametere. I motsetning til mange av de vanlige VBA -metodene, er ingen av disse parameterne valgfrie. Noen av metodene og egenskapene bruker ikke alltid store bokstaver når de legges inn på samme måte som de gjør i Excel VBA. Imidlertid fungerer de fortsatt.
ArrayList -objektet utvides og trekker seg sammen i størrelse i henhold til hvor mange elementer det inneholder. Den trenger ikke å være dimensjonert før bruk som en matrise.
Array List er endimensjonal (samme som Collection -objektet), og standard datatype er Variant, noe som betyr at den godtar alle typer data, enten det er numerisk, tekst eller dato.
På mange måter adresserer matriselisten en rekke mangler ved samlingsobjektet. Det er absolutt langt mer fleksibelt i hva det kan gjøre.
Array List -objektet er ikke en del av det vanlige VBA -biblioteket. Du kan bruke den i Excel VBA -koden ved å bruke sen eller tidlig binding
| 1234 | Sub LateBindingExample ()Dim MyList som objektSett MyList = CreateObject ("System.Collections.ArrayList")Slutt Sub |
| 123 | Sub EarlyBindingExample ()Dim MyList As New ArrayListSlutt Sub |
For å bruke det tidlige bindingseksemplet, må du først skrive inn en referanse i VBA til filen ‘mscorlib.tlb’
Du gjør dette ved å velge ‘Verktøy | Referanser ‘fra Visual Basic Editor (VBE) -vinduet. Et popup-vindu vil vises med alle tilgjengelige referanser. Rull ned til ‘mscorlib.dll’ og merk av i boksen ved siden av. Klikk OK, og biblioteket er nå en del av prosjektet ditt:
En av de store ulempene med et Array List -objekt er at det ikke har ‘Intellisense’. Normalt, der du bruker et objekt i VBA, for eksempel et område, vil du se en popup-liste over alle tilgjengelige egenskaper og metoder. Du får ikke dette med et Array List -objekt, og det må noen ganger nøye kontrolleres for å være sikker på at du har stavet metoden eller eiendommen riktig.
Hvis du trykker på F2 i VBE -vinduet og søker på "arraylist", vil ingenting bli vist, noe som ikke er veldig nyttig for en utvikler.
Koden din vil kjøre betydelig raskere med tidlig binding, fordi den er samlet på forhånd. Ved sen binding må objektet kompileres mens koden kjører
Distribuere Excel -applikasjonen din som inneholder en matriseliste
Som allerede påpekt, er ArrayList -objektet ikke en del av Excel VBA. Dette betyr at alle dine kolleger som du distribuerer programmet til må ha tilgang til filen ‘mscorlib.tlb’
Denne filen er vanligvis plassert i:
C: \ Windows \ Microsoft.NET \ Framework \ v4.0.30319
Det kan være verdt å skrive noen kode (ved bruk av Dir -metoden) for å kontrollere at denne filen finnes når en bruker laster inn programmet slik at de opplever en "myk landing" hvis den ikke blir funnet. Hvis den ikke er tilstede, og koden kjører, vil det oppstå feil.
Brukeren må også ha riktig .Net Framework -versjon installert. Selv om brukeren har en senere versjon, må V3.5 installeres, ellers fungerer ikke programmet
Omfanget av et Array List -objekt
Når det gjelder omfang, er Array List -objektet bare tilgjengelig mens arbeidsboken er åpen. Den blir ikke lagret når arbeidsboken er lagret. Hvis arbeidsboken åpnes på nytt, må Array List-objektet gjenopprettes ved hjelp av VBA-kode.
Hvis du vil at Array List skal være tilgjengelig for all koden i kodemodulen, må du deklarere Array List -objektet i Deklarere -delen helt øverst i modulvinduet
Dette vil sikre at all koden din i den modulen har tilgang til matriselisten. Hvis du vil at en hvilken som helst modul i arbeidsboken din skal få tilgang til Array List -objektet, må du definere den som et globalt objekt
| 1 | Global MyCollection As New ArrayList |
Befolkning og lesing fra matriselisten din
Den mest grunnleggende handlingen du vil gjøre er å lage en matriseliste, legge inn noen data i den og deretter bevise at dataene kan leses. Alle kodeeksemplene i denne artikkelen antar at du bruker tidlig binding, og har lagt til ‘mscorlib.tlb’ i VBA -referansene, som beskrevet ovenfor
| 123456789101112 | Sub ArrayListExample ()‘Lag et nytt matriselisteobjektDim MyList As New ArrayList"Legg til elementer i listenMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3""Skift gjennom matriselisten for å bevise verdierFor N = 0 til MyList.Count - 1MsgBox MyList (N)Neste NSlutt Sub |
Dette eksemplet oppretter et nytt ArrayList -objekt, fyller det ut med 3 elementer, og det gjentas gjennom listen som viser hvert element.
Vær oppmerksom på at ArrayList -indeksen starter på 0, ikke 1, så du må trekke 1 fra Count -verdien
Du kan også bruke en "For … Hver" sløyfe for å lese verdiene:
| 123456789101112 | Sub ArrayListExample ()‘Lag et nytt matriselisteobjektDim MyList As New ArrayList"Legg til elementer i listenMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3""Skift gjennom matriselisten for å bevise verdierFor hver I In MyListMsgBox INeste jegSlutt Sub |
Redigere og endre elementer i en matriseliste
En stor fordel med en matriseliste fremfor en samling er at elementene i listen kan redigeres og endres i koden din. Samlingsobjektet er skrivebeskyttet mens Array List -objektet leses / skrives
| 123456789101112131415 | Sub ArrayListExample ()‘Lag et nytt matriselisteobjektDim MyList As New ArrayList"Legg til elementer i listenMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3""Endre element 1 fra" Item2 "til" Endret "MyList (1) = "Endret""Skift gjennom matriselisten for å bevise at endringen fungerteFor hver I In MyList"Vis elementnavnMsgBox INeste jegSlutt Sub |
I dette eksemplet endres det andre elementet, ‘Item2’ til verdien ‘Endret’ (husk at indeksen starter på 0). Når iterasjonen kjøres på slutten av koden, vil den nye verdien vises
Legge til et matrisearray til en matriseliste
Du kan angi verdier i matriselisten ved å bruke en matrise som inneholder en liste over disse verdiene eller referanser til celleverdier i et regneark
| 123456789101112131415161718 | Sub AddArrayExample ()‘Lag Array list -objektDim MyList As New ArrayList'Iterere gjennom matriseverdier og legge dem til matriselistenFor hver v i matrise ("A1", "A2", "A3")'Legg til hver matrisverdi i listenMyList.Add vNeste'Gjenta gjennom matrisverdier med regnearkreferanser som legger dem til matriselistenFor hver v i matrise (område ("A5"). Verdi, område ("A6"). Verdi)MyList.Add vNeste"Skift gjennom matriselisten for å bevise verdierFor N = 0 til MyList.Count - 1'Vis listeelementMsgBox MyList.Item (N)Neste NSlutt Sub |
Lese / hente en rekke elementer fra en matriseliste
Ved å bruke GetRange -metoden på en matrilliste kan du angi et raseri av påfølgende elementer som skal hentes. De to parameterne som kreves er startindeksposisjonen og antall elementer som skal hentes. Koden fyller ut et andre Array List -objekt med delsettet med elementer som deretter kan leses separat.
| 123456789101112131415161718 | Sub ReadRangeExample ()"Definer objekterDim MyList As New ArrayList, MyList1 As Object"Legg til elementer i" MyList "-objektetMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3"MyList.Legg til "Item6"MyList.Legg til "Item4"MyList.Legg til "Item7""Fang 4 elementer i" MyList "fra indeksposisjon 2Sett MyList1 = MyList.GetRange (2, 4)"Gjenta gjennom objektet" MyList1 "for å vise delsettet med elementerFor hver I I MyList1"Vis elementnavnMsgBox INeste jegSlutt Sub |
Søke etter elementer i en matriseliste
Du kan teste om et navngitt element er på listen din ved å bruke metoden "Inneholder". Dette vil returnere Sant eller usant
| 1 | MsgBox MyList.Contains ("Item2") |
Du kan også finne den faktiske indeksposisjonen ved å bruke 'IndexOf' -metoden. Du må spesifisere startindeksen for søket (vanligvis 0). Returverdien er indeksen for den første forekomsten av elementet som ble funnet. Du kan deretter bruke en sløyfe til å endre utgangspunktet til neste indeksverdi for å finne ytterligere forekomster hvis det er flere dupliserte verdier.
Hvis verdien ikke blir funnet, returneres verdien på -1
Dette eksemplet demonstrerer bruk av 'Inneholder', element ikke funnet, og bla gjennom matriselisten for å finne posisjonen til alle dupliserte elementer:
| 1234567891011121314151617181920212223242526 | Sub SearchListExample ()'Definer matriseliste og variablerDim MyList As New ArrayList, Sp As Integer, Pos As Integer‘Legg til nye varer, inkludert en duplikatMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3"MyList.Legg til "Item1""Test for" Item2 "som er på listen - returnerer TrueMsgBox MyList.Contains ("Item2")"Få indeks for ikke -eksisterende verdi -returnerer -1MsgBox MyList.IndexOf ("element", 0)'Sett startposisjonen for søket til nullSp = 0"Skift gjennom listen for å få alle posisjonene til" Item1 "Gjøre'Få indeksposisjonen til neste' Item1 'basert på posisjonen i variabelen' Sp 'Pos = MyList.IndexOf ("Item1", Sp)"Hvis du ikke finner flere forekomster av" Item1 ", går du ut av løkkenHvis Pos = -1, avslutt Do"Vis den neste forekomsten som er funnet og indeksposisjonenMsgBox MyList (Pos) & "at index" & Pos‘Legg 1 til den siste funnet indeksverdien - dette blir nå den nye startposisjonen for neste søkSp = Pos + 1LøkkeSlutt Sub |
Vær oppmerksom på at søketeksten som brukes er store og små bokstaver, og jokertegn godtas ikke.
Sette inn og fjerne elementer
Hvis du ikke ønsker å legge elementene dine til slutten av listen, kan du sette dem inn på en bestemt indeksposisjon slik at det nye elementet er midt på listen. Indeksnummerene blir automatisk justert for de påfølgende elementene.
| 123456789101112131415 | Sub InsertExample ()"Definer matriselisteobjektDim MyList As New ArrayList'Legg til elementer i matriselistenMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3"MyList.Legg til "Item1"'Sett inn' Item6 'i indeksposisjon 2MyList.Insert 2, "Item6""Skift gjennom elementene i matriselisten for å vise ny rekkefølge og indeksposisjonFor N = 0 til MyList.Count - 1MsgBox MyList (N) & "Index" & NNeste NSlutt Sub |
I dette eksemplet blir ‘Item6’ lagt til i listen ved indeksposisjon 2, så ‘item3’ som var på indeksposisjon 2 nå går over til indeksposisjon 3
Et enkelt element kan fjernes ved å bruke "Fjern" -metoden.
| 1 | MyList.Remove "Item" |
Vær oppmerksom på at det ikke oppstår noen feil hvis varenavnet ikke blir funnet. Alle påfølgende indeksnumre vil bli endret for å passe til fjerningen.
Hvis du kjenner indeksposisjonen til varen, kan du bruke "RemoveAt" -metoden f.eks.
| 1 | MyList.RemoveAt 2 |
Vær oppmerksom på at hvis den angitte indeksposisjonen er større enn antallet elementer i matriselisten, vil en feil bli returnert.
Du kan fjerne en rekke verdier fra listen ved å bruke "RemoveRange" -metoden. Parametrene er startindeksen og deretter antall elementer som skal fjernes f.eks.
| 1 | MyList.RemoveRange 3, 2 |
Vær oppmerksom på at du får en feil i koden din hvis antallet elementer som er forskjøvet fra startverdien er større enn antallet elementer i matriselisten.
I både "RemoveAt" og "RemoveRange" -metodene vil noen kode være tilrådelig for å sjekke om indeksnummerene som er angitt er større enn det totale antallet elementer i matriselisten for å fange eventuelle feil. Egenskapen 'Count' gir det totale antallet elementer i matriselisten.
| 12345678910111213141516171819202122232425 | Sub RemoveExample ()"Definer matriselisteobjektDim MyList As New ArrayList'Legg til elementer i matriselistenMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3"MyList.Legg til "Item1"MyList.Legg til "Item4"MyList.Legg til "Item5"'Sett inn' Item6 'i indeksposisjon 2MyList.Insert 2, "Item6""Fjern" Item2 "MyList.Remove "Item2""Fjern" element " - dette finnes ikke i matriselisten, men det er ingen feilMyList.Remove "Item"'Fjern elementet ved indeksposisjon 2MyList.RemoveAt 2"Fjern to påfølgende elementer som starter på indeksposisjon 2MyList.RemoveRange 3, 2"Skift gjennom matriselisten for å vise hva som er igjen og hvilken indeksposisjon den er i nåFor N = 0 til MyList.Count - 1MsgBox MyList (N) & "Index" & NNeste NSlutt Sub |
Vær oppmerksom på at hvis du bruker ‘RemoveAt’ til å fjerne et element på en bestemt posisjon, så endres alle påfølgende indeksposisjoner så snart elementet er fjernet. Hvis du har flere fjerninger som bruker indeksposisjonen, er det en god idé å begynne med det høyeste indeksnummeret og gå tilbake til posisjon null, slik at du alltid vil fjerne det riktige elementet. På denne måten vil du ikke ha problemet
Sortere en matriseliste
En annen stor fordel i forhold til en samling er at du kan sortere elementene i stigende eller synkende rekkefølge.
Array List -objektet er det eneste objektet i Excel VBA med en sorteringsmetode. Sorteringsmetoden er veldig rask, og dette kan være en viktig faktor for bruk av en matriseliste.
I samleobjektet var det nødvendig med litt "out of the box" -tenkning for å sortere alle elementene, men med en matriseliste er det veldig enkelt.
"Sorter" -metoden sorterer i stigende rekkefølge, og "omvendt" metode sorterer i synkende rekkefølge.
| 12345678910111213141516171819202122 | Sub ArrayListExample ()‘Lag Array List -objektDim MyList As New ArrayList"Legg til varer i en usortert rekkefølgeMyList.Legg til "Item1"MyList.Legg til "Item3"MyList.Legg til "Item2"'Sorter elementene i stigende rekkefølgeMyList.Sort"Skift gjennom elementene for å vise stigende rekkefølgeFor hver I In MyList"Vis elementnavnMsgBox INeste jeg'Sorter elementene i synkende rekkefølgeMyList.Reverse"Skift gjennom elementene for å vise synkende rekkefølgeFor hver I In MyList"Vis elementnavnMsgBox INeste jegSlutt Sub |
Kloning av en matriseliste
En matriseliste har muligheten til å lage en klon eller kopi av seg selv. Dette er nyttig hvis en bruker gjør endringer i elementene ved hjelp av en frontend og VBA -koden, men du må beholde en kopi av elementene i sin opprinnelige tilstand som en sikkerhetskopi.
Dette kan gi brukeren en "Angre" -funksjon. De kan ha gjort endringene, og ønsker å gå tilbake til den opprinnelige listen.
| 123456789101112131415 | Sub CloneExample ()"Definer to objekter - matriseliste og et objektDim MyList As New ArrayList, MyList1 As Object'Befolk det første objektet med gjenstanderMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3"‘Kopier Mylist til MyList1Sett MyList1 = MyList.Clone'Iterer gjennom MyList1 for å bevise kloningFor hver I I MyList1"Vis elementnavnMsgBox INeste jegSlutt Sub |
‘MyList1’ inneholder nå alle elementene fra ‘MyList’ i samme rekkefølge
Kopiere en listeoppstilling til et konvensjonelt VBA -matriseobjekt
Du kan bruke en enkel metode for å kopiere matriselisten til en vanlig VBA -matrise:
| 123456789101112131415 | Sub ArrayExample ()‘Lag matriselisteobjekt og et standard matrisobjektDim MyList As New ArrayList, NewArray As Variant'Befolk matriseliste med elementerMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3"'Kopier matriselisten til den nye matrisenNewArray = MyList.ToArray'Iterate gjennom den nye matrisen - merk at antall matriselister gir maksimal indeksFor N = 0 til MyList.Count - 1"Vis elementnavnMsgBox NewArray (N)Neste NSlutt Sub |
Kopiere en listeoppstilling til et regnearkområde
Du kan kopiere matriselisten til et bestemt regneark og cellereferanse uten at du trenger å gå gjennom matriselisten. Du trenger bare spesifisere den første cellereferansen
| 123456789101112131415 | Delområde Eksempel ()‘Lag et nytt matriselisteobjektDim MyList As New ArrayList"Legg til elementer i listenMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3""Fjern målarketArk ("Ark1"). UsedRange.Clear'Kopier elementer på tvers av en radArk ("Ark1"). Område ("A1"). Endre størrelse (1, MyList.Count) .Value = MyList.toArray'Kopier elementer nedover en kolonneArk ("Ark1"). Område ("A5"). Endre størrelse (MyList.Count, 1) .Value = _WorksheetFunction.Transpose (MyList.toArray)Slutt Sub |
Tøm alle elementer fra en matriseliste
Det er en enkel funksjon (Clear) for å slette matriselisten helt
| 1234567891011121314 | Sub ClearListExample ()‘Lag matriselisteobjektDim MyList As New ArrayList'Legg til nye varerMyList.Legg til "Item1"MyList.Legg til "Item2"MyList.Legg til "Item3""Vis antall varerMsgBox MyList.Count"Fjern alle elementeneMyList.Clear"Vis antall elementer for å bevise at clear har fungertMsgBox MyList.CountSlutt Sub |
Dette eksemplet oppretter elementer i en matriseliste og fjerner deretter matriselisten. Meldingsbokser viser før og etter antall elementer i matriselisten.
Array List Methods Summary for Excel VBA
| Oppgave | Parametere | Eksempler |
| Legg til / rediger element | Verdi | MyList.Legg til "Item1" |
| MyList (4) = “Item2” | ||
| Klon en matriseliste | Ingen | Dim MyList som objekt |
| Sett MyList2 = MyList.Clone | ||
| Kopier til Array | Ingen | Dim MyArray som variant |
| MyArray = MyList.ToArray | ||
| Kopier til et regnearkområde (rad) | Ingen | Ark ("Ark1"). Område ("A1"). Endre størrelse (1, MyList.Count) .Value = MyList.ToArray |
| Kopier til et regnearkområde (kolonne) | Ingen | Ark ("Ark1"). Område ("A3"). Endre størrelse (MyList.Count, 1) .Value = WorksheetFunction.Transpose (MyList.ToArray) |
| Skape | "System.Collections.ArrayList" | Dim MyList som objekt |
| Sett MyList = CreateObject (“System.Collections.ArrayList”) | ||
| Erklære | Ikke tilgjengelig | Dim MyList som objekt |
| Finn / sjekk om elementet finnes | Vare å finne | MyList.Contains (“Item2”) |
| Finn posisjonen til et element i ArrayList | 1. Element å finne. | Dim IndexNo As Long |
| 2. Posisjon å begynne å søke fra. | IndexNo = MyList.IndexOf (“Item3”, 0) | |
| IndexNo = MyList.IndexOf (“Item5”, 3) | ||
| Få antall varer | Ingen | MsgBox MyList.Count |
| Sett inn element | 1. Indeks - posisjon å sette inn på. | MyList.Insert 0, “Item5” |
| 2 Verdi - objekt eller verdi som skal settes inn. | MyList.Insert 4, “Item7” | |
| Les element | Indeks - langt heltall | MsgBox MyList.Item (0) |
| MsgBox MyList.Item (4) | ||
| Les element lagt til sist | Indeks - langt heltall | MsgBox MyList.Item (list.Count - 1) |
| Les element lagt til først | Indeks - langt heltall | MsgBox MyList.Item (0) |
| Les alle elementene (for hver) | Ikke tilgjengelig | Dim element Som variant |
| For hvert element i MyList | ||
| MsgBox -element | ||
| Neste element | ||
| Les alle elementene (For) | Indeks - langt heltall | Dim i As Long |
| For i = 0 Til MyList.Count - 1 | ||
| MsgBox i | ||
| Neste jeg | ||
| Fjern alle elementene | Ingen | MyList.Clear |
| Fjern elementet på posisjon | Indeksposisjon der elementet er | MyList.RemoveAt 5 |
| Fjern elementet med navn | Elementet du vil fjerne fra ArrayList | MyList.Fjerne "Item3" |
| Fjern en rekke elementer | 1. Indeks - startstilling. | MyList.RemoveRange 4,3 |
| 2. Count - antall elementer som skal fjernes. | ||
| Sorter i synkende rekkefølge | Ingen | MyList.Reverse |
| Sorter i stigende rekkefølge | Ikke | MyList.Sort |
