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.
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.