Ten samouczek wyjaśni, jak tworzyć funkcje zdefiniowane przez użytkownika w VBA.
VBA składa się z Procedur Podrzędnych i Procedur Funkcyjnych. Procedury funkcji zwracają wartość i mogą być wywoływane przez procedury podrzędne lub mogą być używane w arkuszu Excel, gdzie wartość tworzona przez funkcję jest zwracana do arkusza Excel. Excel oczywiście ma szereg wbudowanych funkcji - takich jak funkcja Sum lub funkcja If. Procedury funkcji, które piszesz w języku VBA, są używane w podobny sposób do funkcji wbudowanych i są znane jako funkcje zdefiniowane przez użytkownika (UDF).
Po co tworzyć UDF?
Brak wymaganej funkcji
Jednym z głównych powodów, dla których chcesz utworzyć UDF w programie Excel, jest to, że nie ma istniejącej wbudowanej funkcji, która wykona zadanie za Ciebie. Napisanie własnej funkcji w VBA jest zwykle najskuteczniejszym sposobem rozwiązania problemu. Poniższa funkcja przekonwertuje wartość z kilogramów na funty, gdy parametr zmienny (dblKilo) jest używany do uzyskania wartości kilogramów w celu wykonania obliczeń.
Zamień podprogram (makro)
Możesz napisać procedurę podrzędną (makro), aby rozwiązać zadanie za Ciebie - ale procedury podrzędne nie zwracają wartości i nie są dynamiczne - innymi słowy, jeśli wartości w arkuszu zmienią się, będziesz musiał ponownie uruchomić makro, aby obliczenia w makrze aktualizowały dane. Poniższa procedura podrzędna również przekształci nasze kilogramy na funty, ale za każdym razem, gdy dane zmienią się w A1, trzeba będzie ponownie uruchomić makro, aby zaktualizować wynik.
Zamień formułę
Możesz mieć naprawdę skomplikowaną formułę w programie Excel, której musisz używać wielokrotnie – umieszczenie formuły w kodzie VBA ułatwia czytanie i zrozumienie – a także być może usuwając miejsce na błąd użytkownika podczas wpisywania formuły.
Tworzenie UDF
Aby utworzyć UDF, najpierw dodaj moduł do skoroszytu programu Excel lub, jeśli masz skoroszyt makr osobistych, możesz użyć istniejącego modułu lub dodać nowy. Aby to zrobić, musisz być w Edytorze Visual Basic (VBE). Aby przejść do VBE, naciśnij ALT + F11 lub kliknij opcję Visual Basic na karcie Deweloper na wstążce.
WSKAZÓWKA: Jeśli nie masz włączonej karty Deweloper na wstążce, przejdź do Plik, Opcje i kliknij Dostosuj wstążkę. Upewnij się, że pole wyboru Deweloper jest zaznaczone i kliknij OK.
Aby wstawić nowy moduł, wybierz projekt VBA, do którego chcesz wstawić moduł (albo projekt VBA dla bieżącej książki, w której pracujesz, albo skoroszyt makr osobistych), kliknij przycisk Wstawić Menu i kliknij Moduł
Po utworzeniu modułu możesz rozpocząć tworzenie swojego UDF.
Wszystkie UDF zaczynają się od Function, a następnie od nazwy UDF. Funkcje mogą być prywatne lub publiczne, ale zazwyczaj chcesz, aby plik UDF był publiczny, aby były wyświetlane w oknie dialogowym Wstawianie funkcji w programie Excel (zobacz używanie funkcji z arkusza programu Excel w dalszej części tego artykułu). Jeśli nie umieścisz słowa kluczowego Private przed funkcją, funkcja jest automatycznie publiczna.
| 123 | Funkcja TestFunction1 (intA jako liczba całkowita) jako liczba całkowitaFunkcja Testu1= intA * 7Koniec funkcji |
Powyższa funkcja ma jeden argument (intA). Możesz utworzyć funkcję z wieloma argumentami
| 123 | Funkcja TestFunction2(intA jako liczba całkowita, intB jako liczba całkowita, intC jako liczba całkowita) jako liczba całkowitaFunkcja Testu2 = (intA * intB) + intCKoniec funkcji |
Możesz także utworzyć funkcję z opcjonalnymi argumentami. Jeśli argument zostanie pominięty, możesz ustawić wartość domyślną dla argumentu w funkcji.
| 123 | Funkcja TestFunction3(intA jako liczba całkowita, intB jako liczba całkowita, opcjonalnie intC jako liczba całkowita=10) jako liczba całkowitaFunkcja Testu3 = (intA * intB) + intCKoniec funkcji |
Korzystanie z funkcji z arkusza Excel
Utworzone funkcje będą domyślnie wyświetlane na liście funkcji w sekcji Zdefiniowane przez użytkownika listy funkcji.
Kliknij na fx aby wyświetlić okno dialogowe Wstaw funkcję.
Wybierz Określony przez użytkownika z listy kategorii
Wybierz żądaną funkcję z dostępnych Funkcje zdefiniowane przez użytkownika.
Alternatywnie, gdy zaczniesz pisać swoją funkcję w programie Excel, funkcja powinna pojawić się na rozwijanej liście funkcji.
Zapisywanie funkcji w pliku Excel
Ponieważ funkcje są napisane w kodzie VBA, ma się rozumieć, że kod musi być dostępny w skoroszycie, aby można go było używać w arkuszu Excel. Możesz zapisać swoje funkcje w skoroszycie, w którym ich używasz, lub w skoroszycie makr osobistych. Skoroszyt makr osobistych jest ukrytym plikiem, który jest dostępny za każdym razem, gdy program Excel jest otwarty, a zatem dostępny dla dowolnego skoroszytu w programie Excel. Zwykle jest ono tworzone podczas rejestrowania makra i wybierania opcji przechowywania makra w skoroszycie makr osobistych.
Jeśli chcesz zachować swoje funkcje zapisane w skoroszycie, nad którym pracujesz, musisz upewnić się, że podczas zapisywania skoroszytu jest on zapisany jako „Skoroszyt z obsługą makr‘ lub an xlsm plik.
