W dzisiejszym poście będę kontynuował temat funkcji użytkownika. W poprzednim opisałem najważniejsze podstawy tworzenia funkcji, generalnie wystarczające do codziennych i nieprofesjonalnych zastosowań. Na dziś zostawiłem nieco bardziej zaawansowane zagadnienia - przydatne wtedy, gdy robimy rozwiązania bardziej wyszukane, lub które mają być udostępnione światu.
Argumenty opcjonalne
Argumenty opcjonalne to takie, których nie musimy podawać - w przypadku ich braku funkcja użyje wartości domyślnej. Przykładem może być funkcja PMT(Stopa, Liczba_rat, Wa, [Wp], [Typ]) - gdzie argumenty Wp oraz Typ są opcjonalne. Jeśli do tej funkcji nie przekażemy tych argumentów, Excel przyjmie dla wartości domyślne, odpowiednio 0 oraz 0. Innymi słowy, poniższe dwa wywołania funkcji zwrócą tę samą wartość (miesięczna rata pożyczki 200 000 zł, udzielonej na 30 lat, oprocentowanej 6% w skali roku i płatnościach na koniec miesiąca):
=PMT(6%/12;360;200000) =PMT(6%/12;360;200000;0;0)
Argumenty opcjonalne definiujemy poprzedzając nazwę argumentu słowem kluczowym Optional. Musimy również pamiętać, że argumenty opcjonalne muszą znajdować się za wymaganymi (na końcu listy). Od razu w nagłówku funkcji możemy również podać wartość domyślną (użytą w przypadku braku argumentu) w następujący sposób:
Function NazwaFunkcji(arg_wymagany, [Optional arg_opcjonalny [As typ_danych] [= wartosc_domyslna]])
Spróbujmy teraz zaimplementować podobny mechanizm w naszej funkcji ProwizjaMaklerska. Założenie jest takie, że pierwszym argumentem będzie WartośćZlecenia, a drugim - ProwizjaMinimalna, przy czym ProwizjaMinimalna będzie argumentem opcjonalnym - w przypadku jego braku, minimalna prowizja za zlecenie będzie równa 0, czyli biuro maklerskie będzie nam naliczało koszt liniowo. Oto przykład:
Function ProwizjaMaklerska(WartoscZlecenia As Currency, Optional ProwizjaMinimalna As Variant = 0) As Currency Const StawkaProwizji = 0.0029 ProwizjaMaklerska = StawkaProwizji * WartoscZlecenia If ProwizjaMaklerska < ProwizjaMinimalna Then ProwizjaMaklerska = ProwizjaMinimalna End If End Function
Na poniższym obrazku mamy test obu wywołań funkcji (kolumna "B": =ProwizjaMaklerska(A2;3) oraz kolumna "C": =ProwizjaMaklerska(A2)):
Jak widzicie, funkcja działa również z jednym argumentem. Oczywiście argumentów opcjonalnych może być więcej, należy tylko pamiętać, że muszą one znajdować się za argumentami wymaganymi.
Nieokreślona liczba argumentów
Przykładem wbudowanej funkcji o nieokreślonej liczbie argumentów jest funkcja ŚREDNIA(liczba1, liczba2…), gdzie tych liczbx może być więcej i programiści z góry nie określili jaka to będzie liczba.
My również możemy napisać funkcję, która będzie pobierać nieokreśloną z góry liczbę argumentów. Kluczem do tego jest użycie tablicy poprzedzonej słowem ParamArray jako ostatniego lub jedynego argumentu. Argument ten zawsze jest opcjonalny (choć nie stosuje się słowa Optional) i zawsze ma typ Variant. Definicja wygląda następująco:
Function NazwaFunkcji([arg_wymagane], ParamArray NazwaTablicy() [as Variant]) [As typ_danych]
Arg_wymagane mogą zostać pominięte, a NazwaTablicy jest nazwą tablicy przechowującą naszą listę argumentów. Jak to wygląda w praktyce? Oto kolejna implementacja funkcji zwracającej sumę kwadratów liczb podanych jako argumenty:
Function SumaKwadratow(ParamArray ListaArgumentow() As Variant) As Long Dim Argument As Variant For Each Argument In ListaArgumentow SumaKwadratow = SumaKwadratow + Argument ^ 2 Next Argument End Function
W tym wypadku możemy tę funkcję wywołać z różna liczbą argumentów:
=SumaKwadratow(2) =SumaKwadratow(2;3) =SumaKwadratow(2;3;4)
Funkcja tablicowa (zwracająca tablicę wartości)
Możemy również tworzyć funkcje tablicowe, czyli takie, które zwracają tablicę wartości (patrz post o zastosowaniu formuł tablicowych). Aby uzyskać poprawnie wyniki należy je potwierdzić kombinacją klawiszy CTRL+SHIFT+ENTER, uprzednio zaznaczając zakres komórek, w których chcemy otrzymać wyniki.
Wyniki w postaci tablicy możemy uzyskać po zastosowaniu funkcji Array. Prosty przykład funkcji zwracającej poziomą tablicę nazw dni tygodnia przedstawiam poniżej:
Function DniTygodnia() DniTygodnia = Array("Poniedziałek", "Wtorek", "Środa", "Czwartek", "Piątek", "Sobota", "Niedziela") End Function
Aby sprawdzić działanie funkcji zaznaczamy poziomo siedem komórek (np. od A1 do G1), na pasku formuł wpisujemy =DniTygodnia() i zatwierdzamy poprzez CTRL+SHIFT+ENTER.
Zwracanie kodu błędu
Tworząc formuły musimy pamiętać również o tym, aby poinformować użytkownika o problemach w przypadku gdy nasza funkcja napotka takowe. Standardowe funkcje Excela zwracają wtedy specjalne wartości błędu, takie jak #DZIEL/0!, #N/D! itd. Rodzaj zwracanej wartości (rodzaj błędu) zależy od tego, jaka problem funkcja napotkała.
Moglibyśmy w zasadzie przypisać takiej funkcji łańcuch tekstowy np. ProwizjaMaklerska = "#ARG!" w przypadku, gdy brakuje argumentów, ale napotykamy tutaj na następujący problem: otóż inne funkcje odwołujące się do komórki z taką funkcją nie będą w stanie rozpoznać błędu (będą rozpoznawać wartość tekstową "#ARG!" - a to nie jest to samo co kod błędu!) - dotyczy to zwłaszcza funkcji informacyjnych służących do obsługi błędów: CZY.BŁ(), CZY.BŁĄD().
Musimy zatem odrzucić to rozwiązanie. Aby zwrócić rzeczywistą wartość błędu funkcji należy użyć funkcji CVErr języka VBA, która zmienia numer błędu na jego rzeczywistą wartość. Użycie takiej funkcji jest banalne:
NazwaFunkcji = CVErr(StałaBłędu)
Stałe błędów zostały zdefiniowane następująco:
xlErrDiv0(#Dziel/0!),xlErrNA(#N/D!),xlErrName(#NAZWA?),xlErrNull(#ZERO!),xlErrNum(#LICZBA!),xlErrRef(#ADR!),xlErrValue(#ARG!),
Przykład użycia funkcji CVErr:
ProwizjaMaklerska = CVErr(xlErrValue)
Należy pamiętać, aby używając funkcji CVErr do obsługi błędów w funkcji zmienić typ danych wartości zwracanej przez funkcję (typ danych funkcji) na Variant.
Kontrola wykonania funkcji
Każdy, kto tworzy nowe funkcje kiedyś stanie przed pytaniem: kiedy moja funkcja zostanie przeliczona ponownie? Zwykle (domyślnie) jest tak, ze funkcje użytkownika wykonuje obliczenia wtedy, gdy zostanie zmodyfikowany przynajmniej jeden z jej argumentów.
Możemy jednak stanąć przed koniecznością wymuszenia częstszych obliczeń w funkcji. Jeśli umieścimy w procedurze Function poniższą instrukcję nasza funkcja zostanie ponownie uruchomiona każdorazowo przy wykonaniu jakichkolwiek obliczeń w arkuszu (przy modyfikacji dowolnej komórki).
Application.Volatile True
Domyślnie właściwość Volatile ma wartość False.
Dodawanie opisu do funkcji
Opis funkcji to nic innego jak krótkie streszczenie lub pomoc, która pokazuje się w oknie dialogowym Wstaw funkcję przy wybraniu funkcji. Warto dodawać opisy funkcji wtedy, kiedy nasze projekty są udostępniane innym użytkownikom.
W VBA Excela istnieje bardzo wygodny sposób na dodanie opisu funkcji - dokonujemy tego za pomocą metody:
Application.MacroOptions Macro:="Nazwa_funkcji", Description:="Opis funkcji"
Przykład:
Application.MacroOptions Macro:="ProwizjaMaklerska", Description:="Funkcja obliczająca prowizję od zlecenia pobieraną przez biuro maklerskie."
Musimy pamiętać, że powyższa instrukcja powinna być wykonana tylko raz, zatem jeśli dystrybuujemy nasze funkcje w dodatku Excela, najlepiej jest umieścić jet w module ThisWorkbook w obsłudze zdarzenia Workbook_AddinInstall.
Przypisanie funkcji do kategorii
Podobnie jak w przypadku opisu funkcji umieszczenie naszej funkcji w odpowiedniej kategorii (np. Matematyczne, Tekstowe, Statystyczne) ułatwia potencjalnemu użytkownikowi szybkie odnalezienie funkcji. Jeśli chcemy dodać naszą funkcję do odpowiedniej kategorii korzystamy również z metody MacroOptions:
Application.MacroOptions Macro:="Nazwa_funkcji", Category:=Nr_kategorii
Ta instrukcja też powinna być wykonana tylko raz, dlatego przypisanie funkcji do kategorii powinno odbywać się razem z dodaniem opisu:
Application.MacroOptions Macro:="ProwizjaMaklerska", Category: = 1, Description:="Funkcja obliczająca prowizję od zlecenia pobieraną przez biuro maklerskie."
W tym wypadku naszą funkcję przypisaliśmy do kategorii Finansowe. Stałe kategorii przedstawione są poniżej:
| 1 | Financial |
| 2 | Date & Time |
| 3 | Math & Trig |
| 4 | Statistical |
| 5 | Lookup & Reference |
| 6 | Database |
| 7 | Text |
| 8 | Logical |
| 9 | Information |
| 10 | Commands |
| 11 | Customizing |
| 12 | Macro Control |
| 13 | DDE/External |
| 14 | User Defined |
Co ciekawe, możemy również stworzyć własną kategorię - podając zamiast liczby nazwę naszej nowej kategorii w cudzysłowach, np.:
Application.MacroOptions Macro:="ProwizjaMaklerska", _ Category: = "Maklerskie", Description:="Funkcja obliczająca prowizję od zlecenia pobieraną przez biuro maklerskie."
Jeśli taka kategoria już istnieje, nasza funkcja zostanie do niej dołączona, w przeciwnym wypadku zostanie utworzona nowa kategoria.