Excel VBA -samlinger

En samling er et objekt, der rummer en række lignende genstande. Disse kan let tilgås og manipuleres, selvom der er et stort antal genstande i samlingen.

Der er allerede indbyggede samlinger med Excel VBA. Et eksempel er Sheets -samlingen. For hvert regneark i en projektmappe er der et element i Sheets -samlingen.

De indbyggede samlinger har langt flere egenskaber og metoder til rådighed for dig, men disse er ikke tilgængelige i dine egne samlinger, du opretter.

For eksempel kan du bruge samlingen til at indhente oplysninger om et bestemt regneark. For eksempel kan du se navnet på regnearket og også om det er synligt eller ej. Ved at bruge en For Every loop kan du gentage gennem hvert regneark i samlingen.

1234567

Sub TestWorksheets ()Dim Sh Som regnearkFor hver Sh In SheetsMsgBox Sh.NameMsgBox Sh. SynligNæste ShAfslut Sub

Du kan også adressere et specifikt regneark i samlingen ved hjælp af indeksværdien eller det faktiske navn på regnearket:

12	MsgBox Sheets (1). NavnMsgBox Sheets ("Sheet1"). Navn

Når regneark tilføjes eller slettes, vokser eller krymper Sheets -samlingen i størrelse.

Bemærk, at med VBA -samlinger begynder indeksnummeret med 1 ikke med 0

Samlinger Versus Arrays

Arrays og samlinger ligner hinanden i deres funktioner, idet de begge er metoder, der gør det muligt at gemme en stor mængde data, som derefter let kan refereres til ved hjælp af kode. De har imidlertid en række forskelle i den måde, de fungerer på:

1. Arrays er multidimensionale, hvorimod samlinger kun er en enkelt dimension. Du kan dimensionere en matrix med flere dimensioner f.eks.

1	Dim MyArray (10, 2) som streng

Dette skaber en matrix på 10 rækker med 2 kolonner, næsten som et regneark. En samling er faktisk en enkelt kolonne. Arrayen er nyttig, hvis du skal gemme et antal dataelementer, der vedrører hinanden f.eks. navn og adresse. Navn ville være i den første dimension af arrayet og adressen i den anden dimension.

2. Når du udfylder dit array, har du brug for en separat kodelinje for at sætte en værdi i hvert element i arrayet. Hvis du havde et todimensionalt array, ville du faktisk have brug for 2 linjer kode - en linje til at adressere den første kolonne og en linje til at adressere den anden kolonne. Med Samlingsobjektet bruger du blot tilføjelsesmetoden, så det nye element lige tilføjes til samlingen, og indeksværdien automatisk justeres, så det passer.
3. Hvis du skal slette et dataelement, er det mere kompliceret i arrayet. Du kan indstille værdierne for et element til en tom værdi, men selve elementet eksisterer stadig i arrayet. Hvis du bruger en For Næste sløjfe til at gentage gennem matrixen, returnerer løkken en tom værdi, som skal kodes for at sikre, at blankværdien ignoreres. I en samling bruger du tilføjelses- eller fjernmetoderne, og al indeksering og ændring af størrelsen tilpasses automatisk. Det element, der er blevet fjernet, forsvinder helt. Arrays er nyttige til en fast datastørrelse, men samlinger er bedre til, hvor mængden af ​​data kan ændres.
4. Samlinger er skrivebeskyttet, mens matrixværdier kan ændres ved hjælp af VBA. Med en samling skulle du først fjerne den værdi, der skal ændres, og derefter tilføje den nye ændrede værdi.
5. I en matrix kan du kun bruge en enkelt datatype til de elementer, der angives, når du dimensionerer matrixen. I arrayet kan du dog bruge brugerdefinerede datatyper, som du selv har designet. Du kan have en meget kompliceret matrixstruktur ved hjælp af en brugerdefineret datatype, som igen har flere tilpassede datatyper under sig. I en samling kan du tilføje datatyper til brug for hvert element. Du kan have en numerisk værdi, en dato eller en streng - indsamlingsobjektet tager enhver datatype. Hvis du forsøgte at sætte en strengværdi i en matrix, der var dimensioneret som numerisk, ville det give en fejlmeddelelse.
6. Samlinger er generelt lettere at bruge end arrays. I kodningsbetingelser, når du opretter et samlingsobjekt, har det kun to metoder (Tilføj og fjern) og to egenskaber (tæl og element), så objektet er på ingen måde kompliceret at programmere.
7. Samlinger kan bruge nøgler til at lokalisere data. Arrays har ikke denne funktion og kræver looping -kode for at iterere gennem arrayet for at finde bestemte værdier.
8. Størrelsen på en matrix skal defineres, når den først oprettes. Du skal have en idé om, hvor mange data det vil gemme. Hvis du har brug for at øge størrelsen på matrixen, kan du bruge 'ReDim' til at ændre størrelsen, men du skal bruge søgeordet 'Bevar', hvis du ikke vil miste de data, der allerede er i arrayet. En samlingstørrelse behøver ikke at være defineret. Det vokser bare og krymper automatisk, når varer tilføjes eller fjernes.

