VBA voor smarties: Collection

Collection

1. Wat is een Collection

2. Een Collection maken
2.1 Methode Dim
2.2 Methode Set
2.3 Methode With ... End With
2.4 Naamgeving

3. Elementen in de Collection

3.1 Elementen toevoegen

3.2 Toevoegen met sleutel

3.3 Volgorde van elementen
3.3.1 Volgorde op index
3.3.2 Volgorde op sleutel
3.3.3 Volgorde op inhoud

4. Omvang van een Collection

5. Element opvragen

5.1 Met het indexnummer
5.2 Met de sleutel
5.3 Met de inhoud

6. Controle of element voorkomt
6.1 Met een sleutel
6.2 Met de inhoud

7. Elementen filteren
7.1 Met indexnummers
7.2 Met sleutels
7.3 Met een bepaalde inhoud

8. Elementen verwijderen
8.1 Met indexnummer
8.2 Met sleutel
8.3 Op inhoud
8.4 Verwijder alle elementen

9. Sorteren van elementen

1. Wat is een Collection

Collection maakt deel uit van de standaard VBA-bibliotheek.

Een collection is een verzameling van afzonderlijke elementen.
Je kunt in een collection allerlei soorten elementen opslaan, vergelijkbaar met een Array of een Dictionary.
Elementen in een Collection kunnen teksten zijn, getallen, datums, VBA-objekten, arrays, instanties van klassen, etc.
Elementen in een Collection kun je afzonderlijk benaderen: lezen, bewerken, wijzigen, vervangen en opslaan.

De collection bevindt zich in het werkgeheugen en is daardoor snel te benaderen en te wijzigen.
Voor allerlei taken kan dat snelheidswinst opleveren bijv. vergeleken met bewerkingen in een Excel werkblad.
Bewerkingen in een werkblad kunnen namelijk akties aktiveren (Screenupdating, Calculation, Eventprocedures) die de werking van een macro kunnen vertragen.
Met een collection kun je dat vermijden.

2. Een collection maken

Er zijn 3 methoden om een collection te maken.

In twee gevallen maak je expliciet een variabele die de Collection (en alle elementen daarin) bevat.
Dat is dan altijd een object variabele.
Die biedt de mogelijkheid de collection vanuit verschillende procedures in dezelfde codemodule te benaderen ('Private scope') of te benaderen op documentnivo (werkboek in Excel, document in Word, Database in Access of sessie in Outlook; de zg. 'Public scope').

De derde methode is impliciete toewijzing, zonder toewijzing aan een variabele, en vindt daarom altijd binnen één procedure (macro of funktie) plaats.

2.1 Methode Dim

Met de methode Dim kun je in 1 keer zowel de variabele declareren als een Collection aanmaken:

Dim c_00 as New Collection

Local scope

als je deze regel in een procedure (macro of funktie) zet kun je de Collection uitsluitend binnen dezelfde procedure gebruiken.

Private scope

als je deze regel in het declaratiegebied van een codemodule zet (document, werkblad, werkboek, macromodule, klassemodule) kun je de Collection in alle procedures van die codemodule gebruiken.

Private c_00 as New Collection

Dim c_00 as New Collection

Public scope

met deze regel in het declaratiegebied van een macromodule kun je de Collection in alle procedures in alle codemodules gebruiken.

Public c_00 as New Collection

2.2 Methode Set

Local scope

Met de methode Set kun je in 1 keer zowel de variabele declareren als een nieuwe Collection aanmaken:
De Collection c_01 kun je uitsluitend binnen de procedure gebruiken waarin de 'Set' opdracht staat.

Sub M_snb()

Set c_01 = New Collection
c_01.Add "item_1"

End Sub

Private scope

Als je de variabele toegankelijk wil maken voor alle procedures van dezelfde codemodule gebruik dan het declaratiegebied van de codemodule.
Je kunt daar de te gebruiken variabele declareren.

Als je de variabele declareert als Collection of Object, is de Typename 'Nothing' en het VarType 9.
Pas na de 'Set' opdracht is de Typename 'Collection' en kunnen er items aan de Collection worden toegewezen.

