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

by Marcin

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.

Filtrowanie ListBox’a

Filtrowanie ListBox’a 2

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

{ 6 comments… read them below or add one }

Maciek

Dlaczego po zastosowaniu tego kodu wyskakuje mi blad: „Run Time Error „70”: Permission Denied” ?? Mam kontrolke listboxa w UserForm, we wlasciwosciach tej kontrolki mam w RowSource, dynamicznie tworzona liste z arkusza. Czy to moze byc przyczyna? Jak to zmienic?

Maciek

Poprawilem i wszystko dziala! świetny kod 🙂

Marcin

>Dlaczego po zastosowaniu tego kodu wyskakuje mi blad: “Run Time Error “70”

Nie potrafię niestety odpowiedzieć na to pytanie bez zobaczenia kodu, który generuje błąd. Za dużo zgadywania, a ja jeżeli tylko mam wybór pomiędzy siedzeniem i zgadywaniem, a leżeniem i myśleniem o niebieskich migdałach – zawsze wybieram to drugie 🙂

Alex

czy możesz przesłać mi arkusz ( albo udostępnić link do pliku) z takim działającym przykładowym formularzem ( na na przykład 2000wierszy
z funkcją przefiltrowania listy znajdującej się na formularzu i wyświetlenia w oknie o wysokości całej strony (lub dynamicznie zmieniającym się) tylko tych elementów, które pasują do wpisywanego w polu tekstowym wzorca. Wpisuje się literkę „a” i na liście pokazyją się tylko elementy zawierające tę literę na początku elementu. Kontynuujesz wpisywanie „ab” i lista zawęża się odpowiednio.

pozdrowienia
Abc777@poczta.fm

Maciej

Witam,
prosze o odpowiedz gdzie popelniam blad, program sie wysypuje

Dim lista_nazwisk As Variant
Dim lista_nazwisk2 As Variant
Dim i As Integer

i = Cells(Rows.Count, 2).End(xlUp).Row
With Worksheets(4) ‚ nazwiska znajduja; sie; w skoroszycie „Nazwiska”
‚ pobieram do tablicy liste; nazwisk z arkusza.
lista_nazwisk = Range(Cells(i, 2), Cells(i, 2)).Value
‚potrzebujemy liste; jednowymiarowa;
lista_nazwisk2 = Application.Transpose(lista_nazwisk)

‚ filtruje; jednowymiarowa liste;
lista_nazwisk2 = Filter(lista_nazwisk2, Me.TextBox2.Value, True, vbBinaryCompare)

‚ wstawiam przefiltrowana; liste; do Listboxa
Me.ListBox1.List = lista_nazwisk2

Keso

Miałem okazję przeczytać ten artykuł przed kilkoma laty. Wówczas, „raczkując” w VBA nie korzystałem z UserForms. Teraz sytuacja się zmieniła, wciąż „raczkuję” ale nieco mniej, w niektórych kwestiach stawiam już całkiem pewne kroki. W niektórych, bo np. wciąż tajemnicą jest dla mnie fragment Twojego kodu:
‚potrzebujemy listę jednowymiarową
lista_nazwisk2 = Application.Transpose(lista_nazwisk)
Mam wrażenie, że wcześniejszy fragment:
‚ pobieram do tablicy listę nazwisk z arkusza.
lista_nazwisk = Range(Range(„C4”), Range(„C4”).End(xlDown)).Value
tworzy listę jednowymiarową – dlaczego musimy ją transponować? Jest jakiś istotny powód? (oprócz tego by procedura działała – bez transpozycji nie działa). Czy wypełnienie listy ListBox’a wymaga transpozycji pionowego zakresu komórek?
Nie wiem, czy zaglądasz czasem do swoich historycznych tekstów, mam nadzieję, że owszem.
Pozdrawiam
Keso

Leave a Comment

Previous post:

Next post: