VBA-arrays

In VBA, een Array is een enkele variabele die meerdere waarden kan bevatten. Zie een array als een reeks cellen: elke cel kan een waarde opslaan. Arrays kunnen eendimensionaal zijn (denk aan een enkele kolom), tweedimensionaal (denk aan meerdere rijen en kolommen) of multidimensionaal. Arraywaarden zijn toegankelijk via hun positie (indexnummer) binnen de array.

VBA-array snelblad

Arrays

BeschrijvingVBA-codeCreërenDim arr (1 tot 3) als variant
arr(1) = “één”
arr(2) = “twee”
arr(3) = “drie”Maken van ExcelDim arr (1 tot 3) als variant
Dim cel als bereik, i als geheel getal
ik = LBound(arr)
Voor elke cel binnen bereik ("A1: A3")
ik = ik + 1
arr(i) = cel.waarde
Volgende celLees alle artikelenDim ik zo lang
Voor i = LBound(arr) Naar UBound(arr)
MsgBox arr(i)
volgende iWissenErase arrArray naar stringNaam dimmen als string
sName = Join(arr, “:”)Groter makenReDim behouden arr (0 tot 100)set Valuearr(1) = 22

Snelle voorbeelden van VBA-array

Laten we een volledig voorbeeld bekijken voordat we in details duiken:

12345678910

Submatrixvoorbeeld()Dim strNames (1 tot 4) als StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Jose"msgbox strNames(3)Einde sub

Hier hebben we de eendimensionale tekenreeksarray gemaakt: strNames met grootte vier (kunnen vier waarden bevatten) en de vier waarden toegewezen. Als laatste geven we de 3e waarde weer in een berichtenvenster.

In dit geval is het voordeel van het gebruik van een array klein: er is slechts één variabele declaratie nodig in plaats van vier.

Laten we echter eens kijken naar een voorbeeld dat de ware kracht van een array laat zien:

12345678

Submatrixvoorbeeld2()Dim strNames (1 tot 60000) als stringDim ik zo langVoor ik = 1 tot 60000strNames(i) = Cellen(i, 1).Waardevolgende iEinde sub

Hier hebben we een array gemaakt die 60.000 waarden kan bevatten en we hebben de array snel ingevuld vanuit kolom A van een werkblad.

Array voordelen? - Snelheid!

Je zou kunnen denken aan arrays die lijken op Excel-werkbladen:

• Elke cel (of item in een array) kan zijn eigen waarde bevatten
• Elke cel (of item in een array) is toegankelijk via de rij- en kolompositie.
  • Werkblad bijv. cellen (1,4).waarde = "Rij 1, Kolom 4"
  • Array Ex. arrVar(1,4) = "Rij 1, Kolom 4"

Dus waarom zou je je druk maken over arrays? Waarom niet gewoon waarden rechtstreeks naar cellen in Excel lezen en schrijven? Een woord: Snelheid!

Lezen / schrijven naar Excel-cellen is een langzaam proces. Werken met arrays gaat veel sneller!

Een array maken / declareren (Dim)

Opmerking: Arrays kunnen meerdere "dimensies" hebben. Om het simpel te houden, beginnen we door alleen met eendimensionale arrays te werken. Later in de zelfstudie laten we u kennismaken met arrays met meerdere dimensies.

statische Array

Statische arrays zijn arrays die niet van grootte kunnen veranderen. Omgekeerd, Dynamische arrays grootte kan veranderen. Ze worden iets anders aangegeven. Laten we eerst eens kijken naar statische arrays.

Opmerking: als uw array niet in grootte verandert, gebruik dan een statische array.

Het declareren van een statische arrayvariabele lijkt erg op het declareren van een reguliere variabele, behalve dat u de grootte van de array moet definiëren. Er zijn verschillende manieren om de grootte van een array in te stellen.

U kunt de begin- en eindposities van een array expliciet declareren:

123456789101112

Substatisch Array1()'Maakt array met posities 1,2,3,4'Dim arrDemo1 (1 tot 4) als string'Maakt array met posities 4,5,6,7'Dim arrDemo2(4 tot 7) Zo lang'Maakt array met posities 0,1,2,3'Dim arrDemo3(0 tot 3) Zo langEinde sub

Of u kunt alleen de arraygrootte invoeren:

123456

Substatisch Array2()'Maakt array met posities 0,1,2,3'Dim arrDemo1(3) As StringEinde sub

Belangrijk! Merk op dat Arrays standaard beginnen op positie 0. Dus Dim arrDemo1(3) creëert een array met posities 0,1,2,3.

