Uporaba slovarja VBA
Slovar VBA deluje na podoben način kot zbirni predmet, vendar ima več lastnosti in metod ter ponuja večjo prilagodljivost
Slovar shranjuje podatke v pomnilnik in z njimi je mogoče enostavno upravljati. Samodejni izračun, varnostno kopiranje v ozadju in osveževanje zaslona niso potrebni, zato bo vaša koda delovala precej hitreje.
Slovarski predmet deluje na podoben način kot običajen slovar, ki bi ga uporabili, če želite izvedeti pomen besede. Vsak vnos v slovarskem objektu ima vrednost "ključ" in vrednost "postavke". Za iskanje vrednosti postavke v objektu slovarja uporabite ključ in vrednost ključa na podoben način kot pri običajnem slovarju.
Zaradi načina delovanja slovarskega predmeta morajo biti vse ključne vrednosti edinstvene, na enak način kot v običajnem slovarju. Predstavljajte si, če bi odprli svoj običajni slovar, da bi poiskali pomen besede, in našli besedo, navedeno večkrat z dvema popolnoma različnima definicijama. Bili bi zelo zmedeni!
Ključne vrednosti so običajno besedilo ali številke ali oboje. Uporabniki si pogosto lažje zapomnijo imena tipk kot besedilo in ne le številk.
V primerjavi z objektom zbirke je predmet zbiranja samo za branje. Ima samo dve metodi (Dodaj in odstrani) in dve lastnosti (Štetje in postavka). Ko je element dodan v zbirni predmet, ga je mogoče le odstraniti, ne pa tudi urejati, kar je okoren postopek, če je treba vrednost predmeta spremeniti.
Slovarski predmet se bo samodejno spremenil glede na število elementov v njem. Ni ga treba določiti po velikosti, tako kot običajna matrika
Slovarski objekt je enodimenzionalen, podatkovni tip pa je ‘Variant’, zato se vanj lahko vnese kateri koli podatkovni tip, npr. številka, besedilo, datum
Slovar VBA ni izvorni v Excelu in do njega je treba dostopati z zgodnjo ali pozno vezavo pri opredelitvi predmeta slovarja
| 123 | Sub EarlyBindingExample ()Dim MyDictionary As New Scripting.DictionaryEnd Sub |
| 1234 | Sub LateBindingExample ()Zatemni MyDictionary kot objektNastavi MyDictionary = CreateObject ("Scripting.Dictionary")End Sub |
Če uporabljate zgodnjo vezavo, morate dodati referenco v knjižnico »Microsoft Scripting Runtime«
To naredite tako, da izberete »Orodja | Reference ’v menijski vrstici okna urejevalnika Visual Basic (VBE) in pojavilo se bo pojavno okno s seznamom razpoložljivih knjižnic.
Pomaknite se navzdol do »Microsoft Scripting Runtime« in označite polje zraven njega. Kliknite V redu in ta knjižnica je zdaj del vašega projekta VBA in se nanjo lahko sklicujete z zgodnjo vezavo. Vsi primeri kode v tem članku bodo uporabljali zgodnjo vezavo.
Vaša koda bo z zgodnjo vezavo delovala precej hitreje, ker je vsa vnaprej sestavljena. Ob pozni vezavi je treba objekt sestaviti med izvajanjem kode
Knjižnica Scripting Runtime ima "Intellisense". Ko pišete kodo, se prikažejo seznami razpoložljivih metod in lastnosti, ki pomagajo preprečiti napake pri črkovanju, kar bo povzročilo napake v vašem programu
Če pritisnete F2 v VBE in izberete knjižnico »Scripting«, boste videli vse razpoložljive metode in lastnosti ter potrebne parametre za vsako
Distribucija vaše Excelove aplikacije, ki vsebuje slovar
Kot je bilo že poudarjeno, knjižnica Scripting Runtime ni del Excela VBA, zato morate, če svojo aplikacijo razdelite drugim uporabnikom, imeti dostop do knjižnice Scripting Runtime v svojem računalniku. Če niso, bo prišlo do napake.
Dobro je vključiti kodo VBA, da preverite, ali je ta knjižnica prisotna, ko je naložena aplikacija Excel. To lahko storite z ukazom »Dir« na dogodku »Odpiranje delovnega zvezka«
Lokacija datoteke je C: \ Windows \ SysWOW64 \ scrrun.dll
Področje uporabe slovarskega predmeta
Predmet Slovar je na voljo samo, ko je odprt Excelov delovni zvezek. Ko se shrani delovni zvezek, se ne shrani.
Če naj bo vaš slovar na voljo vsem rutinam v vašem modulu, ga morate razglasiti (Dim) v razdelku Declare na samem vrhu modula
Opredelite ga kot globalni objekt, če želite, da se vaš slovar uporablja v celotni kodi.
| 1 | Globalni MyDictionary kot nov slovar |
Prebivanje in branje iz vašega slovarja
Za začetek morate ustvariti slovar, ga napolniti z nekaterimi podatki in ga nato ponoviti, da dokažete, da podatki obstajajo
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodaj "MyItem3", 30Za n = 0 v MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Naprej nEnd Sub |
Ta koda ustvari nov slovarski objekt z imenom »MyDictionary« in ga nato naseli s tremi elementi. Metoda Add ima dva parametra - Key in Item, oba pa sta obvezna
Podatkovni vrsti za ključ in element sta različni, zato sprejmeta vse vrste podatkov - številske, besedilne, datume itd.
Prvi element v slovarju bi lahko dodali kot:
| 1 | MyDictionary.Add 10, "MyItem1" |
Vrednosti so bile obrnjene med ključem in elementom, vendar bi to še vedno delovalo, čeprav bi iskalni ključ zdaj postal 10.
Pomembno pa je razumeti, da je ključna vrednost iskalna vrednost v slovarju. Deluje na zelo podoben način kot funkcija VLOOKUP v Excelu. Ker morajo imeti vsi ključi edinstvene vrednosti, lahko določite vrednost ključa in takoj vrnete vrednost postavke za ta ključ.
Upoštevajte, da se indeks slovarja začne pri 0, zato morate odšteti 1 od števila slovarjev, ki se uporabljajo v zanki For… Next
Za branje vrednosti v slovarju lahko uporabite tudi For… Vsaka zanka:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.Dictionary, I As VariantMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodaj "MyItem3", 30Za vsakega v mojem slovarjuMsgBox I & "" & MyDictionary (I)Naslednji jazEnd Sub |
Ta koda se bo ponavljala skozi vsak element in prikazala ključ predmeta in vrednost predmeta
Uporaba indeksne številke predmeta
Za branje vrednosti lahko uporabite indeksno številko ključa ali predmeta
| 123456789101112 | Pod indeksne številke ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = Primerjaj besediloMyDictionary.Dodaj "Item1", 10MyDictionary.Dodaj "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary. Ključi (2)MsgBox MyDictionary.Items (1)End Sub |
Ta koda bo vrnila ključ „item3“, ko se indeks začne pri 0, vrednost vrednosti pa 20
Z indeksnimi številkami se lahko sklicujete na posamezne vrednosti ključa ali postavke v zbirkah Ključi ali Predmeti.
Filtriranje slovarja
Ne obstaja neposredna metoda za to, vendar je zelo preprosto napisati kodo za to:
| 1234567891011 | PodfilterDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "AAItem1", 10MyDictionary.Dodajte "BBItem2", 20MyDictionary.Dodaj "BBItem3", 30Za vsak filter v meniju (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Naslednji jazEnd Sub |
Vrednost filtra deluje le od začetka vrednosti ključa. V filtru ne morete uporabljati nadomestnih znakov. Ta koda bo vrnila dve vrednosti postavk z imeni ključev, ki se začnejo z 'BB'
Tako boste dobili podnabor slovarja glede na vrednost vašega filtra, ki ga lahko nato prenesete v drug slovar ali na delovni list. S skrbnim načrtovanjem imen ključev in zagotavljanjem, da ima vsaka smiselna predpona, bi slovar zlahka razdelili na različne sestavne dele.
Spreminjanje vrednosti postavke ključa
Slovarski objekt ima veliko prednost pred zbirko, saj lahko vrednost postavke spremenimo npr.
| 1 | MyDictionary ("MyItem4") = "40" |
V zbirki bi morali izbrisati ta vnos in ga nato znova ustvariti.
Tu je primer kode:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodaj "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Za n = 0 v MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Naprej nEnd Sub |
Zgornja koda nastavi tri elemente v slovarju in nato spremeni vrednost »MyItem2« z 20 na 25.
Prav tako spremeni vrednost „MyItem4“ na 40. Upoštevajte, da v stavkih add kode ni bil dodan noben „MyItem4“. Ko spremenite vrednost ključa, ki ne obstaja, se samodejno ustvari. To je zelo priročno, saj se ne sproži nobena napaka, vendar to pomeni, da morate biti pri imenih ključev previdni. Nenamerna črkovalna napaka v imenu ključa bi pomenila, da je ustvarjen nov ključ, prvotno ime ključa pa bi še vedno imelo staro vrednost.
To lahko zlahka privede do težav z integriteto predmeta slovarja.
Preverite, ali ključ obstaja
Lahko preverite, ali v slovarju obstaja vrednost ključa
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodajte "MyItem1", 10MyDictionary.Dodajte "MyItem2", 20MyDictionary.Dodaj "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")End Sub |
Koda novemu slovarskemu objektu doda tri elemente in nato preizkusi ključ ('MyItem8'), ki ga ni v slovarju. To vrne False, če pa bi uporabili enega od obstoječih ključev, bi vrnil True
Nadomestni znaki niso sprejeti. Iskalno besedilo privzeto razlikuje tudi med velikimi in malimi črkami, vendar ga je mogoče spremeniti (glej kasneje v članku)
Uporaba več vrednosti v slovarju
Za razliko od matrike je objekt slovarja samo en dimenzija. To lahko povzroči težave, če imate več vrednosti, ki jih želite postaviti proti ključu.
Eden od načinov za to je združiti vsako vrednost postavke z uporabo znaka razmejevalnika med vsako vrednostjo, npr. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Ustvari slovarski objekt in spremenljivkeZatemni MyDictionary kot nov skript. Slovar, V1 kot celo število, V2 kot nizZatemni V3 kot datum, Temp kot niz, N kot celo število'Popolnite 3 spremenljivke za prikaz več vrednostiV1 = 5V2 = "Primer več vrednosti"V3 = "22. julij 2020"'Dodajte združeno vrednost v slovar z uporabo "|" ločilaMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Zajemite združeno vrednost slovarja iz slovarja v spremenljivkoTemp = MyDictionary ("MyMultipleItem")„Ponovite po združenem nizu, da ločite posamezne vrednostiNaredi'Poiščite položaj ločilnikaN = InStr (Temp, "|")„Če ni več razmejevalnikov, izhodna zanka DoČe je N = 0, potem zapustite Do'Prikaz besedila glede na najdeni položaj ločilnikaMsgBox levo (Temp, N - 1)'Skrajšani združeni niz na naslednji znak po najdenem ločevalnikuTemp = srednja (Temp, N + 1)ZankaEnd Sub |
Drug način za rešitev te težave je oblikovanje lastnega sistema podnapisov za imena ključev. Nobenega razloga ni, da v imenih ključev ne uporabite oklepajev in številk
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodaj "Več (1)", 5MyDictionary.Add "Multiple (2)", "Primer več vrednosti"MyDictionary.Add "Multiple (3)", "22-Jul-2020"Za N = 1 do 3MsgBox MyDictionary ("Več (" & N & ")")Naslednji N.End Sub |
Ta koda v slovar doda tri ključe, vendar vsako ime ključa vsebuje številko pod skripta v oklepaju. Nato se lahko sklicujete na ime ključa, vendar z uporabo številke pod skripta, združene v. To je zelo podobno uporabi predmeta matrike
Brisanje elementov
Posamezne postavke lahko odstranite glede na vrednost ključa
| 1 | MyDictionary.Remove (“MyItem2”) |
Upoštevajte, da ker so imena ključev edinstvena, se s tem odstrani samo en določen ključ in vrednost predmeta
Slovar lahko tudi popolnoma počistite
| 1 | MyDictionary.RemoveAll |
Tu je primer uporabe »Odstrani« v VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodaj "Item1", 10MyDictionary.Dodaj "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("Item2")Za N = 0 do MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Naslednji N.MyDictionary.RemoveAllMsgBox MyDictionary.CountEnd Sub |
Koda v slovar doda tri elemente, nato pa odstrani »element 2«. Nato po slovarju ponovi, da dokaže, da „element 2“ ne obstaja več
Na koncu koda odstrani vse elemente v slovarju in prikaže število slovarjev, ki je zdaj nič.
Spreminjanje občutljivosti velikih in malih črk za iskanja
Če iščete ključ, je privzeto občutljiv na velike in male črke. Če želite to spremeniti, lahko uporabite lastnost »CompareMode«.
Upoštevajte, da je to treba storiti takoj v kodi, potem ko ustvarite objekt slovarja, vendar preden dodate kakršne koli podatke v slovar. Ko je način za primerjavo nastavljen, ga v tem slovarju ni mogoče spremeniti.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = Primerjaj besediloMyDictionary.Dodajte "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Dodaj "Item3", 30MsgBox MyDictionary.Exists ("item2")End Sub |
V tem primeru je način za primerjavo nastavljen na "TextCompare", kar pomeni, da ni občutljiv na velike in male črke. Izjava 'Exists' na koncu primera bo vrnila True, kljub dejstvu, da je iskalno besedilo z malimi črkami.
V Excelu lahko za način primerjave uporabite le dve vrednosti. Binary Compare razlikuje velike in male črke, Text Compare pa ne razlikuje med velikimi in malimi črkami
Če imate način za primerjavo nastavljen na Binary Compare, morate biti pri imenovanju ključev previdni. Če ime nastavite tako, da ima velika črka prvi znak, potem ko spremenite vrednost, se prepričajte, da je prvi znak še vedno velika. Če začnete z malimi črkami, bo to razloženo kot nov ključ in lahko zlahka povzroči zmedo in napake v vašem slovarju
Ne pozabite, da če spremenite vrednost ključa in če ime ključa ne obstaja zaradi uporabe binarne primerjave, bosta v slovar dodana nov ključ in vrednost.
Če namesto tega uporabite Primerjavo besedila, bodo vse spremembe vrednosti prišle do ključa ne glede na velike in male črke. Če poskusite dodati isti element, vendar črkovan z drugačno črko, se prikaže napaka, ker že obstaja.
Razvrščanje slovarja
Tako kot pri zbirnem objektu ni na voljo nobene metode za razvrščanje slovarja s pomočjo ključev ali vrednosti elementov.
Ker pa koda VBA sedi v Excelovem delovnem zvezku, se lahko slovarski podatki prenesejo v Excel v tabelarni obliki, nato pa se zanj lahko uporabi Excel -ovo razvrščanje. Slovar lahko nato počistite s funkcijo »RemoveAll« in razvrščene vrednosti dodate z delovnega lista.
Ta koda bo razvrstila ključe in vrednosti elementov
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | PodrazvrstiMyDictionary ()Dim MyDictionary kot nov slovarDim Counter As Long'Zgradite slovar z naključnimi postavkamiMyDictionary.Dodaj "Item5", 5MyDictionary.Dodaj "Item2", 15MyDictionary.Dodaj "Item4", 11MyDictionary.Dodajte "Item1", 2MyDictionary.Add "Item3", 19'Zajemite število elementov v slovar za prihodnjo uporaboŠtevec = MyDictionary.Count"Ponavljajte skozi slovar, kopirajte vsak ključ in element v zaporedno celico na" Sheet1 "(stolpec A)Za N = 0 do MyDictionary.Count - 1Listi ("List1"). Celice (N + 1, 1) = MyDictionary.Keys (N)Listi ("List1"). Celice (N + 1, 2) = MyDictionary.Items (N)Naslednji N.'Aktivirajte Sheet1 in uporabite Excel razvrščanje za razvrščanje podatkov v naraščajočem vrstnem reduListi ("List1"). AktivirajteObseg ("A1: B" & MyDictionary.Count). IzberiteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Vrstni red: = xlAscending, DataOption: = _xlSortNormalZ ActiveWorkbook.Worksheets ("List1"). RazvrstiObseg .SetRange ("A1: A5").Header = xlGuess.MatchCase = Neresnično.Orientation = xlTopToBottom.SortMethod = xlPinYin.UveljaviKončaj s'Počisti vse elemente iz slovarjaMyDictionary.RemoveAll'Kopirajte vrednosti celic nazaj v prazen objekt slovarja s shranjeno vrednostjo (števec) za zanko'Za N = 1 Za števecMyDictionary.Add Sheets ("Sheet1"). Cells (N, 1) .Vrednost, Sheets ("Sheet1"). Cells (N, 2) .VrednostNaslednji N."Ponovite slovar, da dokažete vrstni red, v katerem so predmetiZa N = 0 do MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Naslednji N.'Počisti delovni list (List1) - če je potrebno, ga tudi izbrišiteListi ("List1"). Obseg (celice (1, 1), celice (števec, 2)). PočistiEnd Sub |
Ta koda ustvari slovar s petimi dodanimi naključnimi vrednostmi. Zajema število elementov v spremenljivko, nato pa se po slovarju ponovi in vrednosti ključa in postavke prenese v ločene stolpce na delovnem listu.
Nato razvrsti preneseni obseg in uporabi stolpec A kot polje za razvrščanje. Slovar se popolnoma počisti z metodo »RemoveAll«, koda pa nato ponovi vrednosti celic na delovnem listu in jih doda nazaj v slovar.
Nazadnje koda ponavlja po slovarju in prikaže združene vrednosti ključa in postavke, da dokaže, da je razvrščanje delovalo.
S spreminjanjem parametrov v kodi za razvrščanje bi lahko podatke razvrstili po vrednostih postavk.
Kopiranje seznama ključev na delovni list
Seznam vseh ključnih vrednosti lahko kopirate na delovni list z naslednjo kodo:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = Primerjaj besediloMyDictionary.Dodaj "Item1", 10MyDictionary.Dodaj "Item2", 20MyDictionary.Add "Item3", 30Listi ("List1"). Razpon ("A1"). Vrednost = Pridruži se (MyDictionary.Keys, vbLf)End Sub |
Tako boste na svojem delovnem listu dobili rezultat:
S to kodo lahko kopirate celoten slovar na delovni list:
| 12345678910 | Sub CopyIntoWorksheet ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Dodaj "Item1", 10MyDictionary.Dodaj "Item2", 20MyDictionary.Add "Item3", 30Obseg ("A1"). Spreminjanje velikosti (MyDictionary.Count, 1) = Funkcija delovnega lista.Prenos (MyDictionary.Keys)Obseg ("B1"). Spreminjanje velikosti (MyDictionary.Count, 1) = Funkcija delovnega lista.Prenos (MyDictionary.Items)End Sub |
Vaš delovni list bo videti tako:
Primerjava slovarja z zbirko
Slovar je hitrejši od zbirke.
Zbirka je že v okviru VBA. Slovar potrebuje sklic na Microsoftov slovar skriptov, ki ga je treba dodati, ali predmet, ustvarjen s pozno vezavo
Element zbirke je mogoče napisati samo enkrat in večkrat prebrati. V slovarju lahko vrednost postavke spremenite. Z zbirko je treba element odstraniti, nato pa spremenjeni predmet dodati nazaj.
Zbirka deluje na vrednostih indeksov, zato je težko ugotoviti, katera vrednost indeksa pripada. Slovar deluje na edinstvenih ključnih vrednostih, ki se uporabljajo za iskanje predmeta
Pridobivanje enega predmeta je v veliki zbirki počasnejše kot v slovarju
V zbirki se ključi uporabljajo samo za iskanje podatkov in jih ni mogoče pridobiti. V slovarju lahko ključe preizkusimo glede obstoja in jih lahko uporabimo za iskanje določenega predmeta.
Zbirke razlikujejo med velikimi in malimi črkami in tega ni mogoče spremeniti. V slovarju lahko način za primerjavo nastavite tako, da daje občutljivost na velike ali male črke
V zbirki morajo biti ključne vrednosti nizi. V slovarju so lahko kateri koli tip podatkov, npr. številski, datum itd
Odstranitev vseh elementov v zbirki vključuje ponovno opredelitev predmeta zbirke. Slovar ima za to metodo "RemoveAll".
