Pewnym problemem VBA i innych języków programowania jest to, że odliczanie często rozpoczynamy od 0, a nie od 1. W przypadku tablic, pozycją pierwszego elementu (z ang. LBound) domyślnie jest właśnie zero. Niesie to ze sobą wiele zagrożeń, o których musimy pamiętać.
Od duplikatów do unikatów
Jedną z najczęściej używanych przeze mnie funkcji VBA jest vUnikaty. Napisałem ją po to, aby z wybranego zakresu komórek wyciągnąć unikatową listę wpisów. Funkcja odrzuca błędy i puste pola.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Public Function vUnikaty(ByRef rngObszar As Range) As Variant '// Funkcja jako argument pobiera zakres komórek arkusza. '// Zwraca w wyniku jednowymiarową tablicę unikatów. Dim objSlownik As Object 'Dictionary Dim avTablica As Variant Dim r As Long, c As Long Dim vElement As Variant 'Pobierz listę unikatów do tablicy 1 If rngObszar.Count = 1 Then 2 vUnikaty = rngObszar(1) 3 Exit Function 4 Else 5 avTablica = rngObszar 6 End If 'Utwórz obiekt słownika 7 Set objSlownik = CreateObject("Scripting.Dictionary") 'Przejedź w pętli po wszystkich elementach tablicy 8 For r = LBound(avTablica, 1) To UBound(avTablica, 1) 9 For c = LBound(avTablica, 2) To UBound(avTablica, 2) 'Pobierz unikatowy wpis do zmiennej 10 vElement = avTablica(r, c) 'Gdy wpis nie jest pustą komórką lub błędem 'to dodaj go do słownika. 11 If Not IsError(vElement) Then 12 If Len(vElement) <> 0 Then 13 If Not objSlownik.Exists(vElement) Then 14 objSlownik.Add Key:=vElement, Item:=vElement 15 End If '>>> If Not objSlownik.Exists(vElement) Then 16 End If '>>> If Len(vElement) <> 0 Then 17 End If '>>> If Not IsError(vElement) Then 18 Next c '>>> For c = LBound(avTablica, 2) To UBound(avTablica, 2) 19 Next r '>>> For r = LBound(avTablica, 1) To UBound(avTablica, 1) 'Wynikiem działania funkcji jest tablica unikatowych wpisów 20 vUnikaty = objSlownik.Items 21 Set objSlownik = Nothing End Function |
Słownik – mózg całej operacji
W funkcji tej całą robotę wykonuje obiekt Scripting.Dictionary, czyli popularny słownik, o którym kiedyś już szczegółowo pisałem w kontekście jego przewag nad kolekcją.
Funkcja najpierw przerzuca dane z zakresu do wirtualnej tablicy (aby całość działała znacznie szybciej), a następnie sprawdza każdy wpis.
Jeżeli wpis nie jest błędem lub pustym polem i jednocześnie nie ma go w słowniku – dodajemy go tam. W wyniku funkcja zwraca tablicę, będącą tak naprawdę właściwością Items słownika.
Odliczanie od zera
W tej funkcji kryje się jednak mały zonk, o którym trzeba pamiętać. Otóż pierwszy element tablicy posiada numer 0, a nie 1. Jest to sytuacja taka sama jak w przypadku np. pola listy na formularzu – tam również odliczanie zaczyna się od 0, a nie od 1.
Dlaczego ta sytuacja jest dość niebezpieczna?
Otóż załóżmy, że mamy w tej naszej tablicy 11 nazwisk nauczycieli. Całą listę nauczycieli chcemy wgrać do komórek arkusza, aby wygenerować plan lekcji dla każdego z nich.
Teraz najważniejsze. Jeżeli pierwszy nauczyciel posiada indeks 0, to jedenasty nauczyciel posiada indeks 10. I ta liczba 10 jest górną granicą tablicy (UBound), mimo, że w środku zawiera ona 11 elementów.
Zazwyczaj przerzucając dane z tablicy do komórek arkusza kierujemy się właściwością Resize i właśnie parametrem Ubound. W takiej sytuacji zgralibyśmy jednak do arkusza dziesięciu nauczycieli, jeden zostałby pominięty…. i nie dostałby raportu….
Dodanie jedynki
Jak sobie z tym radzić?
Według mnie pozostanie przy Ubound wciąż jest najlepszym wyjściem, natomiast trzeba zawsze wiedzieć czy LBound przybiera wartość 0 czy 1.
W pierwszym przypadku zawsze należy dodać 1 do tego, co zwraca Ubound, w drugim nie trzeba niczego dodawać.
Może też kusić sprawdzenie ilości elementów w tablicy… Można to zrobić np. poprzez odwołanie do funkcji arkuszowej ILE.NIEPUSTYCH (w VBA WorksheetFunction.CountA). Jest to jednak o tyle ryzykowne, że w tablicy często posiadamy puste elementy, które nie będą zliczane.
