Lekcja została podzielona na następujące części:

• Deklarowanie tablic
• Przypisywanie i pobieranie wartości
• Zmiana indeksowania tablic
• Skrócona wersja indeksowania tablic
• Podpatrywanie wartości zmiennych za pomocą okna Locals

Tablica VBA jest specjalną konstrukcją służącą do przechowywania wielu wartości dotyczących podobnego rodzaju danych.

Abyś mógł sobie lepiej uzmysłowić, co to tak naprawdę oznacza, załóżmy że chcesz napisać makro obliczające jakiś skomplikowany wskaźnik rentowności, które wymaga danych na temat wartości sprzedaży oraz kosztów poniesionych w każdym z dwunastu miesięcy roku. Gdybyś chciał w tym makrze użyć tylko zwykłych zmiennych, takich jakie wielokrotnie pojawiały się już podczas tego kursu, musiałbyś użyć do tego celu 24 zmiennych: 12 do opisania sprzedaży (jedna zmienna na każdy miesiąc) oraz 12 zmiennych opisujących koszty (również jedna na każdy miesiąc). Deklaracja zmiennych wyglądałaby więc mniej więcej tak:

1
2
3
4
5
6
7
8
9
10
11
12


Dim sprzedaz_styczen As Double
Dim sprzedaz_luty As Double
Dim sprzedaz_marzec As Double
Dim sprzedaz_kwiecien As Double
Dim sprzedaz_maj As Double
'(...)
Dim koszty_styczen As Double
Dim koszty_luty As Double
Dim koszty_marzec As Double
Dim koszty_kwiecien As Double
Dim koszty_maj As Double
'(...)


Aż strach pomyśleć, co by było, gdyby obliczenia wymagały takich danych z kilku lat, a dodatkowo w ujęciu dziennym. Oczywiście byłoby to praktycznie niewykonalne, a nawet jeśli ktoś uporałby się z zadeklarowaniem wszystkich tych zmiennych, to praca z kodem, w którym znajdowałoby się tysiące niemal identycznych zmiennych, byłaby koszmarem, a niepopełnienie żadnego błędu graniczyłoby z cudem.

Właśnie w takich sytuacjach, jak opisana powyżej, należy sięgnąć po tablice.

Tablice swą strukturą przypominają arkusz Excela - posiadają wiersze, kolumny oraz komórki, w których przechowywane są wartości.

W omawianym wcześniej przykładzie, zamiast tych wszystkich zmiennych liczbowych, można byłoby zadeklarować dwie zmienne tablicowe - przychody oraz koszty, z których każda zawierałaby 12 wierszy i 1 kolumnę (lub odwrotnie). W sytuacji, gdyby dane miały obejmować kilka lat, wystarczyłoby po prostu dodać nową kolumnę dla każdego nowego roku.

Tym, co odróżnia tablice VBA od arkusza Excela jest liczba wymiarów. Arkusz Excela posiada zawsze dwa wymiary określone przez numer wiersza oraz numer kolumny. Tablice VBA mogą natomiast posiadać zróżnicowaną liczbę wymiarów - od jednego do trzydziestu (choć w praktyce tablice posiadające więcej niż trzy wymiary należą do rzadkości).

Deklaracja tablicy wygląda następująco:

 

Dim tablica (x1 To y1, ..., xn To yn) As typ_danych

Tablica posiada tyle wymiarów, ile wyrażeń xn To yn zawartych jest w nawiasie po nazwie tablicy. Każde takie wyrażenie określa rozmiar poszczególnego wymiaru tablicy.

W poniższej ramce znajdują się przykłady deklaracji kilku tablic (w komentarzu nad deklaracją opisana jest liczba wymiarów oraz rozmiar tej tablicy):

 
 
 
 
 
 
 
 
 
 
 


'tablica jednowymiarowa zawierająca 12 komórek
Dim tablica(1 To 12) As String

'tablica dwuwymiarowa o rozmiarach 10x10
Dim tablica(1 To 10, 1 To 10) As Single