U kunt verklaren Optie Basis 1 bovenaan uw module zodat de array in plaats daarvan op positie 1 begint:

12345678

Optie Basis 1Substatisch Array3()'Maakt array met posities 1,2,3'Dim arrDemo1(3) As StringEinde sub

Ik vind echter dat het veel gemakkelijker (en minder verwarrend) is om de begin- en eindposities van arrays gewoon expliciet aan te geven.

Moe van het zoeken naar voorbeelden van VBA-codes? Probeer AutoMacro!

dynamische Array

Dynamische arrays zijn arrays waarvan de grootte kan worden gewijzigd (of waarvan de grootte niet hoeft te worden gedefinieerd).

Er zijn twee manieren om een ​​Dynamic Array te declareren.

Variantmatrices

De eerste manier om een ​​dynamische array te declareren is door de array in te stellen op type Variant.

1	Dim arrVar() als variant

Met een variant Array, u hoeft de arraygrootte niet te definiëren. De maat past zich automatisch aan. Onthoud dat de array begint met positie 0 (tenzij u Option Base 1 bovenaan uw module toevoegt)

12345678910111213

Sub VariantArray()Dim arrVar() als variant'Definieer waarden (Grootte = 0,1,2,3)arrVar = Matrix (1, 2, 3, 4)'Waarden wijzigen (Grootte = 0,1,2,3,4)arrVar = Array ("1a", "2a", "3a", "4a", "5a")'Uitgangspositie 4 ("5a")MsgBox arrVar(4)Einde sub

Niet-variante dynamische arrays

Bij niet-variante arrays moet u de arraygrootte definiëren voordat u waarden aan de array toewijst. Het proces om de array te maken is echter iets anders:

1234567

Sub DynamicArray1()Dim arrDemo1() As String'Formaat van array wijzigen met posities 1,2,3,4'ReDim arrDemo1(1 tot 4)Einde sub

Eerst declareer je de array, vergelijkbaar met de statische array, behalve dat je de arraygrootte weglaat:

1	Dim arrDemo1() As String

Als u nu de arraygrootte wilt instellen, gebruikt u de ReDim commando om de array op maat te maken:

12	'Formaat van array wijzigen met posities 1,2,3,4'ReDim arrDemo1(1 tot 4)

ReDim past de grootte van de array aan. Lees hieronder het verschil tussen ReDim en ReDim Preserve.

ReDim versus ReDim behouden

Wanneer u de ReDim commando wis je alle bestaande waarden uit de array. In plaats daarvan kunt u gebruik maken van ReDim behouden om matrixwaarden te behouden:

12	'Formaat van array wijzigen met posities 1,2,3,4 (bestaande waarden behouden)ReDim behouden arrDemo1(1 tot 4)

Arrays vereenvoudigd declareren

Misschien voel je je overweldigd na het lezen van alles hierboven. Om het simpel te houden, zullen we in de rest van het artikel vooral met statische arrays werken.

Reekswaarden instellen

Het instellen van matrixwaarden is heel eenvoudig.

Met een statische array moet u elke positie van de array één voor één definiëren:

12345678

Submatrixvoorbeeld()Dim strNames (1 tot 4) als StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Jose"Einde sub

Met een Variant Array kun je de hele array met één regel definiëren (alleen praktisch voor kleine arrays):

123456

Sub ArrayExample_1Line()Dim strNames() als variantstrNames = Array ("Shelly", "Steve", "Neema", "Jose")Einde sub

Als u probeert een waarde te definiëren voor een matrixlocatie die niet bestaat, ontvangt u de foutmelding Subscript buiten bereik:

1	strNames(5) = "Shannon"

In het gedeelte 'Toewijzen bereik aan array' hieronder laten we u zien hoe u een lus kunt gebruiken om snel grote aantallen waarden aan arrays toe te wijzen.

Arraywaarde ophalen

U kunt arraywaarden op dezelfde manier ophalen. In het onderstaande voorbeeld zullen we matrixwaarden naar cellen schrijven:

1234	Bereik ("A1"). Waarde = strNames (1)Bereik ("A2"). Waarde = strNames (2)Bereik ("A3"). Waarde = strNames (3)Bereik ("A4"). Waarde = strNames (4)

VBA-programmering | Code Generator werkt voor u!

Bereik toewijzen aan array

Om een ​​bereik aan een array toe te wijzen, kunt u een lus gebruiken:

12345678

