Vytváření vlastních funkcí v aplikaci Excel

Vlastní funkce, jako jsou makra, používají programovací jazyk Visual Basic for Application (VBA). Liší se od maker dvěma významnými způsoby. Nejprve používají Function procedury místo Sub procedur. To znamená, že začínají příkazem Function místo příkazu Sub a končí End Function místo End Sub. Za druhé provádějí výpočty místo provádění akcí. Některé druhy příkazů, například příkazy, které vyberou a formátují rozsahy, se z vlastních funkcí vyloučí. V tomto článku se dozvíte, jak vytvářet a používat vlastní funkce. Pokud chcete vytvářet funkce a makra, pracujete s Visual Basic Editor (VBE), který se otevře v novém okně odděleném od Excelu.

Předpokládejme, že vaše společnost nabízí 10% slevu na prodej produktu za předpokladu, že objednávka je pro více než 100 jednotek. V následujících odstavcích předvedeme funkci pro výpočet této slevy.

Následující příklad ukazuje formulář objednávky se seznamem jednotlivých položek, množství, ceny, slevy (pokud existuje) a výsledné rozšířené ceny.

Pokud chcete v tomto sešitu vytvořit vlastní funkci DISCOUNT, postupujte takto:

1. Stisknutím kláves Alt+F11 otevřete Visual Basic Editor (na Macu stiskněte FN+ALT+F11) a potom klikněte na Vložit modul > Modul. Na pravé straně Visual Basic Editor se zobrazí nové okno modulu.

2. Zkopírujte a vložte do nového modulu následující kód.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Teď jste připraveni použít novou funkci DISCOUNT. Zavřete Visual Basic Editor, vyberte buňku G7 a zadejte následující příkaz:

=DISCOUNT(D7;E7)

Excel vypočítá 10% slevu na 200 jednotek za 47,50 USD za jednotku a vrátí 950,00 USD.

Na prvním řádku kódu jazyka VBA jste uvedli, že funkce DISCOUNT(quantity, price) vyžaduje dva argumenty, množství a cenu. Při volání funkce v buňce listu musíte tyto dva argumenty zahrnout. Ve vzorci =DISCOUNT(D7;E7) je D7 argument množství a E7 je argument ceny . Teď můžete vzorec DISCOUNT zkopírovat do G8:G13, abyste získali výsledky uvedené níže.

Pojďme se podívat, jak Excel tuto proceduru funkce interpretuje. Když stisknete Enter, Excel vyhledá název DISCOUNT v aktuálním sešitu a zjistí, že se jedná o vlastní funkci v modulu VBA. Názvy argumentů uzavřené v závorkách, množství a cena jsou zástupné symboly pro hodnoty, na kterých je výpočet slevy založen.

Příkaz If v následujícím bloku kódu prozkoumá množství argumentu a určuje, jestli je počet prodaných položek větší nebo roven 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Pokud je počet prodaných položek větší nebo roven 100, jazyk VBA provede následující příkaz, který vynásobí množství hodnotou cena a pak vynásobí výsledek hodnotou 0,1:

Discount = quantity * price * 0.1

Výsledek se uloží jako proměnná Discount. Příkaz jazyka VBA, který ukládá hodnotu do proměnné, se nazývá přiřazení příkazu, protože vyhodnocuje výraz na pravé straně symbolu rovná se a přiřadí výsledek k názvu proměnné vlevo. Protože proměnná Discount má stejný název jako procedura funkce, vrátí se hodnota uložená v proměnné do vzorce listu, který volal funkci DISCOUNT.

Pokud je množství menší než 100, jazyk VBA provede následující příkaz:

Discount = 0

Nakonec následující příkaz zaokrouhlí hodnotu přiřazenou proměnné Discount na dvě desetinná místa:

Discount = Application.Round(Discount, 2)

Jazyk VBA nemá žádnou funkci ROUND, ale Excel ano. Pokud tedy chcete v tomto příkazu použít funkci ZAOKROUHLIT, řeknete jazyku VBA, aby hledal metodu Zaokrouhlit (funkci) v objektu Aplikace (Excel). Uděláte to tak, že před slovo Zaokrouhlit přidáte slovo Application. Tuto syntaxi použijte vždy, když potřebujete získat přístup k excelové funkci z modulu VBA.

Vlastní funkce musí začínat příkazem Function a končit příkazem End Function. Kromě názvu funkce obvykle příkaz Function určuje jeden nebo více argumentů. Můžete ale vytvořit funkci bez argumentů. Excel obsahuje několik předdefinovaných funkcí—například RAND a NOW—, které nepoužívají argumenty.

Za příkazem Function obsahuje procedura funkce jeden nebo více příkazů jazyka VBA, které provádějí rozhodnutí a provádějí výpočty pomocí argumentů předaných funkci. Nakonec někde v proceduře funkce musíte zahrnout příkaz, který proměnné přiřadí hodnotu se stejným názvem jako funkce. Tato hodnota se vrátí do vzorce, který funkci volá.

