Bruke en VBA -ordbok
En VBA -ordbok fungerer på lignende måte som et samleobjekt, men den har flere egenskaper og metoder, og gir mer fleksibilitet
Ordboken lagrer dataene i minnet og kan enkelt manipuleres. Det er ingen automatisk beregning, bakgrunnssikkerhetskopiering og skjermoppfriskning nødvendig, så koden din vil kjøre betydelig raskere.
Ordbokobjektet fungerer på samme måte som en vanlig ordbok som du vil bruke hvis du vil finne betydningen av et ord. Hver oppføring i ordbokobjektet har en "nøkkel" -verdi og en "element" -verdi. Du bruker ‘nøkkelen’ nøkkelverdien til å slå opp elementverdien i ordbokobjektet, på samme måte som du ville brukt en konvensjonell ordbok.
På grunn av måten ordboksobjektet fungerer på, må nøkkelverdiene alle være unike, på samme måte som i en konvensjonell ordbok. Tenk hvis du åpnet den konvensjonelle ordboken for å slå opp betydningen av et ord, og fant ordet som er oppført mer enn én gang med to helt forskjellige definisjoner. Du ville bli veldig forvirret!
Nøkkelverdier er vanligvis tekst eller tall, eller begge deler. Brukere synes ofte det er lettere å huske navn på nøkler som tekst i stedet for bare tall.
I sammenligning med et samlingsobjekt er samlingsobjektet skrivebeskyttet. Den har bare to metoder (Legg til og fjern) og to egenskaper (Count og Item). Når et element er lagt til i et samleobjekt, kan det bare fjernes, men ikke redigeres, noe som er en tungvint prosedyre hvis verdien av et element må endres.
Et ordbokobjekt endres automatisk i størrelse for å passe til antallet elementer i det. Det trenger ikke å være definert i størrelse, som en konvensjonell matrise
Ordbokobjektet er endimensjonalt og datatypen er ‘Variant’, så enhver datatype kan legges inn i den f.eks. numerisk, tekst, dato
VBA -ordboken er ikke hjemmehørende i Excel, og må nås enten ved tidlig eller sen binding når du definerer ordbokobjektet
| 123 | Sub EarlyBindingExample ()Dim MyDictionary As New Scripting.DictionarySlutt Sub |
| 1234 | Sub LateBindingExample ()Dim MyDictionary Som objektSett MyDictionary = CreateObject ("Scripting.Dictionary")Slutt Sub |
Hvis du bruker den tidlige bindingen, må du legge til en referanse til biblioteket "Microsoft Scripting Runtime"
Du gjør dette ved å velge ‘Verktøy | Referanser på menylinjen i Visual Basic Editor (VBE) -vinduet og et popup-vindu vil vises med en liste over tilgjengelige biblioteker.
Rull ned til ‘Microsoft Scripting Runtime’ og merk av i boksen ved siden av. Klikk OK, og dette biblioteket er nå en del av ditt VBA -prosjekt og kan refereres til ved hjelp av tidlig binding. Alle eksempler på kode i denne artikkelen vil bruke tidlig binding.
Koden din vil kjøre betydelig raskere med tidlig binding, fordi den er samlet på forhånd. Ved sen binding må objektet kompileres mens koden kjører
Scripting Runtime -biblioteket har ‘Intellisense’. Når du skriver koden din, vil du se lister over tilgjengelige metoder og egenskaper som hjelper deg med å forhindre feil i stavemåten, noe som vil forårsake feil i programmet
Hvis du også trykker på F2 i VBE, og velger "Scripting" -biblioteket, vil du se alle tilgjengelige metoder og egenskaper og parametrene som kreves for hver
Distribuere Excel -applikasjonen din som inneholder en ordbok
Som allerede påpekt, er Scripting Runtime -biblioteket ikke en del av Excel VBA, så hvis du distribuerer applikasjonen din til andre brukere, må de ha tilgang til Scripting Runtime -biblioteket på datamaskinen. Hvis de ikke har det, vil det oppstå en feil.
Det er en god idé å inkludere noen VBA -kode for å kontrollere at dette biblioteket er tilstede når Excel -programmet er lastet. Du kan bruke "Dir" -kommandoen til å gjøre dette på "Workbook Open" -hendelsen
Plasseringen av filen er C: \ Windows \ SysWOW64 \ scrrun.dll
Omfang av et ordbokobjekt
Ordbok -objektet er bare tilgjengelig mens Excel -arbeidsboken er åpen. Den blir ikke lagret når arbeidsboken er lagret.
Hvis ordboken din skal være tilgjengelig for alle rutinene i modulen din, må du deklarere den (Dim) i delen Deklarere helt øverst i modulen
Du definerer det som et globalt objekt hvis du vil at ordboken skal brukes i hele koden.
| 1 | Global My Dictionary som ny ordbok |
Befolkning og lesing fra ordboken din
Til å begynne med må du opprette en ordbok, fylle den med noen data, og deretter gjenta den for å bevise at dataene eksisterer
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Legg til "MyItem1", 10MyDictionary.Legg til "MyItem2", 20MyDictionary.Legg til "MyItem3", 30For n = 0 til MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Neste nSlutt Sub |
Denne koden oppretter et nytt ordbokobjekt kalt 'MyDictionary' og fyller det deretter ut med tre elementer. Add -metoden har to parametere - nøkkel og element, og de er begge påkrevde
Datatypene for nøkkel og element er begge varianter, så de godtar alle typer data - numerisk, tekst, dato osv
Det første elementet i ordlisten kan legges til som:
| 1 | MyDictionary.Add 10, "MyItem1" |
Verdiene er reversert mellom nøkkel og element, men dette vil fortsatt fungere, selv om søketasten nå ville bli 10.
Det er imidlertid viktig å forstå at nøkkelverdien er oppslagsverdien i ordboken. Det fungerer på en veldig lignende måte som VLOOKUP -funksjonen i Excel. Fordi alle nøklene må ha unike verdier, kan du angi en nøkkelverdi og umiddelbart returnere varen for denne nøkkelen.
Vær oppmerksom på at ordbokindeksen starter på 0, så du må trekke 1 fra ordlistetellingen som brukes i For… Next -løkken
Du kan også bruke en For … Hver sløyfe til å lese verdiene i ordboken:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting. Dictionary, I As VariantMyDictionary.Legg til "MyItem1", 10MyDictionary.Legg til "MyItem2", 20MyDictionary.Legg til "MyItem3", 30For hver I In MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Neste jegSlutt Sub |
Denne koden går gjennom hvert element og viser varenøkkelen og varens verdi
Bruke vareindeksnummeret
Du kan bruke indeksnummeret til en nøkkel eller et element for å lese verdien
| 123456789101112 | DelindeksNumre ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Legg til "Item1", 10MyDictionary.Legg til "Item2", 20MyDictionary.Legg til "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)Slutt Sub |
Denne koden returnerer nøkkelen ‘item3’ ettersom indeksen starter på 0, og elementverdien 20
Du kan referere til individuelle nøkkel- eller elementverdier i nøklene eller elementene ved å bruke indeksnumrene.
Filtrering av ordboken
Det er ikke en direkte metode for å gjøre dette, men det er ganske enkelt å skrive kode for å gjøre det:
| 1234567891011 | Sub FilterDiction ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Legg til "AAItem1", 10MyDictionary.Legg til "BBItem2", 20MyDictionary.Legg til "BBItem3", 30For hvert I In Filter (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Neste jegSlutt Sub |
Filterverdien fungerer bare fra begynnelsen av nøkkelverdien. Du kan ikke bruke jokertegn i filteret. Denne koden returnerer de to elementverdiene med nøkkelnavn som begynner med 'BB'
Dette vil gi deg et delsett av ordlisten basert på filterverdien din, som du deretter kan overføre til en annen ordbok eller et regneark. Med nøye planlegging av viktige navn, og sørg for at det er et meningsfullt prefiks for hver, ville du enkelt kunne dele ordboken i forskjellige komponentdeler.
Endre en varens verdi for en nøkkel
Ordbokobjektet har en stor fordel i forhold til en samling ved at vareverdien kan endres f.eks.
| 1 | MyDictionary ("MyItem4") = "40" |
I samlingen må du slette den oppføringen og deretter lage den på nytt.
Her er et kodeeksempel:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Legg til "MyItem1", 10MyDictionary.Legg til "MyItem2", 20MyDictionary.Legg til "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"For n = 0 til MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Neste nSlutt Sub |
Koden ovenfor setter opp tre elementer i ordlisten, og endrer deretter verdien til ‘MyItem2’ fra 20 til 25.
Det endrer også verdien av ‘MyItem4’ til 40. Vær oppmerksom på at det ikke ble lagt til ‘MyItem4’ i tilleggsetningene i koden. Når du endrer verdien til en nøkkel som ikke eksisterer, opprettes den automatisk. Dette er ekstremt praktisk, siden ingen feil utløses, men det betyr at du må være forsiktig med nøkkelnavnene dine. En utilsiktet stavefeil i nøkkelnavnet vil bety at en ny nøkkel opprettes, og det opprinnelige nøkkelnavnet vil fortsatt ha den gamle verdien.
Dette kan lett føre til integritetsproblemer i ordbokobjektet.
Test om det finnes en nøkkel
Du kan kontrollere om det finnes en nøkkelverdi i ordlisten
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Legg til "MyItem1", 10MyDictionary.Legg til "MyItem2", 20MyDictionary.Legg til "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")Slutt Sub |
Koden legger til tre elementer i et nytt ordbokobjekt, og tester deretter for en nøkkel ('MyItem8') som ikke er i ordlisten. Dette returnerer False, men hadde en av de eksisterende nøklene blitt brukt, ville det returnere True
Jokertegn godtas ikke. Søketekst er også skift mellom store og små bokstaver, men dette kan endres (se senere i artikkelen)
Bruke flere verdier i en ordbok
I motsetning til en matrise er ordboksobjektet bare en dimensjonal. Dette kan føre til problemer hvis du har flere verdier du vil sette mot en nøkkel.
En vei rundt dette er å sammenkoble hver elementverdi ved å bruke et skilletegn mellom hver verdi f.eks. '|'
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Lag ordlisteobjekt og variablerDim MyDictionary As New Scripting.Dictionary, V1 As Integer, V2 As StringDim V3 As Date, Temp As String, N As Integer'Fyll ut 3 variabler for å demonstrere flere verdierV1 = 5V2 = "Eksempel på flere verdier"V3 = "22. juli 2020"'Legg til den sammenkoblede verdien i ordlisten ved å bruke "|" avgrensningMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Fang den sammenkoblede ordbokverdien fra ordlisten til en variabelTemp = MyDictionary ("MyMultipleItem")'Skift gjennom den sammenkoblede strengen for å skille de enkelte verdieneGjøre'Finn posisjonen til en skilletegnN = InStr (Temp, "|")'Hvis det ikke er flere avgrensere, må du gå ut av sløyfenHvis N = 0, avslutter du Do'Vis tekst i forhold til plasseringen av skilletegn funnetMsgBox Venstre (Temp, N - 1)'Avkort den sammenkoblede strengen til neste tegn etter at skilletegnet er funnetTemp = Midt (Temp, N + 1)LøkkeSlutt Sub |
En annen vei rundt dette problemet er å designe ditt eget underskriptsystem for viktige navn. Det er ingen grunn til at du ikke skal bruke parenteser og tall i nøkkelnavn
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Eksempel på flere verdier"MyDictionary.Add "Multiple (3)", "22-Jul-2020"For N = 1 til 3MsgBox MyDictionary ("Multiple (" & N & ")")Neste NSlutt Sub |
Denne koden legger til tre nøkler i ordlisten, men hvert tastenavn inneholder et underskriptnummer i parentes. Du kan deretter referere til nøkkelenavnet, men ved å bruke subskriptnummeret sammenkoblet. Dette ligner veldig på å bruke et matriseobjekt
Sletter elementer
Du kan fjerne individuelle elementer ved å referere til nøkkelverdien
| 1 | MyDictionary.Remove ("MyItem2") |
Vær oppmerksom på at fordi nøkkelenavn er unike, fjerner dette bare den bestemte nøkkelen og elementverdien
Du kan også slette ordboken helt
| 1 | MyDictionary.RemoveAll |
Her er et eksempel på bruk av ‘Fjern’ i VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Legg til "Item1", 10MyDictionary.Legg til "Item2", 20MyDictionary.Legg til "Item3", 30MyDictionary.Remove ("Item2")For N = 0 Til MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Neste NMyDictionary.RemoveAllMsgBox MyDictionary.CountSlutt Sub |
Koden legger til tre elementer i ordlisten, og fjerner deretter "Item2". Deretter går det gjennom ordboken for å bevise at "Item2" ikke lenger eksisterer
Til slutt fjerner koden alle elementene i ordlisten og viser ordboktallet, som nå er null.
Endre saksfølsomhet for søk
Hvis du søker etter en nøkkel, er den skiftende mellom store og små bokstaver. Du kan imidlertid bruke egenskapen ‘CompareMode’ til å endre dette.
Vær oppmerksom på at dette må gjøres umiddelbart i koden etter at du har opprettet ordbokobjektet, men før du legger til data i ordlisten. Når sammenligningsmodusen er angitt, kan den ikke endres i ordlisten.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Legg til "Item1", 10MyDictionary.Legg til "Item2", 20MyDictionary.Legg til "Item3", 30MsgBox MyDictionary.Exists ("item2")Slutt Sub |
I dette eksemplet er sammenligningsmodusen satt til ‘TextCompare’, noe som betyr at den ikke er store og små bokstaver. 'Eksisterer' -utsagnet på slutten av eksemplet vil returnere True, til tross for at søketeksten er liten.
I Excel er det bare to verdier som kan brukes til sammenligningsmodus. Binær sammenligning er store og små bokstaver
Hvis du har sammenligningsmodus satt til Binær sammenligning, må du være forsiktig når du navngir nøklene dine. Hvis du angir et navn for å ha en stor bokstav som det første tegnet, må du sørge for at du fortsatt gjør det første tegnet til store bokstaver når du endrer verdien. Hvis du starter med små bokstaver, blir dette tolket som en ny nøkkel, og kan lett føre til forvirring og feil i ordlisten din
Husk at hvis du endrer en verdi for en nøkkel og nøkkelnavnet ikke eksisterer på grunn av at en binær sammenligning brukes, vil en ny nøkkel og verdi bli lagt til i ordlisten.
Hvis du bruker Text Compare i stedet, vil eventuelle verdiendringer gå til nøkkelen uavhengig av bokstav. Hvis du prøver å legge til det samme elementet, men stavet med et annet sakstegn, får du en feil fordi det allerede eksisterer.
Sortering av ordboken
Som med samleobjektet, er det ingen metode for å kunne sortere ordlisten, verken ved hjelp av nøkler eller elementverdier.
Siden VBA -koden sitter i en Excel -arbeidsbok, kan imidlertid ordbokdataene overføres til Excel i tabellform, og deretter kan Excel -sorteringsfunksjonen brukes på den. Ordlisten kan deretter tømmes ved hjelp av ‘RemoveAll’ og de sorterte verdiene som legges til fra regnearket.
Denne koden vil sortere både nøklene og elementverdiene
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary As New DictionaryDim Counter As Long'Bygg ordbok med tilfeldige rekkefølgeelementerMyDictionary.Legg til "Item5", 5MyDictionary.Legg til "Item2", 15MyDictionary.Legg til "Item4", 11MyDictionary.Legg til "Item1", 2MyDictionary.Legg til "Item3", 19'Fang antall elementer i ordboken for fremtidig brukTeller = MyDictionary.Count'Gjenta gjennom ordlisten og kopier hver nøkkel og element til en påfølgende celle på' Ark1 '(kolonne A)For N = 0 Til MyDictionary.Count - 1Sheets ("Sheet1"). Celler (N + 1, 1) = MyDictionary.Keys (N)Sheets ("Sheet1"). Celler (N + 1, 2) = MyDictionary.Items (N)Neste N'Aktiver Sheet1 og bruk Excel -sorteringsrutinen for å sortere dataene i stigende rekkefølgeArk ("Ark1"). AktiverOmråde ("A1: B" og MyDictionary.Count) .VelgActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = 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.Søke omSlutt med'Fjern alle elementene fra ordlistenMyDictionary.RemoveAll'Kopier celleverdiene tilbake til det tomme ordbokobjektet ved å bruke den lagrede verdien (telleren) for' loop 'For N = 1 å telleMyDictionary.Add Sheets ("Sheet1"). Celler (N, 1) .Value, Sheets ("Sheet1"). Cells (N, 2) .ValueNeste N'Skift gjennom ordboken for å bevise rekkefølgen elementene nå er iFor N = 0 Til MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Neste N'Fjern regnearket (ark1) - slett det om nødvendig ogsåArk ("Ark1"). Rekkevidde (celler (1, 1), celler (teller, 2)). FjernSlutt Sub |
Denne koden oppretter en ordbok med fem tilfeldige rekkefølgeverdier lagt til. Den registrerer antall elementer i en variabel, og går deretter gjennom ordlisten og overfører nøkkel- og elementverdiene til separate kolonner i et regneark.
Den sorterer deretter det nedlastede området, ved hjelp av kolonne A som sorteringsfelt. Ordlisten tømmes fullstendig ved hjelp av "RemoveAll" -metoden, og koden går deretter gjennom celleverdiene i regnearket og legger dem tilbake til ordlisten.
Til slutt gjentar koden seg gjennom ordlisten, og viser nøkkel- og elementverdiene sammenkoblet for å bevise at sorteringen har fungert.
Ved å endre parametrene i sorteringskoden, kan dataene sorteres etter elementverdier.
Kopiere en liste over nøkler til et regneark
Du kan kopiere en liste over alle nøkkelverdier til et regneark ved å bruke følgende kode:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Legg til "Item1", 10MyDictionary.Legg til "Item2", 20MyDictionary.Legg til "Item3", 30Ark ("Ark1"). Område ("A1"). Verdi = Bli med (MyDictionary.Keys, vbLf)Slutt Sub |
Dette gir resultatet i regnearket ditt:
Du kan kopiere en hel ordbok til et regneark ved å bruke denne koden:
| 12345678910 | UnderkopiIntoWorksheet ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Legg til "Item1", 10MyDictionary.Legg til "Item2", 20MyDictionary.Legg til "Item3", 30Område ("A1"). Endre størrelse (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Område ("B1"). Endre størrelse (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)Slutt Sub |
Regnearket ditt vil se slik ut:
Sammenligning av en ordbok med en samling
Ordboken er raskere enn en samling.
En samling er allerede innenfor VBA. En ordbok trenger en referanse til Microsoft Scripting Dictionary for å legges til eller et objekt opprettes ved hjelp av sen binding
Et samleobjekt kan bare skrives én gang og leses mange ganger. I en ordbok kan elementverdien endres. Med en samling må elementet fjernes og det endrede elementet legges tilbake.
Samlingen jobber med indeksverdier, noe som kan være vanskelig å finne ut hvilken indeksverdi som hører hjemme. Ordboken jobber med unike nøkkelverdier som brukes til å finne et element
Å hente et enkelt element er tregere i en stor samling enn i en ordbok
I en samling brukes nøklene bare til å slå opp data og kan ikke hentes. I en ordbok kan nøkler testes for eksistens, og kan brukes til å finne et bestemt element.
Samlinger er store og små bokstaver, og dette kan ikke endres. I en ordbok kan sammenligningsmodus settes til å gi store eller små bokstaver
I en samling må nøkkelverdiene være strenger. I en ordbok kan de være hvilken som helst datatype f.eks. numerisk, dato osv
Hvis du fjerner alle elementene i en samling, må du definere samlingsobjektet på nytt. Ordboken har "RemoveAll" -metoden for dette.
