Jedną z najczęściej używanych przeze mnie funkcji VB, które sam napisałem, jest vUnikaty.
Kod Funkcji vUnikaty
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
Działanie Funkcji
Jako argument pobiera ona zakres komórek arkusza (może być wielokolumnowy) i w wyniku zwraca tablicę unikatowych wpisów (odrzuca jedynie błędy i puste pola).
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 badany wpis.
Jeżeli wpis nie jest błędem lub pustym polem i jednocześnie nie ma go w słowniku – dodaje go tam. W wyniku funkcja zwraca tablicę, będącą tak naprawdę jest 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 – tak jakby mogło się wydawać. 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ć potem 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….
Rozwiązanie Problemu
Jak sobie z tym radzić?
Według mnie pozostanie przy Ubound wciąż jest najlepszym wyjściem, natomiast trzeba zawsze sprawdzać czy tablica zaczyna się od 0 czy też od 1.
W pierwszym przypadku zawsze należy dodać cyfrę 1 do tego, co zwraca Ubound, w drugim nie trzeba niczego dodawać.
Z pewnością 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), jednak jest to o tyle ryzykowne, że w tablicy często posiadamy puste elementy, które nie będą zliczane.