Omfang af et indsamlingsobjekt

Med hensyn til omfang er indsamlingsobjektet kun tilgængeligt, mens projektmappen er åben. Det gemmes ikke, når projektmappen gemmes. Hvis projektmappen genåbnes, skal samlingen genoprettes ved hjælp af VBA-kode.

Hvis du vil have din samling til at være tilgængelig for al koden i dit kodemodul, skal du deklarere indsamlingsobjektet i sektionen Deklarere øverst i modulvinduet

Dette sikrer, at al din kode inden for det pågældende modul har adgang til samlingen. Hvis du vil have et modul i din projektmappe til at få adgang til samlingen, skal du definere den som et globalt objekt

1	Global MyCollection som ny samling

Oprettelse af en samling, tilføjelse af emner og adgang til emner

Et enkelt indsamlingsobjekt kan oprettes i VBA ved hjælp af følgende kode:

123456

Sub CreateCollection ()Dim MyCollection som ny samlingMyCollection.Tilføj "Item1"MyCollection.Tilføj "Item2"MyCollection.Tilføj "Item3"Afslut Sub

Koden dimensionerer et nyt objekt kaldet 'MyCollection', og derefter bruger følgende kodelinjer tilføjelsesmetoden til at tilføje 3 nye værdier.

Du kan derefter bruge kode til at gentage gennem din samling for at få adgang til værdierne

123	For hvert element i MyCollectionMsgBox VareNæste element

Du kan også gentage din samling ved hjælp af en For Next Loop:

123	For n = 1 Til MyCollection.CountMsgBox MyCollection (n)Næste n

Koden får størrelsen på samlingen ved hjælp af egenskaben Count og bruger derefter denne til at starte en værdi 1 til at indeksere hvert element

For hver sløjfe er hurtigere end For Næste sløjfe, men den fungerer kun i en retning (lavt indeks til højt). For næste løkke har den fordel, at du kan bruge en anden retning (højt indeks til lavt), og du kan også bruge trinmetoden til at ændre inkrementet. Dette er nyttigt, når du vil slette flere elementer, da du bliver nødt til at køre sletningen fra slutningen af ​​samlingen til starten, da indekset vil ændre sig, efterhånden som sletningerne finder sted.

Add -metoden i en samling har 3 valgfrie parametre - nøgle, før og efter

Du kan bruge parametrene 'Før' og 'Efter' til at definere placeringen af ​​dit nye element i forhold til de andre, der allerede er i samlingen

Dette gøres ved at angive det indeksnummer, som du vil have din nye vare til at være i forhold til.

123456

Sub CreateCollection ()Dim MyCollection som ny samlingMyCollection.Add "Item1"MyCollection.Tilføj "Item2",, 1MyCollection.Tilføj "Item3"Afslut Sub

I dette eksempel er 'Item2' angivet for at blive tilføjet før det første indekserede element i samlingen (som er 'Item1'). Når du gentager denne samling, vises 'Item2' først og fremmest efterfulgt af 'Item1' og 'Item3'