'tablica trójwymiarowa o rozmiarach 10x20x30
Dim tablica(1 To 10, 1 To 20, 1 To 30) As Double

'tablica sześciowymiarowa o rozmiarach 8x7x8x6x8x4
Dim tablica(1 To 8, 1 To 7, 1 To 8, 1 To 6, 1 To 8, 1 To 4) As Double


Poniżej znajduje się poglądowy rysunek przedstawiający strukturę tablic jedno- , dwu- i trzywymiarowych. Niestety tablic o większej liczbie wymiarów nie da się przedstawić wizualnie, zresztą, jak przed chwilą zaznaczono, są one rzadko spotykane.

Tablice jednowymiarowe (rysunek a) to po prostu jeden wiersz danych, zawierający tyle pól, ile określono przy deklarowaniu tej tablicy.

Tablice dwuwymiarowe (rysunek b) wyglądają jak wycinek arkusza Excela - zawierają wiersze i kolumny, a w każdej komórce, znajdującej się na przecięciu poszczególnych kolumn i wierszy, przechowywana jest oddzielna wartość.

Tablice trójwymiarowe (rysunek c) można byłoby zobrazować jako sześciany, składające się z wielu mniejszych sześcianów. Każdy z tych mniejszych sześcianów przechowuje oddzielną wartość. Aby odnieść się do odpowiedniego miejsca w tej tablicy, należy podać 3 współrzędne.

W omawianym przykładzie obie tablice - przychody oraz koszty - powinny zawierać 12 wierszy i 1 kolumnę. Ich deklaracja wyglądałaby więc następująco:

 
 

Dim przychody(1 To 12, 1 To 1) As Double
Dim koszty(1 To 12, 1 To 1) As Double


Trzeba jednak zaznaczyć, że deklarowanie tablicy, w której jeden z wymiarów ma rozmiar 1 jest pozbawione sensu. Jeżeli spojrzysz jeszcze raz na przedstawione wcześniej schematy tablic o różnej liczbie wymiarów, zobaczysz, że zadeklarowanie dwuwymiarowej tablicy o rozmiarach 12x1 jest równoznaczne z zadeklarowaniem tablicy jednowymiarowej o rozmiarze 12. Zawsze w takiej sytuacji lepiej zdecydować się na mniejszą liczbę wymiarów. Pozwala to zaoszczędzić trochę pamięci, przyspieszając tym samym późniejsze wykonywanie programu. Ponadto cały kod będzie bardziej przejrzysty, a Ty oszczędzisz trochę czasu na zbędne pisanie, gdyż odwołując się potem do tej tablicy będziesz musiał określać tylko jeden jej wymiar, zamiast dwóch.

Każdy element tablicy jest jakby oddzielną zmienną posiadającą taki typ, jaki posiada cała tablica. Przykładowo, dwuwymiarowa tablica typu Double o dwunastu wierszach i dziesięciu kolumnach składa się ze 120 oddzielnych, niezależnych od siebie zmiennych typu Double.

Odwoływanie się do konkretnego elementu tablicy jest bardzo proste i wymaga podania nazwy tej tablicy oraz indeksów określających pozycję elementu w poszczególnych wymiarach.

Jeśli chciałbyś się więc przykładowo odwołać do wartości sprzedaży dla kwietnia z opisywanej wcześniej jednowymiarowej tablicy przychody, zapis wyglądałby następująco:

 

przychody(4)

Odwołanie do elementu, leżącego w trzecim wierszu i piątej kolumnie dwuwymiarowej tablicy tablica, wymagałoby natomiast takiego zapisu:

 

tablica(3, 5)

Dla większych wymiarów należy podać odpowiednio większą liczbę wymiarów.

Jak widzisz, odwoływanie do elementu tablicy wygląda niemal identycznie, jak odwoływanie do konkretnej komórki arkusza, które było już podczas tego kursu wielokrotnie stosowane.

