Tablice w VBA Excel – Podstawy

Czym są tablice w VBA Excel – VBA Arrays

W tym rozdziale kursu VBA dowiesz się, czym są tablice VBA i jak ich używać w analizie danych. Tablice VBA to kontenery do przechowywania danych o tym samym typie w sposób zorganizowany. Do każdego elementu tablicy możemy się odnieść za pomocą jego indeksowanego adresu. Tablice możesz też porównać do zmiennych, które przechowują inne zmienne. Często w kurach programowania możesz się spotkać z nazwą VBA Arrays – chodzi właśnie o tablice. Za ich pomocą w łatwy i szybki sposób masz dostęp do danych w VBA bez konieczności ich każdorazowego pobierania np z arkusza. Tablice w VBA Excel możemy podzielić na kilka rodzajów w zależności o liczby wymiarów tablicy. Każdy element tablicy może przechowywać dane:

• Tablice jednowymiarowe, tzw. listy
• Tablice dwuwymiarowe, o kształcie tabeli
• Tablice trójwymiarowe, o kształcie sześcianu
• Tablice wielowymiarowe (dla więcej niż 3 wymiarów, maksymalnie 60 wymiarów)

Tablice w Excel VBA powinniśmy zadeklarować. Po deklaracji możemy wprowadzać do nich elementy. Po wprowadzeniu danych do tablicy, dane możemy z tablicy pobierać. Poniżej zobrazowanie tablic VBA.Tablice wielowymiarowe ciężko zobrazować, więc sposób ich zobrazowania jest abstrakcyjny.

Deklarowanie i adresowanie elementów tablicy VBA

Tablice w VBA należy zadeklarować. Tablice tak jak i zmienne VBA posiadają typ danych. Typ danych dla wszystkich elementów tablicy także możemy zadeklarować. Jeśli tego nie zrobimy, dane w tablicy uzyskają typ Variant. Ważne jest, że wszystkie elementy tablicy są tego samego typu danych. Deklarację możemy wykonać na kilka równoważnych sposobów:

Deklarowanie tablicy z określeniem całkowitej liczby elementów:

Deklarując tablicę w ten sposób określasz całkowitą liczbę elementów, którą może przyjąć tablica. Najważniejszą rzeczą przy tej deklaracji jest to, że w nawiasie określasz indeks ostatniego elementu tablicy. Tablice VBA domyślnie numerowane są od zera. Tak więc, jeśli chcesz zadeklarować np tablicę jednowymiarową o liczbie 10 elementów, powinieneś zapisać to w ten sposób Dim NazwaTablicy(9). Następnie do poszczególnych części tablicy odnosisz się liczbami od 0 do 9.

'tomaszkenig.pl
'zadeklarowanie tablicy jednowymiarowej posiadającej 11 elementów, bez określenie typu danych:
Dim TabTablica1(10)
'zadeklarowanie tablicy trójwymiarowej po 11 elementów w każdym wymiarze, z określeniem typu danych:
Dim TabTablica2(10, 10, 10) as String

Deklarowanie tablicy z określeniem indeksów poszczególnych elementów:

Deklarowanie tablicy z określeniem indeksów polega na określeniu numeru pierwszego i ostatniego elementu każdego wymiaru tablicy np 1 do 12 lub 2000 do 2005. Deklarowanie z określeniem numeru indeksu w znaczny sposób upraszcza korzystanie z elementów tablicy. Możemy nadać numer indeksu na wzór np numeru roku, miesiąca, dnia tygodnia itp.

'tomaszkenig.pl
'zadeklarowanie tablicy jednowymiarowej z określeniem indeksów dla 10 elementów:
Dim TabTablica1(1 To 10)
'zadeklarowanie tablicy trójwymiarowej z określeniem indeksów i typu danych:
Dim TabTablica2(2017 To 2025, 1 To 12, 1 To 7) As String

Mieszane deklarowanie tablicy

Możemy też zadeklarować tablice w sposób mieszany. W deklaracji używamy jednocześnie obydwa poznane wcześniej sposoby.

'tomaszkenig.pl
'zadeklarowanie tablicy trójwymiarowej z określeniem typu danych:
Dim TabTablica2(2017 To 2025, 10) As String

Zmiana liczby elementów w tablicy

W VBA Excel możemy także zadeklarować tablicę, bez określenia liczby elementów i wymiarów, które powinna posiadać. Próba wprowadzenia danych do takiej tablicy zakończy się błędem. By uniknąć błędu, liczbę elementów tablicy powinniśmy określić za pomocą polecenia ReDim, jak na przykładzie poniżej. Możemy też naszą tablicę powiększyć o kilka elementów.

'tomaszkenig.pl
Dim TabTable()
ReDim TabTable(1 To 12, 6)
ReDim TabTable(1 To 50, 10, 2015 To 2030)

Adresowanie elementów tablicy

Gdy tablica jest już zadeklarowana, możemy wprowadzać do niej dane oraz je pobierać. By wykonywać te czynności powinniśmy właściwie adresować elementy tablicy. Elementy tablicy adresujemy za pomocą jej indeksów. Do elementów zadeklarowanych tablic, w celu zapisu lub odczytu danych odnosimy się w następujący sposób: Nazwa_tablicy(numer_indeksu).

Wprowadzanie danych do tablicy VBA Excel

By wprowadzić daną do tablicy należy określić wymiar, a więc pola tablicy, które powinny zostać uzupełnione. W zależności od przyjętej metody deklaracji, wybieramy pole tablicy wg. numerowanego indeksu.

Sub TabExample()

Dim TabTable(6) As String

TabTable(0) = "Poniedziałek"
TabTable(1) = "Wtorek"
TabTable(2) = "Środa"
TabTable(3) = "Czwartek"
TabTable(4) = "Piątek"
TabTable(5) = "Sobota"
TabTable(6) = "Niedziela"

End Sub

Jak zapewne zauważyłeś adresowanie tablic zadeklarowanych z określeniem całkowitej liczby elementów zaczyna się od zera. Jest to numer pierwszego indeksu tablicy. Tak więc Jeśli chciałbyś, by tablica posiadała 10 elementów powinieneś zadeklarować tablicę w następujący sposób NazwaTablicy(9). Jeśli chciałbyś, by tablica zadeklarowana w ten sposób posiadała indeks pierwszego elementu zaczynający się od liczby 1, powinieneś wprowadzić przed deklaracją tablicy polecenie Option Base 1.

'tomaszkenig.pl
Sub TabExample()

Option Base 1
Dim TabTable(6) As String

TabTable(1) = "Poniedzialek"
TabTable(2) = "Wtorek"
TabTable(3) = "Sroda"
'(...)
End Sub

Jeśli chcemy wprowadzić dane do tablic wielowymiarowych, postępujemy w podobny sposób, określając element wg. jego indeksów względem poszczególnych wymiarów tablicy.

'tomaszkenig.pl
Sub Table()
'deklaracja tablicy twójwymiarowej
Dim TabTablica(1 To 31, 1 To 12, 2000 To 2030)
'Wprowadzenie elementu do tablicy
TabTablica(30, 9, 2017) = WeekdayName(2, False, vbMonday)
'Potwierdzenie, że pole tablicy zawiera dane
MsgBox TabTablica(30, 9, 2017)
End Sub

Dane do tablic możemy także wprowadzić bezpośrednio z arkusza danych lub z okna InputBox VBA. W jaki sposób to zrobić? Poniżej przykład:

'tomaszkenig.pl
Sub TableInputBox()
'deklaracja tablicy twójwymiarowej
Dim TabTablica(1 To 10, 1 To 10, 1 To 10) As Variant
'Wprowadzenie elemenu do tablicy z okna inputbox
TabTablica(1, 1, 1) = InputBox("Wprowadz dane do pola 1,1,1")
'Potwierdzenie, ze pole tablicy zawiera dane
MsgBox TabTablica(1, 1, 1)

'Wprowadzenie danej do Arkusza - komórka A1
Range("A1") = "Moja dana"
'Pobranie danej do tablicy z komórki arkusza
TabTablica(2, 2, 2) = Range("A1")
'Potwierdzenie poprzez pobranie danej z tablicy
MsgBox TabTablica(2, 2, 2)
End Sub

Pobieranie danych z tablicy VBA Excel

Jeśli chcesz pobrać daną z tablicy, jedyne, co powinieneś zrobić to właściwie zaadresować pole tablicy. Daną z  tablicy możesz pobrać do zmiennej VBA, wyświetlić w oknie VBA MsgBox, wprowadzić bezpośrednio do arkusza danych. Poniżej przykład:

'tomaszkenig.pl
Sub TableProgram()
'deklaracja tablicy twójwymiarowej
Dim TabTablica(1 To 10, 1 To 10, 1 To 10) As Variant
'Wprowadzenie elemenu do tablicy z okna inputbox
TabTablica(1, 1, 1) = InputBox("Wprowadz dane do pola 1,1,1")

'Wprowadzenie danej z tablicy do komórka A1
Range("A1") = TabTablica(1, 1, 1)
'Wprowadzenie danej z tablicy do okna MsgBox
MsgBox TabTablica(1, 1, 1)
End Sub

Zastosowanie tablic w VBA Arrays w Excel

Do czego możemy wykorzystać tablice w VBA? Tablice przede wszystkim przyspieszają obliczenia wykonywane na danych w Excel. Możemy tworzyć z ich użyciem skomplikowane modele matematyczne. W kolejnym rozdziale kursu VBA poznasz dodatkowe informacje dotyczące tablic VBA. Dowiesz się jak tworzyć tablice przy użyciu funkcji Array. Dowiesz się jak przeprowadzać operacje masowe pomiędzy tablicami i arkuszem danych. Stworzysz także model matematyczny z użyciem tablicy wielowymiarowej.

Zadania (Rozwiązanie możesz wpisać w komentarzu)

1 Stwórz tablicę dwuwymiarową, po 7 i 12 elementów w wymiarach. Przypisz do elementów tablicy nazwy dni tygodnia i nazwy miesięcy.

2 Zmodyfikuj powyższy przykład. Wprowadź elementy z tablicy z zadania 6.1 do arkusza danych.

3 Stwórz tablicę jednowymiarową. Przypisz do niej lata od 2000 do 2030 za pomocą pętli Do While.