VBA sõnaraamatu kasutamine
VBA sõnastik toimib sarnaselt kogumisobjektile, kuid sellel on rohkem omadusi ja meetodeid ning see pakub rohkem paindlikkust
Sõnastik salvestab andmed mällu ja seda saab hõlpsasti käsitseda. Automaatset arvutamist, tausta varundamist ja ekraani värskendamist pole vaja, nii et teie kood töötab märgatavalt kiiremini.
Sõnastikuobjekt töötab sarnaselt tavalise sõnastikuga, mida kasutaksite sõna tähenduse väljaselgitamiseks. Igal sõnastikuobjekti kirjel on „võtme” ja „üksuse” väärtus. Võtme „võtme” abil saate otsida sõnastikuobjekti üksuse väärtust sarnaselt tavapärase sõnastikuga.
Sõnastikuobjekti tööpõhimõtte tõttu peavad kõik põhiväärtused olema unikaalsed, nagu tavalises sõnastikus. Kujutage ette, kui avaksite oma tavapärase sõnastiku sõna tähenduse otsimiseks ja leiaksite sõna loetletud mitu korda kahe täiesti erineva määratlusega. Sa oleksid väga segaduses!
Põhiväärtused on tavaliselt tekst või numbrid või mõlemad. Kasutajatel on sageli lihtsam klahvide nimesid teksti asemel meelde jätta.
Võrreldes kogumisobjektiga on kogumisobjekt ainult lugemiseks. Sellel on ainult kaks meetodit (lisamine ja eemaldamine) ja kaks atribuuti (loend ja üksus). Kui üksus on kogumisobjektile lisatud, saab seda ainult eemaldada, kuid mitte muuta, mis on tülikas protseduur, kui üksuse väärtust on vaja muuta.
Sõnastikuobjekti suurus muutub automaatselt, et see vastaks selles olevate üksuste arvule. Seda ei pea suuruse järgi määratlema nagu tavalist massiivi
Sõnastikuobjekt on ühemõõtmeline ja andmetüüp on ‘Variant’, seega saab sinna sisestada mis tahes andmetüübi nt. numbriline, tekst, kuupäev
VBA sõnastik ei ole Exceli emakeel ja sellele tuleb sõnaraamatu objekti määratlemisel juurde pääseda kas varase või hilise sidumisega
| 123 | Sub EarlyBindingExample ()Dim MyDictionary as New Scripting. SõnastikEnd Sub |
| 1234 | Sub LateBindingExample ()Dim MyDictionary kui objektMäära MyDictionary = CreateObject ("Scripting.Dictionary")End Sub |
Kui kasutate varajast sidumist, peate lisama viite teekile „Microsoft Scripting Runtime”
Selleks tehke valik „Tööriistad | Viited ”Visual Basic Editori (VBE) akna menüüribal ja hüpikaken koos saadaolevate teekide loendiga.
Kerige alla jaotiseni „Microsoft Scripting Runtime” ja märkige selle kõrval olev ruut. Klõpsake nuppu OK ja see teek on nüüd osa teie VBA projektist ning sellele saab viidata varase sidumise abil. Kõik selle artikli koodinäited kasutavad varajast sidumist.
Varajase sidumise korral töötab teie kood märgatavalt kiiremini, kuna see on kõik eelnevalt kompileeritud. Hilise sidumise korral tuleb objekt koodi koostamisel kompileerida
Scripting Runtime'i teegis on „Intellisense”. Koodi kirjutades näete saadaolevate meetodite ja omaduste loendeid, mis aitab vältida õigekirjavigu, mis põhjustavad teie programmis vigu
Samuti, kui vajutate VBE -s F2 ja valite teeki „Skriptimine”, näete kõiki saadaolevaid meetodeid ja atribuute ning iga jaoks vajalikke parameetreid
Sõnastikku sisaldava Exceli rakenduse levitamine
Nagu juba märgitud, ei ole Scripting Runtime'i raamatukogu osa Excel VBA -st, nii et kui levitate oma rakendust teistele kasutajatele, peab neil olema juurdepääs oma arvuti Scripting Runtime teekile. Kui nad seda ei tee, ilmneb tõrge.
Mõistlik on lisada mõni VBA -kood, et kontrollida, kas see teek on teie Exceli rakenduse laadimisel olemas. Saate seda teha käsul „Juhtida” sündmuse „Töövihiku avamine” jaoks
Faili asukoht on C: \ Windows \ SysWOW64 \ scrrun.dll
Sõnastiku objekti ulatus
Objekt Sõnaraamat on saadaval ainult siis, kui Exceli töövihik on avatud. Töövihiku salvestamisel seda ei salvestata.
Kui teie sõnastik peaks olema saadaval kõigile teie mooduli rutiinidele, peate selle (Dim) deklareerima mooduli ülaosas olevas jaotises Deklareeri
Kui soovite, et teie sõnastikku kasutataks kogu teie koodis, määratlete selle globaalse objektina.
| 1 | Globaalne MyDictionary kui uus sõnaraamat |
Täitmine ja lugemine teie sõnastikust
Alustuseks peate looma sõnastiku, täitma selle mõne andmetega ja seejärel seda kordama, et tõestada, et andmed on olemas
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.Lisage "MyItem1", 10MyDictionary.Lisage "MyItem2", 20MyDictionary.Lisage "MyItem3", 30Kui n = 0 MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Järgmine nEnd Sub |
See kood loob uue sõnastikuobjekti nimega „MyDictionary” ja täidab selle seejärel kolme üksusega. Lisamismeetodil on kaks parameetrit - võti ja üksus ning need on mõlemad kohustuslikud
Võtme ja üksuse andmetüübid on mõlemad variandid, nii et need aktsepteerivad mis tahes tüüpi andmeid - numbrilisi, teksti, kuupäeva jne
Sõnastiku esimese üksuse võib lisada järgmiselt:
| 1 | MyDictionary.Add 10, "MyItem1" |
Väärtused on võtme ja üksuse vahel ümber pööratud, kuid see toimiks endiselt, kuigi otsinguklahvist saab nüüd 10.
Siiski on oluline mõista, et põhiväärtus on sõnastiku otsinguväärtus. See toimib väga sarnaselt Exceli funktsiooniga VLOOKUP. Kuna kõigil võtmetel peavad olema unikaalsed väärtused, saate määrata võtme väärtuse ja selle võtme väärtuse koheselt tagastada.
Pange tähele, et sõnaraamatute indeks algab 0 -st, nii et peate lahutama 1 sõnastike loendist, mida kasutatakse tsüklis For… Next
Sõnastiku väärtuste lugemiseks saate kasutada ka For … Iga silmust:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary as New Scripting. Sõnastik, I VariantMyDictionary.Lisa "MyItem1", 10MyDictionary.Lisage "MyItem2", 20MyDictionary.Lisage "MyItem3", 30Iga I jaoks minu sõnaraamatus. VõtmedMsgBox I & "" & MyDictionary (I)Edasi minaEnd Sub |
See kood kordab iga üksust ja kuvab üksuse võtme ja üksuse väärtuse
Kaubaindeksinumbri kasutamine
Väärtuse lugemiseks saate kasutada võtme või üksuse registrinumbrit
| 123456789101112 | Alamindeksi numbrid ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.CompareMode = Teksti võrdlusMyDictionary.Lisage "Item1", 10MyDictionary.Lisage "Item2", 20MyDictionary.Lisage "Item3", 30MsgBox MyDictionary. Võtmed (2)MsgBox MyDictionary. Üksused (1)End Sub |
See kood tagastab võtme „item3”, kuna indeks algab nullist ja üksuse väärtus 20
Indeksinumbreid kasutades saate viidata võtmete või üksuste kogude üksikutele võtme- või üksuseväärtustele.
Sõnastiku filtreerimine
Selleks pole otsest meetodit, kuid koodi kirjutamiseks on üsna lihtne:
| 1234567891011 | AlamfilterSõnastik ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.Lisage "AAItem1", 10MyDictionary.Lisage "BBItem2", 20MyDictionary.Lisa "BBItem3", 30Iga filtri kohta (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Edasi minaEnd Sub |
Filtri väärtus töötab ainult võtmeväärtuse algusest. Filtris ei saa kasutada metamärke. See kood tagastab kaks üksuse väärtust, mille võtmete nimed algavad tähega „BB”
See annab teile filtri väärtuse põhjal sõnastiku alamhulga, mille saate seejärel teise sõnastikku või töölehele üle kanda. Põhinimede hoolika planeerimisega, veendudes, et igaühel on tähenduslik eesliide, oleks võimalik sõnastik hõlpsasti jagada mitmeks osaks.
Võtme üksuse väärtuse muutmine
Sõnastikuobjektil on kollektsiooni ees suur eelis, kuna üksuse väärtust saab muuta nt.
| 1 | MyDictionary ("MyItem4") = "40" |
Kogus peate selle kirje kustutama ja seejärel uuesti looma.
Siin on näide koodist:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.Lisage "MyItem1", 10MyDictionary.Lisage "MyItem2", 20MyDictionary.Lisage "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"Kui n = 0 MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Järgmine nEnd Sub |
Ülaltoodud kood seab sõnastikku kolm üksust ja muudab seejärel väärtuse „MinuItem2” väärtuseks 20–25.
See muudab ka väärtuse „MyItem4” väärtuseks 40. Pange tähele, et koodi lisamislausetes ei lisatud „MyItem4”. Kui muudate olemasoleva võtme väärtust, luuakse see automaatselt. See on äärmiselt mugav, kuna ühtegi viga ei käivitata, kuid see tähendab, et peate olema võtme nimedega ettevaatlik. Tahtmatu kirjaviga võtme nimes tähendaks uue võtme loomist ja algse võtme nime säilitamist.
See võib kergesti põhjustada sõnastikuobjekti terviklikkuse probleeme.
Kontrollige, kas võti on olemas
Saate kontrollida, kas sõnastikus on võtmeväärtus
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.Lisage "MyItem1", 10MyDictionary.Lisage "MyItem2", 20MyDictionary.Lisage "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")End Sub |
Kood lisab uuele sõnastikuobjektile kolm üksust ja kontrollib seejärel võtit („MyItem8”), mida pole sõnastikus. See tagastab väärtuse Väär, kuid kui kasutataks mõnda olemasolevat võtit, tagastaks see tõe
Metamärke ei aktsepteerita. Otsingutekst on ka vaikimisi tõstutundlik, kuid seda saab muuta (vt hiljem artiklis)
Mitme väärtuse kasutamine sõnastikus
Erinevalt massiivist on sõnastiku objekt ainult ühemõõtmeline. See võib põhjustada probleeme, kui teil on mitu väärtust, mida soovite võtme vastu seada.
Üks võimalus on ühendada iga üksuse väärtus, kasutades iga väärtuse vahel eraldajat, nt. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()"Looge sõnastiku objekt ja muutujadDim MyDictionary as New Scripting. Sõnastik, V1 täisarvuna, V2 stringinaDim V3 kui kuupäev, temp kui string, N täisarv„Täitke mitu muutujat, et näidata mitut väärtustV1 = 5V2 = "Näide mitmest väärtusest"V3 = "22. juuli 2020"'Lisage sõnaraamatusse ühendatud väärtus, kasutades "|" eraldajaMyDictionary.Lisage "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Jäädvustage sõnastikust ühendatud sõnastiku väärtus muutujaksTemp = MyDictionary ("MyMultipleItem")„Üksikute väärtuste eraldamiseks korrake läbi ühendatud stringTehke"Leidke eraldaja asukohtN = InStr (temp, "|")'Kui piiritlejaid enam pole, väljuge Do loopistKui N = 0, siis väljuge'Kuva tekst eraldaja asukoha suhtesMsgBox vasakule (temp, N - 1)"Lõigake ühendatud string järgmise märgi juurde pärast leitud eraldajatTemp = keskmine (temp, N + 1)LoopEnd Sub |
Teine võimalus selle probleemi lahendamiseks on kujundada võtmete nimede jaoks oma alamskriptide süsteem. Pole põhjust, miks te ei peaks võtmete nimedes kasutama sulgusid ja numbreid
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.Lisage "Mitu (1)", 5MyDictionary.Add "Mitu (2)", "Näide mitmest väärtusest"MyDictionary.Lisage "Mitu (3)", "22. juuli 2020"N = 1 kuni 3MsgBox MyDictionary ("Mitu (" & N & ")")Järgmine N.End Sub |
See kood lisab sõnastikku kolm võtit, kuid iga võtme nimi sisaldab sulgudes alamskripti numbrit. Seejärel saate viidata võtme nimele, kuid kasutada alamskripti numbrit, mis on omavahel ühendatud. See on väga sarnane massiiviobjekti kasutamisega
Üksuste kustutamine
Saate üksikuid üksusi võtmeväärtuse alusel eemaldada
| 1 | MyDictionary.Remove (“MyItem2”) |
Pange tähele, et kuna võtmete nimed on unikaalsed, eemaldatakse see ainult ühe kindla võtme ja üksuse väärtus
Samuti saate sõnastiku täielikult tühjendada
| 1 | MyDictionary.RemoveAll |
Siin on näide „Eemalda” kasutamise kohta VBA -s:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.Lisage "Item1", 10MyDictionary.Lisage "Item2", 20MyDictionary.Lisage "Item3", 30MyDictionary.Remove ("Üksus2")N = 0 jaoks MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Järgmine N.MyDictionary.RemoveAllMsgBox MyDictionary.CountEnd Sub |
Kood lisab sõnastikku kolm üksust ja eemaldab seejärel üksuse2. Seejärel kordab see sõnastikku, et tõestada, et üksust 2 pole enam olemas
Lõpuks eemaldab kood kõik sõnastiku üksused ja kuvab sõnastike arvu, mis on nüüd null.
Otsingute puhul tõstutundlikkuse muutmine
Kui otsite võtit, on see vaikimisi tõstutundlik. Selle muutmiseks saate siiski kasutada atribuuti „CompareMode”.
Pange tähele, et seda tuleb teha koodis kohe pärast sõnastikuobjekti loomist, kuid enne andmete lisamist sõnastikku. Kui võrdlusrežiim on määratud, ei saa seda selles sõnastikus muuta.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.CompareMode = Teksti võrdlusMyDictionary.Lisage "Item1", 10MyDictionary.Lisage "Item2", 20MyDictionary.Lisage "Item3", 30MsgBox MyDictionary.Exists ("item2")End Sub |
Selles näites on võrdlusrežiimiks seatud „TextCompare”, mis tähendab, et see ei ole tõstutundlik. Näite lõpus olev avaldus „Olemasolev” tagastab tõese, vaatamata sellele, et otsingutekst on väiketähtedega.
Excelis on võrdlusrežiimis kasutada ainult kahte väärtust. Binaarvõrdlus on tõstutundlik ja tekstivõrdlus väiketundlik
Kui teil on võrdlusrežiimiks seatud Binaarne võrdlus, peate oma võtmete nimetamisel olema ettevaatlik. Kui määrate nime esimese tähemärgi suurtähtedeks, peate väärtust muutes veenduma, et teeksite ikkagi esimese tähe suurtähtedena. Kui alustate väiketähtedega, tõlgendatakse seda uue võtmena ja see võib kergesti põhjustada segadust ja vigu teie sõnastikus
Pidage meeles, et kui muudate võtme väärtust ja võtme nime pole binaarse võrdluse tõttu olemas, lisatakse sõnastikku uus võti ja väärtus.
Kui kasutate selle asemel teksti võrdlust, lähevad kõik väärtuse muudatused võtmele sõltumata suurtähtedest. Kui proovite lisada sama üksust, kuid kirjutatud erineva väiketähega, kuvatakse tõrge, kuna see on juba olemas.
Sõnastiku sortimine
Sarnaselt kogumisobjektiga ei pakuta meetodit sõnastiku sortimiseks võtmete või üksuste väärtuste abil.
Kuna aga VBA -kood asub Exceli töövihikus, saab sõnastiku andmed Excelisse üle kanda tabelina ja seejärel rakendada sellele Exceli sortimisvõimalust. Seejärel saab sõnastiku kustutada, kasutades käsku „RemoveAll” ja lisades töölehele sorteeritud väärtused.
See kood sorteerib nii võtmed kui ka üksuste väärtused
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary kui uus sõnaraamatPimendusloendur nii kaua"Koostage sõnaraamat juhusliku järjekorra üksustegaMyDictionary.Lisage "Item5", 5MyDictionary.Lisage "Item2", 15MyDictionary.Lisage "Item4", 11MyDictionary.Lisage "Item1", 2MyDictionary.Lisage "Item3", 19'Jäädvustage sõnastikusse üksuste arv edaspidiseks kasutamiseksLoendur = MyDictionary.Count„Korda sõnaraamatut, kopeerides iga võtme ja üksuse lehe 1 järjestikku lahtrisse (veerg A)N = 0 jaoks MyDictionary.Count - 1Arvutustabelid ("leht 1"). Lahtrid (N + 1, 1) = MyDictionary.Võtmed (N)Arvutustabelid ("leht 1"). Lahtrid (N + 1, 2) = MyDictionary. Üksused (N)Järgmine N.'Aktiveerige leht1 ja kasutage Exceli sortimisrutiini andmete sortimiseks kasvavas järjekorrasArvutustabelid ("Leht1"). AktiveerigeVahemik ("A1: B" ja MyDictionary.Count). ValigeActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Vahemik (_"A1: A5"), SortOn: = xlSortOnValues, järjekord: = xlAscending, DataOption: = _xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Sorteeri.SetRange vahemik ("A1: A5").Peal = xlGuess.MatchCase = Vale.Orientation = xlTopToBottom.SortMethod = xlPinYin.KandidaLõpeta'Kustutage sõnastikust kõik üksusedMyDictionary.RemoveAll'Kopeerige lahtri väärtused tagasi tühja sõnastikuobjekti, kasutades' silmuse salvestatud väärtust (loendurit)N = 1 loendurileMyDictionary.Add Sheets ("Sheet1"). Lahtrid (N, 1) .Väärtus, Sheets ("Sheet1"). Lahtrid (N, 2) .VäärtusJärgmine N.„Korda sõnastikku, et tõestada, millises järjekorras üksused praegu onN = 0 jaoks MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Järgmine N.'Tühjendage tööleht (leht 1) - vajadusel kustutage ka seeArvutustabelid ("leht 1"). Vahemik (lahtrid (1, 1), lahtrid (loendur, 2)). SelgeEnd Sub |
See kood loob sõnastiku, millele on lisatud viis juhusliku järjekorra väärtust. See salvestab üksuste arvu muutujaks ja kordab seejärel sõnastikku, kandes võtme ja üksuse väärtused töölehe eraldi veergudesse.
Seejärel sorteerib see allalaaditud vahemiku, kasutades sortimisväljana veergu A. Sõnastik kustutatakse täielikult, kasutades meetodit „RemoveAll”, ja seejärel kordab kood töölehe lahtrite väärtusi, lisades need tagasi sõnastikku.
Lõpuks kordab kood sõnastikku, näidates klahvide ja üksuste väärtusi, mis on ühendatud, et tõestada, et sortimine on toiminud.
Sortimiskoodi parameetreid muutes saaks andmeid sortida üksuste väärtuste järgi.
Klahvide loendi kopeerimine töölehele
Kõigi põhiväärtuste loendi saate töölehele kopeerida, kasutades järgmist koodi:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.CompareMode = Teksti võrdlusMyDictionary.Lisage "Item1", 10MyDictionary.Lisage "Item2", 20MyDictionary.Lisage "Item3", 30Arvutustabelid ("Leht1"). Vahemik ("A1"). Väärtus = Liitu (MyDictionary.Keys, vbLf)End Sub |
See annab teie töölehele tulemuse:
Selle sõna abil saate töölehele töölehe kopeerida:
| 12345678910 | Sub CopyIntoWorksheet ()Dim MyDictionary as New Scripting. SõnastikMyDictionary.Lisage "Item1", 10MyDictionary.Lisage "Item2", 20MyDictionary.Lisage "Item3", 30Vahemik ("A1"). Suuruse muutmine (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Vahemik ("B1"). Suuruse muutmine (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)End Sub |
Teie tööleht näeb välja selline:
Sõnastiku võrdlemine koguga
Sõnastik on kiirem kui kogumik.
Kogu on juba VBA -s. Sõnastik vajab lisamist Microsofti skriptisõnastikule või hilise sidumise abil loodud objekti
Koguartiklit saab kirjutada ainult üks kord ja lugeda mitu korda. Sõnastikus saab üksuse väärtust muuta. Kogu puhul tuleb üksus eemaldada ja seejärel muudetud üksus tagasi lisada.
Kogu töötab indeksiväärtuste alusel, mistõttu võib olla raske välja selgitada, milline indeksi väärtus kuhu kuulub. Sõnastik töötab ainulaadsete võtmeväärtuste alusel, mida kasutatakse üksuse leidmiseks
Üksiku üksuse toomine on suures kogumikus aeglasem kui sõnastikus
Kogus kasutatakse võtmeid ainult andmete otsimiseks ja neid ei ole võimalik taastada. Sõnastikus saab võtmete olemasolu kontrollida ja neid saab kasutada konkreetse üksuse leidmiseks.
Kogud on tõstutundlikud ja seda ei saa muuta. Sõnastikus saab võrdlusrežiimi seada nii, et see tekitaks väiketähti või mitte
Kogus peavad põhiväärtused olema stringid. Sõnastikus võivad need olla mis tahes andmetüübid, nt. numbrid, kuupäev jne
Kogu kogu üksuste eemaldamine hõlmab kogumisobjekti uuesti määratlemist. Sõnastikus on selleks meetod „RemoveAll“.