Odwoływanie się do poszczególnych elementów tablicy jest niezbędne zarówno przy przypisywaniu wartości do tablicy, jak i przy odczytywaniu przechowywanych w niej danych.

Poniżej znajduje się procedura, przedstawiająca przykład przypisywania wartości do elementów tablicy oraz ich późniejszego odczytywania.

W powyższej procedurze najpierw deklarowane są dwie tablice - przychody oraz koszty. Każda z tych tablic zawiera tylko dane dla dwunastu miesięcy jednego roku, dlatego, jak już wcześniej wyjaśniono, najodpowiedniejszą dla nich formą jest jednowymiarowa tablica posiadająca 12 elementów.

Oprócz tablic w procedurze zadeklarowana jest zmienna i, która służy za iterator dla znajdującej się w tej procedurze pętli.

Dane wykorzystywane w tym przykładzie możesz znaleźć w załączonym pliku tablice.xls, w arkuszu przychody_i_koszty. W drugiej kolumnie tego arkusza, w wierszach 2-13 znajdują się dane na temat przychodów dla poszczególnych miesięcy, natomiast w trzeciej kolumnie, w tych samych wierszach, dane dotyczące kosztów.

Aby wczytać dane do tablicy należy wykorzystać pętlę, która przejdzie od pierwszego do dwunastego wiersza arkusza, dla każdego z tych wierszy pobierze wartość przychodu (z drugiej kolumny) oraz kosztu (z trzeciej kolumny) i przypisze te wartości do odpowiednich tablic.

Przy pierwszym wywołaniu pętli wartość iteratora i wynosi 1, więc kompilator odwołuje się do pierwszego elementu tablicy przychody - przychody(1) - i przypisuje do niego wartość określoną wyrażeniem Worksheets("przychody_i_koszty").Cells(i+1, 2), a więc wartość znajdującą się w drugim wierszu i drugiej kolumnie arkusza przychody_i_koszty (numer wiersza, z którego pobierana jest wartość dla danego miesiąca, jest w tym przykładzie większy o 1 od numeru tego miesiąca, ponieważ w pierwszym wierszu arkusza znajduje się nagłówek i wszystkie wartości są przesunięte o jeden wiersz w dół).

Chwilę potem taka sama operacja jest wykonywana dla kosztów - kompilator odwołuje się do pierwszego elementu tablicy koszty - koszty(1) - i przypisuje do niego wartość przechowywaną w komórce (2,3) arkusza przychody_i_koszty.

Następnie wykonywanie kodu dociera do wiersza zamknięcia pętli, wartość iteratora zwiększana jest o 1 (wynosi więc teraz 2) i ponownie wykonywane są operacje znajdujące się wewnątrz pętli - czyli pobranie wartości z arkusza i przypisanie ich do odpowiednich pól tablicy (tym razem są to wartości dla lutego, potem dla marca itd.).

Opisany zestaw operacji wykonywany jest 12 razy, czyli do momentu aż wartość iteratora przekroczy górne ograniczenie pętli.

W momencie, kiedy kompilator opuszcza pętlę, każde pole w obu tablicach ma już przypisaną odpowiednią wartość, pobraną z arkusza.

Teraz wykonywanie kodu natrafia na drugą pętlę, której zadaniem będzie odczytanie danych znajdujących się w obu tabelach i wyprintowanie ich w oknie Immediate.

Jako iterator ponownie została wykorzystana zmienna i, gdyż nie ma sensu wprowadzać dodatkowej zmiennej iteracyjnej, skoro obie występujące w tej procedurze pętle nie są ze sobą w żaden sposób powiązane. Ponownie też górnym i dolnym limitem tej pętli są liczby 1 i 12, ponieważ operacje mają być wykonane dla każdego z dwunastu pól obu tablic.

Krótko mówiąc, obie pętle są niemal identyczne i różnią się tylko poleceniami, znajdującymi się w ich wnętrzu.

Przy każdym powtórzeniu drugiej pętli wykonywane jest tylko jedno polecenie:

 

