Tablice VBA

W VBA, Szyk to pojedyncza zmienna, która może przechowywać wiele wartości. Pomyśl o tablicy jak o zakresie komórek: każda komórka może przechowywać wartość. Tablice mogą być jednowymiarowe (pomyśl o pojedynczej kolumnie), dwuwymiarowe (pomyśl o wielu wierszach i kolumnach) lub wielowymiarowe. Dostęp do wartości tablicy można uzyskać na podstawie ich pozycji (numeru indeksu) w tablicy.

Szybki arkusz VBA Array

Tablice

OpisKod VBATworzyćDim arr (1 do 3) jako wariant
arr(1) = „jeden”
arr(2) = „dwa”
arr(3) = „trzy”Twórz z ExcelaDim arr (1 do 3) jako wariant
Dim cell As Range, i As Integer
i = LBound(arr)
Dla każdej komórki w zakresie („A1:A3”)
ja = ja + 1
arr(i) = komórka.wartość
Następna komórkaPrzeczytaj wszystkie przedmiotyDim ja jak Long
For i = LBound(arr) To UBound(arr)
MsgBox arr(i)
Dalej jaUsuwaćWymażTablica do ciąguDim sName As String
sName = Dołącz(arr, „:”)Zwiększ rozmiarReDim Zachowaj arr(0 do 100)Ustalić wartośćprzykł(1) = 22

Szybkie przykłady macierzy VBA

Spójrzmy na pełny przykład, zanim zagłębimy się w szczegóły:

12345678910

Przykład tablicy podrzędnej()Dim strNames(1 do 4) jako StringstrNames(1) = "Shell"strNames(2) = "Marek"strNames(3) = "Neema"strNames(4) = "Jose"msgbox strNazwy(3)Napis końcowy

Tutaj utworzyliśmy jednowymiarową tablicę ciągów: strNames o rozmiarze cztery (może przechowywać cztery wartości) i przypisano cztery wartości. Na koniec wyświetlamy trzecią wartość w polu wiadomości.

W tym przypadku korzyść z używania Array jest niewielka: wymagana jest tylko jedna deklaracja zmiennej zamiast czterech.

Spójrzmy jednak na przykład, który pokaże prawdziwą moc tablicy:

12345678

Przykład tablicy pomocniczej2()Dim strNames(1 do 60000) As StringPrzyciemnij i tak długoDla i = 1 do 60000strNames(i) = Cells(i, 1)).WartośćDalej jaNapis końcowy

Tutaj stworzyliśmy tablicę, która może pomieścić 60 000 wartości i szybko wypełniliśmy tablicę z kolumny A arkusza roboczego.

Korzyści z tablicy? - Prędkość!

Możesz pomyśleć o tablicach podobnych do arkuszy programu Excel:

• Każda komórka (lub element w tablicy) może zawierać własną wartość
• Dostęp do każdej komórki (lub elementu w tablicy) można uzyskać za pomocą pozycji w wierszu i kolumnie.
  • Arkusz roboczy Np. komórki(1,4).value = "Wiersz 1, Kolumna 4"
  • Tablica Ex. arrVar(1,4) = "Wiersz 1, Kolumna 4"

Po co więc zawracać sobie głowę tablicami? Dlaczego nie po prostu czytać i zapisywać wartości bezpośrednio do komórek w programie Excel? Jedno słowo: Prędkość!

Czytanie / pisanie do komórek Excela to powolny proces. Praca z tablicami jest znacznie szybsza!

Utwórz / zadeklaruj tablicę (Dim)

Uwaga: Tablice mogą mieć wiele „wymiarów”. Aby uprościć sprawę, zaczniemy od pracy tylko z tablicami jednowymiarowymi. W dalszej części samouczka przedstawimy tablice wielowymiarowe.

Tablica statyczna

Tablice statyczne to tablice, które nie mogą zmieniać rozmiaru. Odwrotnie, Tablice dynamiczne może zmienić rozmiar. Są deklarowane nieco inaczej. Najpierw spójrzmy na tablice statyczne.

Uwaga: Jeśli rozmiar tablicy nie zmieni się, użyj tablicy statycznej.

