VBA-Lekcja 13.1. Korzystanie z tablic (Arrays)

Tablice (Arrays) są bardzo często wykorzystywane w programowaniu, m.in. w Excel VBA.

Tablica to zasadniczo pojedyncza zmienna z wieloma komórkami do przechowywania wartości, podczas gdy typowa zmienna ma tylko jedną komórkę pamięci, w której może przechowywać tylko jedną wartość.

Dostęp do tablicy można uzyskać jako całość, jeżeli chcemy odwoływać się do wszystkich zawartych w niej wartości, lub można odwoływać się do poszczególnych jej elementów.

Możesz zadeklarować tablicę do pracy ze zbiorem wartości tego samego typu danych. Omówiliśmy to już w VBA-Lekcja 6.2. Datatypes Ale zanurkujmy teraz jeszcze głębiej...

Dlaczego używane są tablice?

Wyobraź sobie, że próbujesz napisać procedurę, w której musisz zapisać do 500 wartości. Jeśli będziesz musiał utworzyć 500 oddzielnych zmiennych, będzie to bardzo trudne. W środku tablicy przechowywanie i praca z tymi wartościami będzie znacznie łatwiejsza.

Drugim powodem używania tablic jest ich szybkość. Odczyt danych z tablic zajmuje znacznie mniej czasu niż z tabel (złożonych z komórek) w arkuszu Excel.

Przykład użycia tablic

Oto przykład, który pokaże oczywistą zaletę używania tablic VBA w Excel.

Pierwszy arkusz („DS”) zawiera zbiór danych: 5000 wierszy na 3 kolumny:

Na drugim arkuszu znajdziesz tabelę podsumowującą, która uwzględnia wszystkie odpowiedzi „Tak” („YES”) według roku i klienta:

W tym przypadku procedura wykorzysta pętlę do przetworzenia zbioru danych i zapisania liczby odpowiedzi „Tak” dla każdego roku i każdego numeru klienta, a następnie wpisania tych danych do odpowiednich komórek.

Bez użycia tablic wykonanie tej procedury zajęłoby Excel 131,44 sekundy:

Jednak najpierw zapisanie danych w tablicy (z arkusza „DS”), a następnie wykonanie tych samych obliczeń (przy użyciu tablic zamiast zestawu danych arkusza „DS”) zajmie tylko 1,74 sekundy:

Jeśli zdecydujemy się zoptymalizować naszą procedurę, przechowując w tablicy tylko dane zawierające odpowiedzi „YES” (co stanowi około 3/4 danych), zajęłoby to tylko 1,02 sekundy:

To dobry przykład tego, jak użycie tablic pozwala na wykonanie procedury 128 razy szybciej. Wynik naszej optymalizacji byłby jeszcze lepszy, gdybyśmy pracowali z wieloma zbiorami danych jednocześnie.

Do szczegółów naszego przykładu powrócimy pod koniec lekcji.

Deklarowanie tablic

Poniżej kilka przykładów deklarowania tablic w VBA (jeśli pierwsze dwa przykłady nie są dla Ciebie jasne, przeczytaj it):

 'Przykład deklaracji tablicy jednowymiarowej
 Dim array1(4)

 'Przykład deklaracji tablicy dwuwymiarowej
 Dim array2(6, 1)

 'Przykład deklaracji tablicy dynamicznej
 Dim array3()

Jeśli przy deklarowaniu tablic nie można wprowadzić stałych wartości (ponieważ zależą one np. od wielkości zbioru danych), pozostaw nawiasy puste.

Nie musisz deklarować typu danych (string, long itp.), chociaż w wielu przypadkach spowolni to wykonanie procedury.

Przechowywanie danych w tablicy

Spróbujmy zapisać niektóre dane w tablicy:

Chcemy w tym przypadku przechowywać wartości 11x1, dlatego musimy stworzyć tablicę jednowymiarową:

 'Deklaracja
 Dim array_example(10)

Nie zapominaj, że numeracja elementów tablicy zaczyna się od 0 (jest to standard w programowaniu, dlatego warto od razu wyrobić sobie ten nawyk, choć tak naprawdę w VBA możesz zmienić to podejście).

Każdy element tablicy otrzyma teraz swoją wartość:

 'Zapisywanie wartości w tablicy
 array_example(0)  = Range("A2")
 array_example(1)  = Range("A3")
 array_example(2)  = Range("A4")
 array_example(3)  = Range("A5")
 array_example(4)  = Range("A6")
 array_example(5)  = Range("A7")
 array_example(6)  = Range("A8")
 array_example(7)  = Range("A9")
 array_example(8)  = Range("A10")
 array_example(9)  = Range("A11")
 array_example(10) = Range("A12")

Możesz operować na każdym elemencie tablicy lub go modyfikować tak, jakby był zwykłą zmienną.

Następny jest przykład, w którym używamy array_example(8):

Sub example()
     'Deklaracja
     Dim array_example(10)
     
     'Zapisywanie wartości w tablicy
     array_example(0)  = Range("A2")
     array_example(1)  = Range("A3")
     array_example(2)  = Range("A4")
     array_example(3)  = Range("A5")
     array_example(4)  = Range("A6")
     array_example(5)  = Range("A7")
     array_example(6)  = Range("A8")
     array_example(7)  = Range("A9")
     array_example(8)  = Range("A10")
     array_example(9)  = Range("A11")
     array_example(10) = Range("A12")
     
     'Próba 1
     MsgBox array_example(8) '=> zwroty: 04.02.2016
    
     'Zmiana jednej z wartości
     array_example(8) = Year(array_example(8))
     
     'Próba 2
     MsgBox array_example(8) '=> zwroty: 2016
End Sub

Pętla For byłaby lepszą opcją, aby szybciej zapisać tablicę:

 'Deklaracja
 Dim array_example(10)

 'Zapisywanie wartości w tablicy
 For i = 0 To 10
    array_example(i) = Range("A" & i + 2)
 Next