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

• Zmiana wielkości tablicy
• Tworzenie tablicy za pomocą funkcji Array
• Efektywne pobieranie danych z arkusza i wklejanie tablic do arkusza
• Funkcje wbudowane dotyczące tablic

Przy tworzeniu aplikacji bardzo często zdarza się, że potrzebna jest tabela, która będzie zmieniała swój rozmiar w trakcie wykonywania programu. Właściwie można byłoby zaryzykować stwierdzenie, że takie dynamiczne tabele są w poważnych aplikacjach wykorzystywane nawet częściej niż ich statyczne odpowiedniki.

Przypomnij sobie przykład omówiony w lekcji dwunastej, gdzie użytkownik wpisywał nazwy w polu InputBox, które następnie były printowane do arkusza. Załóżmy teraz, że procedura ta ma zostać zmodyfikowana w ten sposób, aby wpisywane przez użytkownika wyrazy, zamiast wyświetlać się w arkuszu, były dodawane do tablicy. Oczywiście przed rozpoczęciem procedury ostateczny rozmiar tej tablicy jest nieznany, ponieważ nigdy nie wiadomo, ile wyrazów wpisze użytkownik, zanim zdecyduje się zamknąć okno InputBox.

Ktoś mógłby w tym miejscu pomyśleć, że wystarczy w takiej sytuacji zadeklarować odpowiednio dużą tablicę (np. tablica(1000)), tak aby mieć pewność, że zmieszczą się w niej wszystkie elementy wpisywane przez użytkownika. Nie jest to jednak zbyt rozsądny pomysł.

Po pierwsze, takiej pewności nigdy mieć nie można. Nawet przy zadeklarowaniu tablicy zawierającej 1000 elementów, nie można zagwarantować, że użytkownik nie będzie chciał wpisać w oknie InputBox na przykład 1001 nazw.

Po drugie, w sytuacji kiedy użytkownik wpisałby przykładowo tylko 2 nazwy, pozostałe 998 pól tej tablicy nie byłoby wykorzystywane i zupełnie niepotrzebnie marnowałyby pamięć komputera.

Na szczęście język VBA posiada mechanizm pozwalający zmieniać rozmiar tablicy w trakcie działania makra. Poniżej znajduje się kod wspomnianej funkcji, wykorzystujący tablice dynamiczne. Pod ramką z kodem znajduje się jego analiza wraz ze szczegółowym omówieniem schematu zmiany rozmiaru tablicy w trakcie wykonywania kodu.

W pierwszym wierszu kodu użyto omówionego już podczas tej lekcji polecenia Option Base 1, które określa, że wszystkie tablice mają być domyślnie numerowane od 1, a nie od 0.

W makrze zostały zadeklarowane cztery zmienne. W trzech pierwszych nie ma nic nadzwyczajnego:

• do zmiennej tekstowej nazwa będzie przypisywany tekst, wprowadzany przez użytkownika w oknie InputBox,
• zmienna liczbowa i będzie służyła jako licznik, zliczający liczbę nazw wpisanych przez użytkownika,
• zmienna liczbowa j zostanie wykorzystana w ostatniej części makra jako iterator w pętli printującej wszystkie wpisane przez użytkownika wartości w arkuszu.

Nowością jest natomiast sposób deklaracji zmiennej tablicowej nazwy. Dotychczas, deklarując tablicę, podawaliśmy jej wymiary już w wierszu deklaracji. W tym przypadku jednak, zamiast wymiarów tablicy, za nazwą zmiennej znajduje się tylko pusty nawias:

 

Dim nazwy() As String

Taka deklaracja zmiennej tablicowej, bez podania jej rozmiarów, ani nawet liczby wymiarów, jest stosowana zawsze wtedy, kiedy tablica w trakcie działania makra ma zmieniać swoje rozmiary lub liczbę wymiarów.

Ponadto określamy w tym wierszu, że tablica nazwy będzie przechowywała zmienne typu tekstowego.

W wierszach 9-18 znajduje się nieskończona pętla Do ... Loop, której jedynym warunkiem zakończenia jest podanie przez użytkownika w oknie InputBox pustego tekstu.

Przy każdym powtórzeniu tej pętli użytkownik wpisuje w oknie InputBox jakąś nazwę, która jest potem przypisywana do zmiennej nazwa (wiersz 10).

Następnie sprawdzana jest długość tej nazwy (wiersz 11).

Jeżeli zmienna nazwa jest pusta (a więc użytkownik wcisnął w oknie InputBox przycisk Anuluj lub zatwierdził pusty tekst), wykonywane jest polecenie Exit Do, czyli opuszczenie pętli (wiersz 12).

Jeżeli natomiast użytkownik wprowadził jakiś tekst w oknie InputBox (czyli długość zmiennej nazwa jest większa od zera), wykonywany jest blok operacji znajdujący się w wierszach 14-16.

Najpierw zwiększana jest o 1 wartość licznika i. Zmienna i posiada w każdym momencie taką wartość, ile nazw zostało aktualnie podanych przez użytkownika. Dzięki temu łatwo jest potem nadać tablicy nazwy odpowiedni rozmiar, czyli dokładnie taki, ile zmiennych ma ona przechowywać. W momencie uruchomienia makra zmienna i ma oczywiście wartość 0, ponieważ użytkownik nie zdążył jeszcze wprowadzić żadnej nazwy, ale przy każdym kolejnym wprowadzeniu nazwy jest zwiększana o 1.

W kolejnym wierszu:

15

ReDim Preserve nazwy(i)

dokonywana jest zmiana rozmiaru tablicy. Słowem kluczowym służącym do rozszerzania lub zwężania tablicy jest ReDim.

Słowo Preserve informuje natomiast kompilator, że przy zmianie rozmiarów tablicy mają zostać zapamiętane wszystkie wartości, które dotychczas przechowywała ta tablica (zastosowanie słowa kluczowego Preserve omówimy nieco dokładniej w dalszej części tego podrozdziału).

Po słowach kluczowych ReDim Preserve należy podać nazwę tablicy, której rozmiar ma być zmieniony oraz określić w nawiasie okrągłym nowy rozmiar tej tablicy.

Załóżmy, że po uruchomieniu makra, użytkownik chce w pojawiającym się na ekranie oknie InputBox wprowadzać nazwy państw.

Kiedy wykonywanie kodu dojdzie do wiersza 9, uruchamiana jest opisana wcześniej pętla, która przy każdym swoim powtórzeniu wyświetla okno InputBox, a następnie rozszerza tablicę i dodaje do niej wpisaną właśnie nazwę.

W wierszu 10 użytkownik wpisuje pierwszą nazwę państwa, np. Polska. W kolejnym wierszu kompilator sprawdza czy wpisana nazwa nie jest pustym ciągiem znaków i po stwierdzeniu, że nie przenosi wykonywanie kodu do bloku Else tej instrukcji warunkowej, a więc do wiersza 14. Tutaj pierwszą czynnością jest zwiększenie wartości zmiennej licznikowej i, tak aby przechowywała aktualną liczbę wpisanych nazw. Dotychczas licznik wynosił 0, teraz po wpisaniu pierwszej nazwy, jego wartość zostaje ustawiona na 1.

W kolejnym wierszu (15) aktualizowany jest rozmiar tablicy nazwy.

Jak na razie w żadnym miejscu kodu nie ustalano jeszcze liczby wymiarów, ani rozmiarów dla tablicy nazwy. Została jedynie zadeklarowana jej obecność w kodzie wraz z informacją, że będzie to tablica dynamicznie zmieniająca swój rozmiar. Do takiej tablicy niemożliwe byłoby przypisanie żadnej wartości, gdyż kompilator nie wydzielił w niej jeszcze pól (a jak pamiętasz z poprzedniej lekcji, wartości przypisywane są do poszczególnych pól tablicy, a nie do całej tablicy). Zmienna nazwy stanie się pełnoprawną tablicą dopiero w momencie jej inicjalizacji, czyli przy ustaleniu jej liczby wymiarów i określeniu jej początkowego rozmiaru.

W omawianym przez nas przykładzie, tablica nazwy zostaje zainicjowana jako tablica jednowymiarowa o rozmiarze 1, ponieważ polecenie

15

ReDim Preserve nazwy(i)

zostaje po raz pierwszy wywołane w wierszu 15, po tym jak użytkownik wpisał w oknie InputBox pierwszą nazwę, a zmienna licznikowa i przybrała tym samym wartość 1.

Kiedy wykonywanie kodu dociera do wiersza 16, tablica nazwy jest już więc tablicą jednowymiarową o rozmiarze 1. Teraz, za pomocą polecenia
w jej ostatnim polu zostaje umieszczona wartość, którą przed chwilą użytkownik wpisał w polu InputBox (przechowywana tymczasowo w zmiennej nazwa; przypisanie tekstu wpisanego przez użytkownika do tej zmiennej odbywa się w wierszu 10).

Napisałem "w jej ostatnim polu", ponieważ nazwy(i), czyli pole tablicy nazwy o indeksie równym zmiennej licznikowej i, zawsze będzie ostatnim polem tej tablicy. Wynika to z tego, że tuż przed tą operacją, w wierszu 15, tablica nazwy jest zawsze rozszerzana właśnie do wielkości równej zmiennej licznikowej i. Dla przypomnienia, zmienna i w każdym momencie działania makra jest równa liczbie nazw wpisanych przez użytkownika, dzięki temu tablica nazwy za każdym razem jest rozszerzana tylko i wyłącznie w takim stopniu, aby mogła przechowywać wszystkie wpisane nazwy.

Na razie użytkownik wpisał jedną nazwę (np. Polska), tablica nazwy jest więc jednowymiarową tablicą o rozmiarze 1 i wpisie Polska w jej jedynym polu.

Kompilator natrafia teraz na wiersz 18, będący zamknięciem pętli Do ... Loop i odsyła wykonywanie kodu z powrotem do wiersza 9, na początek tej pętli.

Na ekranie ponownie pojawia się okno InputBox, w którym użytkownik musi wpisać nazwę (załóżmy, że wpisał Czechy). Wpisana przez niego nazwa jest najpierw przypisywana do zmiennej tekstowej nazwa, następnie o 1 zwiększana jest wartość zmiennej licznikowej i (wiersz 14), tak aby uwzględniała wpisaną przed chwilą nazwę.

W kolejnych dwóch wierszach kodu tablica nazwy(i) ponownie zostaje rozszerzona, tym razem staje się tablicą jednowymiarową o rozmiarze 2. W pierwszym polu, dzięki zastosowaniu słowa kluczowego Preserve, nadal przechowywany jest tekst Polska, do drugiego pola - nazwy(2) - zostaje natomiast przypisany tekst Czechy, czyli tekst wpisany przez użytkownika przy drugim wyświetleniu okna InputBox.

Cały proces jest powtarzany tak długo, aż użytkownik kliknie w oknie InputBox przycisk Anuluj/Cancel lub zatwierdzi pusty tekst. W takiej sytuacji kompilator wychodzi z pętli Do ... Loop, znajdującej się w wierszach 9-18, i przechodzi do pętli For ... Next w wierszach 20-22. Ta pętla jest już Tobie doskonale znana i nie wymaga szczegółowego omówienia. Wystarczy napisać, że jest ona odpowiedzialna za wyświetlenie w arkuszu wszystkich wpisów przechowywanych w tablicy nazwy(i).

Szczegółowego omówienia wymaga jeszcze zastosowanie słowa kluczowego Preserve, które pojawiło się już w poprzednim przykładzie.

Jak już wcześniej wspomniałem, użycie słowa Preserve jest dla kompilatora sygnałem, że przy każdej zmianie rozmiarów tablicy, wszystkie wartości dotychczas przechowywane w tej tablicy mają być w niej zachowane i znajdować się w niej również po jej przeskalowaniu. Niesie to ze sobą mniej lub bardziej istotne konsekwencje, które omówimy w dalszej części tego podrozdziału.

Aby dokładniej wyjaśnić różnicę wynikającą z zastosowania lub pominięcia słowa kluczowego Preserve, wróćmy do omówionego przed chwilą makra, którego działanie polegało na pobieraniu nazw od użytkownika i zapisywaniu ich w tablicy zmiennych tekstowych.

Ustaw punkt zatrzymania w wierszu zamknięcia pętli Do ... Loop (18), a następnie uruchom makro.

Zdarzają się sytuacje, że w kodzie trzeba użyć tablicy, której elementy powinny być przypisane ręcznie, a nie wczytywane z arkusza, bazy danych czy jeszcze jakiegoś innego źródła.

