En samling är ett objekt som innehåller ett antal liknande föremål. Dessa kan lätt nås och manipuleras, även om det finns ett stort antal föremål i samlingen.
Det finns redan inbyggda samlingar med Excel VBA. Ett exempel är Sheets -samlingen. För varje kalkylblad i en arbetsbok finns det ett objekt i Sheets -samlingen.
De inbyggda samlingarna har mycket fler egenskaper och metoder tillgängliga för dig, men dessa är inte tillgängliga i dina egna samlingar som du skapar.
Du kan till exempel använda samlingen för att få information om ett visst kalkylblad. Till exempel kan du se namnet på kalkylbladet och även om det är synligt eller inte. Genom att använda en för varje slinga kan du genomgå varje kalkylblad i samlingen.
| 1234567 | Sub TestWorksheets ()Dim Sh Som arbetsbladFör varje Sh In SheetsMsgBox Sh.NameMsgBox Sh.VisibleNästa ShAvsluta Sub |
Du kan också ta upp ett specifikt kalkylblad i samlingen med hjälp av indexvärdet eller det faktiska namnet på kalkylbladet:
| 12 | MsgBox Sheets (1) .NameMsgBox Sheets ("Sheet1"). Namn |
När kalkylblad läggs till eller raderas så växer eller krymper Sheets -samlingen.
Observera att med VBA -samlingar börjar indexnumret med 1 inte med 0
Samlingar Versus Arrays
Matriser och samlingar liknar sina funktioner genom att de båda är metoder som gör att en stor mängd data kan lagras som sedan enkelt kan refereras med kod. De har dock ett antal skillnader i hur de fungerar:
- Arrays är flerdimensionella medan samlingar endast är en dimension. Du kan dimensionera en array med flera dimensioner t.ex.
| 1 | Dim MyArray (10, 2) som sträng |
Detta skapar en rad med 10 rader med 2 kolumner, nästan som ett kalkylblad. En samling är i själva verket en enda kolumn. Arrayen är användbar om du behöver lagra ett antal dataobjekt som relaterar till varandra t.ex. namn och adress. Namnet skulle finnas i den första dimensionen i matrisen och adressen i den andra dimensionen.
- När du fyller i din matris behöver du en separat kodrad för att sätta ett värde i varje element i matrisen. Om du hade en tvådimensionell array skulle du faktiskt behöva 2 rader kod - en rad för att adressera den första kolumnen och en rad för att adressera den andra kolumnen. Med samlingsobjektet använder du helt enkelt metoden Lägg till så att det nya objektet bara läggs till i samlingen och indexvärdet justeras automatiskt för att passa.
- Om du behöver ta bort en datapost är det mer komplicerat i matrisen. Du kan ange värdena för ett element till ett tomt värde, men själva elementet finns fortfarande i matrisen. Om du använder en For Next -slinga för att iterera genom matrisen, kommer slingan att returnera ett tomt värde, vilket kräver kodning för att se till att det tomma värdet ignoreras. I en samling använder du metoderna Lägg till eller ta bort, och all indexering och storlek ändras automatiskt. Objektet som har tagits bort försvinner helt. Matriser är användbara för en fast datastorlek, men samlingar är bättre för var mängden data kan komma att ändras.
- Samlingar är skrivskyddade medan arrayvärden kan ändras med VBA. Med en samling måste du först ta bort värdet som ska ändras och sedan lägga till det nya ändrade värdet.
- I en array kan du bara använda en enda datatyp för elementen som ställs in när du dimensionerar arrayen. Men i matrisen kan du använda anpassade datatyper som du har designat själv. Du kan ha en mycket komplicerad matrisstruktur med hjälp av en anpassad datatyp som i sin tur har flera anpassade datatyper under sig. I en samling kan du lägga till datatyper för varje objekt. Du kan ha ett numeriskt värde, ett datum eller en sträng - samlingsobjektet tar vilken datatyp som helst. Om du försökte sätta ett strängvärde i en array som var dimensionerad som numerisk skulle det ge ett felmeddelande.
- Samlingar är i allmänhet enklare att använda än matriser. När det gäller kodning, när du skapar ett samlingsobjekt, har det bara två metoder (Lägg till och ta bort) och två egenskaper (räkna och objekt), så objektet är ingalunda komplicerat att programmera.
- Samlingar kan använda nycklar för att hitta data. Arrays har inte denna funktion och kräver looping -kod för att iterera genom arrayen för att hitta specifika värden.
- Storleken på en matris måste definieras när den skapas första gången. Du måste ha en uppfattning om hur mycket data det kommer att lagra. Om du behöver öka storleken på matrisen kan du använda "ReDim" för att ändra storlek, men du måste använda sökordet "Behåll" om du inte vill förlora data som redan finns i matrisen. En samlingsstorlek behöver inte definieras. Det växer bara och krymper automatiskt när objekt läggs till eller tas bort.
Ett samlingsobjekts omfattning
När det gäller omfattning är samlingsobjektet endast tillgängligt medan arbetsboken är öppen. Det sparas inte när arbetsboken sparas. Om arbetsboken öppnas igen måste samlingen skapas på nytt med VBA-kod.
Om du vill att din samling ska vara tillgänglig för all kod i kodmodulen måste du deklarera samlingsobjektet i avsnittet Deklarera högst upp i modulfönstret
Detta säkerställer att all din kod inom den modulen kan komma åt samlingen. Om du vill att någon modul i din arbetsbok ska komma åt samlingen, definiera den som ett globalt objekt
| 1 | Global MyCollection som ny samling |
Skapa en samling, lägga till objekt och få tillgång till objekt
Ett enkelt samlingsobjekt kan skapas i VBA med följande kod:
| 123456 | Sub CreateCollection ()Dim MyCollection Som ny samlingMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Lägg till "Item3"Avsluta Sub |
Koden dimensionerar ett nytt objekt som heter "MyCollection" och sedan använder följande kodrader metoden Lägg till för att lägga till tre nya värden.
Du kan sedan använda kod för att iterera genom din samling för att komma åt värdena
| 123 | För varje artikel i MyCollectionMsgBox ArtikelNästa artikel |
Du kan också iterera genom din samling med en For Next Loop:
| 123 | För n = 1 till MyCollection.CountMsgBox MyCollection (n)Nästa n |
Koden får storleken på samlingen med hjälp av Count -egenskapen och använder sedan denna för att starta ett värde 1 för att indexera varje objekt
For each loop är snabbare än For Next loop men den fungerar bara i en riktning (lågt index till högt). For Next Loop har fördelen att du kan använda en annan riktning (högt index till lågt) och du kan också använda stegmetoden för att ändra steget. Detta är användbart när du vill radera flera objekt eftersom du kommer att behöva köra borttagningen från slutet av samlingen till starten eftersom indexet kommer att ändras när raderingarna sker.
Metoden Lägg till i en samling har tre valfria parametrar - nyckel, före och efter
Du kan använda parametrarna "Före" och "Efter" för att definiera positionen för ditt nya objekt i förhållande till de andra som redan finns i samlingen
Detta görs genom att ange indexnumret som du vill att ditt nya objekt ska vara relativt till.
| 123456 | Sub CreateCollection ()Dim MyCollection Som ny samlingMyCollection.Add "Item1"MyCollection.Lägg till "Item2",, 1MyCollection.Lägg till "Item3"Avsluta Sub |
I detta exempel har "Item2" angetts för att läggas till före det första indexerade objektet i samlingen (vilket är "Item1"). När du går igenom den här samlingen kommer "Item2" att visas först, följt av "Item1" och "Item3"
När du anger en parameter "Före" eller "Efter" justeras indexvärdet automatiskt i samlingen så att "Artikel2" blir indexvärde 1 och "Artikel1" flyttas till ett indexvärde på 2
Du kan också använda parametern ‘Nyckel’ för att lägga till ett referensvärde som du kan använda för att identifiera samlingsobjektet. Observera att ett nyckelvärde måste vara en sträng och måste vara unikt i samlingen.
| 1234567 | Sub CreateCollection ()Dim MyCollection Som ny samlingMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Lägg till "Item3"MsgBox MyCollection ("MyKey")Avsluta Sub |
"Item2" har fått ett "nyckel" -värde på "MyKey" så att du kan hänvisa till det objektet med värdet "MyKey" istället för indexnumret (2)
Observera att "nyckel" -värdet måste vara ett strängvärde. Det kan inte vara någon annan datatyp. Observera att samlingen är skrivskyddad och att du inte kan uppdatera nyckelvärdet när det har ställts in. Du kan inte heller kontrollera om det finns ett nyckelvärde för ett specifikt objekt i samlingen eller se nyckelvärdet som är lite av en nackdel.
Parametern 'Nyckel' har den extra fördelen att göra din kod mer läsbar, särskilt om den överlämnas till en kollega för att stödja, och du inte behöver iterera genom hela samlingen för att hitta det värdet. Tänk om du hade en samling på 10 000 artiklar hur svårt det skulle vara att referera till ett specifikt objekt!
Ta bort ett objekt från en samling
Du kan använda "Ta bort" -metoden för att ta bort objekt från din samling.
| 1 | MyCollection.Remove (2) |
Tyvärr är det inte lätt om samlingen har ett stort antal artiklar för att räkna ut indexet för objektet som du vill radera. Det är här parametern "Key" är till nytta när samlingen skapas
| 1 | MyCollection.Remove ("MyKey") |
När ett objekt tas bort från en samling återställs indexvärdena automatiskt hela vägen genom samlingen. Det är här parametern ‘Key’ är så användbar när du tar bort flera objekt samtidigt. Till exempel kan du ta bort artikelindex 105 och artikelindex 106 blir omedelbart index 105 och allt ovanför objektet har indexvärdet flyttat ner. Om du använder nyckelparametern behöver du inte oroa dig för vilket indexvärde som måste tas bort.
För att radera alla samlingsobjekt och skapa en ny samling använder du Dim -satsen igen som skapar en tom samling.
| 1 | Dim MyCollection Som ny samling |
För att ta bort det faktiska samlingsobjektet helt kan du ställa in objektet till ingenting
| 1 | Ställ in MyCollection = Ingenting |
Detta är användbart om samlingen inte längre krävs av din kod. Att ställa in samlingsobjektet till ingenting tar bort all referens till det och frigör minnet som det använde. Detta kan ha viktiga konsekvenser för hastigheten för exekvering av din kod, om ett stort objekt sitter i minnet som inte längre behövs.
Räkna antalet objekt i en samling
Du kan enkelt ta reda på antalet objekt i din samling med egenskapen "Count"
| 1 | MsgBox MyCollection.Count |
Du skulle använda den här egenskapen om du använde en For Next Loop för att iterera genom samlingen eftersom den ger dig den övre gränsen för indexnumret.
Testsamling för ett specifikt värde
Du kan iterera genom en samling för att söka efter ett specifikt värde för ett objekt med hjälp av en för varje loop
| 123456789101112 | Sub SearchCollection ()Dim MyCollection som ny samlingMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Lägg till "Item3"För varje artikel i MyCollectionIf Item = "Item2" DåMsgBox -objekt och "hittat"Avsluta omNästaAvsluta Sub |
Koden skapar en liten samling och går sedan igenom den igen för att leta efter ett objekt som heter 'item2'. Om den hittas visas en meddelanderuta om att den har hittat det specifika objektet
En av nackdelarna med denna metod är att du inte kan komma åt indexvärdet eller nyckelvärdet
Om du använder en For Next Loop istället kan du använda räknaren For Next för att få indexvärdet, även om du fortfarande inte kan få "Key" -värdet
| 123456789101112 | Sub SearchCollection ()Dim MyCollection Som ny samlingMyCollection.Add "Item1"MyCollection.Lägg till "Item2"MyCollection.Lägg till "Item3"För n = 1 till MyCollection.CountOm MyCollection.Item (n) = "Item2" DåMsgBox MyCollection.Item (n) & "finns vid indexposition" & nAvsluta omNästa nAvsluta Sub |
För nästa räknare (n) anger indexpositionen
Sortera en samling
Det finns ingen inbyggd funktionalitet för att sortera en samling, men med hjälp av "out of the box" -tänkande kan kod skrivas för att göra en sortering med hjälp av Excel: s sorteringsfunktion för kalkylblad. Denna kod använder ett tomt kalkylblad som heter "SortSheet" för att göra den faktiska sorteringen.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection Som ny samlingDim Counter As Long'Bygg en samling med slumpmässiga beställningsartiklarMyCollection.Add "Item5"MyCollection.Lägg till "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Lägg till "Item3""Fånga antalet föremål i samlingen för framtida brukRäknare = MyCollection.Count'Iterera genom samlingen och kopiera varje objekt till en på varandra följande cell på' SortSheet '(kolumn A)För n = 1 till MyCollection.CountSheets ("SortSheet"). Celler (n, 1) = MyCollection (n)Nästa n"Aktivera kalkylbladet och använd Excel -sorteringsrutinen för att sortera data i stigande ordningArk ("SortSheet"). AktiveraOmråde ("A1: A" & MyCollection.Count) .VäljActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 -nyckel: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalMed ActiveWorkbook.Worksheets ("SortSheet"). Sortera.SetRange -intervall ("A1: A5").Header = xlGuess.MatchCase = Falskt.Orientation = xlTopToBottom.SortMethod = xlPinYin.TillämpaSluta med'Radera alla objekt i samlingen - observera att denna For Next Loop körs i omvänd ordningFör n = MyCollection.Count till 1 steg -1MyCollection.Remove (n)Nästa n"Kopiera cellvärdena tillbaka till det tomma samlingsobjektet med hjälp av det lagrade värdet (räknare) för" loop "För n = 1 till räknareMyCollection.Add Sheets ("SortSheet"). Celler (n, 1) .VärdeNästa n'Iterera genom samlingen för att bevisa den ordning som föremålen nu finns iFör varje artikel i MyCollectionMsgBox ArtikelNästa artikel‘Rensa kalkylbladet (sorteringsblad) - ta bort det vid behov ocksåArk ("SortSheet"). Område (celler (1, 1), celler (räknare, 1)). RensaAvsluta Sub |
Denna kod skapar först en samling med objekten som läggs till i slumpmässig ordning. Den kopierar dem sedan till den första kolumnen på ett kalkylblad (SortSheet).
Koden använder sedan Excel -sorteringsfunktionen för att sortera data i kolumnen i stigande ordning. Koden kan också ändras för att sortera i fallande ordning.
Samlingen töms sedan på data med hjälp av en For Next Loop. Observera att stegalternativet används så att det rensas från slutet av samlingen till starten. Detta beror på att när det rensas återställs indexvärdena, om det rensades från början skulle det inte rensas korrekt (index 2 skulle bli index 1)
Slutligen, med en annan For Next Loop, överförs objektvärdena tillbaka till den tomma samlingen
En ytterligare för varje slinga visar att samlingen nu är i god stigande ordning.
Tyvärr handlar detta inte om några nyckelvärden som kan ha angetts ursprungligen, eftersom nyckelvärdena inte kan läsas
Vidarebefordra en samling till en sub / funktion
En samling kan skickas till en sub eller en funktion på samma sätt som alla andra parametrar
| 1 | Funktion MyFunction (ByRef MyCollection som samling) |
Det är viktigt att skicka samlingen med "ByRef". Det betyder att originalsamlingen används. Om samlingen skickas med "ByVal" skapar detta en kopia av samlingen som kan ha olyckliga konsekvenser
Om en kopia skapas med "ByVal" händer allt som ändrar samlingen inom funktionen bara på kopian och inte på originalet. Om till exempel ett nytt objekt läggs till i samlingen i funktionen, kommer detta inte att visas i originalsamlingen, vilket kommer att skapa ett fel i din kod.
Returnera en samling från en funktion
Du kan returnera en samling från en funktion på samma sätt som att returnera ett objekt. Du måste använda sökordet Set
| 12345 | Sub ReturnFromFunction ()Dim MyCollection Som samlingStäll in MyCollection = PopulateCollectionMsgBox MyCollection.CountAvsluta Sub |
Denna kod skapar en underrutin som skapar ett objekt som heter "MyCollection" och sedan använder sökordet "Set" för att effektivt kalla funktionen för att fylla den samlingen. När detta är gjort visas en meddelanderuta som visar antalet 2 objekt
| 1234567 | Funktion PopulateCollection () Som samlingDim MyCollection Som ny samlingMyCollection.Add "Item1"MyCollection.Lägg till "Item2"Ange PopulateCollection = MyCollectionAvsluta funktion |
Funktionen PopulateCollection skapar ett nytt samlingsobjekt och fyller det med 2 objekt. Det skickar sedan tillbaka detta objekt till det samlingsobjekt som skapades i den ursprungliga delrutinen.
Konvertera en samling till en matris
Du kanske vill konvertera din samling till en array. Du kanske vill lagra data där den kan ändras och manipuleras. Denna kod skapar en liten samling och överför den sedan till en array
Lägg märke till att samlingsindexet börjar vid 1 medan arrayindexet börjar på 0. Medan samlingen har 3 objekt behöver matrisen bara dimensioneras till 2 eftersom det finns ett element 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection Som ny samlingDim MyArray (2) Som strängMyCollection.Add "Item1"MyCollection.Lägg till "Item2"MyCollection.Lägg till "Item3"För n = 1 till MyCollection.CountMyArray (n - 1) = MyCollection (n)Nästa nFör n = 0 till 2MsgBox MyArray (n)Nästa nAvsluta Sub |
Konvertera en matris till en samling
Du kanske vill konvertera en array till en samling. Till exempel kanske du vill komma åt data på ett snabbare och mer elegant sätt som använder kod för att få ett arrayelement.
Tänk på att detta bara fungerar för en enda dimension av matrisen eftersom samlingen bara har en dimension
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection Som ny samlingDim MyArray (2) Som strängMyArray (0) = "item1"MyArray (1) = "Item2"MyArray (2) = "Item3"För n = 0 till 2MyCollection.Add MyArray (n)Nästa nFör varje artikel i MyCollectionMsgBox ArtikelNästa artikelAvsluta Sub |
Om du ville använda en flerdimensionell matris kan du sammanfoga matrisvärdena för varje rad i matrisen med hjälp av ett avgränsningstecken mellan matrisdimensionerna, så att när du läser samlingsvärdet kan du programmeringsmässigt använda avgränsarens tecken för att separera värdena.
Du kan också flytta data till samlingen på grundval av att värdet för första dimensionen läggs till (index 1), och sedan läggs nästa dimensionsvärde till (index 2) och så vidare.
Om matrisen hade, säg fyra dimensioner, skulle vart fjärde värde i samlingen vara en ny uppsättning värden.
Du kan också lägga till matrisvärden för att använda som nycklar (förutsatt att de är unika) vilket skulle lägga till ett enkelt sätt att hitta specifik data.
