Een collectie is een object dat een aantal vergelijkbare items bevat. Deze kunnen gemakkelijk worden geopend en gemanipuleerd, zelfs als er een groot aantal items in de collectie is.
Er zijn al ingebouwde collecties met Excel VBA. Een voorbeeld is de Sheets-collectie. Voor elk werkblad in een werkmap is er een item in de Spreadsheets-collectie.
De ingebouwde verzamelingen hebben veel meer eigenschappen en methoden voor u beschikbaar, maar deze zijn niet beschikbaar in uw eigen verzamelingen die u maakt.
U kunt de verzameling bijvoorbeeld gebruiken om informatie over een bepaald werkblad te verkrijgen. U kunt bijvoorbeeld de naam van het werkblad zien en ook of het zichtbaar is of niet. Door een For Each-lus te gebruiken, kunt u elk werkblad in de verzameling herhalen.
| 1234567 | SubtestWerkbladen()Dim Sh As-werkbladVoor elke Sh in bladenMsgBox Sh.NaamMsgBox Sh.ZichtbaarVolgende ShEinde sub |
U kunt ook een specifiek werkblad in de verzameling adresseren met behulp van de indexwaarde of de werkelijke naam van het werkblad:
| 12 | MsgBox-bladen(1).NaamMsgBox-bladen ("Blad1").Naam |
Naarmate werkbladen worden toegevoegd of verwijderd, wordt de verzameling Spreadsheets groter of kleiner.
Merk op dat bij VBA-verzamelingen het indexnummer begint met 1 en niet met 0
Collecties versus arrays
Arrays en verzamelingen zijn vergelijkbaar in hun functies, omdat het beide methodologieën zijn waarmee een grote hoeveelheid gegevens kan worden opgeslagen, waarnaar vervolgens gemakkelijk kan worden verwezen met behulp van code. Ze hebben echter een aantal verschillen in de manier waarop ze werken:
- Arrays zijn multidimensionaal, terwijl collecties slechts één dimensie hebben. U kunt een array met meerdere dimensies bematen, b.v.
| 1 | Dim MyArray (10, 2) als string |
Dit creëert een array van 10 rijen met 2 kolommen, bijna als een werkblad. Een verzameling is in feite een enkele kolom. De array is handig als u een aantal gegevens moet opslaan die aan elkaar gerelateerd zijn, b.v. naam en adres. Naam zou in de eerste dimensie van de array staan en adres in de tweede dimensie.
- Wanneer u uw array vult, hebt u een aparte regel code nodig om een waarde in elk element van de array te plaatsen. Als je een tweedimensionale array had, zou je eigenlijk 2 regels code nodig hebben - een regel om de eerste kolom te adresseren en een regel om de tweede kolom te adresseren. Met het Collection-object gebruikt u gewoon de Add-methode, zodat het nieuwe item gewoon aan de verzameling wordt toegevoegd en de indexwaarde automatisch wordt aangepast.
- Als u een gegevensitem moet verwijderen, is het ingewikkelder in de array. U kunt de waarden van een element instellen op een lege waarde, maar het element zelf bestaat nog steeds binnen de array. Als u een For Next-lus gebruikt om door de array te itereren, retourneert de lus een lege waarde, die moet worden gecodeerd om ervoor te zorgen dat de lege waarde wordt genegeerd. In een verzameling gebruikt u de methoden Toevoegen of Verwijderen, en alle indexering en het formaat wijzigen wordt automatisch geregeld. Het verwijderde item verdwijnt volledig. Arrays zijn handig voor een vaste hoeveelheid gegevens, maar verzamelingen zijn beter voor waar de hoeveelheid gegevens kan veranderen.
- Collecties zijn alleen-lezen, terwijl matrixwaarden kunnen worden gewijzigd met VBA. Bij een verzameling zou u eerst de te wijzigen waarde moeten verwijderen en vervolgens de nieuwe gewijzigde waarde toevoegen.
- In een array kunt u slechts één gegevenstype gebruiken voor de elementen dat wordt ingesteld wanneer u de array bemat. In de array kunt u echter aangepaste gegevenstypen gebruiken die u zelf hebt ontworpen. U kunt een zeer gecompliceerde matrixstructuur hebben met een aangepast gegevenstype dat op zijn beurt verschillende aangepaste gegevenstypen eronder heeft. In een verzameling kunt u voor elk item gebruiksgegevenstypen toevoegen. U kunt een numerieke waarde, een datum of een tekenreeks hebben - het verzamelingsobject kan elk gegevenstype aannemen. Als u probeerde een tekenreekswaarde in een array te plaatsen die als numeriek was gedimensioneerd, zou dit een foutmelding opleveren.
- Collecties zijn over het algemeen gemakkelijker te gebruiken dan arrays. In codeertermen, wanneer u een verzamelingsobject maakt, heeft het slechts twee methoden (Toevoegen en Verwijderen) en twee eigenschappen (Aantal en Item), dus het object is geenszins ingewikkeld om te programmeren.
- Collecties kunnen sleutels gebruiken om gegevens te lokaliseren. Arrays hebben deze functie niet en vereisen luscode om door de array te itereren om specifieke waarden te vinden.
- De grootte van een array moet worden gedefinieerd wanneer deze voor het eerst wordt gemaakt. U moet een idee hebben van hoeveel gegevens het gaat opslaan. Als u de grootte van de array moet vergroten, kunt u 'ReDim' gebruiken om de grootte ervan te wijzigen, maar u moet het trefwoord 'Preserve' gebruiken als u de gegevens die al in de array zijn opgeslagen niet wilt verliezen. Er hoeft geen collectiegrootte te worden gedefinieerd. Het groeit en krimpt automatisch als er items worden toegevoegd of verwijderd.
Omvang van een verzamelobject
Qua omvang is het collectieobject alleen beschikbaar als de werkmap open is. Het wordt niet opgeslagen wanneer de werkmap wordt opgeslagen. Als de werkmap opnieuw wordt geopend, moet de verzameling opnieuw worden gemaakt met VBA-code.
Als u wilt dat uw verzameling beschikbaar is voor alle code in uw codemodule, moet u het verzamelingsobject declareren in de sectie Declareren bovenaan het modulevenster
Dit zorgt ervoor dat al uw code binnen die module toegang heeft tot de collectie. Als u wilt dat een module in uw werkmap toegang krijgt tot de verzameling, definieert u deze als een globaal object
| 1 | Global MyCollection als nieuwe collectie |
Een collectie maken, items toevoegen en toegang krijgen tot items
Een eenvoudig verzamelingsobject kan in VBA worden gemaakt met behulp van de volgende code:
| 123456 | Sub CreateCollection()Dim MyCollection als nieuwe collectieMyCollection.Voeg "Artikel1" toeMyCollection.Voeg "Artikel2" toeMyCollection.Voeg "Artikel3" toeEinde sub |
De code dimensioneert een nieuw object met de naam 'MyCollection' en vervolgens gebruiken de volgende coderegels de Add-methode om 3 nieuwe waarden toe te voegen.
U kunt vervolgens code gebruiken om door uw verzameling te bladeren om toegang te krijgen tot de waarden
| 123 | Voor elk item in MyCollectionMsgBox-itemVolgende item |
Je kunt je verzameling ook doorlopen met een For Next Loop:
| 123 | Voor n = 1 Naar MyCollection.CountMsgBox MyCollection(n)Volgende nr |
De code krijgt de grootte van de verzameling door de eigenschap Count te gebruiken en gebruikt deze vervolgens met een waarde 1 om elk item te indexeren
De For Each-lus is sneller dan de For Next-lus, maar werkt slechts in één richting (lage index naar hoog). De For Next Loop heeft als voordeel dat je een andere richting kunt gebruiken (hoge index naar laag) en je kunt ook de Step methode gebruiken om de increment te veranderen. Dit is handig als u meerdere items wilt verwijderen, omdat u de verwijdering van het einde van de verzameling tot het begin moet uitvoeren, omdat de index zal veranderen als de verwijderingen plaatsvinden.
De methode Toevoegen in een verzameling heeft 3 optionele parameters: Key, Before en After
U kunt de parameters 'Voor' en 'Na' gebruiken om de positie van uw nieuwe item te definiëren ten opzichte van de anderen die al in de collectie zitten
Dit doet u door het indexnummer op te geven waaraan u wilt dat uw nieuwe item relatief is.
| 123456 | Sub CreateCollection()Dim MyCollection als nieuwe collectieMyCollection.Voeg "Artikel1" toeMyCollection.Toevoegen "Artikel2", , 1MyCollection.Voeg "Artikel3" toeEinde sub |
In dit voorbeeld is 'Item2' gespecificeerd om te worden toegevoegd vóór het eerste geïndexeerde item in de verzameling (dat is 'Item1'). Als je door deze verzameling loopt, verschijnt eerst 'Item2', gevolgd door 'Item1' en 'Item3'
Wanneer u een parameter 'Before' of 'After' opgeeft, wordt de indexwaarde automatisch aangepast binnen de collectie, zodat 'Item2' indexwaarde van 1 wordt en 'Item1' wordt verplaatst naar een indexwaarde van 2
U kunt de parameter ‘Sleutel’ ook gebruiken om een referentiewaarde toe te voegen die u kunt gebruiken om het collectie-item te identificeren. Houd er rekening mee dat een sleutelwaarde een tekenreeks moet zijn en uniek moet zijn binnen de verzameling.
| 1234567 | Sub CreateCollection()Dim MyCollection als nieuwe collectieMyCollection.Voeg "Artikel1" toeMyCollection.Voeg "Item2", "MyKey" toeMyCollection.Voeg "Artikel3" toeMsgBox MijnVerzameling("MijnKey")Einde sub |
‘Item2’ heeft de ‘Key’-waarde van ‘MyKey’ gekregen, zodat u naar dat item kunt verwijzen met de waarde van ‘MyKey’ in plaats van het indexnummer (2)
Merk op dat de waarde 'Sleutel' een tekenreekswaarde moet zijn. Het kan geen ander gegevenstype zijn. Houd er rekening mee dat de verzameling alleen-lezen is en dat u de sleutelwaarde niet kunt bijwerken nadat deze is ingesteld. U kunt ook niet controleren of er een sleutelwaarde bestaat voor een specifiek item in de collectie of de sleutelwaarde bekijken, wat een beetje een nadeel is.
De parameter 'Key' heeft als bijkomend voordeel dat uw code leesbaarder wordt, vooral als deze wordt overgedragen aan een collega ter ondersteuning, en u niet de hele verzameling hoeft te doorlopen om die waarde te vinden. Stel je voor dat als je een verzameling van 10.000 items had, hoe moeilijk het zou zijn om naar één specifiek item te verwijzen!
Een item uit een collectie verwijderen
U kunt de methode ‘Verwijderen’ gebruiken om items uit uw collectie te verwijderen.
| 1 | MyCollection.Verwijderen (2) |
Als de collectie een groot aantal items bevat, is het helaas niet eenvoudig om de index te bepalen van het item dat u wilt verwijderen. Dit is waar de parameter 'Key' van pas komt wanneer de verzameling wordt gemaakt
| 1 | MijnVerzameling.Verwijderen (“MijnKey”) |
Wanneer een item uit een collectie wordt verwijderd, worden de indexwaarden tijdens de hele collectie automatisch opnieuw ingesteld. Dit is waar de parameter 'Key' zo handig is wanneer u meerdere items tegelijk verwijdert. U kunt bijvoorbeeld itemindex 105 verwijderen en itemindex 106 wordt onmiddellijk index 105, en van alles boven dit item wordt de indexwaarde naar beneden verplaatst. Als u de parameter Key gebruikt, hoeft u zich geen zorgen te maken over welke indexwaarde moet worden verwijderd.
Om alle collectie-items te verwijderen en een nieuwe collectie aan te maken, gebruik je opnieuw het Dim-statement dat een lege collectie creëert.
| 1 | Dim MyCollection als nieuwe collectie |
Om het eigenlijke verzamelobject volledig te verwijderen, kunt u het object op niets zetten
| 1 | Stel Mijn Verzameling = Niets in |
Dit is handig als de verzameling niet langer vereist is door uw code. Als u het verzamelingsobject op niets instelt, wordt alle verwijzing ernaar verwijderd en komt het geheugen vrij dat het gebruikte. Dit kan belangrijke gevolgen hebben voor de uitvoeringssnelheid van uw code, als een groot object in het geheugen zit dat niet langer nodig is.
Tel het aantal items in een collectie
U kunt eenvoudig het aantal items in uw verzameling achterhalen door de eigenschap 'Count' te gebruiken
| 1 | MsgBox MyCollection.Count |
U zou deze eigenschap gebruiken als u een For Next Loop zou gebruiken om door de verzameling te bladeren, omdat dit u de bovengrens voor het indexnummer geeft.
Testverzameling voor een specifieke waarde
U kunt een verzameling doorlopen om naar een specifieke waarde voor een item te zoeken met behulp van een For Each Loop
| 123456789101112 | Subzoekverzameling()Dim MyCollection als nieuwe collectieMyCollection.Voeg "Artikel1" toeMyCollection.Voeg "Artikel2" toeMyCollection.Voeg "Artikel3" toeVoor elk item in MyCollectionAls Item = "Item2" DanMsgBox-item & "Gevonden"Stop alsVolgendeEinde sub |
De code maakt een kleine verzameling en doorloopt deze vervolgens op zoek naar een item met de naam 'item2'. Indien gevonden, wordt een berichtvenster weergegeven dat het het specifieke item heeft gevonden
Een van de nadelen van deze methode is dat je geen toegang hebt tot de indexwaarde of de sleutelwaarde
Als u in plaats daarvan een For Next Loop gebruikt, kunt u de For Next-teller gebruiken om de indexwaarde te krijgen, hoewel u de 'Key'-waarde nog steeds niet kunt krijgen
| 123456789101112 | Subzoekverzameling()Dim MyCollection als nieuwe collectieMyCollection.Voeg "Artikel1" toeMyCollection.Voeg "Artikel2" toeMyCollection.Voeg "Artikel3" toeVoor n = 1 Naar MyCollection.CountAls MyCollection.Item(n) = "Item2" DanMsgBox MyCollection.Item(n) & " gevonden op indexpositie " & nStop alsVolgende nrEinde sub |
De For Next-teller (n) geeft de indexpositie weer
Een collectie sorteren
Er is geen ingebouwde functionaliteit om een verzameling te sorteren, maar met behulp van wat 'out of the box' denken, kan code worden geschreven om een sortering uit te voeren, met behulp van Excel's werkbladsorteerfunctie. Deze code gebruikt een leeg werkblad met de naam 'SortSheet' om het eigenlijke sorteren uit te voeren.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection()Dim MyCollection als nieuwe collectieDim Teller Zo Lang'Bouw collectie met items in willekeurige volgordeMyCollection.Voeg "Artikel5" toeMyCollection.Voeg "Artikel2" toeMyCollection.Voeg "Artikel4" toeMyCollection.Voeg "Artikel1" toeMyCollection.Voeg "Artikel3" toe'Vast aantal items in collectie voor toekomstig gebruik'Teller = MijnVerzameling.Aantal'Herhaal door de verzameling en kopieer elk item naar een opeenvolgende cel op 'SortSheet' (kolom A)Voor n = 1 Naar MyCollection.CountBladen ("SortBlad"). Cellen (n, 1) = Mijn Verzameling (n)Volgende nr‘Activeer het sorteerblad en gebruik de Excel-sorteerroutine om de gegevens in oplopende volgorde te sorterenSpreadsheets ("Sorteerblad"). ActiverenBereik ("A1:A" & MyCollection.Count).SelecteerActiveWorkbook.Worksheets ("SortSheet").Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Key:=Bereik( _"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlSorterenNormaalMet ActiveWorkbook.Worksheets("SortSheet").Sort.Bereikbereik instellen ("A1:A5").Kop = xlGuess.MatchCase = False.Oriëntatie = xlTopToBottom.SortMethode = xlPinYin.Van toepassing zijnEindigt met'Verwijder alle items in de collectie - merk op dat deze For Next Loop in omgekeerde volgorde loopt'Voor n = Mijn Verzameling. Tel tot 1 Stap -1MyCollection.Verwijderen (n)Volgende nr'Kopieer de celwaarden terug naar het lege verzamelobject met behulp van de opgeslagen waarde (teller) voor de 'lus'Voor n = 1 tot tellerMyCollection.Add Sheets("SortSheet").Cells(n, 1).ValueVolgende nr'Herhaal de verzameling om de volgorde te bewijzen waarin de items zich nu bevinden'Voor elk item in MyCollectionMsgBox-itemVolgende item'Wis het werkblad (sortsheet) - verwijder het indien nodig ookSheets ("SortSheet").Bereik(Cellen(1, 1), Cellen(Teller, 1)).WissenEinde sub |
Deze code maakt eerst een collectie met de items die in willekeurige volgorde zijn toegevoegd. Het kopieert ze vervolgens naar de eerste kolom op een werkblad (SortSheet).
Code gebruikt vervolgens de Excel-sorteerfunctie om de gegevens in de kolom in oplopende volgorde te sorteren. De code kan ook worden aangepast om in aflopende volgorde te sorteren.
De verzameling wordt vervolgens ontdaan van gegevens met behulp van een For Next Loop. Merk op dat de stapoptie wordt gebruikt zodat deze van het einde van de verzameling tot het begin wordt gewist. Dit komt omdat als het wordt gewist, de indexwaarden worden gereset. Als het vanaf het begin wordt gewist, zou het niet correct worden gewist (index 2 wordt index 1)
Ten slotte worden de itemwaarden met een andere For Next Loop terug naar de lege verzameling overgebracht
Een verdere For Each Loop bewijst dat de collectie nu goed oplopend is.
Helaas gaat dit niet over sleutelwaarden die oorspronkelijk zijn ingevoerd, omdat de sleutelwaarden niet kunnen worden gelezen
Een verzameling doorgeven aan een sub/functie
Een verzameling kan op dezelfde manier als elke andere parameter aan een sub of functie worden doorgegeven
| 1 | Functie MijnFunctie(ByRef MyCollection as Collection) |
Het is belangrijk om de collectie door te geven met ‘ByRef’. Dit betekent dat de originele collectie wordt gebruikt. Als de verzameling wordt doorgegeven met behulp van 'ByVal', ontstaat er een kopie van de verzameling die ongelukkige gevolgen kan hebben
Als een kopie wordt gemaakt met 'ByVal', gebeurt alles dat de collectie binnen de functie wijzigt, alleen op de kopie en niet op het origineel. Als er bijvoorbeeld binnen de functie een nieuw item aan de collectie wordt toegevoegd, verschijnt dit niet in de originele collectie, waardoor er een bug in je code ontstaat.
Een verzameling van een functie retourneren
U kunt een verzameling van een functie op dezelfde manier retourneren als een willekeurig object. U moet het trefwoord Set gebruiken
| 12345 | Sub ReturnVanFunctie()Dim MyCollection als collectieStel MyCollection = PopulateCollection inMsgBox MyCollection.CountEinde sub |
Deze code creëert een subroutine die een object met de naam 'MyCollection' maakt en vervolgens het sleutelwoord 'Set' gebruikt om de functie effectief aan te roepen om die verzameling te vullen. Zodra dit is gebeurd, wordt een berichtvenster weergegeven om het aantal van 2 items weer te geven
| 1234567 | Functie PopulateCollection() As CollectionDim MyCollection als nieuwe collectieMyCollection.Voeg "Artikel1" toeMyCollection.Voeg "Artikel2" toeStel PopulateCollection = MyCollection inFunctie beëindigen |
De functie PopulateCollection maakt een nieuw collectieobject aan en vult dit met 2 items. Vervolgens geeft het dit object terug aan het verzamelingsobject dat in de oorspronkelijke subroutine is gemaakt.
Een verzameling naar een array converteren
Misschien wilt u uw verzameling omzetten in een array. Misschien wilt u de gegevens opslaan waar ze kunnen worden gewijzigd en gemanipuleerd. Deze code maakt een kleine verzameling en zet deze vervolgens over in een array
Merk op dat de collectie-index begint bij 1 terwijl de array-index begint bij 0. Terwijl de collectie 3 items heeft, hoeft de array alleen te worden gedimensioneerd tot 2 omdat er een element 0 is
| 1234567891011121314151617 | Sub ConvertCollectionToArray()Dim MyCollection als nieuwe collectieDim MyArray(2) als stringMyCollection.Voeg "Artikel1" toeMyCollection.Voeg "Artikel2" toeMyCollection.Voeg "Artikel3" toeVoor n = 1 Naar MyCollection.CountMijnArray(n - 1) = MijnVerzameling(n)Volgende nrVoor n = 0 tot 2MsgBox MyArray(n)Volgende nrEinde sub |
Een array omzetten in een verzameling
Misschien wilt u een array converteren naar een verzameling. U wilt bijvoorbeeld op een snellere en elegantere manier toegang krijgen tot de gegevens dan met code om een array-element te krijgen.
Houd er rekening mee dat dit alleen werkt voor een enkele dimensie van de array, omdat de verzameling maar één dimensie heeft
| 123456789101112131415 | Sub ConvertArrayIntoCollection()Dim MyCollection als nieuwe collectieDim MyArray(2) als stringMijnArray(0) = "item1"MijnArray(1) = "Artikel2"MijnArray(2) = "Artikel3"Voor n = 0 tot 2MyCollection.MijnArray(n) toevoegenVolgende nrVoor elk item in MyCollectionMsgBox-itemVolgende itemEinde sub |
Als u een multidimensionale array wilt gebruiken, kunt u de arraywaarden voor elke rij binnen de array samenvoegen met een scheidingsteken tussen de arraydimensies, zodat u bij het lezen van de verzamelingswaarde het scheidingsteken programmatisch kunt gebruiken om de waarden scheiden.
U kunt de gegevens ook naar de verzameling verplaatsen op basis van het feit dat de waarde van de eerste dimensie wordt toegevoegd (index 1), en vervolgens de waarde van de volgende dimensie wordt toegevoegd (index 2), enzovoort.
Als de array bijvoorbeeld 4 dimensies had, zou elke vierde waarde in de verzameling een nieuwe set waarden zijn.
U kunt ook arraywaarden toevoegen om als sleutels te gebruiken (op voorwaarde dat ze uniek zijn), wat een gemakkelijke manier zou zijn om specifieke gegevens te lokaliseren.
