Korištenje VBA rječnika
VBA rječnik djeluje na sličan način kao objekt prikupljanja, ali ima više svojstava i metoda te nudi veću fleksibilnost
Rječnik pohranjuje podatke u memoriju i njima se može lako rukovati. Ne zahtijeva se automatski izračun, pozadinsko sigurnosno kopiranje i osvježavanje zaslona, pa će vaš kôd raditi znatno brže.
Objekt rječnika funkcionira na sličan način kao i uobičajeni rječnik koji biste koristili ako želite saznati značenje riječi. Svaki unos u objekt rječnika ima vrijednost 'ključa' i 'stavke'. Vrijednost ključa "key" koristite za traženje vrijednosti stavke u objektu rječnika, na sličan način na koji biste koristili konvencionalni rječnik.
Zbog načina na koji rječnički objekt radi, sve ključne vrijednosti moraju biti jedinstvene, na isti način kao u konvencionalnom rječniku. Zamislite da ste otvorili svoj uobičajeni rječnik kako biste potražili značenje riječi i pronašli riječ navedenu više puta s dvije potpuno različite definicije. Bili biste jako zbunjeni!
Ključne vrijednosti obično su tekst ili brojevi, ili oboje. Korisnicima je često lakše zapamtiti nazive ključeva kao tekst, a ne samo kao brojeve.
U usporedbi s objektom zbirke, objekt zbirke je samo za čitanje. Ima samo dvije metode (dodavanje i uklanjanje) i dva svojstva (broj i stavka). Nakon što je stavka dodana u objekt zbirke, može se samo ukloniti, ali ne i urediti, što je glomazan postupak ako je potrebno promijeniti vrijednost stavke.
Objekt rječnika automatski će promijeniti veličinu tako da odgovara broju stavki u njemu. Ne mora se definirati veličinom, poput konvencionalnog niza
Objekt rječnika je jednodimenzionalan, a tip podataka je ‘Variant’, pa se u njega može unijeti bilo koja vrsta podataka, npr. numerički, tekst, datum
VBA rječnik nije izvorni za Excel i potrebno mu je pristupiti ranim ili kasnim vezivanjem prilikom definiranja objekta rječnika
| 123 | Sub EarlyBindingExample ()Dim MyDictionary As New Scripting.DictionaryKraj podm |
| 1234 | Sub LateBindingExample ()Dim MyDictionary As ObjectPostavi MyDictionary = CreateObject ("Scripting.Dictionary")Kraj podm |
Ako koristite rano vezivanje, morate dodati referencu u biblioteku ‘Microsoft Scripting Runtime’
To činite odabirom ‘Alati | Reference ’na traci izbornika prozora uređivača Visual Basic (VBE) i pojavit će se skočni prozor s popisom dostupnih biblioteka.
Pomaknite se dolje do "Microsoft Scripting Runtime" i označite okvir pored njega. Pritisnite U redu i ova je knjižnica sada dio vašeg VBA projekta i može se referencirati pomoću ranog povezivanja. Svi primjeri koda u ovom članku koristit će rano vezivanje.
Vaš kôd će se izvesti znatno brže s ranim vezanjem, jer je sav unaprijed sastavljen. S kasnim vezanjem, objekt se mora sastaviti dok se kôd izvodi
Knjižnica Scripting Runtime ima "Intellisense". Tijekom pisanja koda vidjet ćete da se pojavljuju popisi dostupnih metoda i svojstava, što pomaže u sprječavanju grešaka u pravopisu, što će uzrokovati greške u vašem programu
Također, ako pritisnete F2 unutar VBE -a i odaberete biblioteku ‘Scripting’, vidjet ćete sve dostupne metode i svojstva te potrebne parametre za svaki
Distribucija vaše Excel aplikacije koja sadrži rječnik
Kao što je već istaknuto, knjižnica Scripting Runtime nije dio programa Excel VBA pa ako svoju aplikaciju distribuirate drugim korisnicima, oni moraju imati pristup biblioteci Skripting Runtime na svom računalu. Ako nisu, doći će do pogreške.
Bilo bi dobro uključiti neki VBA kôd kako biste provjerili je li ova knjižnica prisutna kada se učita vaša Excel aplikacija. To možete učiniti naredbom 'Dir' za događaj 'Workbook Open'
Mjesto datoteke je C: \ Windows \ SysWOW64 \ scrrun.dll
Opseg objekta rječnika
Objekt Rječnik dostupan je samo dok je radna knjiga programa Excel otvorena. Ne sprema se kada se spremi radna knjiga.
Da bi vaš rječnik bio dostupan svim rutinama unutar vašeg modula, morate ga prijaviti (Dim) u odjeljku Declare na samom vrhu modula
Definirate ga kao globalni objekt ako želite da se vaš rječnik koristi u cijelom kodu.
| 1 | Globalni rječnik kao novi rječnik |
Napunjavanje i čitanje iz vašeg rječnika
Za početak morate stvoriti rječnik, popuniti ga nekim podacima, a zatim ga ponoviti kako biste dokazali da podaci postoje
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodajte "MyItem3", 30Za n = 0 u MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Dalje nKraj podm |
Ovaj kôd stvara novi objekt rječnika pod nazivom "MyDictionary", a zatim ga popunjava s tri stavke. Metoda Add ima dva parametra - Key i Item, a oba su obavezna
Vrste podataka za ključ i stavku su varijante pa prihvaćaju sve vrste podataka - numeričke, tekstualne, datumske itd.
Prva stavka u rječniku mogla bi se dodati kao:
| 1 | MyDictionary.Add 10, "MyItem1" |
Vrijednosti su promijenjene između ključa i stavke, ali to bi i dalje funkcioniralo, iako bi ključ za pretraživanje sada postao 10.
Međutim, važno je shvatiti da je ključna vrijednost vrijednost pretraživanja u rječniku. Radi na vrlo sličan način kao i funkcija VLOOKUP u Excelu. Budući da svi ključevi moraju imati jedinstvene vrijednosti, možete navesti vrijednost ključa i odmah vratiti vrijednost stavke za taj ključ.
Imajte na umu da indeks rječnika počinje s 0 pa morate oduzeti 1 od broja rječnika koji se koristi u petlji Za … Sljedeće
Također možete koristiti For… Svaka petlja za čitanje vrijednosti u rječniku:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.Dictionary, I As VariantMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodajte "MyItem3", 30Za svaki I u MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Sljedeći jaKraj podm |
Ovaj kôd će ponavljati svaku stavku i prikazati ključ stavke i vrijednost stavke
Upotreba indeksnog broja stavke
Za čitanje vrijednosti možete koristiti indeksni broj ključa ili stavke
| 123456789101112 | Brojevi podindeksa ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = Usporedi tekstMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)Kraj podm |
Ovaj kôd će vratiti ključ ‘item3’ jer indeks počinje s 0, a vrijednost stavke 20
Pomoću indeksnih brojeva možete se pozvati na pojedinačne vrijednosti ključa ili stavke u zbirkama Ključevi ili Stavke.
Filtriranje Rječnika
Ne postoji izravna metoda za to, ali vrlo je jednostavno napisati kôd za to:
| 1234567891011 | PodfilterDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "AAItem1", 10MyDictionary.Dodajte "BBItem2", 20MyDictionary.Dodajte "BBItem3", 30Za svaki filtar I In (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Sljedeći jaKraj podm |
Vrijednost filtra radi samo od početka ključne vrijednosti. U filtru ne možete koristiti zamjenske znakove. Ovaj kôd će vratiti dvije vrijednosti stavki s nazivima ključeva koji počinju s 'BB'
To će vam dati podskup rječnika na temelju vaše vrijednosti filtra, koji zatim možete prenijeti u drugi rječnik ili na radni list. Pažljivim planiranjem naziva ključeva, pazeći da za svaki postoji smisleni prefiks, lako ćete moći podijeliti rječnik na različite sastavne dijelove.
Promjena vrijednosti stavke ključa
Objekt rječnika ima veliku prednost u odnosu na zbirku u tome što se vrijednost stavke može promijeniti npr.
| 1 | MyDictionary ("MyItem4") = "40" |
U zbirci biste morali izbrisati taj unos, a zatim ga ponovno stvoriti.
Evo primjera koda:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodajte "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Za n = 0 u MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Dalje nKraj podm |
Gornji kôd postavlja tri stavke unutar rječnika, a zatim mijenja vrijednost "MyItem2" s 20 na 25.
Također mijenja vrijednost 'MyItem4' na 40. Imajte na umu da u izrazima add koda nije dodan 'MyItem4'. Kada promijenite vrijednost ključa koji ne postoji, automatski se stvara. To je iznimno prikladno jer se ne pokreće pogreška, ali znači da morate biti oprezni s nazivima ključeva. Nehotična pravopisna pogreška u imenu ključa značila bi da je kreiran novi ključ, a izvorni naziv ključa i dalje će imati staru vrijednost.
To bi lako moglo dovesti do problema s integritetom u objektu rječnika.
Testirajte postoji li ključ
Možete provjeriti postoji li vrijednost ključa u rječniku
| 123456789 | PodprovjeraPostojiDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodajte "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")Kraj podm |
Kôd dodaje tri stavke u novi objekt rječnika, a zatim testira ključ ('MyItem8') koji se ne nalazi u rječniku. Ovo vraća False, ali da je korišten jedan od postojećih ključeva, vratio bi se True
Zamjenski znakovi nisu prihvaćeni. Tekst za pretraživanje prema zadanim postavkama također razlikuje velika i mala slova, ali to se može promijeniti (pogledajte kasnije u članku)
Korištenje više vrijednosti u rječniku
Za razliku od niza, objekt rječnika je samo jednodimenzionalan. To može dovesti do problema ako imate nekoliko vrijednosti koje želite staviti protiv ključa.
Jedan način zaobilaženja ovoga je povezivanje svake vrijednosti stavke pomoću znaka razgraničenja između svake vrijednosti, npr. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Pod više vrijednosti ()'Stvorite objekt rječnika i varijableDim MyDictionary kao novi Scripting.Dictionary, V1 kao cijeli broj, V2 kao nizDim V3 kao datum, Temp kao niz, N kao cijeli broj'Popunite 3 varijable kako biste pokazali više vrijednostiV1 = 5V2 = "Primjer više vrijednosti"V3 = "22. srpnja 2020."'Dodajte spojenu vrijednost u rječnik pomoću "|" razdjelnikMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Snimite ujedinjenu vrijednost rječnika iz rječnika u varijabluTemp = MyDictionary ("MyMultipleItem")'Ponovite ulančani niz kako biste odvojili pojedinačne vrijednostiČini»Pronađi položaj graničnikaN = InStr (Temp, "|")'Ako nema više graničnika, izlazna petlja DoAko je N = 0, tada izađite iz Do'Prikažite tekst u odnosu na pronađen razdjelnikMsgBox lijevo (Temp, N - 1)'Skratite ujedinjeni niz na sljedeći znak nakon što je razdjelnik pronađenTemp = srednja (Temp, N + 1)PetljaKraj podm |
Drugi način zaobilaženja ovog problema je dizajniranje vlastitog sustava skriptiranja za nazive ključeva. Nema razloga zašto u nazivima ključeva ne koristite zagrade i brojeve
| 1234567891011 | Pod više vrijednosti ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "Više (1)", 5MyDictionary.Add "Multiple (2)", "Example of multiple values"MyDictionary.Add "Multiple (3)", "22-Jul-2020"Za N = 1 do 3MsgBox MyDictionary ("Više (" & N & ")")Sljedeći N.Kraj podm |
Ovaj kôd dodaje tri ključa u rječnik, ali svaki naziv ključa sadrži broj pod skripte u zagradama. Zatim se možete pozvati na naziv ključa, ali koristeći broj pod skripte spojen u. To je vrlo slično korištenju objekta niza
Brisanje stavki
Pojedine stavke možete ukloniti pozivanjem na vrijednost ključa
| 1 | MyDictionary.Remove ("MyItem2") |
Imajte na umu da budući da su nazivi ključeva jedinstveni, time se uklanja samo taj određeni ključ i vrijednost stavke
Također možete potpuno očistiti rječnik
| 1 | MyDictionary.RemoveAll |
Evo primjera korištenja ‘Ukloni’ u VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("Stavka2")Za N = 0 u MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Sljedeći N.MyDictionary.RemoveAllMsgBox MyDictionary.CountKraj podm |
Kôd dodaje tri stavke u rječnik, a zatim uklanja "stavku 2". Zatim ponavlja kroz rječnik kako bi dokazao da 'Stavka2' više ne postoji
Na kraju, kod uklanja sve stavke u rječniku i prikazuje broj rječnika, koji je sada nula.
Promjena osjetljivosti velikih i malih slova za pretraživanja
Ako tražite ključ, prema zadanim postavkama razlikuju se velika i mala slova. No za promjenu toga možete koristiti svojstvo "CompareMode".
Imajte na umu da se to mora učiniti odmah u kodu nakon što stvorite objekt rječnika, ali prije nego što dodate bilo kakve podatke u rječnik. Nakon što je postavljen način uspoređivanja, ne može se mijenjati unutar tog rječnika.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = Usporedi tekstMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("item2")Kraj podm |
U ovom primjeru način uspoređivanja postavljen je na "TextCompare" što znači da ne razlikuje velika i mala slova. Izjava 'Exists' na kraju primjera vratit će True, unatoč činjenici da je sav tekst za pretraživanje malih slova.
U Excelu postoje samo dvije vrijednosti koje se mogu koristiti za način usporedbe. Binary Compare razlikuje mala i velika slova, a Text Compare ne razlikuje velika i mala slova
Ako imate način uspoređivanja postavljen na Binarno uspoređivanje, morate biti oprezni pri imenovanju ključeva. Ako ime postavite tako da veliko slovo bude prvi znak, tada kada mijenjate vrijednost morate paziti da prvi znak ipak bude veliki. Ako počnete malim slovom, to će se protumačiti kao novi ključ i lako bi moglo dovesti do zabune i pogrešaka u vašem rječniku
Upamtite da ako promijenite vrijednost za ključ, a naziv ključa ne postoji zbog korištenja Binarne usporedbe, novi ključ i vrijednost bit će dodani u rječnik.
Ako umjesto toga koristite Usporedbu teksta, sve promjene vrijednosti ići će do ključa bez obzira na velika i mala slova. Ako pokušate dodati istu stavku, ali napisanu s drugačijim slovima, dobit ćete pogrešku jer ona već postoji.
Sortiranje Rječnika
Kao i kod objekta prikupljanja, ne postoji način na koji se može sortirati rječnik, bilo pomoću ključeva ili vrijednosti stavki.
No, budući da se VBA kôd nalazi u radnoj knjizi programa Excel, podaci rječnika mogu se prenijeti u Excel u tabličnom obliku, a zatim se na njega može primijeniti mogućnost sortiranja programa Excel. Rječnik se tada može očistiti pomoću opcije 'RemoveAll' i sortirane vrijednosti dodati s radnog lista.
Ovaj će kôd sortirati ključeve i vrijednosti stavki
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | PodvrstaMyDictionary ()Dim MyDictionary As New DictionaryDim Counter As Long'Napravite rječnik sa stavkama slučajnog redoslijedaMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19'Snimite broj stavki u rječniku za buduću upotrebuBrojač = MyDictionary.Count'Iteracijom kroz rječnik kopirate svaki ključ i stavku u uzastopnu ćeliju na' Sheet1 '(stupac A)Za N = 0 u MyDictionary.Count - 1Listovi ("List 1"). Ćelije (N + 1, 1) = MyDictionary.Key (N)Listovi ("List 1"). Ćelije (N + 1, 2) = MyDictionary.Items (N)Sljedeći N.'Aktivirajte Sheet1 i pomoću Excel rutine sortiranja razvrstajte podatke u rastućem redoslijeduListovi ("List1"). AktivirajteRaspon ("A1: B" & MyDictionary.Count) .OdaberiteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Raspon (_"A1: A5"), SortOn: = xlSortOnValues, Redoslijed: = xlAscending, DataOption: = _xlSortNormalS ActiveWorkbook.Worksheets ("List1"). Sortiraj.Raspon raspona ("A1: A5").Header = xlPogađanje.MatchCase = Netačno.Orijentacija = xlTopToBottom.SortMethod = xlPinYin.Prijavi seZavrši s'Obriši sve stavke iz rječnikaMyDictionary.RemoveAll'Kopirajte vrijednosti ćelije natrag u prazan objekt rječnika koristeći pohranjenu vrijednost (Brojač) za petlju'Za N = 1 Za brojačMyDictionary.Add Sheets ("Sheet1"). Cells (N, 1) .Vrednost, Sheets ("Sheet1"). Cells (N, 2) .VrednostSljedeći N.'Ponovite rječnik kako biste dokazali redoslijed u kojem se sada nalaze stavkeZa N = 0 u MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Sljedeći N.'Očistite radni list (List 1) - ako je potrebno, izbrišite i njegaListovi ("List 1"). Raspon (ćelije (1, 1), ćelije (brojač, 2)). OčistiKraj podm |
Ovaj kôd stvara rječnik s pet dodanih slučajnih vrijednosti. On bilježi broj stavki u varijablu, a zatim ponavlja kroz rječnik, prenoseći vrijednosti ključa i stavke u zasebne stupce na radnom listu.
Zatim sortira preuzeti raspon, koristeći stupac A kao polje za sortiranje. Rječnik se potpuno briše metodom ‘RemoveAll’, a kôd zatim ponavlja vrijednosti ćelije na radnom listu dodajući ih natrag u rječnik.
Konačno, kôd ponavlja kroz rječnik, prikazujući povezane vrijednosti ključa i stavke kako bi dokazao da je sortiranje uspjelo.
Promjenom parametara u kodu za sortiranje, podaci bi se mogli sortirati prema vrijednostima stavki.
Kopiranje popisa ključeva na radni list
Popis svih ključnih vrijednosti možete kopirati u radni list pomoću sljedećeg koda:
| 12345678910 | PotkopirajKeyList ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = Usporedi tekstMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Tablice ("List1"). Raspon ("A1"). Vrijednost = Pridruživanje (MyDictionary.Keys, vbLf)Kraj podm |
Ovo će proizvesti rezultat na vašem radnom listu:
Pomoću ovog koda možete kopirati cijeli rječnik na radni list:
| 12345678910 | Potkopiraj u radni list ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Raspon ("A1"). Promijeni veličinu (MyDictionary.Count, 1) = Funkcija radnog lista.Prijenos (MyDictionary.Keys)Raspon ("B1"). Promijeni veličinu (MyDictionary.Count, 1) = Funkcija radnog lista.Prijenos (MyDictionary.Items)Kraj podm |
Vaš radni list izgledat će ovako:
Usporedba rječnika sa zbirkom
Rječnik je brži od zbirke.
Zbirka je već unutar VBA -e. Rječnik treba upućivanje na Microsoftov rječnik skripti za dodavanje ili objekt izrađen kasnim vezanjem
Stavka zbirke može se napisati samo jednom i čitati više puta. U Rječniku se vrijednost stavke može promijeniti. Uz zbirku, stavku je potrebno ukloniti, a zatim promijenjenu stavku dodati natrag.
Zbirka radi na vrijednostima indeksa, što može biti teško odrediti koja vrijednost indeksa gdje pripada. Rječnik radi na jedinstvenim ključnim vrijednostima koje se koriste za lociranje stavke
Dohvaćanje jedne stavke sporije je u velikoj zbirci nego u rječniku
U zbirci se ključevi koriste samo za traženje podataka i nije ih moguće dohvatiti. U rječniku se ključevi mogu testirati na postojanje i mogu se koristiti za pronalaženje određene stavke.
Zbirke razlikuju velika i mala slova i to se ne može promijeniti. U Rječniku se način usporedbe može postaviti tako da daje osjetljivost na velika ili mala slova
U zbirci vrijednosti ključa moraju biti nizovi. U Rječniku to mogu biti bilo koje vrste podataka, npr. numerički, datum, itd
Uklanjanje svih stavki u zbirci uključuje ponovno definiranje objekta zbirke. Rječnik za to ima metodu ‘RemoveAll’.