Za przykład może posłużyć tablica zawierająca nazwy miesięcy: miesięcy jest 12, ich nazwy są zawsze takie same i nie zanosi się na to, aby coś w tej kwestii uległo zmianie. Dlatego nie ma przeszkód, aby elementy do tej tablicy przypisać ręcznie, bezpośrednio w kodzie VBA:

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


Dim miesiace(12) As String

miesiace(1) = "styczeń"
miesiace(2) = "luty"
miesiace(3) = "marzec"
miesiace(4) = "kwiecień"
miesiace(5) = "maj"
miesiace(6) = "czerwiec"
miesiace(7) = "lipiec"
miesiace(8) = "sierpień"
miesiace(9) = "wrzesień"
miesiace(10)= "październik"
miesiace(11)= "listopad"
miesiace(12)= "grudzień"


Mógłbyś sobie w tym miejscu pomyśleć: Dobrze, ale przecież wcześniej było powiedziane, że zamiast pisać tyle linijek ręcznie, lepiej zastosować pętlę, która wczyta te nazwy z arkusza. Faktycznie, na pewno kod byłby w takiej sytuacji krótszy, ale nazwy miesięcy do arkusza też trzeba byłoby wpisać ręcznie, więc takie rozwiązanie raczej nie oszczędziłoby zbyt dużo pisania.

Oczywiście nic nie stoi na przeszkodzie, żeby wypisać nazwy miesięcy w arkuszu i wczytać je za pomocą pętli (tak, jak to zostało pokazane w podrozdziale dotyczącym przypisywania wartości do tablic). Nie jest to jednak optymalne rozwiązanie, gdyż niepotrzebnie angażuje w tę procedurę arkusz Excela, a trzeba zaznaczyć, że odwoływanie się do arkusza jest dla kompilatora wykonującego kod bardzo pracochłonnym i pamięciożernym zadaniem.

Jedyną wadą rozwiązania przedstawionego w powyższej ramce z kodem jest jego rozwlekłość - cała deklaracja zajmuje aż 12 linijek.

W poprzedniej lekcji, omawiającej podstawy tablic VBA, dowiedziałeś się jak wczytywać do tablicy VBA dane z arkusza Excela. Chociaż przedstawiony tam sposób, polegający na zastosowaniu pętli For ... Next, dobrze sprawdza się na niewielkich zakresach danych (tak jak 12 wierszy i 2 kolumny w przytoczonym przykładzie), to jednak ze względu na swoją czasochłonność nie nadaje się raczej do pobierania większych zakresów danych.

W pliku tablice2.xls, który powinieneś pobrać na swój dysk na potrzeby tej lekcji, znajduje się zakres danych składający się z 10000 wieszy i 15 kolumn. Przedstawiona poniżej procedura dokonuje wczytania tych danych do tablicy VBA za pomocą pętli For ... Next, tak jak było to praktykowane w poprzedniej lekcji, co, jak już wspomniałem, jest metodą bardzo nieefektywną.

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


Option Explicit
Option Base 1
Sub pobieranieDanychZArkusza()
    Dim dane(10000, 15) As Variant
    Dim i As Long
    Dim j As Integer

    For i = 1 To 10000
        For j = 1 To 15
            dane(i, j) = Cells(i, j)
        Next j
    Next i
End Sub

W procedurze zadeklarowane zostały 3 zmienne: zmienna tablicowa dane i zmienne iteracyjne i oraz j, które posłużą do przejścia pętlą For ... Next przez wszystkie wiersze i kolumny arkusza.

Zmienna tablicowa została zadeklarowana jako typ Variant, ponieważ w różnych kolumnach arkusza mogą się znajdować różne typy danych (teksty, liczby, daty), co uniemożliwia jednoznaczne określenie typu. Ponadto została ona określona jako tablica dwuwymiarowa o rozmiarach 10000x15, ponieważ tyle właśnie wierszy i kolumn danych znajduje się w arkuszu.

Oddzielną kwestią pozostaje fakt, że liczba wierszy i kolumn została w tym przykładzie sztywno określona na 10000 i 15, co nie jest oczywiście zbyt dobrym rozwiązaniem. Optymalnym wyjściem byłoby, gdyby makro samo rozpoznawało ile wierszy i kolumn danych znajduje się w arkuszu, tak aby procedura była jak najbardziej uniwersalna i mogła być stosowana we wszystkich sytuacjach, niezależnie od ilości danych zawartych w arkuszu. Oczywiście napisanie takiej procedury, która sama określa ilość wierszy i kolumn danych w arkuszu, jest jak najbardziej możliwe i nauczymy się jak to zrobić w lekcji szczegółowo omawiającej operacje na zakresach komórek. Na razie skupmy się jednak na zoptymalizowaniu wczytywania danych z arkusza do tablicy.

