VBA -arrays

I VBA, en Array er en enkelt variabel, der kan indeholde flere værdier. Tænk på en matrix som en række celler: hver celle kan gemme en værdi. Arrays kan være endimensionale (tænk på en enkelt kolonne), todimensionel (tænk på flere rækker og kolonner) eller multidimensionale. Arrayværdier kan tilgås af deres position (indeksnummer) i arrayet.

VBA Array Quick Sheet

Arrays

BeskrivelseVBA -kodeskabDim arr (1 til 3) Som variant
arr (1) = "en"
arr (2) = “to”
arr (3) = “tre”Opret fra ExcelDim arr (1 til 3) Som variant
Dim cell As Range, i As Integer
i = LBound (arr)
For hver celle i området ("A1: A3")
i = i + 1
arr (i) = celle.værdi
Næste celleLæs alle varerDim i som lang
For i = LBound (arr) Til UBound (arr)
MsgBox arr (i)
Næste iSletteSlet arrArray til strengDim sName som streng
sName = Deltag (arr, “:”)Forøg størrelseReDim Bevar arr (0 til 100)Indstil værdiarr (1) = 22

VBA Array Hurtige eksempler

Lad os se på et komplet eksempel, før vi dykker ned i specifikationer:

12345678910

Sub ArrayExample ()Dim strNames (1 til 4) som strengstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)Afslut Sub

Her har vi oprettet det endimensionale strengarray: strNames med størrelse fire (kan indeholde fire værdier) og tildelt de fire værdier. Sidst viser vi den tredje værdi i en meddelelsesboks.

I dette tilfælde er fordelen ved at bruge et array lille: kun en variabel erklæring kræves i stedet for fire.

Lad os dog se på et eksempel, der viser den sande kraft i et array:

12345678

Sub ArrayExample2 ()Dim strNames (1 til 60000) som strengDim i så længeFor i = 1 Til 60000strNames (i) = Celler (i, 1) .VærdiNæste iAfslut Sub

Her har vi oprettet et array, der kan indeholde 60.000 værdier, og vi har hurtigt udfyldt arrayet fra kolonne A i et regneark.

Array fordele? - Hastighed!

Du tænker måske på arrays, der ligner Excel -regneark:

• Hver celle (eller element i en matrix) kan indeholde sin egen værdi
• Hver celle (eller element i en matrix) kan tilgås ved række og kolonne.
  • Arbejdsark Eks. celler (1,4) .value = “Række 1, kolonne 4”
  • Array Ex. arrVar (1,4) = “Række 1, kolonne 4”

Så hvorfor gider Arrays? Hvorfor ikke bare læse og skrive værdier direkte til celler i Excel? Et ord: Hastighed!

At læse / skrive til Excel -celler er en langsom proces. Arbejde med arrays er meget hurtigere!

Opret / erklær et array (dim)

Bemærk: Arrays kan have flere "dimensioner". For at holde tingene enkle, starter vi med kun at arbejde med endimensionelle arrays. Senere i selvstudiet introducerer vi dig til flere dimensioner arrays.

Statisk matrix

Statiske arrays er arrays, der ikke kan ændre størrelse. Omvendt Dynamiske arrays kan ændre størrelse. De erklæres lidt anderledes. Lad os først se på statiske arrays.

Bemærk: Hvis dit array ikke ændres i størrelse, skal du bruge et statisk array.

Deklaration af en statisk array -variabel ligner meget på at erklære en regulær variabel, bortset fra at du skal definere matrixens størrelse. Der er flere forskellige måder at indstille størrelsen på en matrix på.

Du kan eksplicit erklære start- og slutpositionerne for et array:

123456789101112

Sub StaticArray1 ()'Opretter array med positioner 1,2,3,4Dim arrDemo1 (1 til 4) som streng'Opretter array med positioner 4,5,6,7Dim arrDemo2 (4 til 7) så længe'Opretter array med positioner 0,1,2,3Dim arrDemo3 (0 til 3) så længeAfslut Sub

Eller du kan kun indtaste matrixstørrelsen:

123456

Sub StaticArray2 ()'Opretter array med positioner 0,1,2,3Dim arrDemo1 (3) Som strengAfslut Sub

