Funkcje użytkownika UDF w Excelu

Jeżeli pracując w arkuszach na co dzień wykonujecie wiele obliczeń i działacie na niestandardowych modelach, możliwe, że macie potrzebę zdefiniowania logiki w formie funkcji, która nie jest dostępna w excelu. Albo potrzebujecie wykonać operacje na danych, przekształcenie, które nie są zdefiniowane w Excelu. Wtedy warto zdefiniować własną funkcję. Taką, która poprawi czytelność arkuszy i raportów oraz usprawni pracę. Takie funkcje często w sieci są określane mianem UDF od user defined function.

Personal workbook

Zanim przejdziemy do tworzenia funkcji potrzebujemy omówić pewien plik a w zasadzie skoroszyt, który jest uruchamiany zawsze podczas startu programu Excel. Wszystko co tam zostanie umieszczone będzie dostępne w każdym nowo utworzonym pliku xlsx.

Głównym zastosowaniem personal workbook jest osobiste repozytorium makr. Czyli w zasadzie miejsce, gdzie składujemy swój własny kod, który jest dostępny w każdym nowo utworzonym skoroszycie. To jest właśnie naszym celem. Utworzenie funkcji, z której będziemy mogli korzystać w każdym pliku i arkuszu.

Lokalizacja personal.xlsb

Najczęściej ten plik znajduje się w folderze XLSTART, jak poniżej.

C:\Users\Nazwa_Użytkownika\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Dla naszych potrzeb fizyczna lokalizacja pliku nie jest istotna ale w przypadku problemów, które mogą wystąpić warto mieć świadomość, gdzie plik się znajduje aby dokonać ewentualnej naprawy.

Rejestrowanie makra

Makra możemy nagrywać poprzez przycisk w lewym dolnym rogu. Excel daje na możliwość nadanie własnej nazwy oraz przypisania skrótu. Kolejno mamy 3 opcje.

Skoroszyt do makr osobistych – makro zostanie zapisane w personal workbook.
Nowy skoroszyt – Excel utworzy nowy plik i zapisze w nim makro. Jednak dopóki my sami nie zapiszemy pliku, nie zostanie on nigdzie fizycznie zapisany.
Ten skoroszyt – makro zapisane w aktualnie otwartym pliku.

Funkcje UDF - opcje rejestrowania makra — Opcje zapisu makra

W naszym przypadku potrzebujemy nagrać puste makro do skoroszytu makr osobistych. Bez tej akcji nie będziemy mogli pisać własnego kodu w tym pliku.

Przygotowanie ustawień w panelu VBA

W tym momencie potrzebujemy przejść do panelu VBA. Możemy to zrobić skrótem [Alt + F11]. Drugą opcją jest przez przycisk Visual Basic ze zakładki Deweloper na wstążce.

Funkcje UDF - zakładka deweloper — Zakładka deweloper pozwala przejść do panelu VBA

Jeżeli brakuje tej zakładki to należy przejść do opcji programu Excel i przejść do Dostosowywanie Wstążki. Tam możemy zaznaczyć zakładkę Deweloper.

Funkcje UDF - dostosowywanie wstążki — Możemy samodzielnie ustawić widoczne zakładki na wstążce

Panel VBA

Na wstępie chciałbym zaznaczyć, że jeżeli przed otwarciem panelu albo kiedy na nim działacie macie otwarte jakieś dodatkowe okienka w Excelu, jak okienko z opcjami, lub zostawiliście komórkę w edycji to cały panel VBA zostanie zablokowany do momentu zamknięcia okienne i wyjścia z edycji poszczególnych obszarów lub komórek.

Jeżeli zarejestrowaliście prawidłowo makro do skoroszytu personalnego to widok projektu powinien wyglądać następująco.

Funkcje UDF - widok projektów w panelu VBA — W widoku projektów powinien być widoczny personal.xslb

Podstawowe opcje w panelu VBA

Zanim przejdziemy dalej warto jeszcze przejść do Tools-> Options i spojrzeć na 3 punkty.

Funkcje UDF - opcje z zakładki narzędzia w panelu VBA — Oferowane opcje w zakładce tools

Auto Syntax Check – zdecydowanie do wyłączenia. Sprawdzanie składnie w excelu oznacza notoryczne wyrzucanie uciążliwego komunikatu o niepoprawnej składni w niemalże każdym momencie edycji kodu. W rezultacie zamiast pomagać kompletnie utrudnia pracę.

Auto Indent i Tab Width – to w zależności od preferencji, czy chcemy mieć indentację i w jakiej długości.

Require Variable Declaration – to również w zależności od preferencji, czy chcemy wymusić deklarację zmiennych. Natomiast domyślnie nie jest to wymagane i jeżeli uruchamiamy kod z innych źródeł musimy być przygotowani, że ustawienie tej opcji poskutkuje błędami.

Pozostałe z opcji nie są tak istotne i można je zostawić tak jak są ustawione domyślnie. Dla ciekawych polecam zwyczajnie przetestować jak dane opcje działają na własnym środowisku.

Pasek narzędzi – toolbar

Kolejnym elementem, który warto dostosować jest pasek narzędzi.

Przechodzimy przez View -> Toolbars -> Customize

Funkcje UDF - pasek narzędzi w panelu VBA — Dostępne zakładki w pasku narzędzi

Do naszych potrzeb wystarczy dodanie menu Edit. Jest to o tyle istotne, że nie mamy żadnego skrótu do komentowania kodu, a tutaj mam dwa przyciski od dodawania i usuwania komentarza. Oczywiście można też komentować kod dodając ręcznie apostrof ale w dużych projektach z wieloma osobami jest to męczące.

Jeżeli nie chcemy dodawać całego paska a same przyciski możemy przejść do Commands i stamtąd przenieść przez drag & drop wybrany przycisk pod menu.

Funkcje UDF - pasek narzędzi w panelu VBA - opcje edycji — Zdecydowanie użyteczne są przyciski dodawania i zdejmowania komentarzy

Jak dodawać własne skróty do przycisków w panelu VBA

Jak już wcześniej wspomniałem dodatkowe przyciski nie posiadają skrótów. Jedynie pozycje z głównego menu można uruchomić skrótem. Przykładowo okienko Customize możemy otworzyć przez [Alt + v + t + c].

Kiedy mamy już otwarte okienko Customize klikamy ppm. na wybraną ikonę w pasku narzędzi.

W polu name możemy wpisać własną nazwę ale kluczowe jest dodanie znaku & na początku oraz zaznaczenie Image and Text. Tylko te dwie rzeczy razem umożliwią wykonywanie akcji za pomocą skrótu. Na przykładzie poniżej jest &Comment Block, które można teraz uruchamiać przez [Alt + c]

Funkcje UDF - pasek narzędzi w panelu VBA - dodawanie skrótów — Okienko Customize musi być otwarte aby pokazał się po kliknięciu pasek z opcjami edycji przycisku

Wykonanie analogicznej czynności dla Uncomment Block, pozwoli na zdejmowanie skrótem komentarzy prze [Alt + U].

Właściwość IsAddin

Ostatnim etapem przed tworzeniem funkcji jest jeszcze ustawienie parametru IsAddin na skoroszycie personalnym na wartość True.

Funkcje UDF - parametr IsAddin — IsAddin ustawione jako True pozwala na pominięcie nazwy pliku w wykorzystywanych funkcjach

Takie ustawienie pozwoli uniknąć dopisywania nazwy personal.xlsb! przed każdym wykonaniem funkcji w nowych plikach. Będziemy mogli zwyczajnie odnieść się bezpośrednio do nazwy funkcji.

Tworzenie funkcji w VBA

Dla naszych potrzeb utworzymy prostą funkcję OperacjeLiczbowe, która będzie wykonaywać wybraną operacje na dwóch liczbach według zadanego parametru. Będą to mnożenie, dzielenie, dodawanie, odejmowanie. Z dodatkowych założeń przyjmiemy, że przy dzieleniu przez zero funkcja zwraca zero, i analogicznie kiedy wybierzemy wartość parametru inna niż 1-4.

Public Function OperacjeLiczbowe(a As Double, b As Double, param As Byte) As Double
    Select Case param
        Case 1
            OperacjeLiczbowe = a * b
        Case 2
            If b <> 0 Then
                OperacjeLiczbowe = a / b
            Else
                OperacjeLiczbowe = 0
            End If
        Case 3
            OperacjeLiczbowe = a + b
        Case 4
            OperacjeLiczbowe = a - b
        Case Else
            OperacjeLiczbowe = 0
    End Select
End Function

Funkcje definiujemy wyrażeniem function, kolejno w nawiasie podając zdefiniowane argumenty i dodając As double określające typ zawracanej wartości. Kolejno jest cała logika i zamknięcie przez End Function.

To na co warto zwrócić uwagę jest składnia dla zwracania danych. Nie ma polecenia return, które występuje w wielu innych językach programowania. Natomiast wartość zwracaną ustalamy poprzez bezpośrednie przypisanie jej do funkcji jak OperacjeLiczbowe = 0.

Przykładowe zastosowanie może być następujące.

Funkcje UDF - przykład wykorzystania funkcji — Przykładowe zastosowanie utworzonej funkcji

Funkcja działa jak założyliśmy oraz nie wymaga dodatkowego przedrostka w nazwie oznajmiającego, że odnosimy się do PERSONAL.xlsb.

Podgląd parametrów funkcji

Pewną podpowiedzią jaką można wykorzystać jest automatyczne wypełnienie nazw argumentów funkcji.

