Matrice VBA

În VBA, un Matrice este o singură variabilă care poate conține mai multe valori. Gândiți-vă la o matrice ca o gamă de celule: fiecare celulă poate stoca o valoare. Tablourile pot fi unidimensionale (gândiți-vă la o singură coloană), bidimensionale (gândiți-vă la mai multe rânduri și coloane) sau multidimensionale. Valorile matricei pot fi accesate prin poziția lor (numărul de index) din matrice.

VBA Array Quick Sheet

Matrice

DescriereCodul VBACreaDim arr (1 până la 3) Ca variantă
arr (1) = „unul”
arr (2) = „doi”
arr (3) = „trei”Creați din ExcelDim arr (1 până la 3) Ca variantă
Dim celulă ca gamă, i ca număr întreg
i = LBound (arr)
Pentru fiecare celulă din interval („A1: A3”)
i = i + 1
arr (i) = valoare.celulă
Următoarea celulăCitiți toate articoleleDim i as Long
Pentru i = LBound (arr) To UBound (arr)
MsgBox arr (i)
Apoi euŞtergeȘtergeți arArray to StringDim sName As String
sName = Join (arr, „:”)Măriți dimensiuneaReDim Preserve arr (0 la 100)Setați valoareaarr (1) = 22

Exemple rapide de matrice VBA

Să vedem un exemplu complet înainte de a ne arunca în detalii:

12345678910

Sub ArrayExample ()Dim strNames (1 la 4) ca ȘirstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)Sfârșitul Sub

Aici am creat matricea de șir unidimensional: strNames cu dimensiunea patru (poate conține patru valori) și le-am atribuit cele patru valori. Afișăm a treia valoare într-o casetă de mesaje.

În acest caz, avantajul utilizării unui Array este mic: este necesară o singură declarație variabilă în loc de patru.

Cu toate acestea, să vedem un exemplu care va arăta adevărata putere a unui tablou:

12345678

Sub ArrayExample2 ()Dim strNames (1 la 60000) Ca șirDim i As LongPentru i = 1 până la 60000strNames (i) = Celule (i, 1). ValoareApoi euSfârșitul Sub

Aici am creat o matrice care poate conține 60.000 de valori și am populat rapid matricea din coloana A a unei foi de lucru.

Beneficii de matrice? - Viteza!

S-ar putea să vă gândiți la matrice similare foilor de lucru Excel:

• Fiecare celulă (sau articol dintr-o matrice) poate conține propria sa valoare
• Fiecare celulă (sau articol dintr-o matrice) poate fi accesată prin poziția sa de rând și coloană.
  • Foaie de lucru Ex. cells (1,4) .value = „Rândul 1, coloana 4”
  • Array Ex. arrVar (1,4) = „Rândul 1, coloana 4”

Deci, de ce să te deranjezi cu Arrays? De ce nu citiți și scrieți valori direct în celule din Excel? Un cuvânt: Viteză!

Citirea / Scrierea în celule Excel este un proces lent. Lucrul cu matricele este mult mai rapid!

Creați / declarați o matrice (Dim)

Notă: Tablourile pot avea mai multe „dimensiuni”. Pentru a simplifica lucrurile, vom începe lucrând doar cu tablouri unidimensionale. Mai târziu, în tutorial, vă vom prezenta matricele cu mai multe dimensiuni.

Static Array

Matrici statice sunt tablouri care nu pot schimba dimensiunea. Invers, Matrice dinamice poate schimba dimensiunea. Sunt declarate ușor diferit. În primul rând, să analizăm matricile statice.

Notă: dacă matricea dvs. nu se va schimba în dimensiune, utilizați o matrice statică.

Declararea unei variabile de matrice statice este foarte asemănătoare cu declararea unei variabile obișnuite, cu excepția faptului că trebuie să definiți dimensiunea matricei. Există mai multe moduri diferite de a seta dimensiunea unui tablou.

Puteți declara în mod explicit pozițiile de început și de sfârșit ale unui tablou:

123456789101112

Sub StaticArray1 ()'Creează matrice cu pozițiile 1,2,3,4Dim arrDemo1 (1 la 4) Ca șir'Creează matrice cu poziții 4,5,6,7Dim arrDemo2 (4 până la 7) Cât timp'Creează matrice cu pozițiile 0,1,2,3Dim arrDemo3 (0 până la 3) Cât timpSfârșitul Sub

Sau puteți introduce doar dimensiunea matricei:

123456

Sub StaticArray2 ()'Creează matrice cu pozițiile 0,1,2,3Dim arrDemo1 (3) Ca șirSfârșitul Sub