Przed uruchomieniem procedury ustaw jeszcze punkt zatrzymania w wierszu jej zamknięcia (End Sub ), aby po wykonaniu wczytywania danych sprawdzić zawartość tablicy dane.
Jeżeli uruchomisz teraz procedurę wczytywanieDanych, to po dotarciu wykonywania kodu do punktu zatrzymania tablica dane zawiera dokładnie takie same wartości, jakie znajdują się w arkuszu. Problem polega jednak na tym, że cała operacja zajmuje sporo czasu, a przecież arkusz może zawierać znacznie więcej danych.

Na szczęście język VBA oferuje jeszcze inny, nieporównywalnie szybszy, sposób masowego pobierania danych z arkuszy Excela. Polega on na zadeklarowaniu zmiennej typu Variant i przypisaniu do niej zakresu komórek arkusza. Operacjami na arkuszach, komórkach i zakresach zajmiemy się szczegółowo w dalszej części kursu, na razie nauczysz się tylko jak określić pojedynczy zakres, tak aby móc go wczytać do tablicy VBA.

Skopiuj i wklej do edytora VBA następujący kod:

1
2
3
4
5


Option Explicit
Sub efektywneWczytywanieDanych()
    Dim dane As Variant
    dane = Range(Cells(1, 1), Cells(10000, 15))
End Sub

Pomimo swojej znacznej przewagi w szybkości działania, cały kod jesst zaskakująco łatwy do zrozumienia i o wiele krótszy niż metoda wykorzystująca pętle, co jest tylko jego dodatkowym atutem.

Jak już wcześniej wspomniałem, w procedurze należy zadeklarować zmienną typu Variant. Zwróć jednak uwagę, że nie ma być to zmienna tablicowa typu Variant, jak miało to miejsce przy pierwszym sposobie, lecz zwykła zmienna typu Variant, przeznaczona do przechowywania pojedynczej wartości nieznanego typu.

W czwartym wierszu procedury, do zmiennej dane przypisywany jest zakres komórek arkusza, zawierający interesujące nas dane.

Zakresy komórek określane są w VBA poprzez następujący zapis:

 

Range(komórka_początkowa, komórka_końcowa)

w którym komórka_początkowa to komórka znajdująca się w lewym górnym rogu zakresu, a komórka_końcowa w jego prawym dolnym rogu.

Aby poinformować kompilator o współrzędnych komórki początkowej i końcowej, należy skorzystać z zapisu znanego już z poprzednich lekcji:

 

Cells(wiersz, kolumna)

Ostateczny zapis zakresu obejmującego wiersze od 1 do 10000 oraz kolumny od 1 do 15 będzie przedstawiał się następująco:

 

Range(Cells(1, 1), Cells(10000, 15))

Ustaw teraz punkt zatrzymania w ostatnim wierszu procedury, a następnie uruchom makro. Kiedy wykonanie kodu dotrze do końca procedury i działanie makra zostanie wstrzymane, możesz podejrzeć w oknie Locals wartości przypisane do poszczególnych pól tablicy dane i przekonać się, że niczym nie różnią się one od wartości przypisanych wcześniej przy zastosowaniu pętli.

Różnica w czasie wykonania obu procedur jest wyraźna, chociaż na dosyć małym zakresie danych, jakim jest 150000 komórek (10000 wierszy x 15 kolumn), nie jest ona zauważalna gołym okiem. Podczas gdy przy wykorzystaniu pętli wczytanie wartości do tablicy VBA zajęło 1.7 sekundy, to stosując metodę przypisania zakresu komórek do zmiennej typu Variant cały proces trwał tylko 0.1 sekundy.
Na większym zakresie danych (10800000 komórek - 60000 wierszy x 180 kolumn), efektywne wczytanie danych dzięki przypisaniu zakresu zajęło tylko 7 sekund, natomiast sposób wykorzystujący pętlę zajął aż 130 sekund, co raczej dyskwalifikuje stosowanie tej metody z uwagi na zbyt dużą czasochłonność.