Jeżeli zaczniemy wpisywać funkcję i będziemy mieć już podaną nazwę =OperacjeLiczbowe(

To po wciśnięciu [Ctrl + Shift + A] wypełnią się nazwy argumentów =OperacjeLiczbowe(a;b;param)

Dodatkowy opis funkcji UDF

Do naszej funkcji można jeszcze dorzucić opis funkcji oraz argumentów, oraz przypisanie funkcji do danej kategorii. Tych kroków nie można wykonać w prosty sposób z frontu i należy to zrobić poprzez stworzenie właściwego kodu.

Public Sub OpiszFunkcje()
    Dim nazwaFunkcji As String
    Dim opisFunkcji As String
    Dim kategoriaFunkcji As Byte
    Dim argumentyFunkcji(1 To 3) As String

    nazwaFunkcji = "OperacjeLiczbowe"
    opisFunkcji = "Funkcja zwraca wartość operacji na dwóch liczbach w zależności od zadanego parametru"
    kategoriaFunkcji = 3 'Matematyczne
    argumentyFunkcji(1) = "Pierwsza liczba do zadanej operacji"
    argumentyFunkcji(2) = "Druga liczba do zadanej funkcji"
    argumentyFunkcji(3) = "Parametr decydująco o wykonanej operacji. 1 - Mnożenie 2 - Dzielenie 3 - Dodawanie 4 - Odejmowanie"

ThisWorkbook.Activate
   ThisWorkbook.Application.MacroOptions Macro:=nazwaFunkcji, _
         Description:=opisFunkcji, _
         Category:=kategoriaFunkcji, _
         ArgumentDescriptions:=argumentyFunkcji
         
ThisWorkbook.IsAddin = True

End Sub

Powyższa procedura pozwoli nam na wykonanie kilku operacji,

Przede wszystkim dodamy opis funkcji i opis argumentów
Nadamy naszej funkcji kategorię, dzięki czemu będzie widoczna w konkretnej kategorii – w tym przypadku matematyczne. Pełna lista opcji ze znacznikiem numerycznym poniżej.
- 0 – Wszystkie – bez kategorii
- 1 – Finansowe
- 2 – Data i godzina
- 3 – Matematyczne i trygonometryczne
- 4 – Statystyczne
- 5 – Wyszukiwania i odwołań
- 6 – Bazy danych
- 7 – Tekstowe
- 8 – Logiczne
- 9 – Informacyjne
- 10 – Poleceń
- 11 – Customizing
- 12 – Macro Control
- 13 – Zewnętrzne
- 14 – Zdefiniowane prze użytkownika
- 15 – Inżynierskie
- 16 – Modułów
Ponadto nadamy automatycznie wartość True dla parametru ThisWorkbook.IsAddin. W moim przypadku cały ten kod jest wykonywany z poziomu skoroszytu makr osobistych. W przeciwnym wypadku ta linijka kodu nie ma sensu.

Funkcje UDF - argumenty funkcji — Opis funkcji i jej argumentów jest widoczny podczas edycji funkcji

Funkcje UDF - kategorie funkcji — Nasza funkcja jest widoczna w kategorii matematyczne

Dodatkowe ustawienia personal.xlsb

Ukrywanie okna pliku

Może się zdarzyć, że nasz plik z makrami osobistymi będzie sie uruchamiać automatycznie i wtedy najlepszym rozwiązaniem jest ukrycie tego pliku.

Przechodzimy do Widok -> Ukryj okno

Funkcje UDF - ukryj okno pliku — Pozwala ukryć aktualne okno pliku

Wykorzystanie Workbook_Open dla personal.xlsb

Jeżeli chcemy aby opis funkcji był widoczny w każdym pliku oraz chcemy mieć pewność, że parametr IsAddin jest zawsze True dla skoroszytu makr osobistych to najlepiej umieścić kod VBA na poziomie skoroszytu w procedurze Workbook_Open(). Służy ona do uruchamiania kodu zawsze podczas uruchamiania pliku. Wtedy kod może wyglądać następująco.

Funkcje UDF - workbook_open — Dodanie workbook_open dla personal.xlsb jako forma zabezpieczenia przed cofnięciem zmian

Zakończenie

Konkludując niniejszy post chciałbym zaznaczyć, że celem było ukazanie najważniejszych punktów podczas tworzenia własnych funkcji, które planujemy na bieżąco wykorzystywać. W tym konkretnym przypadku cel był aby dana funkcja była ogólnodostępna na danym komputerze wraz z jej podstawową definicją. Oczywiście są inne metody jak chociażby tworzenie własnych plików addin lub tworzenie własnych blibliotek.

W przypadku samej funkcji jaka została wybrana chciałem jedynie pokazać możliwość stworzenia funkcji niż możliwości VBA jakie są oferowane do definiowania funkcji. Tych jest rzecz jasna dużo więcej niż tylko podstawowa funkcja z dwoma argumentami.