Använda en VBA -ordbok
En VBA -ordbok fungerar på ett liknande sätt som ett samlingsobjekt, men den har fler egenskaper och metoder och erbjuder mer flexibilitet
Ordboken lagrar data i minnet och kan enkelt manipuleras. Det krävs ingen automatisk beräkning, bakgrundssäkerhetskopiering och skärmuppfräschning, så din kod körs betydligt snabbare.
Ordboksobjektet fungerar på ett liknande sätt som en vanlig ordbok som du skulle använda om du vill ta reda på innebörden av ett ord. Varje post i ordboksobjektet har ett "nyckel" -värde och ett "objekt" -värde. Du använder "nyckeln" nyckelvärdet för att leta upp objektvärdet i ordboksobjektet, på liknande sätt som du skulle använda en konventionell ordbok.
På grund av hur ordboksobjektet fungerar måste alla nyckelvärden vara unika, på samma sätt som i en konventionell ordbok. Tänk om du öppnade din konventionella ordbok för att leta upp betydelsen av ett ord och hittade ordet listat mer än en gång med två helt olika definitioner. Du skulle bli väldigt förvirrad!
Nyckelvärden är vanligtvis text eller siffror, eller båda. Användare har ofta lättare att komma ihåg namn på nycklar som text snarare än bara siffror.
I jämförelse med ett samlingsobjekt är samlingsobjektet skrivskyddat. Den har bara två metoder (Lägg till och ta bort) och två egenskaper (Count and Item). När ett objekt har lagts till i ett samlingsobjekt kan det bara tas bort, men inte redigeras, vilket är en besvärlig procedur om värdet på ett objekt behöver ändras.
Ett ordboksobjekt ändras automatiskt i storlek för att passa antalet objekt i det. Det behöver inte definieras i storlek, som en konventionell array
Ordboksobjektet är endimensionellt och datatypen är ‘Variant’, så vilken datatyp som helst kan matas in i den t.ex. numeriskt, text, datum
VBA -ordlistan är inte infödd till Excel och måste nås antingen tidigt eller sent när man definierar ordlistans objekt
| 123 | Sub EarlyBindingExample ()Dim MyDictionary As New Scripting.DictionaryAvsluta Sub |
| 1234 | Sub LateBindingExample ()Dim MyDictionary Som objektStäll in MyDictionary = CreateObject ("Scripting.Dictionary")Avsluta Sub |
Om du använder den tidiga bindningen måste du lägga till en referens till biblioteket "Microsoft Scripting Runtime"
Du gör detta genom att välja "Verktyg | Referenser på menyraden i Visual Basic Editor (VBE) -fönstret och ett popup-fönster visas med en lista över tillgängliga bibliotek.
Rulla ner till "Microsoft Scripting Runtime" och markera rutan bredvid den. Klicka på OK och det här biblioteket är nu en del av ditt VBA -projekt och kan refereras med hjälp av tidig bindning. Alla exempel på kod i denna artikel kommer att använda tidig bindning.
Din kod körs betydligt snabbare med tidig bindning, eftersom den är sammanställd på förhand. Med sen bindning måste objektet kompileras när koden körs
Scripting Runtime -biblioteket har "Intellisense". När du skriver din kod ser du listor över tillgängliga metoder och egenskaper som hjälper till att förhindra fel i stavningen, vilket kan orsaka fel i ditt program
Om du också trycker på F2 i VBE och väljer biblioteket ‘Scripting’ ser du alla tillgängliga metoder och egenskaper och parametrarna som krävs för varje
Distribuera din Excel -applikation som innehåller en ordbok
Som redan påpekats är Scripting Runtime -biblioteket inte en del av Excel VBA, så om du distribuerar din applikation till andra användare måste de ha åtkomst till Scripting Runtime -biblioteket på sin dator. Om de inte har det uppstår ett fel.
Det är en bra idé att inkludera lite VBA -kod för att kontrollera att det här biblioteket finns när ditt Excel -program laddas. Du kan använda kommandot ‘Dir’ för att göra detta under händelsen ‘Workbook Open’
Filens plats är C: \ Windows \ SysWOW64 \ scrrun.dll
Omfattning av ett ordboksobjekt
Dictionary -objektet är bara tillgängligt när Excel -arbetsboken är öppen. Det sparas inte när arbetsboken sparas.
Om din ordbok ska vara tillgänglig för alla rutiner i din modul måste du deklarera den (Dim) i avsnittet Deklarera högst upp i modulen
Du definierar det som ett globalt objekt om du vill att din ordbok ska användas i hela din kod.
| 1 | Global My Dictionary som ny ordlista |
Befolkning och läsning från din ordbok
Till att börja med måste du skapa en ordlista, fylla i den med vissa data och sedan iterera igenom den för att bevisa att data finns
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Lägg till "MyItem2", 20MyDictionary.Add "MyItem3", 30För n = 0 till MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Nästa nAvsluta Sub |
Denna kod skapar ett nytt ordboksobjekt som heter 'MyDictionary' och fyller det sedan med tre objekt. Metoden Lägg till har två parametrar - nyckel och artikel, och de krävs båda
Datatyperna för nyckel och artikel är båda varianter så de accepterar alla typer av data - numeriskt, text, datum, etc.
Det första objektet i ordlistan kan läggas till som:
| 1 | MyDictionary.Add 10, "MyItem1" |
Värdena har omkastats mellan nyckel och objekt, men detta skulle fortfarande fungera, även om söknyckeln nu skulle bli 10.
Det är dock viktigt att förstå att nyckelvärdet är uppslagsvärdet i ordlistan. Det fungerar på ett mycket liknande sätt som VLOOKUP -funktionen i Excel. Eftersom alla nycklar måste ha unika värden kan du ange ett nyckelvärde och direkt returnera artikelvärdet för den nyckeln.
Observera att ordlistans index börjar med 0 så du måste subtrahera 1 från ordlistan som används i For… Next -slingan
Du kan också använda en för … Varje loop för att läsa värdena i ordlistan:
| 1234567891011 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting. Dictionary, I As VariantMyDictionary.Add "MyItem1", 10MyDictionary.Lägg till "MyItem2", 20MyDictionary.Add "MyItem3", 30För varje I In MyDictionary.KeysMsgBox I & "" & MyDictionary (I)Nästa jagAvsluta Sub |
Denna kod kommer att iterera genom varje objekt och visa artikelnyckeln och artikelvärdet
Använda artikelindexnumret
Du kan använda indexnumret för en nyckel eller ett objekt för att läsa värdet
| 123456789101112 | Sub IndexNumbers ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Lägg till "Item1", 10MyDictionary.Lägg till "Item2", 20MyDictionary.Lägg till "Item3", 30MsgBox MyDictionary.Keys (2)MsgBox MyDictionary.Items (1)Avsluta Sub |
Denna kod returnerar nyckeln ‘item3’ när indexet börjar vid 0 och artikelvärdet 20
Du kan hänvisa till enskilda nyckel- eller artikelvärden i nycklarna eller artiklarna med hjälp av indexnumren.
Filtrera ordlistan
Det finns ingen direkt metod för att göra detta, men det är ganska enkelt att skriva kod för att göra det:
| 1234567891011 | Sub FilterDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Lägg till "AAItem1", 10MyDictionary.Lägg till "BBItem2", 20MyDictionary.Lägg till "BBItem3", 30För varje I In Filter (MyDictionary.Keys, "BB")MsgBox MyDictionary.Item (I)Nästa jagAvsluta Sub |
Filtervärdet fungerar bara från början av nyckelvärdet. Du kan inte använda jokertecken i filtret. Denna kod returnerar de två objektvärdena med nyckelnamn som börjar med 'BB'
Detta ger dig en delmängd av ordlistan baserat på ditt filtervärde, som du sedan kan överföra till en annan ordlista eller ett kalkylblad. Med noggrann planering av nyckelnamn och se till att det finns ett meningsfullt prefix till varje, skulle du enkelt kunna dela upp ordlistan i olika komponentdelar.
Ändra ett objektvärde för en nyckel
Ordboksobjektet har en stor fördel gentemot en samling genom att artikelvärdet kan ändras t.ex.
| 1 | MyDictionary ("MyItem4") = "40" |
I samlingen måste du ta bort den posten och sedan skapa den igen.
Här är ett exempel på kod:
| 12345678910111213 | Sub PopulateReadDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Lägg till "MyItem2", 20MyDictionary.Add "MyItem3", 30MyDictionary ("MyItem2") = "25"MyDictionary ("MyItem4") = "40"För n = 0 till MyDictionary.Count - 1MsgBox MyDictionary.Keys (n) & "" & MyDictionary.Items (n)Nästa nAvsluta Sub |
Ovanstående kod ställer in tre objekt i ordlistan och ändrar sedan värdet på ‘MyItem2’ från 20 till 25.
Det ändrar också värdet på ‘MyItem4’ till 40. Observera att i kodens tilläggsuttalanden tillkom ingen ‘MyItem4’. När du ändrar värdet på en nyckel som inte finns skapas den automatiskt. Detta är extremt bekvämt, eftersom inget fel utlöses, men det betyder att du måste vara försiktig med dina nyckelnamn. Ett oavsiktligt stavfel i nyckelnamnet skulle innebära att en ny nyckel skapas, och det ursprungliga nyckelnamnet skulle fortfarande ha det gamla värdet.
Detta kan lätt leda till integritetsproblem i ordboksobjektet.
Testa om en nyckel finns
Du kan kontrollera om det finns ett nyckelvärde i ordlistan
| 123456789 | Sub CheckExistsDictionary ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "MyItem1", 10MyDictionary.Lägg till "MyItem2", 20MyDictionary.Add "MyItem3", 30MsgBox MyDictionary.Exists ("MyItem8")Avsluta Sub |
Koden lägger till tre objekt i ett nytt ordboksobjekt och testar sedan om en nyckel ('MyItem8') som inte finns i ordlistan. Detta returnerar False, men hade en av de befintliga nycklarna använts skulle det returnera True
Jokertecken accepteras inte. Söktexten är också skiftlägeskänslig som standard, men detta kan ändras (se senare i artikeln)
Använda flera värden i en ordbok
Till skillnad från en matris är ordboksobjektet endast en dimensionellt. Detta kan leda till problem om du har flera värden som du vill sätta mot en nyckel.
En väg runt detta är att sammanfoga varje artikelvärde med hjälp av ett avgränsande tecken mellan varje värde t.ex. ‘|’
| 12345678910111213141516171819202122232425262728293031323334 | Sub MultipleValues ()'Skapa ordboksobjekt och variablerDim MyDictionary As New Scripting.Dictionary, V1 As Integer, V2 As StringDim V3 As Date, Temp As String, N As Integer'Fyll i tre variabler för att visa flera värdenV1 = 5V2 = "Exempel på flera värden"V3 = "22-jul-2020"'Lägg till det sammanlänkade värdet i ordlistan med "|" avgränsareMyDictionary.Add "MyMultipleItem", V1 & "|" & V2 & "|" & V3 & "|"'Fånga det sammanlänkade ordlistans värde från ordlistan till en variabelTemp = MyDictionary ("MyMultipleItem")'Iterera genom den sammanfogade strängen för att separera de enskilda värderingarnaDo'Hitta positionen för en avgränsareN = InStr (Temp, "|")'Om det inte finns fler avgränsare, avsluta Do loopOm N = 0 Avsluta Gör'Visa text i förhållande till avgränsarens positionMsgBox vänster (Temp, N - 1)'Avkorta den sammanfogade strängen till nästa tecken efter att avgränsaren hittatsTemp = Mid (Temp, N + 1)SlingaAvsluta Sub |
En annan väg runt detta problem är att designa ditt eget sub-scriptsystem för nyckelnamn. Det finns ingen anledning till att du inte ska använda parenteser och siffror i nyckelnamn
| 1234567891011 | Sub MultipleValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Add "Multiple (1)", 5MyDictionary.Add "Multiple (2)", "Exempel på flera värden"MyDictionary.Add "Multiple (3)", "22-Jul-2020"För N = 1 till 3MsgBox MyDictionary ("Multiple (" & N & ")")Nästa NAvsluta Sub |
Denna kod lägger till tre nycklar till ordlistan, men varje nyckelnamn innehåller ett subskriptnummer inom parentes. Du kan sedan hänvisa till nyckelnamn, men med hjälp av det subskriptnummer som är sammanfogat i. Detta liknar mycket att använda ett arrayobjekt
Radera objekt
Du kan ta bort enskilda objekt med hänvisning till nyckelvärdet
| 1 | MyDictionary.Remove ("MyItem2") |
Observera att eftersom nyckelnamn är unika tar detta bara bort den ena nyckeln och objektvärdet
Du kan också rensa ordlistan helt
| 1 | MyDictionary.RemoveAll |
Här är ett exempel på hur du använder "Ta bort" i VBA:
| 12345678910111213141516 | Sub RemoveValues ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Lägg till "Item1", 10MyDictionary.Lägg till "Item2", 20MyDictionary.Lägg till "Item3", 30MyDictionary.Remove ("Item2")För N = 0 till MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Nästa NMyDictionary.RemoveAllMsgBox MyDictionary.CountAvsluta Sub |
Koden lägger till tre objekt i ordlistan och tar sedan bort "Item2". Det iterates sedan genom ordlistan för att bevisa att "Item2" inte längre existerar
Slutligen tar koden bort alla objekt i ordlistan och visar ordlistan, som nu är noll.
Ändra skiftlägeskänslighet för sökningar
Om du söker efter en nyckel är den skiftlägeskänslig som standard. Du kan dock använda egenskapen ‘CompareMode’ för att ändra detta.
Observera att detta måste göras omedelbart i koden efter att du har skapat ordboksobjektet, men innan du lägger till data i ordlistan. När jämförelseläget har ställts in kan det inte ändras i den ordlistan.
| 12345678910 | Sub ChangeCaseSensitivity ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Lägg till "Item1", 10MyDictionary.Lägg till "Item2", 20MyDictionary.Lägg till "Item3", 30MsgBox MyDictionary.Exists ("item2")Avsluta Sub |
I det här exemplet är jämförelseläget inställt på 'TextCompare' vilket innebär att det inte är skiftlägeskänsligt. Påståendet "Existerar" i slutet av exemplet kommer att returnera True, trots att söktexten är liten.
I Excel finns det bara två värden som kan användas för jämförelseläge. Binär jämförelse är skiftlägeskänslig och textjämförelse är inte skiftlägeskänslig
Om du har jämförläge inställt på Binär jämförelse måste du vara försiktig när du namnger dina nycklar. Om du anger ett namn för att ha en stor bokstav som första tecken, måste du se till att du fortfarande gör det första tecknet med stora bokstäver när du ändrar värdet. Om du börjar med en liten bokstav tolkas detta som en ny nyckel och kan lätt leda till förvirring och fel i din ordbok
Kom ihåg att om du ändrar ett värde för en nyckel och nyckelnamnet inte finns på grund av att en binär jämförelse används, kommer en ny nyckel och ett värde att läggas till i ordlistan.
Om du använder Textjämförelse istället går alla värdeändringar till nyckeln oavsett fall. Om du försöker lägga till samma objekt men stavas med ett annat bokstavstecken får du ett felmeddelande eftersom det redan finns.
Sortera ordlistan
Som med samlingsobjektet finns det ingen metod för att kunna sortera ordlistan, antingen med hjälp av nycklar eller objektvärden.
Men eftersom VBA -koden sitter i en Excel -arbetsbok kan ordlistans data överföras till Excel i tabellform och sedan kan Excel -sorteringsfunktionen tillämpas på den. Ordboken kan sedan rensas med "RemoveAll" och de sorterade värdena läggs till från kalkylbladet.
Denna kod kommer att sortera både nycklarna och objektens värden
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMyDictionary ()Dim MyDictionary As New DictionaryDim Counter As Long'Skapa ordlista med slumpmässiga ordningsobjektMyDictionary.Lägg till "Item5", 5MyDictionary.Lägg till "Item2", 15MyDictionary.Lägg till "Item4", 11MyDictionary.Lägg till "Item1", 2MyDictionary.Lägg till "Item3", 19'Fånga antal objekt i ordlistan för framtida brukRäknare = MyDictionary.Count'Iterera genom ordlista och kopiera varje nyckel och objekt till en på varandra följande cell på' Sheet1 '(kolumn A)För N = 0 till MyDictionary.Count - 1Sheets ("Sheet1"). Celler (N + 1, 1) = MyDictionary.Keys (N)Sheets ("Sheet1"). Celler (N + 1, 2) = MyDictionary.Items (N)Nästa N'Aktivera Sheet1 och använd Excel -sorteringsrutinen för att sortera data i stigande ordningArk ("Ark1"). AktiveraOmråde ("A1: B" och MyDictionary.Count) .VäljActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range (_"A1: A5"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _xlSortNormalMed ActiveWorkbook.Worksheets ("Sheet1"). Sortera.SetRange -intervall ("A1: A5").Header = xlGuess.MatchCase = Falskt.Orientation = xlTopToBottom.SortMethod = xlPinYin.TillämpaSluta med'Rensa alla objekt från ordlistanMyDictionary.RemoveAll'Kopiera cellvärdena tillbaka till det tomma ordboksobjektet med hjälp av det lagrade värdet (räknare) för' slinganFör N = 1 till räknareMyDictionary.Add Sheets ("Sheet1"). Celler (N, 1) .Value, Sheets ("Sheet1"). Cells (N, 2) .ValueNästa N'Iterera genom ordlistan för att bevisa den ordning som objekten nu finns iFör N = 0 till MyDictionary.Count - 1MsgBox MyDictionary.Keys (N) & "" & MyDictionary.Items (N)Nästa N'Rensa kalkylbladet (blad 1) - ta bort det vid behov ocksåSheets ("Sheet1"). Range (Cells (1, 1), Cells (Counter, 2)). ClearAvsluta Sub |
Denna kod skapar en ordlista med fem slumpmässiga ordningsvärden tillagda. Det fångar upp antalet objekt i en variabel och går sedan igenom ordboken och överför nyckel- och objektvärdena till separata kolumner i ett kalkylblad.
Det sorterar sedan det nedladdade intervallet med kolumn A som sorteringsfält. Ordlistan rensas helt med metoden ‘RemoveAll’, och koden går sedan igenom cellvärdena i kalkylbladet och lägger dem tillbaka till ordlistan.
Slutligen itererar koden genom ordlistan och visar nyckel- och artikelvärden sammanfogade för att bevisa att sorteringen har fungerat.
Genom att ändra parametrarna i sorteringskoden kan data sorteras efter artikelvärden.
Kopiera en lista med nycklar till ett kalkylblad
Du kan kopiera en lista med alla nyckelvärden till ett kalkylblad med följande kod:
| 12345678910 | Sub CopyKeyList ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.CompareMode = TextCompareMyDictionary.Lägg till "Item1", 10MyDictionary.Lägg till "Item2", 20MyDictionary.Lägg till "Item3", 30Sheets ("Sheet1"). Range ("A1"). Value = Join (MyDictionary.Keys, vbLf)Avsluta Sub |
Detta ger resultatet i ditt kalkylblad:
Du kan kopiera en hel ordlista till ett kalkylblad med den här koden:
| 12345678910 | UnderkopiaIntoWorksheet ()Dim MyDictionary As New Scripting.DictionaryMyDictionary.Lägg till "Item1", 10MyDictionary.Lägg till "Item2", 20MyDictionary.Lägg till "Item3", 30Område ("A1"). Ändra storlek (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Keys)Område ("B1"). Ändra storlek (MyDictionary.Count, 1) = WorksheetFunction.Transpose (MyDictionary.Items)Avsluta Sub |
Ditt kalkylblad kommer att se ut så här:
Jämför en ordbok med en samling
Ordboken är snabbare än en samling.
En samling finns redan inom VBA. En ordbok behöver en referens till Microsoft Scripting Dictionary för att läggas till eller ett objekt skapas med sen bindning
Ett samlingsobjekt kan bara skrivas en gång och läsas många gånger. I en ordbok kan objektvärdet ändras. Med en samling måste objektet tas bort och sedan läggas tillbaka det ändrade objektet.
Samlingen fungerar med indexvärden, vilket kan vara svårt att räkna ut vilket indexvärde som hör hemma. Ordboken arbetar med unika nyckelvärden som används för att hitta ett objekt
Att hämta ett enda objekt går långsammare i en stor samling än i en ordbok
I en samling används nycklarna endast för att leta upp data och kan inte hämtas. I en ordbok kan nycklar testas för existens och kan användas för att hitta ett visst objekt.
Samlingar är skiftlägeskänsliga och detta kan inte ändras. I en ordbok kan jämförelseläget ställas in för att ge skiftlägeskänslighet eller icke-skiftlägeskänslighet
I en samling måste nyckelvärdena vara strängar. I en ordbok kan de vara vilken datatyp som helst, t.ex. numeriskt, datum osv
Att ta bort alla objekt i en samling innebär att omdefiniera samlingsobjektet. Ordboken har "RemoveAll" -metoden för detta.
