Použitie slovníka VBA
Slovník VBA funguje podobne ako objekt zbierky, má však viac vlastností a metód a ponúka väčšiu flexibilitu
Slovník ukladá údaje do pamäte a dá sa s nimi ľahko manipulovať. Nie sú potrebné žiadne automatické výpočty, zálohovanie na pozadí a obnovovanie obrazovky, takže váš kód pobeží podstatne rýchlejšie.
Objekt slovníka funguje podobne ako normálny slovník, ktorý by ste použili, ak chcete zistiť význam slova. Každý záznam v slovníkovom objekte má hodnotu „kľúč“ a hodnotu „položka“. Kľúčovú hodnotu kľúča použijete na vyhľadanie hodnoty položky v slovníkovom objekte podobným spôsobom, ako by ste použili konvenčný slovník.
Pretože spôsob, akým funguje objekt slovníka, všetky hodnoty kľúčov musia byť jedinečné, rovnakým spôsobom ako v konvenčnom slovníku. Predstavte si, že by ste otvorili svoj konvenčný slovník a hľadali význam slova a našli ste slovo uvedené viackrát s dvoma úplne odlišnými definíciami. Boli by ste veľmi zmätení!
Kľúčové hodnoty sú zvyčajne text alebo čísla alebo oboje. Používateľom je často jednoduchšie zapamätať si názvy kláves ako text, nie iba ako čísla.
V porovnaní s objektom kolekcie je objekt zbierky iba na čítanie. Má iba dve metódy (Pridať a Odstrániť) a dve vlastnosti (Počet a Položka). Keď je položka pridaná k objektu zbierky, je možné ju len odstrániť, ale nie upravovať, čo je ťažkopádny postup, ak je potrebné zmeniť hodnotu položky.
Veľkosť objektu slovníka sa automaticky zmení tak, aby zodpovedal počtu položiek v ňom. Nie je potrebné definovať veľkosť, ako konvenčné pole
Objekt slovníka je jednorozmerný a typ údajov je „variant“, takže do neho možno zadať akýkoľvek typ údajov, napr. číselný, textový, dátumový
Slovník VBA nie je natívny pre Excel a pri definovaní objektu slovníka je k nemu potrebné pristupovať buď pomocou skorej alebo neskorej väzby.
| 123 | Sub EarlyBindingPriklad ()Dim MyDictionary ako nové skriptovanie. SlovníkKoniec pod |
| 1234 | Sub LateBindingExample ()Dim MyDictionary as ObjectNastaviť MyDictionary = CreateObject ("Scripting.Dictionary")Koniec pod |
Ak používate skorú väzbu, musíte pridať odkaz na knižnicu „Microsoft Scripting Runtime“
Vykonáte to výberom položky „Nástroje | Odkazy ‘na paneli s ponukami okna editora Visual Basic (VBE) a vyskakovacieho okna sa zobrazia so zoznamom dostupných knižníc.
Posuňte zobrazenie nadol na položku „Microsoft Scripting Runtime“ a začiarknite políčko vedľa neho. Kliknite na tlačidlo OK a táto knižnica je teraz súčasťou vášho projektu VBA a je možné na ňu odkazovať pomocou včasnej väzby. Všetky príklady kódu v tomto článku budú používať skorú väzbu.
Váš kód pobeží oveľa rýchlejšie s včasnou väzbou, pretože je celý zostavený vopred. Pri neskorej väzbe musí byť objekt pri spustení kódu skompilovaný
Knižnica Scripting Runtime má „Intellisense“. Pri písaní kódu sa zobrazia zoznamy dostupných metód a vlastností, čo pomáha predchádzať chybám pri hláskovaní, ktoré môžu spôsobiť chyby vo vašom programe.
Ak v rámci VBE stlačíte kláves F2 a vyberiete knižnicu „Skriptovanie“, zobrazia sa vám všetky dostupné metódy a vlastnosti a parametre požadované pre každú z nich.
Distribúcia aplikácie Excel obsahujúcej slovník
Ako už bolo uvedené, knižnica Scripting Runtime nie je súčasťou programu Excel VBA, takže ak distribuujete svoju aplikáciu iným používateľom, musia mať vo svojom počítači prístup k knižnici Scripting Runtime. Ak to neurobili, dôjde k chybe.
Je vhodné zahrnúť nejaký kód VBA, aby ste skontrolovali, či je táto knižnica k dispozícii pri načítaní aplikácie Excel. Na akciu „Otvorenie zošita“ môžete použiť príkaz „Dir“
Umiestnenie súboru je C: \ Windows \ SysWOW64 \ scrrun.dll
Rozsah objektu slovníka
Objekt Slovník je k dispozícii iba vtedy, keď je otvorený zošit programu Excel. Neuloží sa, keď je uložený zošit.
Ak má byť váš slovník dostupný pre všetky rutiny v rámci vášho modulu, musíte ho deklarovať (stlmenie) v sekcii Deklarovať v hornej časti modulu.
Ak chcete, aby sa v celom kóde používal slovník, definujete ho ako globálny objekt.
| 1 | Globálny slovník ako nový slovník |
Obyvateľstvo a čítanie z vášho slovníka
Na začiatok je potrebné vytvoriť slovník, naplniť ho niektorými údajmi a potom ho opakovať, aby ste dokázali, že údaje existujú.
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Pre n = 0 pre MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Ďalej nKoniec pod |
Tento kód vytvorí nový objekt slovníka s názvom „MyDictionary“ a potom ho naplní tromi položkami. Metóda Add má dva parametre - Key a Item a oba sú povinné
Dátové typy pre kľúč a položku sú variantné, takže budú akceptovať akýkoľvek typ údajov - číselné, textové, dátumové atď
Prvú položku v slovníku je možné pridať ako:
| 1 | MyDictionary.Add 10, "MyItem1" |
Hodnoty boli obrátené medzi kľúčom a položkou, ale to bude fungovať, aj keď vyhľadávací kľúč bude teraz 10.
Je však dôležité pochopiť, že kľúčovou hodnotou je vyhľadávacia hodnota v slovníku. Funguje to veľmi podobne ako funkcia VLOOKUP v programe Excel. Pretože všetky kľúče musia mať jedinečné hodnoty, môžete zadať hodnotu kľúča a okamžite vrátiť hodnotu položky pre tento kľúč.
Všimnite si toho, že index slovníka začína na 0, takže musíte odpočítať 1 od počtu slovníkov použitých v slučke For… Next
Na prečítanie hodnôt v slovníku môžete použiť aj slučku For… Každá z nich:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary ako nové skriptovanie. Slovník, ja ako variantMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Za každé I In MyDictionary.KeyMsgBox I & "" & MyDictionary (I)Dalej jaKoniec pod |
Tento kód bude iterovať každou položkou a zobrazí kľúč položky a hodnotu položky
Použitie indexového čísla položky
Na prečítanie hodnoty môžete použiť indexové číslo kľúča alebo položky
| 123456789101112 | Podindexové čísla ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Key (2)MsgBox MyDictionary.Items (1)Koniec pod |
Tento kód vráti kľúč „item3“, pretože index začína na 0 a hodnota položky 20
Na jednotlivé hodnoty kľúčov alebo položiek v kolekciách kľúčov alebo položiek môžete odkazovať pomocou indexových čísel.
Filtrovanie slovníka
Neexistuje žiadny priamy spôsob, ako to urobiť, ale je celkom jednoduché napísať kód, ako to urobiť:
| 1234567891011 | Podfilter Slovník ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.Add "AAItem1", 10MyDictionary.Add "BBItem2", 20MyDictionary.Add "BBItem3", 30Za každý I vo filtri (MyDictionary.Keys, „BB“)MsgBox MyDictionary.Item (I)Dalej jaKoniec pod |
Hodnota filtra funguje iba od začiatku kľúčovej hodnoty. Vo filtri nemôžete používať zástupné znaky. Tento kód vráti dve hodnoty položky s názvami kľúčov začínajúcimi na „BB“
Získate tak podmnožinu slovníka na základe hodnoty filtra, ktorú potom môžete preniesť do iného slovníka alebo pracovného hárka. Po starostlivom plánovaní názvov kľúčov a zaistení zmysluplnej predpony pre každý z nich budete slovník ľahko rozdeliť na rôzne súčasti.
Zmena hodnoty položky kľúča
Objekt slovníka má oproti kolekcii veľkú výhodu v tom, že hodnotu položky je možné zmeniť napr.
| 1 | MyDictionary ("MyItem4") = "40" |
V zbierke budete musieť tento záznam odstrániť a potom ho znova vytvoriť.
Tu je príklad kódu:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Pre n = 0 pre MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Ďalej nKoniec pod |
Vyššie uvedený kód nastaví tri položky v slovníku a potom zmení hodnotu „MyItem2“ z 20 na 25.
Tiež sa zmení hodnota „MyItem4“ na 40. Upozorňujeme, že do príkazov na pridanie kódu nebol pridaný žiadny „MyItem4“. Keď zmeníte hodnotu neexistujúceho kľúča, automaticky sa vytvorí. Je to veľmi praktické, pretože sa nespúšťa žiadna chyba, ale znamená to, že si musíte dávať pozor na názvy kľúčov. Neúmyselná pravopisná chyba v názve kľúča by znamenala, že sa vytvorí nový kľúč a pôvodný názov kľúča bude mať stále starú hodnotu.
To môže ľahko viesť k problémom s integritou v slovníkovom objekte.
Vyskúšajte, či kľúč existuje
Môžete skontrolovať, či sa v slovníku nachádza hodnota kľúča
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")Koniec pod |
Kód pridá tri položky do nového objektu slovníka a potom otestuje kľúč („MyItem8“), ktorý sa nenachádza v slovníku. Vráti hodnotu False, ale ak bol použitý jeden z existujúcich kľúčov, vráti hodnotu True
Zástupné znaky nie sú akceptované. V texte vyhľadávania sa v predvolenom nastavení rozlišujú malé a veľké písmená, ale to je možné zmeniť (pozri ďalej v článku)
Použitie viacerých hodnôt v slovníku
Na rozdiel od poľa je objekt slovníka iba jednorozmerný. Ak máte niekoľko hodnôt, ktoré chcete postaviť proti kľúču, môže to viesť k problémom.
Jedným zo spôsobov je spojiť všetky hodnoty položky pomocou oddeľovača medzi každou hodnotou, napr. „|“
| 12345678910111213141516171819202122232425262728293031323334 | Vedľajšie viacnásobné hodnoty ()'Vytvorte objekt slovníka a premennéDim MyDictionary ako nové skriptovanie. Slovník, V1 ako celé číslo, V2 ako reťazecDim V3 ako dátum, teplota ako reťazec, N ako celé číslo„Naplňte 3 premenné a ukážte viac hodnôtV1 = 5V2 = "Príklad viacerých hodnôt"V3 = "22. júla 2020"'Pridajte zreťazenú hodnotu do slovníka pomocou „|“ oddeľovačMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Zachyťte zreťazenú hodnotu slovníka zo slovníka do premennejTeplota = MyDictionary ("MyMultipleItem")„Opakujte zreťazený reťazec a oddeľte jednotlivé hodnotyUrob„Nájdite polohu oddeľovačaN = InStr (teplota, "|")„Ak už nie sú žiadne oddeľovače, výstupná slučka DoAk N = 0, potom Exit Do'Zobrazený text vzhľadom na polohu oddeľovača nájdenýMsgBox vľavo (teplota, N - 1)„Po nájdení oddeľovača skráťte zreťazený reťazec na nasledujúci znakTeplota = stredná (teplota, N + 1)SlučkaKoniec pod |
Ďalším riešením tohto problému je navrhnúť vlastný sub-skriptovací systém pre názvy kľúčov. Neexistuje dôvod, prečo by ste v názvoch kľúčov nemali používať zátvorky a čísla
| 1234567891011 | Vedľajšie viacnásobné hodnoty ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Example of multiple values"MyDictionary.Add "Multiple (3)", "22-July-2020"Pre N = 1 až 3MsgBox MyDictionary ("Viacnásobné (" & N & ")")Ďalej N.Koniec pod |
Tento kód pridáva do slovníka tri kľúče, ale každý názov kľúča obsahuje v zátvorkách číslo podskriptu. Potom sa môžete odvolať na názov kľúča, ale pomocou čísla podskriptu zreťazeného v. Je to veľmi podobné ako pri použití objektu poľa
Odstránenie položiek
Jednotlivé položky môžete odstrániť odkazom na kľúčovú hodnotu
| 1 | MyDictionary.Remove („MyItem2“) |
Upozorňujeme, že pretože názvy kľúčov sú jedinečné, odstráni sa tým iba jeden konkrétny kľúč a hodnota položky
Slovník môžete tiež úplne vymazať
| 1 | MyDictionary.RemoveAll |
Tu je príklad použitia „Odstrániť“ vo VBA:
| 12345678910111213141516 | Vedľajšie hodnoty odstránenia ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("Item2")Pre N = 0 do MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Ďalej N.MyDictionary.RemoveAllMsgBox MyDictionary.CountKoniec pod |
Kód pridá do slovníka tri položky a potom odstráni položku „Item2“. Potom iteruje cez slovník, aby dokázal, že „položka 2“ už neexistuje
Nakoniec kód odstráni všetky položky zo slovníka a zobrazí počet slovníkov, ktorý je teraz nulový.
Zmena citlivosti malých a veľkých písmen pri vyhľadávaniach
Ak hľadáte kľúč, v predvolenom nastavení sa rozlišujú malé a veľké písmena. Môžete to však zmeniť pomocou vlastnosti „CompareMode“.
Všimnite si toho, že to musíte urobiť bezprostredne v kóde po vytvorení objektu slovníka, ale pred pridaním akýchkoľvek údajov do slovníka. Keď je režim porovnania nastavený, nemožno ho v tomto slovníku meniť.
| 12345678910 | Citlivosť čiastkovej zmeny prípaduDim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("item2")Koniec pod |
V tomto prípade je režim porovnávania nastavený na „TextCompare“, čo znamená, že nerozlišuje veľké a malé písmená. Príkaz „Existuje“ na konci príkladu vráti hodnotu True, napriek tomu, že hľadaný text je napísaný malými písmenami.
V programe Excel existujú iba dve hodnoty, ktoré je možné použiť na porovnanie. Binárne porovnávanie rozlišuje malé a veľké písmená a porovnávanie textu nerozlišuje malé a veľké písmená
Ak máte režim porovnávania nastavený na Binárne porovnávanie, pri pomenovaní kľúčov musíte byť opatrní. Ak nastavíte názov tak, aby ako prvý znak obsahoval veľké písmená, potom pri zmene hodnoty musíte zaistiť, aby bol prvý znak stále veľký. Ak začnete s malými písmenami, bude to interpretované ako nový kľúč a môže to ľahko viesť k zmätku a chybám vo vašom slovníku
Nezabudnite, že ak zmeníte hodnotu kľúča a názov kľúča neexistuje, pretože sa používa binárne porovnávanie, do slovníka bude pridaný nový kľúč a hodnota.
Ak namiesto toho použijete porovnávanie textu, všetky zmeny hodnôt prejdú na kľúč bez ohľadu na veľkosť písmen. Ak sa pokúsite pridať rovnakú položku, ale s iným malým písmenom, zobrazí sa chyba, pretože už existuje.
Triedenie slovníka
Rovnako ako pre objekt zbierky, neexistuje žiadna metóda, ktorá by umožnila triediť slovník, a to buď pomocou kľúčov alebo hodnôt položiek.
Pretože je však kód VBA uložený v zošite programu Excel, údaje zo slovníka je možné preniesť do programu Excel v tabuľkovej forme a potom naň možno použiť zariadenie na triedenie programu Excel. Slovník je potom možné vymazať pomocou príkazu „RemoveAll“ a zoradené hodnoty pridať z pracovného hárka.
Tento kód zoradí hodnoty kľúčov a položiek
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Podtrieda Môj slovník ()Dim MyDictionary ako nový slovníkDim Counter ako dlhý'Zostavte si slovník s položkami náhodného poradiaMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19„Zachyťte počet položiek v slovníku pre budúce použitiePočítadlo = MyDictionary.Count„Opakujte pomocou slovníka skopírovaním každého kľúča a položky do po sebe idúcej bunky v„ liste1 “(stĺpec A)Pre N = 0 do MyDictionary.Count - 1Listy („List1“). Bunky (N + 1, 1) = MyDictionary.Keys (N)Listy („List1“). Bunky (N + 1, 2) = MyDictionary.Items (N)Ďalej N.'Aktivujte hárok1 a pomocou rutiny triedenia v Exceli zoraďte údaje vzostupneTabuľky („List1“). AktivujteRozsah („A1: B“ & MyDictionary.Count). VyberteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Kľúč: = Rozsah (_"A1: A5"), SortOn: = xlSortOnValues, Poradie: = xlAscending, DataOption: = _xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). ZoradiťRozsah SetRange ("A1: A5").Header = xlHádaj.MatchCase = False.Orientácia = xlTopToBottom.Trieda = xlPinYin.PoužiťUkončiť s„Vymažte všetky položky zo slovníkaMyDictionary.RemoveAll'Skopírujte hodnoty buniek späť do prázdneho objektu slovníka pomocou uloženej hodnoty (počítadlo) pre slučku'Pre N = 1 do počítadlaMyDictionary.Add Sheets ("Sheet1"). Cells (N, 1). Value, Sheets ("Sheet1"). Cells (N, 2) .ValueĎalej N.„Opakujte slovník, aby ste zistili poradie, v ktorom sa položky nachádzajúPre N = 0 do MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Ďalej N.„Vymažte pracovný hárok (list 1) - v prípade potreby ho tiež odstráňteTabuľky („List1“). Rozsah (bunky (1, 1), bunky (počítadlo, 2)). VymazaťKoniec pod |
Tento kód vytvorí slovník s piatimi pridanými hodnotami náhodného poradia. Zachytáva počet položiek do premennej a potom iteruje cez slovník a prenáša hodnoty kľúča a položky do samostatných stĺpcov na hárku.
Potom zoradí stiahnutý rozsah pomocou stĺpca A ako poľa na zoradenie. Slovník sa úplne vymaže pomocou metódy „RemoveAll“ a kód potom iteruje hodnotami buniek v pracovnom hárku a pridá ich späť do slovníka.
Nakoniec kód prechádza slovníkom a zobrazuje hodnoty kľúčov a položiek zreťazené, aby dokázal, že triedenie fungovalo.
Zmenou parametrov v triediacom kóde môžu byť údaje zoradené podľa hodnôt položiek.
Kopírovanie zoznamu kľúčov do pracovného hárka
Zoznam všetkých hodnôt kľúčov môžete skopírovať do pracovného hárka pomocou nasledujúceho kódu:
| 12345678910 | Podpis CopyKeyList ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Tabuľky („List1“). Rozsah („A1“). Hodnota = Pripojenie (MyDictionary.Keys, vbLf)Koniec pod |
Výsledkom bude pracovný list:
Celý slovník môžete skopírovať do pracovného hárka pomocou tohto kódu:
| 12345678910 | Vedľajšia kópiaIntoWorksheet ()Dim MyDictionary ako nové skriptovanie. SlovníkMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Rozsah ("A1"). Zmena veľkosti (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Rozsah („B1“). Zmena veľkosti (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary Items)Koniec pod |
Váš pracovný list bude vyzerať takto:
Porovnanie slovníka so zbierkou
Slovník je rýchlejší ako zbierka.
Zbierka už je vo VBA. Na pridanie slovníka je potrebný odkaz na slovník skriptovania spoločnosti Microsoft alebo objekt vytvorený pomocou neskorej väzby
Zbierkový predmet je možné zapísať a prečítať mnohokrát. V slovníku je možné zmeniť hodnotu položky. Pri zbierke musí byť položka odstránená a potom zmenená položka pridaná späť.
Kolekcia funguje na hodnotách indexu, čo môže byť náročné na zistenie, ktorá hodnota indexu kam patrí. Slovník funguje na jedinečných hodnotách kľúčov, ktoré sa používajú na vyhľadanie položky
Načítanie jednej položky je vo veľkej zbierke pomalšie ako v slovníku
V zbierke sa kľúče používajú iba na vyhľadávanie údajov a nie je ich možné získať. V slovníku je možné testovať existenciu kľúčov a použiť ich na nájdenie konkrétnej položky.
V zbierkach sa rozlišujú malé a veľké písmena a nemožno to zmeniť. V slovníku je možné režim porovnania nastaviť tak, aby rozlišoval malé a veľké písmená alebo nie
V zbierke musia byť kľúčovými hodnotami reťazce. V slovníku to môžu byť akékoľvek dátové typy, napr. číselné, dátumové atď
Odstránenie všetkých položiek v zbierke zahŕňa nanovo definovanie objektu zbierky. Slovník má na to metódu „RemoveAll“.
