VBA -matriser

I VBA, en Array er en enkelt variabel som kan inneholde flere verdier. Tenk på en matrise som en rekke celler: hver celle kan lagre en verdi. Matriser kan være endimensjonale (tenk på en enkelt kolonne), todimensjonal (tenk på flere rader og kolonner) eller flerdimensjonale. Arrayverdier kan nås med deres posisjon (indeksnummer) i matrisen.

VBA Array Quick Sheet

Matriser

BeskrivelseVBA -kodeSkapeDim arr (1 til 3) Som variant
arr (1) = “en”
arr (2) = “to”
arr (3) = “tre”Lag 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) = celleverdi
Neste celleLes alle varerDim i som Long
For i = LBound (arr) Til UBound (arr)
MsgBox arr (i)
Neste jegViske utSlett arrArray to StringDim sName Som streng
sName = Bli med (arr, “:”)Øk størrelseReDim Preserve arr (0 til 100)Angi verdiarr (1) = 22

VBA Array Quick -eksempler

La oss se på et fullstendig eksempel før vi dykker ned i detaljer:

12345678910

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

Her har vi opprettet den endimensjonale strengmatrisen: strNames med størrelse fire (kan inneholde fire verdier) og tilordnet de fire verdiene. Sist viser vi den tredje verdien i en meldingsboks.

I dette tilfellet er fordelen med å bruke en matrise liten: bare én variabel deklarasjon kreves i stedet for fire.

La oss imidlertid se på et eksempel som viser den sanne kraften til en matrise:

12345678

Sub ArrayExample2 ()Dim strNames (1 To 60000) As StringDim i As LongFor i = 1 Til 60000strNames (i) = Celler (i, 1) .ValueNeste jegSlutt Sub

Her har vi opprettet en matrise som kan inneholde 60 000 verdier, og vi har raskt fylt ut matrisen fra kolonne A i et regneark.

Array fordeler? - Hastighet!

Du tenker kanskje på matriser som ligner på Excel -regneark:

• Hver celle (eller element i en matrise) kan inneholde sin egen verdi
• Hver celle (eller element i en matrise) kan nås med sin rad- og kolonneposisjon.
  • Regneark Eks. celler (1,4) .value = “Rad 1, kolonne 4”
  • Array Ex. arrVar (1,4) = “Rad 1, kolonne 4”

Så hvorfor bry deg med Arrays? Hvorfor ikke bare lese og skrive verdier direkte til celler i Excel? Et ord: Hastighet!

Å lese / skrive til Excel -celler er en treg prosess. Å jobbe med matriser er mye raskere!

Opprett / erklær et matrise (Dim)

Merk: Matriser kan ha flere "dimensjoner". For å holde ting enkelt, starter vi med å bare jobbe med endimensjonale matriser. Senere i opplæringen vil vi introdusere deg for flerdimensjonale matriser.

Statisk matrise

Statiske matriser er matriser som ikke kan endre størrelse. Motsatt, Dynamiske matriser kan endre størrelse. De er erklært litt annerledes. La oss først se på statiske matriser.

Merk: Hvis matrisen din ikke vil endre størrelse, bruk en statisk matrise.

Å deklarere en statisk matrisevariabel ligner veldig på å deklarere en vanlig variabel, bortsett fra at du må definere størrelsen på matrisen. Det er flere forskjellige måter å angi størrelsen på en matrise på.

Du kan eksplisitt erklære start- og sluttposisjonen til en matrise:

123456789101112

Sub StaticArray1 ()'Oppretter matrise med posisjoner 1,2,3,4Dim arrDemo1 (1 til 4) som streng'Oppretter matrise med posisjoner 4,5,6,7Dim arrDemo2 (4 til 7) så lenge'Oppretter matrise med posisjoner 0,1,2,3Dim arrDemo3 (0 til 3) så lengeSlutt Sub

Eller du kan bare angi matrisestørrelsen:

123456

Sub StaticArray2 ()'Oppretter matrise med posisjoner 0,1,2,3Dim arrDemo1 (3) Som strengSlutt Sub

Viktig! Legg merke til at som standard starter matriser på posisjon 0. Så Dim arrDemo1 (3) oppretter en matrise med posisjoner 0,1,2,3.

Du kan erklære Alternativbase 1 på toppen av modulen slik at matrisen starter i posisjon 1 i stedet:

12345678

Alternativbase 1Sub StaticArray3 ()'Oppretter matrise med posisjoner 1,2,3Dim arrDemo1 (3) Som strengSlutt Sub

Imidlertid synes jeg at det er mye lettere (og mindre forvirrende) å bare eksplisitt erklære start- og sluttposisjonen til matriser.

Lei av å søke etter eksempler på VBA -kode? Prøv AutoMacro!

Dynamisk matrise

Dynamiske matriser er matriser hvis størrelse kan endres (eller hvis størrelse ikke trenger å defineres).

Det er to måter å erklære en dynamisk matrise på.

Variant Arrays

Den første måten å deklarere en dynamisk matrise er ved å sette matrisen til å skrive Variant.

1	Dim arrVar () Som variant

Med en Variant Array, du trenger ikke å definere matrisestørrelsen. Størrelsen justeres automatisk. Bare husk at matrisen starter med posisjon 0 (med mindre du legger til alternativbase 1 øverst i modulen)

12345678910111213

Sub VariantArray ()Dim arrVar () Som variant'Definer verdier (størrelse = 0,1,2,3)arrVar = Array (1, 2, 3, 4)'Endre verdier (størrelse = 0,1,2,3,4)arrVar = Array ("1a", "2a", "3a", "4a", "5a")'Utgangsposisjon 4 ("5a")MsgBox arrVar (4)Slutt Sub

Ikke-variant dynamiske matriser

Med ikke-variantarrayer må du definere matrisestørrelsen før du tilordner verdier til matrisen. Prosessen for å lage matrisen er imidlertid litt annerledes:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () Som streng'Endrer størrelsen på matrisen med posisjonene 1,2,3,4ReDim arrDemo1 (1 til 4)Slutt Sub

Først deklarerer du matrisen, lik den statiske matrisen, bortsett fra at du utelater matrisestørrelsen:

1	Dim arrDemo1 () Som streng

Når du vil angi matrisestørrelsen, bruker du ReDim kommando for å størrelse matrisen:

12	'Endrer størrelsen på matrisen med posisjonene 1,2,3,4ReDim arrDemo1 (1 til 4)

ReDim endrer størrelsen på matrisen. Les nedenfor for forskjellen mellom ReDim og ReDim Preserve.

ReDim vs. ReDim Preserve

Når du bruker ReDim kommandoen du sletter alle eksisterende verdier fra matrisen. I stedet kan du bruke ReDim Preserve for å bevare matrisverdier:

12	'Endrer størrelsen på matrisen med posisjonene 1,2,3,4 (Bevarer eksisterende verdier)ReDim Bevar arrDemo1 (1 til 4)

Deklarere matriser forenklet

Du kan føle deg overveldet etter å ha lest alt ovenfor. For å holde ting enkelt, vil vi stort sett jobbe med statiske matriser for resten av artikkelen.

Sett matriseverdier

Det er veldig enkelt å sette matriseverdier.

Med en statisk matrise må du definere hver posisjon i matrisen, en om gangen:

12345678

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

Med en Variant Array kan du definere hele matrisen med en linje (bare praktisk for små matriser):

123456

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

Hvis du prøver å definere en verdi for en matriseplassering som ikke eksisterer, vil du motta en Subscript Out of Range -feil:

1	strNames (5) = "Shannon"

I delen "Tilordne område til matrise" nedenfor viser vi deg hvordan du bruker en løkke for raskt å tilordne store verdier til matriser.

Få Array -verdi

Du kan hente matrisverdier på samme måte. I eksemplet nedenfor skriver vi matrisverdier til celler:

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

VBA -programmering | Kodegenerator fungerer for deg!

Tilordne område til matrise

For å tilordne et område til en matrise kan du bruke en sløyfe:

12345678

Sub RangeToArray ()Dim strNames (1 To 60000) As StringDim i As LongFor i = 1 Til 60000strNames (i) = Celler (i, 1) .ValueNeste jegSlutt Sub

Dette vil gå gjennom cellene A1: A60000 og tildele celleverdiene til matrisen.

Utdatamatrise til område

Eller du kan bruke en løkke til å tilordne en matrise til et område:

123	For i = 1 Til 60000Celler (i, 1) .Value = strNames (i)Neste jeg

Dette vil gjøre det motsatte: tilordne matriseverdier til celler A1: A60000

2D / flerdimensjonale matriser

Så langt har vi jobbet utelukkende med endimensjonale (1D) matriser. Imidlertid kan matriser ha opptil 32 dimensjoner.

Tenk på et 1D -array som en enkelt rad eller kolonne med Excel -celler, et 2D -array som et helt Excel -regneark med flere rader og kolonner, og et 3D -array er som en hel arbeidsbok, som inneholder flere ark som hver inneholder flere rader og kolonner (Du kan også tenke på et 3D -array som en Rubiks kube).

Eksempler på flerdimensjonale matriser

La oss nå demonstrere eksempler på arbeid med matriser av forskjellige dimensjoner.

VBA -programmering | Kodegenerator fungerer for deg!

1D Array Eksempel

Denne prosedyren kombinerer de forrige matriseeksemplene til én prosedyre, som viser hvordan du kan bruke matriser i praksis.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 To 60000) As StringDim i As Long'Tilordne verdier til matrisenFor i = 1 Til 60000strNames (i) = Celler (i, 1) .ValueNeste jeg'Output Array Verdier til områdeFor i = 1 Til 60000Ark ("Output"). Celler (i, 1) .Value = strNames (i)Neste jegSlutt Sub

2D Array Eksempel

Denne prosedyren inneholder et eksempel på en 2D -matrise:

123456789101112131415161718

Sub ArrayEx_2d ()Dim strNames (1 To 60000, 1 To 10) As StringDim i As Long, j As Long'Tilordne verdier til matrisenFor i = 1 Til 60000For j = 1 til 10strNames (i, j) = Celler (i, j) .ValueNeste jNeste jeg'Output Array Verdier til områdeFor i = 1 Til 60000For j = 1 til 10Ark ("Output"). Celler (i, j) .Value = strNames (i, j)Neste jNeste jegSlutt Sub

3D Array Eksempel

Denne fremgangsmåten inneholder et eksempel på en 3D -matrise for arbeid med flere ark:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Dim strNames (1 To 60000, 1 To 10, 1 To 3) As StringDim i Så lenge, j Så lenge, k Så lenge'Tilordne verdier til matrisenFor k = 1 til 3For i = 1 Til 60000For j = 1 til 10strNames (i, j, k) = Sheets ("Sheet" & k). Celler (i, j) .ValueNeste jNeste jegNeste k'Output Array Verdier til områdeFor k = 1 til 3For i = 1 Til 60000For j = 1 til 10Ark ("Output" & k). Celler (i, j) .Value = strNames (i, j, k)Neste jNeste jegNeste kSlutt Sub

Array lengde / størrelse

Så langt har vi introdusert deg for de forskjellige matrisene og lært deg hvordan du deklarerer matrisene og får/angir matrisverdier. Deretter vil vi fokusere på andre nødvendige emner for å jobbe med matriser.

VBA -programmering | Kodegenerator fungerer for deg!

UBound- og LBound -funksjoner

Det første trinnet for å få lengden / størrelsen på en matrise er å bruke funksjonene UBound og LBound for å få de øvre og nedre grensene for matrisen:

123456

Sub UBoundLBound ()Dim strNames (1 To 4) As StringMsgBox UBound (strNames)MsgBox LBound (strNames)Slutt Sub

Trekker du de to (og legger til 1) får du lengden:

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

Array lengde funksjon

Her er en funksjon for å få en endimensjons matrises lengde:

1234567

Offentlig funksjon GetArrLength (en som variant) så lengeIf IsEmpty (a) DaGetArrLength = 0EllersGetArrLength = UBound (a) - LBound (a) + 1Slutt omSluttfunksjon

Trenger du å beregne størrelsen på et 2D -array? Ta en titt på opplæringen vår: Beregn størrelsen på matrisen.

Loop Through Array

Det er to måter å gå gjennom en matrise. De første sløyfene gjennom heltallene som tilsvarer tallposisjonene i matrisen. Hvis du kjenner matrisestørrelsen, kan du spesifisere den direkte:

12345678910111213

Sub ArrayExample_Loop1 ()Dim strNames (1 To 4) As StringDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"For i = 1 til 4MsgBox strNames (i)Neste jegSlutt Sub

Hvis du imidlertid ikke kjenner matrisestørrelsen (hvis matrisen er dynamisk), kan du bruke funksjonene LBound og UBound fra forrige seksjon:

12345678910111213

Sub ArrayExample_Loop2 ()Dim strNames (1 To 4) As StringDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"For i = LBound (strNames) Til UBound (strNames)MsgBox strNames (i)Neste jegSlutt Sub

For hver Array Loop

Den andre metoden er med en For Every Loop. Dette går gjennom hvert element i matrisen:

12345678910111213

Sub ArrayExample_Loop3 ()Dim strNames (1 To 4) As StringDim elementstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"For hvert element i strNamesMsgBox -vareNeste elementSlutt Sub

For Every Array Loop fungerer med flerdimensjonale matriser i tillegg til endimensjonale matriser.

VBA -programmering | Kodegenerator fungerer for deg!

Loop Through 2D Array

Du kan også bruke UBound- og LBound-funksjonene til å gå gjennom et flerdimensjonalt array. I dette eksemplet vil vi gå gjennom et 2D -array. Legg merke til at UBound- og LBound -funksjonene lar deg angi hvilken dimensjon i matrisen som skal finne de øvre og nedre grensene (1 for første dimensjon, 2 for andre dimensjon).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Dim strNames (1 To 4, 1 To 2) As 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"For j = LBound (strNames, 2) Til UBound (strNames, 2)For i = LBound (strNames, 1) Til UBound (strNames, 1)MsgBox strNames (i, j)Neste jegNeste jSlutt Sub

Andre oppgaver

Klar matrise

For å slette en hel matrise, bruk sletteerklæringen:

1	Slett strNames

Eksempel på bruk:

12345678910

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

Alternativt kan du også Redimere matrisen for å endre størrelsen på den, slette en del av matrisen:

1	ReDim strNames (1 til 2)

Dette endrer størrelsen på matrisen til størrelse 2, og sletter posisjonene 3 og 4.

Count Array

Du kan telle antall posisjoner i hver dimensjon i en matrise ved å bruke UBound- og LBound -funksjonene (omtalt ovenfor).

Du kan også telle antall oppførte elementer (eller elementer som oppfyller visse kriterier) ved å gå gjennom matrisen.

Dette eksemplet går gjennom en rekke objekter og teller antall ikke-tomme strenger som finnes i matrisen:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNames (1 To 4) As StringDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"For i = LBound (strNames) Til UBound (strNames)If strNames (i) "" Dan = n + 1Slutt omNeste jegMsgBox n & "ikke-tomme verdier funnet."Slutt Sub

VBA -programmering | Kodegenerator fungerer for deg!

Fjern duplikater

På et tidspunkt vil du kanskje fjerne duplikater fra en matrise. Dessverre har VBA ikke en innebygd funksjon for å gjøre dette. Imidlertid har vi skrevet en funksjon for å fjerne duplikater fra en matrise (det er for lang tid å inkludere i denne opplæringen, men besøk lenken for å lære mer).

Filter

VBA -filterfunksjonen lar deg filtrere en matrise. Det gjør det ved å opprette en ny matrise med bare de filtrerte verdiene. Nedenfor er et raskt eksempel, men sørg for å lese artikkelen for flere eksempler for forskjellige behov.

1234567891011121314

Sub Filter_Match ()'Definer matriseDim strNames As VariantstrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Filter ArrayDim strSubNames As VariantstrSubNames = Filter (strNames, "Smith")'Count Filtered ArrayMsgBox "Found" & UBound (strSubNames) - LBound (strSubNames) + 1 & "names."Slutt Sub

IsArray -funksjon

Du kan teste om en variabel er en matrise ved hjelp av IsArray -funksjonen:

123456789101112

Sub IsArrayEx ()'Oppretter matrise med posisjoner 1,2,3Dim arrDemo1 (3) Som streng'Oppretter vanlig strengvariabelDim str As StringMsgBox IsArray (arrDemo1)MsgBox IsArray (str)Slutt Sub

Bli med i Array

Du kan raskt "bli med" i en hel matrise sammen med Join -funksjonen:

123456789101112

Sub Array_Join ()Dim strNames (1 To 4) As StringDim joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Bli med (strNames, ",")MsgBox joinNamesSlutt Sub

VBA -programmering | Kodegenerator fungerer for deg!

Del strengen i Array

VBA -delingsfunksjonen vil dele en tekststreng inn i en matrise som inneholder verdier fra den opprinnelige strengen. La oss se på et eksempel:

123456789

Sub Array_Split ()Dim Names () Som strengDim sluttet seg til Names As StringjoinedNames = "Shelly, Steve, Nema, Jose"Navn = Split (joinedNames, ",")MsgBox navn (1)Slutt Sub

Her deler vi denne tekststrengen "Shelly, Steve, Nema, Jose" i en matrise (størrelse 4) ved hjelp av komma -skilletegnet (, ").

Const Array

En matrise kan ikke erklæres som en konstant i VBA. Du kan imidlertid omgå dette ved å opprette en funksjon som skal brukes som en matrise:

123456789

'Definer ConstantArrayFunksjon ConstantArray ()ConstantArray = Array (4, 12, 21, 100, 5)Sluttfunksjon'Retrive ConstantArray -verdiSub RetrieveValues ​​()MsgBox ConstantArray (3)Slutt Sub

Copy Array

Det er ingen innebygd måte å kopiere en matrise med VBA. I stedet må du bruke en løkke for å tilordne verdiene fra en matrise til en annen.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 til 100) så lengeDim Arr2 (1 til 100) så lengeDim i As Long'Lag matrise1For i = 1 til 100Arr1 (i) = iNeste jeg'CopyArray1 til Array2For i = 1 til 100Arr2 (i) = Arr1 (i)Neste jegMsgBox Arr2 (74)Slutt Sub

Transponere

Det er ingen innebygd VBA-funksjon som lar deg transponere en matrise. Imidlertid har vi skrevet en funksjon for å transponere et 2D -array. Les artikkelen for å lære mer.

VBA -programmering | Kodegenerator fungerer for deg!

Funksjon Return Array

Et vanlig spørsmål VBA -utviklere har, er hvordan man lager en funksjon som returnerer en matrise. Jeg tror de fleste problemene er løst ved å bruke Variant Arrays. Vi har skrevet en artikkel om emnet: VBA Function Return Array.

Bruke matriser i Access VBA

De fleste Array -eksemplene ovenfor fungerer nøyaktig det samme i Access VBA som de gjør i Excel VBA. Den eneste store forskjellen er at når du ønsker å fylle ut en matrise med Access -data, må du gå gjennom RecordSet -objektet i stedet for Range -objektet.

1234567891011121314151617181920212223

Sub RangeToArrayAccess ()Ved feil Fortsett nesteDim strNames () As StringDim i As LongDim iCount As LongDim dbs som databaseDim rst As RecordsetSett dbs = CurrentDbSett rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Med første.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 til iCount)For i = 1 Til iCountstrNames (i) = rst.Fields ("ClientName").MoveNextNeste jegSlutt medførste lukkSett først = ingentingSett dbs = ingentingSlutt Sub

Array Tutorials
Array Mega-Guide	ja
Få matrisestørrelse
Klar matrise
Filter Array
Transpose Array
Funksjon Return Array
Fjern duplikater