Important! Observați că, în mod implicit, matricile încep de la poziția 0. Deci Dim arrDemo1 (3) creează o matrice cu pozițiile 0,1,2,3.

Puteți declara Opțiunea Baza 1 în partea de sus a modulului, astfel încât matricea să înceapă în poziția 1:

12345678

Opțiunea Baza 1Sub StaticArray3 ()'Creează matrice cu pozițiile 1,2,3Dim arrDemo1 (3) Ca șirSfârșitul Sub

Cu toate acestea, consider că este mult mai ușor (și mai puțin confuz) să declarăm în mod explicit pozițiile de început și de sfârșit ale matricilor.

V-ați săturat să căutați exemple de cod VBA? Încercați AutoMacro!

Matrice dinamică

Matrice dinamice sunt tablouri a căror dimensiune poate fi modificată (sau a căror dimensiune nu trebuie definită).

Există două moduri de a declara o matrice dinamică.

Matrici de variante

Prima modalitate de a declara o matrice dinamică este prin setarea matricei de tip Variantă.

1	Dim arrVar () Ca variantă

Cu Varianta Array, nu trebuie să definiți dimensiunea matricei. Dimensiunea se va regla automat. Rețineți că matricea începe cu poziția 0 (cu excepția cazului în care adăugați opțiunea Baza 1 în partea de sus a modulului)

12345678910111213

Sub VariantArray ()Dim arrVar () Ca variantă„Definiți valori (dimensiune = 0,1,2,3)arrVar = Array (1, 2, 3, 4)„Schimbați valorile (dimensiune = 0,1,2,3,4)arrVar = Array („1a”, „2a”, „3a”, „4a”, „5a”)„Poziția de ieșire 4 („ 5a ”)MsgBox arrVar (4)Sfârșitul Sub

Matrice dinamice non-variabile

Cu matricele non-variante, trebuie să definiți dimensiunea matricei înainte de a atribui valori matricei. Cu toate acestea, procesul de creare a matricei este ușor diferit:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () Ca șir'Redimensionează matricea cu pozițiile 1,2,3,4ReDim arrDemo1 (1 la 4)Sfârșitul Sub

Mai întâi declarați matricea, similară matricei statice, cu excepția faptului că omiteți dimensiunea matricei:

1	Dim arrDemo1 () Ca șir

Acum, când doriți să setați dimensiunea matricei, utilizați fișierul ReDim comanda pentru a dimensiona matricea:

12	'Redimensionează matricea cu pozițiile 1,2,3,4ReDim arrDemo1 (1 la 4)

ReDim redimensionează matricea. Citiți mai jos pentru diferența dintre ReDim și ReDim Preserve.

ReDim vs. ReDim Preserve

Când utilizați ReDim comanda pentru a șterge toate valorile existente din matrice. În schimb, puteți utiliza ReDim Preserve pentru a păstra valorile matricei:

12	'Redimensionează matricea cu pozițiile 1,2,3,4 (Păstrarea valorilor existente)ReDim Preserve arrDemo1 (1 la 4)

Declararea matricilor simplificate

S-ar putea să vă simțiți copleșiți după ce ați citit totul de mai sus. Pentru a simplifica lucrurile, vom lucra mai ales cu tablouri statice pentru restul articolului.

Setați valorile matricei

Setarea valorilor matricei este foarte ușoară.

Cu un tablou static, trebuie să definiți fiecare poziție a tabloului, una câte una:

12345678

Sub ArrayExample ()Dim strNames (1 la 4) ca ȘirstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Sfârșitul Sub

Cu un Variant Array puteți defini întreaga matrice cu o singură linie (practic numai pentru matricele mici):

123456

Sub ArrayExample_1Line ()Dim strNames () Ca variantăstrNames = Array („Shelly”, „Steve”, „Neema”, „Jose”)Sfârșitul Sub

Dacă încercați să definiți o valoare pentru o locație a matricei care nu există, veți primi o eroare de subscript în afara intervalului:

1	strNames (5) = "Shannon"

În secțiunea „Atribuiți intervalul pentru matrice” de mai jos, vă vom arăta cum să utilizați o buclă pentru a atribui rapid un număr mare de valori matricelor.

Obțineți valoarea matricei

Puteți prelua valorile matricei în același mod. În exemplul de mai jos vom scrie valori matrice în celule:

1234	Interval ("A1"). Valoare = strNames (1)Interval ("A2"). Valoare = strNames (2)Gama ("A3"). Valoare = strNames (3)Interval („A4”). Valoare = strNames (4)