Als je de variabele declareert zonder specificatie van het type is de TypeName 'Empty' en het VarTpe 0.
Ook hier geldt dat de 'Set' opdracht ervoor zorgt dat de variabele als TypeName 'Collection' krijgt en er daardoor items aan toegewezen kunnen worden.

Dim c_03 As Collection
Dim c_04 As Object
Dim c_05

Private c_06 As Collection
Private c_07 As Object
Private c_08

Daarna moet je in de macro met een 'Set' opdracht een nieuwe instantie van de Collection aan de variabele toewijzen voordat je elementen kunt toevoegen:

' Nothing
' 9

' Collection

' Empty

' 0

' Collection

x0 = TypeName(c_03)
x1 = VarType(c_03)

Set c_03 = New Collection
x2 = TypeName(c_03)
c_03.Add "item_1"

Set c_04 = New Collection
c_04.Add "item_1"

x0 = TypeName(c_05)
x1 = VarType(c_05)
Set c_05 = New Collection
x2 = TypeName(c_05)
c_05.Add "item_1"

Set c_06 = New Collection
c_06.Add "item_1"

Set c_07 = New Collection
c_07.Add "item_1"

Set c_08 = New Collection
c_08.Add "item_1"

Public scope

Om de Collection toegankelijk te maken voor alle procedures in het document/werkboek dien je de variabele waaraan de Collection wordt toegewezen te declareren in het declaratiegebied van de codemodule van een macromodule.
Als je de variabele declareert als Collection of Object, is de Typename 'Nothing' en het VarType 9.
Na de 'Set' opdracht is de Typename 'Collection' en kunnen er items aan de Collection worden toegewezen.

Als je de variabele declareert zonder specificatie van het type is de TypeName 'Empty' en het VarTpe 0.
Ook hier geldt dat de 'Set' opdracht ervoor zorgt dat de variabele als TypeName 'Collection' krijgt en er daardoor items aan toegewezen kunnen worden.

Public c_03 As Collection
Public c_04 As Object
Public c_05

Daarna moet je in de macro eerst met een 'Set' opdracht een nieuwe instantie van de Collection aan de variabele toewijzen, voordat je elementen kunt toevoegen:

' Nothing
' 9

' Collection

' Empty

' 0

' Collection

2.3 Methode With .... End With

Met de methode With ... End With kun je een Collection aanmaken zonder daarvoor een variabele te gebruiken.

With New Collection

End With

Alles wat op deze collection betrekking heeft kan alleen maar benaderd worden binnen het With ... End With kader.
De 'scope' is daarmee per definitie beperkt tot de procedure die deze code bevat: 'local'.

2.4 Naamgeving

Het is handig een eigen conventie aan te houden voor de benaming van een variabele die een Collection bevat.
Daarmee is dan in één keer duidelijk met wat voor soort variabele je te maken hebt.
Vermijd bij naamgeving iedere referentie naar een gereserveerde naam in een Applicatie of VBA.
Het gebruik van een underscore garandeert dat.
Op deze pagina krijgen variabelen met een Collection de vorm 'c_00', 'c_01', 'c_02', etc.

3 Elementen in de Collection

De elementen in een collection hebben inhoudelijk geen enkel verband met elkaar.
De elementen kunnen gelijksoortig zijn (teksten, getallen, datums, arrays, objekten) of iedere mogelijke mix van soorten elementen.
Het enige verband is dat ze deel uitmaken van dezelfde Collection, dat ze een volgorde-indexnummer hebben en eventueel een uniek kenmerk/sleutel/key.

3.1 Zet elementen in een Collection

Er is slechts één methode om elementen in een collection te zetten: .Add
De methode .Add heeft 1 verplicht argument: de inhoud van het element.

Dim c_00 as new collection

c_00.add "abc"
c_00.add 123
c_00.add date
c_00.add array("aac","bbb","ccc")

With new Collection

.add "abc"
.add 123
.add date
.add array("aac","bbb","ccc")

End With

3.2 Zet elementen in een Collection met een unieke sleutel

De sleutel moet bestaan uit een unieke tekstreeks; iets anders wordt niet geaccepteerd.
Als een element wordt toegevoegd met een sleutel die al bestaat genereert VBA een foutmelding en breekt de uitvoering van de code af.

