VBA voor smarties: Arrays

Arrays

1. Wat is een array

Een array is een verzameling van afzonderlijke elementen.
Een array-variabeleis een variabele waarin je die afzonderlijke elementen kunt opslaan.

Elementen die in een array kunnen worden opgeslagen kunnen teksten zijn, getallen, datums, VBA-objekten, arrays, etc.
Elementen in een array kun je afzonderlijk benaderen: lezen, bewerken, wijzigen, vervangen en opslaan.
De array bevindt zich in het werkgeheugen en is daardoor erg snel te benaderen en te wijzigen.
Voor allerlei taken kan dat snelheidswinst opleveren vergeleken met bewerkingen in een werkblad.
Bewerkingen in een werkblad kunnen akties aktiveren (Screenupdating, Calculation, Eventprocedures) die bewerkingen kunnen vertragen.
Met arrays kun je die vermijden.

2. De naamgeving van een array variabele

Je kunt een array-variabele iedere geldige variabele-naam geven.
Het is handig een eigen conventie te hanteren om op basis van de naam te zien of een variabele een array is of niet (bijv. 'a_sn', 'a_sp', 'a_sq', etc.).
Als je gebruik maakt van een underscore _ in de naam weet je zeker dat jouw naam niet interfereert met een gereserveerde naam in VBA of in je applicatie.

3. Soorten arrays

VBA maakt onderscheid tussen 1-dimensionele arrays en meerdimensionele arrays

3.1 1-dimensionele array

In de array zijn elementen in een bepaalde volgorde opgeslagen.
Je kunt je een 1-dimensionele array voorstellen als een stapel munten van dezelfde grootte.
Als je van boven op de stapel kijkt zie je maar 1 munt (geen dimensie); maar je weet dat eronder nog diverse andere munten liggen.
Een element is uit een array te lezen door het volgnummer van het element te gebruiken: bijv a_sn(2), a_sn(7)

3.2 meer-dimensionele array

Een 2-dimensionele array is te vergelijken met een tabel of een Excel werkblad.
Elk element krijgt een plaats in de array, op basis van de 'rij' en de 'kolom'.
Een element uit een 2-dimensionele array kun je uitlezen door per dimensie het volgnummer op te geven; bijv. a_sn(2,4), a_sn(10,1)

Een 3-dimensionele array is te vergelijken met een kubus. Elk element krijgt een plaats in de array op basis van de 'rij'-, de 'kolom'- en de 'diepte'-dimensie.
Een element uit een 3-dimensionele array kun je uitlezen door per dimensie het volgnummer op te geven; bijv. a_sn(2,4,5), a_sn(10,1,10)

Het maximum aantal dimensies van een array in VBA is 60.

Hoe kun je je meerdimensionele arrays voorstellen ?

Neem Excel als voorbeeld

2-dimensionele array

Een werkblad is een 2-dimensionele array.
Een cel kun je vinden door zowel de ene dimensie (rijen) als de andere dimensie (kolommen) te specificeren:
cells(4,10) staat voor cel J4.

3-dimensionele array
Een derde dimensie wordt gevormd door bijvoorbeeld het aantal werkbladen in een werkboek:
sheets(3).cells(4,10)
Geschreven als 3-dimensionele array: a_sn(4,10,3)
Waarbij de eerste dimensie de rijen zijn, de tweede dimensie de kolommen en de derde dimensie de werkbladen.

4-dimensionele array
Een vierde dimensie kun je je voorstellen als het aantal geladen werkboeken
workbooks(7).sheets(3).cells(4,10)
Geschreven als 4-dimensionele array: a_sn(4,10,3,7)

een 5-dimensionele array en meer

- Een vijfde dimensie kun je je voorstellen als het aantal bestanden in een folder

- Een zesde dimensie als het aantal subfolders in een folder

- Een zevende dimensie als het aantal folders in een Drive

- Een achtste dimensie als het aantal drives op een systeem.

4 Eigenschappen van een array

4.1 De ondergrens: Lbound

Standaard heeft een array als ondergrens 0.
Het is even wennen maar het is mathematisch gezien de meest logische opbouw.
In een 10-tallig stelsel is 0 nl. het eerste getal.
Het eerste element
- in een 1-dimensionele array: a_sn(0),
- in een 2-dimensionele array: a_sn(0,0)
- in een 3-dimensionele array: a_sn(0,0,0).

Je kunt in VBA instellen dat de ondergrens 1 moet zijn (Option base 1).

De ondergrens van een 1-dimensionele array bepaal je met de VBA-methode Lbound.

x=Lbound(a_sn)

Bij meer-dimensionele arrays moet je aangeven van welke dimensie je de ondergrens zoekt;
- de ondergrens van de eerste dimensie: Lbound(a_sn,1) of Lbound(a_sn)
- de ondergrens van de tweede dimensie: Lbound(a_sn,2)
- de ondergrens van de derde dimensie: Lbound(a_sn,3)

4.2 De bovengrens: Ubound

De bovengrens (Upperbound) van een 1-dimensionele array vind je met de VBA-methode Ubound.

x=Ubound(a_sn)

Omdat standaard de ondergrens van een dimensie in een array 0 is, bevat zo'n array dus Ubound(a_sn)+1 elementen.

Bij meer-dimensionele arrays moet je aangeven van welke dimensie je de bovengrens zoekt;
De bovengrens
- eerste dimensie: Ubound(a_sn,1) of Ubound(a_sn)
- tweede dimensie: Ubound(a_sn,2)
- derde dimensie: Ubound(a_sn,3)

De eerste dimensie met ondergrens 0 van een array bevat Ubound(sn,1)+1 (of Ubound(a_sn)+1) elementen
De tweede dimensie met ondergrens 0 van een array bevat Ubound(sn,2)+1 elementen
De derde dimensie met ondergrens 0 van een array bevat Ubound(sn,3)+1 elementen

4.3 Het type array: Vartype

Met Vartype kun je achterhalen of een variabele een array is, en zo ja met welke inhoud.
Het Vartype nummer van een array is 8192.
Vartype is een sommatie van de Vartype voor een array (8192) en de Vartype van de inhoud.
De Vartype-nummers van de verschillende inhoudtypen; voor een gewone variabele en na () voor een arrayvariabele

2 () 8194
3 () 8195
4 () 8196
5 () 8197

Integer
Long
Single
Double

6 () 8198
7 () 8199
8 () 8200
9 () 8201

Currency
Date
String
Object

11 () 8203
12 () 8204
17 () 8209
36 () 8228

Boolean
Variant
Byte
UserDefinedType

' 8204=8192 (array) + 12(variant)
' 8200=8192 (array) + 8 (string)
' 8204=8192 (array) + 12 (variant)
' 8199=8192 (array) + 7 (date)

a_sn=Array(1, 2, 3, 4)
a_sp=Split("1,2,3,45", ",")
a_sq=Range("A1:F5")
Dim a_st() As Date

x=VarType(a_sn)
x=VarType(a_sp)
x=VarType(a_sq)
x=VarType(a_st)

4.4 De naam van de soort array: TypeName

Met TypeName kun je achterhalen wat voor soort array een variabele bevat.

' Variant()
' String()
' Variant()
' Date()

' Range
' Variant

a_sn=Array(1, 2, 3, 4)
a_sp=Split("1,2,3,45", ",")
a_sq=Range("A1:F5")
Dim a_st() As Date

x=TypeName(a_sn)
x=TypeName(a_sp)
x=TypeName(a_sq)
x=TypeName(a_st)

x=TypeName(Range("A1:F5"))
x=TypeName(Range("A1:F5").Value)

In tegenstelling tot VarType interpreteert TypeName de Range in een werkblad niet als een array.
Wanneer je de Range echter inleest in een variabele wordt de defaulteigenschap - Value - ingelezen, zodat de variabele meteen als array wordt geïnterpreteerd.

4.5 Controleer of een variabele een array is: IsArray

' True
' True
' True
' False
' False

a_sn=Array(1, 2, 3, 4)
a_sp=Split("1,2,3,45", ",")
a_sq=Range("A1:F5")
a_sr=Range("F5")
a_st="aaa"

y=IsArray(a_sn)
y=IsArray(a_sp)
y=IsArray(a_sq)
y=IsArray(a_sr)
y=IsArray(a_st)

IsArray interpreteert een Range in een werkblad als een array, tenzij de range 1 cel is.

4.6 Redim

Voor het gebruik van Redim zie 5.3.1.3 en 5.3.1.4

4.7 Erase

MS noemt Erase een 'Statement'.

4.7.1 Variant variabele met een array

Erase verwijdert de omvang van een array die aan een variantvariabele is toegewezen.

a_sn=Array(2,55,73,822,200)

msgbox Ubound(a_sn)
Erase a_sn

Na de toepassing van Erase heeft variabele a_sn, met Ubound(a_sn)=4 geen Ubound of lbound meer.

Erase verwijdert de omvang van een array die als Variant is gedeclareerd.

Dim a_sn

a_sn=Array(2,55,73,822,200)
msgbox ubound(a_sn)
erase a_sn

Na de toepassing van Erase heeft variabele a_sn, met Ubound(a_sn)=4 geen Ubound of Lbound meer.

4.7.2 Dynamische array

Erase verwijdert de omvang van een dynamische array die met Dim is gedeclareerd

Dim a_sp()

ReDim a_sp(2, 2)
For j=0 To UBound(a_sp)

For jj=0 To UBound(a_sp, 2)

a_sp(j, jj)=j * jj

Next

MsgBox VarType(a_sp) & vbTab & UBound(a_sp) & vbTab & UBound(a_sp, 2)
Erase a_sp

Na de toepassing van Erase heeft variabele a_sp, met Ubound(a_sp)=2 en Ubound(sp,2)=2, geen Ubound meer van beide dimensies.

Erase verwijdert de omvang een dynamische array die met ReDim is gedeclareerd

ReDim a_sp(2, 2)

For j=0 To UBound(a_sp)

For jj=0 To UBound(a_sp, 2)

a_sp(j, jj)=j * jj

Next

MsgBox VarType(a_sp) & vbTab & UBound(a_sp) & vbTab & UBound(a_sp, 2)
Erase a_sp

Na de toepassing van Erase heeft variabele a_sp, met Ubound(a_sp)=2 en Ubound(sp,2)=2, geen Ubound meer voor beide dimensies.

4.7.3 Array met vaste omvang

Erase verwijdert de inhoud (en niet de omvang !) van een met vaste omvang gedeclareerde array.

Dim a_sq(5, 10)

For j=0 To UBound(a_sq)

For jj=0 To UBound(a_sq, 2)

a_sq(j, jj)=j * jj

Next

MsgBox VarType(a_sq) & vbTab & UBound(a_sq) & vbTab & UBound(a_sq, 2)
MsgBox a_sq(0, 0) & vbLf & a_sq(UBound(a_sq), UBound(a_sq, 2))

Erase a_sq
MsgBox a_sq(0, 0) & vbLf & a_sq(UBound(a_sq), UBound(a_sq, 2))
MsgBox VarType(a_sq) & vbTab & UBound(a_sq) & vbTab & UBound(a_sq, 2)

Na de toepassing van Erase heeft variabele a_sq geen inhoud meer, maar is de Ubound van iedere dimensie intact gebleven.

5. Hoe maak je een array ?

Er zijn 5 methoden om een array te maken
- de VBA methode 'Array'
- de VBA methode 'Split'
- declareer de array variabele
- wijs de inhoud een object (in VBA, Excel of Word) met een array-karakter toe aan een variabele
- geef waarden door aan een macro of funktie die de argumenten/parameters opslaat in een paramarray

5.1 De VBA methode 'Array'

Met deze methode doe je 2 dingen tegelijkertijd: je definieert een array en je vult hem met waarden.
Met deze methode kun je alleen een 1-dimensionele array maken.
In een array kun je tekst, getallen, data, objecten, arrays en een combinatie ervan zetten.

VBA-technisch
de resulterende array-variabeleis
TypeName: Variant()
Vartype: 8204=8102 (array) + 12 ( variant)

Voorbeelden

' 5 elementen: Ubound(a_sn)=4
' 6 elementen: Ubound(a_sp)=5
' 4 elementen: Ubound(a_sq)=3
' 6 elementen: Ubound(a_st)=5

a_sn=Array(1,2,4,6,8)
a_sp=Array("qq","ww","ee","rr","tt","yy")
a_sq=Array(date,date+1,date+2,date+3)
a_st=Array("tekst",3,Date+12,sheet1,Range("A10:K30"),array(1,2,3,4,5))

Van al deze arrays is het eerste element a_sn(0), resp. a_sp(0), a_sq(0), a_st(0)

5.2 De VBA methode Split

Ook met deze methode doe je 2 dingen tegelijkertijd: je definieert een array en je vult hem met waarden.
Je kunt deze methode alleen maar gebruiken met een tekstreeks(string).
De elementen van de resulterende array bestaan uit tekst.
Je splitst de tekenreeks aan de hand van een bepaald 'splitsings'teken; bijv. de komma.
Het resultaat is een 1-dimensionele array met louter tekstelementen.

a_sp=split("aaa,fff,45,date,89,,ppp",",")

Het resultaat is een array met 7 elementen: a_sp(0) t/m a_sp(6)
a_sp(0)="aaa"
a_sp(1)="fff"
a_sp(2)="45"
a_sp(3)="date"
a_sp(4)="89"
a_sp(5)=""
a_sp(6)="ppp"

De ondergrens van deze array is 0
De bovengrens is 6
Alle elementen zijn tekenreeks (string)-elementen.

VBA-technisch
de resulterende array variabele
TypeNamwe: String()
VarType: 8200=8102 (array) + 8 (string)

Als je aan een element van deze array een getal toekent bijv. a_sp(5)=2430 wordt het getal omgezet in een tekstwaarde.

5.3 Declareer de array-variabele

