Tworzenie funkcji niestandardowych w programie Excel

Funkcje niestandardowe, podobnie jak makra, wykorzystują język programowania Visual Basic for Applications (VBA). Różnią się one od makr na dwa istotne sposoby. Po pierwsze używają one procedur Function zamiast procedur Sub. Oznacza to, że zaczynają się od instrukcji Function zamiast instrukcji Sub i kończą się instrukcją End Function zamiast End Sub. Po drugie, wykonują obliczenia zamiast wykonywać działania. Niektóre rodzaje instrukcji, takie jak instrukcje wybierające i formatujące zakresy, są wykluczone z funkcji niestandardowych. W tym artykule dowiesz się, jak tworzyć i używać funkcji niestandardowych. Aby tworzyć funkcje i makra, należy pracować z Edytorem Visual Basic (VBE), który jest otwierany w nowym oknie oddzielonym od programu Excel.

Załóżmy, że Twoja firma oferuje rabat ilościowy w wysokości 10% na sprzedaż produktu, pod warunkiem, że zamówienie obejmuje więcej niż 100 sztuk. W kolejnych akapitach przedstawimy funkcję do obliczania tego rabatu.

W poniższym przykładzie przedstawiono formularz zamówienia, w którym wymieniono każdą pozycję, ilość, cenę, rabat (jeśli istnieje) i wynikową cenę rozszerzoną.

Aby utworzyć niestandardową funkcję DISCOUNT w tym skoroszycie, wykonaj następujące kroki:

1. Naciśnij klawisze Alt+F11, aby otworzyć Edytor Visual Basic (na komputerze Mac naciśnij klawisze FN+ALT+F11), a następnie kliknij pozycję Wstaw > Moduł. Po prawej stronie Edytora Visual Basic zostanie wyświetlone nowe okno modułu.

2. Skopiuj poniższy kod i wklej go do nowego modułu.

   Function DISCOUNT(quantity, price)
    If quantity >=100 Then
    DISCOUNT = quantity * price * 0.1
    Else
    DISCOUNT = 0
    End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Teraz możesz przystąpić do korzystania z nowej funkcji DISCOUNT. Zamknij Edytor Visual Basic, zaznacz komórkę G7 i wpisz następujące polecenie:

=DISCOUNT(D7;E7)

Program Excel oblicza 10-procentowy rabat na 200 sztuk po cenie 47,50 zł za sztukę i zwraca 950,00 zł.

W pierwszym wierszu kodu języka VBA, Function DISCOUNT(quantity, price), wskazano, że funkcja DISCOUNT wymaga dwóch argumentów: ilości i ceny. Podczas wywoływania funkcji w komórce arkusza należy podać te dwa argumenty. W formule =DISCOUNT(D7;E7), D7 jest argumentem ilości, a E7 jest argumentem ceny. Teraz możesz skopiować formułę DISCOUNT do G8:G13, aby uzyskać pokazane poniżej wyniki.

Zastanówmy się, jak program Excel interpretuje tę procedurę funkcji. Po naciśnięciu klawisza Enter program Excel wyszukuje nazwę DISCOUNT w bieżącym skoroszycie i stwierdza, że jest to funkcja niestandardowa w module VBA. Nazwy argumentów ujęte w nawiasy, ilość i cena, są symbolami zastępczymi wartości, na których jest oparte obliczenie rabatu.

Instrukcja If w poniższym bloku kodu sprawdza argument ilość i określa, czy liczba sprzedanych sztuk jest większa lub równa 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Jeśli liczba sprzedanych sztuk jest większa niż lub równa 100, w języku VBA jest wykonywana następująca instrukcja, która mnoży wartość ilości przez wartość ceny, a następnie mnoży wynik przez 0,1:

Discount = quantity * price * 0.1

Wynik jest przechowywany jako zmienna Discount. Instrukcja języka VBA przechowująca wartość w zmiennej jest nazywana instrukcją przypisania, ponieważ oblicza wyrażenie po prawej stronie znaku równości i przypisuje wynik do nazwy zmiennej po lewej stronie. Ponieważ zmienna Discount ma taką samą nazwę jak procedura funkcji, wartość przechowywana w zmiennej jest zwracana do formuły arkusza, która wywołała funkcję DISCOUNT.

Jeśli ilość jest mniejsza niż 100, język VBA wykonuje następującą instrukcję:

Discount = 0

Na koniec poniższa instrukcja zaokrągla wartość przypisaną do zmiennej Discount do dwóch miejsc dziesiętnych:

Discount = Application.Round(Discount, 2)

Język VBA nie ma funkcji ROUND, ale program Excel ma taką funkcję. Dlatego, aby użyć funkcji ROUND w tej instrukcji, należy wskazać językowi VBA, aby wyszukała metodę Round (funkcja) w obiekcie Application (Excel). Można to zrobić, dodając wyraz Application przed wyrazem Round. Tej składni używaj zawsze, gdy chcesz uzyskać dostęp do funkcji programu Excel z modułu języka VBA.

Funkcja niestandardowa musi zaczynać się od instrukcji Function i kończyć się instrukcją End Function. Oprócz nazwy funkcji instrukcja Function zazwyczaj określa jeden lub więcej argumentów. Możesz jednak utworzyć funkcję bez żadnych argumentów. Program Excel zawiera kilka wbudowanych funkcji — na przykład LOS i TERAZ — które nie używają argumentów.

Zgodnie z instrukcją Function procedura funkcji zawiera jedną lub więcej instrukcji języka VBA, które podejmują decyzje i wykonują obliczenia przy użyciu argumentów przekazywanych do funkcji. Na koniec, gdzieś w procedurze funkcji, dołącz instrukcję, która przypisuje wartość do zmiennej o takiej samej nazwie jak funkcja. Ta wartość jest zwracana do formuły, która wywołuje funkcję.

