Jak stworzyć własną funkcję w Excelu?

MS Excel proponuje bardzo ciekawą możliwość tworzenia i wykorzystywania samodzielnie stworzonych funkcji. Wydawać by się mogło, że sam arkusz oferuje wystarczającą liczbę możliwości, które są w stanie spełnić oczekiwania użytkowników. Jednak niejednokrotnie zastosowanie tzw. własnej funkcji pozwala wygodniej posługiwać się Excelowymi formułami. Sprawdź, o co w nich chodzi i jak, krok po kroku, stworzyć własne funkcje w Excelu.

Czy znasz funkcję WYSZUKAJ.PIONOWO?

Jeżeli odpowiedź na powyższe pytanie brzmi „tak”, to mam kolejne: Czy też irytuje Cię to, że używając tej funkcji, nie możesz pobrać danych z lewej strony przeszukiwanej kolumny?

Moja rada: przestań się irytować. Napisz własne wyszukaj pionowo. Dzisiaj przygotowałem dla Ciebie, krótki i prosty temat, czyli pisanie własnych funkcji z wykorzystaniem języka VBA.

Jest to rodzaj makra, który działa jak wbudowane w Excela formuły, czyli tak jak znane Ci funkcje: SUMA, JEŻELI, czy chociażby wspomniane wcześniej WYSZUKAJ.PIONOWO.

Działają tak… Czyli dokładnie jak?

Co charakteryzuje funkcje użytkownika?

Cechy funkcji użytkownika:

 Używa się ich tak, jak funkcji wbudowanych, wprowadzając w komórki arkusza znak „=”, a następnie podaje nazwę (lub wybiera z listy podpowiedzi).
 Funkcje przeliczają się z każdą zmianą argumentów (danych wejściowych). Nie musisz ich uruchamiać tak jak makr.
 Funkcje dostępne są w każdym arkuszu, ale tylko w pliku, w którym zostały napisane.
 Pisze się je w modułach edytora VBA.
 Każda funkcja musi mieć przynajmniej jeden argument.

Jak stworzyć własną funkcję w Excelu?

Przygotuj pusty moduł w edytorze VBA i śledź poniższe kroki.

Pisanie funkcji rozpoczynamy od zastosowania słowa kluczowego Function. Następnie musisz zapisać nazwę swojej funkcji. Podobnie jak w zwyczajnych makrach, nazwa nie może zawierać spacji. Zamiast tego musisz użyć tzw. podłogi „_”. Nie sugeruj się też tym, że funkcje wbudowane mają w nazwie kropki. Funkcje użytkownika zostały pozbawione tego przywileju. Ja nazwałem moją funkcję NOWE_WYSZUKAJ_PIONOWO.
Function NOWE_WYSZUKAJ_PIONOWO() 

Własne WYSZUKAJ.PIONOWO – instrukcja krok po kroku

W nawiasie po nazwie funkcji musimy zadeklarować argumenty, czyli zestaw danych wejściowych, jakie musi podać użytkownik. To nic innego jak dane, które podajesz i oddzielasz średnikami wewnątrz nawiasów funkcji arkuszowych.

Aby zadeklarować argument, nie potrzebujesz słowa kluczowego Dim, tak jak w przypadku zmiennych. Wystarczy podać nazwę argumentu, a następnie po słowie kluczowym As podać typ danych. Zacznijmy od zadeklarowania trzech argumentów.
  Function NOWE_WYSZUKAJ_PIONOWO (Szukana_wartość As Variant, Szukaj_zakres As Range, Kolumna As Range) 

Często pewne argumenty funkcji arkuszowych zawarte są wewnątrz nawiasów kwadratowych, jak np.: ostatni argument funkcji wyszukaj pionowo – [przeszukiwany_zakres]. Taki zapis oznacza, że dany argument jest opcjonalny, czyli jeżeli go nie podasz, zostanie nadana mu wartość domyślna. Pisząc własną funkcję, również możesz skorzystać z argumentów opcjonalnych. Ich deklarację rozpoczynamy od słowa kluczowego „Optional”. Kolejne kroki to nadanie nazwy i typu danych. Aby zapisać wartość domyślną dla argumentu wystarczy, że użyjesz znaku „=” po typie danych i po prostu ją wprowadzisz.
 Function NOWE_WYSZUKAJ_PIONOWO(Szukana_wartość As Range, Szukaj_zakres As Range, kolumna As Range, Optional Format_danych As Byte = 0) 

W tej chwili nasza funkcja ma 4 argumenty, w tym jeden opcjonalny.

Dane, które będą przechowywane w poszczególnych argumentach:

 Szukana_wartość – będzie to dokładnie taki sam argument, jak w przypadku wyszukaj pionowo. Będziesz mógł tu podać komórkę z szukaną wartością lub zwyczajnie ją wpisać.
 Szukaj_zakres – tu będziesz podawał zakres, w którym znajduje się szukana wartość.
 Kolumna – komórka lub kolumna danych, w której znajduje się wartość, jaką chcesz pozyskać.
 Format_danych ­– argument opcjonalny. Umożliwi nam dodanie mechanizmu zmiany formatu danych zwracanych przez funkcję.

Mechanizm działania funkcji

