Pchełki VBA – odcinek 9: szukajcie, a znajdziecie

Dzisiaj pokażę jak za pomocą VBA przeszukać zakres komórek pod kątem konkretnego tekstu.

Zadanie: napisać funkcję, która na podstawie zadanego zakresu komórek oraz szukanego tekstu, znajdzie ten tekst oraz zwróci opisową informację czego szukano oraz w której komórce to znaleziono. Jeżeli nie uda się znaleźć szukanego tekstu, funkcja ma zwrócić informację o nieudanych poszukiwaniach, czego szukano i w jakim zakresie.

Najpierw rozwiązanie:

Excel, Alt-F11, nowy moduł, wklejamy:

Option Explicit

Public Function szukaj(gdzie As Excel.Range, czego As String) As String
    Dim ret As Excel.Range
    Set ret = gdzie.Find(what:=czego)
    If ret Is Nothing Then
        szukaj = "Nie znaleziono '" & czego & "' w komór" & Switch(gdzie.Count = 1, "ce", True, "kach") & "[" & gdzie.Address & "]"
    Else
        szukaj = "Szukano '" & czego & "', znaleziono '" & ret.Value & "' w komórce [" & ret.Address & "]"
    End If
End Function

Teraz sprawdźmy, czy to działa. Przełączamy się do Excela, wpisujemy w komórkę A1 tekst "Ala ma kota.", w komórkę A2 tekst "Ala nie ma kota!", w komórkę A3 tekst "Po co Ali kot?"

Następnie, w komórkę B1 wpisujemy:

=szukaj(A1, "ala")

i kopiujemy w dół do komórek B2 i B3.

W komórkę B5 wpisujemy:

=szukaj(A1:A3, "ali")

W komórkę B6 wpisujemy:

=szukaj(A1:A3, "nie")

W komórkę B7 wpisujemy:

=szukaj(A1:A3, ".")

W komórkę B8 wpisujemy:

=szukaj(A1:A3, "pies")

Jak widzimy, funkcja działa jak należy. Teraz rzut okiem na szczegóły:

Public Function szukaj(gdzie As Excel.Range, czego As String) As String

Funkcja nazywa się szukaj, parametr gdzie to przeszukiwana komórka (lub zakres komórek), parametr czego to szukany tekst. Zwracany jest tekst - opisówka co i gdzie znaleziono, bądź czego nie znaleziono.

    Dim ret As Excel.Range
    Set ret = gdzie.Find(what:=czego)

Deklarujemy zmienną ret (od angielskiego "return" - "zwracać") typu Range, a następnie, za pomocą metody Find przeszukujemy zakres gdzie pod kątem tekstu czego.

Metoda Find obiektu typu Range jest o wiele bardziej rozbudowana niżby to wynikało z powyższego przykładu. Można za jej pomocą kontrolować wiele parametrów wyszukiwania, na przykład czy ma znajdować wyłącznie całe zawartości komórek, czy również ich fragmenty (np. jeżeli w komórce jest "Ala" a my szukamy tylko "a"), od której komórki ma rozpocząć wyszukiwanie, czy ma szukać w treści formuł czy tylko w ich wynikach, czy ma szukać wierszami czy kolumnami, czy ma zwracać uwagę na wielkość liter i tak dalej. W naszym przypadku pozostawiamy wszystkie wartości domyślne i używamy wyłącznie parametru what.

Przy okazji, cóż to za dziwna składnia? what:=czego

Otóż dla funkcji (a także dla procedur) można zdefiniować parametry opcjonalne, czyli takie, których podawanie jest nieobowiązkowe. I jeżeli chcemy potem wywołać taką funkcję (lub procedurę) z kilkoma parametrami opcjonalnymi, ale chcemy użyć tylko jednego z tych parametrów, możemy użyć takiej właśnie składni: nazwa:=wartość. Gdybyśmy tego nie zrobili, trzeba by było zamiast tego powstawiać dużo przecinków, w efekcie czego wywołanie funkcji byłoby o wiele mniej czytelne.

    If ret Is Nothing Then
        szukaj = ...
    Else
        szukaj = ...
    End If