Deklarowanie statycznej zmiennej tablicowej jest bardzo podobne do deklarowania zwykłej zmiennej, z tą różnicą, że musisz zdefiniować rozmiar tablicy. Istnieje kilka różnych sposobów na ustawienie rozmiaru tablicy.

Możesz jawnie zadeklarować pozycję początkową i końcową tablicy:

123456789101112

Podrzędna tablica statyczna1()'Tworzy tablicę z pozycjami 1,2,3,4Dim arrDemo1(1 do 4) As String'Tworzy tablicę z pozycjami 4,5,6,7Dim arrDemo2 (4 do 7) tak długo'Tworzy tablicę z pozycjami 0,1,2,3Dim arrDemo3(0 do 3) tak długoNapis końcowy

Lub możesz wprowadzić tylko rozmiar tablicy:

123456

Podrzędna tablica statyczna2()'Tworzy tablicę z pozycjami 0,1,2,3Dim arrDemo1(3) jako ciągNapis końcowy

Ważny! Zauważ, że domyślnie tablice zaczynają się od pozycji 0. Więc Dim arrDemo1(3) tworzy tablicę z pozycjami 0,1,2,3.

Możesz zadeklarować Podstawa opcji 1 u góry modułu, tak aby tablica zaczynała się na pozycji 1:

12345678

Podstawa opcji 1Podrzędna tablica statyczna3()'Tworzy tablicę z pozycjami 1,2,3Dim arrDemo1(3) jako ciągNapis końcowy

Uważam jednak, że znacznie łatwiej (i mniej zagmatwać) jest po prostu jawne zadeklarowanie pozycji początkowej i końcowej tablic.

Masz dość wyszukiwania przykładów kodu VBA? Wypróbuj Automakro!

Tablica dynamiczna

Tablice dynamiczne to tablice, których rozmiar można zmienić (lub których rozmiar nie musi być definiowany).

Istnieją dwa sposoby deklarowania tablicy dynamicznej.

Tablice wariantów

Pierwszym sposobem zadeklarowania tablicy dynamicznej jest ustawienie tablicy na type Wariant.

1	Dim arrVar() jako wariant

Z Wariant Array, nie musisz definiować rozmiaru tablicy. Rozmiar dostosuje się automatycznie. Pamiętaj tylko, że tablica zaczyna się od pozycji 0 (chyba że dodasz bazę opcji 1 na górze modułu)

12345678910111213

Podrzędna tablica wariantów()Dim arrVar() jako wariant'Zdefiniuj wartości (rozmiar = 0,1,2,3)ArrVar = Tablica (1, 2, 3, 4)'Zmień wartości (rozmiar = 0,1,2,3,4)arrVar = Array("1a", "2a", "3a", "4a", "5a")„Pozycja wyjściowa 4 („5a”)MsgBox arrVar(4)Napis końcowy

Niezmienne tablice dynamiczne

W przypadku tablic niezmiennych należy zdefiniować rozmiar tablicy przed przypisaniem wartości do tablicy. Jednak proces tworzenia tablicy jest nieco inny:

1234567

Podrzędna tablica dynamiczna1()Dim arrDemo1() As String'Zmienia rozmiar tablicy z pozycjami 1,2,3,4ReDim arrDemo1(1 do 4)Napis końcowy

Najpierw deklarujesz tablicę, podobnie jak tablica statyczna, z wyjątkiem tego, że pomijasz rozmiar tablicy:

1	Dim arrDemo1() As String

Teraz, gdy chcesz ustawić rozmiar tablicy, użyj ReDim polecenie, aby zmienić rozmiar tablicy:

12	'Zmienia rozmiar tablicy z pozycjami 1,2,3,4ReDim arrDemo1(1 do 4)

ReDim zmienia rozmiar tablicy. Przeczytaj poniżej różnicę między ReDim i ReDim Preserve.

ReDim a ReDim Zachowaj

Kiedy używasz ReDim polecenie usunie wszystkie istniejące wartości z szyku. Zamiast tego możesz użyć ReDim Zachowaj aby zachować wartości tablicy:

12	'Zmienia rozmiar tablicy z pozycjami 1,2,3,4 (Zachowując istniejące wartości)ReDim Zachowaj arrDemo1(1 do 4)

Uproszczone deklarowanie tablic

Możesz czuć się przytłoczony po przeczytaniu wszystkiego powyżej. Aby uprościć sprawę, w dalszej części artykułu będziemy pracować głównie z tablicami statycznymi.

Ustaw wartości tablicy

Ustawienie wartości tablicy jest bardzo łatwe.

W przypadku tablicy statycznej musisz zdefiniować każdą pozycję tablicy, pojedynczo:

12345678

Przykład tablicy podrzędnej()Dim strNames(1 do 4) jako StringstrNames(1) = "Shell"strNames(2) = "Marek"strNames(3) = "Neema"strNames(4) = "Jose"Napis końcowy

Dzięki Variant Array możesz zdefiniować całą tablicę w jednym wierszu (praktyczne tylko w przypadku małych tablic):

123456

PodtablicaPrzykład_1Line()Dim strNames() jako wariantstrNames = Array("Shelly", "Steve", "Neema", "Jose")Napis końcowy

Jeśli spróbujesz zdefiniować wartość dla lokalizacji tablicy, która nie istnieje, otrzymasz błąd Subscript Out of Range:

1	strNames(5) = "Shannon"

W sekcji „Przypisz zakres do tablicy” Poniżej pokażemy, jak używać pętli do szybkiego przypisywania dużej liczby wartości do tablic.

Uzyskaj wartość tablicy

Możesz pobrać wartości tablicy w ten sam sposób. W poniższym przykładzie zapiszemy wartości tablicowe do komórek:

1234	Zakres("A1").Value = strNames(1)Zakres("A2").Value = strNames(2)Zakres("A3").Value = strNames(3)Zakres("A4").Value = strNames(4)

Programowanie VBA | Generator kodu działa dla Ciebie!

Przypisz zakres do tablicy

Aby przypisać zakres do tablicy, możesz użyć pętli:

12345678

Podzakres do tablicy()Dim strNames(1 do 60000) As StringPrzyciemnij i tak długoDla i = 1 do 60000strNames(i) = Cells(i, 1)).WartośćDalej jaNapis końcowy

Spowoduje to przejście przez komórki A1:A60000, przypisując wartości komórek do tablicy.

Tablica wyjściowa do zakresu

Możesz też użyć pętli, aby przypisać tablicę do zakresu:

123	Dla i = 1 do 60000Komórki(i, 1)).Wartość = strNames(i)Dalej ja

To zrobi odwrotnie: przypisz wartości tablicy do komórek A1:A60000

Tablice 2D / wielowymiarowe

Do tej pory pracowaliśmy wyłącznie z tablicami jednowymiarowymi (1D). Jednak tablice mogą mieć maksymalnie 32 wymiary.

Pomyśl o tablicy 1D, takiej jak pojedynczy wiersz lub kolumna komórek programu Excel, o tablicy 2D, takiej jak cały arkusz programu Excel z wieloma wierszami i kolumnami, a tablica 3D jest jak cały skoroszyt zawierający wiele arkuszy, z których każdy zawiera wiele wierszy i kolumn (Ty może również myśleć o tablicy 3D jak o kostce Rubika).

Przykłady tablic wielowymiarowych

Teraz zademonstrujmy przykłady pracy z tablicami o różnych wymiarach.

Programowanie VBA | Generator kodu działa dla Ciebie!

Przykład tablicy 1D

Ta procedura łączy poprzednie przykłady tablic w jedną procedurę, pokazując, jak można używać tablic w praktyce.

1234567891011121314

PodtablicaEx_1d()Dim strNames(1 do 60000) As StringPrzyciemnij i tak długo„Przypisz wartości do tablicy”Dla i = 1 do 60000strNames(i) = Cells(i, 1)).WartośćDalej ja„Wartości tablicy wyjściowej do zakresu”Dla i = 1 do 60000Arkusze("Wyjście").Cells(i, 1)).Value = strNames(i)Dalej jaNapis końcowy

Przykład tablicy 2D

Ta procedura zawiera przykład szyku 2D:

123456789101112131415161718

