VBA -matriser

I VBA, en Array är en enda variabel som kan hålla flera värden. Tänk på en array som ett cellintervall: varje cell kan lagra ett värde. Matriser kan vara endimensionella (tänk på en enda kolumn), tvådimensionell (tänk på flera rader och kolumner) eller flerdimensionella. Arrayvärden kan nås med deras position (indexnummer) i matrisen.

VBA Array Quick Sheet

Matriser

BeskrivningVBA -kodSkapaDim arr (1 till 3) som variant
arr (1) = “en”
arr (2) = “två”
arr (3) = “tre”Skapa från ExcelDim arr (1 till 3) som variant
Dim cell As Range, i As Integer
i = LBound (arr)
För varje cell i intervallet ("A1: A3")
i = i + 1
arr (i) = cell.värde
Nästa cellLäs alla artiklarDim i som Long
För i = LBound (arr) Till UBound (arr)
MsgBox arr (i)
Nästa iRaderaRadera arrArray till strängDim sName som sträng
sName = Gå med (arr, “:”)Öka storlekReDim Preserve arr (0 till 100)Satt värdearr (1) = 22

VBA Array Snabba exempel

Låt oss titta på ett fullständigt exempel innan vi går in på detaljer:

12345678910

Sub ArrayExample ()Dim strNames (1 till 4) som strängstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)Avsluta Sub

Här har vi skapat den endimensionella strängmatrisen: strNames med storlek fyra (kan hålla fyra värden) och tilldelat de fyra värdena. Senast visar vi det tredje värdet i en meddelanderuta.

I det här fallet är fördelen med att använda en matris liten: endast en variabel deklaration krävs istället för fyra.

Låt oss dock titta på ett exempel som visar den sanna kraften hos en array:

12345678

Delmatrisexempel2 ()Dim strNames (1 till 60000) som strängDim i As LongFör i = 1 Till 60000strNames (i) = Celler (i, 1) .VärdeNästa iAvsluta Sub

Här har vi skapat en matris som rymmer 60 000 värden och vi har snabbt fyllt i matrisen från kolumn A i ett kalkylblad.

Array fördelar? - Fart!

Du kanske tänker på matriser som liknar Excel -kalkylblad:

• Varje cell (eller objekt i en array) kan innehålla sitt eget värde
• Varje cell (eller objekt i en array) kan nås med dess rad- och kolumnposition.
  • Arbetsblad Ex. celler (1,4) .value = “Rad 1, kolumn 4”
  • Array Ex. arrVar (1,4) = “Rad 1, kolumn 4”

Så varför bry sig om Arrays? Varför inte bara läsa och skriva värden direkt till celler i Excel? Ett ord: Fart!

Att läsa / skriva till Excel -celler är en långsam process. Att arbeta med matriser är mycket snabbare!

Skapa / deklarera en matris (dim)

Obs! Matriser kan ha flera "dimensioner". För att hålla sakerna enkla börjar vi med att bara arbeta med endimensionella matriser. Senare i handledningen kommer vi att presentera dig för flerdimensionella matriser.

Statisk matris

Statiska matriser är matriser som inte kan ändra storlek. Omvänt, Dynamiska matriser kan ändra storlek. De deklareras något annorlunda. Låt oss först titta på statiska matriser.

Obs! Om din array inte ändras i storlek, använd en statisk array.

Att deklarera en statisk matrisvariabel liknar mycket att deklarera en vanlig variabel, förutom att du måste definiera matrisens storlek. Det finns flera olika sätt att ställa in storleken på en array.

Du kan uttryckligen deklarera start- och slutpositionerna för en array:

123456789101112

Sub StaticArray1 ()'Skapar array med positioner 1,2,3,4Dim arrDemo1 (1 till 4) som sträng'Skapar array med positioner 4,5,6,7Dim arrDemo2 (4 till 7) så länge'Skapar array med positioner 0,1,2,3Dim arrDemo3 (0 till 3) så längeAvsluta Sub

Eller så kan du bara ange matrisstorleken:

123456

Sub StaticArray2 ()'Skapar array med positioner 0,1,2,3Dim arrDemo1 (3) Som strängAvsluta Sub

Viktig! Lägg märke till att som standard börjar matriser på position 0. Så Dim arrDemo1 (3) skapar en array med positioner 0,1,2,3.

Du kan förklara Alternativbas 1 högst upp i modulen så att arrayen börjar på position 1 istället:

12345678

Alternativbas 1Sub StaticArray3 ()'Skapar array med positioner 1,2,3Dim arrDemo1 (3) Som strängAvsluta Sub

Jag tycker dock att det är mycket lättare (och mindre förvirrande) att bara uttryckligen deklarera start- och slutpositioner för matriser.

Trött på att söka efter exempel på VBA -kod? Prova AutoMacro!

Dynamisk matris

Dynamiska matriser är matriser vars storlek kan ändras (eller vars storlek inte behöver definieras).

Det finns två sätt att deklarera en dynamisk matris.

Varianter

Det första sättet att deklarera en dynamisk matris är genom att ställa in matrisen för att skriva Variant.

1	Dim arrVar () Som variant

Med en Variant Array, du behöver inte definiera matrisstorleken. Storleken justeras automatiskt. Kom bara ihåg att matrisen börjar med position 0 (om du inte lägger till alternativbas 1 högst upp i modulen)

12345678910111213

Sub VariantArray ()Dim arrVar () Som variant'Definiera värden (storlek = 0,1,2,3)arrVar = Array (1, 2, 3, 4)'Ändra värden (storlek = 0,1,2,3,4)arrVar = Array ("1a", "2a", "3a", "4a", "5a")'Utgångsläge 4 ("5a")MsgBox arrVar (4)Avsluta Sub

Icke-variant dynamiska matriser

Med icke-variantarrayer måste du definiera matrisstorleken innan du tilldelar värden till matrisen. Processen för att skapa matrisen är dock något annorlunda:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () Som sträng'Ändrar storlek på matris med positioner 1,2,3,4ReDim arrDemo1 (1 till 4)Avsluta Sub

Först deklarerar du matrisen, liknande den statiska matrisen, förutom att du utelämnar matrisstorleken:

1	Dim arrDemo1 () Som sträng

Nu när du vill ställa in matrisstorleken använder du ReDim kommando för att storleken på matrisen:

12	'Ändrar storlek på array med positioner 1,2,3,4ReDim arrDemo1 (1 till 4)

ReDim ändrar storlek på matrisen. Läs nedan för skillnaden mellan ReDim och ReDim Preserve.

ReDim vs ReDim Preserve

När du använder ReDim kommando du rensar alla befintliga värden från matrisen. Istället kan du använda ReDim Preserve för att bevara matrisvärden:

12	'Ändrar storlek på array med positioner 1,2,3,4 (bevara befintliga värden)ReDim Preserve arrDemo1 (1 till 4)

Förklarar matriser förenklade

Du kan känna dig överväldigad efter att ha läst allt ovan. För att hålla saker enkla kommer vi mestadels att arbeta med statiska matriser för resten av artikeln.

Ange matrisvärden

Det är väldigt enkelt att ställa in arrayvärden.

Med en statisk matris måste du definiera varje position i matrisen, en i taget:

12345678

Sub ArrayExample ()Dim strNames (1 till 4) som strängstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Avsluta Sub

Med en Variant Array kan du definiera hela arrayen med en rad (endast praktiskt för små matriser):

123456

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

Om du försöker definiera ett värde för en matrisplats som inte existerar, får du ett felmeddelande för abonnemang utanför intervallet:

1	strNames (5) = "Shannon"

I avsnittet "Tilldela område till matris" nedan visar vi dig hur du använder en loop för att snabbt tilldela stora värden till matriser.

Få Array Value

Du kan hämta matrisvärden på samma sätt. I exemplet nedan skriver vi matrisvärden till celler:

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

VBA -programmering | Kodgenerator fungerar för dig!