Vigtig! Bemærk, at som standard starter arrays på position 0. Så Dim arrDemo1 (3) opretter en matrix med positioner 0,1,2,3.

Du kan erklære Option Base 1 øverst på dit modul, så arrayet starter i position 1 i stedet:

12345678

Option Base 1Sub StaticArray3 ()'Opretter array med positioner 1,2,3Dim arrDemo1 (3) Som strengAfslut Sub

Jeg synes dog, at det er meget lettere (og mindre forvirrende) bare eksplicit at erklære start- og slutpositioner for arrays.

Træt af at søge efter eksempler på VBA -kode? Prøv AutoMacro!

Dynamisk matrix

Dynamiske arrays er arrays, hvis størrelse kan ændres (eller hvis størrelse ikke skal defineres).

Der er to måder at erklære et dynamisk array.

Variant arrays

Den første måde at erklære et dynamisk array er ved at indstille arrayet til at skrive Variant.

1	Dim arrVar () Som variant

Med en Variant Array, du behøver ikke at definere matrixstørrelsen. Størrelsen justeres automatisk. Husk bare, at Array starter med position 0 (medmindre du tilføjer Option Base 1 til toppen af ​​dit modul)

12345678910111213

Sub VariantArray ()Dim arrVar () Som variant'Definer værdier (størrelse = 0,1,2,3)arrVar = Array (1, 2, 3, 4)'Ændre værdier (størrelse = 0,1,2,3,4)arrVar = Array ("1a", "2a", "3a", "4a", "5a")'Outputposition 4 ("5a")MsgBox arrVar (4)Afslut Sub

Ikke-variant dynamiske arrays

Med ikke-variantarrays skal du definere matrixstørrelsen, før du tildeler værdier til arrayet. Processen til at oprette arrayet er dog en smule anderledes:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () Som streng'Ændrer størrelsen på array med positioner 1,2,3,4ReDim arrDemo1 (1 til 4)Afslut Sub

Først erklærer du arrayet, der ligner det statiske array, medmindre du udelader arraystørrelsen:

1	Dim arrDemo1 () Som streng

Når du nu vil angive matrixstørrelsen, bruger du ReDim kommando for at størrelse arrayet:

12	'Ændrer størrelsen på array med positioner 1,2,3,4ReDim arrDemo1 (1 til 4)

ReDim ændrer størrelsen på matrixen. Læs nedenfor for forskellen mellem ReDim og ReDim Preserve.

ReDim vs. ReDim Preserve

Når du bruger ReDim kommando, du rydder alle eksisterende værdier fra arrayet. I stedet kan du bruge ReDim Preserve for at bevare arrayværdier:

12	'Ændrer størrelsen på array med positioner 1,2,3,4 (Bevar eksisterende værdier)ReDim Bevar arrDemo1 (1 til 4)

Deklarere arrays forenklet

Du føler dig måske overvældet efter at have læst alt ovenfor. For at holde tingene enkle vil vi for det meste arbejde med statiske arrays i resten af ​​artiklen.

Angiv matrixværdier

Det er meget let at indstille arrayværdier.

Med et statisk array skal du definere hver position i arrayet, en ad gangen:

12345678

Sub ArrayExample ()Dim strNames (1 til 4) som strengstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Afslut Sub

Med en Variant Array kan du definere hele arrayet med en linje (kun praktisk til små arrays):

123456

Sub ArrayExample_1Line ()Dim strNames () Som variantstrNames = Array ("Shelly", "Steve", "Neema", "Jose")Afslut Sub

Hvis du forsøger at definere en værdi for en matrixplacering, der ikke findes, modtager du en fejl i abonnement uden for område:

1	strNames (5) = "Shannon"

I afsnittet 'Tildel område til matrix' nedenfor viser vi dig, hvordan du bruger en loop til hurtigt at tildele store værdier til arrays.

Få Array -værdi

Du kan hente matrixværdier på samme måde. I eksemplet herunder skriver vi matrixværdier til celler:

1234	Område ("A1"). Værdi = strNames (1)Område ("A2"). Værdi = strNames (2)Område ("A3"). Værdi = strNames (3)Område ("A4"). Værdi = strNames (4)

VBA -programmering | Kodegenerator virker for dig!

Tildel rækkevidde til array

For at tildele et område til et array kan du bruge en loop:

12345678

Sub RangeToArray ()Dim strNames (1 til 60000) som strengDim i så længeFor i = 1 Til 60000strNames (i) = Celler (i, 1) .VærdiNæste iAfslut Sub

Dette vil gå gennem cellerne A1: A60000 og tildele celleværdierne til arrayet.

Output Array til område

Eller du kan bruge en loop til at tildele en array til et område:

123	For i = 1 Til 60000Celler (i, 1) .Værdi = strNames (i)Næste i

Dette vil gøre det modsatte: tildel array -værdier til celler A1: A60000

2D / flerdimensionale arrays

Indtil videre har vi udelukkende arbejdet med endimensionale (1D) arrays. Arrays kan dog have op til 32 dimensioner.

Tænk på et 1D -array som en enkelt række eller kolonne med Excel -celler, et 2D -array som et helt Excel -regneark med flere rækker og kolonner, og et 3D -array er som en hel projektmappe, der indeholder flere ark, der hver indeholder flere rækker og kolonner (Du kunne også tænke på et 3D -array som en Rubiks kube).

Eksempler på flere dimensioner

Lad os nu demonstrere eksempler på arbejde med arrays af forskellige dimensioner.

VBA -programmering | Kodegenerator virker for dig!

1D Array Eksempel

Denne procedure kombinerer de tidligere matrixeksempler til en procedure, der viser, hvordan du kan bruge arrays i praksis.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 til 60000) som strengDim i så længe'Tildel værdier til arrayFor i = 1 Til 60000strNames (i) = Celler (i, 1) .VærdiNæste i'Output -arrayværdier til områdeFor i = 1 Til 60000Ark ("Output"). Celler (i, 1) .Værdi = strNames (i)Næste iAfslut Sub

2D Array Eksempel

Denne procedure indeholder et eksempel på et 2D -array:

123456789101112131415161718

Sub ArrayEx_2d ()Dim strNavne (1 til 60000, 1 til 10) som strengDim i As Long, j As Long'Tildel værdier til arrayFor i = 1 Til 60000For j = 1 til 10strNames (i, j) = Celler (i, j) .VærdiNæste jNæste i'Output -arrayværdier til områdeFor i = 1 Til 60000For j = 1 til 10Ark ("Output"). Celler (i, j) .Værdi = strNames (i, j)Næste jNæste iAfslut Sub

Eksempel på 3D -array

Denne procedure indeholder et eksempel på et 3D -array til arbejde med flere ark:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Dim strNavne (1 til 60000, 1 til 10, 1 til 3) som strengDim i så længe, ​​j så længe, ​​k så længe'Tildel værdier til arrayFor k = 1 til 3For i = 1 Til 60000For j = 1 til 10strNames (i, j, k) = Sheets ("Sheet" & k). Celler (i, j) .VærdiNæste jNæste iNæste k'Output -arrayværdier til områdeFor k = 1 til 3For i = 1 Til 60000For j = 1 til 10Ark ("Output" & k). Celler (i, j) .Værdi = strNames (i, j, k)Næste jNæste iNæste kAfslut Sub

Array længde / størrelse

Indtil videre har vi introduceret dig til de forskellige typer arrays og lært dig, hvordan du deklarerer arrays og får/indstiller array -værdier. Dernæst vil vi fokusere på andre nødvendige emner til arbejde med arrays.

VBA -programmering | Kodegenerator virker for dig!

UBound og LBound funktioner

Det første trin for at få længden / størrelsen på en matrix er at bruge funktionerne UBound og LBound til at få matrixens øvre og nedre grænser:

123456

Sub UBoundLBound ()Dim strNavne (1 til 4) som strengMsgBox UBound (strNames)MsgBox LBound (strNames)Afslut Sub

Ved at trække de to (og tilføje 1) får du længden:

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

Array længde funktion

Her er en funktion til at få en enkeltdimensions array længde:

1234567