Når du angiver en parameter "Før" eller "Efter", justeres indeksværdien automatisk i samlingen, så "Item2" bliver indeksværdi 1 og "Item1" flyttes til en indeksværdi på 2

Du kan også bruge parameteren 'Nøgle' ​​til at tilføje en referenceværdi, som du kan bruge til at identificere indsamlingselementet. Bemærk, at en nøgleværdi skal være en streng og skal være unik i samlingen.

1234567

Sub CreateCollection ()Dim MyCollection som ny samlingMyCollection.Tilføj "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Tilføj "Item3"MsgBox MyCollection ("MyKey")Afslut Sub

'Item2' har fået en 'nøgle' ​​-værdi på' MyKey ', så du kan henvise til dette element ved hjælp af værdien' MyKey 'i stedet for indeksnummeret (2)

Bemærk, at værdien 'Nøgle' ​​skal være en strengværdi. Det kan ikke være nogen anden datatype. Bemærk, at samlingen er skrivebeskyttet, og du kan ikke opdatere nøgleværdien, når den er blevet indstillet. Du kan heller ikke kontrollere, om der findes en nøgleværdi for et bestemt element i samlingen, eller se nøgleværdien, som er lidt af en ulempe.

Parameteren 'Nøgle' ​​har den ekstra fordel, at den gør din kode mere læsbar, især hvis den overdrages til en kollega for at understøtte, og du ikke behøver at gentage hele samlingen for at finde den værdi. Forestil dig, at hvis du havde en samling på 10.000 genstande, hvor svært det ville være at referere til et bestemt emne!

Fjernelse af en vare fra en samling

Du kan bruge metoden 'Fjern' til at slette elementer fra din samling.

1	MyCollection.Remove (2)

Desværre er det ikke let, hvis samlingen har et stort antal varer til at beregne indekset over den vare, du vil slette. Det er her parameteren ‘Nøgle’ er praktisk, når samlingen oprettes

1	MyCollection.Remove ("MyKey")

Når et element fjernes fra en samling, nulstilles indeksværdierne automatisk hele vejen igennem samlingen. Det er her parameteren ‘Nøgle’ er så nyttig, når du sletter flere elementer på én gang. For eksempel kan du slette vareindeks 105, og med det samme bliver vareindeks 106 indeks 105, og alt over dette element får sin indeksværdi flyttet ned. Hvis du bruger parameteren Nøgle, er der ingen grund til at bekymre dig om, hvilken indeksværdi der skal fjernes.

For at slette alle samlingselementerne og oprette en ny samling, bruger du Dim -sætningen igen, som opretter en tom samling.

1	Dim MyCollection som ny samling

For at fjerne det faktiske indsamlingsobjekt helt, kan du indstille objektet til ingenting

1	Indstil MyCollection = Intet

Dette er nyttigt, hvis samlingen ikke længere kræves af din kode. Indstilling af samlingsobjektet til ingenting fjerner al henvisning til det og frigiver den hukommelse, det brugte. Dette kan have vigtige konsekvenser for hastigheden af ​​udførelsen af ​​din kode, hvis et stort objekt sidder i hukommelsen, som ikke længere er påkrævet.

Tæl antallet af varer i en samling

Du kan let finde ud af antallet af genstande i din samling ved at bruge egenskaben 'Count'

1	MsgBox MyCollection.Count

Du ville bruge denne egenskab, hvis du brugte en For Next Loop til at gentage samlingen, da den giver dig den øvre grænse for indeksnummeret.

Testindsamling for en bestemt værdi

Du kan gentage gennem en samling for at søge efter en bestemt værdi for et element ved hjælp af en for hver sløjfe

123456789101112

Sub SearchCollection ()Dim MyCollection som ny samlingMyCollection.Tilføj "Item1"MyCollection.Tilføj "Item2"MyCollection.Tilføj "Item3"For hvert element i MyCollectionHvis Item = "Item2" SåMsgBox -element og "fundet"Afslut HvisNæsteAfslut Sub

Koden opretter en lille samling, og gentager derefter gennem den på udkig efter et element kaldet 'item2'. Hvis den findes, vises en meddelelsesboks om, at den har fundet det specifikke element