Dim c_00 as new collection

c_00.add "abc","sleutel_1"
c_00.add 123,"sleutel_2"
c_00.add date,"sleutel_3"
c_00.add array("aac","bbb","ccc"),"sleutel_4"

With new Collection

.add "abc","sleutel_1"
.add 123,"sleutel_2"
.add date,"sleutel_3"
.add array("aac","bbb","ccc"),"sleutel_4"

End With

Omdat niet te achterhalen is welk item welke sleutel heeft kan het handig zijn een koppeling te leggen tussen het indexnummer en de sleutel.

Dim c_00 as new collection

c_00.add "abc","K_" & .count
c_00.add 123,"K_" & .count
c_00.add date,"K_" & .count
c_00.add array("aac","bbb","ccc"),"K_" & .count

With new Collection

for j=1 to 4

.add choose(j,"abc",123,date, array("aac","bbb","ccc")),"K_" & j

End With

Als je met behulp van de unieke sleutel slechts unieke tekst-elementen in de Collection wil kan dat bijv. met:

On Error Resume Next
sn = Array("aa", "bb", "cc", "dd", "aa", "bb", "cc", "dd")
With New Collection

For Each it In sn

.Add it, it

MsgBox .Count
End With

Omdat een sleutel niet uit een getal kan bestaan kan de toevoeging van unieke getallen zó

On Error Resume Next
sn = Array(5, 4, 3, 2, 3, 4, 5, 2)
With New Collection

For Each it In sn
.

Add it, format(it)

Next
MsgBox .item(1) & vbtab & .item(2) & vbtab & .item(3) & vbtab & .item(4)

End With

3.3 De volgorde van elementen

Elk element van een Collection heeft een indexnummer.
Dat wordt toegekend op basis van de volgorde waarin de elementen aan de Collection zijn toegevoegd.
Het eerste element heeft het indexnummer 1, het laatste indexnummer komt overeen met het resultaat van de methode .count

Als je een element aan een collection toevoegt kun je meteen aangeven waar in de collection je het nieuwe element wil plaatsen: vóór een bepaald element in de collection of erná.
De methode .add heeft daarvoor een tweetal argumenten: 'before' en 'after'.
Het 'before' argument is het derde argument, 'after' is het vierde argument.
De argumenten before en after vereisen ieder het indexnummer van het item waarvoor/-na een item geplaatst moet worden.
Het achterhalen van de indexnummers vergt een afzonderlijke doorloop van de Collection.

3.3.1 De volgorde van elementen op index

In de argumenten kun je met behulp van het indexnummer aangeven vóór resp. ná welk element het nieuwe element geplaatst moet worden.

' zet dit item ná het 2e item in de Collection

' zet dit item vóór het 1e item in de Collection

With New Collection

.Add "A1"
.Add "A2"
.Add "A3", , 2

.Add "A4", 1

MsgBox .item(1) & vbtab & .item(2). & vbtab & .item(3) & vbtab & .item(4)

End With

3.3.2 De volgorde van elementen op sleutel

Als je gebruik maakt van sleutels (keys) voor de elementen kun je met een sleutel aangeven vóór resp. ná welk element het nieuwe element geplaatst moet worden.

' zet dit item ná het item met key "L2" in de Collection

' zet dit item vóór het item met key "L1" in de Collection

With New Collection

.Add "A4", "L1"
.Add "A5", "L2"
.Add "A6","L3" , "L2"

.Add "A6","L3" "L1"

MsgBox .item(1) & vbtab & .item(2). & vbtab & .item(3) & vbtab & .item(4)

End With

3.3.3 De volgorde van elementen op inhoud

Als je op grond van de inhoud van de elementen van een Collection een nieuw element wil toevoegen moet je alle elementen van de collection aflopen en testen aan de gestelde voorwaarde.
Het gevonden indexnummer gebruik je vervolgens om het nieuwe element in te passen.

' alternatieve methode

' alternatieve methode

' before
' after

With New Collection

.Add "A1"
.Add "D2"
.Add "G3"
.Add "H4"

c00 = "F9"
For j = 1 To .Count

If j = 1 And c00 < .Item(j) Then Exit For
If j > 1 Then