PodtablicaEx_2d()Dim strNames(1 do 60000, 1 do 10) As StringDim i tak długo, j tak długo„Przypisz wartości do tablicy”Dla i = 1 do 60000Dla j = 1 do 10strNazwy(i, j) = Komórki(i, j).WartośćNastępny jDalej ja„Wartości tablicy wyjściowej do zakresu”Dla i = 1 do 60000Dla j = 1 do 10Arkusze("Wyjście").Cells(i, j).Value = strNames(i, j)Następny jDalej jaNapis końcowy

Przykład tablicy 3D

Ta procedura zawiera przykład tablicy 3D do pracy z wieloma arkuszami:

12345678910111213141516171819202122

PodtablicaEx_3d()Dim strNames(1 do 60000, 1 do 10, 1 do 3) As StringDim i tak długo, j tak długo, k jak długo„Przypisz wartości do tablicy”Dla k = 1 do 3Dla i = 1 do 60000Dla j = 1 do 10strNames(i, j, k) = Arkusze("Arkusz" & k).Komórki(i, j).WartośćNastępny jDalej jaNastępna k„Wartości tablicy wyjściowej do zakresu”Dla k = 1 do 3Dla i = 1 do 60000Dla j = 1 do 10Arkusze("Wyjście" & k).Cells(i, j).Value = strNames(i, j, k)Następny jDalej jaNastępna kNapis końcowy

Długość/rozmiar tablicy

Do tej pory przedstawiliśmy Ci różne typy tablic i nauczyliśmy Cię, jak deklarować tablice i pobierać/ustawiać wartości tablic. Następnie skupimy się na innych tematach niezbędnych do pracy z tablicami.

Programowanie VBA | Generator kodu działa dla Ciebie!

Funkcje UBound i LBound

Pierwszym krokiem do uzyskania długości/rozmiaru tablicy jest użycie funkcji UBound i LBound, aby uzyskać górną i dolną granicę tablicy:

123456

Sub UBoundLBound()Dim strNames(1 do 4) As StringMsgBox UBound(strNames)MsgBox LBound(strNames)Napis końcowy

Odjęcie dwóch (i dodanie 1) da ci długość:

1	GetArrLength = UBound(strNames) - LBound(strNames) + 1

Funkcja długości tablicy

Oto funkcja pobierająca długość tablicy jednowymiarowej:

1234567

Funkcja publiczna GetArrLength(a jako wariant) tak długaJeśli jest pusty(a), toGetArrLength = 0W przeciwnym razieGetArrLength = UBound(a) - LBound(a) + 1Zakończ, jeśliKoniec funkcji

Chcesz obliczyć rozmiar tablicy 2D? Sprawdź nasz samouczek: Oblicz rozmiar tablicy.

Zapętlaj tablicę

Istnieją dwa sposoby na przechodzenie przez tablicę. Pierwsza pętla przechodzi przez liczby całkowite odpowiadające pozycjom liczbowym w tablicy. Jeśli znasz rozmiar tablicy, możesz określić go bezpośrednio:

12345678910111213

PodtablicaExample_Loop1()Dim strNames(1 do 4) As StringPrzyciemnij i tak długostrNames(1) = "Shell"strNames(2) = "Marek"strNames(3) = "Neema"strNames(4) = "Jose"Dla i = 1 do 4MsgBox strNames(i)Dalej jaNapis końcowy

Jeśli jednak nie znasz rozmiaru tablicy (jeśli tablica jest dynamiczna), możesz użyć funkcji LBound i UBound z poprzedniej sekcji:

12345678910111213

PodtablicaExample_Loop2()Dim strNames(1 do 4) As StringPrzyciemnij i tak długostrNames(1) = "Shell"strNames(2) = "Marek"strNames(3) = "Neema"strNames(4) = "Jose"For i = LBound(strNames) To UBound(strNames)MsgBox strNames(i)Dalej jaNapis końcowy

Dla każdej pętli tablicy

Druga metoda to pętla For Each. To przechodzi przez każdy element w tablicy:

12345678910111213

PodtablicaExample_Loop3()Dim strNames(1 do 4) As StringPrzyciemnij przedmiotstrNames(1) = "Shell"strNames(2) = "Marek"strNames(3) = "Neema"strNames(4) = "Jose"Dla każdego elementu w strNamesElement MsgBoxNastępny przedmiotNapis końcowy

Pętla For Each Array będzie działać z tablicami wielowymiarowymi oprócz tablic jednowymiarowych.

