Jak napisać własną funkcję w Excelu (UDF)?

Z lekcji dowiesz się:

• Jak napisać własną funkcję, której nie ma w Excelu?

• Co to jest ciało funkcji? Jak je zrobić?

• Jako: księgowa, kadrowa, analityk – możesz opracować potrzebną Ci funkcję!

Na przykładzie zawodu brokera można pokazać, że chciałby on z pewnością dysponować następującą funkcją liczącą:

((wartość kontraktu + stawka brokera) * pozycja transakcji) / wartość kontraktu

Wystarczy podać trzy dane: wartość kontraktu, stawkę brokera, pozycję transakcji i otrzymujemy wynik. Bez konieczności posiadania wiedzy o tych wszystkich zależnościach i kolejności działań.

Dlatego żeby życie było prostsze, pokażemy dzisiaj jak stworzyć własną funkcję.

Na początek należy sprawdzić w Excelu czy jest w nim zakładka Deweloper (rys. 1).

Jeżeli nie, wystarczy że wejdziesz w opcję programu Excel -> Dostosuj wstążkę -> po prawej stronie zaznaczysz ptaszkiem pozycję Developer.

Nowa zakładka od razu pojawi się w programie bez konieczności resetowania i będziesz mógł z niej od razu korzystać.

Nowa zakładka jest już na miejscu, więc teraz przejdź do niej i wybierz Visual Basic.

(Rys. 1)

Zobaczysz nowe okno, nie będę się w tym filmie skupiał na wszystkich aspektach tego okna, tylko przejdę do meritum.

W części Project - VBAProject znajduje się otwarty arkusz.

Jeżeli masz więcej arkuszy, tutaj także pojawi się więcej wpisów.

Klikasz prawym klawiszem myszy na wybrany arkusz wybierasz opcję Insert, a następnie Module (rys. 2).

(Rys. 2)

Możesz zauważyć, że pojawił się nowy element nazwany Modules oraz Module1.

Natomiast z prawej strony pojawił się biały obszar roboczy, a w tytule okna na samym końcu pojawiła się nazwa [Module1 (Code)].

Teraz na tym białym polu będziesz wpisywał to, co będzie wykonywała Twoja funkcja.

Funkcja ma stałe elementy: nagłówek funkcji, stopka funkcji oraz ciało funkcji.

Nagłówek to informacja, że zaczynam pisać funkcję, stopka to informacja że kończę pisać funkcję. Ciało to wszystkie elementy, które będzie wykonywać funkcja, aby zwrócić jakąś wartość.

Zatem zaczynamy pisanie!

Każda funkcja zaczyna się od nagłówka Function nazwa funkcji, ja napiszę SuperFunkcja, otwieram i zamykam nawias - potem wyjaśnię dlaczego tak. Na koniec piszę jaki typ danych będzie zwracać funkcja as Integer, czyli liczbę.

Naciskasz enter i zauważ, że zapis trochę się zmienił, została automatycznie dopisana stopka funkcji, a nazwa Function ma pierwszą wielką literę.

Dzieje się, ponieważ Excel w tym miejscu próbuje nam pomóc, aby wszystko wyglądało i było napisane tak jak należy. Jest to także rozwiązanie dla trochę leniwych, którzy nie chcą powtarzać tych samych czynności.

Dwie części funkcji masz już za sobą, teraz kolej na najważniejszą, czyli to co funkcja będzie wykonywać.

Na początek coś prostego: funkcja będzie zwracać liczbę 5.

Robi się to w prosty sposób, wpisuje się nazwę funkcji równa się 5 (rys. 3).

(Rys. 3)

Gratuluję, napisałeś samodzielnie swoją pierwszą funkcję w Excelu!

Prawda, że to nie takie trudne?

Teraz sprawdź czy funkcja działa, w dowolnej komórce wpisz równa się SuperFunkcja, otwórz i zamknij nawias (rys. 4).

(Rys. 4)

Enter i voile'a widać, że funkcja zwraca wpisaną liczbę 5.

Jeżeli teraz zmienisz liczbę np. na 7 zmiana nastąpi przy kolejnym użyciu funkcji lub po przeliczeniu wartości - innymi słowy zmiany nie następują automatycznie.

Dobrze, ale funkcja która zwraca liczbę 5 jest mało użyteczna, więc spróbuj zmienić tę funkcję tak, aby po podaniu jej liczby dodać 5.

W tym celu po pierwsze musisz dopisać parametr wejściowy, czyli coś co podane przez użytkownika będzie potem zmienione przez funkcję.

Parametry wpisujesz w nagłówku pomiędzy nawiasami, więc dopisz liczba as Integer.

Ok, liczba wejdzie do funkcji, więc pora coś z nią zrobić.

W linijce SuperFunkcja równa się dopisz liczba + i pozostaw 5 niezmienione (rys. 5).

(Rys. 5)

Ponownie spróbuj wywołać swoją funkcję, tym razem w nawiasie podaj liczbę, np. 5.

Jak widzisz funkcja ponownie działa poprawnie i wskazuje 10 (rys. 6).

(Rys. 6)

Oczywiście to tylko podstawowe działania matematyczne, ale możliwości tych funkcji są znacznie, znacznie szersze i tak naprawdę ogranicza Cię tylko Twoja wyobraźnia.

Możliwości są ogromne, jednak jest też kilka braków.

Po pierwsze nie ma informacji o tym co robi funkcja, tak jak to jest przy zwykłych funkcjach w Excelu.

Po drugie brakuje opisu parametrów, które są używane w funkcji, aby było wiadomo co należy tam wprowadzić.

Po trzecie funkcje mogą być używane wyłącznie w arkuszu, w którym zostały wpisane.

Z pierwszym nie ma za bardzo metody, aby sobie poradzić, lecz biorąc pod uwagę fakt, że samemu piszesz funkcję, to często nazwa potrafi wyjaśnić wszystko.

W drugiej kwestii można posiłkować się nazywaniem zmiennych tak, aby było jasne co wprowadzić.

Dodatkowo skrót CTRL+SHIFT+A, pomaga zobaczyć co dokładnie należy wprowadzić w naszej funkcji (rys. 7).

(Rys. 7)

Trzecia sprawa jest do obejścia i są sposoby, aby móc korzystać z własnej funkcji we wszystkich arkuszach Excela.

Jednak sposoby na ich wprowadzenie są na tyle karkołomne, że osobiście odradzam i zamiast tego zalecam przekopiowanie funkcji do arkusza, w którym faktycznie będzie używana.

Na koniec najważniejsze, aby móc korzystać z własnej funkcji Excela trzeba zapisać plik w formacie obsługującym makra.

Taki format to XLS albo nowszy XLSM.

Polecam tę funkcję, warto ją przećwiczyć, ale najważniejsze to wykorzystywać ją na co dzień.

Polecamy także:

• Jak wykorzystać autouzupełnianie oraz wypełnianie błyskawiczne?

• Gra Lotto – jak samemu ją napisać?