Výuka funkcí Excel VBA: Návrat, Volání, Příklady

Co je funkce?

Funkce je část kódu, která provádí konkrétní úkol a vrací výsledek. Funkce se většinou používají k provádění opakujících se úkolů, jako je formátování dat pro výstup, provádění výpočtů atd.

Předpokládejme, že se vyvíjíteping program, který vypočítává úrok z půjčky. Můžete vytvořit funkci, která přijímá výši půjčky a dobu splatnosti. Funkce pak může použít výši půjčky a dobu splatnosti k výpočtu úroku a vrácení hodnoty.

Proč používat funkce

Výhody používání funkcí jsou stejné jako u podprogramů: rozdělují dlouhý program na snadno zvládnutelné části, lze je znovu použít odkudkoli v projektu a popisný název dokumentuje, co kód dělá. Výukový program k podprogramům VBA v Excelu plně pokrývá tyto výhody.

Pravidla pojmenovávání funkcí

Pravidla pro pojmenování jsou také shodná s pravidly pro podprogramy. Název funkce nesmí obsahovat mezeru, musí začínat písmenem nebo podtržítkem a nesmí být rezervovaným názvem. VBA klíčové slovo, jako například Funkce, Private nebo End.

Syntaxe jazyka VBA pro deklaraci funkce

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

ZDE v syntaxi,

Jak vrátit hodnotu a nastavit datový typ funkce

Funkce má jeden úkol, který podprogram neplní: vrací hodnotu zpět. Tuto hodnotu řídí dva detaily a oba lze snadno přehlédnout.

Prvním je přiřazení. VBA nemá příkaz Return. Místo toho přiřadíte výsledek k vlastnímu názvu funkce, a proto je tento řádek zobrazen myFunction = arg1 + arg2Pokud se toto přiřazení nikdy nespustí, funkce tiše vrátí prázdnou hodnotu, místo aby vyvolala chybu, takže ji musí nastavit každá větev kódu.

Druhým je návratový typ. Výše ​​uvedená deklarace končí uzavírací závorkou, takže funkce vrací typ typu Variant. Přidání klauzule As za závorky tento typ opraví, což je rychlejší, spotřebovává méně paměti a umožňuje kompilátoru zachytit neshodu.

💡 Tip: Použijte funkci Exit pro předčasný odchod po nastavení návratové hodnoty, stejným způsobem jako Exit Sub opouští podprogram.

Funkce ukázaná na příkladu:

Funkce jsou velmi podobné podprogramu. Hlavní rozdíl mezi podprogramem a funkcí je v tom, že funkce vrací hodnotu, když je volána. Zatímco podprogram nevrací hodnotu, když je volán. Řekněme, že chcete sečíst dvě čísla. Můžete vytvořit funkci, která přijímá dvě čísla a vrací součet čísel.

1. Vytvořte uživatelské rozhraní
2. Přidejte funkci
3. Napište kód pro příkazové tlačítko
4. Vyzkoušejte kód

Krok 1) Uživatelské rozhraní

Přidejte příkazové tlačítko do listu, jak je znázorněno níže

Nastavte následující vlastnosti CommandButton1 na následující hodnotu.

Vaše rozhraní by nyní mělo vypadat následovně

Krok 2) Kód funkce.

1. Stisknutím Alt + F11 otevřete okno kódu
2. Přidejte následující kód

Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

ZDE v kódu,

Krok 3) Napište Code která volá funkci

1. Klikněte pravým tlačítkem myši na tlačítko PřidatNumbers příkazové tlačítko
2. Vyberte zobrazení Code
3. Přidejte následující kód

Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

ZDE v kódu,

Krok 4) Spusťte program, získáte následující výsledky

Stáhněte si Excel obsahující výše uvedený kód

Stáhněte si výše uvedený Excel Code

Tlačítko výše volá funkci z kódu VBA. Funkci lze také volat ze samotného listu, bez použití tlačítka.

Jak použít funkci VBA v buňce pracovního listu

Funkci napsanou ve VBA lze zadat do buňky přesně jako SUM nebo VLOOKUP. Excel to nazývá uživatelsky definovaná funkce neboli UDF a to je důvod, proč se mnoho lidí učí funkce dříve než podprogramy. Musí být splněny tři podmínky.

• Umístěte jej do standardního modulu: Vložit, Modul v editoru. Funkce uložená za listem nebo v ThisWorkbooku není viditelná pro řádek vzorců.
• Prohlásit to za veřejné: Výše uvedený příklad používá možnost Private (Soukromé), která ji skryje před Excelem. Výchozí nastavení je Public (Veřejné), takže stačí pouhé odstranění klíčového slova.
• Vrátit hodnotu, nic neměnit: Funkce UDF nemůže formátovat buňky, mazat řádky ani zapisovat do jiné buňky. Excel tyto akce blokuje a buňka zobrazuje chybu #HODNOTA!.

Níže uvedená funkce převádí teplotu a lze ji použít kdekoli na listu.

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

Uložte sešit jako soubor XLSM s podporou maker a poté zadejte =CelsiusToF(A1) do libovolné buňky. Výsledek se aktualizuje vždy, když se změní buňka A1, a název se zobrazí v seznamu automatického dokončování vzorců v kategorii Definováno uživatelem. Protože sešit nyní obsahuje makra, musí každý, kdo jej otevře, povolit obsah, než vzorec vrátí hodnotu, nikoli #NÁZEV?.

Běžné chyby funkcí VBA a jak je opravit

Čtyři problémy vysvětlují většinu funkcí, které se kompilují, ale vracejí nesprávnou odpověď.

• Funkce vrací hodnotu Prázdno nebo 0: Výsledek nebyl nikdy přiřazen názvu funkce nebo jedna větev příkazu If přiřazení přeskakuje. Nastavte návratovou hodnotu pro každou cestu.
• #NÁZEV? v buňce listu: Funkce je soukromá, nachází se v modulu listu namísto standardního modulu nebo byl sešit uložen bez povolených maker.
• Přetečení s celočíselnými argumenty: V příkladu se používá As Integer, který se zastaví na čísle 32 767. Pro všechna reálná data změňte oba parametry i návratový typ na Long.
• Změněný argument překvapí volajícího: Vynechání ByVal způsobí, že VBA předá samotnou proměnnou, takže funkce může změnit hodnotu volajícího. Pokud tento efekt nepožadujete, pište ByVal.

Nejčastější dotazy