Korzystanie ze słownika VBA
Słownik VBA działa podobnie do obiektu kolekcji, ale ma więcej właściwości i metod oraz oferuje większą elastyczność
Słownik przechowuje dane w pamięci i można nim łatwo manipulować. Nie jest wymagane automatyczne obliczanie, tworzenie kopii zapasowych w tle ani odświeżanie ekranu, dzięki czemu Twój kod będzie działał znacznie szybciej.
Obiekt słownika działa w sposób podobny do zwykłego słownika, którego można użyć do poznania znaczenia słowa. Każdy wpis w obiekcie słownika ma wartość „klucz” i wartość „elementu”. Używasz „klucza” jako wartości klucza, aby wyszukać wartość elementu w obiekcie słownika, w podobny sposób, jak w zwykłym słowniku.
Ze względu na sposób działania obiektu słownika wszystkie wartości klucza muszą być unikatowe, w taki sam sposób jak w konwencjonalnym słowniku. Wyobraź sobie, że otworzyłeś swój konwencjonalny słownik, aby sprawdzić znaczenie słowa i znalazłeś to słowo wymienione więcej niż raz w dwóch całkowicie różnych definicjach. Byłbyś bardzo zdezorientowany!
Kluczowe wartości to zazwyczaj tekst lub liczby, albo obie te wartości. Użytkownikom często łatwiej jest zapamiętać nazwy kluczy jako tekst, a nie tylko liczby.
W porównaniu z obiektem kolekcji obiekt kolekcji jest tylko do odczytu. Ma tylko dwie metody (Add i Remove) oraz dwie właściwości (Count i Item). Po dodaniu elementu do obiektu kolekcji można go tylko usunąć, ale nie można go edytować, co jest kłopotliwą procedurą w przypadku konieczności zmiany wartości elementu.
Obiekt słownika zmieni rozmiar automatycznie, aby dopasować się do liczby elementów w nim zawartych. Nie trzeba określać rozmiaru, jak w przypadku konwencjonalnej tablicy
Obiekt słownika jest jednowymiarowy, a typ danych to „Wariant”, więc można w nim wprowadzić dowolny typ danych, np. numeryczna, tekstowa, data
Słownik VBA nie jest natywny dla programu Excel i wymaga dostępu przez wcześniejsze lub późne wiązanie podczas definiowania obiektu słownika
| 123 | Sub Early BindingPrzykład()Dim MyDictionary jako nowy Scripting.DictionaryNapis końcowy |
| 1234 | Przykład późnego wiązania podrzędnego ()Przyciemnij mój słownik jako obiektUstaw MyDictionary = CreateObject("Scripting.Dictionary")Napis końcowy |
Jeśli korzystasz z wczesnego wiązania, musisz dodać odwołanie do biblioteki „Microsoft Scripting Runtime”
Robisz to, wybierając „Narzędzia | References” na pasku menu okna Edytora Visual Basic (VBE) i pojawi się wyskakujące okienko z listą dostępnych bibliotek.
Przewiń w dół do „Microsoft Scripting Runtime” i zaznacz pole obok niego. Kliknij OK, a ta biblioteka jest teraz częścią projektu VBA i można się do niej odwoływać przy użyciu wczesnego wiązania. Wszystkie przykłady kodu w tym artykule będą używać wczesnego wiązania.
Twój kod będzie działał znacznie szybciej z wczesnym wiązaniem, ponieważ wszystko jest kompilowane z góry. W przypadku późnego wiązania obiekt musi być kompilowany podczas działania kodu
Biblioteka Scripting Runtime ma „Intellisense”. Podczas pisania kodu pojawiają się listy dostępnych metod i właściwości, co pomaga zapobiegać błędom w pisowni, które mogą powodować błędy w programie
Ponadto, jeśli naciśniesz F2 w VBE i wybierzesz bibliotekę „Skrypty”, zobaczysz wszystkie dostępne metody i właściwości oraz parametry wymagane dla każdego
Dystrybucja aplikacji Excel zawierającej słownik
Jak już wspomniano, biblioteka Scripting Runtime nie jest częścią Excel VBA, więc jeśli rozpowszechniasz swoją aplikację wśród innych użytkowników, muszą oni mieć dostęp do biblioteki Scripting Runtime na swoim komputerze. Jeśli nie, wystąpi błąd.
Dobrym pomysłem jest dołączenie kodu VBA, aby sprawdzić, czy ta biblioteka jest obecna podczas ładowania aplikacji Excel. Możesz użyć polecenia „Dir”, aby zrobić to w zdarzeniu „Workbook Open”
Lokalizacja pliku to C:\Windows\SysWOW64\scrrun.dll
Zakres obiektu słownika
Obiekt słownika jest dostępny tylko wtedy, gdy skoroszyt programu Excel jest otwarty. Nie jest zapisywany po zapisaniu skoroszytu.
Jeśli twój słownik ma być dostępny dla wszystkich procedur w twoim module, musisz go zadeklarować (Dim) w sekcji Declare na samej górze modułu
Definiujesz go jako obiekt globalny, jeśli chcesz, aby Twój słownik był używany w całym kodzie.
| 1 | Globalny MyDictionary jako nowy słownik |
Wypełnianie i czytanie z Twojego słownika
Na początek musisz utworzyć słownik, wypełnić go danymi, a następnie przejść przez niego, aby udowodnić, że dane istnieją
| 1234567891011 | Sub PopulujPrzeczytajSłownik()Dim MyDictionary jako nowy Scripting.DictionaryMójSłownik.Dodaj „MójElement1”, 10MójSłownik.Dodaj „MyItem2”, 20MyDictionary.Dodaj "MyItem3", 30Dla n = 0 Do MyDictionary.Count - 1MsgBox MyDictionary.Keys(n) & „ ” & MyDictionary.Items(n)Następna nNapis końcowy |
Ten kod tworzy nowy obiekt słownika o nazwie „MyDictionary”, a następnie wypełnia go trzema elementami. Metoda Add ma dwa parametry - Key i Item, i oba są wymagane
Typy danych dla klucza i elementu są wariantami, więc akceptują każdy typ danych - numeryczne, tekstowe, daty itp
Pierwsza pozycja w słowniku mogłaby zostać dodana jako:
| 1 | MyDictionary.Add 10, "MyItem1" |
Wartości zostały zamienione między kluczem a elementem, ale to nadal działa, chociaż klucz wyszukiwania zmieni się teraz na 10.
Jednak ważne jest, aby zrozumieć, że wartość klucza jest wartością wyszukiwania w słowniku. Działa w bardzo podobny sposób do funkcji WYSZUKAJ.PIONOWO w programie Excel. Ponieważ wszystkie klucze muszą mieć unikatowe wartości, możesz określić wartość klucza i natychmiast zwrócić wartość elementu dla tego klucza.
Zauważ, że indeks słownika zaczyna się od 0, więc musisz odjąć 1 od licznika słownika użytego w pętli For… Next
Możesz również użyć pętli For… Each, aby odczytać wartości ze słownika:
| 1234567891011 | Sub PopulujPrzeczytajSłownik()Dim MyDictionary jako nowy Scripting.Dictionary, ja jako wariantMójSłownik.Dodaj „MójElement1”, 10MyDictionary.Dodaj "MyItem2", 20MyDictionary.Dodaj „MyItem3”, 30Dla każdego I w MyDictionary.KeysMsgBox I & „ ” & MyDictionary(I)DalejNapis końcowy |
Ten kod przejdzie przez każdą pozycję i wyświetli klucz pozycji oraz wartość pozycji
Korzystanie z numeru indeksu pozycji
Możesz użyć numeru indeksu klucza lub elementu, aby odczytać wartość
| 123456789101112 | Numery indeksów podrzędnych ()Dim MyDictionary jako nowy Scripting.DictionaryMyDictionary.CompareMode = TekstPorównajMójSłownik.Dodaj „Pozycja1”, 10MójSłownik.Dodaj „Item2”, 20MójSłownik.Dodaj „Item3”, 30MsgBox MyDictionary.Keys(2)MsgBox MyDictionary.Items(1)Napis końcowy |
Ten kod zwróci klucz „item3”, ponieważ indeks zaczyna się od 0, a wartość pozycji 20
Za pomocą numerów indeksu można odwoływać się do poszczególnych wartości klucza lub elementu w kolekcjach Keys lub Items.
Filtrowanie słownika
Nie ma na to bezpośredniej metody, ale napisanie odpowiedniego kodu jest dość proste:
| 1234567891011 | Podrzędny słownik filtrów()Dim MyDictionary jako nowy Scripting.DictionaryMójSłownik.Dodaj „AAItem1”, 10MójSłownik.Dodaj „BBItem2”, 20MójSłownik.Dodaj „BBItem3”, 30Dla każdego I In Filter (MyDictionary.Keys, "BB")MsgBox MójSłownik.Pozycja(I)DalejNapis końcowy |
Wartość filtra działa tylko od początku wartości klucza. W filtrze nie można używać symboli wieloznacznych. Ten kod zwróci dwie wartości pozycji z nazwami kluczy zaczynającymi się od „BB”
W ten sposób otrzymasz podzbiór słownika oparty na wartości filtru, który możesz następnie przenieść do innego słownika lub arkusza roboczego. Dzięki starannemu planowaniu nazw kluczy, upewniając się, że każdy z nich ma sensowny przedrostek, możesz łatwo podzielić słownik na różne części składowe.
Zmiana wartości pozycji klucza
Obiekt słownika ma dużą przewagę nad kolekcją, ponieważ wartość elementu można zmienić, np.
| 1 | MójSłownik("MyItem4") = "40" |
W kolekcji musisz usunąć ten wpis, a następnie utworzyć go ponownie.
Oto przykład kodu:
| 12345678910111213 | Sub PopulujPrzeczytajSłownik()Dim MyDictionary jako nowy Scripting.DictionaryMójSłownik.Dodaj „MójElement1”, 10MyDictionary.Dodaj "MyItem2", 20MyDictionary.Dodaj "MyItem3", 30MójSłownik("MyItem2") = "25"MójSłownik("MyItem4") = "40"Dla n = 0 Do MyDictionary.Count - 1MsgBox MyDictionary.Keys(n) & „ ” & MyDictionary.Items(n)Następna nNapis końcowy |
Powyższy kod konfiguruje trzy elementy w słowniku, a następnie zmienia wartość „MyItem2” z 20 na 25.
Zmienia również wartość „MyItem4” na 40. Zauważ, że w instrukcjach add kodu nie dodano „MyItem4”. Gdy zmienisz wartość klucza, który nie istnieje, zostanie on automatycznie utworzony. Jest to niezwykle wygodne, ponieważ nie jest wyzwalany żaden błąd, ale oznacza to, że musisz uważać na nazwy kluczy. Nieumyślny błąd pisowni w nazwie klucza oznaczałby, że zostanie utworzony nowy klucz, a oryginalna nazwa klucza nadal będzie miała starą wartość.
Może to łatwo prowadzić do problemów z integralnością w obiekcie słownika.
Sprawdź, czy klucz istnieje
Możesz sprawdzić, czy wartość klucza istnieje w słowniku
| 123456789 | Sub CheckExistsDictionary()Dim MyDictionary jako nowy Scripting.DictionaryMójSłownik.Dodaj „MójElement1”, 10MyDictionary.Dodaj "MyItem2", 20MyDictionary.Dodaj "MyItem3", 30MsgBox MyDictionary.Exists("MyItem8")Napis końcowy |
Kod dodaje trzy elementy do nowego obiektu słownika, a następnie testuje klucz („MyItem8”), którego nie ma w słowniku. Zwraca False, ale gdyby został użyty jeden z istniejących kluczy, zwróciłby True
Symbole wieloznaczne nie są akceptowane. W wyszukiwanym tekście domyślnie rozróżniana jest również wielkość liter, ale można to zmienić (patrz dalej w artykule)
Używanie wielu wartości w słowniku
W przeciwieństwie do tablicy obiekt słownika jest tylko jednowymiarowy. Może to prowadzić do problemów, jeśli masz kilka wartości, które chcesz umieścić w kluczu.
Jednym ze sposobów jest łączenie wartości każdej pozycji za pomocą znaku ogranicznika pomiędzy każdą wartością, np. „|”
| 12345678910111213141516171819202122232425262728293031323334 | Wiele wartości podrzędnych()'Utwórz obiekt i zmienne słownikaDim MyDictionary jako nowy Scripting.Dictionary, V1 jako liczba całkowita, V2 jako ciągDim V3 jako data, Temp jako ciąg, N jako liczba całkowita„Wypełnij 3 zmienne, aby zademonstrować wiele wartości”V1 = 5V2 = "Przykład wielu wartości"V3 = „22-lip-2020”'Dodaj połączoną wartość do słownika, używając "|" ogranicznikMyDictionary.Dodaj "MyMultipleItem", V1 i "|" & V2 & "|" & V3 & "|"'Przechwyć połączoną wartość słownikową ze słownika do zmiennejTemp = MyDictionary("MyMultipleItem")'Iterowanie przez połączony ciąg, aby oddzielić poszczególne wartościRobić„Znajdź pozycję ogranicznika”N = InStr(Temp, "|")'Jeśli nie ma więcej ograniczników, wyjściowa pętla WykonajJeśli N = 0, to zakończ Wykonaj'Wyświetl tekst względem znalezionej pozycji ogranicznikaMsgBox po lewej (Temp, N - 1)'Obetnij połączony ciąg do następnego znaku po znalezionym ogranicznikuTemp = Średnia (Temp, N + 1)PętlaNapis końcowy |
Innym sposobem obejścia tego problemu jest zaprojektowanie własnego systemu skryptów podrzędnych dla nazw kluczy. Nie ma powodu, dla którego nie powinno się używać nawiasów i cyfr w nazwach kluczy
| 1234567891011 | Wiele wartości podrzędnych()Dim MyDictionary jako nowy Scripting.DictionaryMójSłownik.Dodaj „Wiele(1)”, 5MyDictionary.Add "Multiple(2)", "Przykład wielu wartości"MyDictionary.Add „Wiele(3)”, „22-lip-2020”Dla N = 1 do 3MsgBox MyDictionary("Wiele(" & N & ")")Następny NNapis końcowy |
Ten kod dodaje do słownika trzy klucze, ale każda nazwa klucza zawiera numer skryptu podrzędnego w nawiasach. Następnie możesz odwołać się do nazwy klucza, ale używając numeru skryptu podrzędnego połączonego w. Jest to bardzo podobne do korzystania z obiektu tablicy
Usuwanie przedmiotów
Możesz usunąć poszczególne pozycje, odwołując się do wartości klucza
| 1 | MyDictionary.Remove („MyItem2”) |
Pamiętaj, że ponieważ nazwy kluczy są unikalne, usuwa to tylko jeden konkretny klucz i wartość pozycji
Możesz także całkowicie wyczyścić słownik
| 1 | MójSłownik.UsuńWszystko |
Oto przykład użycia „Usuń” w VBA:
| 12345678910111213141516 | Sub UsuńWartości()Dim MyDictionary jako nowy Scripting.DictionaryMójSłownik.Dodaj „Pozycja1”, 10MójSłownik.Dodaj „Item2”, 20MójSłownik.Dodaj „Item3”, 30MyDictionary.Remove ("Pozycja2")Dla N = 0 Do MyDictionary.Count - 1MsgBox MyDictionary.Keys(N) & „ ” & MyDictionary.Items(N)Następny NMójSłownik.UsuńWszystkoMsgBox MyDictionary.CountNapis końcowy |
Kod dodaje trzy elementy do słownika, a następnie usuwa „Item2”. Następnie przechodzi przez słownik, aby udowodnić, że „Pozycja 2” już nie istnieje
Na koniec kod usuwa wszystkie elementy ze słownika i wyświetla licznik słownika, który teraz wynosi zero.
Zmiana rozróżniania wielkości liter w wyszukiwaniach
Jeśli szukasz klucza, domyślnie rozróżniana jest wielkość liter. Możesz jednak użyć właściwości „CompareMode”, aby to zmienić.
Należy zauważyć, że należy to zrobić natychmiast w kodzie po utworzeniu obiektu słownika, ale przed dodaniem jakichkolwiek danych do słownika. Po ustawieniu trybu porównywania nie można go zmienić w tym słowniku.
| 12345678910 | Sub ChangeCase Sensitivity()Dim MyDictionary jako nowy Scripting.DictionaryMyDictionary.CompareMode = TekstPorównajMójSłownik.Dodaj „Pozycja1”, 10MójSłownik.Dodaj „Item2”, 20MójSłownik.Dodaj „Item3”, 30MsgBox MyDictionary.Exists("item2")Napis końcowy |
W tym przykładzie tryb porównania jest ustawiony na „TextCompare”, co oznacza, że wielkość liter nie jest rozróżniana. Wyrażenie „Exists” na końcu przykładu zwróci True, mimo że cały wyszukiwany tekst jest pisany małymi literami.
W programie Excel są tylko dwie wartości, których można użyć w trybie porównania. Porównanie binarne rozróżnia wielkość liter, a porównywanie tekstu nie rozróżnia wielkości liter
Jeśli masz ustawiony tryb porównania na Binary Compare, musisz być ostrożny przy nazywaniu kluczy. Jeśli ustawisz nazwę tak, aby jako pierwszy znak była wielka litera, to podczas zmiany wartości musisz upewnić się, że nadal pierwszy znak jest pisany wielką literą. Jeśli zaczniesz od małej litery, zostanie to zinterpretowane jako nowy klucz i może łatwo doprowadzić do zamieszania i błędów w słowniku
Pamiętaj, że jeśli zmienisz wartość klucza, a nazwa klucza nie istnieje z powodu użycia funkcji Binary Compare, do słownika zostanie dodany nowy klucz i wartość.
Jeśli zamiast tego użyjesz funkcji Text Compare, wszelkie zmiany wartości zostaną przeniesione do klucza, niezależnie od wielkości liter. Jeśli spróbujesz dodać ten sam element, ale pisany z inną literą wielkości liter, otrzymasz błąd, ponieważ już istnieje.
Sortowanie słownika
Podobnie jak w przypadku obiektu kolekcji, nie ma metody umożliwiającej sortowanie słownika za pomocą kluczy lub wartości pozycji.
Ponieważ jednak kod VBA znajduje się w skoroszycie Excela, dane ze słownika można przenieść do Excela w formie tabelarycznej, a następnie zastosować do niego funkcję sortowania Excela. Słownik można następnie wyczyścić za pomocą „Usuń wszystko”, a posortowane wartości dodać z arkusza roboczego.
Ten kod posortuje zarówno klucze, jak i wartości pozycji
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 | Sub SortMySłownik()Przyciemnij mój słownik jako nowy słownikLicznik przyciemnienia tak długo'Zbuduj słownik z losowymi pozycjami zamówieniaMójSłownik.Dodaj „Pozycja5”, 5MójSłownik.Dodaj „Item2”, 15MójSłownik.Dodaj „Item4”, 11MójSłownik.Dodaj „Pozycja1”, 2MójSłownik.Dodaj „Pozycja3”, 19„Przechwyć liczbę pozycji w słowniku do wykorzystania w przyszłości”Licznik = MyDictionary.Count„Iteruj przez słownik, kopiując każdy klucz i element do kolejnej komórki w „Arkuszu1” (kolumna A)Dla N = 0 Do MyDictionary.Count - 1Arkusze("Arkusz1").Komórki(N + 1, 1) = MójSłownik.Klawisze(N)Arkusze("Arkusz1").Komórki(N + 1, 2) = MójSłownik.Items(N)Następny N'Aktywuj Arkusz1 i użyj procedury sortowania Excela, aby posortować dane w porządku rosnącymArkusze("Arkusz1").AktywujZakres("A1:B" & MyDictionary.Count).WybierzActiveWorkbook.Worksheets("Arkusz1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Arkusz1").Sort.SortFields.Add2 Klucz:=Zakres( _"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlSortuj NormalnieZ ActiveWorkbook.Worksheets("Arkusz1").Sort.SetRange Range("A1:A5").Nagłówek = xlZgadnij.MatchCase = Fałsz.Orientacja = xlTopToBottom.Metoda sortowania = xlPinYin.ZastosowaćKończyć z'Wyczyść wszystkie pozycje ze słownikaMójSłownik.UsuńWszystko'Skopiuj wartości komórek z powrotem do pustego obiektu słownika, używając wartości przechowywanej (Licznik) dla 'pętliDla N = 1 Do LicznikaMójSłownik.Add Sheets("Arkusz1").Cells(N, 1).Value, Sheets("Arkusz1").Cells(N, 2).ValueNastępny N„Iteruj po słowniku, aby udowodnić kolejność, w jakiej są teraz elementy”Dla N = 0 Do MyDictionary.Count - 1MsgBox MyDictionary.Keys(N) & „ ” & MyDictionary.Items(N)Następny N'Wyczyść arkusz (Arkusz 1) - w razie potrzeby usuń go równieżArkusze("Arkusz1").Zakres(Komórki(1,1),Komórki(Licznik, 2)).WyczyśćNapis końcowy |
Ten kod tworzy słownik z dodanymi pięcioma losowymi wartościami kolejności. Przechwytuje liczbę elementów do zmiennej, a następnie iteruje po słowniku, przenosząc wartości klucza i elementu do oddzielnych kolumn w arkuszu.
Następnie sortuje pobrany zakres, używając kolumny A jako pola sortowania. Słownik jest całkowicie czyszczony za pomocą metody „Usuń wszystko”, a następnie kod przechodzi przez wartości komórek w arkuszu, dodając je z powrotem do słownika.
Na koniec kod iteruje po słowniku, wyświetlając połączone wartości klucza i elementu, aby udowodnić, że sortowanie zadziałało.
Zmieniając parametry w kodzie sortowania, dane można sortować według wartości pozycji.
Kopiowanie listy kluczy do arkusza roboczego
Listę wszystkich wartości kluczy można skopiować do arkusza, korzystając z następującego kodu:
| 12345678910 | Sub CopyKeyList()Dim MyDictionary jako nowy Scripting.DictionaryMyDictionary.CompareMode = TekstPorównajMójSłownik.Dodaj „Pozycja1”, 10MójSłownik.Dodaj „Item2”, 20MójSłownik.Dodaj „Item3”, 30Sheets("Arkusz1").Range("A1").Value = Dołącz(MójSłownik.Klucze, vbLf)Napis końcowy |
To da wynik w twoim arkuszu:
Możesz skopiować cały słownik do arkusza za pomocą tego kodu:
| 12345678910 | Sub Kopiuj do arkusza roboczego ()Dim MyDictionary jako nowy Scripting.DictionaryMójSłownik.Dodaj „Pozycja1”, 10MójSłownik.Dodaj „Item2”, 20MójSłownik.Dodaj „Item3”, 30Range("A1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Keys)Range("B1").Resize(MyDictionary.Count, 1) = WorksheetFunction.Transpose(MyDictionary.Items)Napis końcowy |
Twój arkusz będzie wyglądał tak:
Porównanie słownika z kolekcją
Słownik jest szybszy niż kolekcja.
Kolekcja jest już w VBA. Słownik wymaga odwołania do słownika Microsoft Scripting Dictionary lub obiektu utworzonego przy użyciu późnego wiązania
Element Kolekcji można zapisać tylko raz i przeczytać wiele razy. W Słowniku można zmienić wartość pozycji. W przypadku kolekcji element musi zostać usunięty, a następnie zmieniony element dodany z powrotem.
Kolekcja działa na wartościach indeksów, co może być trudne do ustalenia, która wartość indeksu należy do którego. Słownik działa na unikalnych wartościach kluczy, które są używane do zlokalizowania elementu
Pobieranie pojedynczego elementu jest wolniejsze w dużej kolekcji niż w słowniku
W kolekcji klucze są używane tylko do wyszukiwania danych i nie można ich odzyskać. W słowniku klucze mogą być testowane pod kątem istnienia i mogą być użyte do znalezienia konkretnego elementu.
W kolekcjach rozróżniana jest wielkość liter i nie można tego zmienić. W słowniku tryb porównania można ustawić tak, aby uwzględniał wielkość liter lub nie
W kolekcji wartości klucza muszą być ciągami. W słowniku mogą to być dane dowolnego typu, np. numeryczna, data itp.
Usunięcie wszystkich elementów w kolekcji wiąże się z ponownym zdefiniowaniem obiektu Collection. Słownik ma do tego metodę „RemoveAll”.