Offentlig funktion GetArrLength (a som variant) så længeHvis er tom (a) SåGetArrLength = 0AndetGetArrLength = UBound (a) - LBound (a) + 1Afslut HvisAfslut funktion

Skal du beregne størrelsen på et 2D -array? Tjek vores selvstudium: Beregn matrixstørrelse.

Loop Through Array

Der er to måder at sløjfe igennem et array. De første sløjfer gennem heltalene, der svarer til matrixens talpositioner. Hvis du kender matrixstørrelsen, kan du angive den direkte:

12345678910111213

Sub ArrayExample_Loop1 ()Dim strNavne (1 til 4) som strengDim i så længestrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"For i = 1 til 4MsgBox strNames (i)Næste iAfslut Sub

Men hvis du ikke kender matrixstørrelsen (hvis arrayet er dynamisk), kan du bruge LBound- og UBound -funktionerne fra det forrige afsnit:

12345678910111213

Sub ArrayExample_Loop2 ()Dim strNavne (1 til 4) som strengDim i så længestrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"For i = LBound (strNames) Til UBound (strNames)MsgBox strNames (i)Næste iAfslut Sub

For hver Array Loop

Den anden metode er med en For Every Loop. Dette går gennem hvert element i arrayet:

12345678910111213

Sub ArrayExample_Loop3 ()Dim strNavne (1 til 4) som strengDim objektstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"For hvert element i strNamesMsgBox VareNæste elementAfslut Sub

For hver matrix-loop fungerer sammen med multidimensionale arrays ud over endimensionale arrays.

VBA -programmering | Kodegenerator virker for dig!

Loop Through 2D Array

Du kan også bruge UBound- og LBound-funktionerne til også at gå gennem et multidimensionalt array. I dette eksempel går vi gennem et 2D -array. Bemærk, at UBound- og LBound -funktionerne giver dig mulighed for at angive, hvilken dimension af arrayet der skal findes de øvre og nedre grænser (1 for første dimension, 2 for anden dimension).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Dim strNavne (1 til 4, 1 til 2) som strengDim 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"For j = LBound (strNames, 2) Til UBound (strNames, 2)For i = LBound (strNames, 1) Til UBound (strNames, 1)MsgBox strNames (i, j)Næste iNæste jAfslut Sub

Andre opgaver

Klar matrix

For at rydde en hel matrix skal du bruge sletningserklæringen:

1	Slet strNames

Eksempel på brug:

12345678910

Sub ArrayExample ()Dim strNames (1 til 4) som strengstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Slet strNamesAfslut Sub

Alternativt kan du også ReDim arrayet for at ændre størrelsen på det, og ryd en del af arrayet:

1	ReDim strNames (1 til 2)

Dette ændrer størrelsen på arrayet til størrelse 2, og sletter position 3 og 4.

Count Array

Du kan tælle antallet af positioner i hver dimension af en matrix ved hjælp af UBound- og LBound -funktionerne (diskuteret ovenfor).

Du kan også tælle antallet af indtastede elementer (eller elementer, der opfylder visse kriterier) ved at gå gennem arrayet.

Dette eksempel går gennem en række objekter og tæller antallet af ikke-tomme strenge, der findes i arrayet:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNavne (1 til 4) som strengDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"For i = LBound (strNames) Til UBound (strNames)Hvis strNames (i) "" Sån = n + 1Afslut HvisNæste iMsgBox n & "ikke-tomme værdier fundet."Afslut Sub

VBA -programmering | Kodegenerator virker for dig!

Fjern dubletter

På et tidspunkt vil du måske fjerne dubletter fra et array. Desværre har VBA ikke en indbygget funktion til at gøre dette. Vi har dog skrevet en funktion til at fjerne dubletter fra et array (det er for lang tid at inkludere i denne vejledning, men besøg linket for at lære mere).

Filter

VBA -filterfunktionen giver dig mulighed for at filtrere et array. Det gør det ved at oprette et nyt array med kun de filtrerede værdier. Nedenfor er et hurtigt eksempel, men sørg for at læse artiklen for flere eksempler til forskellige behov.

1234567891011121314