Debug.Print MonthName(i) & ": przychody - " & przychody(i) & ", koszty - " & koszty(i)

Jak pamiętasz z lekcji szóstej, polecenie Debug.Print służy do wyświetlania wartości w oknie Immediate podczas działania makra.

W tym przypadku w oknie Immediate wyświetlany będzie tekst, który łączy w sobie nazwę miesiąca (zwracaną przez funkcję MonthName), wartość przychodu dla tego miesiąca (uzyskaną za pomocą wyrażenia przychody(i)) oraz wartość kosztów poniesionych w tym miesiącu (koszty(i)). Wszystkie wymienione wartości dodatkowo połączone są ze sobą fragmentami tekstów, dzięku czemu opis wyświetlony w oknie Immediate będzie o wiele bardziej czytelny.

Przy odwoływaniu się do konkretnej komórki tablicy, na programistów czyhają dwa rodzaje błędów.

Pierwszy z nich to odniesienie się do niewłaściwej liczby wymiarów tablicy.

Załóżmy, że w przedstawionym przed momentem kodzie, zastąpiłbyś piąty wiersz następującym wyrażeniem:

 

przychody(i, 1) = Worksheets("przychody_i_koszty").Cells(i + 1, 2)

Po natrafieniu na powyższą linijkę kodu, kompilator próbowałby przypisać wartość do pola tablicy przychody, określonego za pomocą dwóch indeksów - i oraz 1 (odpowiedni fragment zaznaczono w ramce na czerwono). Zwróć jednak uwagę, że tablica przychody została zadeklarowana jako tablica jednowymiarowa, nie można więc odnosić się do niej podając indeksy dla dwóch wymiarów.

Kompilator przy próbie uruchomienia procedury, zawierającej taki zapis, zgłosi błąd Compile error: Wrong number of dimensions.

Błędem jest też oczywiście określenie mniejszej liczby indeksów, niż wynikałoby to z liczby wymiarów danej tablicy. Przykładowo, jeśli próbując odwołać się do konkretnego elementu tablicy trójwymiarowej, użyjesz zapisu określającego indeksy tylko dwóch wymiarów (np. tablica(1,1)), kompilator zgłosi identyczny błąd, ponieważ przy odwoływaniu do tablicy trójwymiarowej wymagane jest określenie indeksów dla wszystkich trzech jej wymiarów.

Drugim, często spotykanym błędem, występującym przy operacjach na tablicach, jest odwołanie przekraczające istniejący rozmiar tablicy.

Przykładem takiej sytuacji mogłaby być próba odniesienia się do pola przychody(13) w omawianym wcześniej przykładzie. Tablica przychody była zadeklarowana jako jednowymiarowa tablica posiadająca 12 pól zaindeksowanych od 1 do 12, dlatego próba odwołania się do pola z indeksem 13 spowoduje wygenerowanie błędu Subscript out of range.

W odróżnieniu od poprzedniego błędu, ten błąd nie przeszkadza jednak w normalnym uruchomieniu makra. Dopiero, gdy kompilator w toku działania makra natrafi na taki błąd, wstrzymuje dalsze wykonywanie kodu i wyświetla komunikat z odpowiednią informacją.

Tablice VBA mają tę zaletę, że definiując rozmiary poszczególnych wymiarów nie trzeba rozpoczynać ich od 1.

Załóżmy, że w opisywanym wcześniej przykładzie tablica przychody nadal musiałaby zawierać dane miesięczne, ale już nie z jednego roku, lecz z okresu 2000-2010 (a więc z 11 lat). Tablica musiałaby więc zostać zmodyfikowana do postaci tablicy dwuwymiarowej o wymiarach 12x11, a jej deklaracja wyglądałaby tak:

 

Dim przychody(1 To 12, 1 To 11) As Double

