Filtrowanie ListBox’a część 1- funkcja „filter”

Bardzo wygodna (z punktu widzenia użytkownika) wydaje się być możliwość przefiltrowania listy znajdującej się na formularzu (zwłaszcza jeżeli lista jest długa) i wyświetlenia tylko tych elementów, które pasują do wpisywanego w polu tekstowym wzorca. Rozwiązanie znane każdemu. Wpisuje literkę „a” i na liście mam tylko elementy zawierające tę literę. Kontynuujesz wpisywanie „ab” i lista zawęża się odpowiednio.

Całe zadanie sprowadza się do obsługi zdarzenia „Change” pola tekstowego zawierającego wprowadzany tekst. Odpowiedni kod, w procedurze obslugi zdarzenia, powinien odpowiednio przefiltrować listę i wyświetlić tylko te elementy, które pasują do wprowadzanego wzorca.

Bardzo wygodną metodą przefiltrowania listy (tablicy) jest skorzystanie z funkcji FILTER.
Funkcja zwraca tablicę zawierającą wszystkie elementy innej tablicy, które pasują do podanego wzorca. Składnia jest następująca:

Filter(sourcesrray, match[, include[, compare]])

Sourcearray – tablica (KONIECZNIE JEDNOWYMIAROWA – inaczej otrzymasz błąd), którą chcemy przefiltrować.

Match – ciąg tekstowy, którego poszukujemy. Niestety nie ma możliwości użycia znaków „wildcards” („?”,”*”). Lub inaczej – możesz ich użyć, ale nie będą one traktowane w żaden „specjalny” sposób. Poszukując ciągu „s*” wbrew oczekiwaniom nie otrzymasz wszystkich słów zaczynających się na „s”. Funkcja będzie szukała dwuliterowego ciągu „s*”.

Dodatkowo możesz użyć dwóch dodatkowych parametrów.

Include (przyjmuje wartość TRUE lub FALSE). Jeżeli TRUE funkcja zwróci wszystkie te rekordy, które zawierają tekst podany w parametrze match. Jeżeli ustawisz wartość na FALSE otrzymasz te rekordy, które nie zawierają tekstu.

Compare pozwala Ci ustawić sposób porównania tekstów. Jeżeli przyjmuje wartość vbTextCompare wielkość liter nie będzie brana pod uwagę, dla wartości vbBinaryCompare wielkość liter będzie miała znaczenie.

Przykład użycia funkcji – obsługa zdarzenia on_change.

Private Sub TextBox1_Change()
Dim lista_nazwisk As Variant
Dim lista_nazwisk2 As Variant

With Worksheets("Nazwiska") ' nazwiska znajdują się w skoroszycie "Nazwiska"
   ' pobieram do tablicy listę nazwisk z arkusza.
   lista_nazwisk = Range(Range("C4"), Range("C4").End(xlDown)).Value

   'potrzebujemy listę jednowymiarową
   lista_nazwisk2 = Application.Transpose(lista_nazwisk)

   ' filtruję jednowymiarowa listę
   lista_nazwisk2 = Filter(lista_nazwisk2, Me.TextBox1.Value, True, vbTextCompare)

   ' wstawiam przefiltrowaną listę do Listboxa
   Me.ListBox1.List = lista_nazwisk2
End With
End Sub

Funkcja jest (lub przynajmniej wydaje się być) BARDZO SZYBKA. W przypadku tablicy ponad 6 tys nazwisk czas filtrowania listy jest na moim komputerze w zasadzie niezauważalny (Excel 2003, 512 MB Ram, procesor celeron mobile 1500 MHz).

Funkcja ma jednak przynajmniej dwa poważne minusy.

- Operuje tylko na jednowymiarowej tablicy. Kiedy więc chcesz przefiltrować ListBox zawierający kilka kolumn, jej wykorzystanie w pierwotnej postaci raczej odpada.

- W parametrze match nie działają znaki wildcards. W praktyce oznacza to tyle, że jeżeli w swoim textbox’ie wpiszesz literę „c” funkcja zwróć Ci wszystkie nazwiska zawierające tę literę (na początku, na końcu, w środku wyrazu). Z reguły oczekujemy rozwiązania, w którym wyświetlane byłyby tylko nazwiska rozpoczynające się literą „c”.

Drugiemu problemowi można częściowo zapobiec ustawiając wartość parametru compare na vbBinaryCompare. Twój filtr stanie się wtedy wrażliwy na wielkość liter. W moim przypadku wszystkie nazwiska na liście zaczynają się z wielkiej litery. Jeżeli więc wpiszę w pole wyszukiwania „M” otrzymam tylko wyrazy rozpoczynające się literą M (jako, że wielkie litery nie występują u mnie w środku wyrazu). Rozwiązanie połowiczne, ale …. w niektórych przypadkach może wystarczyć.

Jeżeli nie wystarczy … można spróbować podejść do tematu nieco inaczej.

ciąg dalszy nastąpi ….

Tagged as: filter, filtrowanie, formularze, kontrolka, kontrolki, ListBox, UserForm, VBA