Kolekcja to obiekt, który zawiera wiele podobnych elementów. Można do nich łatwo uzyskać dostęp i manipulować nimi, nawet jeśli w kolekcji znajduje się duża liczba elementów.
Istnieją już kolekcje wbudowane w Excel VBA. Przykładem jest kolekcja Arkusze. Dla każdego arkusza w skoroszycie istnieje element w kolekcji Arkusze.
Kolekcje wbudowane mają znacznie więcej dostępnych właściwości i metod, ale nie są one dostępne we własnych kolekcjach, które tworzysz.
Na przykład możesz użyć kolekcji, aby uzyskać informacje o określonym arkuszu. Na przykład możesz zobaczyć nazwę arkusza roboczego, a także to, czy jest widoczny, czy nie. Używając pętli For Each, możesz iterować po każdym arkuszu w kolekcji.
| 1234567 | Arkusze testowe podrzędne()Dim Sh jako arkuszDla każdego Sh w arkuszachMsgBox Sh.NameWidoczne MsgBox Sh.NastępnyNapis końcowy |
Możesz również odnieść się do określonego arkusza roboczego w kolekcji, używając wartości indeksu lub rzeczywistej nazwy arkusza roboczego:
| 12 | Arkusze MsgBox(1).NameMsgBox Sheets("Arkusz1").Name |
Gdy arkusze są dodawane lub usuwane, kolekcja Arkuszy powiększa się lub zmniejsza.
Zauważ, że w przypadku kolekcji VBA numer indeksu zaczyna się od 1, a nie od 0
Kolekcje a tablice
Tablice i kolekcje są podobne pod względem funkcji, ponieważ obie są metodologiami, które umożliwiają przechowywanie dużej ilości danych, do których można następnie łatwo odwoływać się za pomocą kodu. Mają jednak szereg różnic w sposobie działania:
- Tablice są wielowymiarowe, podczas gdy kolekcje są tylko jednowymiarowe. Możesz zwymiarować tablicę z kilkoma wymiarami, np.
| 1 | Dim MyArray(10, 2) As String |
Tworzy to tablicę 10 wierszy z 2 kolumnami, prawie jak arkusz. Kolekcja to w rzeczywistości pojedyncza kolumna. Tablica przydaje się, jeśli potrzebujesz przechowywać wiele elementów danych, które są ze sobą powiązane, np. imię i adres. Nazwa byłaby w pierwszym wymiarze tablicy, a adres w drugim wymiarze.
- Kiedy wypełniasz tablicę, potrzebujesz osobnego wiersza kodu, aby umieścić wartość w każdym elemencie tablicy. Gdybyś miał dwuwymiarową tablicę, w rzeczywistości potrzebowałbyś 2 wierszy kodu - jednego do zaadresowania pierwszej kolumny i jednego do zaadresowania drugiej kolumny. W przypadku obiektu Collection wystarczy użyć metody Add, aby nowy element został właśnie dodany do kolekcji, a wartość indeksu została automatycznie dostosowana do potrzeb.
- Jeśli chcesz usunąć element danych, jest to bardziej skomplikowane w tablicy. Możesz ustawić wartości elementu na pustą wartość, ale sam element nadal istnieje w tablicy. Jeśli używasz pętli For Next do iteracji po tablicy, pętla zwróci pustą wartość, która będzie wymagała kodowania, aby upewnić się, że pusta wartość zostanie zignorowana. W kolekcji używasz metod Add lub Remove, a wszystkie indeksowanie i zmiana rozmiaru są wykonywane automatycznie. Przedmiot, który został usunięty, znika całkowicie. Tablice są przydatne w przypadku danych o stałym rozmiarze, ale kolekcje są lepsze w przypadku, gdy ilość danych może ulec zmianie.
- Kolekcje są tylko do odczytu, podczas gdy wartości tablic można zmieniać za pomocą języka VBA. W przypadku kolekcji musisz najpierw usunąć wartość, która ma zostać zmieniona, a następnie dodać nową zmienioną wartość.
- W tablicy można używać tylko jednego typu danych dla elementów, który jest ustawiany podczas wymiarowania tablicy. Jednak w tablicy możesz używać niestandardowych typów danych, które sam zaprojektowałeś. Możesz mieć bardzo skomplikowaną strukturę tablicy przy użyciu niestandardowego typu danych, który z kolei ma kilka niestandardowych typów danych poniżej. W kolekcji można dodać typy danych użycia dla każdego elementu. Możesz mieć wartość liczbową, datę lub ciąg znaków - obiekt kolekcji przyjmie dowolny typ danych. Jeśli spróbujesz umieścić wartość ciągu w tablicy, która została zwymiarowana jako numeryczna, spowoduje to wyświetlenie komunikatu o błędzie.
- Kolekcje są na ogół łatwiejsze w użyciu niż tablice. Jeśli chodzi o kodowanie, kiedy tworzysz obiekt kolekcji, ma on tylko dwie metody (Add i Remove) oraz dwie właściwości (Count i Item), więc obiekt nie jest w żaden sposób skomplikowany w programowaniu.
- Kolekcje mogą używać kluczy do lokalizowania danych. Tablice nie mają tej funkcji i wymagają zapętlenia kodu w celu iteracji przez tablicę w celu znalezienia określonych wartości.
- Rozmiar tablicy należy określić podczas jej tworzenia. Musisz wiedzieć, ile danych będzie przechowywać. Jeśli potrzebujesz zwiększyć rozmiar tablicy, możesz użyć „ReDim”, aby zmienić jej rozmiar, ale musisz użyć słowa kluczowego „Zachowaj”, jeśli nie chcesz stracić danych już przechowywanych w tablicy. Nie trzeba określać rozmiaru kolekcji. Po prostu rośnie i kurczy się automatycznie w miarę dodawania lub usuwania elementów.
Zakres obiektu kolekcji
Jeśli chodzi o zakres, obiekt kolekcji jest dostępny tylko wtedy, gdy skoroszyt jest otwarty. Nie jest zapisywany po zapisaniu skoroszytu. Jeśli skoroszyt zostanie ponownie otwarty, kolekcja musi zostać ponownie utworzona przy użyciu kodu VBA.
Jeśli chcesz, aby Twoja kolekcja była dostępna dla całego kodu w module kodu, musisz zadeklarować obiekt kolekcji w sekcji Declare u góry okna modułu
Dzięki temu cały kod w tym module będzie miał dostęp do kolekcji. Jeśli chcesz, aby dowolny moduł w skoroszycie miał dostęp do kolekcji, zdefiniuj go jako obiekt globalny
| 1 | Global MyCollection jako nowa kolekcja |
Tworzenie kolekcji, dodawanie elementów i uzyskiwanie dostępu do elementów
Prosty obiekt kolekcji można utworzyć w VBA za pomocą następującego kodu:
| 123456 | Sub Utwórz kolekcję()Przyciemnij MyCollection jako nową kolekcjęMojaKolekcja.Dodaj "Pozycja1"MojaKolekcja.Dodaj "Pozycja2"MojaKolekcja.Dodaj „Pozycja3”Napis końcowy |
Kod wymiaruje nowy obiekt o nazwie „MyCollection”, a następnie następujące wiersze kodu wykorzystują metodę Add, aby dodać 3 nowe wartości.
Następnie możesz użyć kodu, aby przejść przez swoją kolekcję, aby uzyskać dostęp do wartości
| 123 | Dla każdego elementu w MyCollectionElement MsgBoxNastępny przedmiot |
Możesz także iterować po swojej kolekcji za pomocą pętli For Next:
| 123 | Dla n = 1 To MyCollection.CountMsgBox Moja kolekcja(n)Następna n |
Kod pobiera rozmiar kolekcji za pomocą właściwości Count, a następnie używa tej wartości, zaczynając od wartości 1, aby zindeksować każdy element
Pętla For Each jest szybsza niż pętla For Next, ale działa tylko w jednym kierunku (od niskiego indeksu do wysokiego). Pętla For Next ma tę zaletę, że można użyć innego kierunku (od wysokiego indeksu do niskiego) i można również użyć metody Step, aby zmienić przyrost. Jest to przydatne, gdy chcesz usunąć kilka elementów, ponieważ będziesz musiał przeprowadzić usuwanie od końca kolekcji do początku, ponieważ indeks będzie się zmieniał w miarę usuwania.
Metoda Add w kolekcji ma 3 opcjonalne parametry - Key, Before i After
Możesz użyć parametrów „Przed” i „Po”, aby określić pozycję swojego nowego przedmiotu w stosunku do innych znajdujących się już w kolekcji
Odbywa się to poprzez określenie numeru indeksu, względem którego ma się odnosić nowa pozycja.
| 123456 | Sub Utwórz kolekcję()Przyciemnij MyCollection jako nową kolekcjęMojaKolekcja.Dodaj "Pozycja1"MojaKolekcja.Dodaj "Pozycja2", , 1MojaKolekcja.Dodaj „Pozycja3”Napis końcowy |
W tym przykładzie „Pozycja2” została określona do dodania przed pierwszym zindeksowanym pozycją w kolekcji (czyli „Pozycja1”). Kiedy przejdziesz przez tę kolekcję, najpierw pojawi się „Pozycja2”, a następnie „Pozycja1” i „Pozycja3”
Po określeniu parametru „Przed” lub „Po”, wartość indeksu jest automatycznie dostosowywana w ramach kolekcji, dzięki czemu „Item2” staje się wartością indeksu 1, a „Item1” zostaje przesunięta do wartości indeksu 2
Możesz również użyć parametru „Klucz”, aby dodać wartość referencyjną, której możesz użyć do zidentyfikowania elementu kolekcji. Należy pamiętać, że wartość klucza musi być ciągiem i musi być unikatowa w kolekcji.
| 1234567 | Sub Utwórz kolekcję()Przyciemnij MyCollection jako nową kolekcjęMojaKolekcja.Dodaj "Pozycja1"MyCollection.Dodaj "Item2", "MyKey"MojaKolekcja.Dodaj „Pozycja3”MsgBox MyCollection("MyKey")Napis końcowy |
„Item2” nadano wartość „Key” „MyKey”, dzięki czemu można odwoływać się do tej pozycji za pomocą wartości „MyKey” zamiast numeru indeksu (2)
Pamiętaj, że wartość „Klucz” musi być wartością ciągu. Nie może to być żaden inny typ danych. Należy zauważyć, że kolekcja jest tylko do odczytu i nie można zaktualizować wartości klucza po jej ustawieniu. Ponadto nie można sprawdzić, czy istnieje wartość klucza dla określonego elementu w kolekcji ani wyświetlić wartości klucza, co jest niewielką wadą.
Dodatkową zaletą parametru „Klucz” jest to, że kod jest bardziej czytelny, zwłaszcza jeśli jest on przekazywany współpracownikowi do wsparcia i nie musisz iterować po całej kolekcji, aby znaleźć tę wartość. Wyobraź sobie, że masz kolekcję 10 000 przedmiotów, jak trudno byłoby odnieść się do jednego konkretnego przedmiotu!
Usuwanie elementu z kolekcji
Możesz użyć metody „Usuń”, aby usunąć elementy ze swojej kolekcji.
| 1 | MojaKolekcja.Usuń (2) |
Niestety nie jest to łatwe, jeśli kolekcja zawiera dużą liczbę elementów, aby wypracować indeks elementu, który chcesz usunąć. Tutaj przydaje się parametr „Klucz” podczas tworzenia kolekcji
| 1 | MyCollection.Remove ("MyKey") |
Gdy element jest usuwany z kolekcji, wartości indeksu są automatycznie resetowane przez całą kolekcję. W tym miejscu parametr „Klucz” jest tak przydatny, gdy usuwasz kilka elementów naraz. Na przykład, można usunąć indeks elementu 105 i natychmiast indeks elementu 106 stanie się indeksem 105, a wartość indeksu wszystkiego powyżej tego elementu zostanie przesunięta w dół. Jeśli użyjesz parametru Key, nie musisz się martwić o to, która wartość indeksu musi zostać usunięta.
Aby usunąć wszystkie elementy kolekcji i utworzyć nową kolekcję, użyj ponownie instrukcji Dim, która tworzy pustą kolekcję.
| 1 | Przyciemnij MyCollection jako nową kolekcję |
Aby całkowicie usunąć rzeczywisty obiekt kolekcji, możesz ustawić obiekt na nic
| 1 | Ustaw Moja kolekcja = Nic |
Jest to przydatne, jeśli kolekcja nie jest już wymagana przez kod. Ustawienie obiektu kolekcji na wartość Nic usuwa wszystkie odwołania do niego i zwalnia pamięć, z której korzystał. Może to mieć istotny wpływ na szybkość wykonywania kodu, jeśli duży obiekt znajduje się w pamięci, która nie jest już potrzebna.
Policz liczbę przedmiotów w kolekcji
Możesz łatwo sprawdzić liczbę elementów w swojej kolekcji, korzystając z właściwości „Liczba”
| 1 | MsgBox MyCollection.Count |
Użyj tej właściwości, jeśli używasz For Next Loop do iteracji przez kolekcję, ponieważ zapewni Ci ona górny limit numeru indeksu.
Zbieranie testów dla określonej wartości
Możesz iterować po kolekcji, aby wyszukać konkretną wartość dla elementu za pomocą opcji For Each Loop
| 123456789101112 | Podrzędna kolekcja wyszukiwania()Dim MyCollection jako nowa kolekcjaMojaKolekcja.Dodaj "Pozycja1"MojaKolekcja.Dodaj "Pozycja2"MojaKolekcja.Dodaj „Pozycja3”Dla każdego elementu w MyCollectionJeśli pozycja = „Pozycja 2” WtedyElement MsgBox i „Znaleziono”Zakończ, jeśliNastępnyNapis końcowy |
Kod tworzy małą kolekcję, a następnie przechodzi przez nią w poszukiwaniu elementu o nazwie „item2”. Jeśli zostanie znaleziony, wyświetla okno komunikatu, że znalazł określony przedmiot
Jedną z wad tej metodologii jest brak dostępu do wartości indeksu lub wartości klucza
Jeśli zamiast tego użyjesz For Next Loop, możesz użyć licznika For Next, aby uzyskać wartość indeksu, chociaż nadal nie możesz uzyskać wartości „Klucza”
| 123456789101112 | Podrzędna kolekcja wyszukiwania()Przyciemnij MyCollection jako nową kolekcjęMojaKolekcja.Dodaj "Pozycja1"MojaKolekcja.Dodaj "Pozycja2"MojaKolekcja.Dodaj „Pozycja3”Dla n = 1 To MyCollection.CountJeśli MyCollection.Item(n) = "Item2" WtedyMsgBox MyCollection.Item(n) & " znaleziono w pozycji indeksu" & nZakończ, jeśliNastępna nNapis końcowy |
Licznik For Next (n) poda pozycję indeksu
Sortowanie kolekcji
Nie ma wbudowanej funkcji sortowania kolekcji, ale korzystając z myślenia „po wyjęciu z pudełka”, można napisać kod, aby wykonać sortowanie, korzystając z funkcji sortowania arkusza kalkulacyjnego Excela. Ten kod używa pustego arkusza o nazwie „SortSheet” do wykonania faktycznego sortowania.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Podrzędna kolekcja sortowania()Przyciemnij MyCollection jako nową kolekcjęLicznik przyciemnienia tak długo„Zbuduj kolekcję z losowymi przedmiotami zamówieniaMojaKolekcja.Dodaj „Pozycja5”MojaKolekcja.Dodaj "Pozycja2"MojaKolekcja.Dodaj „Pozycja4”MojaKolekcja.Dodaj "Pozycja1"MojaKolekcja.Dodaj „Pozycja3”„Zdobądź liczbę przedmiotów w kolekcji do wykorzystania w przyszłości”Licznik = MojaKolekcja.Liczba„Iteruj po kolekcji, kopiując każdy element do kolejnej komórki w „Arkuszu sortowania” (kolumna A)Dla n = 1 To MyCollection.CountArkusze("SortSheet").Komórki(n, 1) = MojaKolekcja(n)Następna n„Aktywuj arkusz sortowania i użyj procedury sortowania Excela, aby posortować dane w porządku rosnącym”Arkusze("SortSheet").AktywujZakres("A1:A" i MojaKolekcja.Liczba).WybierzActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.ClearActiveWorkbook.Worksheets("Arkusz sortowania"). Sort.SortFields.Add2 Klucz:=Zakres(_"A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _xlSortuj NormalnieZ ActiveWorkbook.Worksheets("SortSheet").Sort.SetRange Range("A1:A5").Nagłówek = xlZgadnij.MatchCase = Fałsz.Orientacja = xlTopToBottom.Metoda sortowania = xlPinYin.ZastosowaćKończyć z„Usuń wszystkie elementy z kolekcji – pamiętaj, że ta pętla For Next Loop działa w odwrotnej kolejności”Dla n = MyCollection.Count to 1 Krok -1MojaKolekcja.Usuń (n)Następna n„Skopiuj wartości komórek z powrotem do pustego obiektu kolekcji, używając przechowywanej wartości (licznika) dla „pętli”Dla n = 1 Do LicznikaMyCollection.Add Sheets("SortSheet").Cells(n, 1).ValueNastępna n„Przejrzyj kolekcję, aby udowodnić kolejność, w jakiej przedmioty są teraz w”Dla każdego elementu w MyCollectionElement MsgBoxNastępny przedmiot„Wyczyść arkusz roboczy (arkusz sortowania) – w razie potrzeby usuń go również”Arkusze("SortSheet").Range(Komórki(1, 1), Komórki(Licznik, 1)).WyczyśćNapis końcowy |
Ten kod najpierw tworzy kolekcję z elementami dodanymi w losowej kolejności. Następnie kopiuje je do pierwszej kolumny w arkuszu (SortSheet).
Następnie Code używa funkcji sortowania programu Excel do sortowania danych w kolumnie w kolejności rosnącej. Kod można również zmodyfikować, aby sortować w porządku malejącym.
Kolekcja jest następnie opróżniana z danych za pomocą pętli For Next. Zwróć uwagę, że opcja step jest używana, aby wyczyścić od końca kolekcji do początku. Dzieje się tak dlatego, że po wyczyszczeniu wartości indeksu są resetowane, jeśli zostanie wyczyszczony od początku, nie zostanie wyczyszczony poprawnie (indeks 2 stanie się indeksem 1)
Wreszcie, za pomocą kolejnej pętli For Next, wartości elementów są przenoszone z powrotem do pustej kolekcji
Kolejny For Each Loop dowodzi, że kolekcja jest teraz w dobrym porządku rosnącym.
Niestety nie dotyczy to żadnych wartości klucza, które mogły zostać wprowadzone pierwotnie, ponieważ wartości klucza nie można odczytać
Przekazywanie kolekcji do podrzędnej / funkcji
Kolekcję można przekazać do podrzędnej lub funkcji w taki sam sposób, jak każdy inny parametr
| 1 | Funkcja MyFunction(ByRef MyCollection jako kolekcja) |
Ważne jest, aby przekazać kolekcję za pomocą „ByRef”. Oznacza to, że używana jest oryginalna kolekcja. Jeśli kolekcja zostanie przekazana za pomocą „ByVal”, tworzy to kopię kolekcji, która może mieć niefortunne reperkusje
Jeśli kopia jest tworzona za pomocą „ByVal”, wszystko, co zmienia kolekcję w ramach funkcji, dzieje się tylko na kopii, a nie na oryginale. Na przykład, jeśli w ramach funkcji do kolekcji zostanie dodany nowy element, nie pojawi się on w oryginalnej kolekcji, co spowoduje powstanie błędu w kodzie.
Zwracanie kolekcji z funkcji
Kolekcję można zwrócić z funkcji w taki sam sposób, jak zwracanie dowolnego obiektu. Musisz użyć słowa kluczowego Set
| 12345 | Zwrot z funkcji pomocniczej()Przyciemnij moją kolekcję jako kolekcjęUstaw MyCollection = Wypełnij kolekcjęMsgBox MyCollection.CountNapis końcowy |
Ten kod tworzy podprogram, który tworzy obiekt o nazwie „MyCollection”, a następnie używa słowa kluczowego „Set”, aby skutecznie wywołać funkcję w celu wypełnienia tej kolekcji. Gdy to zrobisz, wyświetli się okno komunikatu, aby pokazać liczbę 2 przedmiotów
| 1234567 | Funkcja PopulateCollection() jako kolekcjaPrzyciemnij MyCollection jako nową kolekcjęMojaKolekcja.Dodaj "Pozycja1"MojaKolekcja.Dodaj "Pozycja2"Ustaw PopulateCollection = MyCollectionKoniec funkcji |
Funkcja PopulateCollection tworzy nowy obiekt kolekcji i wypełnia go 2 elementami. Następnie przekazuje ten obiekt z powrotem do obiektu kolekcji utworzonego w oryginalnej procedurze podrzędnej.
Konwertowanie kolekcji na tablicę
Możesz przekonwertować swoją kolekcję na tablicę. Możesz chcieć przechowywać dane tam, gdzie można je zmieniać i manipulować. Ten kod tworzy małą kolekcję, a następnie przenosi ją do tablicy
Zauważ, że indeks kolekcji zaczyna się od 1, podczas gdy indeks tablicy zaczyna się od 0. Podczas gdy kolekcja zawiera 3 elementy, tablica musi być zwymiarowana tylko na 2, ponieważ istnieje element 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray()Przyciemnij MyCollection jako nową kolekcjęDim MyArray(2) jako ciągMojaKolekcja.Dodaj "Pozycja1"MojaKolekcja.Dodaj "Pozycja2"MojaKolekcja.Dodaj „Pozycja3”Dla n = 1 To MyCollection.CountMojaTablica (n - 1) = MojaKolekcja (n)Następna nDla n = 0 do 2MsgBox Moja tablica(n)Następna nNapis końcowy |
Konwersja tablicy w kolekcję
Możesz chcieć przekonwertować tablicę na kolekcję. Na przykład możesz chcieć uzyskać dostęp do danych w szybszy i bardziej elegancki sposób niż przy użyciu kodu do uzyskania elementu tablicy.
Pamiętaj, że zadziała to tylko dla jednego wymiaru tablicy, ponieważ kolekcja ma tylko jeden wymiar
| 123456789101112131415 | Sub ConvertArrayIntoCollection()Przyciemnij MyCollection jako nową kolekcjęDim MyArray(2) jako ciągMojaTablica(0) = "element1"MojaTablica(1) = "Element 2"MojaTablica(2) = "Element 3"Dla n = 0 do 2MyCollection.Add MyArray(n)Następna nDla każdego elementu w MyCollectionElement MsgBoxNastępny przedmiotNapis końcowy |
Jeśli chcesz użyć tablicy wielowymiarowej, możesz połączyć wartości tablicy razem dla każdego wiersza w tablicy, używając znaku ogranicznika między wymiarami tablicy, tak aby podczas odczytywania wartości kolekcji można było programowo użyć znaku ogranicznika do oddzielić wartości.
Można również przenieść dane do kolekcji na podstawie dodania wartości pierwszego wymiaru (indeks 1), a następnie dodania wartości następnego wymiaru (indeks 2) i tak dalej.
Gdyby tablica miała, powiedzmy, 4 wymiary, co czwarta wartość w kolekcji byłaby nowym zestawem wartości.
Możesz również dodać wartości tablicowe, które będą używane jako klucze (pod warunkiem, że są unikalne), co ułatwiłoby zlokalizowanie określonych danych.