Zauważ jednak, że przy takiej postaci tablicy, odwołanie się do wartości dla określonego miesiąca i roku może czasem przysparzać kłopotów i wymagać chwili zastanowienia, a w związku z tym jest też podatne na błędy - w pośpiechu można byłoby na przykład pomyśleć, że wartość dla kwietnia 2004 roku zawarta jest w komórce (4, 4) tej tablicy, co oczywiście byłoby błędem, ponieważ komórka (4, 4) przechowuje wartość dla kwietnia 2003.

Problemu tego można uniknąć odpowiednio zmieniając indeksowanie tablicy.

Całkowicie poprawna i znacznie wygodniejsza w użyciu jest poniższa postać deklaracji tej tablicy:

 

Dim przychody(1 To 12, 2000 To 2010) As Double

Teraz tablica przychody również zawiera 12 wierszy i 11 kolumn, ale kolumny te są ponumerowane od 2000 do 2010. Odnalezienie komórki odpowiadającej kwietniowi 2004 nie sprawia już teraz żadnych trudności, gdyż od razu widać, że przechowuje ją komórka (4, 2004) tej tablicy.

Dopuszczalne jest też stosowanie w deklaracji rozmiarów tablic liczb ujemnych, w związku z czym poprawne byłyby poniższe przykłady deklaracji:

 
 
 

Dim tablica(-5 To 5, -10 To -1) As Double
Dim tablica(-7 To -1, 5 To 15) As String
Dim tablica(1 To 5, -5 To 0) As Variant

W poprzednim podrozdziale dowiedziałeś się, że przy deklarowaniu tablic w VBA możliwe jest nadanie im dowolnej numeracji wierszy i kolumn (oraz kolejnych wymiarów, jakkolwiek się one nazywają). Jednak mimo to, w zdecydowanej większości przypadków indeksowanie numerów kolumn i wierszy rozpoczyna się od 0 lub 1.

W związku z tym, twórcy języka VBA wyszli na przeciw programistom i wprowadzili domyślne indeksowanie od zera. Oznacza to, że jeśli przy deklarowaniu tablicy pominiesz dolny indeks, kompilator automatycznie potraktuje tę tablicę tak, jakby była zaindeksowana od zera.

Jeżeli więc zamierzasz przykładowo zadeklarować taką tablicę:

 

Dim tablica(0 To 5, 0 To 10) As Double

możesz pominąć przy każdym z wymiarów zapis [0 To] i zadeklarować tę tablicę w taki sposób:

 

Dim tablica(5, 10) As Double

Oba powyższe zapisy są ze sobą równoważne, ale drugi z nich jest zdecydowanie wygodniejszy w użyciu.

Dla wielu osób łatwiejsze jest jednak posługiwanie się tablicami indeksowanymi od 1. Programiści Microsoftu pomyśleli też i o nich. Jeżeli na samej górze modułu umieścisz wiersz z poleceniem Option Base 1, to wszystkie tablice zadeklarowane w tym module będą się domyślnie indeksowały od 1.

W takiej sytuacji zapis:

 

Dim tablica(5, 10) As Double

jest równoznaczny z zapisem:

 

Dim tablica(1 To 5, 1 To 10) As Double

Nie ma też żadnych przeszkód, żeby łączyć ze sobą klasyczny zapis rozmiaru tablic z jego skróconą postacią. Całkowicie prawidłowe byłyby więc przykładowo poniższe deklaracje tablic:

 
 
 
 

Dim tablica(5, 10, 5 To 10) As Double
Dim tablica(20, -5 To 5, 10) As Double
Dim tablica(10, 1 To 10) As Double
Dim tablica(0 To 10, 10) As Double

Jakiś czas temu omówiliśmy działanie okna Immediate - bardzo przydatnego narzędzia, służącego między innymi do sprawdzania tymczasowych wartości zmiennych i stałych.

Edytor VBA oferuje programistom jeszcze jedno narzędzie umożliwiające podpatrywanie wartości poszczególnych zmiennych w trakcie wykonywania kodu programu - okno Locals, którego szczegółowym omówieniem zajmiemy się w tym podrozdziale.

