excel vba array array methods with examples
Ten samouczek objaśni tablicę VBA, różne typy tablic, tablice wariantowe i metody tablicowe za pomocą przykładów programowania:
Zwykła zmienna VBA to element zastępczy, który przechowuje wartość pojedynczych danych. Ma relację 1 do 1, czyli 1 zmienną na 1 wartość.
Teraz wyobraź sobie przechowywanie wielu wartości tego samego typu. Zamiast tworzyć wiele zmiennych, możesz po prostu utworzyć jedną zmienną i przechowywać wartości tego samego typu. Ta zmienna nazywa się ARRAY.
=> Odwiedź tutaj, aby zobaczyć serię szkoleń VBA dla wszystkich
W tym samouczku dowiesz się, czym jest tablica VBA, tablice jednowymiarowe i dwuwymiarowe wraz z różnymi typami tablic, takimi jak Stałe i Dynamiczne. Zrozumiemy także różne metody tablicowe używane w VBA.
najlepsze oprogramowanie szpiegujące dla telefonów komórkowych z systemem Android
Czego się nauczysz:
VBA Array
Tablice to specjalny rodzaj zmiennych, które mogą przechowywać wiele wartości tego samego typu danych.
Na przykład, jeśli masz nazwiska 100 pracowników, to zamiast tworzyć 100 zmiennych typu string, możesz po prostu utworzyć jedną zmienną tablicową typu string i przypisać 100 wartości do tej samej zmiennej tablicowej.
Jednowymiarowa tablica
Tablica zawierająca wszystkie elementy w jednym wierszu lub w jednej kolumnie nazywana jest tablicą jednowymiarową. Podanie nazwisk wszystkich uczniów w klasie w jednej kolumnie jest przykładem jednowymiarowej tablicy. Jest zadeklarowane, jak pokazano poniżej.
Dim arrayname (bottombound To UpperBound) As DataType
Istnieje wiele sposobów zadeklarowania tablicy. Poniżej podano kilka przykładów.
Przykład:
# 1) Dim MyArrayExample (0 do 3) As Integer
Tworzy tablicę z lokalizacją 0, 1, 2, 3, która będzie akceptować wartości całkowite.
# 2) Dim MyArray2 (3) As String
Domyślnie od 0 do 3 i tworzy tablicę z lokalizacją 0, 1, 2, 3, która będzie akceptować wartości typu String.
# 3) Dim MyArray2 (13 do 15) As Double
Tworzy tablicę zaczynającą się od 13, tj. 13, 14 i 15, i akceptuje wartości Double. Wspomnieliśmy o dolnej granicy jako 13, więc tablica zacznie przydzielać wartości od lokalizacji 13 zamiast 0.
Stwórzmy prosty kod i zrozummy wszystkie 3 sposoby deklarowania tablicy.
Uwaga: Aby napisać kod VB Otwórz Microsoft Excel (obsługiwane wersje to Excel 2007, 2010, 2013, 2016, 2019). Nawigować do Karta Deweloper -> Visual Basic (Alternatywnie użyj skrótu Alt + F11). W edytorze VB kliknij Wstaw -> Moduł i wklej poniższy kod.
Rozważ poniższą procedurę przedstawiającą różne typy deklaracji.
Private Sub arrayExample1() Dim firstQuarter(0 To 2) As String ‘creates array with index 0,1,2 firstQuarter(0) = 'Jan' firstQuarter(1) = 'Feb' firstQuarter(2) = 'Mar' MsgBox 'First Quarter in calendar ' & ' ' & firstQuarter(0) & ' ' & firstQuarter(1) & ' ' & firstQuarter(2) End Sub Private Sub arrayExample2() Dim secondQuarter(2) As String ‘creates array with index 0,1,2 secondQuarter(0) = 'April' secondQuarter(1) = 'May' secondQuarter(2) = 'June' MsgBox 'Second Quarter in calendar ' & ' ' & secondQuarter(0) & ' ' & secondQuarter(1) & ' ' & secondQuarter(2) End Sub Private Sub arrayExample3() Dim thirdQuarter(13 To 15) As String ‘creates array with index 13,14,15 thirdQuarter(13) = 'July' thirdQuarter(14) = 'Aug' thirdQuarter(15) = 'Sep' MsgBox 'Third Quarter in calendar ' & ' ' & thirdQuarter(13) & ' ' & thirdQuarter(14) & ' ' & thirdQuarter(15) End SubNaciśnij klawisz F5 lub naciśnij przycisk uruchamiania na pasku narzędzi, aby wykonać kod.
Zmienna regularna Vs Zmienna tablicowa
Teraz wiemy, jak działa jednowymiarowa tablica. Poświęćmy więc chwilę, aby zrozumieć, dlaczego tablice są tak ważne w językach programowania.
Załóżmy, że musisz podać wynagrodzenie 5 pracowników. Aby to osiągnąć za pomocą zwykłej zmiennej, musisz utworzyć 5 zmiennych.
Public Sub RegularVariable() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets('Sheet1') ' Declare variable for each student Dim Emp1 As String Dim Emp2 As String Dim Emp3 As String Dim Emp4 As String Dim Emp5 As String ' Read student marks from cell Emp1 = shet.Range('A' & 2).Value Emp2 = shet.Range('A' & 3).Value Emp3 = shet.Range('A' & 4).Value Emp4 = shet.Range('A' & 5).Value Emp5 = shet.Range('A' & 6).Value ' Print student marks Debug.Print 'Emp Name' Debug.Print Emp1 Debug.Print Emp2 Debug.Print Emp3 Debug.Print Emp4 Debug.Print Emp5 End SubTeraz stwórzmy ten sam kod, używając zmiennej typu Array.
Option Explicit Public Sub ArrayVarible() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets('Sheet1') Dim Employee(1 To 6) As String Dim i As Integer For i = 1 To 6 Employee(i) = shet.Range('A' & i).Value Debug.Print Employee(i) Next i End SubTutaj właśnie użyliśmy jednej zmiennej tablicowej, która będzie przechowywać wszystkie nazwiska pracowników. Załóżmy, że musisz dodać 100 kolejnych nazwisk pracowników, a następnie wystarczy zmienić rozmiar tablicy i nie musisz tworzyć nowej zmiennej.
Zmniejszy to liczbę wierszy w kodzie, a tym samym sprawi, że będzie on łatwiejszy do zrozumienia i czytelny.
Tablica dwuwymiarowa
Dwuwymiarowa tablica ma 2 indeksy - pierwszy indeks będzie reprezentował wiersze, a drugi indeks będzie reprezentował kolumnę. Ma wiele wierszy i kolumn i zwykle jest reprezentowany w formacie tabeli.
Deklaracja tablicy 2 dim jest następująca:
Dim ArrayName (FirstIndex To LastIndex, FirstIndex To LastIndex) As DataType.
Rozważmy przykład przechowywania ocen 2 uczniów uzyskanych z 3 przedmiotów. Dlatego utworzymy dwuwymiarową tablicę, która ma 2 wiersze i 3 kolumny.
Zaczniemy tablicę od wiersza 1 do wiersza 2 i kolumny 1 do kolumny 3.
Sub Twodim() Dim totalMarks(1 To 2, 1 To 3) As Integer totalMarks(1, 1) = 23 totalMarks(2, 1) = 34 totalMarks(1, 2) = 33 totalMarks(2, 2) = 55 totalMarks(1, 3) = 45 totalMarks(2, 3) = 44 Msgbox “Total Marks in Row 2 and column 2 is “ &totalMarks(2,2) Msgbox “Total Marks in Row 1 and column 3 is “ &totalMarks(1,3) End SubNaciśnij klawisz F5 lub naciśnij przycisk uruchamiania na pasku narzędzi, aby wykonać kod.
Wiersz 2 i kolumna 2
Wiersz 1 i kolumna 3
Naprawiono tablice
Tablice ustalone, zwane również tablicami statycznymi, mają ustaloną dolną i górną granicę i tego rozmiaru nie można zmienić w czasie wykonywania. Rozmiar tablicy jest określany podczas deklaracji w nawiasach. Wszystkie powyższe przykłady są tablicami Fixed, ponieważ wspomnieliśmy o ich rozmiarze podczas deklaracji.
Tablice ustalone są zwykle używane, gdy masz pewność co do rozmiaru tablicy. Na przykład, liczbę dni w tygodniu, możesz utworzyć tablicę z dolną granicą 0 i górną granicą 6 i być pewnym, że nigdy nie zmienisz jej rozmiaru.
Tablice dynamiczne
Tablice dynamiczne pozwalają nam zmieniać rozmiar tablicy w czasie wykonywania. Są one przydatne, gdy nie masz pewności co do rozmiaru tablicy. Załóżmy, że przy przyjęciu na studia możesz nie być pewien, ilu uczniów faktycznie otrzyma przyjęcie, więc nie możesz określić rozmiaru na etapie projektowania lub deklaracji.
Deklaracja tablicy dynamicznej jest podobna do tablicy statycznej z pustymi nawiasami.
Dim Employee () As String
REDIM
Kiedy chcemy zmienić rozmiar, którego potrzebujemy REDIM , musimy zauważyć, że dolnej granicy nie można zmienić, możemy tylko zmienić górną granicę tablicy.
Sub dynamicArray() Dim dynArray() As String Dim curdate As Date curdate = Now ReDim dynArray(2) ‘ Redim will help to change the array size during runtime dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled after ' & curdate & ' are “ & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) End Sub 
Teraz wiemy, że możemy zmienić rozmiar tablicy w czasie wykonywania, dlatego możemy użyć instrukcji ReDim, gdy potrzebujemy zwiększyć podbicie tablicy. Spróbujmy jeszcze raz zwiększyć rozmiar tablicy i dodać nowe imię i nazwisko ucznia.
Sub RedimExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) ReDim dynArray(3) ‘ Redim will reinitialise the array and destroy the old values dynArray(3) = 'John' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) & ' , ' & dynArray(3) End Sub 
Można zauważyć, że wynik nie zawiera nazw uczniów dodanych wcześniej, daje wartość null. Dzieje się tak, ponieważ instrukcja Redim utworzy nową tablicę z nowym rozmiarem i zniszczy stare wartości.
ReDim Preserve
Instrukcja Represerve pomaga nam przezwyciężyć ograniczenia ReDim poprzez zachowanie starych wartości, a tym samym zwiększenie rozmiaru tablicy.
Przepiszmy powyższy kod za pomocą ReDim Preserve.
Sub preserveExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) ReDim preserve dynArray(3) ‘ Redim preserve will retain the old values dynArray(3) = 'John' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) & ' , ' & dynArray(3) End Sub 
Ponieważ użyliśmy słowa kluczowego preserve, wcześniej wprowadzone wartości nie zostaną utracone i nowa wartość zostanie pomyślnie dodana.
Tablica wariantów
Do tej pory widzieliśmy tablicę akceptującą wartości tego samego typu. Teraz zadeklarujmy tablicę jako wariant i zapiszmy różne typy danych, takie jak String, Date, Long, Integer w jednej tablicy.
Przykład:
Sub arrayVariant() Dim arrayData(3) As Variant arrayData(0) = 'Vikram Vikrant' arrayData(1) = 411234567890# arrayData(2) = 38 arrayData(3) = '06-09-1972' MsgBox 'Details of person ' & arrayData(0) & ' is ' & ' Phone No ' & arrayData(1) & ' ,Id ' & arrayData(2) & ' ,DOB ' & arrayData(3) End Sub
Metody tablicowe VBA
Istnieje kilka metod w tablicach VBA, które pomogą nam wykonywać różne funkcje, jak wspomniano poniżej.
| Sl. Nie | Nazwa | Składnia | Opis |
|---|---|---|---|
| jeden | Szyk | Tablica (fałszywa) | Konwertuje zwykły wariant zmiennej w Array. |
| dwa | Wymazać | Usuń nazwę tablicy | Służy do ponownej inicjalizacji tablicy o stałym rozmiarze i zwalnia pamięć dla tablicy dynamicznej. |
| 3 | IsArray | IsArray (nazwa_zmiennej) | Określa, czy zmienna jest tablicą. |
| 4 | Lbound | LBound (ArrayName, [Dimension]) | Zwraca najniższy indeks tablicy. |
| 5 | Ubound | UBound (ArrayName, [Dimension]) | Zwraca najwyższy indeks tablicy. |
| 6 | Rozdzielać | Podziel (wyrażenie, [separator, [limit, [porównanie]]]) | Dzieli ciąg na wiele podciągów i zwraca tablicę zaczynającą się od zera. |
| 7 | Przystąp | Połącz (tablica źródłowa, [separator]) | Łączy wiele podciągów w tablicy i zwraca wartość ciągu. |
| 8 | Filtr | Filtr (tablica źródłowa, dopasuj, [dołącz, [porównaj]]) | Filtr pozwoli nam przeszukać plik określone dopasowanie z tablicy. |
Omówmy szczegółowo każdy z nich na przykładzie.
# 1) Tablica
Zadeklarujmy zwykłą zmienną wariantową i użyjmy jej jako tablicy. Jeśli chcesz zmienić zwykłą zmienną wariantu w tablicę, musimy użyć SZYK działają, jak pokazano w poniższym przykładzie.
Funkcje tablicowe akceptują argument zawierający wartości oddzielone przecinkami. Te wartości są przypisywane jako element tablicy.
Sub variantArray() Dim varData As Variant varData = Array('Mon Bel', '+61 112334123', 567, '06-09-1972') MsgBox 'Details of person ' & varData(0) & ' is ' & ' Phone No ' & varData(1) & ' ,Id ' & varData(2) & ' ,DOB ' & varData(3) End Sub 
Musisz zidentyfikować zmienną tablicową za pomocą indeksu, stąd w powyższym przykładzie wartości są pobierane jako varData (0) varData (2) varData (3).
# 2) Wymaż
Ta funkcja usunie wszystkie wartości wprowadzone dla tablicy o stałym rozmiarze i zwolni miejsce w pamięci dla tablicy dynamicznej.
Składnia: Erase arrayname
Wymazywanie ma różne zachowanie dla różnych typów danych, jak podano poniżej.
- W przypadku stałej liczby: Wszystkie wartości są zerowane.
- W przypadku stałego typu danych łańcuchowych: Wszystkie wartości są zerowane.
- W przypadku tablicy dynamicznej: Zwalnia pamięć używaną przez tablicę.
Przykład:
Sub eraseExample() Dim NumArray(3) As Integer Dim decArray(2) As Double Dim strArray(2) As String NumArray(0) = 12345 decArray(1) = 34.5 strArray(1) = 'Erase Function' Dim DynaArray() ReDim DynaArray(3) MsgBox ' Values before Erase ' & (NumArray(0)) & ',' & (decArray(1)) & ' , ' & (strArray(1)) Erase NumArray Erase decArray Erase strArray Erase DynaArray ' Free the memory ' All values are erased. MsgBox ' Values after Erase ' & NumArray(0) & ',' & decArray(1) & ' , ' & strArray(1) End SubWynik przed użyciem funkcji Wymaż
Wynik po użyciu Wymaż
# 3) IsArray
Ta funkcja służy do określenia, czy dana zmienna wejściowa jest tablicą, czy nie. Zwraca prawdę, jeśli wprowadzona zmienna jest prawdziwa, w przeciwnym razie zwraca fałsz.
Składnia: IsArray (nazwa_zmiennej)
Przykład:
Sub isArrayTest() Dim arr1, arr2 As Variant arr1 = Array('Jan', 'Feb', 'Mar') arr2 = '12345' MsgBox ('Is arr1 an Array : ' & IsArray(arr1)) MsgBox ('Is arr2 an Array : ' & IsArray(arr2)) EndWynik z pierwszego Msgbox
Wynik z drugiego msgbox
co zrobić z plikiem bin
# 4) Lbound
Zwraca najniższy indeks tablicy określonej jako argument funkcji Lbound.
Składnia: LBound (ArrayName; [Dimension])
ArrayName to nazwa tablicy.
Wymiar to opcjonalna liczba całkowita, jeśli tablica ma wiele wymiarów, możesz określić, do którego wymiaru chcesz określić Lbound.
Przykład:
Sub lboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim Arraywithoutlbound(10) Result1 = LBound(ArrayValue, 1) ' Returns 1. Result2 = LBound(ArrayValue, 3) ' Returns 10. Result3 = LBound(Arraywithoutlbound) MsgBox 'Lowest subscript in first array ' & Result1 & ' lowest subscript in 3rd array ' & Result2 & ' Lowest subscript in Arraywithoutlbound ' & Result3 End Sub
# 5) Ubound
Zwraca górny indeks tablicy określonej jako argument w funkcji Ubound.
Składnia: UBound (ArrayName; [Dimension])
ArrayName to nazwa tablicy.
Wymiar to opcjonalna liczba całkowita, jeśli tablica ma wiele wymiarów, możesz określić, który wymiar ma zostać określony dla Ubound.
Przykład:
Sub UboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim ArraywithoutUbound(10) Result1 = UBound(ArrayValue, 1) Result2 = UBound(ArrayValue, 3) Result3 = UBound(ArraywithoutUbound) MsgBox 'Lowest subscript in first array ' & Result1 & ' lowest subscript in 3rd array ' & Result2 & ' Lowest subscript in Arraywithoutlbound ' & Result3 End Sub 
# 6) Podziel
Zwraca tablicę z liczbą podciągów pochodzących z podanego całego ciągu.
Składnia: Split (wyrażenie, [separator, [limit, [porównanie]]])
- Wyrażenie: To jest cały ciąg, który będzie używany do tworzenia podciągów.
- Separator: Przy użyciu określonego separatora zostaną wygenerowane podciągi. Jeśli nie zostało to wspomniane, za separator uważa się spację.
- Limit: Liczba podciągów do zwrócenia.
- Porównać: Po utworzeniu podciągu można użyć różnych opcji porównania, aby przetestować wynik.
Przykład: W poniższym przykładzie używamy separatora jako - i ograniczenia jako 3.
Dlatego funkcja split rozdzieli cały ciąg na podciąg na podstawie separatora. Ale wspomnieliśmy również o limicie jako 3, więc podciągi nie zostaną utworzone po limicie 3. W ten sposób ostatni separator - zostanie pominięty.
Sub splitExample() Dim MyString As String Dim Result() As String Dim DisplayText As String MyString = 'This is the example for-VBA-Split-Function' Result = Split(MyString, '-',3) MsgBox Result(0) & vbNewLine & Result(1) & vbNewLine & Result(2) & vbNewLine & Result(3) End Sub
# 7) Dołącz
To tylko odwrotność podziału, funkcja Join utworzy jeden ciąg, łącząc kilka podciągów.
Składnia: Join (tablica źródłowa; [separator])
Sourcearray: Jednowymiarowa tablica ciągów, które chcesz połączyć w jeden.
Separator: Określony separator zostanie dodany po każdym łańcuchu podczas łączenia.
Przykład:
Sub joinExample() Dim Result As String Dim dirarray(0 To 2) As String dirarray(0) = 'D:' dirarray(1) = 'SoftwareTestingHelp' dirarray(2) = 'Arrays' Result = Join(dirarray, '') MsgBox 'Date after joining ' & Result End SubWszystkie 3 wartości są łączone, a jest umieszczane między każdym słowem, jak wspomnieliśmy jako separator.
# 8) Filtruj
Filtr pozwoli nam wyszukać określone dopasowanie z tablicy. Na podstawie kryteriów filtru zostanie zwrócony podzbiór tablicy ciągów.
Składnia: filtr (tablica źródłowa, dopasowanie, [uwzględnij, [porównaj]])
Przykład:
Sub filterExample() Dim Mystring As Variant Mystring = Array('Software Testing', 'Testing help', 'Software help') filterString = Filter(Mystring, 'help') MsgBox 'Found ' & UBound(Mystring) - LBound(Mystring) + 1 & ' words matching the criteria ' End SubTen przykład wyszuka słowo „pomoc” w całym ciągu tablicy przy użyciu funkcji filtru.
Często Zadawane Pytania
Q # 1) Jak uzyskać długość tablicy w VBA?
Odpowiedź: Aby uzyskać długość tablicy, używamy funkcji Ubound. Ta funkcja da nam górny indeks określonej tablicy.
Q # 2) Jak zadeklarować tablicę w VBA?
Odpowiedź: Jednowymiarowa tablica jest zadeklarowana, jak pokazano poniżej.
Dim arrayname (bottombound To UpperBound) As DataType
Przykład: Dim Myarray (0 do 2) As Integer
Dwuwymiarowa tablica jest zadeklarowana, jak pokazano poniżej.
Dim ArrayName (FirstIndex To LastIndex, FirstIndex To LastIndex) As DataType.
Przykład: Znaki dim (1 do 3, 0 do 2) jako liczby całkowite
Q # 3) Jak przekonwertować Range na Array?
Odpowiedź: Możemy użyć funkcji Transpose, aby przekonwertować zakres na tablicę. Ten kod utworzy Mys [10]
Sub Example() Dim Mys As Variant Mys = Application.Transpose(Range('A1:A10')) End Sub 
P # 4) Co to jest wariant tablicy w języku VBA?
Odpowiedź: Tablica wariantowa przyjmuje wszystkie rodzaje danych jako indeks, tj. Można przechowywać różne typy wartości w jednej tablicy.
Przykład:
Dim arrayData (3) jako wariant
arrayData (0) = 'Vikas Vipal'
arrayData (1) = 411234567890 #
P # 5) Co to jest instrukcja podstawowa opcji?
Odpowiedź: Służy do deklarowania dolnego indeksu tablicy i jest używany na początku modułu. Domyślnie dolny indeks to 0, jeśli zadeklarujesz opcję Base 1 na poziomie modułu, wówczas dla wszystkich tablic domyślny dolny indeks będzie wynosił 1.
Wniosek
W tym samouczku nauczyliśmy się, jak tablice są używane w VBA i widzieliśmy, jak tablice różnią się od zwykłych zmiennych. Zbadaliśmy tablice jednowymiarowe i dwuwymiarowe z przykładami. Omówiliśmy również tablice stałe i dynamiczne.
Sposoby zmiany rozmiaru tablicy w czasie wykonywania, a także zachowania wartości za pomocą redim preserve, zostały omówione z przykładami. W końcu poznaliśmy metody Array, które pomogą nam w wykonaniu kilku operacji.
=> Sprawdź WSZYSTKIE samouczki VBA tutaj
rekomendowane lektury
- Samouczek Excel VBA - Wprowadzenie do VBA w programie Excel
- Typy danych VBA - Numeryczne i nieliczbowe typy danych w VBA
- Zmienne VBA i opcje jawne w VBA
- Odwróć tablicę w Javie - 3 metody z przykładami
- Samouczek dotyczący długości tablicy w języku Java z przykładami kodu
- Postrzępiona tablica w Javie - samouczek z przykładami
- Typy danych tablic - int Array, Double array, Array of Strings itp.
- String Array C ++: Implementacja i reprezentacja z przykładami