Programowanie VBA | Generator kodu działa dla Ciebie!

Zapętlaj macierz 2D

Możesz także użyć funkcji UBound i LBound, aby przejść przez tablicę wielowymiarową. W tym przykładzie przejdziemy przez tablicę 2D. Zauważ, że funkcje UBound i LBound pozwalają określić, który wymiar tablicy ma znaleźć górną i dolną granicę (1 dla pierwszego wymiaru, 2 dla drugiego wymiaru).

1234567891011121314151617181920

PodtablicaExample_Loop4()Dim strNames(1 do 4, 1 do 2) As StringDim i tak długo, j tak długostrNames(1, 1) = "Shelly"strNames(2, 1) = "Marek"strNames(3, 1) = "Neema"strNames(4, 1) = "Jose"strNames(1, 2) = "Shelby"strNames(2, 2) = "Steven"strNames(3, 2) = "Nemo"strNames(4, 2) = "Jesse"For j = LBound(strNames, 2) To UBound(strNames, 2)For i = LBound(strNames, 1) To UBound(strNames, 1)MsgBox strNames(i, j)Dalej jaNastępny jNapis końcowy

Inne zadania tablicy

Wyczyść tablicę

Aby wyczyścić całą tablicę, użyj instrukcji Erase:

1	Wymaż nazwy strName

Przykład użycia:

12345678910

Przykład tablicy podrzędnej()Dim strNames(1 do 4) jako StringstrNames(1) = "Shell"strNames(2) = "Marek"strNames(3) = "Neema"strNames(4) = "Jose"Wymaż nazwy strNameNapis końcowy

Alternatywnie możesz również Wymnij tablicę, aby zmienić jej rozmiar, czyszcząc część tablicy:

1	ReDim strNames(1 do 2)

Spowoduje to zmianę rozmiaru tablicy do rozmiaru 2, usuwając pozycje 3 i 4.

Zliczanie tablicy

Liczbę pozycji w każdym wymiarze tablicy można policzyć za pomocą funkcji UBound i LBound (omówionych powyżej).

Liczbę wprowadzonych elementów (lub elementów spełniających określone kryteria) można również policzyć, przeglądając tablicę w pętli.

Ten przykład przejdzie przez tablicę obiektów i policzy liczbę niepustych ciągów znalezionych w tablicy:

123456789101112131415

PodtablicaLoopandCount()Dim strNames(1 do 4) As StringDim i tak długo, n tak długostrNames(1) = "Shell"strNames(2) = "Marek"For i = LBound(strNames) To UBound(strNames)Jeśli strNames(i) "" Wtedyn = n + 1Zakończ, jeśliDalej jaMsgBox n & " znaleziono niepuste wartości."Napis końcowy

Programowanie VBA | Generator kodu działa dla Ciebie!

Usuń duplikaty

W pewnym momencie możesz chcieć usunąć duplikaty z tablicy. Niestety, VBA nie ma wbudowanej funkcji, aby to zrobić. Napisaliśmy jednak funkcję do usuwania duplikatów z tablicy (za dużo czasu na uwzględnienie w tym samouczku, ale odwiedź link, aby dowiedzieć się więcej).

Filtr

Funkcja filtrowania VBA umożliwia filtrowanie tablicy. Robi to, tworząc nową tablicę zawierającą tylko przefiltrowane wartości. Poniżej znajduje się krótki przykład, ale koniecznie przeczytaj artykuł, aby uzyskać więcej przykładów dla różnych potrzeb.

1234567891011121314

Pod filtr_Match()'Zdefiniuj szykDim strNames As VariantstrNames = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")„Filtruj tablicę”Dim strSubNames As VariantstrSubNames = Filter(strNames, "Kowalski")'Liczba filtrowanej tablicyMsgBox "Found " & UBound(strSubNames) - LBound(strSubNames) + 1 & " names."Napis końcowy

Funkcja IsArray

Możesz sprawdzić, czy zmienna jest tablicą, używając funkcji IsArray:

123456789101112

Sub IsArrayEx()'Tworzy tablicę z pozycjami 1,2,3Dim arrDemo1(3) jako ciąg'Tworzy zwykłą zmienną łańcuchowąDim str As StringMsgBox IsArray(arrDemo1)MsgBox IsArray(str)Napis końcowy

Dołącz do tablicy

Możesz szybko „dołączyć” całą tablicę razem z funkcją Join:

123456789101112

Podtablica_Join()Dim strNames(1 do 4) As StringDim joinNames As StringstrNames(1) = "Shell"strNames(2) = "Marek"strNames(3) = "Neema"strNames(4) = "Jose"joinNames = Join(strNames, ", ")MsgBox joinNamesNapis końcowy

Programowanie VBA | Generator kodu działa dla Ciebie!

Podziel ciąg na tablicę

Funkcja podziału VBA podzieli ciąg tekstu na tablicę zawierającą wartości z oryginalnego ciągu. Spójrzmy na przykład:

123456789

Subarray_Split()Dim Names() jako ciągDim joinNames As StringjoinNames = "Shelly, Steve, Nema, Jose"Names = Split(joinedNames, ",")Nazwy MsgBoxów(1)Napis końcowy

Tutaj dzielimy ten ciąg tekstu „Shelly,Steve,Nema,Jose” na tablicę (rozmiar 4) za pomocą separatora przecinka (,).

Const Array

Tablica Nie mogę być zadeklarowane jako stała w VBA. Możesz jednak obejść ten problem, tworząc funkcję używaną jako tablica:

123456789

' Zdefiniuj stałą tablicęFunkcja ConstantArray()ConstantArray = Tablica(4, 12, 21, 100, 5)Koniec funkcji„Pobierz wartość stałej tablicy”SubRetrieValues()Tablica stała MsgBox(3)Napis końcowy

Kopiuj tablicę

Nie ma wbudowanego sposobu kopiowania tablicy za pomocą VBA. Zamiast tego będziesz musiał użyć pętli, aby przypisać wartości z jednej tablicy do drugiej.

12345678910111213141516171819

Sub CopyArray()Dim Arr1 (1 do 100) tak długoDim Arr2 (1 do 100) tak długoPrzyciemnij i tak długo'Utwórz tablicę1Dla i = 1 do 100Arr1(i) = iDalej ja„Kopiuj tablicę1 do tablicy2”Dla i = 1 do 100Arr2(i) = Arr1(i)Dalej jaMsgBox Arr2(74)Napis końcowy

Transponować

Nie ma wbudowanej funkcji VBA umożliwiającej transpozycję tablicy. Jednak napisaliśmy funkcję transponowania tablicy 2D. Przeczytaj artykuł, aby dowiedzieć się więcej.

Programowanie VBA | Generator kodu działa dla Ciebie!

Funkcja zwracana tablica

Częstym pytaniem programistów VBA jest to, jak utworzyć funkcję, która zwraca tablicę. Myślę, że większość trudności można rozwiązać za pomocą tablic wariantów. Napisaliśmy artykuł na ten temat: Tablica zwrotów funkcji VBA.

Korzystanie z tablic w programie Access VBA

Większość powyższych przykładów Array działa dokładnie tak samo w programie Access VBA, jak w programie Excel VBA. Główną różnicą jest to, że jeśli chcesz zapełnić tablicę danymi programu Access, musisz przejść przez obiekt RecordSet, a nie obiekt Range.

1234567891011121314151617181920212223

PodzakresDoArrayAccess()Przy błędzie Wznów DalejDim strNames() jako ciągPrzyciemnij i tak długoDim iCount tak długoDim dbs jako baza danychPrzyciemnij najpierw jako zestaw rekordówUstaw dbs = Bieżąca baza danychUstaw rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)Z pierwszym.PrzenieśOstatni.Najpierw przesuńiCount = .RecordCountReDim strNames(1 do iCount)Dla i = 1 do iCountstrNames(i) = rst.Fields("Nazwa Klienta").PrzesuńDalejDalej jaKończyć zpierwsze.ZamknijUstaw pierwsze = NicUstaw dbs = NicNapis końcowy

Samouczki dotyczące tablic
Mega-przewodnik po tablicy	tak
Uzyskaj rozmiar tablicy
Wyczyść tablicę
Filtruj tablicę
Transpozycja tablicy
Funkcja zwracana tablica
Usuń duplikaty