Korzystanie z okna Locals jest bardzo łatwe i intuicyjne. Po zapoznaniu się z poniższym przykładem z pewnością nie będziesz miał problemów ze zrozumieniem i korzystaniem z narzędzia Locals.

W przeciwieństwie do okna Immediate, które umożliwia sprawdzanie wartości zmiennych nawet bez zatrzymywania działania makra, jeżeli chcesz skorzystać z okna Locals musisz wstrzymać działanie makra ustawiając punkt zatrzymania.

Aby przetestować działanie tego narzędzia, ustaw punkty zatrzymania w wierszach 10. i 14. przytoczonego przed momentem kodu (tak jak na rysunku poniżej), a następnie uruchom makro.

Kiedy wykonywanie kodu dojdzie do pierwszego z punktów zatrzymania i działanie makra zostanie wstrzymane, uruchom okno Locals. W tym celu kliknij następujące opcje na pasku menu edytora VBA:

• View
• Locals Window

Na ekranie wyświetlone zostanie okno Locals, wyglądające tak, jak na poniższym rysunku:

W górnej części okna Locals (oznaczonej na powyższym rysunku na czerwono) znajduje się informacja na temat modułu oraz procedury (lub funkcji), w której aktualnie zatrzymał się kompilator wykonując kod. W naszym przypadku jest to moduł Module2 (na Twoim komputerze może nazywać się inaczej, jest to uzależnione od tego, w jakim module umieściłeś kod) oraz procedura oknoLocals.

W dolnej części okna Locals znajduje się wykaz wszystkich zmiennych znajdujących się w danej procedurze (lub funkcji). Przy każdej zmiennej znajduje się jej aktualna wartość oraz typ tej zmiennej.

I tak przykładowo, zmienna wyraz przechowuje aktualnie pusty ciąg znaków i jest zmienną typu String. Zmienna i oraz j przechowują aktualnie wartość 0 i są zmiennymi typu Integer.

Ostatnia występująca w kodzie zmienna - tablica - jest zmienną tablicową, przechowującą wartości typu Long. O tym, że zmienna tablica jest zmienną tablicową świadczą w oknie Locals dwie rzeczy:

• Znak plusa z lewej strony nazwy zmiennej tablica, umożliwiający rozwinięcie tablicy i podpatrywanie wartości dla poszczególnych pól tej tablicy.
  Kliknięcie znaku plusa rozwija i zwija listę pól tablicy, tak, jak pokazano na poniższym rysunku:
  

  

  zauważ, że przy zwykłych zmiennych (wyraz, i oraz j) z lewej strony nie ma znaku plusa.
• Po określeniu typu zmiennej (Long) znajduje się nawias, w którym określono liczbę wymiarów oraz rozmiar poszczególnych wymiarów - (1 To 5, 1 To 5).

Wciśnij teraz kilkakrotnie klawisz F5, aby wykonać kilka powtórzeń pętli, przypisującej do każdego kolejnego pola tablicy tablica iloczyn jego indeksów.

Po kilku powtórzeniach pętli ponownie sprawdź wartości w oknie Locals. Powinny one wyglądać mniej więcej tak, jak na poniższym zrzucie ekranowym (widok na Twoim komputerze może się trochę różnić, w zależności od tego, ile razy wcisnąłeś klawisz F5):

Okno Locals, podobnie jak Immediate, powinno być na stałe zakotwiczone w edytorze VBA i używane najczęściej, jak to tylko możliwe, ponieważ znacznie ułatwia programowanie, a w szczególności testowanie napisanego kodu.

Aby zakotwiczyć okno Locals z prawej strony edytora VBA, ustaw kursor myszy na niebieskim polu tytułu okna Locals (zaznaczonym na poniższym rysunku czerwoną ramką), następnie naciśnij lewy przycisk myszy, aby chwycić okno Locals, a potem przeciągnij je i upuść przy prawej krawędzi ekranu.