Pravomoci vlastních funkcí (UDF)

Úvodní stránka | Blog | Excel VBA | 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, …

    1
    2
    3
    4
    Function TESTOVKA()
        'dialog (MsgBox)
        TESTOVKA = MsgBox("Baf!")
    End Function
    Vlastní funkce - MsgBox
    Vlastní funkce – MsgBox
    1
    2
    3
    4
    Function TESTOVKA() As String
        'dialog s textovým polem (InputBox)
        TESTOVKA = Application.InputBox("Kdo jsi?", "Identifikace")
    End Function
    Vlastní funkce - InputBox
    Vlastní funkce – InputBox
    1
    2
    3
    4
    5
    6
    7
    8
    9
    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

    1
    2
    3
    4
    5
    6
    7
    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

    1
    2
    3
    4
    5
    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í

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    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

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

    Grafy

    1
    2
    3
    4
    5
    6
    7
    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.

    1
    2
    3
    4
    5
    6
    7
    8
    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.

    1
    2
    3
    4
    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.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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.

    1
    2
    3
    4
    5
    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.

    1
    2
    3
    4
    5
    6
    7
    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.

    1
    2
    3
    4
    5
    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.

    1
    2
    3
    4
    5
    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.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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
    1
    2
    3
    4
    5
    6
    7
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Function TESTOVKA()
        'pozastavení kódu na 5 sekund
        Sleep 5000
        TESTOVKA = "API!"
    End Function
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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.

    Uložit článek jako PDF