Podczas omawiania typów zmiennych, zwróciłem uwagę na to, że zmienne typu Variant są najbardziej pamięciożerne itylko w nielicznych sytuacjach ich użycie jest uzasadnione. Omówione powyżej przykład jest właśnie jedną z takich wyjątkowych sytuacji, gdzie skorzystanie z uniwersalnego typu zmiennej Variant przynosi o wiele więcej korzyści niż szkód. Zresztą nawet przy stosowaniu nieefektywnej metody wczytywania danych (z użyciem pętli For ... Next), tablica musiałaby być zadeklarowana jako tablica przechowująca zmienne typu Variant, z uwagi na nieznany typ danych wczytywanych do tej tablicy z arkusza.

Podobnie rzecz ma się z zapisem wartości z tablicy do arkusza. W tym przypadku różnica w efektywności również jest wyraźnie widoczna.

Jednym ze sposobów na wyprintowanie tablicy VBA w arkuszu Excela jest zastosowanie dwóch pętli For ... Next, które przechodzą kolejno przez wszystkie wiersze i kolumny tablicy VBA i zapisują poszczególne wartości do arkusza. Taki sposób jest oczywiście zupełnie prawidłowy i doskonale sprawdziłby się na niewielkich zakresach danych. Jednak w przypadku większych tablic, czas wypisywania danych do komórek Excela znacznie się wydłuża, sprawiając, że korzystanie z tej metody staje się mało opłacalne.

Tak samo, jak w przypadku pobierania danych z arkusza, z pomocą idą operacje na zakresach komórek. W poniższej ramce z kodem znajduje się przykład obrazujący wykorzystanie zakresów komórek do efektywnego zapisu wartości tablicy VBA do arkusza Excela.

Funkcje wbudowane dotyczące tablic

Wśród funkcji wbudowanych języka VBA znaleźć można trzy funkcje, które zwracają właściwości tablic:

• IsArray - sprawdza czy zmienna podana jako argument jest tablicą,
• LBound, UBound - zwraca najniższy lub najwyższy indeks dla podanego wymiaru tablicy.

Przytoczone funkcje zostały pominięte w lekcji poświęconej funkcjom wbudowanym, ponieważ nie byłeś wtedy jeszcze zaznajomiony z tablicami. Teraz, kiedy deklarowanie i korzystanie z tablic nie jest już Ci obce, nic nie stoi na przeszkodzie, aby omówić te bardzo przydatne funkcje.

Pierwszą z omawianych funkcji jest funkcja IsArray, sprawdzająca czy podany argument jest tablicą.

Składnia tej funkcji wygląda następująco:

 

Function IsArray(value As Variant) As Boolean

Jako argument Value może zostać podana wartość dowolnego typu.

Funkcja zwróci błąd, jeżeli przy jej wywołaniu zostanie pominięty argument Value.

Jak nietrudno się domyślić, funkcja zwraca wartość True, jeżeli argument jest tablicą, oraz wartość False, jeżeli argument nie jest tablicą.
Funkcja IsArray zwraca wartość True także dla zmiennych typu Variant, które w trakcie działania makra stały się tablicami. Dotychczas poznałeś dwie sytuacje, kiedy zmienna Variant może stać się tablicą - przypisanie do niej zakresu z arkusza lub wyniku funkcji Array.

Poniżej znajdziesz przykład obrazujący wykorzystanie funkcji IsArray:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16


Sub testTablic()
    Dim arr() As String
    Dim i As Integer

    If IsArray(arr) Then
        Debug.Print "Zmienna arr jest tablicą"
    Else
        Debug.Print "Zmienna arr nie jest tablicą"
    End If

    If IsArray(i) Then
        Debug.Print "Zmienna i jest tablicą"
    Else
        Debug.Print "Zmienna i nie jest tablicą"
    End If
End Sub

W makrze zadeklarowane zostały dwie zmienne, która zostaną następnie sprawdzone funkcją IsArray. Pierwsza z nich to zmienna arr, zadeklarowana jako tablica dynamiczna, czyli tablica, której rozmiar i liczba wymiarów może zmieniać się w trakcie działania makra. Drugą zmienną jest natomiast zmienna i, będąca zwyczajną zmienną typu Integer.