De declaratie van een array bepaalt de omvang van de array.
Die wordt dan in een later stadium gevuld.
De declaratie bepaalt welke ruimte voor de array beschikbaar is.
Met de declaratie kun je tegelijkertijd ook de reikwijdte van de variabele bepalen (zie 5.3.2).
Met de declaratie kun je tegelijkertijd de inhoud van de variabele bepalen (zie 5.3.3

5.3.1 Hoe declareer je een array-variabele?

Gebruik de instructie Dim.

5.3.1.1 Declareer een 1-dimensionele array-variabelemet vaste omvang

Dim a_sp(5)

De variabele a_sp heeft als ondergrens 0, als bovengrens 5 en kan 6 elementen bevatten.

- Bij het gebruik van de methode 'Dim' kun je alleen positieve gehele getallen invoeren als argument voor de omvang van de array.
- Een variabele, een berekening of een eigenschap wordt niet als argument geaccepteerd.
- Ook een berekening wordt niet geaccepteerd.
- Wil je gebruik maken van variabelen of een berekening van de omvang van een array, gebruik dan de methode 'Redim'.

Wanneer je een andere ondergrens wil gebruiken moet je die specificeren

Dim a_sp(1 to 12)

De variabele a_sp heeft als ondergrens 1, als bovengrens 12 en kan 12 elementen bevatten.

Je kunt ook een andere ondergrens kiezen. Of dat zinvol is is de vraag.
Dit is dus ook een geldige declaratie

Dim a_sp(4 to 20)

De variabele a_sp heeft als ondergrens 4, als bovengrens 20 en kan 17 elementen bevatten.

5.3.1.2 Declareer een meer-dimensionele array-variabelemet een vaste omvang

Dim a_sp(500,20)

- Beide dimensies van variabele a_sp hebben als ondergrens 0
- de bovengrens van de eerste dimensie is 500, de bovengrens van de tweede dimensie is 20
- de variabele kan 501 * 21 elementen bevatten.

- Bij het gebruik van de methode 'Dim' kun je alleen positieve gehele getallen invoeren als argument voor de omvang van de array.
- Een variabele, een berekening of een eigenschap wordt niet als argument geaccepteerd.
- Ook een berekening wordt niet geaccepteerd.
- Wil je gebruik maken van variabelen of een berekening van de omvang van een array, gebruik dan de methode 'Redim'.

Wanneer je een andere ondergrens voor een dimensie wil gebruiken moet je die specificeren

Dim a_sp(1 to 500,1 to 20)

In variabele a_sp heeft
- de eerste dimensie als ondergrens 1 en als bovengrens 500
- de tweede dimensie als ondergrens 1 en als bovengrens 20
- en kan dus 500*20 elementen bevatten.

Je kunt ook een andere ondergrens kiezen. Zelfs voor iedere dimensie. Of dat zinvol is is de vraag.
Dit is dus ook een geldige declaratie:

Dim a_sp(4 to 500,2 to 20)

In variabele a_sp heeft
- de eerste dimensie als ondergrens 4, als bovengrens 500
- de tweede dimensie als ondergrens 2 en als bovengrens 20
- en kan dus 496*18 elementen bevatten.

5.3.1.3 Declareer een 1-dimensionele array-variabele met een dynamische omvang

Met de Dim methode bepaal je vooraf, voordat de macro gaat lopen, wat de omvang van een array moet zijn.
Die omvang staat daarmee voor de hele looptijd van de macro vast.
Niet in alle gevallen kun je vooraf weten wat de omvang van een array moet zijn.
Dan kun je gebruik maken van de methode Redim.
De ReDim methode accepteert in tegenstelling tot de Dim methode wel variabelen, expressies, etc. om de omvang van de variabele te bepalen.
Bovendien is het mogelijk om de omvang van een array die met de ReDim methode is gemaakt te wijzigen.

Met de ReDim methode zijn dit dus geldige declaraties:

ReDim a_sn(thisworkbook.sheets.count)

y=12
Redim a_sq(y)

redim a_st(date)

In deze voorbeelden is de ondergrens van de array-variabele 0
De bovengrens is afhankelijk van de waarde van het argument.
Houd er rekening mee dat de array door de ondergrens 0 altijd 1 element groter is dan het argument.

Nadat de array op deze manier gedeclareerd is kan die gevuld worden

ReDim a_sn(thisworkbook.sheets.count-1)

For j=0 to Ubound(a_sn)

a_sn(j)=thisworkbook.sheets(j+1).Name

Wil je de 1 als ondergrens dan kan dat met

ReDim a_sn(1 to thisworkbook.sheets.count)

y=12
redim a_sq(1 to y)

redim a_st(1 to date)

Nu bevat iedere array evenveel elementen als het argument.
Het vullen gaat dan als volgt

ReDim a_sn(1 to thisworkbook.sheets.count)

for j=1 to ubound(a_sn)

a_sn(j)=thisworkbook.sheets(j).Name

5.3.1.4 Declareer een meer-dimensionele array-variabele met een dynamische omvang

Met de Dim methode bepaal je vooraf, voordat de macro gaat lopen, wat de omvang van een array moet zijn.
Die omvang staat daarmee voor de hele looptijd van de macro vast.
Niet in alle gevallen kun je vooraf weten wat de omvang van een array moet zijn.
Dan kun je gebruik maken van de methode Redim.
De ReDim methode accepteert in tegenstelling tot de Dim methode wel variabelen, expressies, etc.
Bovendien is het mogelijk om de omvang van een array die met de ReDim methode is gemaakt te wijzigen.

Met de ReDim methode zijn dit dus geldige declaraties:

ReDim a_sn(thisworkbook.sheets.count,thisworkbook.sheets.count)

y=12
x=20
Redim a_sq(y,x)

ReDim a_st(date,weekday(date,2))

ReDim a_sn(thisworkbook.sheets.count-1, thisworkbook.sheets.count-1)

For j=0 to Ubound(a_sn)

For jj=0 to Ubound(a_sn,2)

a_sn(j,jj)=Choose(jj+1,thisworkbook.sheets(j+1).Name,thisworkbook.sheets(j+1).hyperlinks.count)

Wil je de 1 als ondergrens dan kan dat met

ReDim a_sn(1 to thisworkbook.sheets.count,1 to thisworkbook.sheets.count)

y=12
x=20
Redim a_sq(1 to y, 1 to x)

Redim a_st(1 to date, 1 to weekday(date,2))

Nu bevat iedere array evenveel elementen als het argument.
Het vullen gaat dan als volgt

ReDim a_sn(1 to thisworkbook.sheets.count, 1 to thisworkbook.sheets.count)

For j=1 to Ubound(a_sn)

For jj=1 to Ubound(a_sn,2)

a_sn(j,jj)=Choose(jj,thisworkbook.sheets(j).Name,thisworkbook.sheets(j).hyperlinks.count)

5.3.2 De reikwijdte en lokatie van de declaratie

De reikwijdte van een variabele kan
- één macro/funktie zijn,
- één codemodule zijn: werkboek, werkblad, macromodule, klassemodule of userform
- het hele werkboek met alle objecten zijn: het VBA projekt.

5.3.2.1 Reikwijdte 'Local': één macro of funktie

De variabele kan alleen gebruikt worden in de macro of funktie waarin de variabele is gedeclareerd.
Als de variabele alleen gebruikt wordt in 1 macro of funktie kan de declaratie overal in die macro of funktie gezet worden.
In een andere macro/funktie kun je geen waarden uit deze variabele lezen/wijzigen.

Je gebruikt daarvoor de instruktie 'Dim' of 'Redim'.
Het gebruik van Private a_sp(5) of Public a_sp(5) in een macro of funktie veroorzaakt een melding van een syntaxisfout.
Voor de overzichtelijkheid kan het handig zijn de declaratie als eerste in de macrocode te zetten.

Zet in de macro/funktie

sub M_example()
Dim a_sn(5)
' VBA code
end sub

sub M_example()
Redim a_sn(5)
' VBA code
end sub

5.3.2.2 Reikwijdte 'Private'

De reikwijdte 'Private' zorgt ervoor dat alle macro's / funkties in dezelfde codemodule (werkboek, werkblad, userform, macromodule of klassemodule) van de variabele gebruik kunnen maken.

Iedere codemodule heeft een apart declaratiedeel aan het begin van de codemodule.
Alle declaraties die daar staan zijn op de hele codemodule van toepassing.
In het declaratiedeel van een codemodule kun je geen gebruik maken van de instructie 'ReDim'.
Je kunt gebruik maken van het sleutelwoord 'Private', of de Instructie 'Dim' gebruiken. Het resultaat is hetzelfde.
Zet in het declaratiedeel (vóór de eerste macro/funktie) van de codemodule.

Private a_sp(5)

Dim a_sp(5)

Alle declaraties in het declaratiedeel van een codemodule zijn per definitie Private.
Andere codemodules kunnen geen gebruik (lezen/schrijven) maken van deze variabelen.

5.3.2.3 Reikwijdte 'Public'

De reikwijdte 'Public' zorgt ervoor dat alle macro's / funkties in hetzelfde werkboek (in de codemodules van het werkboek, ieder werkblad, ieder userform, iedere macromodule of iedere klassemodule) van deze variabele gebruik kunnen maken.

De declaratie van een 'Public' variabele moet altijd in een macromodule staan.
Gebruik het sleutelwoord 'Public' om de variabele als publiek te declareren.

Zet in het declaratiedeel van de codemodule van een macromodule

Public a_sp(5)

5.3.3 Declareer als Variant-variabele of als array-variabele

Als je een array toegankelijk wil maken voor meer macro's/funkties zul je de array moeten declareren.
Dan heb je de keuze de array als een Variant variabele te declareren of als een array.
De variabele van het type Variant is nl. de enige die een array kan bevatten.

5.3.3.1 Declareer als Variant variabele

Je kunt in een variant variabele allerlei soorten elementen opnemen; dus ook een array.
Als je een variantvariabele declareert, zul je die wel later moeten vullen met een array (met Dim , ReDim of Array of met een methode uit 5.4)
Een Variant variabele declareer je met de instructie

Dim a_sn as Variant

Omdat de Variant het defaulttype is voor een variabele volstaat deze instructie ook

Dim a_sn

Voorbeelden

Met Dim in de macro

Dim a_sn

Sub M_snb()

Dim a_sn(7)

a_sn(UBound(a_sn))=12
MsgBox a_sn(UBound(a_sn))

end sub

Met ReDim in de macro

Dim a_sn

Sub M_snb()

ReDim a_sn(9)

a_sn(UBound(a_sn))=312
MsgBox a_sn(UBound(a_sn))

end sub

Met array in de macro

Dim a_sn

Sub M_snb()

a_sn=Array(12,45,36)
MsgBox a_sn(UBound(a_sn))

end sub

met Split in de macro

Dim a_sn

Sub M_snb()

a_sn=split("aaa,450," & date,",")
MsgBox a_sn(UBound(a_sn))

end sub

NB. alle elementen worden door de methode 'Split' in tekstwaarden omgezet.

5.3.3.2 Declareer als Array variabele

Je kunt de variabele ook meteen als array declareren.
Dat kan een array met een vaste omvang zijn of met een onbepaalde/dynamische omvang.
Je kunt bij de declaratie aangeven welke inhoud de array gaat krijgen: zie 5.3.4.
Als je niets over de inhoud van de array specificeert wordt de array automatisch een variant.

1-dimensionele array met vaste omvang

Dim a_sn(5)
Dim a_sn(1 to 22)
Private a_sn(40)
Public a_sn(6)

1-dimensionele array met onbepaalde omvang

Dim a_sn()
Private a_sn()
Public a_sn()

meer-dimensionele array met bepaalde omvang

Dim a_sp(5,12,4)
Dim a_sn(1 to 22,1 to 60)
Private a_sn(40,10)
Public a_sn(6,6,6)

meer dimensionele array met onbepaalde omvang

Dim a_sn()
Private a_sn()
Public a_sn()

In al deze gevallen is de variabele een array-variabelemet inhoud variant.

5.3.4 Declareer de inhoud van de Array

Als je de array voor gebruik gaat declareren kun je ook instellen welke inhoud de array gaat/mag krijgen.
Wil je dat de array alleen tekstelementen gaat bevatten gebruik dan

dim a_sn(10) as String
dim a_sn() as String

Als de array alleen integer getallen mag bevatten gebruik dan

dim a_sn(27) as Integer
dim a_sn() as Integer

Alle typen voor variabelen zijn ook van toepassing op arrays.

Dim a_sn As Integer
Dim a_sn As Byte
Dim a_sn As Boolean
Dim a_sn As Integer
Dim a_sn As Long
Dim a_sn As LongPtr
Dim a_sn As Single
Dim a_sn As Double
Dim a_sn As Currency
Dim a_sn As Date
Dim a_sn As Object
Dim a_sn As String
Dim a_sn As Variant

De TypeName verschijnt bij string variabelen als 'String'
De TypeName van een string array is 'String()'

dim a_sn() as string
msgbox TypeName(a_sn)

Het Vartype is de sommatie van de Vartype van een array (8192) + het type variabele (string=12)
De Vartype van een string Array is dus 8192 +12=8204

dim a_sn(5,9) as string
msgbox Vartype(a_sn)

5.4 Gebruik objecten / eigenschappen om een array te maken

Diverse objecten in Excel of VBA bibliotheken en de eigenschappen van deze objecten resulteren in een 1-dimensionele of meerdimensionele array.
Zo is bijv. een Excel werkblad een 2-dimensionele array

5.4.1 Range in Excel werkblad

5.4.1.1 Een meer-dimensionele array

a_sn=Range("A1:K6").Value

Het resultaat
- Een array a_sn van het type 'Variant', met de gegevens van het werkbladgebied A1:K6.
- Vartype(a_sn)=8204 (8192 (array) + 12 (variant))
- TypeName(a_sn) is Variant().
- De ondergrens van de eerste dimensie van a_sn is 1; van de tweede dimensie ook 1. - De bovengrens van de eerste dimensie van a_sn is 6; van de tweede dimensie 11.
Omdat 'Value' de defaultwaarde is van een range, zijn alternatieve schrijfwijzen

a_sn=range("A1:K6")
a_sn=[A1:K6]

De bovenstaande code gaat uit van een statisch gebied.
Wanneer je de omvang van een array flexibeler wil maken via een gebied in een werkblad kan dat met de methode 'Resize'.
Met de methode 'Resize' kun je, uitgaande van 1 cel, de omvang van de eerste (rijen) en tweede dimensie (kolommen) aangeven

x=6
y=11
a_sn=cells(1,1).Resize(6,11)
a_sn=cells(1).Resize(6,11)
a_sn=[A1].Resize(6,11)
a_sn=cells(1,1).Resize(x,y)
a_sn=cells(1).Resize(x,y)
a_sn=[A1].Resize(x,y)

5.4.1.2 1-dimensionele array

Iedere 'Range' in een Excelwerkblad wordt bepaald door de rijen en kolommen.
Ook alleen een rij of alleen een kolom is een meerdimensionele array

Met twee verschillende Excelfunkties kun je van een meer-dimensionele array, een 1-dimensionele array maken
Voorwaarde is wel dat alle gegevens óf in 1 rij óf in 1 kolom staan. De ondergrens (Lbound) van een array die via een werkbladgebied is gemaakt is altijd 1.

Gegevens in één rij

Application.Index

a_sn=Application.Index(Range("A1:M1").Value, 1, 0)
a_sn=Application.Index(Range("A1:M1"), 1, 0)

Application.Transpose

a_sn=Application.Transpose(Application.Transpose(Range("A1:M1").Value))
a_sn=Application.Transpose(Application.Transpose(Range("A1:M1")))
a_sn=[Transpose(Transpose(A1:M1))]

Gegevens in één kolom

Application.Transpose

a_sn=Application.Transpose(Range("A1:A10").Value)
a_sn=[Transpose(A1:A10)]

Resultaat
- Een array a_sn van het type 'Variant', met de gegevens van het aangegeven werkbladgebied.
- Vartype(a_sn)=8204 (8192 (array) + 12 (variant))
- TypeName((a_sn) is Variant().
- De ondergrens van de eerste dimensie van a_sn is 1. - De bovengrens van de eerste dimensie van a_sn is gelijk aan het aantal cellen van het aangegeven werkbladgebied.

5.4.2 Een ActiveX-control

Zowel een ComboBox als een ListBox is/bevat een array.
Dat kan een 1-dimensionele array zijn of een 2-dimensionele.
De ondergrens van iedere dimensie is 0.
Een ComboBox/ListBox vul je met een array

a_sn=array(22,44,77,88)
Combobox1.list=a_sn
Listbox1.list=a_sn

Combobox/listbox-gegevens schrijf je naar een array met

a_sn=combobox1.List
a_sn=listbox1.list

Houd er rekening mee dat de ondergrens van beide dimensies in een combobox/Listbox 0 is.
Het aantal items (eigenschap .Listcount) in een listbox/combobox is daarom altijd 1 groter dan de bovengrens van de eerste dimensie

a_sn=array(22,44,77,88)
Combobox1.List=a_sn

nu is
Combobox1.Listcount: 4
Ubound(ComboBox.List)=3
het eerste item krijg je met

c00=Combobox1.list(0)

Omdat een combobox/Listbox zowel een array karakter heeft als bepaalde eigenschappen van een collection/dictionary kan een combobox/listbox van pas komen om elementen in een array te wijzigen, toe te voegen of te verwijderen.

5.4.3 De methode 'Evaluate'

Met de Evaluate methode in Excel kun je arrays maken door gebruik te maken van het array-karakter van een werkblad.
In feite maak je met de Evaluate-methode arrayformules die je niet in een werkblad zet maar waarmee je een variabele in het werkgeheugen maakt.

5.4.3.1 een 1-dimensionele array

De gegevens in 1 kolom

a_sn=[Transpose(A1:A20)]

- een array met 20 elementen
- met de inhoud van de range A1:A20
- met een ondergrens 1

De gegevens in 1 rij

a_sn=[Transpose(Transpose(A1:K1))]

- een array met 11 elementen
- met de inhoud van de range A1:K1
- met een ondergrens 1

Wil je een lege array

vanuit een kolom

a_sn=[Transpose(iferror(A1:A20/0,""))]

a_sn=[Transpose(if(A1:A20="","",""))]

Vanuit een rij kan het ook, maar is omslachtiger, terwijl het resultaat hetzelfde is.

a_sn=[Transpose(Transpose(if(A1:K1="","","")))]
a_sn=[Transpose(Transpose(iferror(A1:K1/0,"")))]

Je kunt met Evaluate de array meteen vullen met de resultaten van een bewerking/berekening.
Die bewerking/berekening kan met de gegevens uit een werkblad plaatsvinden of zonder enig verband met enig werkblad.

Voeg aan iedere waarde in range A1:A20 het rijnummer toe

a_sn=[Transpose(A1:A20&"_"&row(A1:A20))]

Vermenigvuldig iedere waarde in A1:A20 met 12

a_sn=[Transpose(12*A1:A20)]

Berekening los van enig werkboek

a_sn=[Transpose(row(1:20))]

Resultaat
- een array met 20 elementen
- met de getallen 1 t/m 20
- met een ondergrens 1

a_sn=[Transpose(text(today()+row(1:365),"dd-mm-yyyy"))]

Resultaat
- een array met 365 elementen
- met de datums van morgen t/m vandaag over een jaar
- met een ondergrens 1

Sub M_Permutaties_van_5()

sn=[index(int((row(1:125)-1)/25)+1 & int(mod((row(1:125)-1),25)/5)+1& mod(row(1:125)-1,5)+1,)]

End Sub

Resultaat
- een array met 125 elementen
- met alle permutaties van 3 elementen uit de getallen 1 t/m 5
- met een ondergrens 1

5.4.3.2 een 2-dimensionele array

De simpelste is natuurlijk een range in het werkblad

a_sn=[A1:K10]

Resultaat
- een array met 11 * 10 elementen
- met de gegevens uit range(A1:K10) van het aktieve werkblad
- met ondergrens 1 voor beide dimensies

Als je een lege array wil

a_sn=[if(A1:K10="","","")]

Resultaat
- een array met 11 * 10 elementen
- zonder de gegevens uit range(A1:K10) van het aktieve werkblad
- met ondergrens 1 voor beide dimensies

Een berekende 2-dimensionele array, die inhoudelijk niets met een werkblad van doen heeft

Sub unieke_combinaties_2letters_uit5()

a_sn=[if(row(1:5)<Transpose(row(1:5)),mid("ZFHKN",row(1:5),1) & Transpose(mid("ZFHKN",row(1:5),1)),"")]

End Sub

Resultaat
- een array met 5 * 5 elementen
- alle unieke combinaties van 2 letters uit een groep van 5 (ZFHKN)
- met ondergrens 1 voor beide dimensies

Sub exponentiele_reeksen()

a_sn=[row(A1:D10)^column(a1:D10)]

end sub

Resultaat
- een array met 10 * 4 elementen
- ieder rijnummer tot de macht van de kolom
- met ondergrens 1 voor beide dimensies

5.4.4 De dictionary

5.4.4.1 1-dimensionele array

In een dictionary kun je afzonderlijke gegevens opslaan
Ieder gegeven krijgt een unieke sleutel.
De dictionary maakt automatisch van alle sleutels een 1-dimensionele array.
Die kun je uitlezen

Sub M_snb()

with createobject("scripting.dictionary")

x0=.item("een")
x0=.item("twee")
x0=.item("drie")
a_sn=.keys

end with

end sub

Resultaat
- een array met drie elementen: "een", "twee" en "drie"
- met ondergrens 0

Als de afzonderlijke gegevens teksten, getallen of datums zijn kun je ook de items als array uitlezen

Sub M_snb()

with createobject("scripting.dictionary")

.item("it_" & .count)="een"
.item("it_" & .count)="twee"
.item("it_" & .count)="drie"
a_sn=.items

end with

end sub

Resultaat
- een array a_sn met drie elementen: "een", "twee" en "drie"
- met ondergrens 0

5.4.4.2 2-dimensionele array

In een dictionary kun je 1-dimensionele arrays als elementen opslaan.
Met de Excel funktie index kun je die 'samensmeden' tot een 2-dimensionele array.
De 1-dimensionele array moeten wel even groot zijn (dezelfde bovengrens (Ubound)).

Sub M_snb()

with createobject("scripting.dictionary")

.item("it_" & .count)=array("een","twee","drie","vier")
.item("it_" & .count)=array("vier","vijf","zes","zeven")
.item("it_" & .count)=array("zeven","acht","negen",tien")
a_sn=Application.index(.items,0,0)

end with

end sub

Resultaat
- een 2-dimensionele array met 3 * 4 elementen
- met ondergrens 0 voor beide dimensies

NB. Wanneer de items bestaan uit 2-dimensionele arrays kun je ze niet 'samensmeden' met de Index funktie van Excel.

5.5 Het argument ParamArray

In VBA kun je argumenten doorgeven aan een macro of funktie.
Alle soorten gegevens kunnen als argument worden doorgegeven en gebruikt in de ontvangende macro/funktie: tekst, getallen, datums, arrays en VBA objekten.
De keus van de variabele naam waarin het argument wordt opgeslagen (in het onderstaande voorbeeld 'c00') is volledig vrij.

Sub M_snb()

M_snb_000 "Illustratie"

End sub

Sub M_snb_000(c00)

msgbox c00

end sub

Als je verschillende argumenten wil doorgeven moet je die van elkaar scheiden met een komma:

Sub M_snb()

M_snb_000 "Illustratie",4,date

End sub

Sub M_snb_000(c00,y,d01)

msgbox c00
msgbox y
msgbox d01

end sub

Je kunt in de ontvangende macro per variabele aangeven welk soort type gegevens kunnen worden ontvangen.

Sub M_snb()

M_snb_000 "Illustratie",4,date

End sub

Sub M_snb_000(c00 As String, y As Integer, d01 As Date)

msgbox c00
msgbox y
msgbox d01

end sub

Je kunt in plaats van naar afzonderlijke ontvangende variabelen de verschillende argumenten/parameters naar een paramarray schrijven.
Dat is een dynamische 1-dimensionele Variant array die alle doorgegeven argumenten/parameters kan bevatten.
De paramarray is 'optioneel': als je geen argument doorgeeft ontstaat er geen foutmelding.
De paramarray is dynamisch: je kunt 1 argument doorgeven of een onbeperkt aantal.
De paramarray is 1-dimensioneel: de ondergrens is 0, de bovengrens afhankelijk van het aantal doorgegeven argumenten.
De paramarray is een Variant: je kunt de paramarray niet beperken tot een bepaald type gegevens (bijv. tekst, of integers).
De 'levensduur' van de array blijft beperkt tot de ontvangende macro/funktie.

Sub M_snb()

M_snb_param "aa1", 34, Date, Array(1, 2, 3, 4, 5), Range("A1:K10"), Range("A1:K10").Value

End Sub

' tekst
' getal
' datum
' 1-dimensionele array
' Object
' 2-dimensionele array

Sub M_snb_param(ParamArray a_sn())

MsgBox LBound(a_sn) & vbLf & UBound(a_sn)
MsgBox a_sn(0)
MsgBox a_sn(1)
MsgBox a_sn(2)
MsgBox a_sn(3)(4)
MsgBox a_sn(4).Address
MsgBox a_sn(5)(2, 2)

End Sub

6. Werken met arrays

Een array is geen VBA-object met eigen eigenschappen en/of methoden of gebeurtenissen.
Wil je bewerkingen uitvoeren in arrays of met arrays dan zul je gebruik moeten maken van methoden uit verschillende VBA bibliotheken.
Een belangrijk hulpmiddel is de (werkblad)funktie Index uit de Excelbibliotheek.
Omdat dit niet de meest eenvoudige funktie is besteed ik hieraan apart uitgebreid aandacht.
Bij ieder gebruik van de Excel funktie 'Index' wordt dan voor nadere toelichting naar deze plaats verwezen.

In alle voorbeelden met 2-dimensionele arrays wordt voor de eenvoud als variabele een range uit een werkboek gelezen.
Dat is de snelste methode om een gevulde 2-dimensionele array te verkrijgen, vooropgesteld dat de betreffende range waarden bevat.

6.0 Excel funktie Index

De Excelfunktie Index kent 3 argumenten
- de range/array die bewerkt moet worden
- de rijen uit de range/array
- de kolommen uit de range/array

6.0.1 1 waarde uit een array lezen

Je kunt 1 item uit de array verkrijgen door het rijnummer en kolomnummer te specificeren

c00=Application.index(a_sn,3,5)

Maar dat resultaat is niet beter dan

c00=a_sn(3,5)

6.0.2 1 rij uit een 2-dimensionele array lezen

Lees rij 3 uit de array a_sn

a_sp=Application.index(a_sn,3,0)

Opmerkelijk is dat de funktie 'Index' de geselecteerde 'rij' in een 1-dimensionele array converteert met ondergrens 1 en bovengrens het aantal 'kolommen' van array a_sn.

6.0.3 1 kolom uit een 2-dimensionele array lezen

Lees kolom 5 uit de array a_sn.

a_sp=Application.index(a_sn,0,5)

Array a_sp is een 2-dimensionele array.
Dimensie 1: ondergrens 1, dezelfde bovengrens als de eerste dimensie van array a_sn.
Dimensie 2: ondergrens 1, bovengrens 1 (ergo: a_sp bevat slechts 1 'kolom')

6.0.4 meer rijen/kolommen uit een array lezen

In de tweede argument van Application.Index kun je met een 2-dimensionele array aangeven welke 'rijen'/'records' je wil 'filteren'.
In die 2-dimensionele array staan dan de rijnummers aangegeven van die rijen.
Het resultaat van de Application.Index funktie is een 2-dimensionele array.

Wil je van een 2-dimensionele array ook gegevens uit de 'kolommen' filteren, dan moet je in het derde argument specificeren van welke kolommen.
In het derde argument van Application.Index kun je met een 1-dimensionele array aangeven welke kolommen je wil filteren.
In die array staan de kolomnummers van iedere kolom die je wil hebben.

Hoe maak je een 2-dimensionele array met gewenste rijnummers ? 4 alternatieven

a_sr=[row(1:10)]

Resultaat: a_sr(1,1)=1, a_sr(2,1)=2,a_sr(3,1)=3 .... a_sr(10,1)=10

a_sr=Application.Transpose(Array(1,2,3,4,5,6,7,8,9,10))

Resultaat: Application.Transpose converteert de 1-dimensionele array array(..,..,) naar een 2-dimensionele

a_sr=Application.Transpose(split("1,2,3,4,5,6,7,8,9,10",",))

Resultaat: Application.Transpose converteert de 1-dimensionele array die resulteert uit split() naar een 2-dimensionele

c00="1,3,5,7"
a_sr=Application.Transpose(split(c00,",))

Resultaat: Application.Transpose converteert de 1-dimensionele array die resulteert uit split() naar een 2-dimensionele

Hoe maak je een 1-dimensionele array met gewenste kolomnummers ?

a_sc=array(1,3,5,6)

Resultaat: 1-dimensionele array a_sc(1)=1, a_sc(2)=3, a_sc(3)=5, a_sc(4)=6

a_sc=split("1,3,5,6",",")

Resultaat: 1-dimensionele array a_sc(1)=1, a_sc(2)=3, a_sc(3)=5, a_sc(4)=6

c00="1,3,5,6"
a_sc=split(c00,",")

Resultaat: 1-dimensionele array a_sc(1)=1, a_sc(2)=3, a_sc(3)=5, a_sc(4)=6

a_sc=[Transpose(row(1:8))]

Resultaat: Transpose converteert de 2-dimensionele array die resulteert uit row( .. ) in een 1-dimensionele: a_sc(1)=1, ... a_sc(8)=8

6.0.4.0 Alle gegevens uit een array

Stel een 2-dimensionele array heeft 10 'rijen' / 'records' en 8 'kolommen' en je wil de gegevens uit alle rijen en alle kolommen.

a_sp=Application.index(a_sn,[row(1:10)],Application.Transpose([row(1:8)]))

Alternatieve schrijfwijzen

a_sp=Application.index(a_sn,Application.Transpose(array(1,2,3,4,5,6,7,8,9,10)),Application.Transpose([row(1:8)]))

a_sp=Application.index(a_sn,Application.Transpose(array(1,2,3,4,5,6,7,8,9,10)),array(1,2,3,4,5,6,7,8))

a_sp=Application.index(a_sn,Application.Transpose(split("1,2,3,4,5,6,7,8,9,10",",")),split("1,2,3,4,5,6,7,8",","))

6.0.4.1 Filter bepaalde 'rijen' / 'records'

Filter de records 3, 5 en 7

a_sp=Application.index(a_sn,Application.Transpose(array(3,5,7)),Application.Transpose([row(1:8)]))

6.0.4.2 Filter bepaalde 'kolommen'

Filter de kolommen 2, 4 en 6

a_sp=Application.index(a_sn,[row(1:10)],array(2,4,6))

6.0.4.3 Filter 'rijen' / 'records' en wijzig hun volgorde

Filter de records 2, 5 en 7 en zet ze in omgekeerde volgorde

a_sp=Application.index(a_sn,Application.Transpose(array(7,5,2)),Application.Transpose([row(1:8)]))

6.0.4.4 Filter kolommen en wijzig hun volgorde

Filter de kolommen 1, 4 en 6 en zet ze in omgekeerde volgorde

a_sp=Application.index(a_sn,[row(1:10)],array(6,4,1))

6.1 Wijzig de omvang van een array

6.1.1 1-dimensionele array

Als de omvang van een array is gedeclareerd met de methode Dim valt er niets meer aan de omvang te wijzigen.
Het is dus niet mogelijk de array-omvang te wijzigen met

Dim a_sp(5)
redim a_sp(12)

Als de array niet is gedeclareerd, maar toegewezen aan een variabele, kan de omvang ook niet gewijzigd worden.
De code geeft een foutmelding

a_sn=Array(23,44,36,47,82)
Redim a_sn(12)

De omvang van een array kan wel gewijzigd worden als
- de array gedeclareerd is als een onbepaalde array: Dim a_sn()
- de array gedeclareerd is als een Variant variabele: Dim a_sn
- de array gedeclareerd is met de methode Redim: Redim a_sn(5)

De omvang van de array kan zowel groter worden gemaakt als kleiner.
Daarvoor kent VBA de methode ReDim.
Met ReDim kun je alleen de bovengrens (Ubound) van een variabele wijzigen; de ondergrens blijft ongewijzigd.
Houd er rekening mee dat een wijziging van de omvang van een array de inhoud van de array meteen wist.

Wijzig de array-omvang als de variabele als een onbepaalde array-variabele is gedeclareerd

Dim a_sn()
Redim a_sn(12)

Wijzig de array-omvang als de variabele als een variant is gedeclareerd

Dim a_sn
Redim a_sn(12)

Wijzig de array-omvang als de variabele met Redim is gedeclareerd

ReDim a_sn(6) msgbox UBound(a_sn) ReDim a_sn(20) msgbox Ubound(a_sn)

De combinatie van Redim en de methode Array

Wijzig de array-omvang als de variabele als een onbepaalde array-variabele is gedeclareerd en gevuld met de methode array

Dim a_sn()
a_sn=Array(23,44,36,47,82)
Redim a_sn(12)

Wijzig de array-omvang als de variabele als variant is gedeclareerd en gevuld met de methode array

Dim a_sn
a_sn=Array(23,44,36,47,82)
Redim a_sn(12)

Wijzig de array-omvang als de variabele met de methode Redim is gedeclareerd en gevuld met de methode array

Redim a_sn(0)
a_sn=Array(23,44,36,47,82)
Redim a_sn(12)

De combinatie van Redim en de methode Split

Het is niet mogelijk met de methode split een array te vullen als de array-omvang als onbepaald is gedeclareerd
Deze code genereert een foutmelding

Dim a_sn()
a_sn=split("a23,b44,c36,d47,e82",","))

Het is niet mogelijk de array te vullen als de variabele met de methode Redim is 'gedeclareerd'
Deze code genereert een foutmelding

Redim a_sn(0)
a_sn=split("23,44,36,47,82",",")

Het is mogelijk de array-omvang te wijzigen als de variabele als variant is gedeclareerd en gevuld met de methode split

Dim a_sn
a_sn=split("23,44,36,47,82",",")
Redim a_sn(12)

6.1.2 meer-dimensionele array

Als de omvang van een array is gedeclareerd met de methode Dim valt er niets meer aan de omvang te wijzigen.
Het is dus niet mogelijk de array-omvang te wijzigen met

Dim a_sp(5,10)
Redim a_sp(12,7)

Als de array niet is gedeclareerd, maar toegewezen aan een variabele, kan de omvang ook niet gewijzigd worden.
De code geeft een foutmelding

a_sn=Range("A1:K10")
Redim a_sn(12,7)

De omvang van een array kan wel gewijzigd worden als
- de array gedeclareerd is als een onbepaalde array: Dim a_sn()
- de array gedeclareerd is als een Variant variabele: Dim a_sn
- de array gedeclareerd is met de methode Redim: Redim a_sn(5,4)

De omvang van de array kan zowel groter worden gemaakt als kleiner.
Daarvoor kent VBA de methode ReDim.
Met ReDim kun je alleen de bovengrens (Ubound) van een variabele wijzigen; de ondergrens blijft ongewijzigd.
Houd er rekening mee dat een wijziging van de omvang van een array de inhoud van de array meteen wist.

Wijzig de array-omvang als de variabele als een onbepaalde array-variabele is gedeclareerd

Dim a_sn()
Redim a_sn(12,10)

Wijzig de array-omvang als de variabele als een variant is gedeclareerd

Dim a_sn
Redim a_sn(12,10)

Wijzig de array-omvang als de variabele met Redim is gedeclareerd

redim a_sn(thisworkbook.sheets.count-1, thisworkbook.sheets.count-1)

for j=0 to Ubound(a_sn)

for jj=0 to Ubound(a_sn,2)

a_sn(j,jj)=choose(jj+1,thisworkbook.sheets(j).Name,thisworkbook.hyperlinks.count)

next

Redim a_sn(Ubound(a_sn)+3,Ubound(a_sn,2)+5)

Nu kan de array a_sn weliswaar 15 meer elementen bevatten, maar alle elementen zijn nu nog leeg en moeten opnieuw ingelezen worden.

6.1.3 wijzig de array-omvang van een 1-dimensionele array met behoud van gegevens

Wil je de ingelezen elementen behouden dan kun je gebruik maken van het sleutelwoord 'Preserve'.

1-dimensionele array

Redim a_sn(0)
a_sn=array("aa1","aa2","aa3","aa4")
Redim Preserve a_sn(Ubound(a_sn)+4)
MsgBox a_sn(3) & vbLf & UBound(a_sn)

6.1.4 wijzig de array-omvang van een meer-dimensionele array met behoud van gegevens

Maak hiervoor gebruik van het sleutelwoord 'Preserve'.
NB. Je kunt alleen de laatste dimensie van de array wijzigen.

Een 2-dimensionele array.

ReDim a_sn(3, 3)

For j=0 To UBound(a_sn)

For jj=0 To UBound(a_sn, 2)

a_sn(j, jj)=j & "_" & jj

Next

ReDim Preserve a_sn(3, 7)

MsgBox a_sn(2, 3) & vbLf & UBound(a_sn) & vbTab & UBound(a_sn, 2)

Een 3-dimensionele array.

ReDim a_sn(3, 3, 3)

For j=0 To UBound(a_sn)

For jj=0 To UBound(a_sn, 2)

For jjj=0 To UBound(a_sn, 3)

a_sn(j, jj, jjj)=j & "_" & jj & "_" & jjj

Next

ReDim Preserve a_sn(3, 3, 7)

MsgBox a_sn(2, 3, 3) & vbLf & UBound(a_sn) & vbTab & UBound(a_sn, 2) & vbTab & UBound(a_sn, 3)

6.2 Bepaal of een item in een array voorkomt

6.2.1 1-dimensionele array

6.2.1.1 De Excel funktie 'Match'

a_sn=Array("aa1", "aa2", "aa3", "aa4")

msgbox Not IsError(Application.Match("aa3", sn, 0))
msgbox Not IsError(Application.Match("aa7", sn, 0))

NB. De funktie Application.match is hoofdletteronafhankelijk: zowel "aa3" als "AA3" zal worden 'gevonden'.

6.2.1.2 De Excel funktie 'Find'

De excelfunktie 'Find' geeft als resultaat een array terug met dezelfde omvang als de array waarin gezocht wordt, maar met lbound 1.
Als de gezochte waarde niet wordt gevonden in de array staat in de resultaat-array op de plaats van het niet-gevonden item een errorwaarde.
Als de gezochte waarde wél wordt gevonden staat daaar in de resultaat-array een 1.

a_sn=Array("aa1", "aa2", "aa3", "aa4")
sr=application.find("aa2",sn)

De elementen van de resultaat-array: sr(1)=error 2015, sr(2)=1, sr(3)=error 2015, sr(4)=error 2015
Vanwege de error-waarden kan deze resultaat-array niet naar een tekstreeks worden omgezet met 'join'.
Je kunt er wel de Excelfunktie 'Sum' op toepassen, zodat je een vergelijkbaar resultaat krijgt als met 'Countif'.

Of een item voorkomt:

a_sn=Array("aa1", "aa2", "aa3", "aa4")
msgbox application.sum(application.find("aa2",sn))>0

Hoe vaak de waarde 'aa2' voorkomt in de array a_sn:

a_sn=Array("aa1", "aa2", "aa3", "aa4")
msgbox application.sum(application.find("aa2",sn))

6.2.1.3 De VBA methode 'filter'

In een 1-dimensionele array kun je naar een item zoeken met de methode 'filter'

a_sp=filter(a_sn,"abcde")

- de 'filter' methode filtert alle items uit de array die het zoekcriterium bevatten
- de 'filter' methode is inherent 'joker' georiënteerd: niet alleen items die overeenkomen met het filtercriterium, maar alle items die het filtercriterium bevatten komen in de selectie terecht: zowel item "abcde", als item "234abcde88".
je kunt het filtercriterium daarom ook lezen als "*abcde*"
- als de array geen item bevat dat aan het filtercriterium voldoet is de resultante een 'lege' array 'a_sp', te herkennen aan de bovengrens (Ubound) met de waarde -1

Bepaal of een item in een 1-dimensionele array voorkomt

MsgBox "Item 'abcde' komt " & IIf(UBound(Filter(a_sn, "abcde"))=-1, "niet ", "") & "voor in array a_sn"

Alleen exacte overeenkomsten

Om de filtermethode alleen items te laten filteren die exact overeenkomen met het zoekcriterium zul je ieder item met een uniek teken vooraan en achteraan moeten markeren

a_sp=filter(split("~" & join(a_sn,"~|~") & "~","|"),"~abcde~")

Hoofdletterafhankelijk zoeken

MsgBox "Item 'abcde' komt " & IIf(UBound(Filter(a_sn, "abcde",,0))=-1, "niet ", "") & "voor in array a_sn"

Hoofdletteronafhankelijk zoeken

MsgBox "Item 'abcde' komt " & IIf(UBound(Filter(a_sn, "abcde",,1))=-1, "niet ", "") & "voor in array a_sn"

6.2.1.4 Een lus op indexnummer

Alle items kunnen worden afgelopen en getest op de voorwaarde.
Als de waarde wordt gevonden wordt de lus afgebroken.

Hoofdletterafhankelijk

a_sn=Array("aa1", "aa2", "aa3", "aa4")

for j=0 to Ubound(a_sn)

if a_sn(j)="Aa3" then exit for

next

msgbox j<(Ubound(a_sn)+1)

Hoofdletteronafhankelijk

a_sn=Array("aa1", "aa2", "aa3", "aa4")

for j=0 to Ubound(a_sn)

If Lcase(a_sn(j))="aa3" then exit for

next

msgbox j<(Ubound(a_sn)+1)

6.2.1.5 Een lus per element

Alle items kunnen worden afgelopen en getest op de voorwaarde.
Als de waarde wordt gevonden wordt de melding gewijzigd.
Een array lijkt op een 'Collection', omdat je alle elementen af kunt lopen met een For Each .. Next lus.

Hoofdletterafhankelijk

a_sn=Array("aa1", "aa2", "aa3", "aa4")

c00="niet "
For Each it In a_sn

if it="Aa3" then c00=""

next

msgbox "Item Aa3 is " c00 & "gevonden"

Hoofdletteronafhankelijk

a_sn=Array("aa1", "aa2", "aa3", "aa4")

c00="niet" Fr Each it In a_sn

If Lcase(it)="aa3" then c00=""

next

msgbox "Item aa3 is " c00 & "gevonden"

6.2.2 2-dimensionele array

6.2.2.1 Een lus

Als je wil nagaan of een waarde ergens in een 2-dimensionele array voorkomt kun je alle elementen van de array aflopen met

For Each it In a_sn

If it="aa33" Then

c00=" "
Exit For

End If

Next
MsgBox c00 <> ""

6.2.2.2 De Excel funktie 'Match'

De Excel funktie 'match' kun je toepassen op 1 rij of 1 kolom

controle per rij

for j=1 to ubound(a_sn)

If Not IsError(Application.Match("abcde", Application.Index(a_sn, j, 0), 0)) Then Exit For

next
msgbox j< (Ubound(a_sn)+1)

controle per kolom

a_sn=Cells(1).CurrentRegion
For j=1 To UBound(a_sn, 2)

If Not IsError(Application.Match("aa33", Application.Index(a_sn, 0, j), 0)) Then Exit For

Next
MsgBox j < (UBound(a_sn, 2) + 1)

6.2.2.3 De Excel funktie 'Find'

De Excelfunktie 'Find' geeft als resultaat een array terug met dezelfde omvang als de array waarin gezocht wordt, maar met lbound 1.
Als de gezochte waarde niet wordt gevonden in de array staat in de resultaat-array op de plaats van het niet-gevonden item een errorwaarde.
Als de gezochte waarde wél wordt gevonden staat daar in de resultaat-array een 1.

a_sn=Range("A1:D10")
sr=application.find("aa2",sn)

De elementen van de resultaat-array: sr(1,1)=error 2015, sr(2,1)=1, sr(3,1)=error 2015, sr(4,1)=error 2015 ... sr(10,4)=error 2015
Daarop kun je de Excelfunktie 'Sum' toepassen, zodat je een vergelijkbaar resultaat krijgt als met 'Countif'.

Of een item voorkomt:

a_sn= Range("A1:D10")
msgbox application.sum(application.find("aa2",sn))>0

Hoe vaak de waarde 'aa2' voorkomt in de array a_sn:

a_sn= Range("A1:D10")
msgbox application.sum(application.find("aa2",sn))

6.2.2.4 Het Excel-object 'Names'

Zet de array in een 'Name' (benoemd bereik)
Excel maakt daarvoor automatisch een arrayformule aan, bestaande uit een tekstreeks.
Daarin kan naar de gezochte waarde worden gezocht.
Zet de te zoeken tekstwaarden altijd tussen aanhalings- en sluittekens
Zet de te zoeken numerieke waarden altijd tussen komma's

Names.Add "snb_002", a_sn
msgbox InStr(Names("snb_002").Value, """abcde""")>0
msgbox InStr(Names("snb_002").Value, ",1245,")>0

6.3 Bepaal de positie van een item in een array

6.3.1 een 1-dimensionele array

6.3.1.1 De Excel funktie 'match'

a_sn=Array("aa1", "aa2", "aa3", "aa4")

Msgbox Application.Match("aa3", sn, 0)

NB. hoewel de array een ondergrens 0 heeft, geeft Application.match de positie van het gezochte item weer.
"aa3" is het derde item in de array, maar komt overeen met a_sn(2)

6.3.1.2 VBA

a_sn=Array("aa1", "aa2", "aa3", "aa4")

c00="|~" & join(a_sn,"~|~") & "~|"
MsgBox UBound(Split(Split(c00, "~aa3~")(0), "|"))

6.3.2 een 2-dimensionele array

6.3.2.1 Met een lus

for j=0 to Ubound(a_sn)

for jj=0 to Ubound(a_sn)

if a_sn(j,jj)="abcde" then exit for

next
if jj< (ubound(a_sn)+1) then exit for

next

Msgbox " 'rij' : " & vbtab & j & vbtab & " 'kolom' : " & jj

6.3.2.2 Met een 'Name' in Excel

a_sn=Cells(1).CurrentRegion

Names.Add "snb_002", sn
Names("snb_002").RefersTo=[if(snb_002="a25","#","~")]
c00=Names("snb_002").RefersToLocal
c00=Left(c00, InStr(c00, "#"))

x=UBound(Split(c00, ";"))
MsgBox " 'rij' : " & vbtab & x + 1 & vbtab & " 'kolom' : " & UBound(Split(Split(c00, ";")(x), "\")) + 1

6.4 kopieer een item in een array

6.4.1 een 1-dimensionele array

6.4.1.1 VBA methoden join en split

Kopieer een item direkt achter/voor het te kopiëren item

a_sn=array("aa1","aa2","aa3","aa4","aa5")

a_sn(3)=a_sn(3) & "," & a_sn(3)
a_sn=split(join(a_sn,","),",")

Kopieer een item achter het laatste item van de array

a_sn=array("aa1","aa2","aa3","aa4","aa5")

a_sn=split(join(a_sn,",") & "," & a_sn(3),",")

6.4.2 2-dimensionele array

6.4.2.1 Excel funktie 'Index'

Zie: uitgebreide toelichting op de funktie Index

Kopieer een item direkt achter/voor het te kopiëren item

Lees in de array eerst de waarden in range A1:D9
Kopieer het derde item

a_sn=cells(1).currentregion
a_sn=Application.index(a_sn,Transpose(array(1,2,3,3,4,5,6,7,8,9),array(1,2,3,4))

Alternatieve schrijfwijzen

a_sn=Application.Index(a_sn, Application.Transpose(Array(1,2,3,3,4,5,6,7,8,9)), [Transpose(row(1:4))])

a_sn=Application.Index(a_sn, Application.Transpose(Split("1,2,3,3,4,5,6,7,8,9", ",")), [Transpose(row(1:4))])

Kopieer het derde item direkt achter het laatste item van de array

a_sn=cells(1).currentregion
a_sn=Application.index(a_sn,Transpose(array(1,2,3,4,5,6,7,8,9,3),array(1,2,3,4))

6.5 Filter items uit een array

6.5.1 1-dimensionele array

6.5.1.1 De VBA methode 'filter'

Met de methode 'filter kun je alle items die een bepaalde tekstreeks bevatten uit de array filteren.
Houd er rekening mee dat alle waarden in de gefilterde array tekstwaarden (dus niet numeriek) zijn

a_sn=array(2,4,1250,"aa12","aa14","aa50")
a_sp=filter(a_sn,"1")

- a_sp is een 1-dimensionele array met 3 tekst-elementen
- array a_sp bevat de elementen "1250", "aa12" en "aa14"

6.5.2 2-dimensionele array

Voor een 2-dimensionele array bestaat geen direkte VBA filtermethode (zoals bijv. autofilter of advancedfilter in Excel)
Met behulp van een lus en de funktie Index in Excel kan het wel

6.5.2.1 Excel funktie Index

Zie: uitgebreide toelichting op de funktie Index

Filter alle items uit de array met in de eerste kolom de tekst "abcde"

a_sn=Sheet1.Cells(1).CurrentRegion

For j=1 To UBound(a_sn)

If a_sn(j, 1)="abcde" Then c00=c00 & "_" & j

Next
a_sp=Application.Index(a_sn, Application.Transpose(Split(Mid(c00, 2), "_")), Array(1, 2, 3, 4))

Een samengesteld filter

Filter alle items uit de array met in de 1e kolom de tekst "abcde" en in de 4e kolom het getal 12

a_sn=Sheet1.Cells(1).CurrentRegion

For j=1 To UBound(a_sn)

If a_sn(j, 1)="abcde" and a_sn(j,4)=12 Then c00=c00 & "_" & j

Next
a_sp=Application.Index(a_sn, Application.Transpose(Split(Mid(c00, 2), "_")), Array(1, 2, 3, 4))

6.6 Filter 'rijen'/'records uit een 2-dimensionele array

Een meer-dimensionele array kent geen filtermethode.
Met de Excel funktie Index kun je een filtering maken.

6.6.1 Excel funktie Index

Zie: uitgebreide toelichting op de funktie Index

Filter de 'rijen' 1,3 en 5 van een 2-dimensionele array.

a_sn=range("A1:H10")
a_sp=Application.index(a_sn,Application.Transpose(array(1,3,5)),Application.Transpose([row(1:8)]))

Wijzig de volgorde van de 'records'/'rijen'

a_sn=range("A1:H10")
a_sp=Application.index(a_sn,Application.Transpose(array(5,3,1)),Application.Transpose([row(1:8)]))

Filter rijen op basis van een voorwaarde: maak de 2-dimensionele array met een lus.

a_sn=range("A1:H10")
for j=1 to Ubound(a_sn)

if instr("jaardatumdag",a_sn(j,1)) then c00=c00 & "_" & j

next

a_sp=Application.index(a_sn,Application.Transpose(split(mid(c00,2),"_")),Application.Transpose([row(1:8)]))

6.7 Filter kolommen uit een 2-dimensionele array

Een meer-dimensionele array kent geen eigen filtermethode.
Met de Excel funktie Index kun je wel een 2-dimensionele array filteren.

6.7.1 Excel funktie Index

Zie: uitgebreide toelichting op de funktie Index

Filter de kolommen 1,3 en 5 van een 2-dimensionele array.

a_sn=range("A1:H10")
a_sp=Application.index(a_sn,[row(1:10)],array(1,3,5))

Wijzig de volgorde van de kolommen.

a_sn=range("A1:H10")
a_sp=Application.index(a_sn,[row(1:10)],array(5,3,1))

Filter kolommen op basis van een voorwaarde: maak de 1-dimensionele array met een lus.

a_sn=range("A1:H10")
for j=1 to ubound(a_sn,2)

if instr("jaardatumdag",a_sn(1,j)) then c00=c00 & "_" & j

next

a_sp=Application.index(a_sn,[row(1:10)],split(mid(c00,2),"_"))

6.8 verwijder items uit een array

6.8.1 Verwijder 1 item aan de hand van de index

6.8.1.1 1-dimensionele array

6.8.1.1.1 VBA methode filter

a_sn=aray("aa1","aa2","aa3","aa4","aa5")

Je zou denken dat je 1 item kunt verwijderen met
a_sn(3)=""

Dat is niet het geval. Het item blijft bestaan, de array is niet kleiner geworden, alleen de inhoud is verwijderd.
Nadat je de inhoud hebt verwijderd zou je denken dat je de array kunt filteren met de methode 'filter' zodat alleen de items met gegevens overblijven.
De methode filter kent die mogelijkheid niet.
Als alternatief kun je het te verwijderen item een waarde geven die geen van de andere items heeft.
Vervolgens filter je dan alle elementen die die specifieke waarde niet hebben; bijv.

a_sn=aray("aa1","aa2","aa3","aa4","aa5")
a_sn(3)="~~~"
a_sn=filter(a_sn,"~~~",false)

Nu bevat de resulterende array 1 item minder.

6.8.1.1.2 ActiveX control

Je kunt een array aan een ActiveX-control toewijzen: een combobox of een listbox.
Houd er rekening mee dat de ondergrens van een Combobox of Listbox 0 is.
Een combobox of listbox heeft eigenschappen van een array en eigenschappen van een collection.
Uit een collection kun je rechtstreeks elementen verwijderen via de index met .RemoveItem
Daarna kun je de inhoud van de Listbox / Combobox inlezen in een array-variabele

a_sn=array("aa1","aa2","aa3","aa4","aa5")

Listbox1.List=a_sn
Listbox1.Removeitem 2
a_sn=listbox1.list

Het resultaat is een 2-dimensionele array.
Wil je die converteren naar een 1-dimensionele array, gebruik dan

a_sn=array("aa1","aa2","aa3","aa4","aa5")

Listbox1.List=a_sn
Listbox1.Removeitem 2
a_sn=Application.Transpose(ListBox1.List)

6.8.2 een 2-dimensionele array

Voor een 2-dimensionele array bestaat geen methode om een item aan de hand van het indexnummer te verwijderen.
Met behulp van een ActiveX-control lukt het wel.

6.8.2.1 ActiveX-control

a_sn=range("A1:K10")

listbox1.List=a_sn
listbox1.Removeitem 2
a_sn=listbox1.list

6.8.3 Verwijder een item op basis van de inhoud

6.8.3.1 1-dimensionele array

6.8.3.1.1 VBA - methode Filter

De methode filter kent een argument/switch om juist alle elementen die niet aan een bepaalde voorwaarde voldoen te bewaren.
De te filteren array kan tekstreeksen, getallen en/of datums bevatten.
Het filtercriterium kan een tekstreeks, een getal of een datum zijn.
De resulterende array bestaat louter uit tekstreekselementen.

Hoofdletteronafhankelijk

a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5")
a_sp=filter(a_sn,"a4",false)

- de 1-dimensionele array sp resulteert in een array met 4 elementen
- "aa1", "aa2", "aa3" en "aa5"; alle elementen die "a4" bevatten (nl "aa4" en "aa4a") zijn 'verwijderd'

alternatieve schrijfwijzen

a_sp=filter(a_sn,"a4",false,1)
a_sp=filter(a_sn,"a4",false,vbTextCompare)

Hoofdletterafhankelijk

a_sn=array("aa1","aa2","aa3","aa4","aA4a","aa5")
a_sp=filter(a_sn,"a4",false,0)

- resultaat: een 1-dimensionele array
- met 5 elementen
- "aa1","aa2", "aa3", "aa4" en "aa5"; ieder element dat "A4" bevat (nl "aA4a") is 'verwijderd'

alternatieve schrijfwijze

a_sp=filter(a_sn,"a4",false,vbBinaryCompare)

De methode werkt als default alsof het 'joker' tekens zijn: alle item die de tekenreeks bevatten worden verwijderd.
Wil je dat alleen items worden verwijderd die exact overeenkomen met de tekenreeks, dat zul je het begin en einde van ieder item moeten markeren.
Na de filtering moet je wel weer alle begin- en eindmarkeringen verwijderen.
Bijv. markeer ieder item met tildes

a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5")

a_sn=split("~"&join(a_sn,"~|~)&"~","|")
a_sp=split(replace(join(filter(a_sn,"~aa4~",false),"|"),"~"),"|")

Alleen het item 'aa4' is nu verwijderd.

6.8.3.1.2 ActiveX

JWijs de array-variabele toe aan een activeX control (Combobox of Listbox).
Met de methode .Removeitem kun je een item verwijderen. Daarvoor heb je wel het indexnummer nodig.
Dat verkrijg je door de ActiveX control de waarde te laten aannemen van de te verwijderen waarde.
Het indexnummer in een ListBox/Combobox is de eigenschap .ListIndex.

a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5")
Combobox1.list=a_sn
Combobox1.value="aa4"
if Combobox1.ListIndex>-1 then Combobox1.Removeitem Combobox1.ListIndex
a_sn=Application.Transpose(Combobox1.list)

Het item 'aa4' is verwijderd

6.8.4 Verwijder meer dan 1 item op basis van de inhoud

6.8.4.1 1-dimensionele array

6.8.4.1.1 VBA-methode filter

Met de VBA methode filter kun je alle items uit een 1-dimensionele array verwijderen die een bepaalde tekstreeks bevatten

a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5")
a_sn=filter(a_sn,"a4",false)

De items 'aa4' en 'aa4a' zijn verwijderd

Omdat de filtermethode inherent een 'joker' methode is, kun je geen gebruik maken van jokertekens als ? of *.

Je kunt tegelijkertijd verschillende filters gebruiken

a_sn=array("aa1","aa2","aa3","aa4","aa4a","aa5")
a_sn=filter(filter(a_sn,"a4",false),"aa2",false)

De items 'aa4', 'aa4a' en 'aa2' zijn verwijderd

6.9 verwijder 'kolommen' uit een 2-dimensionele array

Eigenlijk kun je geen kolommen uit een meer-dimensionele array verwijderen.
Met de Excel funktie 'Index' kun je wel aangeven welke kolommen je wil 'behouden'.
Het resultaat van een array waarin alleen bepaalde kolommen zijn behouden is natuurlijk dat alle overige kolommen 'verwijderd' zijn/lijken

Met een array kun je aangeven welke kolommen behouden moeten blijven

a_sn=range("A1:F14")

a_sp=Application.index(a_sn, [row(1:14)],Array(1,3,5))

- a_sp is een array met 20 'rijen' en 3 'kolommen
- a_sp bevat de waarden van de kolommen 1,3,5 van array a_sn
- de kolommen 2,4 en 6 zijn daarmee uit array a_sn 'verwijderd'.

Alternatieve schrijfwijzen

voor het 'kolom' argument

sp=Application.Index(sn, [row(1:14)], [{1, 3, 5}])
sp=Application.Index(sn, [row(1:14)], Split("1 2 3"))

voor het 'rij' argument

sp=Application.Index(sn, Application.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)), Array(1,2,3))
sp=Application.Index(sn, Application.Transpose(Split("1 2 3 4 5 6 7 8 9 10 11 12 13 14")), Split("1 2 3"))
sp=Application.Index(sn, Evaluate("row(1:" & UBound(sn) & ")"), [{1, 3, 5}])

Names.Add "snb_003", Rows(1).Resize(UBound(sn))
sp=Application.Index(sp, [row(snb_003)], [{1, 3, 5}])

6.10 verwijder rijen uit een 2-dimensionele array

Eigenlijk kun je geen rijen uit een meer-dimensionele array verwijderen.
Met de Excel funktie 'Index' kun je wel aangeven welke rijen je wil 'behouden'.
Het resultaat van een array waarin alleen bepaalde rijen zijn behouden is natuurlijk dat alle overige rijen 'verwijderd' zijn/lijken

Met een array kun je aangeven welke rijen behouden moeten blijven

a_sn=range("A1:F14")

a_sp=Application.Index(a_sn, Application.Transpose(Array(1, 5, 10, 14)), [Transpose(row(1:6))])

- a_sp is een array met 4 'rijen' en 6 'kolommen
- a_sp bevat de waarden van de rijen 1,5, 10 en 14 van array a_sn
- de rijen 2-4, 6-9 en 11-13 zijn daarmee uit array a_sn 'verwijderd'.

Alternatieve schrijfwijzen

a_sp=Application.Index(a_sn, Application.Transpose(Split("1 5 10 14")), [Transpose(row(1:6))])
a_sp=Application.Index(a_sn, [Transpose({1,5,10,14})], [Transpose(row(1:6))])

6.11 sorteer de items van een array

6.11.1 1-dimensionele array

Een 1-dimensionele array kent geen sorteermethode
Je zult daarvoor gebruik moeten maken van sorteermogelijkheden in andere VBA bibliotheken (arraylist, sortedlist, ADODB) of een werkblad in Excel.

6.11.1.1 Arraylist

a_sn=Array("aa5", "aa2", "aa4", "aa3", "aa1")
With CreateObject("system.collections.arraylist")

For j=0 To UBound(a_sn)

.Add a_sn(j)

Next
.Sort
a_sp=.toarray()

End With

- aan een 'arraylist' kun je alleen waarden toevoegen, geen 'sleutels' (zoals in een dictionary, collection of sortedlist)
- in de arraylist zetten we de te sorteren items
- na sortering bevat de eigenschap .toarray de gesorteerde items.

6.11.1.2 Sortedlist

In een sortedlist worden nieuwe gegevens meteen met de bestaande gesorteerd.
Er is geen afzonderlijke .sort instructie nodig.
In een sortedlist worden items op de sleutel van de items gesorteerd.
In de sleutel zetten we dan de inhoud van het te sorteren item uit de array a_sn: sn(j,1).
In het item van de sortedlist zetten we het 'rij'nummer van het sorteeritem: j
Na sortering kan via de sleutel de inhoud (met alle 'rij'nummers van a_sn) van de sortedlist worden uitgelezen.

a_sn=Array("aa5", "aa2", "aa4", "aa3", "aa1")

With CreateObject("System.Collections.SortedList")

For j=0 To UBound(a_sn)

.Item(a_sn(j))=""

Next

For j=0 To .Count - 1

a_sn(j)=.getkey(j)

End With

6.11.1.3 ADODB

Via de ADODB bibliotheek kun je van een array een virtuele database maken.
Daarin maak je evenveel velden aan als de array 'kolommen' (omvang 2e dimensie) heeft: .fields.append
Ieder veld geef je de eigenschap dat ie kan bestaan uit een variabel aantal tekens. adVarChar (waarde 200)
Vervolgens maak je net zoveel 'records' aan als de array a_sn 'rijen' heeft: .Addnew.
Per record vul je ieder veld met het overeenkomstige gegeven uit a_sn: .fields("item" & jj)=sn(j,jj)
Vervolgens geef je aan op welk veld de database gesorteerd moet worden: .Sort="item1"
Het resultaat lees je uit de array .Getrows
Om die overeen te laten stemmen met de dimensies van array a_sn, moet je .Getrows transponeren.

a_sn=Array("aa5", "aa2", "aa4", "aa3", "aa1")

With CreateObject("ADODB.recordset")

.Fields.Append "item", 200, 30
.Open

For j=0 To UBound(a_sn)

.AddNew
.Fields("item")=a_sn(j)
.Update

Next
.Sort="item"
a_sp=split(.GetString,vbCr)

End With

6.11.1.4 Excel werkblad

Schrijf de array naar een ongebruikt deel van een werkblad.
Sorteer die range met de waarden van de array a_sn op het 'veld' (i.c de cel) van de range
Lees de waarden van de gesorteerde range in in een arrayvariabele.

a_sn=Array("aa5", "aa2", "aa4", "aa3", "aa1")

cells(1,200).Resize(ubound(a_sn)+1)=Application.Transpose(a_sn)
cells(1,200).currentregion.sort cells(1,200),,,,,,,0

a_sp=cells(1,200).currentregion

cells(1,200).currentregion.clearcontents

6.11.2 2-dimensionele array

Een 2-dimensionele array kent geen sorteermethode
Je zult daarvoor gebruik moeten maken van sorteermogelijkheden in andere VBA bibliotheken (arraylist, sortedlist, ADODB) of een werkblad in Excel.

6.11.2.1 Arraylist

sn=Cells(1).CurrentRegion

With CreateObject("system.collections.arraylist")
For j=1 To UBound(sn)
.Add sn(j, 1) & "~_" & j
Next
.Sort
sp=Filter(Split(Join(.toarray(), "_"), "_"), "~", False)
End With

st=Application.Index(sn, Application.Transpose(sp), Application.Evaluate("Transpose(row(1:" & UBound(sn, 2) & "))"))

- aan een 'arraylist' kun je alleen waarden toevoegen, geen 'sleutels' (zoals in een dictionary of collection)
- omdat je uiteindelijk voor de Application.index-methode een array nodig hebt met de 'rij'nummers van de array a_sn in de gesorteerde volgorde, wijzen we aan de arraylist het sorteer item toe a_sn(j,1) en voegen eraan meteen het rijnummer toe: "_" & j
- na sortering bevat de array met gesorteerde items daardoor meteen ook de 'rij'nummers op de gesorteerde volgorde.
- die 'rij'nummers isoleren we uit die array en gebruiken die in de Application.index-methode.

6.11.2.2 Sortedlist

In een sortedlist worden nieuwe gegevens meteen met de bestaande gesorteerd.
Er is geen afzonderlijke .sort instructie nodig.
In een sortedlist worden items op de sleutel van de items gesorteerd.
In de sleutel zetten we dan de inhoud van het sorteeritem uit de array a_sn: sn(j,1).
In het item van de sortedlist zetten we het 'rij'nummer van het sorteeritem: j
Na sortering kan via de sleutel de inhoud (met alle 'rij'nummers van a_sn) van de sortedlist worden uitgelezen.

sn=Range("A1:K14")

With CreateObject("System.Collections.SortedList")

For j=1 To UBound(sn)

.Item(sn(j, 1))=j

Next

For j=0 To .Count - 1

c00=c00 & "_" & .Item(.getkey(j))

End With

sp=Application.Index(sn, Application.Transpose(Split(Mid(c00, 2), "_")), Array(1, 2, 3, 4, 5))

6.11.2.3 ADODB

sn=Range("A1:K14")

With CreateObject("ADODB.recordset")

For j=1 To UBound(sn, 2)

.Fields.Append "item" & j, 200, 30

Next
.Open

For j=1 To UBound(sn)

.AddNew
For jj=1 To UBound(sn, 2)

.Fields("item" & jj)=sn(j, jj)

Next
.Update

Next
.Sort="item1"
sp=Application.Transpose(.GetRows)

End With

6.11.2.4 Excel werkblad

a_sn=Range("A1:K12")

cells(1,200).Resize(ubound(a_sn),ubound(a_sn,2))=a_sn
cells(1,200).currentregion.sort cells(1,200),,,,,,,0

a_sp=cells(1,200).currentregion

cells(1,200).currentregion.clearcontents

6.12 2-dimensionele array: voeg lege rijen toe

Regelmatig kom je de vraag tegen om 'lege rijen' tussen bestaande gegevens in een werkblad te voegen.
Strikt genomen is dat niet mogelijk: ieder werkblad heeft altijd een vast aantal rijen vanaf Excel 2007: 1048576 (2^20).
De toevoeging van rijen kan veel gebeurtenisprocedures activeren en herberekeningen laten plaatsvinden, waardoor de aktie onnodig traag wordt.
Het is handiger de gegevens in een array in te lezen en de array uit te breiden met lege 'rijen'
- lees het gebied in in array a_sn
- breidt het in te lezen gebied uit met 1 lege 'rij'; als je currentregion gebruikt weet je zeker dat de rij onder het gebied een lege rij is.
- met de Excel funktie Index kun je met een array aangeven in welke volgorde de 'rijen' uit de index moeten worden ingelezen.
- het resultaat is een nieuwe 2-dimensionele array

Stel het gebied Range("A1:E6") bevat gegevens
Ik wil na iedere rij 2 lege 'rijen' / 'records' toevoegen
Lees Range("A1:E6") in + 1 lege regel: Range("A1:E7")

With cells(1).currentregion

a_sn=.Resize(.Rows.Count + 1)

End With

De volgende methode maakt een kopie van array a_sn:

a_sp=Application.Index(a_sn,Application.Transpose(Array(1, 2, 3, 4, 5, 6,7)), Array(1, 2, 3, 4, 5))

De lege 'rij' in array a_sn is rij '7'
De volgende methode voegt achter iedere 'rij' twee keer de lege rij 7 toe

a_sp=Application.Index(a_sn, Application.Transpose(Array(1, 7, 7, 2, 7, 7, 3, 7, 7, 4, 7, 7, 5, 7, 7, 6, 7, 7)), Array(1, 2, 3, 4, 5))

Je kunt dit controleren door de array weg te schrijven in het werkblad

Cells(20, 1).Resize(UBound(a_sp), UBound(a_sp, 2))=a_sp

Een robuustere manier om dit te schrijven
waarbij y het aantal toe te voegen lege rijen bevat

y=2
With Sheet1.Cells(1).CurrentRegion

a_sn=.Resize(.Rows.Count + 1)

End With

sp=Application.Index(a_sn, Application.Transpose(Split(Join(Evaluate("Transpose(row(1:" & UBound(a_sn) & "))"), Replace(String(y, ","), ",", "," & UBound(a_sn)) & ","), ",")), Evaluate("Transpose(row(1:" & UBound(a_sn, 2) & "))"))

6.13 Voeg arrays samen

VBA kent geen methode om arrays samen te voegen
VBA kent wel een aantal voorzieningen om de samenvoeging te realiseren

6.13.1 1-dimensionele array

6.13.1.1 VBA methoden Join & Split

Wanneer 2 1-dimensionele arrays uitsluitend tekst bevatten kun je met join en split de arrays samenvoegen.

a_sn=array("aa1","aa2","aa23","aa4","aa5")
a_sp=array("bb11","bb22","bb33","bb44","bb55")

a_sq=split(join(a_sn,"|") & "|" & join(a_sp,"|"),"|")

6.13.1.2 VBA methode Redim

Als de arrays niet alleen maar tekst bevatten en je wil voorkomen dat die waarden naar tekst worden geconverteerd zul je een nieuwe array moeten definiëren die de gegevens van beide arrays kan bevatten

a_sn=Array("aa1", "aa2", "aa23", "aa4", "aa5")
a_sp=Array("bb11", 220, "bb33", Date, "bb55")

ReDim a_sq(UBound(a_sn) + UBound(a_sp) + 1)

For j=0 To UBound(a_sq)

If j > UBound(a_sn) Then

a_sq(j)=a_sp(j - UBound(a_sn) - 1)

Else

a_sq(j)=a_sn(j)

End If

6.13.1.3 Excel werkblad

Schrijf beide arrays naar 1 rij van een werkblad.
Lees vervolgens de gegevens uit die rij in in een variabele.
Converteer die 2-dimensionele variabele naar een 1-dimensionele.

a_sn=Array("aa1", "aa2", "aa23", "aa4", "aa5")
a_sp=Array("bb11", "bb22", "bb33", "bb44", "bb55")

Sheet1.Cells(1).Resize(, UBound(a_sn, 1) + 1)=a_sn
Sheet1.Cells(1).Offset(, UBound(a_sn) + 1).Resize(, UBound(a_sp, 1) + 1)=a_sp
a_sq=Application.Transpose(Application.Transpose(Cells(1).Resize(, UBound(a_sn) + UBound(a_sp) + 1)))

6.13.2 2-dimensionele array

6.13.2.1 Excel werkblad

Schrijf beide (of meer) arrays onder elkaar in een werkblad.
Lees het resulterende aaneengesloten gebied in als nieuwe array variabele.

a_sn=sheet5.Range("A1:K10")
a_sp=sheet4.Range("A1:F17")

sheet1.cells(1).resize(ubound(a_sn),ubound(a_sn,2))=a_sn
sheet1.cells(ubound(a_sn)+1,1).resize(ubound(a_sp),ubound(a_sp,2))=a_sp

a_sq=sheet1.cells(1).currentregion

6.13.2.2 Dictionary

Met een dictionary kun je 1-dimensionele arrays samenvoegen tot een 2-dimensionele
Als je twee (of meer) 2-dimensionele arrays 'regel-voor-regel' toewijst aan een dictionary kunt je ze vervolgens samenvoegen tot 1 array.
Voorwaarde is wel dat iedere array evenveel 'kolommen' heeft.

a_sn=Sheet4.Range("A1:F10")
a_sp=Sheet5.Range("A1:F28")

With CreateObject("scripting.dictionary")

For j=1 To UBound(a_sn)

.Item("nr" & .Count)=Application.Index(a_sn, j, 0)

Next

For j=1 To UBound(a_sp)

.Item("nr" & .Count)=Application.Index(a_sp, j, 0)

Next

a_sq=Application.Index(.items, 0, 0)

End With

6.14 Conversie arrays van 1-dimensioneel naar 2-dimensioneel en andersom

6.14.1 1-dimensionele array conversie naar een 2-dimensionele

Alleen een 2-dimensionele array kun je vertikaal naar een werkblad schrijven.
Een 1 dimensionele array zul je daarvoor moeten converteren naar een 2-dimensionele.
Let op dat een 1-dimensionele array 1 item meer bevat dan de bovengrens (Ubound) van de array, omdat het eerste item van de array het indexnummer 0 heeft.

6.14.1.1 Excel funktie Transpose

a_sn=Array("aa1","aa2","aa3","aa4","aa5")
a_sp=Application.Transpose(a_sn)

alternatieve schrijfwijze

a_sp=Application.Transpose(Array("aa1","aa2","aa3","aa4","aa5"))

- a_sn is een 1-dimensionele array met lbound 0 en Ubound 4.
- a_sp is een 2-dimensionele array Ubound 5 voor de eerste dimensie en Ubound 1 voor de tweede dimensie.
- de beide dimensies in a_sp hebben een ondergrens 1.
- de Transpose funktie gaat impliciet uit van ondergrens 1 (Option Compare 1) voor beide dimensies.

6.14.1.2 ActiveX-control

Wanneer je een 1-dimensionele array in een Combobox of Listbox inleest, wordt die automatisch omgezet in een 2 dimensionele array.

a_sn=Array("aa1","aa2","aa3","aa4","aa5")

ComboBox1.List=a_sn
a_sn=ComboBox1.List

- een array met 5 elementen a_sn(0,0), a_sn(1,0), a_sn(2,0), a_sn(3,0) en a_sn(4,0)
- dimensie 1: ondergrens 0, bovengrens 4
- dimensie 2: ondergrens 0, bovengrens 0

6.14.1.3 Dictionary

Met een dictionary kun je verschillende 1-dimensionele arrays tot een 2-dimensionele array samenvoegen.

With CreateObject("scripting.dictionary")

.Item(.Count)=Array("aa1", "aa2", "aa3", "aa4", "aa5")
.Item(.Count)=Array("bb1", "bb2", "bb3", "bb4", "bb5")
.Item(.Count)=Array("cc1", "cc2", "cc3", "cc4", "cc5")
a_sn=Application.Index(.items, 0, 0)

End With

- een array met 15 elementen
- na het gebruik van de Excel funktie Index, is de ondergrens van iedere dimensie van de resulterende array altijd 1.
- dimensie 1: ondergrens 1 lbound(a_sn), bovengrens 3 Ubound(a_sn)
- dimensie 2: ondergrens 1 lbound(a_sn,2), bovengrens 5 Ubound(a_sn,2)

6.14.2 converteer een 2-dimensionele array in een 1 dimensionele

Er zijn twee bijzondere 2-dimensionele arrays naar een 1-dimensionele te converteren: de 'rij' array en de 'kolom' array.

6.14.2.1 converteer een 'rij'

Een 'rij' array heeft 1 rij en diverse kolommen.
Je kunt hem maken met

a_sn=range("A1:K1")

- dimensie 1: ondergrens 1, bovengrens 1
- dimensie 2: ondergrens 1, bovengrens 11

6.14.2.1.1 Excel funktie Index

Zie: uitgebreide toelichting op de funktie Index

a_sn=range("A1:K1")
a_sn=Application.index(a_sn,1,0)

resultaat
- een 1-dimensionele array
- met ondergrens 1 lbound(a_sn)=1
- en bovengrens 11 ubound(a_sn)=11

Wil je als resultaat een 1-dimensionele array met ondergrens 0

a_sn=range("A1:K1")
a_sn=filter(Application.index(a_sn,1,0),"")

resultaat
- een 1-dimensionele array
- met ondergrens 0 lbound(a_sn)=0
- en bovengrens 10 Ubound(a_sn)=10

- door het gebruik van 'Filter' zijn alle waarden omgezet in tekstreeksen

6.14.2.1.2 Transpose

a_sn=Range("A1:K1")
a_sn=Application.Transpose(Application.Transpose(a_sn))

resultaat
- een 1-dimensionele array
- met ondergrens 1 lbound(a_sn)=1
- en bovengrens 11 Ubound(a_sn)=11

Wil je als resultaat een 1-dimensionele array met ondergrens 0

a_sn=range("A1:K1")
a_sn=filter(Application.Transpose(Application.Transpose(a_sn)),"")

resultaat
- een 1-dimensionele array
- met ondergrens 0 lbound(a_sn)=0
- en bovengrens 10 Ubound(a_sn)=10

6.14.2.2 converteer een 'kolom'

Een 'kolom'-array heeft 1 kolom en diverse rijen.
Je kunt hem maken met

a_sn=range("A1:A12")

- dimensie 1: ondergrens 1, bovengrens 11
- dimensie 2: ondergrens 1, bovengrens 1

6.14.2.2.1 Transpose

a_sn=range("A1:A12")
a_sn=Application.Transpose(a_sn)

resultaat
- een 1-dimensionele array
- dimensie 1: ondergrens 1, bovengrens 12

Wil je een 1-dimensionele array met ondergrens 0

a_sn=range("A1:A12")
a_sn=filter(Application.Transpose(a_sn),"")

NB. Door het gebruik van 'Filter' zijn alle waarden omgezet in tekstreeksen

6.14.2.2.2 Excel funktie Index

Zie: uitgebreide toelichting op de funktie Index

Ook met Index kan de conversie plaatsvinden, maar is vergeleken met de Transpose-funktie onnodig ingewikkeld.

a_sn=range("A1:A12")
a_sn=Application.Index(a_sn, [Transpose(row(1:12))], 0)

resultaat
- een 1-dimensionele array
- met ondergrens 1 lbound(a_sn)=1
- en bovengrens 12 Ubound(a_sn)=12

6.15 Converteer een array naar een tekstreeks

6.15.1 1-dimensionele array

6.15.1.1 VBA methode join

Iedere 1-dimensionele array zonder arrays kun je converteren naar een tekstreeks met de methode join.
Het resultaat is wel dat alle waarden van de array, ook cijfers en datums, omgezet worden naar een tekstreeks.

a_sn=array("aa1",dateserial(2014,10,20),1250,30.6)
msgbox join(a_sn)

het resultaat

- tekenreeks: "aa1 20-10-2014 1250 30,6"

De methode join voegt standaard een spatie tussen items toe.
Wil je geen scheidingsteken tussen items gebruik dan

msgbox join(a_sn,"")

Je kunt ieder teken of tekenreeks gebruiken om de items samen te voegen

msgbox join(a_sn,"|")
msgbox "Dit is item " & join(a_sn,vbLf & "Dit is item ")

6.15.2 2-dimensionele array

6.15.2.1 Een lus

a_sn=range("A1:K12")

for each it in a_sn

c00=c00 & "|" & it

next

msgbox c00

6.15.2.2 Excel funktie Index

Zie: uitgebreide toelichting op de funktie Index

Met de funktie Index kun je van iedere 'rij' in een 2-dimensionele array een 1-dimensionele array maken.
Die 1-dimensionele array kun je vervolgens tot een tekenreeks samenvoegen met de methode join.

a_sn=range("A1:K12")

for j=1 to ubound(a_sn)

c00=c00 & vblf & join(Application.index(a_sn,j,0),"_")

6.15.2.3 Het klembord

Je kunt een 2-dimensionele array naar een werkblad schrijven en vervolgens de gegevens uit het werkblad kopiëren.
Met de methode DataObject uit de MSForms 2.0 bibliotheek kun je vervolgens die gegevens als tekst uit het klembord halen.

Via een 'late' verbinding

sheets(1).cells(1).resize(ubound(a_sn),ubound(a_sn,2))=a_sn
sheets(1).cells(1).currentregion.copy

With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

.GetFromClipboard
c00=.GetText

End With

Via een 'vroege' verbinding

' referentie naar Microsoft Forms 2.0 Object Library

sheets(1).cells(1).resize(ubound(a_sn),ubound(a_sn,2))=a_sn
sheets(1).cells(1).currentregion.copy

With New dataobject

.GetFromClipboard
c00=.GetText

End With

6.15.2.4 'Name' in Excel

Wijs een 2-dimensionele array toe aan een 'Name'.
De inhoud van de array wordt als tekenreeks opgeslagen in de default eigenschap '.RefersTo' van de 'Name', ingesloten in "={ .. }".
Die eigenschap kun je uitlezen en de tekens "={ ... }" verwijderen.
Beperking: het aantal rij-scheidingstekens + het aantal kolom-scheidingstekens + het aantal tekens in de inhoud mag niet groter zijn dan 8221.

a_sn=range("A1:K10")
Names.Add "proef", a_sn
CreateObject("Scripting.FileSystemObject").CreateTextFile("G:\OF\example.csv").write mid(Names("proef"),3,len(Names("proef"))-3)

6.16 Arrays en Excelformules

Met Excelformules kun je berekeningen uitvoeren in/met arrays.
In VBA heten Excelformules 'Worksheetfunctions'.

6.16.1 Schrijfconventies

In VBA kun je formules op drie manieren schrijven ( bijv. de formule MAX) :
- Application.Worksheetfunction.Max( .. )
- Worksheetfunction.Max( .. )
- Application.Max( .. )

De schrijfwijze Application.formule werkt essentieel anders dan de .worksheetfunction.formule schrijfwijze (met dank aan R. Ceulemans die mij hierop attendeerde).

6.16.1.1 De afwikkeling van 'errors'

Application.worksheetfunction.formule en worksheetfunction.formule

Als de .worksheetfunction.formule op enig moment een fout genereert, bijv. omdat de array een bepaalde waarde niet bevat, wordt de VBA-code onderbroken.

Voorbeeld

sp=array(2,4,6,8,10)
sr=application.worksheetfunction.match(6,sp,0)
sr=worksheetfunction.match(6,sp,0)

De waarde wordt gevonden en in variabele sr komt de waarde 3 te staan

sp=array(2,4,6,8,10)
sr=application.worksheetfunction.match(5,sp,0)
sr=worksheetfunction.match(5,sp,0)

De waarde wordt niet gevonden en leidt tot een VBA-foutmelding en onderbreking van de code.

Application.formule

De application.formule versie registreert de fout en slaat hem op in de resulterende variabele.

sp=array(2,4,6,8,10)
sr=Application.match(5,sp,0)

De waarde wordt niet gevonden, een errormelding wordt in variabele sr gezet.
VBA genereert geen foutmelding, de VBA code wordt niet onderbroken.

De foutwaarde in de variabele kan verder in de code gebruikt worden, zoals:

if iserror(sr) then msgbox "niet gevonden"

6.16.1.2 De toepassing als 'matrix'formule

Application.worksheetfunction.formule en worksheetfunction.formule

In alle gevallen waarin je een Excelformule als 'matrix'formule moet invoeren, laten de schrijfwijzen application.worksheetfunction en application.worksheetfunction het afweten.
Ze produceren beide een VBA-foutmelding en onderbreken de uitvoering van de VBA-code.

Voorbeeld

sp=array(2,4,6,8,10)
sn=array(2,6,10)
sr=application.worksheetfunction.match(sn,sp,0)
sr=worksheetfunction.match(sn,sp,0)

Application.formule

De Application.formule verwerkt de argumenten van de formule alsof het een array-formule is. Het lijkt alsof deze methode een ingebouwde 'Evaluate' funktie heeft.

sp=array(2,4,6,8,10)
sn=array(2,6,10)
sr=application.match(sn,sp,0)

De resulterende variabele is een 1-dimensionele array met de omvang van de als argument ingevoerde array sn, maar met ondergrens 1.
In dit voorbeeld zijn de waarden sr(1)=1, sr(2)=3 en sr(3)=5

Ook als een waarde niet gevonden wordt is er geen probleem:

sp=array(2,4,6,8,10)
sn=array(2,6,12)
sr=application.match(sn,sp,0)

De resulterende waarden sr(1)=1, sr(2)=3 en sr(3)=error 2042
te gebruiken in bijv.

if iserror(sr(3)) then msgbox sn(2) & " is niet gevonden"

Dit werkt ook met 2-dimensionele variabelen:

sn=range("A1:K10")
sr=application.find("abc",sn)

In de (10*11) array sn wordt gezocht naar de tekstreeks "abc".
De resultaatarray is een 2-dimensionele array met dezelfde omvang (10*11).
Als de tekstreeks wordt gevonden komt in de overeenkomstige 'cel' van de resultaatarray sr een 1 te staan.
Als de tekstreeks niet wordt gevonden komt in de overeenkomstige cel 'error 2012' te staan.
Hoe je deze formule verder kunt gebruiken, zie De Excel funktie 'Find'

Conclusie

Het gebruik van Application.formule biedt veel meer toegang tot gebruik van Excelformules dan de (application.)Worksheetfunction.formule variant.
Bovendien hoeft bij de Application.formule variant geen foutafhandelingsprocedure te worden toegevoegd om doorloop van de code te garanderen.

Argumenten

In VBA moeten de argumenten in een formule altijd gescheiden worden door een komma. De puntkomma speelt geen enkele rol.
bijv.

msgbox application.date(2016,6,21)

Werkbladnamen en VBA-namen

Als een formule een punt bevat, zoals bijv. MODE.SNGL, moet die punt in VBA als underscore _ geschreven worden.

msgbox application.mode_sngl(sn)

Evaluate en formules

In 'Evaluate' kun je geen variabelen, dus ook geen array invoeren.
Deze code werkt daarom niet:

sn=sheet1.range("A1:A10")
y=[sum(sn)]

Dat geldt ook voor de andere schrijfwijze van Evaluate:

sn=sheet1.range("A1:A10")
y=Evaluate("sum(sn)")

Je kunt dit oplossen door de array eerst aan een 'benoemd bereik' toe te wijzen:

sn=sheet1.range("A1:A10")
Application.names.add "snb_01", sn
msgbox [sum(snb_01)]
msgbox Evaluate("sum(snb_01)")

Een array kan in 'Evaluate' alleen geëvalueerd worden als uitgeschreven tekstreeks:
Bij een 1-dimensionele array kan je die array converteren naar een tekstreeks met transpose en join.
Een kolom:

sn=sheet1.range("A1:A10")
msgbox Evaluate("sum(" & join(application.transpose(sn),",") & ")")

Een rij:

sn=sheet1.range("A1:K1")
msgbox Evaluate("sum(" & join(application.transpose(application.transpose(sn)),",") & ")")
msgbox Evaluate("sum(" & join(application.index(sn,1)),",") & ")")

Bij een 2-dimensionele array kan het o.a. via een benoemd bereik:

sn=sheet1.range("A1:A10")
Application.names.add "snb_01",sn
msgbox Evaluate("sum(" & mid(application.names("snb_01").value,2) & ")")

Deze werkwijze heeft natuurlijk geen enkel voordeel ten opzichte van het gebruik van het benoemde bereik zelf.

6.16.2 Snelheid

Het gebruik van Excelformules in VBA is niet sneller dan het gebruik van 'pure' VBA-methoden.
Zo kun je met de formule 'match' de positie van een waarde in een 1-dimensionele array bepalen. Met een loop in de array gaat dat 'sneller'.
Het snelheidsverschil is echter zo klein dat een gebruiker dit zelden zal kunnen merken: een niet-waarneembaar verschil is dan identiek aan 'geen verschil'.
In extreme gevallen kun je hiermee wel rekening houden.
Het voordeel van het gebruik van een Excelformule is de eenvoud van schrijven:

msgbox application.match("abc",sn,0)

ten opzichte van

for j=1 to ubound(sn)

if sn(j,1)="abc" then exit for

next
Msgbox j

6.16.3 Excelformules: overzicht

Niet alle formules kunnen voor Arrays gebruikt worden.
Voorwaarde is natuurlijk dat een formule argumenten kent: RAND(), TODAY() en NOW() hebben die bijv. niet.
Als een formule alleen maar getallen - bijv. DATE(..,..,..), MOD(..,..) - en/of tekst - bijv. DATE(..), Clean(..) - als argumenten kent zijn ze niet op Arrays toepasbaar.
Ook als een Range als argument vereist is, zoals bij AGGREGATE(getal,range) of RANK(range,range) is zo'n formule voor arrays niet beschikbaar.

Noch uit de hulptekst in Excel bij de invoer van formules, noch uit Intellisense bij de invoer van Worksheetfunctions in VBA kan ik een duidelijke gemeenschappelijke noemer destilleren om te bepalen of een formule toepasbaar is op een array of niet.
Het is dus ook een kwestie van uitproberen.
Voor de onderstaande formules in Excel 2010 is in ieder geval getest en gebleken dat ze met arrays samenwerken.
De lijst is waarschijnlijk dan ook niet volledig.

De voorbeelden gebruiken een 2-dimensionele array voor de formule, tenzij anders vermeld.
Het resultaat van een formule kan een getal zijn of een array.
In de onderstaande voorbeelden gebruik ik de variabele 'y' als het resultaat een getal is.
Is het resultaat een array, dan heet die 'sr'.

Sommige formules berekenen niets in de array, maar veranderen die slechts: TRANSPOSE(..) en INDEX(..).
Als een formule in Excel 2010 'verouderd' is, en alleen nog bestaat vanwege compatibiliteit met eerdere versies van Excel, staat dat tussen haakjes aangegeven: (2007).

In de voorbeelden hieronder figureren twee 2-dimensionele arrays (sn en sq).
De VBA-code voor het gebruik van 1-dimensionele arrays is identiek.

sn = Range("A1:D10")
sq = Range("A11:D20")

informatie
y = Application.Count(sn)	Aantal getallen in de array
y = Application.CountA(sn)	Aantal gegevens in de array
rekenkundig
y = Application.Max(sn)	Maximumwaarde in de array
y = Application.Min(sn)	Minimumwaarde in de array
y = Application.Large(sn,4)	op 3 na hoogste waarde in de array
y = Application.Small(sn,2)	op 1 na kleinste waarde in de array
y = Application.Gcd(sn,2)	grootste gemene deler in de array
y = Application.Sum(sn)	som van alle waarden
y = Application.Product(sn)	vermenigvuldiging van alle waarden
y = Application.SumProduct(sn,sn)	som van vermenigvuldiging van waarden in 1 of meer arrays
y = Application.SumSq(sn)	som van gekwadrateerde waarden in 1 of meer arrays
y = Application.SumX2PY2(sn,sq)	som van gekwadrateerde waarden in 2 of meer arrays
y = Application.SumX2MY2(sn,sq)	som van verschillen van gekwadrateerde waarden in 2 of meer arrays
y = Application.SumXMY2(sn,sq)	som van gekwadrateerde verschillen van waarden in 2 of meer arrays
sp = Array(1, 2, 3, 4, 5) st = Array(2, 10, 20) sr = Application.MMult(Application.Transpose(st), sp)	vermenigvuldiging van twee 1-dimensionele arrays (rijen en kolommen) naar een 2-dimensionele array
st = Application.Index(sn, 2) sp = Application.Index(sn, evaluate("row(1:" & ubound(sn) &")"), 3) sr = Application.MMult(st, sp)	vermenigvuldiging van de 2e rij (st) en 3e kolom (sp) van een 2-dimensionele array
y = Application.Average(sn)	gemiddelde
y = Application.Median(sn)	mediaan
y = Application.AveDev(sn)	gemiddelde absolute afwijking van gemiddelde
y = Application.GeoMean(sn)	geometrisch gemiddelde van positieve getallen
y = Application.HarMean(sn)	harmonisch gemiddelde van positieve getallen
y = Application.TrimMean(sn,.05)	gemiddelde van gegevens zonder .05 top & .05 bodem percentage (5 procent)
statistiek beschrijvend
y = Application.Mode(sn)	meest voorkomende waarde (2007)
y = Application.Mode_Sngl(sn)	meest voorkomende waarde
y = Application.Mode_Mult(sn)	meest voorkomende waarden
y = Application.Percentile(sn,.6)	het 60% percentiel van de array (2007)
y = Application.Percentile_Exc(sn,.6)	het 60% percentiel van de array exclusief
y = Application.Perc_inc(sn,.6)	het 60% percentiel van de array inclusief
y = Application.PercentRank(sn,3)	de rang van waarde 3 als percentage (2007)
y = Application.PercentRank_Exc(sn,3)	de rang van waarde 3 als percentage exclusief
y = Application.PercenRank_Inc(sn,34)	de rang van waarde 34 als percentage inclusief
y = Application.Quartile(sn,3)	het derde kwartiel (2007)
y = Application.Quartile_Exc(sn,3)	het derde kwartiel exclulsief
y = Application.Quartile_Inc(sn,4)	het vierde kwartiel inclusief
y = Application.Var(sn)	schatting variantie vanuit steekproef (2007)
y = Application.Var_S(sn,3)	schatting van variantie vanuit steekproef
y = Application.VarA(sn,3)	schatting van variantie vanuit steekproef incl. tekst en logische waarden
y = Application.VarP(sn)	variantie in de populatie (2007)
y = Application.Var_P(sn,3)	variantie in de populatie
y = Application.VarPA(sn,3)	variantie in de populatie incl. tekst en logische waarden
y = Application.StDev(sn)	schatting van standaarddeviatie vanuit steekproef (2007)
y = Application.StDev_S(sn,1)	schatting van standaarddeviatie vanuit steekproef
y = Application.StDevA(sn,1)	schatting van standaarddeviatie vanuit steekproef incl. tekst en logische waarden
y = Application.StDevP(sn)	standaarddeviatie van de populatie (2007)
y = Application.StDev_P(sn,1)	standaarddeviatie van de populatie
y = Application.StDevPA(sn)	standaarddeviatie van de populatie incl. tekst en logische waarden
y = Application.Covar(sn,sp)	covariantie van gegevensparen in 2 arrays (2007)
y = Application.Covariance_S(sn,sp)	steekproefcovariantie van gegevensparen in 2 arrays
y = Application.Covariance_P(sn,sp)	populatiecovariantie van gegevensparen in 2 arrays
y = Application.DevSq(sn)	som van de gekwadrateerde afwijkingen van het gemiddelde
y = Application.SKew(sn)	maat van verdelingssymmetrie rond het gemiddelde
y = Application.Slope(sn,sp)	hellingshoek van lineaire regressielijn
y = Application.StEyx(sn,sp)	voorspelde standaardfout voor iedere waarde in de tweede array in regressie
y = Application.ImProduct(sn)	produkt van complexe nummers in de array
y = Application.ImSum(sn)	som van complexe nummers in de array
y = Application.Intercept(sn,sp)	snijpunt van regressielijn met y-as
sr = Application.Frequency(sn,sn)	frekwentie van ieder getal in de array
sr = Application.Growth(sn)	getallen in een exponentiële groeitrend
sr = Application.Trend(sn)	numbers in a linear trend matching datapoints (least square method)
sr = Application.LinEst(sn)	straight line matching data points (least square method)
sr = Application.LogEst(sn)	exponential curve matching data points
statistiek toetsend
y = Application.FTest(sn,sp)	F-test: tweezijdige toetsing op significante variantie
y = Application.TTest(sn,sp,2,2)	Student T-test (2007)
y = Application.ZTest(sn)	eenzijdige P-waarde van een Z-test (2007)
y = Application.Pearson(sn,sp)	Pearson correlatie-coëfficiënt: r
y = Application.RSq(sn,sp)	het kwadraat van de Pearson correlatie-coëfficiënt
database
y = Application.VLookup(21,sn,4,0)	zoekt waarde 21 in 1e 'kolom' van array sn, geeft overeenkomstige waarde uit 4e 'kolom'
y = Application.HLookup("3",sn,4,0)	zoekt waarde "3" in 1e 'rij' van array sn, geeft overeenkomstige waarde uit 4e 'rij'
y = Application.Lookup(22,sn)	zoekt waarde 22 en geeft de overeenkomstige waarde uit de laatste kolom weer(2007)
y = Application.Match(21,sr,0)	positie van een waarde in een 1-dimensionele array
y = Application.Index(sn,3,7)	waarde uit een bepaalde rij en kolom van de array
sr = Application.Transpose(sn)	wijzigt rijen in kolommen en andersom

6.17 Zoeken en vervangen in een array

6.17.1 Excelfunktie 'Substitute'

De VBA-funktie 'replace' werkt niet met een array.
De 'Range.replace' methode van Excel werkt alleen op gebieden (ranges) en niet met arrays.
De Excelformule 'Substitute' werkt in een arrayformula op gebieden.
Hij blijkt ook toepasbaar op arrays.
Houd er rekening mee dat de vervanging plaatsvindt alsof alle elementen in de array tekenreeksen zijn.
Het resultaat bestaat ook altijd uit tekenreeksen.
De vervangingsfunktie is inherent met jokertekens: iedere tekenreeks die in de array wordt aangetroffen ( aan het begin, binnen, aan het eind of als volledig element in de array) wordt vervangen met de vervangende tekenreeks.
Jokertekens als * of ? zijn dan ook overbodig.
'Substitute' is hoofdlettergevoelig.
Het argument welke tekenreeks-instantie vervangen moet worden, wordt genegeerd.

sn = Array("aa1", "aa2", "cc", "aa4")
sr = Application.Substitute(sn, "aa", "bb")

sr(1) ="bb1", sr(2)="bb2", sr(3)="cc", sr(4)="bb4"