A VBA szótár használata
A VBA szótár a gyűjteményobjektumhoz hasonlóan működik, de több tulajdonsággal és módszerrel rendelkezik, és nagyobb rugalmasságot kínál
A szótár tárolja az adatokat a memóriában, és könnyen kezelhető. Nincs szükség automatikus számításra, háttér -mentésre és képernyőfrissítésre, így a kód lényegesen gyorsabban fog futni.
A szótár objektum hasonló módon működik, mint egy normál szótár, amelyet akkor használna, ha szeretné megtudni egy szó jelentését. A szótárobjektum minden bejegyzésének van egy „kulcs” értéke és egy „item” értéke. A „kulcs” kulcsértékkel keresheti meg a tétel értékét a szótár objektumban, hasonlóan a hagyományos szótárhoz.
A szótárobjektum működése miatt a kulcsértékeknek egyedinek kell lenniük, ugyanúgy, mint egy hagyományos szótárban. Képzelje el, ha megnyitja hagyományos szótárát, hogy megkeresse egy szó jelentését, és többször találja a felsorolt szót két teljesen eltérő definícióval. Nagyon összezavarodnál!
A kulcsértékek általában szöveg vagy számok, vagy mindkettő. A felhasználók gyakran könnyebben megjegyzik a billentyűk nevét szövegként, nem csak számokként.
A gyűjteményobjektumhoz képest a gyűjtemény objektum csak olvasható. Csak két módszerrel rendelkezik (Hozzáadás és eltávolítás) és két tulajdonsággal (Számolás és Tétel). Miután egy elemet hozzáadtak egy gyűjteményobjektumhoz, csak eltávolítható, de nem szerkeszthető, ami nehézkes eljárás, ha egy elem értékét módosítani kell.
A szótár objektumának mérete automatikusan megváltozik, hogy megfeleljen a benne található elemek számának. Nem kell méretben meghatározni, mint egy hagyományos tömbhöz
A szótártárgy egydimenziós, az adattípus pedig ‘Változat’, így bármilyen adattípust be lehet írni pl. szám, szöveg, dátum
A VBA szótár nem natív az Excelből, és a korai vagy késői kötéssel kell elérni a szótár objektumának meghatározásakor
| 123 | Sub EarlyBindingExample ()Dim MyDictionary As New Scripting.DictionaryEnd Sub |
| 1234 | Sub LateBindingExample ()Dim Saját szótár objektumkéntSet MyDictionary = CreateObject ("Scripting.Dictionary")End Sub |
Ha a korai kötést használja, hozzá kell adnia egy hivatkozást a „Microsoft Scripting Runtime” könyvtárhoz
Ezt az „Eszközök | Hivatkozások ”a Visual Basic Editor (VBE) ablak menüsorában, és megjelenik egy előugró ablak az elérhető könyvtárak listájával.
Görgessen le a „Microsoft Scripting Runtime” szakaszhoz, és jelölje be a mellette lévő négyzetet. Kattintson az OK gombra, és ez a könyvtár most a VBA -projekt része, és hivatkozhat rá a korai kötés használatával. A cikkben szereplő összes példa korai kötést alkalmaz.
A kód lényegesen gyorsabban fog futni a korai kötéssel, mert minden előre le van fordítva. Késői kötés esetén az objektumot fordítani kell a kód futása közben
A Scripting Runtime könyvtár rendelkezik „Intellisense” funkcióval. A kód írásakor megjelenik a rendelkezésre álló módszerek és tulajdonságok listája, ami segít megelőzni a helyesírási hibákat, amelyek hibákat okozhatnak a programban
Továbbá, ha megnyomja az F2 billentyűt a VBE -n belül, és kiválasztja a „Scripting” könyvtárat, látni fogja az összes elérhető módszert és tulajdonságot, valamint az egyes paramétereket
A szótárat tartalmazó Excel -alkalmazás terjesztése
Amint már említettük, a Scripting Runtime könyvtár nem része az Excel VBA -nak, így ha más felhasználóknak terjeszti az alkalmazást, akkor hozzá kell férniük a számítógépük Scripting Runtime könyvtárához. Ha nem, akkor hiba lép fel.
Érdemes néhány VBA -kódot beilleszteni annak ellenőrzésére, hogy ez a könyvtár jelen van -e az Excel -alkalmazás betöltésekor. Ezt a „Dir” paranccsal teheti meg a „Workbook Open” eseményen
A fájl helye: C: \ Windows \ SysWOW64 \ scrrun.dll
Szótár objektum hatóköre
A Szótár objektum csak akkor érhető el, ha az Excel munkafüzet nyitva van. A munkafüzet mentésekor nem kerül mentésre.
Ha a szótára elérhető lesz a modulon belüli összes rutin számára, akkor ezt ki kell jelentenie (Dim) a modul tetején található Nyilatkozat részben
Globális objektumként határozza meg, ha azt szeretné, hogy a szótár a kódban szerepeljen.
| 1 | Global MyDictionary as New Dictionary |
Népesítés és olvasás a szótárból
Először is létre kell hoznia egy szótárt, meg kell töltenie bizonyos adatokkal, majd iterálnia kell, hogy bizonyítsa az adatok létezését.
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30N = 0 esetén a Saját szótárhoz. Szám - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Következő nEnd Sub |
Ez a kód létrehoz egy új szótárobjektumot, a „MyDictionary” nevet, majd három elemmel tölti fel. A Hozzáadás metódusnak két paramétere van - Kulcs és Elem, és mindkettő kötelező
A kulcs és a tétel adattípusai változatosak, így bármilyen típusú adatot elfogadnak - numerikus, szöveges, dátumos stb
A szótár első eleme a következőképpen egészíthető ki:
| 1 | MyDictionary.Add 10, "MyItem1" |
Az értékek megfordultak a Kulcs és az Elem között, de ez továbbra is működik, bár a keresési kulcs most 10 lesz.
Fontos azonban megérteni, hogy a kulcsérték a szótárban található keresési érték. Nagyon hasonlóan működik, mint az Excel VLOOKUP funkciója. Mivel minden kulcsnak egyedi értékkel kell rendelkeznie, megadhat egy kulcsértéket, és azonnal visszaadhatja a kulcs értékét.
Ne feledje, hogy a szótárindex 0 -tól kezdődik, ezért ki kell vonni 1 -et a For… Next ciklusban használt szótárak számából
A For… Minden ciklus segítségével a szótár értékeit is elolvashatja:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.Dictionary, I As VariantMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30For Every I In MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Következő énEnd Sub |
Ez a kód megismétli az egyes elemeket, és megjeleníti a tételkulcsot és az elem értékét
A cikkindexszám használata
Az érték olvasásához használhatja a kulcs vagy elem indexszámát
| 123456789101112 | Alindexszámok ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary. Elemek (1)End Sub |
Ez a kód visszaadja a „item3” kulcsot, amikor az index 0 -tól kezdődik, és a tétel értéke 20
Az indexszámok segítségével hivatkozhat a Kulcsok vagy elemek gyűjteményekben található egyes kulcs- vagy elemértékekre.
A szótár szűrése
Erre nincs közvetlen módszer, de nagyon egyszerű kódot írni:
| 1234567891011 | Sub FilterDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "AAItem1", 10MyDictionary.Add hozzá a "BBItem2", 20MyDictionary.Add hozzá a "BBItem3", 30Minden I szűrőben (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Következő énEnd Sub |
A szűrő értéke csak a kulcsérték elejétől működik. A szűrőben nem használható helyettesítő karakter. Ez a kód visszaadja a két tételértéket, amelyek neve „BB”
Ez a szűrő értéke alapján megadja a szótár egy részhalmazát, amelyet aztán átvihet egy másik szótárba vagy egy munkalapra. Ha gondosan megtervezi a kulcsneveket, és győződjön meg arról, hogy mindegyikhez értelmes előtag tartozik, könnyen feloszthatja a szótárat különböző összetevőkre.
Kulcs elemértékének módosítása
A szótári objektumnak nagy előnye van a gyűjteménnyel szemben, hogy a tétel értéke megváltoztatható pl.
| 1 | MyDictionary ("MyItem4") = "40" |
A gyűjteményben törölnie kell ezt a bejegyzést, majd újra létre kell hoznia.
Itt egy példa a kódra:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"N = 0 esetén a Saját szótárhoz. Szám - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Következő nEnd Sub |
A fenti kód három elemet állít be a szótárban, majd megváltoztatja a „MyItem2” értékét 20 -ról 25 -re.
Ezenkívül a „MyItem4” értékét 40 -re változtatja. Vegye figyelembe, hogy a kód add utasításaiban a „MyItem4” nem került hozzáadásra. Ha egy nem létező kulcs értékét módosítja, az automatikusan létrejön. Ez rendkívül kényelmes, mivel nem lép fel hiba, de ez azt jelenti, hogy óvatosnak kell lennie a kulcsnevekkel. A véletlen helyesírási hiba a kulcs nevében azt jelentené, hogy új kulcs jön létre, és az eredeti kulcsnév továbbra is a régi értéket tartalmazza.
Ez könnyen integritási problémákhoz vezethet a szótár objektumában.
Próbálja ki, hogy létezik -e kulcs
Ellenőrizheti, hogy van -e kulcsérték a szótárban
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Add "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")End Sub |
A kód három elemet ad hozzá egy új szótárobjektumhoz, majd tesztel egy kulcsot („MyItem8”), amely nincs a szótárban. Ez hamis értéket ad vissza, de ha a meglévő kulcsok egyikét használná, akkor igaz
A helyettesítő karakterek nem fogadhatók el. A keresési szöveg alapértelmezés szerint a kis- és nagybetűket is megkülönbözteti, de ez megváltoztatható (lásd később a cikkben)
Több érték használata szótárban
A tömbtől eltérően a szótár objektum csak egydimenziós. Ez problémákat okozhat, ha több olyan értékkel rendelkezik, amelyeket egy kulccsal szemben szeretne feltüntetni.
Ennek egyik módja az, hogy az egyes elemek értékeit összekapcsoljuk egy elválasztó karakter használatával az egyes értékek között, pl. '|'
| 12345678910111213141516171819202122232425262728293031323334 | Többszörös értékek ()'Hozzon létre szótár objektumot és változókatDim MyDictionary as New Scripting.Dictionary, V1 As Integer, V2 as StringDim V3, mint dátum, Temp, mint karakterlánc, N, mint egész'Töltsön ki 3 változót több érték bemutatásáhozV1 = 5V2 = "Példa több értékre"V3 = "2020. július 22."'Adja hozzá az összefűzött értéket a szótárhoz a "|" használatával határolóMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Rögzítse az összefűzött szótár értékét a szótárból egy változóbaTemp = MyDictionary ("MyMultipleItem")- Ismételje meg az összefűzött karakterláncot, hogy elkülönítse az egyes értékeketTedd'Keresse meg a határoló helyzetétN = InStr (temp, "|")'Ha nincs több határoló, akkor lépjen ki a Do hurokbólHa N = 0, akkor lépjen ki'Szöveg megjelenítése a határoló pozíciójához képestMsgBox bal (Temp, N - 1)'Vágja le az összefűzött karakterláncot a megtalált elválasztó karakter után a következő karakterreHőmérséklet = Közepes (Hőmérséklet, N + 1)HurokEnd Sub |
A probléma másik megoldása az, ha saját alszkript-rendszert tervez a kulcsnevekhez. Nincs ok arra, hogy miért ne használjon zárójeleket és számokat a kulcsnevekben
| 1234567891011 | Többszörös értékek ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Több (2)", "Példa több értékre"MyDictionary.Add "Multiple (3)", "2020. július 22."N = 1-3MsgBox MyDictionary ("Több (" & N & ")")Következő N.End Sub |
Ez a kód három kulcsot ad hozzá a szótárhoz, de minden kulcsnév zárójelben tartalmaz egy szkript számát. Ezután hivatkozhat a kulcs nevére, de az alszkript számát összekapcsolva használhatja. Ez nagyon hasonlít a tömbobjektum használatához
Elemek törlése
Az egyes elemeket a kulcsérték alapján távolíthatja el
| 1 | MyDictionary.Remove („MyItem2”) |
Ne feledje, hogy mivel a kulcsnevek egyediek, ez csak az adott kulcs- és elemértéket távolítja el
A szótárt teljesen törölheti
| 1 | MyDictionary.RemoveAll |
Íme egy példa az „Eltávolítás” használatára a VBA -ban:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MyDictionary.Remove ("2. tétel")N = 0 esetén a Saját szótárhoz. Szám - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Következő N.MyDictionary.RemoveAllMsgBox MyDictionary.CountEnd Sub |
A kód három elemet ad hozzá a szótárhoz, majd eltávolítja a „2. tételt”. Ezután ismétli a szótárat, hogy bebizonyítsa, hogy a „2. tétel” már nem létezik
Végül a kód eltávolítja a szótár összes elemét, és megjeleníti a szótárak számát, amely most nulla.
A kis- és nagybetűk megkülönböztetése a kereséseknél
Ha kulcsot keres, alapértelmezés szerint megkülönbözteti a kis- és nagybetűket. Ennek megváltoztatásához azonban használhatja a „CompareMode” tulajdonságot.
Ne feledje, hogy ezt azonnal meg kell tenni a kódban a szótárobjektum létrehozása után, de mielőtt bármilyen adatot hozzáadna a szótárhoz. Az összehasonlítási mód beállítása után az adott szótárban nem módosítható.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30MsgBox MyDictionary.Exists ("item2")End Sub |
Ebben a példában az összehasonlítási mód „TextCompare” értékre van állítva, ami azt jelenti, hogy nem különbözteti meg a kis- és nagybetűket. A példa végén található „Létezik” állítás igaz értéket ad vissza, annak ellenére, hogy a keresési szöveg kisbetűs.
Az Excelben csak két érték használható az összehasonlítási módhoz. A Bináris Összehasonlítás kis- és nagybetűket, a Szöveges Összehasonlítás pedig nem különbözteti meg a kis- és nagybetűket
Ha az összehasonlítási mód beállítása Bináris összehasonlítás, akkor óvatosnak kell lennie a kulcsok elnevezésénél. Ha a nevet úgy állítja be, hogy első karaktere legyen nagybetű, akkor az érték módosításakor győződjön meg arról, hogy az első karaktert továbbra is nagybetűvel írja be. Ha kisbetűvel kezdi, akkor ezt új kulcsként értelmezi, és könnyen zavart és hibákat okozhat a szótárban
Ne feledje, hogy ha módosít egy kulcs értékét, és a kulcs neve nem létezik a bináris összehasonlítás miatt, akkor új kulcs és érték kerül a szótárba.
Ha helyette a Szöveg -összehasonlítást használja, akkor minden értékváltozás a kulcshoz kerül, kis- és nagybetűktől függetlenül. Ha ugyanazt az elemet próbálja hozzáadni, de más kis- és nagybetűvel írva, akkor hibaüzenetet kap, mert az már létezik.
A szótár rendezése
A gyűjtőobjektumhoz hasonlóan nincs olyan módszer, amely lehetővé tenné a szótár rendezését kulcsok vagy elemértékek használatával.
Mivel azonban a VBA -kód egy Excel -munkafüzetben található, a szótáradatok táblázatos formában átvihetők az Excelbe, majd az Excel rendezési lehetőség alkalmazható rájuk. A szótár ezután törölhető az „RemoveAll” használatával, és a rendezett értékeket hozzáadhatja a munkalaphoz.
Ez a kód rendezi mind a kulcsokat, mind az elemek értékeit
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim Saját szótár új szótárkéntHosszú tompított számláló'Szótár készítése véletlen sorrendű elemekkelMyDictionary.Add "Item5", 5MyDictionary.Add "Item2", 15MyDictionary.Add "Item4", 11MyDictionary.Add "Item1", 2MyDictionary.Add "Item3", 19'Rögzítse az elemek számát a szótárban későbbi használatraSzámláló = MyDictionary.Count„Ismételje meg a szótárat, és másolja az egyes kulcsokat és elemeket a„ Sheet1 ”(A oszlop) egymást követő cellájábaN = 0 esetén a Saját szótárhoz. Szám - 1Táblázatok ("Sheet1"). Sejtek (N + 1, 1) = MyDictionary.Keys (N)Táblázatok ("Sheet1"). Sejtek (N + 1, 2) = Saját szótár.Tételek (N)Következő N.'Aktiválja a munkalap1 -et, és az Excel rendezési rutin segítségével rendezze az adatokat növekvő sorrendbeTáblázatok ("Sheet1"). AktiválásTartomány ("A1: B" és MyDictionary.Count). Válassza kiActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Tartomány (_"A1: A5"), SortOn: = xlSortOnValues, Sorrend: = xlNövekvő, DataOption: = _xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Rendezés.SetRange tartomány ("A1: A5").Header = xlGuess.MatchCase = Hamis.Orientation = xlTopToBottom.SortMethod = xlPinYin.AlkalmazVége ezzel'Törölje az összes elemet a szótárbólMyDictionary.RemoveAll'Másolja vissza a cellaértékeket az üres szótárobjektumba a' ciklus tárolt értéke (számlálója) segítségévelN = 1 SzámlálóhozMyDictionary.Add Sheets ("Sheet1"). Cellák (N, 1). Érték, Sheets ("Sheet1"). Cellák (N, 2). ValueKövetkező N.- Ismételje meg a szótárat, hogy bizonyítsa a tételek sorrendjétN = 0 esetén a Saját szótárhoz. Szám - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Következő N.'Törölje a munkalapot (1. lap) - ha szükséges, törölje azt isTáblázatok ("Sheet1"). Tartomány (cellák (1, 1), cellák (számláló, 2)). TisztaEnd Sub |
Ez a kód létrehoz egy szótárat öt véletlen sorrendű értékkel. Ez rögzíti az elemek számát egy változóba, majd iterál a szótárban, áthelyezve a kulcs- és elemértékeket a munkalap külön oszlopaiba.
Ezután rendezi a letöltött tartományt, az A oszlopot rendezési mezőként. A szótár az „RemoveAll” módszerrel teljesen törlődik, és a kód ezután iterálja a munkalap cellaértékeit, és hozzáadja azokat a szótárhoz.
Végül a kód ismétlődik a szótárban, megjelenítve a kulcs- és elemértékeket, amelyek összefűzve bizonyítják, hogy a rendezés működött.
A rendezési kód paramétereinek megváltoztatásával az adatok elemértékek szerint rendezhetők.
Kulcslista másolása a munkalapra
Az összes kulcsérték listáját másolhatja egy munkalapra a következő kód használatával:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = Szöveges összehasonlításMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Táblázatok ("Sheet1"). Tartomány ("A1"). Érték = Csatlakozás (MyDictionary.Keys, vbLf)End Sub |
Ez eredményezi a munkalapot:
A teljes szótárat másolhatja egy munkalapra a következő kód használatával:
| 12345678910 | Sub CopyIntoWorksheet ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Item1", 10MyDictionary.Add "Item2", 20MyDictionary.Add "Item3", 30Tartomány ("A1"). Átméretezés (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Tartomány ("B1"). Átméretezés (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)End Sub |
A munkalapja így fog kinézni:
Szótár összehasonlítása gyűjteménnyel
A szótár gyorsabb, mint egy gyűjtemény.
Egy gyűjtemény már a VBA -n belül található. A szótárnak hivatkoznia kell a Microsoft Scripting Dictionary -re, vagy egy késői kötéssel létrehozott objektumra
Egy gyűjteménytétel csak egyszer írható és sokszor olvasható. A szótárban az elem értéke megváltoztatható. Gyűjtemény esetén az elemet el kell távolítani, majd a módosított elemet vissza kell adni.
A Gyűjtemény az indexértékeken dolgozik, ami nehéz lehet eldönteni, hogy melyik indexérték hova tartozik. A Szótár egyedi kulcsértékeken dolgozik, amelyeket egy elem megkeresésére használnak
Egy tétel lekérése lassabb egy nagy gyűjteményben, mint egy szótárban
A gyűjteményben a kulcsok csak az adatok kikeresésére szolgálnak, és nem visszakereshetők. A szótárban a kulcsok tesztelhetők létezésükre, és felhasználhatók egy adott elem megkeresésére.
A gyűjtemények megkülönböztetik a kis- és nagybetűket, és ez nem módosítható. A szótárban az összehasonlítási mód beállítható úgy, hogy a kis- és nagybetűket megkülönböztesse
A gyűjteményben a kulcsértékeknek karakterláncoknak kell lenniük. Egy szótárban bármilyen adattípus lehet, pl. szám, dátum, stb
A gyűjtemény összes elemének eltávolítása magában foglalja a Collection objektum újradefiniálását. A szótár rendelkezik ezzel az „RemoveAll” módszerrel.