Programare VBA | Generatorul de coduri funcționează pentru dvs.!

Atribuiți gama pentru matrice

Pentru a atribui un Range unui Array puteți utiliza o buclă:

12345678

Sub RangeToArray ()Dim strNames (1 la 60000) Ca șirDim i As LongPentru i = 1 până la 60000strNames (i) = Celule (i, 1). ValoareApoi euSfârșitul Sub

Aceasta va parcurge celulele A1: A60000, atribuind valorile celulei matricei.

Matrice de ieșire la interval

Sau puteți utiliza o buclă pentru a atribui o matrice unui interval:

123	Pentru i = 1 până la 60000Celule (i, 1). Valoare = strNames (i)Apoi eu

Acest lucru va face invers: atribuiți valori matrice celulelor A1: A60000

Matrice 2D / multi-dimensionale

Până acum am lucrat exclusiv cu tablouri unidimensionale (1D). Cu toate acestea, tablourile pot avea până la 32 de dimensiuni.

Gândiți-vă la o matrice 1D, cum ar fi un singur rând sau coloană de celule Excel, o matrice 2D ca o întreagă foaie de lucru Excel cu mai multe rânduri și coloane și o matrice 3D este ca un întreg registru de lucru, conținând mai multe foi fiecare conținând mai multe rânduri și coloane (Tu s-ar putea gândi și la o matrice 3D ca la un cub Rubik).

Exemple de matrice multi-dimensionale

Acum să demonstrăm exemple de lucru cu matrici de diferite dimensiuni.

Programare VBA | Generatorul de coduri funcționează pentru dvs.!

Exemplu de matrice 1D

Această procedură combină exemplele de matrice anterioare într-o singură procedură, demonstrând modul în care ați putea folosi matricele în practică.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 la 60000) Ca șirDim i As Long„Atribuiți valori pentru matricePentru i = 1 până la 60000strNames (i) = Celule (i, 1). ValoareApoi eu„Valorile matricei de ieșire la intervalPentru i = 1 până la 60000Foi de calcul („Ieșire”). Celule (i, 1) .Value = strNames (i)Apoi euSfârșitul Sub

Exemplu de matrice 2D

Această procedură conține un exemplu de matrice 2D:

123456789101112131415161718

Sub ArrayEx_2d ()Dim strNames (1 la 60000, 1 la 10) Ca șirDim i As Long, j As Long„Atribuiți valori pentru matricePentru i = 1 până la 60000Pentru j = 1 până la 10strNames (i, j) = Celule (i, j). ValoareUrmătorul jApoi eu„Valorile matricei de ieșire la intervalPentru i = 1 până la 60000Pentru j = 1 până la 10Foi de calcul („Ieșire”). Celule (i, j) .Value = strNames (i, j)Următorul jApoi euSfârșitul Sub

Exemplu de matrice 3D

Această procedură conține un exemplu de matrice 3D pentru lucrul cu mai multe foi:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Dim strNames (1 la 60000, 1 la 10, 1 la 3) Ca șirDim i As Long, j As Long, k As Long„Atribuiți valori pentru matricePentru k = 1 până la 3Pentru i = 1 până la 60000Pentru j = 1 până la 10strNames (i, j, k) = Foi ("Sheet" & k). Celule (i, j). ValoareUrmătorul jApoi euUrmătorul k„Valorile matricei de ieșire la intervalPentru k = 1 până la 3Pentru i = 1 până la 60000Pentru j = 1 până la 10Foi de calcul („Ieșire” & k). Celule (i, j) .Value = strNames (i, j, k)Următorul jApoi euUrmătorul kSfârșitul Sub

Lungime / dimensiune matrice

Până în prezent, v-am prezentat diferitele tipuri de matrici și v-am învățat cum să declarați matricele și să obțineți / setați valorile matricei. În continuare ne vom concentra pe alte subiecte necesare pentru lucrul cu tablouri.

Programare VBA | Generatorul de coduri funcționează pentru dvs.!

Funcții UBound și LBound

Primul pas pentru a obține lungimea / dimensiunea unui tablou este utilizarea funcțiilor UBound și LBound pentru a obține limitele superioare și inferioare ale tabloului:

123456

Sub UBoundLBound ()Dim strNames (1 până la 4) ca șirMsgBox UBound (strNames)MsgBox LBound (strNames)Sfârșitul Sub

Scăderea celor două (și adăugarea 1) vă va oferi lungimea:

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

Funcția Lungime matrice

Iată o funcție pentru a obține lungimea unui tablou cu o singură dimensiune:

1234567

Funcția publică GetArrLength (ca o variantă) atât de lungăDacă IsEmpty (a) AtunciGetArrLength = 0AltfelGetArrLength = UBound (a) - LBound (a) + 1End IfFuncția de sfârșit

Trebuie să calculați dimensiunea unui tablou 2D? Consultați tutorialul nostru: Calculați dimensiunea matricei.

Buclă prin matrice

Există două modalități de a parcurge o matrice. Primul parcurge numerele întregi corespunzătoare pozițiilor numerice ale tabloului. Dacă cunoașteți dimensiunea matricei, o puteți specifica direct:

12345678910111213

Sub ArrayExample_Loop1 ()Dim strNames (1 până la 4) ca șirDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pentru i = 1 până la 4MsgBox strNames (i)Apoi euSfârșitul Sub

Cu toate acestea, dacă nu cunoașteți dimensiunea matricei (dacă matricea este dinamică), puteți utiliza funcțiile LBound și UBound din secțiunea anterioară:

12345678910111213

Sub ArrayExample_Loop2 ()Dim strNames (1 până la 4) ca șirDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pentru i = LBound (strNames) To UBound (strNames)MsgBox strNames (i)Apoi euSfârșitul Sub

Pentru fiecare buclă de matrice

A doua metodă este cu un Pentru fiecare buclă. Aceasta parcurge fiecare element din matrice:

12345678910111213

Sub ArrayExample_Loop3 ()Dim strNames (1 până la 4) ca șirAfișează elementulstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pentru fiecare articol din strNamesElement MsgBoxUrmătorul articolSfârșitul Sub

Bucla pentru fiecare matrice va funcționa cu matrice multidimensionale, pe lângă matrice unidimensionale.

Programare VBA | Generatorul de coduri funcționează pentru dvs.!

Buclați prin matrice 2D

De asemenea, puteți utiliza funcțiile UBound și LBound pentru a parcurge și o matrice multidimensională. În acest exemplu vom face o buclă printr-o matrice 2D. Observați că funcțiile UBound și LBound vă permit să specificați ce dimensiune a matricei să găsiți limitele superioară și inferioară (1 pentru prima dimensiune, 2 pentru a doua dimensiune).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Dim strNames (1 până la 4, 1 până la 2) Ca șirDim 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"Pentru j = LBound (strNames, 2) To UBound (strNames, 2)Pentru i = LBound (strNames, 1) To UBound (strNames, 1)MsgBox strNames (i, j)Apoi euUrmătorul jSfârșitul Sub

Alte sarcini de matrice

Clear Array

Pentru a șterge o întreagă matrice, utilizați Instrucțiunea de ștergere:

1	Ștergeți strNames

Exemplu de utilizare:

12345678910

Sub ArrayExample ()Dim strNames (1 la 4) ca ȘirstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Ștergeți strNamesSfârșitul Sub

Alternativ, puteți redimensiona matricea pentru a o redimensiona, ștergând o parte din matrice:

1	ReDim strNames (1 la 2)

Aceasta redimensionează matricea la dimensiunea 2, ștergând pozițiile 3 și 4.

Contele Array

Puteți număra numărul de poziții din fiecare dimensiune a unui tablou utilizând funcțiile UBound și LBound (discutate mai sus).

Puteți număra, de asemenea, numărul de articole introduse (sau elemente care îndeplinesc anumite criterii) parcurgerea prin matrice.

Acest exemplu va face o buclă printr-o matrice de obiecte și va număra numărul de șiruri ne-goale găsite în matrice:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNames (1 până la 4) ca șirDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"Pentru i = LBound (strNames) To UBound (strNames)Dacă strNames (i) "" Atuncin = n + 1End IfApoi euMsgBox n & „s-au găsit valori necompletate”.Sfârșitul Sub

Programare VBA | Generatorul de coduri funcționează pentru dvs.!

Eliminați duplicatele

La un moment dat, poate doriți să eliminați duplicatele dintr-o matrice. Din păcate, VBA nu are o funcție încorporată pentru a face acest lucru. Cu toate acestea, am scris o funcție pentru a elimina duplicatele dintr-o matrice (este prea lung să fie inclusă în acest tutorial, dar accesați linkul pentru a afla mai multe).

Filtru

Funcția de filtrare VBA vă permite să filtrați o matrice. Face acest lucru prin crearea unei matrice noi cu numai valorile filtrate. Mai jos este un exemplu rapid, dar asigurați-vă că citiți articolul pentru mai multe exemple pentru diferite nevoi.

