Použití slovníku VBA
Slovník VBA funguje podobným způsobem jako objekt kolekce, ale má více vlastností a metod a nabízí větší flexibilitu
Slovník ukládá data do paměti a lze s nimi snadno manipulovat. Není vyžadován žádný automatický výpočet, zálohování na pozadí a obnovení obrazovky, takže váš kód poběží podstatně rychleji.
Objekt slovníku funguje podobným způsobem jako normální slovník, který byste použili, pokud chcete zjistit význam slova. Každý záznam ve slovníkovém objektu má hodnotu „klíč“ a hodnotu „položky“. Klíčovou hodnotu klíče použijete k vyhledání hodnoty položky v objektu slovníku podobným způsobem, jako byste použili konvenční slovník.
Vzhledem ke způsobu fungování objektu slovníku musí být všechny hodnoty klíčů jedinečné, stejným způsobem jako v konvenčním slovníku. Představte si, že byste otevřeli konvenční slovník a vyhledali význam slova a našli jste slovo uvedené více než jednou se dvěma zcela odlišnými definicemi. Byli byste velmi zmatení!
Klíčové hodnoty jsou obvykle text nebo čísla nebo obojí. Uživatelé často snáze zapamatují názvy kláves jako text, nikoli pouze čísla.
Ve srovnání s objektem kolekce je objekt kolekce pouze pro čtení. Má pouze dvě metody (Přidat a Odebrat) a dvě vlastnosti (Počet a Položka). Jakmile je položka přidána do objektu kolekce, lze ji pouze odebrat, ale nikoli upravovat, což je těžkopádný postup, pokud je třeba změnit hodnotu položky.
Velikost objektu slovníku se automaticky změní, aby odpovídal počtu položek v něm. Není nutné ji definovat ve velikosti, jako konvenční pole
Objekt slovníku je jednorozměrný a datový typ je „Variant“, takže do něj lze zadat jakýkoli datový typ, např. číslice, text, datum
Slovník VBA není nativní pro Excel a je třeba k němu přistupovat buď s časnou nebo pozdní vazbou při definování objektu slovníku
| 123 | Sub EarlyBindingExample ()Dim MyDictionary as New Scripting.DictionaryEnd Sub |
| 1234 | Sub LateBindingExample ()Dim MyDictionary jako objektNastavit MyDictionary = CreateObject ("Scripting.Dictionary")End Sub |
Pokud používáte ranou vazbu, musíte přidat odkaz na knihovnu „Microsoft Scripting Runtime“
To provedete výběrem „Nástroje | Reference ‘na panelu nabídek v okně editoru Visual Basic (VBE) a vyskakovacího okna se zobrazí se seznamem dostupných knihoven.
Přejděte dolů na „Microsoft Scripting Runtime“ a zaškrtněte políčko vedle něj. Klikněte na OK a tato knihovna je nyní součástí vašeho projektu VBA a lze na ni odkazovat pomocí časné vazby. Všechny příklady kódu v tomto článku použijí časnou vazbu.
Váš kód poběží podstatně rychleji s včasnou vazbou, protože je celý kompilován předem. Při pozdní vazbě musí být objekt kompilován při spuštění kódu
Knihovna Scripting Runtime má „Intellisense“. Při psaní kódu se zobrazí seznam dostupných metod a vlastností, což pomáhá předcházet chybám v pravopisu, které způsobují chyby ve vašem programu
Pokud také stisknete F2 v rámci VBE a vyberete knihovnu „Skriptování“, zobrazí se všechny dostupné metody a vlastnosti a parametry požadované pro každou z nich.
Distribuce aplikace Excel obsahující slovník
Jak již bylo uvedeno, knihovna Scripting Runtime není součástí Excel VBA, takže pokud distribuujete svou aplikaci jiným uživatelům, musí mít přístup ke knihovně Scripting Runtime ve svém počítači. Pokud tomu tak není, dojde k chybě.
Je dobré zahrnout nějaký kód VBA, abyste zkontrolovali, zda je tato knihovna při načtení aplikace Excel k dispozici. K tomu můžete použít příkaz „Dir“ v případě „Otevření sešitu“
Umístění souboru je C: \ Windows \ SysWOW64 \ scrrun.dll
Rozsah slovníkového objektu
Objekt Dictionary je k dispozici pouze v případě, že je otevřený sešit aplikace Excel. Při uložení sešitu se neuloží.
Pokud má být váš slovník k dispozici všem rutinám ve vašem modulu, musíte jej deklarovat (Dim) v sekci Declare v horní části modulu
Pokud chcete, aby se váš slovník používal v celém kódu, definujete jej jako globální objekt.
| 1 | Globální slovník jako nový slovník |
Plnění a čtení ze slovníku
Nejprve musíte vytvořit slovník, naplnit ho některými daty a poté jej opakovat, abyste dokázali, že data existují.
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary jako nový skript. SlovníkMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Pro n = 0 pro MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary Items (n)Další nEnd Sub |
Tento kód vytvoří nový objekt slovníku s názvem „MyDictionary“ a poté jej naplní třemi položkami. Metoda Add má dva parametry - Key a Item a oba jsou povinné
Datové typy pro klíč a položku jsou obě varianty, takže budou přijímat jakýkoli typ dat - číselný, textový, datum atd
První položku ve slovníku lze přidat jako:
| 1 | MyDictionary.Add 10, "MyItem1" |
Hodnoty byly obráceny mezi klíčem a položkou, ale to by stále fungovalo, ačkoli klíč pro vyhledávání by nyní měl hodnotu 10.
Je však důležité pochopit, že klíčovou hodnotou je vyhledávací hodnota ve slovníku. Funguje velmi podobným způsobem jako funkce VLOOKUP v Excelu. Protože všechny klíče musí mít jedinečné hodnoty, můžete zadat hodnotu klíče a okamžitě vrátit hodnotu položky pro tento klíč.
Všimněte si toho, že index slovníku začíná na 0, takže musíte odečíst 1 od počtu slovníků použitých ve smyčce For… Next
Ke čtení hodnot ve slovníku můžete použít také smyčku For… Each:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary as New Scripting.Dictionary, I As VariantMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30Za každý I In MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Příště jáEnd Sub |
Tento kód bude iterovat každou položku a zobrazí klíč položky a hodnotu položky
Použití indexového čísla položky
Ke čtení hodnoty můžete použít indexové číslo klíče nebo položky
| 123456789101112 | Dílčí indexová čísla ()Dim MyDictionary jako nový skript. SlovníkMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Key (2)MsgBox MyDictionary.Items (1)End Sub |
Tento kód vrátí klíč ‘item3’, protože index začíná na 0, a hodnota položky 20
Pomocí čísel indexů můžete odkazovat na jednotlivé hodnoty klíčů nebo položek v kolekcích klíčů nebo položek.
Filtrování slovníku
Neexistuje přímý způsob, jak to udělat, ale je docela jednoduché napsat kód:
| 1234567891011 | Dílčí filtrSlovník ()Dim MyDictionary jako nový skript. SlovníkMyDictionary.Add "AAItem1", 10MyDictionary.Add "BBItem2", 20MyDictionary.Add "BBItem3", 30Za každý I ve filtru (MyDictionary.Keys, „BB“)MsgBox MyDictionary.Item (I)Příště jáEnd Sub |
Hodnota filtru funguje pouze od začátku hodnoty klíče. Ve filtru nelze použít zástupné znaky. Tento kód vrátí dvě hodnoty položek s názvy klíčů začínajícími na „BB“
Získáte tak podmnožinu slovníku na základě hodnoty vašeho filtru, kterou pak můžete přenést do jiného slovníku nebo listu. S pečlivým plánováním názvů klíčů a zajištěním smysluplné předpony každého z nich snadno rozdělíte slovník na různé součásti.
Změna hodnoty položky klíče
Objekt slovníku má oproti kolekci velkou výhodu v tom, že hodnotu položky lze změnit např.
| 1 | MyDictionary ("MyItem4") = "40" |
V kolekci budete muset tuto položku odstranit a poté ji znovu vytvořit.
Zde je příklad kódu:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary jako nový skript. SlovníkMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Pro n = 0 pro MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary Items (n)Další nEnd Sub |
Výše uvedený kód nastaví tři položky ve slovníku a poté změní hodnotu „MyItem2“ z 20 na 25.
Mění také hodnotu „MyItem4“ na 40. Všimněte si, že v příkazech add kódu nebyl přidán žádný „MyItem4“. Když změníte hodnotu neexistujícího klíče, automaticky se vytvoří. To je velmi výhodné, protože se nespouští žádná chyba, ale znamená to, že musíte být opatrní s názvy klíčů. Neúmyslná pravopisná chyba v názvu klíče by znamenala vytvoření nového klíče a původní název klíče by stále měl starou hodnotu.
To by mohlo snadno vést k problémům s integritou v objektu slovníku.
Vyzkoušejte, zda klíč existuje
Můžete zkontrolovat, zda ve slovníku existuje hodnota klíče
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary jako nový skript. SlovníkMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")End Sub |
Kód přidá tři položky do nového objektu slovníku a poté otestuje klíč (‘MyItem8’), který není ve slovníku. To vrátí False, ale pokud by byl použit jeden z existujících klíčů, vrátil by True
Zástupné znaky nejsou přijímány. Ve vyhledávacím textu se ve výchozím nastavení rozlišují velká a malá písmena, ale lze to změnit (viz dále v článku)
Použití více hodnot ve slovníku
Na rozdíl od pole je objekt slovníku pouze jednorozměrný. To může vést k problémům, pokud máte několik hodnot, které chcete dát proti klíči.
Jedním ze způsobů, jak to obejít, je zřetězit každou hodnotu položky pomocí oddělovače mezi každou hodnotou, např. '|'
| 12345678910111213141516171819202122232425262728293031323334 | Vícenásobné hodnoty ()`` Vytvořte objekt slovníku a proměnnéDim MyDictionary as New Scripting.Dictionary, V1 As Integer, V2 As StringDim V3 jako datum, teplota jako řetězec, N jako celé číslo„Naplňte 3 proměnné a předveďte více hodnotV1 = 5V2 = "Příklad více hodnot"V3 = "22. července 2020"'Přidejte zřetězenou hodnotu do slovníku pomocí „|“ oddělovačMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Zachyťte zřetězenou hodnotu slovníku ze slovníku do proměnné.'Teplota = MyDictionary ("MyMultipleItem")„Opakujte zřetězený řetězec a oddělte jednotlivé hodnotyDělat"Najděte polohu oddělovače."N = InStr (teplota, "|")„Pokud již neexistují žádné oddělovače, výstupní smyčka DoPokud N = 0, pak Exit Do'Zobrazit text vzhledem k poloze oddělovače nalezenMsgBox vlevo (teplota, N - 1)„Po nalezení oddělovače zkraťte zřetězený řetězec na další znakTeplota = střední (teplota, N + 1)SmyčkaEnd Sub |
Dalším způsobem, jak tento problém obejít, je navrhnout vlastní podskriptovací systém pro názvy klíčů. Neexistuje žádný důvod, proč byste v názvech klíčů neměli používat závorky a čísla
| 1234567891011 | Vícenásobné hodnoty ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Example of multiple values"MyDictionary.Add "Multiple (3)", "22. července 2020"Pro N = 1 až 3MsgBox MyDictionary ("Multiple (" & N & ")")Další N.End Sub |
Tento kód přidá do slovníku tři klíče, ale každý název klíče obsahuje v závorkách číslo podskriptu. Potom můžete odkazovat na název klíče, ale pomocí čísla dílčího skriptu zřetězeného v. To je velmi podobné použití objektu pole
Mazání položek
Jednotlivé položky můžete odebrat odkazem na hodnotu klíče
| 1 | MyDictionary.Remove („MyItem2“) |
Všimněte si toho, že protože názvy klíčů jsou jedinečná, odstraní se tím pouze jeden konkrétní klíč a hodnota položky
Slovník můžete také úplně vymazat
| 1 | MyDictionary.RemoveAll |
Zde je příklad použití „Odebrat“ ve VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary jako nový skript. SlovníkMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("Item2")Pro N = 0 pro MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary Items (N)Další N.MyDictionary.RemoveAllMsgBox MyDictionary.CountEnd Sub |
Kód přidá do slovníku tři položky a poté odebere „Item2“. Poté iteruje slovníkem, aby dokázal, že „Item2“ již neexistuje
Nakonec kód odebere všechny položky ve slovníku a zobrazí počet slovníků, který je nyní nula.
Změna rozlišování malých a velkých písmen pro vyhledávání
Pokud hledáte klíč, ve výchozím nastavení rozlišuje velká a malá písmena. Můžete to však změnit pomocí vlastnosti „CompareMode“.
Všimněte si toho, že to musíte udělat okamžitě v kódu po vytvoření objektu slovníku, ale před přidáním jakýchkoli dat do slovníku. Jakmile je režim porovnání nastaven, nelze jej v daném slovníku změnit.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary jako nový skript. SlovníkMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("item2")End Sub |
V tomto případě je režim porovnání nastaven na „TextCompare“, což znamená, že nerozlišuje velká a malá písmena. Příkaz „Exists“ na konci příkladu vrátí hodnotu True, přestože hledaný text je psán malými písmeny.
V aplikaci Excel existují pouze dvě hodnoty, které lze použít pro režim porovnání. Binární srovnání rozlišuje malá a velká písmena a porovnávání textu nerozlišuje velká a malá písmena
Pokud máte režim porovnání nastaven na Binární porovnání, musíte být opatrní při pojmenovávání klíčů. Pokud nastavíte název tak, aby měl jako první znak velká písmena, pak při změně hodnoty musíte zajistit, aby byl první znak stále velký. Pokud začínáte na malá písmena, bude to interpretováno jako nový klíč a může to snadno vést k záměně a chybám ve vašem slovníku
Pamatujte, že pokud změníte hodnotu klíče a název klíče neexistuje, protože se používá binární srovnání, bude do slovníku přidán nový klíč a hodnota.
Pokud místo toho použijete porovnávání textu, pak všechny změny hodnot přejdou na klíč bez ohledu na velká a malá písmena. Pokusíte -li se přidat stejnou položku, ale s jiným malým písmenem, zobrazí se chyba, protože již existuje.
Třídění slovníku
Stejně jako u objektu kolekce neexistuje žádná metoda, která by umožnila řadit slovník, a to buď pomocí klíčů nebo hodnot položek.
Protože je ale kód VBA umístěn v excelovém sešitu, lze data slovníku přenést do Excelu v tabulkové formě a poté na něj lze použít nástroj pro řazení v Excelu. Slovník pak lze vymazat pomocí ‘RemoveAll’ a seřazené hodnoty přidat z listu.
Tento kód seřadí hodnoty klíčů i položek
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary jako nový slovníkDim Counter jako dlouhý"Vytvořte slovník s položkami náhodného pořadí."MyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19„Zachyťte počet položek ve slovníku pro budoucí použitíPočitadlo = MyDictionary.Count„Opakujte ve slovníku kopírováním každého klíče a položky do po sobě jdoucí buňky na„ List1 “(sloupec A)Pro N = 0 pro MyDictionary.Count - 1Listy („List1“). Buňky (N + 1, 1) = MyDictionary.Keys (N)Listy („List1“). Buňky (N + 1, 2) = MyDictionary.Items (N)Další N.'Aktivujte List1 a použijte třídící rutinu aplikace Excel k seřazení dat vzestupněTabulky („List1“). AktivujteRozsah ("A1: B" & MyDictionary.Count). VyberteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Klíč: = Rozsah (_"A1: A5"), SortOn: = xlSortOnValues, Objednávka: = xlAscending, DataOption: = _xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). TříditRozsah SetRange ("A1: A5").Header = xlHádej.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplikovatKonec s"Vymažte všechny položky ze slovníku."MyDictionary.RemoveAll'Zkopírujte hodnoty buňky zpět do prázdného objektu slovníku pomocí uložené hodnoty (čítače) pro smyčku'Pro N = 1 do čítačeMyDictionary.Add Sheets ("Sheet1"). Cells (N, 1). Value, Sheets ("Sheet1"). Cells (N, 2) .ValueDalší N."Opakujte ve slovníku, abyste dokázali, v jakém pořadí jsou položky nyní."Pro N = 0 pro MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary Items (N)Další N.'Vymažte list (List1) - v případě potřeby jej také odstraňteListy („List1“). Rozsah (buňky (1, 1), buňky (čítač, 2)). VymazatEnd Sub |
Tento kód vytvoří slovník s pěti přidanými hodnotami náhodného pořadí. Zachycuje počet položek do proměnné a poté iteruje slovníkem a přenáší hodnoty klíčů a položek do samostatných sloupců na listu.
Poté seřadí stažený rozsah pomocí sloupce A jako pole pro řazení. Slovník je zcela vymazán metodou ‘RemoveAll’ a kód poté iteruje hodnotami buněk v listu a přidá je zpět do slovníku.
Nakonec kód iteruje prostřednictvím slovníku a zobrazuje hodnoty klíčů a položek zřetězené, aby dokázal, že řazení fungovalo.
Změnou parametrů v třídícím kódu lze data řadit podle hodnot položek.
Kopírování seznamu klíčů do listu
Seznam všech klíčových hodnot můžete zkopírovat do listu pomocí následujícího kódu:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Listy („List1“). Rozsah („A1“). Hodnota = Připojit (MyDictionary.Keys, vbLf)End Sub |
Výsledkem bude pracovní list:
Celý slovník můžete zkopírovat do listu pomocí tohoto kódu:
| 12345678910 | Dílčí kopieIntoWorksheet ()Dim MyDictionary as New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Rozsah ("A1"). Změnit velikost (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Rozsah ("B1"). Změnit velikost (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary Items)End Sub |
Váš pracovní list bude vypadat takto:
Porovnání slovníku se sbírkou
Slovník je rychlejší než sbírka.
Sbírka již v rámci VBA je. Slovník potřebuje přidat odkaz na slovník Microsoft Scripting Dictionary nebo vytvořit objekt pomocí pozdní vazby
Sbírkový předmět lze zapsat pouze jednou a mnohokrát přečíst. Ve slovníku lze hodnotu položky změnit. U sbírky musí být položka odstraněna a poté změněná položka přidána zpět.
Sbírka funguje na hodnotách indexu, což může být obtížné zjistit, která hodnota indexu kam patří. Slovník funguje na jedinečných hodnotách klíčů, které se používají k vyhledání položky
Načítání jedné položky je ve velké sbírce pomalejší než ve slovníku
Ve sbírce se klíče používají pouze k vyhledávání dat a nelze je získat. Ve slovníku lze testovat existenci klíčů a lze je použít k vyhledání konkrétní položky.
U sbírek se rozlišují malá a velká písmena a nelze to změnit. Ve slovníku lze režim porovnání nastavit tak, aby rozlišoval malá a velká písmena
V kolekci musí být klíčovými hodnotami řetězce. Ve slovníku to mohou být libovolné datové typy, např. číslice, datum atd
Odebrání všech položek ve sbírce zahrnuje novou definici objektu kolekce. K tomu má slovník metodu „RemoveAll“.
