Brug af en VBA -ordbog
En VBA -ordbog fungerer på samme måde som et samleobjekt, men den har flere egenskaber og metoder og giver mere fleksibilitet
Ordbogen gemmer dataene i hukommelsen og kan let manipuleres. Der kræves ingen automatisk beregning, baggrundssikkerhedskopiering og skærmopfriskning, så din kode kører betydeligt hurtigere.
Ordbogsobjektet fungerer på samme måde som en normal ordbog, som du ville bruge, hvis du vil finde ud af betydningen af et ord. Hver post i ordbogsobjektet har en 'nøgle' -værdi og en' element' -værdi. Du bruger 'nøglen' nøgleværdien til at slå elementværdien op i ordbogsobjektet på en lignende måde, som du ville bruge en konventionel ordbog.
På grund af den måde, ordbogsobjektet fungerer på, skal nøgleværdierne alle være unikke på samme måde som i en konventionel ordbog. Tænk, hvis du åbnede din konventionelle ordbog for at slå op på betydningen af et ord og fandt ordet opført mere end én gang med to helt forskellige definitioner. Du ville være meget forvirret!
Nøgleværdier er normalt tekst eller tal eller begge dele. Brugere finder det ofte lettere at huske navne på nøgler som tekst frem for blot tal.
I sammenligning med et samleobjekt er samlingsobjektet skrivebeskyttet. Det har kun to metoder (Tilføj og fjern) og to egenskaber (tæl og element). Når et element er føjet til et samleobjekt, kan det kun fjernes, men ikke redigeres, hvilket er en besværlig procedure, hvis værdien af et element skal ændres.
Et ordbogsobjekt ændres automatisk i størrelse, så det passer til antallet af emner i det. Det behøver ikke at være defineret i størrelse, ligesom et konventionelt array
Ordbogsobjektet er endimensionelt, og datatypen er ‘Variant’, så enhver datatype kan indtastes i den f.eks. numerisk, tekst, dato
VBA -ordbogen er ikke hjemmehørende i Excel og skal tilgås enten ved tidlig eller sen binding ved definition af ordbogsobjektet
| 123 | Sub EarlyBindingExample ()Dim MyDictionary som nyt scripting. OrdbogAfslut Sub |
| 1234 | Sub LateBindingExample ()Dim MyDictionary som objektIndstil MyDictionary = CreateObject ("Scripting.Dictionary")Afslut Sub |
Hvis du bruger den tidlige binding, skal du tilføje en reference til biblioteket 'Microsoft Scripting Runtime'
Det gør du ved at vælge ‘Værktøjer | Referencer 'på menulinjen i Visual Basic Editor (VBE) -vinduet og et pop op-vindue vises med en liste over tilgængelige biblioteker.
Rul ned til 'Microsoft Scripting Runtime', og marker afkrydsningsfeltet ved siden af det. Klik på OK, og dette bibliotek er nu en del af dit VBA -projekt og kan refereres til ved hjælp af tidlig binding. Alle eksempler på kode i denne artikel vil bruge tidlig binding.
Din kode kører betydeligt hurtigere med tidlig binding, fordi den er samlet på forhånd. Ved sen binding skal objektet kompileres, mens koden kører
Scripting Runtime -biblioteket har 'Intellisense'. Når du skriver din kode, vil du se lister over tilgængelige metoder og egenskaber, der hjælper med at forhindre fejl i stavefejl, hvilket vil forårsage fejl i dit program
Hvis du også trykker på F2 i VBE og vælger biblioteket ‘Scripting’, vil du se alle tilgængelige metoder og egenskaber og de nødvendige parametre for hver
Distribuering af din Excel -applikation indeholdende en ordbog
Som allerede påpeget er Scripting Runtime -biblioteket ikke en del af Excel VBA, så hvis du distribuerer din applikation til andre brugere, skal de have adgang til Scripting Runtime -biblioteket på deres computer. Hvis de ikke har det, vil der opstå en fejl.
Det er en god idé at inkludere nogle VBA -kode for at kontrollere, at dette bibliotek er til stede, når din Excel -applikation er indlæst. Du kan bruge kommandoen 'Dir' til at gøre dette på 'Workbook Open' -begivenheden
Filens placering er C: \ Windows \ SysWOW64 \ scrrun.dll
Omfang af et ordbogsobjekt
Dictionary -objektet er kun tilgængeligt, mens Excel -projektmappen er åben. Det gemmes ikke, når projektmappen gemmes.
Hvis din ordbog skal være tilgængelig for alle rutiner i dit modul, skal du deklarere den (Dim) i sektionen Deklarere øverst i modulet
Du definerer det som et globalt objekt, hvis du vil have, at din ordbog skal bruges i hele din kode.
| 1 | Global My Dictionary som ny ordbog |
Befolkning og læsning fra din ordbog
Til at begynde med skal du oprette en ordbog, udfylde den med nogle data og derefter gentage den for at bevise, at dataene findes
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.Add "MyItem1", 10MyDictionary.Tilføj "MyItem2", 20MyDictionary.Add "MyItem3", 30For n = 0 til MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Næste nAfslut Sub |
Denne kode opretter et nyt ordbogsobjekt kaldet 'MyDictionary' og udfylder det derefter med tre elementer. Add -metoden har to parametre - nøgle og element, og de er begge påkrævede
Datatyperne for nøgle og element er begge varianter, så de accepterer enhver form for data - numerisk, tekst, dato osv
Det første element i ordbogen kan tilføjes som:
| 1 | MyDictionary.Add 10, "MyItem1" |
Værdierne er vendt om mellem nøgle og element, men dette ville stadig fungere, selvom søgetasten nu ville blive 10.
Det er imidlertid vigtigt at forstå, at nøgleværdien er opslagsværdien i ordbogen. Det fungerer på en meget lignende måde som VLOOKUP -funktionen i Excel. Fordi alle nøgler skal have unikke værdier, kan du angive en nøgleværdi og øjeblikkeligt returnere varens værdi for denne nøgle.
Bemærk, at ordbogsindekset starter ved 0, så du skal trække 1 fra ordbogstællingen, der bruges i For… Next -løkken
Du kan også bruge en For … Hver loop til at læse værdierne i ordbogen:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting. Dictionary, I As VariantMyDictionary.Add "MyItem1", 10MyDictionary.Tilføj "MyItem2", 20MyDictionary.Add "MyItem3", 30For hver I I MyDictionary.TasterMsgBox I & "" & MyDictionary (I)Næste jegAfslut Sub |
Denne kode gentager gennem hvert element og viser varenøglen og varens værdi
Brug af vareindeksnummeret
Du kan bruge indeksnummeret på en nøgle eller et element til at læse værdien
| 123456789101112 | UnderindeksNumre ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.CompareMode = TextCompareMyDictionary.Tilføj "Item1", 10MyDictionary.Tilføj "Item2", 20MyDictionary.Tilføj "Item3", 30MsgBox MyDictionary.Taster (2)MsgBox MyDictionary.Items (1)Afslut Sub |
Denne kode returnerer nøglen ‘item3’, da indekset starter ved 0, og varens værdi 20
Du kan henvise til individuelle nøgle- eller elementværdier inden for nøglerne eller elementernes samlinger ved hjælp af indeksnumrene.
Filtrering af ordbogen
Der er ikke en direkte metode til at gøre dette, men det er ganske enkelt at skrive kode for at gøre det:
| 1234567891011 | Sub FilterDictionary ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.Tilføj "AAItem1", 10MyDictionary.Tilføj "BBItem2", 20MyDictionary.Tilføj "BBItem3", 30For hvert I In Filter (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Næste jegAfslut Sub |
Filterværdien fungerer kun fra begyndelsen af nøgleværdien. Du kan ikke bruge jokertegn i filteret. Denne kode returnerer de to elementværdier med nøglenavne, der begynder med 'BB'
Dette giver dig en delmængde af ordbogen baseret på din filterværdi, som du derefter kan overføre til en anden ordbog eller et regneark. Med omhyggelig planlægning af nøglenavne, og sørg for at der er et meningsfuldt præfiks til hver, ville du let kunne opdele ordbogen i forskellige komponentdele.
Ændring af en artikels værdi for en nøgle
Ordbogsobjektet har en stor fordel i forhold til en samling ved, at varens værdi kan ændres f.eks.
| 1 | MyDictionary ("MyItem4") = "40" |
I samlingen skal du slette denne post og derefter genskabe den.
Her er et kodeeksempel:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.Add "MyItem1", 10MyDictionary.Tilføj "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"For n = 0 til MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Næste nAfslut Sub |
Ovenstående kode opsætter tre elementer i ordbogen og ændrer derefter værdien af 'MyItem2' fra 20 til 25.
Det ændrer også værdien af 'MyItem4' til 40. Bemærk, at der i tilføjelsessætningerne i koden ikke blev tilføjet 'MyItem4'. Når du ændrer værdien af en nøgle, der ikke findes, oprettes den automatisk. Dette er yderst praktisk, da der ikke udløses nogen fejl, men det betyder, at du skal være forsigtig med dine nøglenavne. En utilsigtet stavefejl i nøglenavnet ville betyde, at der oprettes en ny nøgle, og det originale nøglenavn ville stadig have den gamle værdi.
Dette kan let føre til integritetsproblemer i ordbogsobjektet.
Test om der findes en nøgle
Du kan kontrollere, om der findes en nøgleværdi i ordbogen
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.Add "MyItem1", 10MyDictionary.Tilføj "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")Afslut Sub |
Koden tilføjer tre elementer til et nyt ordbogsobjekt og tester derefter for en nøgle ('MyItem8'), som ikke er i ordbogen. Dette returnerer Falsk, men hvis en af de eksisterende nøgler var blevet brugt, ville det returnere True
Jokertegn accepteres ikke. Søgetekst er som standard også store og små bogstaver, men dette kan ændres (se senere i artiklen)
Brug af flere værdier i en ordbog
I modsætning til en matrix er ordbogsobjektet kun en -dimensionelt. Dette kan føre til problemer, hvis du har flere værdier, som du vil sætte mod en nøgle.
En vej rundt er at sammenkæde hver elementværdi ved hjælp af et afgrænsningstegn mellem hver værdi f.eks. '|'
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Opret ordbogsobjekt og variablerDim MyDictionary As New Scripting. Dictionary, V1 As Integer, V2 As StringDim V3 som dato, Temp som streng, N som heltal'Befolk 3 variabler for at demonstrere flere værdierV1 = 5V2 = "Eksempel på flere værdier"V3 = "22-jul-2020"'Føj den sammenkædede værdi til ordbogen ved hjælp af "|" afgrænserMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Fang den sammenkædede ordbogsværdi fra ordbogen til en variabelTemp = MyDictionary ("MyMultipleItem")'Iterere gennem den sammenkædede streng for at adskille de enkelte værdierGør'Find positionen som en afgrænserN = InStr (Temp, "|")'Hvis der ikke er flere afgrænsere, skal du afslutte Do -sløjfenHvis N = 0 Så afslut Do'Vis tekst i forhold til placeringen af afgrænseren fundetMsgBox Venstre (Temp, N - 1)'Afkort den sammenkædede streng til det næste tegn efter afgrænsningen fundetTemp = Midt (Temp, N + 1)SløjfeAfslut Sub |
En anden vej udenom dette problem er at designe dit eget sub-script-system til nøglenavne. Der er ingen grund til, at du ikke skal bruge parenteser og tal i nøglenavne
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Eksempel på flere værdier"MyDictionary.Add "Multiple (3)", "22-Jul-2020"For N = 1 til 3MsgBox MyDictionary ("Multiple (" & N & ")")Næste NAfslut Sub |
Denne kode tilføjer tre nøgler til ordbogen, men hvert tastenavn indeholder et underskriptnummer i parentes. Du kan derefter henvise til nøglenavnet, men ved hjælp af det sub -script -nummer, der er sammenkædet i. Dette ligner meget brug af et array -objekt
Sletning af varer
Du kan fjerne individuelle elementer ved at henvise til nøgleværdien
| 1 | MyDictionary.Remove ("MyItem2") |
Bemærk, at fordi nøglenavne er unikke, fjerner dette kun den ene nøgle og elementværdi
Du kan også rydde ordbogen helt
| 1 | MyDictionary.RemoveAll |
Her er et eksempel på brug af 'Fjern' i VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.Tilføj "Item1", 10MyDictionary.Tilføj "Item2", 20MyDictionary.Tilføj "Item3", 30MyDictionary.Remove ("Item2")For N = 0 Til MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Næste NMyDictionary.RemoveAllMsgBox MyDictionary.CountAfslut Sub |
Koden tilføjer tre elementer til ordbogen og fjerner derefter 'Item2'. Det gentager derefter gennem ordbogen for at bevise, at 'Item2' ikke længere eksisterer
Endelig fjerner koden alle elementer i ordbogen og viser ordbogstællingen, som nu er nul.
Ændring af sagfølsomhed for søgninger
Hvis du søger efter en nøgle, er den som standard følsom mellem store og små bogstaver. Du kan dog bruge egenskaben ‘CompareMode’ til at ændre dette.
Bemærk, at dette skal gøres straks i koden, efter at du har oprettet ordbogsobjektet, men før du tilføjer data til ordbogen. Når sammenligningstilstanden er blevet indstillet, kan den ikke ændres i denne ordbog.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.CompareMode = TextCompareMyDictionary.Tilføj "Item1", 10MyDictionary.Tilføj "Item2", 20MyDictionary.Tilføj "Item3", 30MsgBox MyDictionary.Exists ("item2")Afslut Sub |
I dette eksempel er sammenligningstilstanden sat til 'TextCompare', hvilket betyder, at den ikke er store og små bogstaver. Udtalelsen 'Eksisterer' i slutningen af eksemplet returnerer True, på trods af at søgeteksten alle er med små bogstaver.
I Excel er der kun to værdier, der kan bruges til sammenligningstilstand. Binær sammenligning er store og små bogstaver, og tekst sammenligning er ikke store og små bogstaver
Hvis du har sammenligningstilstand indstillet til Binær sammenligning, skal du være forsigtig med at navngive dine nøgler. Hvis du angiver et navn til at have et stort bogstav som det første tegn, skal du sørge for, at du stadig foretager det første tegn med store bogstaver, når du ændrer værdien. Hvis du starter med en lille bogstav, vil dette blive fortolket som en ny nøgle og kan let føre til forvirring og fejl i din ordbog
Husk, at hvis du ændrer en værdi for en nøgle, og nøglenavnet ikke findes på grund af, at der bruges en binær sammenligning, tilføjes en ny nøgle og værdi til ordbogen.
Hvis du i stedet bruger Tekst sammenligning, vil eventuelle værdiforandringer gå til nøglen uanset sag. Hvis du prøver at tilføje det samme element, men stavet med et andet store bogstaver, får du en fejl, fordi det allerede findes.
Sortering af ordbogen
Som med samleobjektet er der ingen metode til at kunne sortere ordbogen, hverken ved hjælp af nøgler eller elementværdier.
Da VBA -koden sidder i en Excel -projektmappe, kan ordbogsdataene imidlertid overføres til Excel i tabelform, og derefter kan Excel -sorteringsfunktionen anvendes på den. Ordbogen kan derefter ryddes ved hjælp af 'RemoveAll' og de sorterede værdier tilføjet fra regnearket.
Denne kode sorterer både nøglerne og elementernes værdier
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary som ny ordbogDim tæller så længe'Byg ordbog med tilfældige rækkefølgeelementerMyDictionary.Tilføj "Item5", 5MyDictionary.Tilføj "Item2", 15MyDictionary.Tilføj "Item4", 11MyDictionary.Tilføj "Item1", 2MyDictionary.Tilføj "Item3", 19'Fang antal elementer i ordbogen til fremtidig brugTæller = MyDictionary.Count'Skift gennem ordbog og kopier hver nøgle og element til en på hinanden følgende celle på' Ark1 '(kolonne A)For N = 0 Til MyDictionary.Count - 1Ark ("Ark1"). Celler (N + 1, 1) = MyDictionary.Keys (N)Sheets ("Sheet1"). Celler (N + 1, 2) = MyDictionary.Items (N)Næste N'Aktiver Sheet1, og brug Excel -sorteringsrutinen til at sortere dataene i stigende rækkefølgeArk ("Ark1"). AktiverOmråde ("A1: B" og MyDictionary.Count) .VælgActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 -nøgle: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalMed ActiveWorkbook.Worksheets ("Ark1"). Sorter.SetRange -område ("A1: A5").Header = xlGuess.MatchCase = Falsk.Orientation = xlTopToBottom.SortMethod = xlPinYin.AnsøgeAfslut med'Ryd alle elementer fra ordbogenMyDictionary.RemoveAll'Kopier celleværdierne tilbage til det tomme ordbogsobjekt ved hjælp af den lagrede værdi (tæller) for' loop'enFor N = 1 til tællerMyDictionary.Add Sheets ("Sheet1"). Celler (N, 1) .Værdi, Sheets ("Sheet1"). Celler (N, 2) .ValueNæste N'Skift gennem ordbogen for at bevise den rækkefølge, elementerne nu er iFor N = 0 Til MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Næste N'Ryd regnearket (Sheet1) - hvis det er nødvendigt, skal du også slette detArk ("Ark1"). Område (celler (1, 1), celler (tæller, 2)). FjernAfslut Sub |
Denne kode opretter en ordbog med fem tilfældige rækkefølgeværdier tilføjet. Det indfanger antallet af elementer i en variabel og gentager derefter gennem ordbogen og overfører nøgle- og elementværdierne til separate kolonner på et regneark.
Det sorterer derefter det downloadede område ved hjælp af kolonne A som sorteringsfelt. Ordbogen ryddes fuldstændigt ved hjælp af 'RemoveAll' -metoden, og koden gentages derefter gennem celleværdierne i regnearket og tilføjer dem tilbage til ordbogen.
Endelig gentages koden gennem ordbogen og viser nøgle- og elementværdierne sammenkædet for at bevise, at sorteringen har fungeret.
Ved at ændre parametrene i sorteringskoden kunne dataene sorteres efter elementværdier.
Kopiering af en liste over nøgler til et regneark
Du kan kopiere en liste over alle nøgleværdier til et regneark ved hjælp af følgende kode:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.CompareMode = TextCompareMyDictionary.Tilføj "Item1", 10MyDictionary.Tilføj "Item2", 20MyDictionary.Tilføj "Item3", 30Sheets ("Sheet1"). Range ("A1"). Value = Join (MyDictionary.Keys, vbLf)Afslut Sub |
Dette frembringer resultatet i dit regneark:
Du kan kopiere en hel ordbog til et regneark ved hjælp af denne kode:
| 12345678910 | UnderkopiIntoWorksheet ()Dim MyDictionary som nyt scripting. OrdbogMyDictionary.Tilføj "Item1", 10MyDictionary.Tilføj "Item2", 20MyDictionary.Tilføj "Item3", 30Område ("A1"). Tilpas størrelse (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Område ("B1"). Tilpas størrelse (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)Afslut Sub |
Dit regneark vil se sådan ud:
Sammenligning af en ordbog med en samling
Ordbogen er hurtigere end en samling.
En samling er allerede inden for VBA. En ordbog har brug for en reference til Microsoft Scripting Dictionary for at blive tilføjet eller et objekt oprettet ved hjælp af sen binding
En samling kan kun skrives én gang og læses mange gange. I en ordbog kan varens værdi ændres. Med en samling skal varen fjernes, og derefter skal den ændrede vare tilføjes tilbage.
Samlingen arbejder på indeksværdier, hvilket kan være svært at finde ud af, hvilken indeksværdi der hører til. Ordbogen arbejder på unikke nøgleværdier, der bruges til at lokalisere et element
Hentning af et enkelt element er langsommere i en stor samling end i en ordbog
I en samling bruges nøglerne kun til at søge data og kan ikke hentes. I en ordbog kan nøgler testes for eksistens og kan bruges til at finde et bestemt element.
Samlinger er store og små bogstaver, og dette kan ikke ændres. I en ordbog kan sammenligningstilstanden indstilles til at give store eller små bogstaver
I en samling skal nøgleværdierne være strenge. I en ordbog kan de være enhver datatype f.eks. numerisk, dato osv
Fjernelse af alle elementer i en samling indebærer en omdefinering af samlingobjektet. Ordbogen har metoden ‘Fjern alt’ til dette.
