Pravomoci vlastních funkcí (UDF)

Při výkladu funkcí (Function) ve VBA se na první místě zmiňuje, že na rozdíl od procedur (Sub) vracejí hodnotu a vyskytují se tak často na pravé straně příkazů přiřazení. A stejně jako procedury, které „tajně“ mohou propašovat ven hodnoty prostřednictvím veřejných proměnných nebo přebírání argumentů odkazem (ByRef), tak i funkce mají svá tajemství. Pokud je užijeme na listu, stávají se „dospělými“ uživatelskými funkcemi (UDF) – a měly by se krotit ve svém chování. Jejich výsledky neopouští místo, odkud jsou volány, tzn. buňky (viz Application.Caller), a jejich užití neovlivňuje ani prostředí aplikace. Je tomu tak vždy? Pojďme se podívat na jejich čertovské kousky, když se nikdo nedívá…

MsgBox, InputBox, FileDialog, …

Function TESTOVKA()
    'dialog (MsgBox)
    TESTOVKA = MsgBox("Baf!")
End Function
Vlastní funkce - MsgBox

Vlastní funkce – MsgBox

Function TESTOVKA() As String
    'dialog s textovým polem (InputBox)
    TESTOVKA = Application.InputBox("Kdo jsi?", "Identifikace")
End Function
Vlastní funkce - InputBox

Vlastní funkce – InputBox

Function TESTOVKA()
    'dialog pro výběr souboru
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Show
        'vrací cestu k vybranému souboru
        TESTOVKA = .SelectedItems(1)
    End With
End Function
Vlastní funkce - FileDialog

Vlastní funkce – FileDialog

CommandBars a Dialogs

Function TESTOVKA()
    'zobrazení panelu
    'nestandardní chování po uzavření
    'zde Vložit symbol
    Application.CommandBars.FindControl(ID:=308).Execute
    TESTOVKA = "Hej!"
End Function

Vyvolávání panelů ve funkcích může způsobit neočekávané stavy. Excel často v danou chvíli jednoduše neočekává jejich zobrazení a neumí si poradit se sledem událostí v souvislosti s editací buňky. Vestavěné dialogy kolekce Dialogs se mi nepodařilo z funkcí vyvolat.

Komentáře

Function TESTOVKA()
    'komentář v buňce, lze nastavit i pro jinou buňku
    Application.Caller.AddComment "Jupí!"
    TESTOVKA = "Jejda!"
End Function
Vlastní funkce - komentář

Vlastní funkce – komentář

Zjednodušeně lze říci, že vykreslované objekty lze ve funkcích na listu použít. A ano, takový komentář lze například vložit do sousední buňky (Application.Caller.Offset(0, 1))

Ověření

Function TESTOVKA()
    'ověření, lze nastavit i pro jinou buňku
    With Application.Caller.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, _
            Operator:=xlBetween, Formula1:="první;druhá"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = False
    End With
    TESTOVKA = "Šmarjá!"
End Function

Pro ověření samozřejmě platí, že výsledek funkce nesmí být v rozporu s pravidly ověřování.

Vlastní funkce - ověření

Vlastní funkce – ověření

Obrazce

Function TESTOVKA()
    'nakreslení tvaru
    ActiveSheet.Shapes.AddShape msoShapeCorner, 114, 30, 96, 90
    TESTOVKA = "Nakresleno!"
End Function
Vlastní funkce - obrazec

Vlastní funkce – obrazec

Grafy

Function TESTOVKA()
    'vložení grafu
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("List1!$C$7:$E$8")
    TESTOVKA = "Safra!"
End Function
Vlastní funkce - graf

Vlastní funkce – graf

Barva pozadí a ohraničení buňky

Nenašel jsem způsob, jak ve funkci úspěšně nastavit barvu pozadí buňky a to ani podmíněným formátem, který by jinak aplikovat šel. Omezené možnosti má i ohraničení buňky.

Function TESTOVKA()
    'ohraničení, nelze měnit typ a tloušťka čáry
    With Application.Caller.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = 3
    End With
    TESTOVKA = "Jaj!"
End Function
Vlastní funkce - ohraničení

lastní funkce – ohraničení

Jiné buňky, výběry, odskoky, aktivace

Ve funkcích listu nelze měnit výběry a provádět aktivace objektů (.Select, .Activate), ani řídit odskoky přes Applicaton.GoTo nebo hypertextové odkazy. Zakázány jsou také zápisy do oblastí buněk (Range, Cells) nebo manipulace s definovanými názvy (pojmenovanými vzorci).

Změny titulků, popisků aj.