Liczba słów kluczowych języka VBA, których możesz użyć w funkcjach niestandardowych, jest mniejsza niż liczba słów kluczowych, których możesz użyć w makrach. Funkcje niestandardowe nie mogą robić nic innego niż zwracać wartość do formuły w arkuszu lub do wyrażenia używanego w innym makrze lub funkcji języka VBA. Funkcje niestandardowe nie mogą na przykład zmieniać rozmiaru okien, edytować formuły w komórce ani zmieniać czcionki, koloru ani opcji deseniu tekstu w komórce. Jeśli do procedury funkcji zostanie dołączony kod akcji tego typu, funkcja zwróci #VALUE! .

Jedyną akcją, jaką może wykonać procedura funkcji (poza wykonywaniem obliczeń), jest wyświetlenie okna dialogowego. Możesz użyć instrukcji InputBox w funkcji niestandardowej jako metody uzyskiwania danych wejściowych od użytkownika wykonującego funkcję. Możesz użyć instrukcji MsgBox jako metody przekazywania informacji użytkownikowi. Można również używać niestandardowych okien dialogowych lub formularzy użytkownika, ale to zagadnienie wykraczające poza zakres tego wprowadzenia.

Nawet proste makra i funkcje niestandardowe mogą być trudne do odczytania. Możesz ułatwić ich zrozumienie, wpisując tekst objaśniający w formie komentarzy. Komentarze można dodawać, poprzedzając tekst objaśniający apostrofem. Na przykład w poniższym przykładzie przedstawiono funkcję DISCOUNT z komentarzami. Dodawanie takich komentarzy ułatwia Tobie lub innym osobom utrzymanie kodu języka VBA w miarę upływu czasu. Jeśli chcesz wprowadzić zmiany w kodzie w przyszłości, łatwiej zrozumiesz, co pierwotnie zrobiłeś.

Apostrof informuje program Excel, aby ignorował wszystko po prawej stronie w tym samym wierszu, dzięki czemu możesz tworzyć komentarze zarówno w samych wierszach, jak i po prawej stronie wierszy zawierających kod języka VBA. Możesz rozpocząć stosunkowo długi blok kodu od komentarza wyjaśniającego jego ogólny cel, a następnie użyć komentarzy w tekście w celu udokumentowania poszczególnych instrukcji.

Innym sposobem na udokumentowanie makr i funkcji niestandardowych jest nadanie im opisowych nazw. Na przykład, zamiast nazywać makro Labels, możesz nazwać je MonthLabels, aby dokładniej opisać przeznaczenie makra. Używanie opisowych nazw makr i funkcji niestandardowych jest szczególnie przydatne w przypadku tworzenia wielu procedur, szczególnie w przypadku tworzenia procedur o podobnych, ale nie identycznych celach.

Sposób dokumentowanie makr i funkcji niestandardowych to kwestia osobistych preferencji. Ważne jest, aby przyjąć jakąś metodę dokumentacji i używać jej spójnie.

Aby użyć funkcji niestandardowej, skoroszyt zawierający moduł, w którym utworzono funkcję, musi być otwarty. Jeśli ten skoroszyt nie jest otwarty, otrzymasz błąd #NAME? podczas próby użycia funkcji. Jeśli odwołujesz się do funkcji w innym skoroszycie, musisz poprzedzić nazwę funkcji nazwą skoroszytu, w którym znajduje się funkcja. Jeśli na przykład utworzysz funkcję o nazwie DISCOUNT w skoroszycie o nazwie Personal.xlsb i wywołasz tę funkcję z innego skoroszytu, musisz wpisać =personal.xlsb!discount(), a nie tylko =discount().

Możesz ograniczyć liczbę naciśnięć klawiszy (i możliwych błędów podczas pisania), wybierając funkcje niestandardowe z okna dialogowego Wstawianie funkcji. Funkcje niestandardowe są wyświetlane w kategorii Zdefiniowane przez użytkownika:

Łatwiejszym sposobem na udostępnienie funkcji niestandardowych przez cały czas jest przechowywanie ich w osobnym skoroszycie, a następnie zapisanie tego skoroszytu jako dodatku. Następnie możesz udostępnić dodatek przy każdym uruchomieniu programu Excel. Poniżej opisano, jak to zrobić:

Po wykonaniu tych kroków funkcje niestandardowe będą dostępne przy każdym uruchomieniu programu Excel. Jeśli chcesz dodać do biblioteki funkcji, wróć do Edytora Visual Basic. Jeśli poszukasz w eksploratorze projektu Edytora Visual Basic pod nagłówkiem VBAProject, zobaczysz moduł o nazwie pliku dodatku. Dodatek będzie miał rozszerzenie .xlam.

Dwukrotne kliknięcie tego modułu w eksploratorze projektu powoduje wyświetlenie kodu funkcji w Edytorze Visual Basic. Aby dodać nową funkcję, umieść punkt wstawiania po instrukcji End Function, która kończy ostatnią funkcję w oknie Kod, i zacznij pisać. W ten sposób możesz utworzyć dowolną liczbę funkcji, które będą zawsze dostępne w kategorii Zdefiniowane przez użytkownika w oknie dialogowym Wstawianie funkcji.

Ta zawartość została pierwotnie napisana przez Marka Dodge'a i Craiga Stinsona jako część ich książki Microsoft Office Excel 2007 Inside Out. Od tego czasu została ona zaktualizowana, aby mieć zastosowanie również do nowszych wersji programu Excel.