1234567891011121314

Sub Filter_Match ()„Definiți matriceaDim strNames As VariantstrNames = Array („Steve Smith”, „Shannon Smith”, „Ryan Johnson”)'Filtru matriceDim strSubNames As VariantstrSubNames = Filtru (strNames, "Smith")'Numără matricea filtratăMsgBox "Found" & UBound (strSubNames) - LBound (strSubNames) + 1 & "names".Sfârșitul Sub

Funcția IsArray

Puteți testa dacă o variabilă este o matrice utilizând funcția IsArray:

123456789101112

Sub IsArrayEx ()'Creează matrice cu pozițiile 1,2,3Dim arrDemo1 (3) Ca șir'Creează o variabilă de șir regulatDim str As StringMsgBox IsArray (arrDemo1)MsgBox IsArray (str)Sfârșitul Sub

Alătură-te Array

Puteți „alătura” rapid o întreagă matrice împreună cu funcția de alăturare:

123456789101112

Sub Array_Join ()Dim strNames (1 până la 4) ca șirDim joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Join (strNames, ",")MsgBox joinNamesSfârșitul Sub

Programare VBA | Generatorul de coduri funcționează pentru dvs.!

Divizați șirul în matrice

Funcția VBA Split va împărți un șir de text într-o matrice care conține valori din șirul original. Să vedem un exemplu:

123456789

Sub Array_Split ()Dim Nume () Ca șirDim s-a alăturat Nume ca șirjoinNames = "Shelly, Steve, Nema, Jose"Nume = Split (joinNames, ",")Numele MsgBox (1)Sfârșitul Sub

Aici împărțim acest șir de text „Shelly, Steve, Nema, Jose” într-o matrice (dimensiunea 4) folosind delimitatorul virgulă (, ”).

Const Array

Un Array nu poti să fie declarat ca o constantă în VBA. Cu toate acestea, puteți rezolva acest lucru creând o funcție pe care să o utilizați ca matrice:

123456789

„Definiți ConstantArrayFuncția ConstantArray ()ConstantArray = Array (4, 12, 21, 100, 5)Funcția de sfârșit„Recuperați valoarea ConstantArraySub RetrieveValues ​​()MsgBox ConstantArray (3)Sfârșitul Sub

Copiați matricea

Nu există un mod încorporat de a copia un Array folosind VBA. În schimb, va trebui să utilizați o buclă pentru a atribui valorile dintr-o matrice în alta.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 la 100) Cât timpDim Arr2 (1 la 100) Cât timpDim i As Long„Creați matrice1Pentru i = 1 până la 100Arr1 (i) = iApoi eu'CopyArray1 în Array2Pentru i = 1 până la 100Arr2 (i) = Arr1 (i)Apoi euMsgBox Arr2 (74)Sfârșitul Sub

Transpune

Nu există nicio funcție VBA încorporată care să vă permită să transpuneți o matrice. Cu toate acestea, am scris o funcție pentru a transpune un tablou 2D. Citiți articolul pentru a afla mai multe.

Programare VBA | Generatorul de coduri funcționează pentru dvs.!

Funcție Return Return

O întrebare obișnuită pe care o au dezvoltatorii VBA este cum se creează o funcție care returnează o matrice. Cred că majoritatea dificultăților sunt rezolvate folosind tablouri de variante. Am scris un articol pe tema: VBA Function Return Array.

Utilizarea matricilor în Access VBA

Majoritatea exemplelor Array de mai sus funcționează exact la fel în Access VBA ca în Excel VBA. Singura diferență majoră este că, atunci când doriți să completați o matrice folosind date Access, va trebui să parcurgeți obiectul RecordSet, mai degrabă decât obiectul Range.

1234567891011121314151617181920212223

Sub RangeToArrayAccess ()La eroare Reluați în continuareDim strNames () Ca șirDim i As LongDim iCount As LongDim dbs ca bază de dateDim rst As RecordsetSetați dbs = CurrentDbSetați rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Cu prima.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 To iCount)Pentru i = 1 To iCountstrNames (i) = rst.Fields ("ClientName").MoveNextApoi euSe termina cumai întâi ÎnchideSet rst = NimicSet dbs = NothingSfârșitul Sub

Tutoriale pentru matrice
Mega-ghid Array	da
Obțineți dimensiunea matricei
Clear Array
Filtru matrice
Transpune matricea
Funcție Return Return
Eliminați duplicatele