Tworzenie funkcji niestandardowych w programie Excel

W funkcjach niestandardowych, takich jak makra, jest Visual Basic for Applications języka programowania VBA. Różnią się one od makr na dwa istotne sposoby. Najpierw należy użyć procedur funkcji zamiast procedur podrzędnych. Oznacza to, że zaczynają się od instrukcji Function zamiast instrukcji Sub i kończą się funkcją End Function zamiast End Sub. Po drugie, zamiast wykonywać akcje, wykonują obliczenia. Niektóre rodzaje instrukcji, takie jak instrukcje zaznaczania i formatowania zakresów, są wyłączane z funkcji niestandardowych. Z tego artykułu dowiesz się, jak tworzyć i używać funkcji niestandardowych. Aby utworzyć funkcje i makra, należy pracować z edytorem Visual Basic (VBE),który jest otwierany w nowym oknie, oddzielnie od Excel.

Załóżmy, że firma oferuje rabat w ilości 10 procent na sprzedaż produktu pod warunkiem, że zamówienie jest dla ponad 100 jednostek. W poniższych akapitach przedstawiono funkcję obliczania tego rabatu.

W poniższym przykładzie pokazano formularz zamówienia z listą każdej pozycji, ilości, ceny, rabatu (jeśli jest dostępna) i wynikowej rozszerzonej ceny.

Aby utworzyć w tym skoroszycie niestandardową funkcję RABAT, wykonaj następujące czynności:

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 okna modułu zostanie wyświetlone nowe okno Visual Basic moduł.

2. Skopiuj i wklej następujący kod 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 już korzystać z nowej funkcji RABAT. Zamknij edytor Visual Basic, zaznacz komórkę G7 i wpisz następujące polecenie:

=RABAT(D7;E7)

Excel oblicza 10-procentowy dyskont dla 200 jednostek przy wartości 47,50 zł na jednostkę i zwraca wartość 950,00 zł.

W pierwszym wierszu kodu VBA funkcja RABAT(ilość;cena) wskazano, że funkcja RABAT wymaga dwóch argumentów: ilości i ceny. W przypadku wywołania funkcji w komórce arkusza należy uwzględnić te dwa argumenty. W formule =RABAT(D7;E7) argument ilość jest argumentem ilości, a E7 to argument cena. Teraz możesz skopiować formułę RABAT do G8:G13, aby uzyskać przedstawione poniżej wyniki.

Przyjrzyjmy się, jak Excel tę procedurę funkcji. Po naciśnięciu klawisza EnterExcel wyszukuje nazwę RABAT w bieżącym skoroszycie i znajduje w module VBA tę funkcję niestandardową. Nazwy argumentów ujęte w nawiasy, ilość i cena sąsymbolami zastępczymi wartości, na których jest oparty obliczenie rabatu.

Instrukcja Jeżeli w następującym bloku kodu sprawdza argument ilość i ustala, czy sprzedana liczba pozycji jest większa niż lub równa 100:

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

Jeśli liczba sprzedanych pozycji jest większa niż lub równa 100, VBA wykonuje następującą instrukcje, 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 Rabat. Instrukcja VBA, która przechowuje wartość w zmiennej, jest nazywana instrukcjią przydziału, ponieważ oblicza wyrażenie po prawej stronie znaku równości i przypisuje wynik do nazwy zmiennej po lewej stronie. Zmienna Rabat ma taką samą nazwę jak procedura funkcji, dlatego wartość przechowywana w zmiennej jest zwracana do formuły arkusza o nazwie FUNKCJA.DYSKON.

Jeśli liczba jest mniejsza niż 100, VBA wykonuje następujące instrukcje:

Discount = 0

Na koniec następująca instrukcja zaokrągla wartość przypisaną do zmiennej Rabat do dwóch miejsc dziesiętnych:

Discount = Application.Round(Discount, 2)

VBA nie ma funkcji ZAOŁ, ale Excel działa. Dlatego użycie funkcji ROUND w tej instrukcji wymaga, aby funkcja VBA szukać metody Round (funkcji) w obiekcie Application (Excel). Możesz to zrobić, dodając wyraz Aplikacja przed wyrazem Zaokrąglaj. Używaj tej składni zawsze, gdy chcesz uzyskać dostęp do Excel z modułu VBA.

Funkcja niestandardowa musi zaczynać się od instrukcji Function i kończyć się instrukcje End Function. Oprócz nazwy funkcji instrukcja Function zazwyczaj określa co najmniej jeden argument. Można jednak utworzyć funkcję bez argumentów. Excel kilka wbudowanych funkcji, na przykład LOS i TERAZ, które nie używają argumentów.

Po instrukcji Function procedura funkcji zawiera jedną lub więcej instrukcji VBA, które mogą podejmować decyzje i wykonywać obliczenia przy użyciu argumentów przekazanych do funkcji. Na koniec gdzieś w procedurze funkcji należy dołączyć instrukcje, które przydzielają wartość do zmiennej o takiej samej nazwie jak funkcja. Ta wartość jest zwracana do formuły, która wywołuje tę funkcję.

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

Jedną z czynności, które może wykonać procedura funkcji (oprócz wykonywania obliczeń), jest wyświetlenie okna dialogowego. Instrukcji InputBox można użyć w funkcji niestandardowej jako środka uzyskiwania danych wejściowych od użytkownika wykonującego tę funkcję. Instrukcji MsgBox można użyć jako środka przekazywania informacji do użytkownika. Możesz także używać niestandardowych okien dialogowych lub formularzy użytkownika,ale jest to temat wykraczający poza zakres tego wprowadzenia.

Nawet proste makra i funkcje niestandardowe mogą być trudne do odczytania. Możesz je ułatwić, wpisując tekst objaśnijący w formie komentarzy. Komentarze należy dodać, poprzedzając tekst objaśnijący apostrofem. Na przykład w poniższym przykładzie pokazano funkcję RABAT z komentarzami. Dodawanie takich komentarzy ułatwia Ty i inne osoby zachowywanie kodu VBA w czasie, który mije czas. Jeśli w przyszłości będzie konieczne zmiany w kodzie, ułatwisz zrozumienie tego, co pierwotnie zostało przez Ciebie założone.

Apostrof nakazuje Excel zignorować wszystko po prawej stronie w tym samym wierszu, aby można było tworzyć komentarze samodzielnie lub po prawej stronie wierszy zawierających kod VBA. Możesz zacząć stosunkowo długi blok kodu od komentarza objaśniacego jego ogólny cel, a następnie użyć komentarzy w tekście do poszczególnych instrukcji dokumentu.

Innym sposobem dokumentować makra i funkcje niestandardowe jest nadaj im opisowe nazwy. Zamiast na przykład nazwać makro Etykiety, możesz nazwać etykiety miesięcy, opisujące dokładniej cel makra. Używanie opisowych nazw makr i funkcji niestandardowych jest szczególnie przydatne w przypadku tworzenia wielu procedur, zwłaszcza w przypadku tworzenia procedur o podobnych, ale nie identycznych celach.

Sposób dokumentować makra i funkcje niestandardowe należy do osobistych preferencji. Co ważne, należy stosować jakiś sposób dokumentacji i konsekwentnie jej używać.

Aby użyć funkcji niestandardowej, musi być otwarty skoroszyt zawierający moduł, w którym została utworzona funkcja. Jeśli ten skoroszyt nie jest otwarty, zostanie #NAME? podczas próby użycia tej funkcji. Odwołanie do funkcji w innym skoroszycie musi być poprzedzone nazwą funkcji skoroszytu, w którym ta funkcja się znajduje. Jeśli na przykład utworzysz funkcję RABAT w skoroszycie o nazwie Personal.xlsb i zadzwonisz do tej funkcji z innego skoroszytu, musisz wpisać =personal.xlsb!discount(),a nie po prostu =discount().

Możesz zaoszczędzić kilka naciśnięć klawiszy (i możliwe błędy pisowni), wybierając funkcje niestandardowe w oknie dialogowym Wstawianie funkcji. Funkcje niestandardowe są wyświetlane w kategorii Zdefiniowane przez użytkownika:

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

Po zakończeniu 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 funkcji. Jeśli w oknie Visual Basic w Eksploratorze Project pod nagłówkiem VBAProject zobaczysz moduł o nazwie nazwanej po pliku dodatku. Dodatek będzie miał rozszerzenie xlam.

Dwukrotne kliknięcie tego modułu w Eksploratorze Project powoduje wyświetlenie kodu Visual Basic funkcji w Edytorze plików. 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żna utworzyć tyle funkcji, ile potrzeba, a funkcje te będą zawsze dostępne w kategorii Zdefiniowane przez użytkownika w oknie dialogowym Wstawianie funkcji.

Ta zawartość została pierwotnie autorstwa Marka Dodgea i Andy'ego Stinsona w ramach ich książki Microsoft Office Excel 2007 Inside Out. Od tego czasu została zaktualizowana, aby można było stosować ją również Excel wersjach.