Tilldela område till Array

För att tilldela ett område till en matris kan du använda en loop:

12345678

Sub RangeToArray ()Dim strNames (1 till 60000) som strängDim i As LongFör i = 1 Till 60000strNames (i) = Celler (i, 1) .VärdeNästa iAvsluta Sub

Detta går igenom cellerna A1: A60000 och tilldelar cellvärdena till matrisen.

Utmatningsmatris till område

Eller så kan du använda en loop för att tilldela en array till ett intervall:

123	För i = 1 Till 60000Celler (i, 1) .Value = strNames (i)Nästa i

Detta gör det omvända: tilldela arrayvärden till cellerna A1: A60000

2D / flerdimensionella matriser

Hittills har vi arbetat uteslutande med endimensionella (1D) matriser. Arrayer kan dock ha upp till 32 dimensioner.

Tänk på en 1D -array som en enda rad eller kolumn med Excel -celler, en 2D -array som ett helt Excel -kalkylblad med flera rader och kolumner, och en 3D -array är som en hel arbetsbok, som innehåller flera ark som alla innehåller flera rader och kolumner (Du kan också tänka mig en 3D -array som en Rubiks kub).

Flerdimensionella matrisexempel

Låt oss nu visa exempel på att arbeta med matriser av olika dimensioner.

VBA -programmering | Kodgenerator fungerar för dig!

1D Array Exempel

Den här proceduren kombinerar de tidigare matrisexemplen till ett förfarande som visar hur du kan använda matriser i praktiken.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 till 60000) som strängDim i As Long'Tilldela värden till ArrayFör i = 1 Till 60000strNames (i) = Celler (i, 1) .VärdeNästa i'Utmatningsmatrisvärden till intervallFör i = 1 Till 60000Sheets ("Output"). Celler (i, 1) .Value = strNames (i)Nästa iAvsluta Sub

2D Array Exempel

Det här förfarandet innehåller ett exempel på en 2D -array:

123456789101112131415161718

Sub ArrayEx_2d ()Dim strNames (1 till 60000, 1 till 10) som strängDim i As Long, j As Long'Tilldela värden till ArrayFör i = 1 Till 60000För j = 1 till 10strNames (i, j) = Celler (i, j) .VärdeNästa jNästa i'Utmatningsmatrisvärden till intervallFör i = 1 Till 60000För j = 1 till 10Sheets ("Output"). Celler (i, j) .Value = strNames (i, j)Nästa jNästa iAvsluta Sub

3D Array Exempel

Den här proceduren innehåller ett exempel på en 3D -array för att arbeta med flera ark:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Dim strNames (1 till 60000, 1 till 10, 1 till 3) som strängDim i As Long, j As Long, k As Long'Tilldela värden till ArrayFör k = 1 till 3För i = 1 Till 60000För j = 1 till 10strNames (i, j, k) = Sheets ("Sheet" & k). Celler (i, j) .VärdeNästa jNästa iNästa k'Utmatningsmatrisvärden till intervallFör k = 1 till 3För i = 1 Till 60000För j = 1 till 10Sheets ("Output" & k) .Cells (i, j) .Value = strNames (i, j, k)Nästa jNästa iNästa kAvsluta Sub

Array längd / storlek

Hittills har vi introducerat dig till de olika typerna av matriser och lärt dig hur du deklarerar matriserna och får/ställer in arrayvärden. Därefter fokuserar vi på andra nödvändiga ämnen för att arbeta med matriser.

VBA -programmering | Kodgenerator fungerar för dig!

UBound och LBound funktioner

Det första steget för att få längden / storleken på en array använder UBound- och LBound -funktionerna för att få de övre och nedre gränserna för arrayen:

123456

Sub UBoundLBound ()Dim strNames (1 till 4) som strängMsgBox UBound (strNames)MsgBox LBound (strNames)Avsluta Sub

Att subtrahera de två (och lägga till 1) ger dig längden:

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

Arraylängd Funktion

Här är en funktion för att få en endimensionell matris längd:

1234567

Public Function GetArrLength (a As Variant) Så längeIf IsEmpty (a) DåGetArrLength = 0AnnanGetArrLength = UBound (a) - LBound (a) + 1Avsluta omAvsluta funktion

Behöver du beräkna storleken på en 2D -array? Kolla in vår handledning: Beräkna matrisstorlek.

Loop Through Array

Det finns två sätt att gå igenom en array. De första slingorna genom heltalen som motsvarar matrisens antal positioner. Om du känner till matrisstorleken kan du ange den direkt:

12345678910111213

Sub ArrayExample_Loop1 ()Dim strNames (1 till 4) som strängDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"För i = 1 till 4MsgBox strNames (i)Nästa iAvsluta Sub

Men om du inte känner till matrisstorleken (om matrisen är dynamisk) kan du använda funktionerna LBound och UBound från föregående avsnitt:

12345678910111213

Sub ArrayExample_Loop2 ()Dim strNames (1 till 4) som strängDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"För i = LBound (strNames) To UBound (strNames)MsgBox strNames (i)Nästa iAvsluta Sub

För varje Array Loop

Den andra metoden är med en för varje loop. Detta går igenom varje objekt i matrisen:

12345678910111213

Sub ArrayExample_Loop3 ()Dim strNames (1 till 4) som strängDim objektstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"För varje artikel i strNamesMsgBox ArtikelNästa artikelAvsluta Sub

For Each Array Loop kommer att fungera med flerdimensionella matriser utöver endimensionella matriser.

VBA -programmering | Kodgenerator fungerar för dig!

Loop Through 2D Array

Du kan också använda UBound- och LBound-funktionerna för att också gå igenom en flerdimensionell array. I det här exemplet går vi igenom en 2D -array. Observera att UBound- och LBound -funktionerna låter dig ange vilken dimension i arrayen som ska hitta de övre och nedre gränserna (1 för första dimensionen, 2 för andra dimensionen).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Dim strNames (1 till 4, 1 till 2) som strängDim 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"För j = LBound (strNames, 2) Till UBound (strNames, 2)För i = LBound (strNames, 1) Till UBound (strNames, 1)MsgBox strNames (i, j)Nästa iNästa jAvsluta Sub

Andra matrisuppgifter

Tydlig matris

Om du vill rensa en hel array använder du Radera uttalande:

1	Radera strNames

Användningsexempel:

12345678910

Sub ArrayExample ()Dim strNames (1 till 4) som strängstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Radera strNamesAvsluta Sub

Alternativt kan du också ReDim arrayen för att ändra storlek på den, rensa en del av arrayen:

1	ReDim strNames (1 till 2)

Detta ändrar storleken på matrisen till storlek 2 och tar bort positionerna 3 och 4.

Count Array

Du kan räkna antalet positioner i varje dimension av en array med hjälp av UBound- och LBound -funktionerna (diskuterade ovan).

Du kan också räkna antalet inmatade objekt (eller objekt som uppfyller vissa kriterier) genom att gå igenom matrisen.

Det här exemplet går igenom en uppsättning objekt och räknar antalet icke-tomma strängar som finns i matrisen:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNames (1 till 4) som strängDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"För i = LBound (strNames) To UBound (strNames)Om strNames (i) "" Dån = n + 1Avsluta omNästa iMsgBox n & "icke-tomma värden hittades."Avsluta Sub

VBA -programmering | Kodgenerator fungerar för dig!

Ta bort dubbletter

Någon gång kanske du vill ta bort dubbletter från en matris. Tyvärr har VBA inte en inbyggd funktion för att göra detta. Vi har dock skrivit en funktion för att ta bort dubbletter från en matris (det är för långt att inkludera i den här självstudien, men besök länken för att lära dig mer).

Filtrera

Med VBA -filterfunktionen kan du filtrera en matris. Det gör det genom att skapa en ny array med endast de filtrerade värdena. Nedan är ett snabbt exempel, men se till att läsa artikeln för fler exempel för olika behov.