Funkcja zacznie od znalezienia pozycji, według kryteriów podanych w argumentach. Poniższe dwie linie kodu stanowią cały mechanizm szukania.
 Dim pozycja As Range
Po pierwsze musisz zadeklarować dodatkową zmienną, w której przechowamy wynik wyszukiwania. Zmienna ma typ danych Range, więc pobierze cały zakres tak, abyś w mechanizmie zwracania wartości, mógł wykorzystać numer wiersza tej pozycji i połączyć go z numerem kolumny dla komórki podanej w argumencie kolumna. Taki zabieg, pozwoli nam wyszukiwać dowolne wartości zarówno po prawej, jak i lewej stronie zakresu wyjściowego. Pozwoli Ci też swobodniej przeciągać formuły, ponieważ używasz odwołania do komórki, a nie tak jak w przypadku wyszukaj pionowo indeksu kolumny. Nie ogranicza Cię zakres i kolejność. Najważniejszą zaletą takiego rozwiązania jest odporność na zmianę struktury tabeli. Indeksy kolumn w wyszukaj pionowo są przypisywane na sztywno i jeżeli do zakresu, w którym działa taka funkcja, dodasz kolumnę, przestanie ona działać lub zwróci błędne wyniki. Oczywiście zamiast przypisywać indeks ręcznie, mógłbyś użyć pomocniczych wierszy z numerami i użyć odwołania do komórek. Nie zawsze jest to możliwe, ponieważ mógłbyś w ten sposób popsuć strukturę tabeli, pogorszyć jej wizualne aspekty lub utrudnić wprowadzanie danych. W pliku, który dziś dla Ciebie przygotowałem, pokazałem wariant z wierszami pomocniczymi. Wracając do naszej najważniejszej zalety, eliminuje ona konieczność używania wszystkich półśrodków, ponieważ bazujemy na indeksie kolumny, do której odwołania użyłeś.

 Set pozycja = Szukaj_zakres.Find(What:=Szukana_wartość, MatchCase:=True) 

Zauważ, że nie używamy żadnych pętli. Nie wertujemy wszystkich komórek w arkuszu, przez co nasza funkcja nie będzie mocno obciążała komputera. Oczywiście, jeżeli użyjesz jej w 10 arkuszach na 10 000 pozycji, Twoja stacja robocza będzie musiała zaangażować więcej zasobów do pracy i odczujesz spowolnioną pracę arkusza.

Co do samej składni przed nazwą zadeklarowanej zmiennej używamy słowa kluczowego Set, ponieważ będą tam przechowywane dane typu Range. Mam nadzieję, że pamiętasz, dlaczego przed tym typem zmiennej musimy stosować słowo Set. Jeżeli nie, możesz odświeżyć tę wiedzę w pierwszym artykule poświęconym programowaniu w VBA.

Następnie używamy metody Find dostępnej dla zmiennych typu Range. Tej metody możemy użyć tylko na zdefiniowanym zakresie. Wywołujemy ją dostawiając kropkę na końcu nazwy zmiennej lub tak jak w naszym przypadku argumentu funkcji – Szukaj_zakres.Find. W kolejnym kroku musimy podać parametry metody. Zauważ, że po wpisaniu pierwszego nawiasu (o ile wszystkie poprzednie kroki wykonałeś poprawnie), pojawi się lista, parametrów, jakie przyjmuje instrukcja Find.

Opcjonalna konwersja na inne typy danych 

Użyjemy teraz instrukcji, która w zależności od wybrane opcji, zwróci w wyniku sformatowane dane. Będzie się ona opierać na naszym opcjonalnym argumencie, więc cała funkcjonalność również będzie opcjonalna.

Jak możesz wnioskować z powyższego fragmentu kodu, mamy 5 wariantów danych, które nasza funkcja może zwrócić:

0 – opcja domyślna. Format ogólny,
1 – liczba całkowita,
2 – liczba zmiennoprzecinkowa podwójnej precyzji,
3 – tekst,
4 – Data w formacie – 01.01.1900.

Co do samego wyniku, spójrz proszę na wariant podstawowy:

  NOWE_WYSZUKAJ_PIONOWO = Cells(pozycja.Row, Kolumna.Column).Value 

Instrukcję cells miałeś już okazję poznać, więc mam nadzieję, że wiesz, jak działa. Ta prosta linijka kodu to klucz do eliminacji wad wyszukaj pionowo. Odwołujemy się zawsze do wiersza znalezionej pozycji i do numeru kolumny podanej w argumentach komórki.

Tak jak makra kończyły się słowem klucz End Sub, tak i funkcje należy zakończyć zasadniczym zwrotem End Function.

Elementy instrukcji Application.MacroOptions:

 Macro – nazwa makra – przechowywana jako tekst;
 Description – opis makra, który wyświetli się po wejściu w okno edycji funkcji;
 Category – kategoria, do której przypisana zostanie dana funkcja:

To wszystko, co musisz wiedzieć o pisaniu własnych funkcji w Excelu. W przygotowanym na dzisiaj pliku znajdziesz jeszcze trzy przykładowe funkcje, które mam nadzieje zainspirują Cię do jeszcze mądrzejszej pracy. Jak zwykle opisałem dla Ciebie kod wewnątrz edytora VBA.

Zobacz pozostałe artykuły dotyczące opracowania narzędzi Excel: