Pchełki VBA

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.

Tags
Show More

xpil

Po czterdziestce. Żonaty. Dzieciaty. Komputerowiec. Krwiodawca. Emigrant. Rusofil. Lemofil. Sarkastyczny. Uparty. Mól książkowy. Ateista. Apolityczny. Nie oglądam TV. Uwielbiam matematykę. Walę prosto z mostu. Gram na paru instrumentach. Lubię planszówki. Słucham bluesa, poezji śpiewanej i kapel a'capella. || Kliknij tutaj po więcej szczegółów ||

Dodaj komentarz

Bądź pierwszy!

avatar
  Subscribe  
Powiadom o