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

https://xpil.eu/WYeTU

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.

https://xpil.eu/WYeTU