O colecție este un obiect care deține un număr de articole similare. Acestea pot fi accesate și manipulate cu ușurință, chiar dacă există un număr mare de articole în colecție.
Există deja colecții încorporate cu Excel VBA. Un exemplu este colecția Foi. Pentru fiecare foaie de lucru dintr-un registru de lucru, există un articol în colecția Foi.
Colecțiile încorporate au la dispoziție mult mai multe proprietăți și metode, dar acestea nu sunt disponibile în propriile colecții pe care le creați.
De exemplu, puteți utiliza colecția pentru a obține informații despre o anumită foaie de lucru. De exemplu, puteți vedea numele foii de lucru și, de asemenea, dacă este vizibilă sau nu. Utilizând o buclă pentru fiecare, puteți itera prin fiecare foaie de lucru din colecție.
| 1234567 | Sub TestWorksheets ()Dim Sh Ca foaie de lucruPentru fiecare Sh In FoiMsgBox Sh.NameMsgBox Sh.VizibilUrmătorul ShSfârșitul Sub |
De asemenea, puteți adresa o anumită foaie de lucru din colecție utilizând valoarea indexului sau numele real al foii de lucru:
| 12 | MsgBox Sheets (1) .NumeFoi de calcul MsgBox („Sheet1”). Nume |
Pe măsură ce foile de lucru sunt adăugate sau șterse, colecția Sheets crește sau se micșorează.
Rețineți că, cu colecțiile VBA, numărul de index începe cu 1 nu cu 0
Colecții versus matrice
Matricile și colecțiile sunt similare în funcțiile lor, în sensul că sunt ambele metodologii care permit stocarea unei cantități mari de date, care poate fi apoi ușor de referințat folosind codul. Cu toate acestea, au o serie de diferențe în modul în care operează:
- Tablourile sunt multidimensionale, în timp ce colecțiile sunt doar o singură dimensiune. Puteți dimensiona o matrice cu mai multe dimensiuni, de ex.
| 1 | Dim MyArray (10, 2) ca șir |
Aceasta creează o matrice de 10 rânduri cu 2 coloane, aproape ca o foaie de lucru. O colecție este efectiv o singură coloană. Matricea este utilă dacă trebuie să stocați o serie de articole de date care se raportează între ele, de ex. numele și adresa. Numele ar fi în prima dimensiune a matricei și adresa în a doua dimensiune.
- Când completați matricea, aveți nevoie de o linie separată de cod pentru a pune o valoare în fiecare element al matricei. Dacă ați avea un tablou bidimensional, ați avea nevoie de fapt de 2 linii de cod - o linie pentru a aborda prima coloană și o linie pentru a adresa a doua coloană. Cu obiectul Colecție, pur și simplu utilizați metoda Adăugare astfel încât noul element să fie adăugat în colecție și valoarea indexului să fie ajustată automat pentru a se potrivi.
- Dacă trebuie să ștergeți un element de date, atunci este mai complicat în matrice. Puteți seta valorile unui element la o valoare necompletată, dar elementul în sine există încă în cadrul matricei. Dacă utilizați o buclă For Next pentru a itera prin matrice, bucla va returna o valoare necompletată, care va necesita codificare pentru a vă asigura că valoarea necompletată este ignorată. Într-o colecție utilizați metodele Adăugare sau Eliminare, iar toate indexările și redimensionarea sunt automat îngrijite. Elementul care a fost eliminat dispare complet. Tablourile sunt utile pentru o dimensiune fixă a datelor, dar colecțiile sunt mai bune pentru cazurile în care cantitatea de date este susceptibilă să se schimbe.
- Colecțiile sunt numai în citire, în timp ce valorile matricei pot fi modificate folosind VBA. Cu o colecție, ar trebui să eliminați mai întâi valoarea care trebuie modificată și apoi să adăugați noua valoare modificată.
- Într-o matrice, puteți utiliza un singur tip de date numai pentru elementele care sunt setate atunci când dimensionați matricea. Cu toate acestea, în matrice puteți utiliza tipuri de date personalizate pe care le-ați proiectat personal. Ați putea avea o structură de matrice foarte complicată folosind un tip de date personalizat care, la rândul său, are mai multe tipuri de date personalizate sub acesta. Într-o colecție, puteți adăuga tipuri de date de utilizare pentru fiecare articol. Puteți avea o valoare numerică, o dată sau un șir - obiectul de colectare va lua orice tip de date. Dacă ați încerca să puneți o valoare șir într-o matrice care a fost dimensionată ca numerică, aceasta va produce un mesaj de eroare.
- Colecțiile sunt, în general, mai ușor de utilizat decât matricele. În termeni de codificare, atunci când creați un obiect de colecție, acesta are doar două metode (Adăugare și eliminare) și două proprietăți (Număr și element), astfel încât obiectul nu este nicidecum complicat de programat.
- Colecțiile pot utiliza tastele pentru localizarea datelor. Matricele nu au această funcție și necesită cod de buclă pentru a itera prin matrice pentru a găsi valori specifice.
- Dimensiunea unui tablou trebuie definită atunci când este creată prima dată. Trebuie să aveți o idee despre cât de multe date va stoca. Dacă trebuie să măriți dimensiunea tabloului, puteți utiliza „ReDim” pentru a-l redimensiona, dar trebuie să utilizați cuvântul cheie „Conservare” dacă nu doriți să pierdeți datele deja păstrate în tablou. Nu este necesară definirea unei dimensiuni a colecției. Pur și simplu crește și se micșorează automat pe măsură ce elementele sunt adăugate sau eliminate.
Scopul unui obiect de colecție
În ceea ce privește domeniul de aplicare, obiectul de colecție este disponibil numai în timp ce registrul de lucru este deschis. Nu se salvează când este salvat registrul de lucru. Dacă registrul de lucru este redeschis, atunci colecția trebuie recreată folosind codul VBA.
Dacă doriți ca colecția dvs. să fie disponibilă pentru tot codul din modulul de cod, atunci trebuie să declarați obiectul de colecție în secțiunea Declarați din partea de sus a ferestrei modulului
Acest lucru vă va asigura că tot codul dvs. din cadrul acelui modul poate accesa colecția. Dacă doriți ca orice modul din registrul dvs. de lucru să acceseze colecția, atunci definiți-l ca un obiect global
| 1 | MyCollection globală ca colecție nouă |
Crearea unei colecții, adăugarea articolelor și accesarea articolelor
Un obiect de colecție simplu poate fi creat în VBA folosind următorul cod:
| 123456 | Sub CreateCollection ()Dim MyCollection ca colecție nouăMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Sfârșitul Sub |
Codul dimensionează un obiect nou numit „Colecția mea” și apoi următoarele linii de cod folosesc metoda Adăugare pentru a adăuga 3 valori noi.
Apoi puteți utiliza codul pentru a itera prin colecția dvs. pentru a accesa valorile
| 123 | Pentru fiecare articol din colecția meaElement MsgBoxUrmătorul articol |
De asemenea, puteți itera prin colecția dvs. utilizând un buclă pentru următoarea buclă:
| 123 | Pentru n = 1 To MyCollection.CountMsgBox MyCollection (n)Următorul n |
Codul obține dimensiunea colecției utilizând proprietatea Count și apoi folosește aceasta începând cu o valoare 1 pentru a indexa fiecare element
Bucla pentru fiecare buclă este mai rapidă decât bucla pentru următorul, dar funcționează doar într-o singură direcție (de la index mic la mare). Bucla următoare are avantajul că puteți utiliza o direcție diferită (de la index mare la scăzut) și puteți utiliza și metoda Step pentru a modifica incrementul. Acest lucru este util atunci când doriți să ștergeți mai multe articole, deoarece va trebui să rulați ștergerea de la sfârșitul colecției până la început, deoarece indexul se va schimba odată cu ștergerea.
Metoda Adăugare într-o colecție are 3 parametri opționali - Cheie, Înainte și După
Puteți utiliza parametrii „Înainte” și „După” pentru a defini poziția noului articol față de celelalte deja din colecție
Acest lucru se face prin specificarea numărului de index la care doriți să fie noul articol.
| 123456 | Sub CreateCollection ()Dim MyCollection ca colecție nouăMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"Sfârșitul Sub |
În acest exemplu, „Item2” a fost specificat pentru a fi adăugat înainte de primul articol indexat din colecție (care este „Item1”). Când iterați prin această colecție, „Item2” va apărea în primul rând, urmat de „Item1” și „Item3”
Când specificați un parametru „Înainte” sau „După”, valoarea indexului este ajustată automat în cadrul colecției, astfel încât „Item2” să devină valoarea indexului 1 și „Item1” să fie mutat la o valoare index a 2
De asemenea, puteți utiliza parametrul „Cheie” pentru a adăuga o valoare de referință pe care o puteți utiliza pentru a identifica elementul de colecție. Rețineți că o valoare cheie trebuie să fie un șir și trebuie să fie unică în cadrul colecției.
| 1234567 | Sub CreateCollection ()Dim MyCollection ca colecție nouăMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"MsgBox MyCollection („MyKey”)Sfârșitul Sub |
„Item2” a primit o valoare „Key” pentru „MyKey”, astfel încât să puteți face referire la acel element folosind valoarea „MyKey” în locul numărului de index (2)
Rețineți că valoarea „Cheie” trebuie să fie o valoare șir. Nu poate fi alt tip de date. Rețineți că colecția este numai în citire și nu puteți actualiza valoarea cheii după ce a fost setată. De asemenea, nu puteți verifica dacă există o valoare cheie pentru un anumit element din colecție sau nu puteți vizualiza valoarea cheii care este un pic dezavantaj.
Parametrul „Cheie” are avantajul suplimentar de a face codul mai ușor de citit, mai ales dacă este predat unui coleg pentru a-l sprijini și nu trebuie să parcurgeți întreaga colecție pentru a găsi acea valoare. Imaginați-vă dacă ați avea o colecție de 10.000 de articole cât de dificil ar fi să faceți referire la un anumit articol!
Eliminarea unui articol dintr-o colecție
Puteți utiliza metoda „Eliminați” pentru a șterge articole din colecția dvs.
| 1 | MyCollection.Remove (2) |
Din păcate, nu este ușor dacă colecția are un număr mare de articole pentru a stabili indexul articolului pe care doriți să îl ștergeți. Aici parametrul „Cheie” este util atunci când colecția este creată
| 1 | MyCollection.Remove („MyKey”) |
Când un element este eliminat dintr-o colecție, valorile indexului sunt resetate automat până la final prin colecție. Aici parametrul „Cheie” este atât de util atunci când ștergeți mai multe elemente simultan. De exemplu, ați putea șterge indexul articolului 105 și instantaneu indexul articolului 106 devine indexul 105 și tot ceea ce este deasupra acestui element are valoarea indexului mutat în jos. Dacă utilizați parametrul Cheie, nu este nevoie să vă faceți griji cu privire la valoarea indexului care trebuie eliminată.
Pentru a șterge toate elementele colecției și a crea o colecție nouă, utilizați din nou instrucțiunea Dim, care creează o colecție goală.
| 1 | Dim MyCollection ca colecție nouă |
Pentru a elimina complet obiectul de colecție complet, puteți seta obiectul la nimic
| 1 | Set MyCollection = Nimic |
Acest lucru este util dacă colecția nu mai este cerută de codul dvs. Setarea obiectului de colectare la nimic elimină toate referințele la acesta și eliberează memoria pe care o folosea. Acest lucru poate avea implicații importante asupra vitezei de execuție a codului dvs., dacă un obiect mare stă în memorie care nu mai este necesar.
Numărați numărul de articole dintr-o colecție
Puteți afla cu ușurință numărul de articole din colecția dvs. utilizând proprietatea „Numără”
| 1 | MsgBox MyCollection.Count |
Ați utiliza această proprietate dacă utilizați un For Next Loop pentru a itera prin colecție, deoarece vă va oferi limita superioară pentru numărul de index.
Colectarea testelor pentru o valoare specifică
Puteți itera printr-o colecție pentru a căuta o anumită valoare pentru un articol folosind un Pentru fiecare buclă
| 123456789101112 | Sub SearchCollection ()Dim MyCollection ca colecție nouăMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pentru fiecare articol din colecția meaDacă Item = "Item2" AtunciElement MsgBox și „Găsit”End IfUrmătorulSfârșitul Sub |
Codul creează o mică colecție și apoi iterează prin ea căutând un articol numit ‘item2”. Dacă este găsit, se afișează o casetă de mesaj că a găsit elementul specific
Unul dintre dezavantajele acestei metodologii este că nu puteți accesa valoarea indexului sau valoarea cheie
Dacă utilizați în schimb un buclă pentru următorul, puteți utiliza contorul pentru următorul pentru a obține valoarea indexului, deși încă nu puteți obține valoarea „cheie”
| 123456789101112 | Sub SearchCollection ()Dim MyCollection ca colecție nouăMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pentru n = 1 To MyCollection.CountDacă MyCollection.Item (n) = "Item2" AtunciMsgBox MyCollection.Item (n) & "găsit la poziția indexului" & nEnd IfUrmătorul nSfârșitul Sub |
Contorul For Next (n) va furniza poziția indexului
Sortarea unei colecții
Nu există nicio funcționalitate încorporată pentru a sorta o colecție, dar folosind gândirea „out of the box”, codul poate fi scris pentru a face un sort, utilizând funcția de sortare a foilor de lucru Excel. Acest cod folosește o foaie de lucru goală numită „SortSheet” pentru a face sortarea efectivă.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Sub SortCollection ()Dim MyCollection ca colecție nouăDim Counter At Long‘Construiți colecție cu articole de comandă aleatoriiMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"‘Captează numărul de articole din colecție pentru utilizare ulterioarăContor = MyCollection.Count„Ierați prin colecție copiind fiecare articol într-o celulă consecutivă din„ SortSheet ”(coloana A)Pentru n = 1 To MyCollection.CountFoi de calcul („SortSheet”). Celule (n, 1) = Colecția mea (n)Următorul n‘Activați foaia de sortare și utilizați rutina de sortare Excel pentru a sorta datele în ordine crescătoareFoi de calcul („SortSheet”). ActivațiRange („A1: A” & MyCollection.Count). SelectațiActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalCu ActiveWorkbook.Worksheets („SortSheet”).SetRange Range („A1: A5”).Header = xlGuess.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicaSe termina cu‘Ștergeți toate articolele din colecție - rețineți că acest ciclu pentru următoarea buclă rulează în ordine inversăPentru n = Colecția mea. Număr la 1 pas -1MyCollection.Remove (n)Următorul n‘Copiați valorile celulei înapoi în obiectul de colecție gol folosind valoarea stocată (Contor) pentru‘ buclăPentru n = 1 Pentru a contracaraMyCollection.Add Sheets („SortSheet”). Celule (n, 1). ValoareUrmătorul n„Repetați colecția pentru a dovedi ordinea în care se află acum articolelePentru fiecare articol din colecția meaElement MsgBoxUrmătorul articol‘Ștergeți foaia de lucru (foaie de sortare) - dacă este necesar, ștergeți-o și pe aceastaFoi ("SortSheet"). Interval (celule (1, 1), celule (Contor, 1)). ȘtergețiSfârșitul Sub |
Acest cod creează mai întâi o colecție cu elementele adăugate într-o ordine aleatorie. Apoi le copiază în prima coloană a unei foi de lucru (SortSheet).
Codul folosește apoi funcția de sortare Excel pentru a sorta datele din coloană în ordine crescătoare. Codul ar putea fi, de asemenea, modificat pentru a fi sortat în ordine descrescătoare.
Colecția este apoi golită de date folosind un buclă pentru următoarea buclă. Rețineți că opțiunea pas este utilizată astfel încât să se șteargă de la sfârșitul colecției până la început. Acest lucru se datorează faptului că pe măsură ce se șterge, valorile indexului sunt resetate, dacă s-au șters de la început, nu s-ar șterge corect (indexul 2 ar deveni indexul 1)
În cele din urmă, folosind o altă buclă pentru următoarea buclă, valorile articolului sunt transferate înapoi în colecția goală
În continuare, For Each Loop demonstrează că colecția este acum în bună ordine ascendentă.
Din păcate, acest lucru nu tratează nicio valoare cheie care ar fi putut fi introdusă inițial, deoarece valorile cheii nu pot fi citite
Trecerea unei colecții la o subfuncție
O colecție poate fi transmisă unui sub sau unei funcții în același mod ca orice alt parametru
| 1 | Funcția MyFunction (ByRef MyCollection ca colecție) |
Este important să treceți colecția folosind „ByRef”. Aceasta înseamnă că este utilizată colecția originală. Dacă colecția este transmisă folosind „ByVal”, atunci se creează o copie a colecției, care poate avea repercusiuni nefericite
Dacă se creează o copie utilizând „ByVal”, atunci orice schimbă colecția din cadrul funcției se întâmplă numai pe copie și nu pe original. De exemplu, dacă în cadrul funcției, un element nou este adăugat la colecție, acesta nu va apărea în colecția originală, ceea ce va crea o eroare în codul dvs.
Returnarea unei colecții dintr-o funcție
Puteți returna o colecție dintr-o funcție în același mod ca și returnarea oricărui obiect. Trebuie să utilizați cuvântul cheie Set
| 12345 | Sub ReturnFromFunction ()Dim MyCollection Ca colecțieSetați MyCollection = PopulateCollectionMsgBox MyCollection.CountSfârșitul Sub |
Acest cod creează o rutină secundară care creează un obiect numit „Colecția mea” și apoi folosește cuvântul cheie „Set” pentru a apela în mod eficient funcția pentru a completa acea colecție. După ce se face acest lucru, se afișează o casetă de mesaj pentru a arăta numărul de 2 articole
| 1234567 | Funcția PopulateCollection () Ca colecțieDim MyCollection ca colecție nouăMyCollection.Add "Item1"MyCollection.Add "Item2"Setați PopulateCollection = Colecția meaFuncția de sfârșit |
Funcția PopulateCollection creează un nou obiect de colecție și îl populează cu 2 articole. Apoi trece acest obiect înapoi la obiectul de colecție creat în sub rutina originală.
Conversia unei colecții într-o matrice
Poate doriți să vă convertiți colecția într-o matrice. Poate doriți să stocați datele acolo unde pot fi modificate și manipulate. Acest cod creează o mică colecție și apoi o transferă într-o matrice
Observați că indicele colecției începe de la 1, în timp ce indicele matrice începe la 0. În timp ce colecția are 3 elemente, matricea trebuie dimensionată doar la 2 deoarece există un element 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection ca colecție nouăDim MyArray (2) Ca șirMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pentru n = 1 To MyCollection.CountMyArray (n - 1) = MyCollection (n)Următorul nPentru n = 0 până la 2MsgBox MyArray (n)Următorul nSfârșitul Sub |
Conversia unui tablou într-o colecție
Poate doriți să convertiți o matrice într-o colecție. De exemplu, poate doriți să accesați datele într-un mod mai rapid și mai elegant decât folosind codul pentru a obține un element matrice.
Rețineți că acest lucru va funcționa numai pentru o singură dimensiune a matricei, deoarece colecția are o singură dimensiune
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection ca colecție nouăDim MyArray (2) Ca șirMyArray (0) = "item1"MyArray (1) = "Item2"MyArray (2) = "Item3"Pentru n = 0 până la 2MyCollection.Add MyArray (n)Următorul nPentru fiecare articol din colecția meaElement MsgBoxUrmătorul articolSfârșitul Sub |
Dacă ați dorit să utilizați o matrice multidimensională, puteți concatena valorile matricei împreună pentru fiecare rând din matrice utilizând un caracter delimitator între dimensiunile matricei, astfel încât atunci când citiți valoarea colecției, puteți utiliza caracterul delimitator la separați valorile.
De asemenea, puteți muta datele în colecție pe baza faptului că se adaugă valoarea primei dimensiuni (index 1), apoi se adaugă următoarea valoare a dimensiunii (index 2) și așa mai departe.
Dacă matricea ar avea, să zicem, 4 dimensiuni, fiecare a patra valoare din colecție ar fi un nou set de valori.
De asemenea, puteți adăuga valori de matrice pentru a le folosi ca chei (cu condiția ca acestea să fie unice), ceea ce ar adăuga un mod ușor de localizare a datelor specifice.
