Utilizarea unui dicționar VBA
Un dicționar VBA funcționează într-un mod similar cu un obiect de colecție, dar are mai multe proprietăți și metode și oferă mai multă flexibilitate
Dicționarul stochează datele în memorie și poate fi ușor manipulat. Nu sunt necesare calcule automate, backup de fundal și reîmprospătarea ecranului, astfel încât codul dvs. va rula considerabil mai repede.
Obiectul dicționar funcționează în mod similar cu un dicționar normal pe care l-ați folosi dacă doriți să aflați semnificația unui cuvânt. Fiecare intrare din obiectul dicționar are o valoare „cheie” și o valoare „articol”. Utilizați „cheia”, valoarea cheii pentru a căuta valoarea articolului în obiectul dicționar, într-un mod similar în care ați folosi un dicționar convențional.
Datorită modului în care funcționează obiectul dicționar, valorile cheie trebuie să fie toate unice, în același mod ca într-un dicționar convențional. Imaginați-vă dacă ați deschis dicționarul dvs. convențional pentru a căuta semnificația unui cuvânt și ați găsi cuvântul listat de mai multe ori cu două definiții complet diferite. Ai fi foarte confuz!
Valorile cheie sunt de obicei text sau numere sau ambele. Utilizatorii găsesc adesea mai ușor să-și amintească numele tastelor ca text, mai degrabă decât ca numere.
Comparativ cu un obiect de colecție, obiectul de colecție este numai în citire. Are doar două metode (Add and Remove) și două proprietăți (Count și Item). Odată ce un element a fost adăugat la un obiect de colecție, acesta poate fi eliminat numai, dar nu editat, ceea ce reprezintă o procedură greoaie dacă trebuie modificată valoarea unui articol.
Un obiect din dicționar se va schimba automat în dimensiune pentru a se potrivi cu numărul de articole din acesta. Nu trebuie să fie definit ca dimensiune, ca un tablou convențional
Obiectul dicționar este unidimensional, iar tipul de date este „Variantă”, deci orice tip de date poate fi introdus în acesta, de ex. numeric, text, data
Dicționarul VBA nu este nativ din Excel și trebuie accesat fie prin legare timpurie, fie târzie la definirea obiectului dicționar
| 123 | Sub EarlyBindingExample ()Dim MyDictionary as New Scripting.DictionarySfârșitul Sub |
| 1234 | Sub LateBindingExample ()Reduceți dicționarul meu ca obiectSetați MyDictionary = CreateObject ("Scripting.Dictionary")Sfârșitul Sub |
Dacă utilizați legarea timpurie, trebuie să adăugați o referință la biblioteca „Microsoft Scripting Runtime”
Faceți acest lucru selectând „Instrumente | Referințe ”din bara de meniu a ferestrei Visual Basic Editor (VBE) și va apărea o fereastră pop-up cu o listă de biblioteci disponibile.
Derulați în jos până la „Microsoft Scripting Runtime” și bifați caseta de lângă acesta. Faceți clic pe OK și această bibliotecă face acum parte din proiectul dvs. VBA și poate fi referită folosind legarea timpurie. Toate exemplele de cod din acest articol vor folosi legarea timpurie.
Codul dvs. va rula considerabil mai rapid cu legarea timpurie, deoarece este compilat în față. Cu legarea târzie, obiectul trebuie să fie compilat pe măsură ce rulează codul
Biblioteca Scripting Runtime are „Intellisense”. Pe măsură ce vă scrieți codul, veți vedea liste cu metodele și proprietățile disponibile care apar, ceea ce ajută la prevenirea greșelilor de ortografie, care vor provoca erori în programul dvs.
De asemenea, dacă apăsați F2 în cadrul VBE și selectați biblioteca „Scripting”, veți vedea toate metodele și proprietățile disponibile și parametrii necesari pentru fiecare
Distribuirea aplicației Excel care conține un dicționar
După cum sa menționat deja, biblioteca Scripting Runtime nu face parte din Excel VBA, așadar, dacă distribuiți aplicația dvs. către alți utilizatori, aceștia trebuie să aibă acces la biblioteca Scripting Runtime de pe computerul lor. Dacă nu au făcut-o, atunci va apărea o eroare.
Este o idee bună să includeți un cod VBA pentru a verifica dacă această bibliotecă este prezentă atunci când aplicația dvs. Excel este încărcată. Puteți utiliza comanda „Dir” pentru a face acest lucru la evenimentul „Workbook Open”
Locația fișierului este C: \ Windows \ SysWOW64 \ scrrun.dll
Scopul unui obiect de dicționar
Obiectul Dicționar este disponibil numai în timp ce registrul de lucru Excel este deschis. Nu se salvează când este salvat registrul de lucru.
Dacă dicționarul dvs. trebuie să fie disponibil pentru toate rutinele din modulul dvs., trebuie să îl declarați (Dim) în secțiunea Declara din partea de sus a modulului.
Îl definiți ca un obiect global dacă doriți ca dicționarul dvs. să fie folosit în întregul cod.
| 1 | Global MyDictionary as New Dictionary |
Popularea și citirea din dicționarul tău
Pentru început, trebuie să creați un dicționar, să îl completați cu niște date, apoi să iterați prin el pentru a dovedi că datele există
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Pentru n = 0 către MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Următorul nSfârșitul Sub |
Acest cod creează un nou obiect de dicționar numit „MyDictionary” și apoi îl populează cu trei elemente. Metoda Adăugare are doi parametri - Cheie și Element și sunt ambii necesari
Tipurile de date pentru cheie și articol sunt ambele variante, așa că vor accepta orice tip de date - numerice, text, data etc.
Primul articol din dicționar ar putea fi adăugat ca:
| 1 | MyDictionary.Add 10, „MyItem1” |
Valorile au fost inversate între cheie și element, dar acest lucru ar funcționa în continuare, deși cheia de căutare ar deveni acum 10.
Cu toate acestea, este important să înțelegem că valoarea cheie este valoarea de căutare din dicționar. Funcționează într-un mod foarte similar cu funcția VLOOKUP din Excel. Deoarece toate cheile trebuie să aibă valori unice, puteți specifica o valoare a cheii și puteți returna instantaneu valoarea elementului pentru acea cheie.
Rețineți că indexul dicționarului începe de la 0, deci trebuie să scăpați 1 din numărul dicționarului utilizat în bucla Pentru … Următorul
Puteți utiliza, de asemenea, o Pentru … Fiecare buclă pentru a citi valorile din dicționar:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary as New Scripting.Dictionary, I As VariantMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Pentru fiecare I din MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Apoi euSfârșitul Sub |
Acest cod va itera prin fiecare articol și va afișa cheia articolului și valoarea articolului
Utilizarea numărului de index al articolelor
Puteți utiliza numărul de index al unei chei sau al unui element pentru a citi valoarea
| 123456789101112 | Numere subindice ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)Sfârșitul Sub |
Acest cod va returna cheia „item3” deoarece indexul începe de la 0 și valoarea articolului 20
Puteți face referire la valorile cheii sau articolelor individuale din colecțiile Chei sau Elemente utilizând numerele index.
Filtrarea dicționarului
Nu există o metodă directă pentru a face acest lucru, dar este destul de simplu să scrieți cod pentru a face acest lucru:
| 1234567891011 | Sub FilterDictionary ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "AAItem1", 10MyDictionary.Add "BBItem2", 20MyDictionary.Add "BBItem3", 30Pentru fiecare I din filtru (MyDictionary.Keys, „BB”)MsgBox MyDictionary.Item (I)Apoi euSfârșitul Sub |
Valoarea filtrului funcționează numai de la începutul valorii cheii. Nu puteți utiliza metacaracterele în filtru. Acest cod va returna cele două valori ale articolelor cu nume de chei care încep cu „BB”
Acest lucru vă va oferi un subset al dicționarului pe baza valorii filtrului, pe care îl puteți transfera apoi într-un alt dicționar sau o foaie de lucru. Cu o planificare atentă a numelor cheie, asigurându-vă că există un prefix semnificativ pentru fiecare, ați putea cu ușurință să împărțiți dicționarul în diferite părți componente.
Modificarea valorii unui articol pentru o cheie
Obiectul dicționar are un mare avantaj față de o colecție prin faptul că valoarea articolului poate fi modificată de ex.
| 1 | MyDictionary ("MyItem4") = "40" |
În colecție, va trebui să ștergeți acea intrare și apoi să o creați din nou.
Iată un exemplu de cod:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Pentru n = 0 către MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Următorul nSfârșitul Sub |
Codul de mai sus setează trei elemente în dicționar și apoi modifică valoarea „MyItem2” de la 20 la 25.
De asemenea, schimbă valoarea „MyItem4” la 40. Rețineți că în instrucțiunile add ale codului nu a fost adăugat niciun „MyItem4”. Când modificați valoarea unei chei care nu există, aceasta este creată automat. Acest lucru este extrem de convenabil, deoarece nu se declanșează nicio eroare, dar înseamnă că trebuie să fii atent cu numele cheilor tale. O eroare de ortografie accidentală în numele cheii ar însemna că este creată o nouă cheie, iar numele original al cheii ar avea în continuare vechea valoare.
Acest lucru ar putea duce cu ușurință la probleme de integritate în obiectul dicționar.
Testați dacă există o cheie
Puteți verifica dacă există o valoare cheie în dicționar
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")Sfârșitul Sub |
Codul adaugă trei elemente unui nou obiect de dicționar și apoi testează o cheie („MyItem8”) care nu se află în dicționar. Acest lucru returnează False, dar dacă una dintre cheile existente ar fi fost folosită, acesta va reveni True
Nu se acceptă metacaracterele. Textul de căutare este, de asemenea, sensibil la majuscule și minuscule, dar acest lucru poate fi modificat (vezi mai târziu în articol)
Utilizarea valorilor multiple într-un dicționar
Spre deosebire de o matrice, obiectul dicționar este doar unidimensional. Acest lucru poate duce la probleme dacă aveți mai multe valori pe care doriți să le puneți împotriva unei chei.
O modalitate în acest sens este de a concatena fiecare valoare a articolului folosind un caracter delimitator între fiecare valoare, de ex. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Creați un obiect de dicționar și variabileDim MyDictionary as New Scripting.Dictionary, V1 As Integer, V2 As StringDim V3 ca dată, Temp ca șir, N ca întreg'Populați 3 variabile pentru a demonstra valori multipleV1 = 5V2 = "Exemplu de valori multiple"V3 = "22-Jul-2020"'Adăugați valoarea concatenată în dicționar folosind „|” delimitatorMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Capturați valoarea dicționarului concatenat din dicționar într-o variabilăTemp = MyDictionary ("MyMultipleItem")'Iterează prin șirul concatenat pentru a separa valorile individualeDo- Găsiți poziția unui delimitatorN = InStr (Temp, "|")'Dacă nu mai există delimitatori, ieșiți din bucla DoDacă N = 0, apoi ieșiți din Do'Afișați textul relativ la poziția delimitatorului găsitMsgBox la stânga (Temp, N - 1)'Trunchiați șirul concatenat la următorul caracter după găsirea delimitatoruluiTemp = Mid (Temp, N + 1)BuclăSfârșitul Sub |
O altă modalitate de a rezolva această problemă este de a proiecta propriul sistem de sub-scriptare pentru numele cheilor. Nu există niciun motiv pentru care nu ar trebui să utilizați paranteze și numere în numele cheilor
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Exemplu de valori multiple"MyDictionary.Add "Multiple (3)", "22-Jul-2020"Pentru N = 1 până la 3MsgBox MyDictionary ("Multiple (" & N & ")")Următorul NSfârșitul Sub |
Acest cod adaugă trei chei în dicționar, dar fiecare nume de cheie conține un număr de script secundar între paranteze. Puteți face apoi referire la numele cheii, dar folosind numărul de script secundar concatenat în. Acest lucru este foarte asemănător cu utilizarea unui obiect matrice
Ștergerea articolelor
Puteți elimina elemente individuale făcând referire la valoarea cheie
| 1 | MyDictionary.Remove („MyItem2”) |
Rețineți că, deoarece numele cheilor sunt unice, acest lucru elimină doar acea anumită cheie și valoare a articolului
De asemenea, puteți șterge complet dicționarul
| 1 | MyDictionary.RemoveAll |
Iată un exemplu de utilizare a „Eliminării” în VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove („Item2”)Pentru N = 0 către MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Următorul NMyDictionary.RemoveAllMsgBox MyDictionary.CountSfârșitul Sub |
Codul adaugă trei articole în dicționar și apoi elimină „Item2”. Apoi itera prin dicționar pentru a demonstra că „Item2” nu mai există
În cele din urmă, codul elimină toate articolele din dicționar și afișează numărul dicționarului, care este acum zero.
Modificarea sensibilității majusculelor / minusculelor pentru căutări
Dacă efectuați o căutare pentru o cheie, aceasta este sensibilă la majuscule și minuscule. Cu toate acestea, puteți utiliza proprietatea „CompareMode” pentru a modifica acest lucru.
Rețineți că acest lucru trebuie făcut imediat în cod după ce creați obiectul dicționar, dar înainte de a adăuga date în dicționar. Odată ce modul de comparare a fost setat, acesta nu poate fi schimbat în dicționarul respectiv.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists („item2”)Sfârșitul Sub |
În acest exemplu, modul de comparare este setat la „TextCompare”, ceea ce înseamnă că nu este sensibil la majuscule. Declarația „Există” de la sfârșitul exemplului va reveni True, în ciuda faptului că textul căutării este cu litere mici.
În Excel există doar două valori care pot fi utilizate pentru modul de comparare. Comparația binară este diferențiată de majuscule și minuscule, iar Compararea textului este diferențiată de majuscule și minuscule
Dacă ați setat modul de comparare la Comparare binară, trebuie să fiți atenți la denumirea cheilor. Dacă setați un nume pentru a avea o primă literă cu majuscule, atunci când schimbați valoarea, trebuie să vă asigurați că faceți încă primul caracter cu majuscule. Dacă începeți cu un caracter minuscul, acesta va fi interpretat ca o nouă cheie și ar putea duce cu ușurință la confuzie și erori în dicționarul dvs.
Amintiți-vă că, dacă modificați o valoare pentru o cheie și numele cheii nu există datorită utilizării unei comparații binare, o nouă cheie și valoare vor fi adăugate în dicționar.
Dacă folosiți în schimb Text Compare, atunci orice modificare a valorii va merge la cheie, indiferent de caz. Dacă încercați să adăugați același element, dar scrieți cu un caracter diferit, veți primi o eroare, deoarece există deja.
Sortarea dicționarului
Ca și în cazul obiectului de colecție, nu există nicio metodă furnizată pentru a putea sorta dicționarul, fie folosind chei, fie valori ale articolului.
Cu toate acestea, deoarece codul VBA este așezat într-un registru de lucru Excel, datele dicționarului pot fi transferate în Excel sub formă de tabel și apoi i se poate aplica facilitatea de sortare Excel. Dicționarul poate fi apoi șters cu „RemoveAll” și valorile sortate adăugate din foaia de lucru.
Acest cod va sorta atât cheile, cât și valorile articolelor
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary ca dicționar nouDim Counter At Long'Construiți dicționar cu elemente de comandă aleatoriiMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19„Capturați numărul de articole din dicționar pentru utilizare ulterioarăCounter = MyDictionary.Count„Iterează prin dicționar copierea fiecărei chei și element într-o celulă consecutivă din„ Foaie1 ”(coloana A)Pentru N = 0 către MyDictionary.Count - 1Foi ("Sheet1"). Celule (N + 1, 1) = MyDictionary.Keys (N)Foi ("Sheet1"). Celule (N + 1, 2) = MyDictionary.Items (N)Următorul N'Activați Sheet1 și utilizați rutina de sortare Excel pentru a sorta datele în ordine crescătoareFoi („Sheet1”). ActivațiGama („A1: B” & MyDictionary.Count). SelectațiActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalCu ActiveWorkbook.Worksheets („Sheet1”). Sortați.SetRange Range („A1: A5”).Header = xlGuess.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicaSe termina cu„Ștergeți toate articolele din dicționarMyDictionary.RemoveAll'Copiați valorile celulei înapoi în obiectul dicționar gol folosind valoarea stocată (Contor) pentru bucla'Pentru N = 1 Pentru a contracaraMyDictionary.Add Sheets ("Sheet1"). Celule (N, 1). Valoare, Foi ("Sheet1"). Cell (N, 2) .ValueUrmătorul N„Repetați dicționarul pentru a dovedi ordinea în care sunt acum articolelePentru N = 0 către MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Următorul N'Ștergeți foaia de lucru (Sheet1) - dacă este necesar, ștergeți-o și pe aceastaFoi ("Sheet1"). Gama (celule (1, 1), celule (contor, 2)). ȘtergeSfârșitul Sub |
Acest cod creează un dicționar cu cinci valori de ordine aleatorii adăugate. Captează numărul de articole într-o variabilă și apoi iterează prin dicționar, transferând valorile cheii și articolelor în coloane separate pe o foaie de lucru.
Apoi sortează intervalul descărcat, folosind coloana A ca câmp de sortare. Dicționarul este șters complet folosind metoda „RemoveAll”, iar codul iterează apoi valorile celulei din foaia de lucru adăugându-le înapoi în dicționar.
În cele din urmă, codul iterează prin dicționar, afișând valorile cheii și articolelor concatenate pentru a dovedi că sortarea a funcționat.
Prin modificarea parametrilor din codul de sortare, datele pot fi sortate după valorile articolelor.
Copierea unei liste de chei într-o foaie de lucru
Puteți copia o listă a tuturor valorilor cheie într-o foaie de lucru folosind următorul cod:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Foi de calcul („Sheet1”). Range („A1”). Value = Join (MyDictionary.Keys, vbLf)Sfârșitul Sub |
Aceasta va produce rezultatul în foaia de lucru:
Puteți copia un dicționar întreg într-o foaie de lucru folosind acest cod:
| 12345678910 | Sub CopyIntoWorksheet ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Range ("A1"). Redimensionare (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Range ("B1"). Resize (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)Sfârșitul Sub |
Foaia dvs. de lucru va arăta astfel:
Compararea unui dicționar cu o colecție
Dicționarul este mai rapid decât o colecție.
O colecție este deja în cadrul VBA. Un dicționar are nevoie de o referință la Microsoft Scripting Dictionary pentru a fi adăugat sau de un obiect creat folosind legarea târzie
Un articol de colecție poate fi scris o singură dată și citit de multe ori. Într-un dicționar, valoarea articolului poate fi modificată. Cu o colecție, elementul trebuie eliminat și apoi elementul modificat adăugat înapoi.
Colecția funcționează pe valori ale indexului, ceea ce poate fi dificil de determinat care este valoarea indexului unde. Dicționarul funcționează pe valori cheie unice care sunt utilizate pentru a localiza un element
Preluarea unui singur articol este mai lentă într-o colecție mare decât într-un dicționar
Într-o colecție, cheile sunt folosite numai pentru a căuta date și nu pot fi recuperate. Într-un dicționar, cheile pot fi testate pentru existență și pot fi folosite pentru a găsi un anumit element.
Colecțiile sunt sensibile la majuscule și minuscule și acest lucru nu poate fi modificat. Într-un dicționar, modul de comparare poate fi setat pentru a da sensibilitate la majuscule sau minuscule
Într-o colecție, valorile cheie trebuie să fie șiruri. Într-un dicționar pot fi orice tip de date, de ex. numeric, dată etc.
Eliminarea tuturor articolelor dintr-o colecție implică redefinirea obiectului Colecție. Dicționarul are metoda „RemoveAll” pentru aceasta.
