VBA/Excel – tworzenie opisów pomocy dla własnych funkcji (UDF)

Tworząc moduł z własnymi funkcjami fajnie by było stworzyć jakieś ułatwienie dla użytkowników naszych funkcji. Przykładowo napisałem prostą funkcję, która na podstawie twierdzenia Pitagorasa oblicza długość przeciwprostokątnej, gdy znamy długości dwóch przyprostokątnych.

Function Pitagoras(BokA As Single, BokB As Single) As Single
    Pitagoras = Sqr(BokA ^ 2 + BokB ^ 2)
End Function

Fajnie by było, gdyby pisząc nazwę funkcji podpowiadacz powiedział co owa funkcja robi, oraz jakie argumenty przyjmuje (podobnie jak ma to miejsce gdy wpisuję z palca nazwę funkcji =SUMA

Niestety, ale tego nie da się osiągnąć w VBA. Aby działał podpowiadacz (IntelliSense) należałoby napisać ową funkcję w Visual Studio jako dodatek XLL (taki odpowiednik pliku DLL). Jest jeszcze inna opcja: Excel DNA, ale instalowanie jakiś dodatkowych pluginów do Excela, aby makra pisane w VBA obsługiwały Intellisense uważam za nieporozumienie. Istnieją natomiast dwie namiastki owego rozwiązania, którą tutaj opiszę.

Ctrl+Shift+A

Po wpisaniu nazwy funkcji =Pitagoras( możemy nacisnąć kombinację klawiszy Ctrl + Shift + A. Excel dopisze wtedy w nawiasie nazwy argumentów (nazwy zmiennych) jakie przyjmuje ta funkcja. Rozwiązanie średnie, ale można zapamiętać tę sztuczkę.

Podpowiadacz w oknie fx (Argumenty funkcji)

Dodając odpowiedni kod (metoda Application.MacroOptions) możemy dodać opis funkcji, który pojawia się w okienku Argumenty funkcji.

Aby coś takiego zrobić należy wywołać metodę Application.MacroOptions z odpowiednimi argumentami. Najlepiej jest ją podpiąć pod funkcję auto_open znajdująca się w dowolnym module – wtedy automatycznie przy otwieraniu skoroszytu zostanie wywołana metoda rejestrująca pomoc dla naszej funkcji. Oto przykładowy kod jaki popełniłem:

Function Pitagoras(BokA As Single, BokB As Single) As Single
    Pitagoras = Sqr(BokA ^ 2 + BokB ^ 2)
End Function

Private Sub PomocUDF(Nazwa As String, Opis As String, Args, Optional Kategoria As Variant = 14, Optional HelpFile = "")
    On Error Resume Next
    
    #If VBA7 Then
        'Excel 2010 i nowsze
        Application.MacroOptions Macro:=Nazwa, Description:=Opis, Category:=Kategoria, HelpFile:=HelpF, ArgumentDescriptions:=Args
    #Else
        'W Excelu 2007 MacroOptions nie ma ostatniego argumentu ArgumentDescriptions i VBA by sypał błędem kompilacji
        'stąd to obejście z kompilacją warunkową
        Application.MacroOptions Macro:=Nazwa, Description:=Opis, HelpFile:=HelpF, Category:=Kategoria
    #End If
End Sub


Sub RejestrujMojeFunkcje()
    Dim Nazwa As String, Opis As String
    Dim Args(1 To 2) As String
    
    Nazwa = "Pitagoras"
    Opis = "Funkcja na podstawie tw. Pitagorasa oblicza długość przeciwprostokątnej gdy podamy wartości obu przyprostokątnych"
    Args(1) = "Długość pierwszej przyprostokątnej"
    Args(2) = "Długość drugiej przyprostokątnej"
        
    Call PomocUDF(Nazwa, Opis, Args, "OfficeBlog.pl")
End Sub


Sub auto_open()
    Call RejestrujMojeFunkcje
End Sub

Sub auto_close()
    Application.MacroOptions Macro:="Pitagoras", Description:=Empty, Category:=Empty
End Sub

Pobierz przykładowy plik

Tutaj mamy dwie ciekawostki: po pierwsze w Office 2007 metoda Application.MacroOptions nie obsługuje parametru Parameters (tablica zawierająca opisy poszczególnych zmiennych/parametrów dla naszej funkcji). Stąd użyłem instrukcji kompilacji warunkowej #IF VBA7 (ten fragment kodu wykona się gdy mamy pakiet Office 2007 lub nowszy).

Po drugie: jako ID kategorii możemy użyć liczby. Wtedy dana funkcja zostanie umieszczona w okienku funkcje w odpowiedniej grupie według poniższej rozpiski:

Ale jeśli ten argument potraktujemy jako string to zostanie utworzona nowa kategoria o podanej nazwie w tym przypadku utworzyłem kategorię OfficeBlog.pl