W kolejnych linijkach kodu (wiersze 5-9) znajduje się instrukcja warunkowa, sprawdzająca czy zmienna arr jest traktowana przez omawianą funkcję IsArray jako tablica. Po uruchomieniu makra instrukcja warunkowa wypisze w oknie Immediate napis Zmienna arr jest tablicą lub Zmienna arr nie jest tablicą, w zależności od wyniku funkcji IsArray. W dalszej części procedury znajduje się identyczna instrukcja warunkowa przeznaczona dla zmiennej liczbowej i.

Po uruchomieniu procedury zauważysz w oknie Immediate, że w przypadku zmiennej tablicowej arr funkcja IsArray() zwróciła wartość True, natomiast dla zmiennej liczbowej i zwrócona została wartość False.

Na wynik zwracany przez funkcję IsArray trzeba zwracać szczególną uwagę, jeżeli jako argument podawana jest zmienna, do której przypisano wartości pobrane bezpośrednio z arkusza. W podrozdziale poświęconym efektywnemu pobieraniu wartości z arkusza dowiedziałeś się, że po przypisaniu do zmiennej typu Variant zakresu komórek z arkusza, zmienna ta staje się dwuwymiarową tablicą o rozmiarach odpowiadających rozmiarom tego zakresu i wartościach odpowiadających wartościom poszczególnych komórek Excela, znajdujących się w tym zakresie. Od tej zasady istnieje jednak wyjątek, o którym opowiem poniżej.

Kiedy do zmiennej typu Variant przypisywany jest zakres składający się tylko z jednej komórki, można byłoby się spodziewać, że zmienna ta stanie się dwuwymiarową tablicą o rozmiarach 1x1. W rzeczywistości jednak, zmienna ta przybiera po prostu pojedynczą wartość, dokładnie taką, jaka znajduje się w jedynej komórce wskazanego zakresu. Aby dokładnie zrozumieć tę kwestię przeanalizuj poniższy przykład:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


Sub przypisywanieZakresowDoZmiennej()
    Dim pojedynczaKomorka As Variant
    Dim wieleKomorek As Variant

    'Przypisanie jednej komórki
    pojedynczaKomorka = Range(Cells(1, 1), Cells(1, 1))

    'Przypisanie wielu komórek
    wieleKomorek = Range(Cells(1, 1), Cells(2, 2))

    If IsArray(pojedynczaKomorka) Then
        Debug.Print "Zmienna pojedycznaKomorka jest tablicą"
    Else
        Debug.Print "Zmienna pojedycznaKomorka nie jest tablicą"
    End If

    If IsArray(wieleKomorek) Then
        Debug.Print "Zmienna wieleKomorek jest tablicą"
    Else
        Debug.Print "Zmienna wieleKomorek nie jest tablicą"
    End If

End Sub

W przedstawionej ramce z kodem zadeklarowane zostały dwie zmienne typu Variant: pojedynczaKomorka, do której przypisany zostanie zakres jednokomórkowy, oraz wieleKomorek, do której przypiszemy większy zakres.

W kolejnych wierszach kodu znajdują się dwie instrukcje warunkowe, które sprawdzają kolejno każdą z tych dwóch zmiennych i wyświetlają w oknie Immediate odpowiedni komunikat - nazwę rozpatrywanej zmiennej oraz tekst jest tablicą lub nie jest tablicą, w zależności od wyniku funkcji IsArray.

Po uruchomieniu procedury, w oknie Immediate pojawi się następujący wpis:

Potwierdza to, że zmienne Variant, do których przypisano jednokomórkowy zakres, nie są przez kompilator traktowane jako tablice.

Istnieje jeszcze jeden łatwy sposób, abyś przekonał się o istnieniu powyższej zasady, wykorzystujący w tym celu okno Locals.

W przedstawionym wyżej kodzie ustaw punkt zatrzymania w ostatniej linijce procedury, a następnie uruchom makro.

W momencie, gdy wykonanie kodu zostane wstrzymane, przejdź do okna Locals. Jak widać na poniższym rysunku (i na Twoim monitorze), zmienna pojedynczaKomorka jest traktowana jako zwykła wartość (nie istnieje nawet możliwość jej rozwinięcia, tak jak ma to miejsce w przypadku tablic), zmienna wieleKomorek jest natomiast przedstawiona jako tablica, którą można rozwinąć, aby podejrzeć wartości jej poszczególnych pól.