En af ulemperne ved denne metode er, at du ikke kan få adgang til indeksværdien eller nøgleværdien

Hvis du i stedet bruger en For Next Loop, kan du bruge For Next -tælleren til at få indeksværdien, selvom du stadig ikke kan få ‘Key’ -værdien

123456789101112

Sub SearchCollection ()Dim MyCollection som ny samlingMyCollection.Tilføj "Item1"MyCollection.Tilføj "Item2"MyCollection.Tilføj "Item3"For n = 1 Til MyCollection.CountHvis MyCollection.Item (n) = "Item2" SåMsgBox MyCollection.Item (n) & "fundet ved indeksposition" & nAfslut HvisNæste nAfslut Sub

For næste tælleren (n) angiver indekspositionen

Sortering af en samling

Der er ingen indbygget funktionalitet til at sortere en samling, men ved hjælp af nogle 'out of the box'-tænkninger kan kode skrives til at foretage en sortering ved hjælp af Excel's regnearkssorteringsfunktion. Denne kode bruger et tomt regneark kaldet 'SortSheet' til at foretage den faktiske sortering.

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152

Sub SortCollection ()Dim MyCollection som ny samlingDim tæller så længe'Byg samling med tilfældige ordrerMyCollection.Tilføj "Item5"MyCollection.Tilføj "Item2"MyCollection.Tilføj "Item4"MyCollection.Tilføj "Item1"MyCollection.Tilføj "Item3"'Fang antallet af genstande i samlingen til fremtidig brugTæller = MyCollection.Count'Iterere gennem samlingen og kopiere hvert element til en på hinanden følgende celle på' SortSheet '(kolonne A)For n = 1 Til MyCollection.CountArk ("SortSheet"). Celler (n, 1) = MyCollection (n)Næste n'Aktiver sorteringsarket, og brug Excel -sorteringsrutinen til at sortere dataene i stigende rækkefølgeArk ("SortSheet"). AktiverOmråde ("A1: A" og MyCollection.Count) .VælgActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 -nøgle: = 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.AnsøgeAfslut med'Slet alle genstande i samlingen - bemærk, at denne For Next Loop kører i omvendt rækkefølgeFor n = MyCollection.Count til 1 trin -1MyCollection.Remove (n)Næste n'Kopier celleværdierne tilbage til det tomme indsamlingsobjekt ved hjælp af den lagrede værdi (tæller) for' løkkenFor n = 1 til tællerMyCollection.Add Sheets ("SortSheet"). Celler (n, 1). VærdiNæste n»Skift igennem samlingen for at bevise den rækkefølge, varerne nu er iFor hvert element i MyCollectionMsgBox VareNæste element'Ryd regnearket (sorteringsark) - slet det om nødvendigt ogsåArk ("SortSheet"). Område (celler (1, 1), celler (tæller, 1)). FjernAfslut Sub

Denne kode opretter først en samling med elementerne tilføjet i en tilfældig rækkefølge. Den kopierer dem derefter til den første kolonne på et regneark (SortSheet).

Kode bruger derefter Excel -sorteringsfunktionen til at sortere dataene i kolonnen i stigende rækkefølge. Koden kan også ændres til at sortere i faldende rækkefølge.

Samlingen tømmes derefter for data ved hjælp af en For Next Loop. Bemærk, at trinindstillingen bruges, så den ryddes fra slutningen af ​​samlingen til starten. Dette skyldes, at når det ryddes, nulstilles indeksværdierne, hvis det ryddes fra starten, ville det ikke ryddes korrekt (indeks 2 ville blive indeks 1)

Endelig, ved hjælp af en anden For Next Loop, overføres elementværdierne tilbage til den tomme samling

En yderligere For Every Loop viser, at samlingen nu er i god stigende rækkefølge.

Desværre omhandler dette ikke nogen nøgleværdier, der kan have været indtastet oprindeligt, da nøgleværdierne ikke kan læses

Videregivelse af en samling til en sub / funktion

En samling kan overføres til en sub eller en funktion på samme måde som enhver anden parameter