If c00 > .Item(j - 1) And c00 < .Item(j) Then Exit For

If j = 1 And StrComp(c00, .Item(j)) = -1 Then Exit For

If j > 1 Then

If StrComp(c00, .Item(j - 1)) & StrComp(c00, .Item(j)) = "1-1" Then Exit For

End If

Next

If j <= .Count Then .Add c00, , j
If j > .Count Then .Add c00, , , j

MsgBox .Item(1) & vbLf & .Item(2) & vbLf & .Item(3) & vbLf & .Item(4) & vbLf & .Item(5)

End With

4 De omvang van een Collection

De omvang van een Collection kun je opvragen met de eigenschap .Count

Msgbox c_00.count

Als de Collection nog geen elementen bevat is het resultaat van .count: 0.

5 Element in de Collection opvragen

Ieder element in een Collection heeft een:
- inhoud
- unieke index

Aan een element van een collection kan een unieke sleutel zijn toegekend.
Op geen enkele manier is te achterhalen welke sleutels de Collection bevat.
Evenmin is te achterhalen welke sleutel met welke index overeenkomt.
Bij het toevoegen van elementen aan de Collection kun je zelf voor een koppeling tussen indexnummer en sleutel zorgdragen; zie koppeling key en indexnummer
Je kunt op alle drie aspekten naar een element van een Collection zoeken.

5.1 Element opvragen met de index

Elk getal vanaf 1 tot .count is een geldig indexnummer.
Het resultaat is de inhoud van het element (niet de sleutel).

Het eerste element

Dim c_00 as new Collection
y=c_00(1)
y=c_00.item(1)

Het laatste element

Dim c_00 as new Collection
y=c_00(c_00.count)
y=c_00.item(c_00.count)

Het op een na laatste element

Dim c_00 as new Collection
y=c_00(c_00.count-1)
y=c_00.item(c_00.count-1)

Het vierde element:

Dim c_00 as new Collection
y=c_00(4)
y=c_00.Item(4)

with createobject new collection

.add "abc","Sleutel_4"
y=.item(4)

end with

5.2 Element opvragen met de sleutel

Wil je bijv. het element dat de sleutel "sleutel_4" heeft gekregen oproepen:

Dim c_00 as New Collection
c_00.add 12,"sleutel_4"

MsgBox c_00("sleutel_4")
of
MsgBox c_00.Item("sleutel_4")

with new Collection

.add "abc","Sleutel_4"
MsgBox .item("sleutel_4")

end with

Omdat niet te achterhalen is welke sleutels zich in de Collection bevinden, kun je deze methode alleen maar gebruiken als je weet welke sleutels in de Collection aanwezig zijn.
Daarvoor kun je die sleutels op een andere plaats in je code of in een object vastleggen.

5.3 Element opvragen met de inhoud

Om te controleren of een item in de Collection een bepaalde inhoud bevat dien je gewoon alle elementen van de Collection af te lopen.
Op basis daarvan kun je het indexnummer van het aangetroffen element bepalen.
De sleutel is echter op geen enkele manier te achterhalen via de inhoud of het indexnummer van een element.

Dim c_00 as new Collection

for j= 1 to c_00.count

if c00(j)= "gezocht" then exit for

next
if j < c00.count+1 then msgbox "index " & j

6 Controle of een element in een Collection voorkomt

6.1 Aan de hand van een sleutel

De Collection heeft geen eigen methode om weer te geven of een bepaalde sleutel bestaat.
De methode moet je dus zelf bedenken.
Het kan bijv. zó:

on error resume next
y=c_00("item_1")
msgbox "Sleutel item_1 bestaat" & iif(err.number=0,""," niet")

6.2 Aan de hand van de inhoud

De Collection heeft geen eigen methode om weer te geven of een element een bepaalde inhoud heeft.
Daarvoor kun je dezelfde methode gebruiken als weergegeven bij het uitlezen van een element op basis van de inhoud.

Dim c_00 as new Collection

for j= 1 to c_00.count

if c00(j)= "gezocht" then exit for

next
if j < c00.count+1 then msgbox "het element 'gezocht' bestaat" & j

7 Elementen filteren uit een Collection