Metoda Find zwraca komórkę, w której znaleziono szukany tekst. Jeżeli tekstu nie znaleziono, zwracany jest specjalny, pusty obiekt Nothing. Tutaj sprawdzamy, czy to nastąpiło, i jeżeli tak, generujemy komunikat o niepowodzeniu wyszukiwania:

        szukaj = "Nie znaleziono '" & czego & "' w komór" & Switch(gdzie.Count = 1, "ce", True, "kach") & "[" & gdzie.Address & "]"

Tutaj stosujemy kilka tricków w jednej linii kodu. Warto się przyjrzeć bliżej:

"Nie znaleziono '"

Nasz tekst będzie się zaczynał od słów "Nie znaleziono" oraz apostrofu otwierającego cytowanie szukanej wartości.

& czego

Operator & omawiałem wcześniej - służy on do konkatenacji stringów, czyli po naszemu łączenia łańcuchów tekstowych. Tutaj dołączamy szukaną wartość.

& "' w komór"

Zamykamy apostrof i dodajemy na końcu "w komór". Dalej będzie albo "ce" albo "kach", w zależności od tego, czy szukaliśmy w jednej komórce czy w kilku:

& Switch(gdzie.Count = 1, "ce", True, "kach")

Funkcję Switch omawiałem wcześniej. Pierwszy warunek to gdzie.Count = 1.

Metoda Count obiektu typu Range zwraca liczbę komórek w zakresie. Jeżeli jest jedna komórka, dołączamy końcówkę "ce", w przeciwnym przypadku "kach".

& "[" & gdzie.Address & "]"

Wreszcie na końcu dołączamy, w nawiasach kwadratowych, adres przeszukiwanego zakresu komórek.

W przeciwnym wypadku (czyli jeżeli udało się znaleźć szukany tekst), zwracamy informację, że się udało, i w której komórce:

szukaj = "Szukano '" & czego & "', znaleziono '" & ret.Value & "' w komórce [" & ret.Address & "]"

Analizę powyższej linii pozostawiam już Czytelnikowi.

3 komentarze

  1. a jakby tak miał znaleźć, zaznaczyć na kolor i pokazać tylko wiersze , w których znalazł a resztę ukryć to dałby rade ?

    1. Dałoby, ale czy to aby na pewno dobry pomysł? Efekt będzie taki, że najpierw pokolorujemy wiersze, w których było trafienie, a potem ukryjemy wszystkie pozostałe. Czyli widoczne pozostaną tylko wiersze pokolorowane. Moim zdaniem warto albo tylko pokolorować, albo tylko ukryć, ale nie jedno i drugie.

      1. ooo. dzięki bo stary ten wpis był bardzo .
        wydaje się to co ja chcę troche dziwne , ale dokładnie tego potrzebuję
        Mam np xls 10 kolumn i 500 wierszy . W któjejś np kolumnie nr 3 ( różnie dla rożnych szukanych) danych) kolumnie numerek np ‘1234’ , i w innej kolumnie np nr
        9 w innym wierszu numerek ‘abc1234’ ( niestety poprzedzony literami) . I recznie robię tak : ctr F – znajdź 1234 , ctrl A zaznacz wszystko – klik w kolorek i tadam ! zaznaczam wszystkie wiersze w których nie mam ! kolorku i okiem porównuję dane z wierszy z kolorkiem z pewnych kolumn rożnych dla rożnych danych .
        Czyli najpierw potrzebuję msgBox – wpisz szukany numerek i jak znajdzeisz ( w całym arkuszu – to zaznacz- to jeszcze jakoś zrobię . ale o ukrywaniu – nie mam pojecia i jaka pętla by była do tego najlepsza
        ( z grubego sznura , wiem 😉

Leave a Comment

Twój adres e-mail nie zostanie opublikowany.