Menu

Excel VBA Array and Array Methods with Przykłady

  • Główny
  • Inny
  • Excel VBA Array and Array Methods with Przykłady

excel vba array array methods with examples

Wypróbuj Nasz Instrument Do Eliminowania Problemów

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

Tablice w VBA

najlepsze oprogramowanie szpiegujące dla telefonów komórkowych z systemem Android

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.

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 Sub

Naciśnij klawisz F5 lub naciśnij przycisk uruchamiania na pasku narzędzi, aby wykonać kod.

Jednowymiarowa tablica - wyjście

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 Sub

Teraz 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 Sub

Tutaj 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 Sub

Naciśnij klawisz F5 lub naciśnij przycisk uruchamiania na pasku narzędzi, aby wykonać kod.

Wiersz 2 i kolumna 2

Tablica dwuwymiarowa - przykład

Wiersz 1 i kolumna 3

Dwuwymiarowa tablica - wyjście

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

REDIM

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

Przykład REDim

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

REDim Preserve

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

Tablica wariantów

Metody tablicowe VBA

Istnieje kilka metod w tablicach VBA, które pomogą nam wykonywać różne funkcje, jak wspomniano poniżej.

Sl. NieNazwaSkładniaOpis
7 Przystąp Połącz (tablica źródłowa, (separator))Łączy wiele podciągów w tablicy i zwraca wartość ciągu.
jeden Szyk Tablica (fałszywa)Konwertuje zwykły wariant
zmiennej w Array.
dwa Wymazać Usuń nazwę tablicySł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.
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

Szyk

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 Sub

Wynik przed użyciem funkcji Wymaż

Wynik przed użyciem funkcji Wymaż

Wynik po użyciu 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)) End

Wynik z pierwszego Msgbox

IsArray - wynik z pierwszego Msgbox

co zrobić z plikiem bin

Wynik z drugiego msgbox

IsArray - wynik z drugiego Msgbox

# 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

związany

# 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

Ubound

# 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

Rozdzielać

# 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 Sub

Wszystkie 3 wartości są łączone, a jest umieszczane między każdym słowem, jak wspomnieliśmy jako separator.

Przystąp

# 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 Sub

Ten przykład wyszuka słowo „pomoc” w całym ciągu tablicy przy użyciu funkcji filtru.

Filtr

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

przekonwertuj Range na tablicę

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

^