SubbereikToArray()Dim strNames (1 tot 60000) als stringDim ik zo langVoor ik = 1 tot 60000strNames(i) = Cellen(i, 1).Waardevolgende iEinde sub

Dit doorloopt cellen A1:A60000 en wijst de celwaarden toe aan de array.

Uitvoerarray naar bereik

Of u kunt een lus gebruiken om een ​​array aan een bereik toe te wijzen:

123	Voor ik = 1 tot 60000Cellen (i, 1). Waarde = strNames (i)volgende i

Dit doet het omgekeerde: wijs matrixwaarden toe aan cellen A1:A60000

2D / multidimensionale arrays

Tot nu toe hebben we uitsluitend gewerkt met eendimensionale (1D) arrays. Arrays kunnen echter maximaal 32 dimensies hebben.

Denk aan een 1D-array zoals een enkele rij of kolom met Excel-cellen, een 2D-array zoals een heel Excel-werkblad met meerdere rijen en kolommen, en een 3D-array is als een hele werkmap, met meerdere bladen die elk meerdere rijen en kolommen bevatten (u zou een 3D-array ook kunnen zien als een Rubik's Cube).

Voorbeelden van multidimensionale arrays

Laten we nu voorbeelden demonstreren van het werken met arrays van verschillende dimensies.

VBA-programmering | Code Generator werkt voor u!

Voorbeeld van 1D-array

Deze procedure combineert de vorige array-voorbeelden in één procedure en laat zien hoe u arrays in de praktijk kunt gebruiken.

1234567891011121314

Sub-arrayEx_1d()Dim strNames (1 tot 60000) als stringDim ik zo lang'Waarden toewijzen aan array'Voor ik = 1 tot 60000strNames(i) = Cellen(i, 1).Waardevolgende i'Uitvoerarraywaarden naar bereik'Voor ik = 1 tot 60000Sheets ("Output"). Cellen (i, 1). Waarde = strNames (i)volgende iEinde sub

Voorbeeld van 2D-array

Deze procedure bevat een voorbeeld van een 2D-array:

123456789101112131415161718

SubmatrixEx_2d()Dim strNames (1 tot 60000, 1 tot 10) als stringDim i As Long, j As Long'Waarden toewijzen aan array'Voor ik = 1 tot 60000Voor j = 1 tot 10strNames(i, j) = Cellen(i, j).Waardevolgende jvolgende i'Uitvoerarraywaarden naar bereik'Voor ik = 1 tot 60000Voor j = 1 tot 10Bladen ("Uitvoer"). Cellen (i, j). Waarde = strNames (i, j)volgende jvolgende iEinde sub

Voorbeeld 3D-array

Deze procedure bevat een voorbeeld van een 3D-array voor het werken met meerdere bladen:

12345678910111213141516171819202122

SubmatrixEx_3d()Dim strNames (1 tot 60000, 1 tot 10, 1 tot 3) als stringDim i zo lang, j zo lang, k zo ​​lang'Waarden toewijzen aan array'Voor k = 1 tot 3Voor ik = 1 tot 60000Voor j = 1 tot 10strNames(i, j, k) = Bladen ("Blad" & k). Cellen (i, j). Waardevolgende jvolgende ivolgende k'Uitvoerarraywaarden naar bereik'Voor k = 1 tot 3Voor ik = 1 tot 60000Voor j = 1 tot 10Sheets ("Output" & k). Cellen (i, j). Waarde = strNames (i, j, k)volgende jvolgende ivolgende kEinde sub

Matrixlengte / -grootte

Tot nu toe hebben we u kennis laten maken met de verschillende soorten arrays en hebben we u geleerd hoe u de arrays declareert en arraywaarden krijgt/instelt. Vervolgens zullen we ons concentreren op andere noodzakelijke onderwerpen voor het werken met arrays.

VBA-programmering | Code Generator werkt voor u!

UBound- en LBound-functies

De eerste stap om de lengte / grootte van een array te krijgen, is door de functies UBound en LBound te gebruiken om de boven- en ondergrenzen van de array te krijgen:

123456

Sub UBoundLBound()Dim strNames (1 tot 4) als stringMsgBox UBound(strNames)MsgBox LBound(strNamen)Einde sub

Het aftrekken van de twee (en het toevoegen van 1) geeft je de lengte:

1	GetArrLength = UBound(strNames) - LBound(strNames) + 1

Functie matrixlengte

Hier is een functie om de lengte van een array met één dimensie te krijgen:

1234567

Publieke functie GetArrLength(a As Variant) As LongAls Is Leeg(a) DanGetArrLength = 0AndersGetArrLength = UBound(a) - LBound(a) + 1Stop alsFunctie beëindigen

Wilt u de grootte van een 2D-array berekenen? Bekijk onze tutorial: Grootte van array berekenen.

Doorlus Array

Er zijn twee manieren om door een array te lussen. De eerste doorloopt de gehele getallen die overeenkomen met de nummerposities van de array. Als u de arraygrootte kent, kunt u deze direct specificeren:

12345678910111213

Sub ArrayExample_Loop1()Dim strNames (1 tot 4) als stringDim ik zo langstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Jose"Voor i = 1 tot 4MsgBox strNames(i)volgende iEinde sub

Als u de arraygrootte echter niet weet (als de array dynamisch is), kunt u de functies LBound en UBound uit de vorige sectie gebruiken:

12345678910111213

Sub ArrayExample_Loop2()Dim strNames (1 tot 4) als stringDim ik zo langstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Jose"Voor i = LBound(strNames) Naar UBound(strNames)MsgBox strNames(i)volgende iEinde sub

Voor elke array-lus

De tweede methode is met een For Each Loop. Dit doorloopt elk item in de array:

12345678910111213

Sub ArrayExample_Loop3()Dim strNames (1 tot 4) als stringDim ItemstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Jose"Voor elk item in strNamesMsgBox-itemVolgende itemEinde sub

De For Each Array Loop werkt met multidimensionale arrays naast eendimensionale arrays.

VBA-programmering | Code Generator werkt voor u!

Loop door 2D-array

U kunt ook de functies UBound en LBound gebruiken om door een multidimensionale array te lussen. In dit voorbeeld doorlopen we een 2D-array. Merk op dat u met de functies UBound en LBound kunt specificeren in welke dimensie van de array de boven- en ondergrenzen moeten worden gevonden (1 voor de eerste dimensie, 2 voor de tweede dimensie).

1234567891011121314151617181920

Sub ArrayExample_Loop4()Dim strNames (1 tot 4, 1 tot 2) als stringDim i As Long, j As LongstrNames(1, 1) = "Shelly"strNames(2, 1) = "Steve"strNames(3, 1) = "Neema"strNames(4, 1) = "Jose"strNames(1, 2) = "Shelby"strNames(2, 2) = "Steven"strNames(3, 2) = "Nemo"strNames(4, 2) = "Jesse"Voor j = LBound(strNames, 2) Naar UBound(strNames, 2)Voor i = LBound(strNames, 1) Naar UBound(strNames, 1)MsgBox strNames(i, j)volgende ivolgende jEinde sub

Andere matrixtaken

Clear Array

Gebruik de Erase-instructie om een ​​hele array te wissen:

1	Wis strNames

Gebruiksvoorbeeld:

12345678910

Submatrixvoorbeeld()Dim strNames (1 tot 4) als StringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Jose"Wis strNamesEinde sub

Als alternatief kunt u de array ook opnieuw dimmen om het formaat te wijzigen, waarbij een deel van de array wordt gewist:

1	ReDim strNames (1 tot 2)

Hierdoor wordt de grootte van de array aangepast naar grootte 2, waarbij positie 3 en 4 worden verwijderd.

graafmatrix

U kunt het aantal posities in elke dimensie van een array tellen met behulp van de UBound- en LBound-functies (hierboven besproken).

U kunt ook het aantal ingevoerde items (of items die aan bepaalde criteria voldoen) tellen door de array te doorlopen.

In dit voorbeeld wordt een reeks objecten doorgelust en wordt het aantal niet-lege tekenreeksen in de array geteld:

123456789101112131415

SubarrayLoopandCount()Dim strNames (1 tot 4) als stringDim ik zo lang, n zo langstrNames(1) = "Shelly"strNames(2) = "Steve"Voor i = LBound(strNames) Naar UBound(strNames)Als strNames(i) "" Dann = n + 1Stop alsvolgende iMsgBox n & "niet-lege waarden gevonden."Einde sub

VBA-programmering | Code Generator werkt voor u!

Duplicaten verwijderen

Op een gegeven moment wilt u misschien duplicaten uit een array verwijderen. Helaas heeft VBA geen ingebouwde functie om dit te doen. We hebben echter een functie geschreven om duplicaten uit een array te verwijderen (het is te lang om in deze tutorial op te nemen, maar bezoek de link voor meer informatie).

Filter

Met de VBA-filterfunctie kunt u een array filteren. Dit gebeurt door een nieuwe array te maken met alleen de gefilterde waarden. Hieronder is een snel voorbeeld, maar lees het artikel voor meer voorbeelden voor verschillende behoeften.

1234567891011121314

Subfilter_Match()'Definieer matrix'Dim strNamen als variantstrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Filterarray'Dim strSubNamen als variantstrSubNames = Filter(strNames, "Smith")'Gefilterde array tellen'MsgBox "Gevonden " & UBound(strSubNames) - LBound(strSubNames) + 1 & " names."Einde sub

IsArray Functie

U kunt testen of een variabele een array is met behulp van de IsArray-functie:

123456789101112

Sub IsArrayEx()'Maakt array met posities 1,2,3'Dim arrDemo1(3) As String'Maakt een normale tekenreeksvariabele'Dim str As StringMsgBox IsArray(arrDemo1)MsgBox IsArray(str)Einde sub

Deelnemen aan array

U kunt snel een hele array samenvoegen met de Join-functie:

123456789101112

Sub-array_Join()Dim strNames (1 tot 4) als stringDim joinNamen als stringstrNames(1) = "Shelly"strNames(2) = "Steve"strNames(3) = "Neema"strNames(4) = "Jose"joinNames = Join(strNames, ", ")MsgBox joinNamesEinde sub

VBA-programmering | Code Generator werkt voor u!

String splitsen in array

De VBA-splitsingsfunctie splitst een tekstreeks in een array met waarden uit de originele tekenreeks. Laten we een voorbeeld bekijken:

123456789

Sub-array_Split()Dim Names() As StringDim samengevoegde namen als tekenreeksjoinNames = "Shelly,Steve,Nema,Jose"Namen = Split(samengevoegdNamen, ",")MsgBox-namen(1)Einde sub

Hier splitsen we deze tekstreeks "Shelly,Steve,Nema,Jose" in een array (grootte 4) met behulp van het komma-scheidingsteken (").

Const-array

een matrix kan niet gedeclareerd worden als een constante in VBA. U kunt dit echter omzeilen door een functie te maken die u als array kunt gebruiken:

123456789

'Definieer ConstantArray'Functie ConstantArray()Constante Array = Array (4, 12, 21, 100, 5)Functie beëindigen'ConstantArray-waarde ophalen'Sub RetrieveValues()MsgBox ConstantArray(3)Einde sub

Kopieer array

Er is geen ingebouwde manier om een ​​array te kopiëren met VBA. In plaats daarvan moet u een lus gebruiken om de waarden van de ene array aan de andere toe te wijzen.

12345678910111213141516171819

Sub CopyArray()Dim Arr1 (1 tot 100) zo langDim Arr2 (1 tot 100) zo langDim ik zo lang'Maak Matrix1'Voor i = 1 tot 100Arr1(i) = ivolgende i'KopieerArray1 naar Array2'Voor i = 1 tot 100Arr2(i) = Arr1(i)volgende iMsgBox Arr2(74)Einde sub

transponeren

Er is geen ingebouwde VBA-functie waarmee u een array kunt transponeren. We hebben echter een functie geschreven om een ​​2D-array te transponeren. Lees het artikel voor meer informatie.

VBA-programmering | Code Generator werkt voor u!

Functie Return Array

Een veel voorkomende vraag die VBA-ontwikkelaars hebben, is hoe ze een functie kunnen maken die een array retourneert. Ik denk dat de meeste problemen worden opgelost door Variant Arrays te gebruiken. We hebben een artikel geschreven over het onderwerp: VBA Function Return Array.

Arrays gebruiken in Access VBA

De meeste van de bovenstaande Array-voorbeelden werken precies hetzelfde in Access VBA als in Excel VBA. Het enige grote verschil is dat wanneer u een array wilt vullen met Access-gegevens, u door het RecordSet-object moet lopen in plaats van door het Range-object.

1234567891011121314151617181920212223

SubbereikToArrayAccess()Bij fout Hervatten volgendeDim strNames() As StringDim ik zo langDim iCount zo langDim dbs als databaseDim eerst als recordsetStel dbs = CurrentDb inStel rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)Met eerst.MoveLast.Beweeg eerstiCount = .RecordCountReDim strNames (1 naar iCount)Voor i = 1 Naar iCountstrNames(i) = rst.Fields("ClientName").VerplaatsVolgendevolgende iEindigt meteerst.SluitenStel eerst = Niets inStel dbs = Niets inEinde sub

Array-zelfstudies
Matrix Mega-gids	Ja
Matrixgrootte ophalen
Reeks wissen
Filterarray
Matrix transponeren
Functie Return Array
Duplicaten verwijderen