1234567891011121314

Sub Filter_Match ()'Definiera ArrayDim strNames Som variantstrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Filter ArrayDim strSubNames som variantstrSubNames = Filter (strNames, "Smith")'Count Filtered ArrayMsgBox "Found" & UBound (strSubNames) - LBound (strSubNames) + 1 & "names."Avsluta Sub

IsArray -funktion

Du kan testa om en variabel är en array med funktionen IsArray:

123456789101112

Sub IsArrayEx ()'Skapar array med positioner 1,2,3Dim arrDemo1 (3) Som sträng'Skapar en vanlig strängvariabelDim str As StringMsgBox IsArray (arrDemo1)MsgBox IsArray (str)Avsluta Sub

Gå med i Array

Du kan snabbt "gå med" i en hel grupp tillsammans med Join -funktionen:

123456789101112

Sub Array_Join ()Dim strNames (1 till 4) som strängDim joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Join (strNames, ",")MsgBox joinNamesAvsluta Sub

VBA -programmering | Kodgenerator fungerar för dig!

Dela sträng i matris

VBA -delningsfunktionen delar upp en textsträng i en matris som innehåller värden från den ursprungliga strängen. Låt oss titta på ett exempel:

123456789

Sub Array_Split ()Dim Names () Som strängDim gick medNames As StringjoinedNames = "Shelly, Steve, Nema, Jose"Names = Split (joinedNames, ",")MsgBox -namn (1)Avsluta Sub

Här delar vi upp den här textsträngen "Shelly, Steve, Nema, Jose" i en matris (storlek 4) med hjälp av en kommaavgränsare (, ").

Const Array

En matris kan inte förklaras som en konstant i VBA. Du kan dock kringgå detta genom att skapa en funktion som ska användas som en matris:

123456789

'Definiera ConstantArrayFunktion ConstantArray ()ConstantArray = Array (4, 12, 21, 100, 5)Avsluta funktion'Retrive ConstantArray ValueSub RetrieveValues ​​()MsgBox ConstantArray (3)Avsluta Sub

Copy Array

Det finns inget inbyggt sätt att kopiera en matris med VBA. I stället måste du använda en loop för att tilldela värdena från en array till en annan.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 till 100) så längeDim Arr2 (1 till 100) så längeDim i As Long'Skapa matris 1För i = 1 till 100Arr1 (i) = iNästa i'CopyArray1 till Array2För i = 1 till 100Arr2 (i) = Arr1 (i)Nästa iMsgBox Arr2 (74)Avsluta Sub

Transponera

Det finns ingen inbyggd VBA-funktion som gör att du kan transponera en array. Vi har dock skrivit en funktion för att transponera en 2D -matris. Läs artikeln för att lära dig mer.

VBA -programmering | Kodgenerator fungerar för dig!

Funktion Return Array

En vanlig fråga som VBA -utvecklare har är hur man skapar en funktion som returnerar en array. Jag tror att de flesta svårigheterna är lösta med hjälp av Variant Arrays. Vi har skrivit en artikel om ämnet: VBA Function Return Array.

Använda matriser i Access VBA

De flesta Array -exemplen ovan fungerar exakt samma i Access VBA som de gör i Excel VBA. Den stora skillnaden är att när du vill fylla i en matris med Access -data måste du gå igenom RecordSet -objektet snarare än Range -objektet.

1234567891011121314151617181920212223

Sub RangeToArrayAccess ()Vid fel Återuppta nästaDim strNames () Som strängDim i As LongDim iCount As LongDim dbs som databasDim först som rekorduppsättningStäll in dbs = CurrentDbAnge rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Med första.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 till iCount)För i = 1 Till iCountstrNames (i) = rst.Fields ("ClientName").MoveNextNästa iSluta medförsta stängaAnge först = ingentingStäll in dbs = ingentingAvsluta Sub

Array handledning
Array Mega-Guide	ja
Få matrisstorlek
Tydlig matris
Filtermatris
Transpose Array
Funktion Return Array
Ta bort dubbletter