Jednoznačně není možné říci, co lze a nelze měnit. Kupříkladu stavový řádek se ovlivnit z funkce listu nenechá, na druhou stranu titulek aplikace ano.

Function TESTOVKA()
    Application.Caption = "Můj šéf je lump!"
    TESTOVKA = "Hmm!"
End Function

Filtry, Tabulky

Z funkcí listu není možné si pohrát s automatickým filtrem nebo třeba vložit Tabulku (Seznam). Na druhou stranu vás nechá setřídit oblast.

Function TESTOVKA()
    'setřídění oblasti
    With ActiveWorkbook.Worksheets("List1").Sort
        .SetRange Range("A1:A6")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    TESTOVKA = "Aha!"
End Function

Volání procedury

Funkce se může odvolávat na jiné funkce a procedury. V tu chvíli pro ně platí stejná pravidla jako pro funkce, ze kterých jsou volány. Ve funkcích je stejně jako v procedurách možné užít v rámci parametrů ByRef, nicméně z „funkčního vězení“ ven nepropašujete žádný moták s hodnotou.

Function TESTOVKA()
    'volání procedury
    Call Procedura
    TESTOVKA = "Sláva!"
End Function

Sub Procedura()
    MsgBox "Hurá!"
End Sub
Vlastní funkce - volání procedury

Vlastní funkce – volání procedury

Nelze načasovat spuštění procedury (Application.OnTime), ani ve funkci přiřadit makro klávesové zkratce (Application.OnKey). Ve funkci listu není možné nastavit události (Applicaton.OnEntry, .OnDoubleClick, .OnCalculate aj.). Excel 4 makra (ExecuteExcel4Macro) jsou rovněž odstaveny na druhou kolej.

Průšvihy v zabezpečení

Ve funkcích listu je možné uplatnit metodu SendKeys pro odesílání virtuálních stisků kláves.

Function TESTOVKA()
    'text následovaný stiskem ENTER
    SendKeys "Jde to!~"
    TESTOVKA = "Vida!"
End Function
Vlastní funkce - SendKeys

Vlastní funkce – SendKeys

Sekvence je odeslána až po vrácení hodnoty a ukončení editace buňky.

Function TESTOVKA()
    Dim strVzorec As String
    'vložení vzorec do buňky vpravo
    strVzorec = Application.Caller.FormulaLocal
    SendKeys "{UP}{TAB}'" & strVzorec & "~"
    TESTOVKA = "Vzorec vpravo!"
End Function
Vlastní funkce - SendKeys podruhé

Vlastní funkce – SendKeys podruhé

Projde i sebedestrukce: SendKeys „{UP}^-~“.

Šokem byla pro mě snadná cesta spuštění prakticky libovolné aplikace přes funkci Shell.

Function TESTOVKA()
    'spuštění aplikace (Poznámkový blok) bez její aktivace
    ID = Shell("Notepad", vbMinimizedNoFocus)
    TESTOVKA = "ID"
End Function

Excel neklade odpor ani při aktivaci nainstalovaného doplňku.

Function TESTOVKA()
    'spuštění doplňku
    AddIns("Analytické nástroje").Installed = True
    TESTOVKA = "Chi chi!"
End Function
Vlastní funkce - aktivace doplňku

Vlastní funkce – aktivace doplňku

Ve funkcích listu je do jisté míry přípustné používat API.

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpBuffer As String, nSize As Long) As Long

Function TESTOVKA()
    'výpis aktuálního uživatele Windows
    Dim strUzivatel As String
    strUzivatel = String(100, Chr$(0))
    GetUserName strUzivatel, 100
    strUzivatel = Left$(strUzivatel, InStr(strUzivatel, Chr$(0)) - 1)
    TESTOVKA = strUzivatel
End Function
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function TESTOVKA()
    'pozastavení kódu na 5 sekund
    Sleep 5000
    TESTOVKA = "API!"
End Function
Private Const SND_ASYNC = &H1
Private Const SND_NODEFAULT = &H2

Private Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Function TESTOVKA()
    'přehrání zvuku (asynchronně, nečeká na ukončení procedury)
    sndPlaySound "c:\Windows\Media\notify.wav", SND_ASYNC Or SND_NODEFAULT
    TESTOVKA = "Tutú!"
End Function

Ukázali jsme si, že vlastní funkce nejsou až takové puritánky, za jaké je učebnice Excelu vydávají. Dnešní cvičení berte ovšem za čistě experimentální. V řadě případů vám nikdo nezaručí, jak se Excel zachová poté, co mu hodíte na záda vykutálenou funkci. Problémy mohou nastat při přepočtu (viz také Application.Volatile), při volání událostí ze zásobníku atd.

Klikni a stahuj!