1	Funktion MyFunction (ByRef MyCollection som samling)

Det er vigtigt at videregive samlingen ved hjælp af 'ByRef'. Det betyder, at den originale samling er brugt. Hvis samlingen sendes ved hjælp af 'ByVal', skaber dette en kopi af samlingen, som kan have uheldige konsekvenser

Hvis en kopi oprettes ved hjælp af 'ByVal', sker alt, der ændrer samlingen inden for funktionen, kun på kopien og ikke på originalen. For eksempel, hvis der inden for funktionen tilføjes et nyt element til samlingen, vises dette ikke i den originale samling, hvilket vil skabe en fejl i din kode.

Returnering af en samling fra en funktion

Du kan returnere en samling fra en funktion på samme måde som at returnere ethvert objekt. Du skal bruge nøgleordet Set

12345

Sub ReturnFromFunction ()Dim MyCollection som samlingIndstil MyCollection = PopulateCollectionMsgBox MyCollection.CountAfslut Sub

Denne kode opretter en underrutine, der opretter et objekt kaldet 'MyCollection' og derefter bruger søgeordet 'Set' til effektivt at kalde funktionen til at udfylde den samling. Når dette er gjort, viser det en meddelelsesboks for at vise antallet af 2 elementer

1234567

Funktion PopulateCollection () Som samlingDim MyCollection som ny samlingMyCollection.Add "Item1"MyCollection.Tilføj "Item2"Indstil PopulateCollection = MyCollectionAfslut funktion

Funktionen PopulateCollection opretter et nyt samlingsobjekt og udfylder det med 2 elementer. Det sender derefter dette objekt tilbage til det samleobjekt, der blev oprettet i den originale underrutine.

Konvertering af en samling til et array

Det kan være en god idé at konvertere din samling til et array. Du vil måske gemme dataene, hvor de kan ændres og manipuleres. Denne kode opretter en lille samling og overfører den derefter til en matrix

Bemærk, at indsamlingsindekset starter ved 1, mens matrixindekset starter ved 0. Mens samlingen har 3 elementer, skal matrixen kun dimensioneres til 2, fordi der er et element 0

1234567891011121314151617

Sub ConvertCollectionToArray ()Dim MyCollection som ny samlingDim MyArray (2) Som strengMyCollection.Add "Item1"MyCollection.Tilføj "Item2"MyCollection.Tilføj "Item3"For n = 1 Til MyCollection.CountMyArray (n - 1) = MyCollection (n)Næste nFor n = 0 til 2MsgBox MyArray (n)Næste nAfslut Sub

Konvertering af et array til en samling

Du vil måske konvertere en matrix til en samling. For eksempel kan du ønske at få adgang til dataene på en hurtigere og mere elegant måde, der bruger kode til at få et array -element.

Husk, at dette kun fungerer for en enkelt dimension af arrayet, fordi samlingen kun har en dimension

123456789101112131415

Sub ConvertArrayIntoCollection ()Dim MyCollection som ny samlingDim MyArray (2) Som strengMyArray (0) = "item1"MyArray (1) = "Item2"MyArray (2) = "Item3"For n = 0 til 2MyCollection.Add MyArray (n)Næste nFor hvert element i MyCollectionMsgBox VareNæste elementAfslut Sub

Hvis du ønskede at bruge et flerdimensionalt array, kunne du sammenkæde matrixværdierne sammen for hver række i arrayet ved hjælp af et afgrænsningstegn mellem matrixdimensionerne, så du ved læsning af samlingsværdien programmeringsmæssigt kunne bruge afgrænsningstegnet til at adskille værdierne.

Du kan også flytte dataene til samlingen på grundlag af, at værdien i den første dimension tilføjes (indeks 1), og derefter tilføjes den næste dimensionsværdi (indeks 2) og så videre.

Hvis arrayet f.eks. Havde 4 dimensioner, ville hver fjerde værdi i samlingen være et nyt sæt værdier.

Du kan også tilføje matrixværdier til brug som nøgler (forudsat at de er unikke), hvilket ville tilføje en let måde at lokalisere specifikke data.