Sub Filter_Match ()'Definer ArrayDim strNames som variantstrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Filter ArrayDim strSubNames som variantstrSubNames = Filter (strNames, "Smith")'Tæl filtreret matrixMsgBox "Fundet" og UBound (strSubNames) - LBound (strSubNames) + 1 & "navne."Afslut Sub

IsArray -funktion

Du kan teste, om en variabel er en matrix ved hjælp af IsArray -funktionen:

123456789101112

Sub IsArrayEx ()'Opretter array med positioner 1,2,3Dim arrDemo1 (3) Som streng'Opretter regelmæssig strengvariabelDim str Som strengMsgBox IsArray (arrDemo1)MsgBox IsArray (str)Afslut Sub

Deltag i Array

Du kan hurtigt “slutte sig til” et helt array sammen med Join -funktionen:

123456789101112

Sub Array_Join ()Dim strNavne (1 til 4) som strengDim joinNames som strengstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Deltag i (strNames, ",")MsgBox joinNamesAfslut Sub

VBA -programmering | Kodegenerator virker for dig!

Opdel streng i array

VBA -splitfunktionen vil opdele en tekststreng i et array, der indeholder værdier fra den originale streng. Lad os se på et eksempel:

123456789

Sub Array_Split ()Dim navne () som strengDim sluttede sig til Navne som strengjoinedNames = "Shelly, Steve, Nema, Jose"Names = Split (joinedNames, ",")MsgBox navne (1)Afslut Sub

Her opdeler vi denne tekststreng “Shelly, Steve, Nema, Jose” i en matrix (størrelse 4) ved hjælp af en kommaafgrænser (, ”).

Const Array

Et array kan ikke erklæres som en konstant i VBA. Du kan dog omgå dette ved at oprette en funktion, der skal bruges som et array:

123456789

'Definer ConstantArrayFunktion ConstantArray ()ConstantArray = Array (4, 12, 21, 100, 5)Afslut funktion'Retrive ConstantArray -værdiSub RetrieveValues ​​()MsgBox ConstantArray (3)Afslut Sub

Copy Array

Der er ingen indbygget måde at kopiere et array ved hjælp af VBA. I stedet skal du bruge en loop til at tildele værdierne fra et array til et andet.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 til 100) så længeDim Arr2 (1 til 100) så længeDim i så længe'Opret array1For i = 1 til 100Arr1 (i) = iNæste i'CopyArray1 til Array2For i = 1 til 100Arr2 (i) = Arr1 (i)Næste iMsgBox Arr2 (74)Afslut Sub

Transponere

Der er ingen indbygget VBA-funktion, der giver dig mulighed for at transponere et array. Vi har dog skrevet en funktion til Transpose a 2D Array. Læs artiklen for at lære mere.

VBA -programmering | Kodegenerator virker for dig!

Funktion Return Array

Et almindeligt spørgsmål, VBA -udviklere har, er, hvordan man opretter en funktion, der returnerer en matrix. Jeg tror, ​​at de fleste vanskeligheder er løst ved at bruge Variant Arrays. Vi har skrevet en artikel om emnet: VBA Function Return Array.

Brug af arrays i Access VBA

De fleste af Array -eksemplerne ovenfor fungerer nøjagtig det samme i Access VBA, som de gør i Excel VBA. Den ene store forskel er, at når du ønsker at udfylde en matrix ved hjælp af Access -data, skal du gå gennem RecordSet -objektet frem for Range -objektet.

1234567891011121314151617181920212223

UnderområdeToArrayAccess ()Ved fejl Genoptag næsteDim strNames () Som strengDim i så længeDim iCount så længeDim dbs som databaseDim første som rekordsætIndstil dbs = CurrentDbIndstil rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Med første.Flyt sidste.MoveFirstiCount = .RecordCountReDim strNames (1 til iCount)For i = 1 Til iCountstrNames (i) = rst.Fields ("ClientName").MoveNextNæste iAfslut medførst. lukIndstil rst = IntetIndstil dbs = IntetAfslut Sub

Array Tutorials
Array Mega-Guide	Ja
Få matrixstørrelse
Klar matrix
Filter Array
Transpose Array
Funktion Return Array
Fjern dubletter