Počet klíčových slov jazyka VBA, která můžete použít ve vlastních funkcích, je menší než počet, který můžete použít v makrech. Vlastní funkce nemohou dělat nic jiného než vracet hodnotu vzorci v listu nebo výrazu použitému v jiném makru nebo funkci jazyka VBA. Vlastní funkce například nemůžou měnit velikost oken, upravovat vzorec v buňce ani měnit možnosti písma, barvy nebo vzorku textu v buňce. Pokud do procedury funkce zahrnete „kód akce“ tohoto typu, vrátí funkce #VALUE! .

Jedna akce, kterou může procedura funkce provést (kromě provádění výpočtů), je zobrazení dialogového okna. Příkaz InputBox ve vlastní funkci můžete použít jako způsob získání vstupu od uživatele, který funkci spouští. Jako způsob předávání informací uživateli můžete použít příkaz MsgBox. Můžete také použít vlastní dialogová okna nebo UserForms, ale to je téma nad rámec tohoto úvodu.

Dokonce i jednoduchá makra a vlastní funkce můžou být obtížně čitelné. Srozumitelnější je můžete učinit zadáním vysvětlujícího textu ve formě komentářů. Komentáře přidáte před vysvětlující text apostrofem. Například následující příklad ukazuje funkci DISCOUNT s komentáři. Přidání podobných komentářů vám nebo ostatním usnadní údržbu kódu jazyka VBA v průběhu času. Pokud budete potřebovat v budoucnu provést změnu kódu, bude pro vás snazší pochopit, co jste původně udělali.

Apostrof říká Excelu, aby ignorovala všechno napravo na stejném řádku, takže můžete vytvářet komentáře buď na řádcích samostatně, nebo na pravé straně řádků obsahujících kód jazyka VBA. Relativně dlouhý blok kódu můžete začít komentářem, který vysvětluje jeho celkový účel, a pak pomocí vložených komentářů zdokumentovat jednotlivé příkazy.

Dalším způsobem, jak zdokumentovat makra a vlastní funkce, je dát jim popisné názvy. Například místo pojmenování makra Popisky, můžete makro pojmenovat MonthLabels, abyste přesněji popsali účel, kterému makro slouží. Použití popisných názvů pro makra a vlastní funkce je zvláště užitečné, když jste vytvořili mnoho procedur, zejména pokud vytváříte procedury, které mají podobné, ale ne totožné účely.

Jak dokumentujete svá makra a vlastní funkce, je věcí osobních preferencí. Důležité je přijmout nějakou metodu dokumentace a důsledně ji používat.

Pokud chcete použít vlastní funkci, musí být sešit obsahující modul, ve kterém jste funkci vytvořili, otevřený. Pokud tento sešit není otevřený, získáte #NAME? při pokusu o použití funkce došlo k chybě. Pokud odkazujete na funkci v jiném sešitu, musíte před název funkce zadat název sešitu, ve kterém se funkce nachází. Pokud například vytvoříte funkci s názvem DISCOUNT v sešitu s názvem Personal.xlsb a zavoláte tuto funkci z jiného sešitu, musíte zadat =personal.xlsb!discount(), nikoli jednoduše =discount().

Výběrem vlastních funkcí v dialogovém okně Vložit funkci si můžete ušetřit některé úhozy (a případné chyby při psaní). Vaše vlastní funkce se zobrazí v kategorii Definované uživatelem:

Jednodušší způsob, jak svoje vlastní funkce kdykoli zpřístupnit, je uložit je do samostatného sešitu a pak ho uložit jako doplněk. Doplněk pak můžete zpřístupnit při každém spuštění aplikace Excel. Tady je postup:

Po provedení těchto kroků budou vaše vlastní funkce k dispozici při každém spuštění Excelu. Pokud chcete přidat do knihovny funkcí, vraťte se do Visual Basic Editor. Pokud se podíváte do Visual Basic Editor Project Exploreru pod nadpisem VBAProject, zobrazí se modul pojmenovaný podle souboru doplňku. Váš doplněk bude mít příponu .xlam.

Dvojité kliknutí na tento modul v Průzkumníku projektů způsobí, že Visual Basic Editor zobrazí kód vaší funkce. Pokud chcete přidat novou funkci, umístěte kurzor za příkaz End Function, který ukončí poslední funkci v okně Kódu, a začněte psát. Tímto způsobem můžete vytvořit tolik funkcí, kolik potřebujete, a budou vždy dostupné v kategorii Definované uživatelem v dialogovém okně Vložit funkci.

Tento obsah původně napsali Mark Dodge a Craig Stinson jako součást jejich knihy Microsoft Office Excelu 2007 Inside Out. Od té doby se aktualizovala tak, aby platila i pro novější verze Excelu.