Ten z pozoru błahy i nieistotny problem może czasem spowodować poważne błędy w działaniu aplikacji, o czym przekonasz się w jednej z kolejnych lekcji.

LBound i UBound to bardzo przydatne funkcje, umożliwiające sprawdzenie granicy poszczególnych wymiarów tablicy.

Składnia tych funkcji przedstawia się następująco:

 
 

Function LBound(array As Variant, Optional dimension As Byte) As Integer
Function UBound(array As Variant, Optional dimension As Byte) As Integer

Jako argument array należy podać tablicę, której rozmiary mają byc sprawdzone.

Zmienna dimension reprezentuje wymiar, którego rozmiar ma być sprawdzony. Jest to zmienna opcjonalna, która przy wywoływaniu funkcji może zostać pominięta - w takim przypadku domyślnie sprawdzany jest rozmiar dla pierwszego wymiaru tablicy.

Funkcja LBound zwraca dolną granicę poszczególnych wymiarów tablicy, natomiast funkcja UBound górną granicę.

Przykładowo dla tablicy zadeklarowanej w następujący sposób:

 

Dim arr(-5To 5, 1 To 10) As Variant

funkcja LBound(arr, 1) zwróci wartość -5 (ponieważ dolna granica pierwszego wymiaru tej tablicy wynosi -5), natomiast funkcja UBound(arr, 2) zwróci wartość 10 (gdyż górna granica drugiego wymiaru wynosi 10). Więcej przykładów działania funkcji LBound i UBound znajdziesz w dalszej części tego podrozdziału.

Funkcje LBound i UBound są bardzo wrażliwe, jeżeli chodzi o poprawność przekazywanych do nich argumentów.

Aby wywołanie tych funkcji nie spowodowało błędu, jako argument array musi zostać podana zmienna tablicowa, czyli taka, dla której omówiona niedawno funkcja IsArray zwraca wartość True.
Jeżeli wywołując którąś z tych funkcji, jako argument array podasz zmienną nie będącą tablicą, może dojść do jednej z dwóch opisanych poniżej sytuacji:

• jeżeli zmienna, którą przekazałeś do funkcji LBound lub UBound była zadeklarowana jako zmienna typu podstawowego innego niż Variant (np. String lub Byte) lub obiekt (o obiektach omówimy szczegółowo w drugiej części kursu), kompilator nie zezwoli nawet na uruchomienie makra i wyświetli na ekranie błąd Compile error: Expected array. Dzieje się tak, ponieważ dla kompilatora już przed uruchomieniem makra jest oczywiste, że zmienna ta nie będzie tablicą i spowoduje błąd w programie, dlatego próbuje zapobiec temu jeszcze przed rozpoczęciem działania makra.
• jeżeli zmienna, przekazana do funkcji LBound lub UBound, jest zmienną typu Variant, kompilator przed uruchomieniem makra nie może wiedzieć czy zmienna ta nie będzie tablicą. Jak dowiedziałeś się wcześniej podczas tej lekcji, istnieje kilka sytuacji, kiedy zmienna typu Variant może stać się tablicą - można przykładowo przypisać do niej zakres z arkusza lub wynik funkcji Array. W związku z tym, kompilator normalnie rozpoczyna wykonywanie makra i jeśli w momencie wywołania funkcji okazuje się, że przekazywana do niej zmienna nie jest tablicą, działanie makra zostaje wstrzymane, a na ekranie wyświetlany jest błąd Run time error '13': Type mismatch.

Pewne zastrzeżenia dotyczą także drugiego z argumentów tych funkcji - dimension. Wartość tego argumentu musi być dodatnią liczbą naturalną i to taką, która nie przekracza liczby wymiarów rozpatrywanej tablicy array. Nie można więc przykładowo wywołać funkcji UBound(arr, 3), jeśli tablica arr została zadeklarowana jako tablica dwuwymiarowa. W takiej sytuacji wygenerowany zostanie błąd: Run time error '9': Subscript out of range.

Poniżej znajduje się kilka przykładów działania funkcji LBound oraz UBound dla dwuwymiarowej tablicy zadeklarowanej z następującymi rozmiarami: arr(-5 To 5, 1 To 10).