Een Collection bevat geen filtermethode.
Als je wilt filteren zul je daarvoor zelf een procedure moeten schrijven.
Hieronder voorbeelden van filteren op basis van het indexnummer, de sleutel of de inhoud van het opgeslagen item.

7.1 Elementen filteren uit een Collection met indexnummer

Zet de te filteren indexnummers in een array en loop vervolgens die array door:

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
For Each it In Array(1, 3, 6, 7, 3, 1)

c00 = c00 & vbLf & .Item(it)

End With

MsgBox c00

7.2 Elementen filteren uit een Collection met sleutels

Zet de te filteren sleutels in een array en loop vervolgens die array door:

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
For Each it In Array("L_6", "L_4", "L_3", "L_1", "L_0")

c00 = c00 & vbLf & .Item(it)

End With

MsgBox c00

7.3 Elementen filteren uit een Collection met een bepaalde inhoud

De enige methode is alle elementen van de Collection door te lopen en per item te toetsen op de gestelde voorwaarde:

With New Collection

For j = 1 To 7

.Add choose(j,"een","uitgezocht ","drie","vier","opgezocht","zes","gezocht noch gevonden"), "L_" & .Count

Next
For j = 1 To .Count

If instr(.Item(j),"gezocht") Then c00 = c00 & vblf & .item(j)

End With

MsgBox c00

Als de Collection aan een variabele is toegewezen werkt ook de methode For each ... Next.

Set c_00 = New Collection

For j = 1 To 7

c_00.Add choose(j,"een","gezocht verleden","drie","vier","voor niets gezocht","zes","toch gezocht"), "L_" & c_00.Count

Next
For Each it In c_00

If instr(it,"gezocht") Then c00 = c00 & vblf & it

Next

MsgBox c00

8 Elementen verwijderen

Met de methode 'Remove' kun je een item verwijderen aan de hand van de index of van een sleutel.
Je moet erop bedacht zijn dat na verwijdering van een element alle indices van de elementen in de Collection na het verwijderde element worden gewijzigd.

Als je een item wil verwijderen op basis van de inhoud, zul je alle elementen van de Collection af moeten lopen en testen op de voorwaarde; verwijdering kan dan met de index ( de sleutel is nl. niet te achterhalen).

8.1 Element verwijderen met index

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
.remove 5

End With

8.2 Element verwijderen met sleutel

Als de sleutel bekend is

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
.remove "L_5"

End With

8.3 Element verwijderen op grond van de inhoud

Loop door de Collection in omgekeerde volgorde, toets per element de voorwaarde; verwijder met de index.

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
For j = .Count to 1 step -1

If .Item(j) = "CCCCC" Then .Remove j

End With

8.4 Verwijder alle elementen

De Collection heeft geen methode om alle items in één keer te verwijderen (zoals. bijv. met 'Clear' in een Dictionary of een array()).

Wanneer je de collection aan een objectvariabele hebt toegewezen kun je hetzelfde effekt bereiken door aan de variabele een nieuwe instantie van Collection toe te wijzen.

set c_00 = New Collection

Als je de Collection impliciet gedeclareerd hebt ligt het anders.
Dan zul je ieder element afzonderlijk moeten verwijderen.
Na iedere verwijdering van een element krijgen de overblijvende elementen nieuwe indexnummers.
De indexnummers bestaan uit de nummers 1 t/m .count.
Als je steeds het element met indexnummer 1 verwijdert voorkom je een foutmelding door de reïndexatie.
Die foutmelding voorkom je ook door steeds het laatste element te verwijderen

' alternatieve methode

With New Collection

For j = 1 To 7

.Add String(5, Chr(65 + j)), "L_" & .Count

Next
For j = 1 To .Count

.remove 1
.remove .count

End With

9 Sorteren van de elementen in een Collection

De Collection heeft geen methode om alle elementen te sorteren.
Voor het op volgorde invoegen van een item kun je gebruik maken van het argument 'before' of 'after' van de methode .Add.
Een bestaande Collection kun je alleen sorteren door alle elementen met een sorteermethode buiten de Collection (bijv. Arraylist, Sortedlist, sorteren in Excel of Word, etc.) te sorteren en in de gesorteerde volgorde opnieuw toe te wijzen aan de Collection.