En samling er et objekt som inneholder en rekke lignende gjenstander. Disse kan lett nås og manipuleres, selv om det er et stort antall elementer i samlingen.
Det er allerede innebygde samlinger med Excel VBA. Et eksempel er Sheets -samlingen. For hvert regneark i en arbeidsbok er det et element i Sheets -samlingen.
De innebygde samlingene har langt flere egenskaper og metoder tilgjengelig for deg, men disse er ikke tilgjengelige i dine egne samlinger du oppretter.
For eksempel kan du bruke samlingen til å skaffe informasjon om et bestemt regneark. For eksempel kan du se navnet på regnearket og også om det er synlig eller ikke. Ved å bruke en for hver sløyfe kan du gå gjennom hvert regneark i samlingen.
| 1234567 | Sub TestWorksheets ()Dim Sh Som regnearkFor hver Sh In SheetsMsgBox Sh.NameMsgBox Sh. SynligNeste ShSlutt Sub |
Du kan også adressere et bestemt regneark i samlingen ved hjelp av indeksverdien, eller det faktiske navnet på regnearket:
| 12 | MsgBox Sheets (1) .NameMsgBox Sheets ("Sheet1"). Navn |
Etter hvert som regneark legges til eller slettes, vokser eller krymper Sheets -samlingen.
Vær oppmerksom på at med VBA -samlinger begynner indeksnummeret med 1 ikke med 0
Samlinger Versus Arrays
Matriser og samlinger er like i deres funksjoner ved at de begge er metoder som gjør at en stor mengde data kan lagres som deretter enkelt kan refereres til ved hjelp av kode. De har imidlertid en rekke forskjeller i måten de fungerer på:
- Matriser er flerdimensjonale, mens samlinger bare er én dimensjon. Du kan dimensjonere en matrise med flere dimensjoner f.eks.
| 1 | Dim MyArray (10, 2) Som streng |
Dette skaper en matrise på 10 rader med 2 kolonner, nesten som et regneark. En samling er faktisk en enkelt kolonne. Arrayen er nyttig hvis du trenger å lagre en rekke dataelementer som er relatert til hverandre f.eks. navn og adresse. Navnet vil være i den første dimensjonen i matrisen og adressen i den andre dimensjonen.
- Når du fyller ut matrisen, trenger du en egen kodelinje for å sette en verdi i hvert element i matrisen. Hvis du hadde en todimensjonal matrise, ville du faktisk trenge 2 linjer med kode - en linje for å adressere den første kolonnen og en linje for å adressere den andre kolonnen. Med Collection -objektet bruker du ganske enkelt Add -metoden slik at det nye elementet bare legges til i samlingen og indeksverdien justeres automatisk for å passe.
- Hvis du trenger å slette et dataelement, er det mer komplisert i matrisen. Du kan sette verdiene til et element til en tom verdi, men selve elementet eksisterer fortsatt i matrisen. Hvis du bruker en For Next -sløyfe til å gjenta gjennom matrisen, vil sløyfen returnere en tom verdi, som må kodes for å sikre at blankverdi ignoreres. I en samling bruker du metodene Legg til eller fjern, og all indeksering og størrelse på størrelse blir automatisk tatt hånd om. Varen som er fjernet forsvinner helt. Matriser er nyttige for en fast størrelse på data, men samlinger er bedre for hvor mengden data kan endres.
- Samlinger er skrivebeskyttet, mens matrisverdier kan endres ved hjelp av VBA. Med en samling må du først fjerne verdien som skal endres og deretter legge til den nye endrede verdien.
- I en matrise kan du bare bruke en enkelt datatype for elementene som angis når du dimensjonerer matrisen. I matrisen kan du imidlertid bruke tilpassede datatyper som du har designet selv. Du kan ha en veldig komplisert matrisestruktur ved å bruke en tilpasset datatype som igjen har flere tilpassede datatyper under seg. I en samling kan du legge til bruk datatyper for hvert element. Du kan ha en numerisk verdi, en dato eller en streng - samlingsobjektet vil ta hvilken som helst datatype. Hvis du prøver å sette en strengverdi i en matrise som er dimensjonert som numerisk, vil det gi en feilmelding.
- Samlinger er generelt enklere å bruke enn matriser. Når det gjelder koding, når du oppretter et samlingsobjekt, har det bare to metoder (Legg til og fjern) og to egenskaper (Count and Item), så objektet er på ingen måte komplisert å programmere.
- Samlinger kan bruke nøkler til å finne data. Arrays har ikke denne funksjonen og krever looping -kode for å iterere gjennom matrisen for å finne spesifikke verdier.
- Størrelsen på en matrise må defineres når den først opprettes. Du må ha en ide om hvor mye data det skal lagre. Hvis du trenger å øke størrelsen på matrisen, kan du bruke 'ReDim' til å endre størrelsen, men du må bruke søkeordet 'Bevar' hvis du ikke vil miste dataene som allerede finnes i matrisen. En samlingsstørrelse trenger ikke å være definert. Det vokser bare og krymper automatisk etter hvert som elementer legges til eller fjernes.
Omfanget av et samleobjekt
Når det gjelder omfang, er samlingsobjektet bare tilgjengelig mens arbeidsboken er åpen. Den blir ikke lagret når arbeidsboken er lagret. Hvis arbeidsboken åpnes på nytt, må samlingen gjenopprettes ved hjelp av VBA-kode.
Hvis du vil at samlingen din skal være tilgjengelig for all koden i kodemodulen, må du deklarere samleobjektet i delen Deklarere øverst i modulvinduet
Dette vil sikre at all koden din i den modulen har tilgang til samlingen. Hvis du vil at en modul i arbeidsboken din skal få tilgang til samlingen, må du definere den som et globalt objekt
| 1 | Global MyCollection som ny samling |
Opprette en samling, legge til elementer og få tilgang til elementer
Et enkelt samleobjekt kan opprettes i VBA ved hjelp av følgende kode:
| 123456 | Sub CreateCollection ()Dim MyCollection Som ny samlingMyCollection.Legg til "Item1"MyCollection.Legg til "Item2"MyCollection.Legg til "Item3"Slutt Sub |
Koden dimensjonerer et nytt objekt kalt 'MyCollection', og deretter bruker følgende kodelinjer Add -metoden for å legge til tre nye verdier.
Du kan deretter bruke kode for å iterere gjennom samlingen din for å få tilgang til verdiene
| 123 | For hvert element i MyCollectionMsgBox -vareNeste element |
Du kan også iterere gjennom samlingen din ved hjelp av en For Next Loop:
| 123 | For n = 1 Til MyCollection.CountMsgBox MyCollection (n)Neste n |
Koden får størrelsen på samlingen ved å bruke Count -egenskapen og bruker deretter denne til å starte verdien 1 for å indeksere hvert element
For hver sløyfe er raskere enn For neste sløyfe, men den fungerer bare i én retning (lav indeks til høy). For neste sløyfe har fordelen at du kan bruke en annen retning (høy indeks til lav), og du kan også bruke trinnmetoden for å endre trinnet. Dette er nyttig når du vil slette flere elementer siden du må kjøre slettingen fra slutten av samlingen til starten ettersom indeksen endres etter hvert som slettingene finner sted.
Add -metoden i en samling har tre valgfrie parametere - nøkkel, før og etter
Du kan bruke parameterne "Før" og "Etter" til å definere plasseringen av det nye elementet i forhold til de andre som allerede er i samlingen
Dette gjøres ved å spesifisere indeksnummeret du vil at den nye varen skal være i forhold til.
| 123456 | Sub CreateCollection ()Dim MyCollection som ny samlingMyCollection.Legg til "Item1"MyCollection.Legg til "Item2",, 1MyCollection.Legg til "Item3"Slutt Sub |
I dette eksemplet er ‘Item2’ spesifisert for å bli lagt til før det første indekserte elementet i samlingen (som er ‘Item1’). Når du gjentar denne samlingen, vil "Item2" først vises, etterfulgt av "Item1" og "Item3"
Når du angir en parameter «Før» eller «Etter», justeres indeksverdien automatisk i samlingen slik at «Item2» blir indeksverdi på 1 og «Item1» flyttes til en indeksverdi på 2
Du kan også bruke parameteren ‘Nøkkel’ til å legge til en referanseverdi som du kan bruke til å identifisere samleelementet. Vær oppmerksom på at en nøkkelverdi må være en streng og må være unik i samlingen.
| 1234567 | Sub CreateCollection ()Dim MyCollection Som ny samlingMyCollection.Legg til "Item1"MyCollection.Legg til "Item2", "MyKey"MyCollection.Legg til "Item3"MsgBox MyCollection ("MyKey")Slutt Sub |
"Item2" har fått en "nøkkel" -verdi på "MyKey", slik at du kan referere til det elementet ved å bruke verdien av "MyKey" i stedet for indeksnummeret (2)
Vær oppmerksom på at nøkkelen må være en strengverdi. Det kan ikke være noen annen datatype. Vær oppmerksom på at samlingen er skrivebeskyttet, og du kan ikke oppdatere nøkkelverdien når den er angitt. Du kan heller ikke kontrollere om det finnes en nøkkelverdi for et bestemt element i samlingen, eller se nøkkelverdien som er litt av en ulempe.
Parameteren "Nøkkel" har den ekstra fordelen ved å gjøre koden din mer lesbar, spesielt hvis den blir overlevert til en kollega for å støtte, og du ikke trenger å gjennomgå hele samlingen for å finne den verdien. Tenk om du hadde en samling på 10 000 varer hvor vanskelig det ville være å referere til et bestemt element!
Fjerne et element fra en samling
Du kan bruke "Fjern" -metoden til å slette elementer fra samlingen din.
| 1 | MyCollection.Remove (2) |
Dessverre er det ikke lett hvis samlingen har et stort antall elementer for å regne ut indeksen til elementet du vil slette. Det er her ‘Nøkkel’ -parameteren er nyttig når samlingen opprettes
| 1 | MyCollection.Remove ("MyKey") |
Når et element fjernes fra en samling, blir indeksverdiene automatisk tilbakestilt hele veien gjennom samlingen. Det er her ‘Nøkkel’ -parameteren er så nyttig når du sletter flere elementer samtidig. For eksempel kan du slette vareindeks 105, og elementindeks 106 blir øyeblikkelig indeks 105, og alt over dette elementet har indeksverdien flyttet ned. Hvis du bruker parameteren Nøkkel, trenger du ikke bekymre deg for hvilken indeksverdi som må fjernes.
For å slette alle samlingselementene og opprette en ny samling, bruker du Dim -setningen igjen som lager en tom samling.
| 1 | Dim MyCollection som ny samling |
For å fjerne selve samleobjektet helt, kan du sette objektet til ingenting
| 1 | Sett MyCollection = Ingenting |
Dette er nyttig hvis samlingen ikke lenger kreves av koden din. Hvis du setter samleobjektet til ingenting, fjernes all referanse til det og frigjør minnet det brukte. Dette kan ha viktige konsekvenser for hastigheten på utførelsen av koden din, hvis et stort objekt sitter i minnet som ikke lenger er nødvendig.
Tell antall elementer i en samling
Du kan enkelt finne ut antall gjenstander i samlingen din ved å bruke "Count" -egenskapen
| 1 | MsgBox MyCollection.Count |
Du ville bruke denne egenskapen hvis du brukte en For Next Loop til å gjennomgå samlingen, da den gir deg den øvre grensen for indeksnummeret.
Testsamling for en bestemt verdi
Du kan iterere gjennom en samling for å søke etter en bestemt verdi for et element ved å bruke en For Every Loop
| 123456789101112 | Sub SearchCollection ()Dim MyCollection som ny samlingMyCollection.Legg til "Item1"MyCollection.Legg til "Item2"MyCollection.Legg til "Item3"For hvert element i MyCollectionIf Item = "Item2" DaMsgBox -element og "funnet"Slutt omNesteSlutt Sub |
Koden oppretter en liten samling, og går deretter gjennom den på jakt etter et element kalt 'item2'. Hvis den vises, vises en meldingsboks om at den har funnet det spesifikke elementet
En av ulempene med denne metoden er at du ikke får tilgang til indeksverdien eller nøkkelverdien
Hvis du bruker en For Next Loop i stedet, kan du bruke For Next -telleren for å få indeksverdien, selv om du fremdeles ikke kan få ‘Key’ -verdien
| 123456789101112 | Sub SearchCollection ()Dim MyCollection Som ny samlingMyCollection.Legg til "Item1"MyCollection.Legg til "Item2"MyCollection.Legg til "Item3"For n = 1 Til MyCollection.CountHvis MyCollection.Item (n) = "Item2" DaMsgBox MyCollection.Item (n) & "funnet på indeksposisjon" & nSlutt omNeste nSlutt Sub |
For neste teller (n) gir indeksposisjonen
Sortere en samling
Det er ingen innebygd funksjonalitet for å sortere en samling, men ved å bruke litt "out of the box" -tenkning kan kode skrives for å gjøre en sortering ved å bruke Excels sorteringsfunksjon for regneark. Denne koden bruker et tomt regneark kalt 'SortSheet' for å utføre selve sorteringen.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection Som ny samlingDim Counter As Long'Bygg samling med tilfeldige bestillingselementerMyCollection.Legg til "Item5"MyCollection.Legg til "Item2"MyCollection.Legg til "Item4"MyCollection.Legg til "Item1"MyCollection.Legg til "Item3""Lag antall gjenstander i samlingen for fremtidig brukTeller = MyCollection.Count"Gjenta gjennom samlingen og kopier hvert element til en påfølgende celle på" SortSheet "(kolonne A)For n = 1 Til MyCollection.CountArk ("SortSheet"). Celler (n, 1) = MyCollection (n)Neste n'Aktiver sorteringsarket og bruk Excel -sorteringsrutinen for å sortere dataene i stigende rekkefølgeArk ("SortSheet"). AktiverOmråde ("A1: A" og MyCollection.Count) .VelgActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 -nøkkel: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalMed ActiveWorkbook.Worksheets ("SortSheet"). Sorter.SetRange -område ("A1: A5").Header = xlGuess.MatchCase = Falsk.Orientation = xlTopToBottom.SortMethod = xlPinYin.Søke omSlutt med'Slett alle elementene i samlingen - merk at denne For Next Loop kjører i motsatt rekkefølgeFor n = MyCollection.Count til 1 trinn -1MyCollection.Remove (n)Neste n'Kopier celleverdiene tilbake til det tomme samleobjektet ved å bruke den lagrede verdien (telleren) for' loop 'For n = 1 til tellerMyCollection.Add Sheets ("SortSheet"). Celler (n, 1) .VærdiNeste n"Skift gjennom samlingen for å bevise rekkefølgen elementene nå er iFor hvert element i MyCollectionMsgBox -vareNeste element"Fjern regnearket (sorteringsarket) - slett det om nødvendig ogsåArk ("SortSheet"). Område (celler (1, 1), celler (teller, 1)). FjernSlutt Sub |
Denne koden oppretter først en samling med elementene lagt til i en tilfeldig rekkefølge. Den kopierer dem deretter til den første kolonnen på et regneark (SortSheet).
Koden bruker deretter Excel -sorteringsfunksjonen til å sortere dataene i kolonnen i stigende rekkefølge. Koden kan også endres for å sortere i synkende rekkefølge.
Samlingen tømmes deretter for data ved hjelp av en For Next Loop. Vær oppmerksom på at trinnalternativet brukes slik at det slettes fra slutten av samlingen til starten. Dette er fordi indeksverdiene blir tilbakestilt etter hvert som de tømmes. Hvis den slettes fra starten, vil den ikke bli riktig (indeks 2 vil bli indeks 1)
Til slutt, ved å bruke en annen For Next Loop, blir elementverdiene overført tilbake til den tomme samlingen
En ytterligere For Every Loop viser at samlingen nå er i god stigende rekkefølge.
Dessverre omhandler dette ikke noen nøkkelverdier som kan ha blitt angitt opprinnelig, siden nøkkelverdiene ikke kan leses
Overføring av en samling til en sub / funksjon
En samling kan overføres til en sub eller en funksjon på samme måte som en hvilken som helst annen parameter
| 1 | Funksjon MyFunction (ByRef MyCollection som samling) |
Det er viktig å passere samlingen ved hjelp av ‘ByRef’. Dette betyr at den originale samlingen er brukt. Hvis samlingen passeres ved hjelp av 'ByVal', skaper dette en kopi av samlingen som kan ha uheldige konsekvenser
Hvis en kopi blir opprettet ved hjelp av 'ByVal', skjer alt som endrer samlingen i funksjonen bare på kopien og ikke på originalen. For eksempel, hvis det i funksjonen blir lagt til et nytt element i samlingen, vil dette ikke vises i den originale samlingen, noe som vil opprette en feil i koden din.
Returnere en samling fra en funksjon
Du kan returnere en samling fra en funksjon på samme måte som å returnere et objekt. Du må bruke Set -søkeordet
| 12345 | Sub ReturnFromFunction ()Dim MyCollection Som samlingSett MyCollection = PopulateCollectionMsgBox MyCollection.CountSlutt Sub |
Denne koden oppretter en underrutine som oppretter et objekt kalt "MyCollection" og bruker deretter "Set" -søkeordet for effektivt å ringe funksjonen for å fylle ut samlingen. Når dette er gjort, viser det en meldingsboks for å vise antallet 2 elementer
| 1234567 | Funksjon PopulateCollection () Som samlingDim MyCollection Som ny samlingMyCollection.Legg til "Item1"MyCollection.Legg til "Item2"Sett PopulateCollection = MyCollectionSluttfunksjon |
Funksjonen PopulateCollection lager et nytt samlingsobjekt og fyller det med 2 elementer. Det sender deretter dette objektet tilbake til samlingsobjektet som ble opprettet i den opprinnelige delrutinen.
Konvertering av en samling til en matrise
Det kan være lurt å konvertere samlingen din til en matrise. Det kan være lurt å lagre dataene der de kan endres og manipuleres. Denne koden oppretter en liten samling og overfører den deretter til en matrise
Legg merke til at samleindeksen starter ved 1 mens matrisindeksen starter på 0. Mens samlingen har 3 elementer, trenger matrisen bare å dimensjoneres til 2 fordi det er et element 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection Som ny samlingDim MyArray (2) Som strengMyCollection.Legg til "Item1"MyCollection.Legg til "Item2"MyCollection.Legg til "Item3"For n = 1 Til MyCollection.CountMyArray (n - 1) = MyCollection (n)Neste nFor n = 0 til 2MsgBox MyArray (n)Neste nSlutt Sub |
Konvertering av en matrise til en samling
Det kan være lurt å konvertere en matrise til en samling. For eksempel kan det være lurt å få tilgang til dataene på en raskere og mer elegant måte ved å bruke kode for å få et array -element.
Husk at dette bare vil fungere for en enkelt dimensjon av matrisen fordi samlingen bare har en dimensjon
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection som ny samlingDim MyArray (2) Som strengMyArray (0) = "element1"MyArray (1) = "Item2"MyArray (2) = "Item3"For n = 0 til 2MyCollection.Legg til MyArray (n)Neste nFor hvert element i MyCollectionMsgBox -vareNeste elementSlutt Sub |
Hvis du ønsket å bruke en flerdimensjonal matrise, kan du sammenkoble matrisverdiene sammen for hver rad i matrisen ved å bruke et skilletegn mellom matrisedimensjonene, slik at du ved å lese samlingsverdien programmatisk kan bruke skilletegnet til å skille ut verdiene.
Du kan også flytte dataene til samlingen på grunnlag av at den første dimensjonsverdien legges til (indeks 1), og deretter legges den neste dimensjonsverdien til (indeks 2) og så videre.
Hvis matrisen hadde, for eksempel, fire dimensjoner, ville hver fjerde verdi i samlingen være et nytt sett med verdier.
Du kan også legge til matrisverdier som du kan bruke som nøkler (forutsatt at de er unike), noe som vil legge til en enkel måte å finne spesifikke data på.
