Tweet

Vlastní funkce - Excel VBA

Co se v článku dozvíte

Jak na vlastní funkce od teoretických základu, přes vytvoření funkce, její popis, doplnění o nápovědu. Opět pro přehlednost je článek rozdělen na kapitoly:

• Úvodem do tvorby vlastních funkcí
• Praktická ukázka - zápis funkce klasicky
• Jak zobrazit pás karet - VÝVOJÁŘ
• Vytvoření funkce ve VBA
• Podrobněji o vlastních funkcích
• Popis - nápověda vlastní funkce Object Browser
• Popis - nápověda vlastní funkce VBA MacroOptions
• Jak na vlastní nápověda (Help)
• Další možnosti - v přípravě
• Ke stažení

Úvodem do tvorby vlastních funkcí v Microsoft Excelu

Při pokročilejší práci v Excelu se vám stane, že potřebujete speciální funkcí, která se v Excelu nenachází. Mnohdy jde vytvořit kombinací z několika standartních funkcí. Pokud nechce tvořit složitou dlouhou funkci (která je na první pohled nepřehledná), můžete si vytvořit funkcí vlastní.

Vlastní funkce

Na pozadí to bude stále složená funkce, ale na popředí bude vypadat jednoduše. Navíc si můžete zvolit její pojmenování (ať se Vám dobře hledá).

Poznámka: Jedná se o odpověď na dotazy z emailu, týkající se tvorby funkcí. Z dotazu to vypadá na školní úkol ;). Ono tvořit funkci na výpočet obsahu kruhu, nebo objem kvádru není úplně praktické, ale na druhou stranu se na těchto příkladech lehce demonstruje tvorba vlastních funkcí.

Praktická ukázka - zápis funkce

Než dlouze povídat, ukáží použití na jednoduchém příkladu. Potřebujeme vypočíst plochu kruhu pomocí vlastní funkce, kdy zadáme jen průměr. Jako druhý příklad vypočteme objem kvádru, kdy jsou zadány tři strany (výška, šířka, hloubka).

Klasické řešení

V klasickém řešení napíšeme do buňky vzorec:

=PI()*A1^2/4

Pro objem krychle vzorec:

=A1*A2*A3

Poznámka - takto jednoduchý vzorec je zvolen záměrně. Klidně si udělejte funkci na výpočet plochy čtverce :), nebo jakýkoliv složitější výpočet (např. výpočet momentu setrvačnosti, transfigurace hvězdy na trojúhelník, atd.).

Jak zobrazit pás karet - VÝVOJÁŘ

Po standardní instalaci MS Excelu, není karta vývojář k dispozici. Proto si ji musíte zobrazit. Trvalé zobrazení karty vývojář je sepsáno v článku:

• Zobrazení karty vývojář Excel 2007
• Zobrazení karty vývojář Excel 2010

Vytvoření funkce ve VBA

Na kartě vývojář v sekci Kód ikona Visual Basic nebo klávesová zkratka Alt + F11.

Vložit modul

V okně Visual Basic for Applications přidáte přes menu modul: Insert - Module. Do okna Project - VBAProject se přidá Module1

Zapsat vlastní funkci

Module rozkliknout a přidat vaše funkce:

Function ObsahKruh(Prumer) Pi = Application.WorksheetFunction.Pi() ObsahKruh = Pi * Prumer ^ 2 / 4 End Function Function ObjemKvadru(Delka, Sirka, Hloubka) ObjemKvadru = Delka * Sirka * Hloubka End Function

Otestovat

Teď jen otestujete, zda funguje v Excelu. Stačí zadat příslušné názvy funkcí:

=ObsahKruh(A1) =ObjemKvadru(A1;A2;A3)

Podrobněji o vlastních funkcích

Jednoduchou funkci umíte vytvořit. Vlastní funkce v Excelu mohou být funkce trojího druhu:

• nepotřebující parametr
• povinný parametr
• volitelný parametr

Funkce nepotřebující parametr

Function MojeKonstanta()

Funkce s povinným parametrem

Function ObsahKrychle(Cislo)

Funkce s volitelným parametrem

Function MojeNahodneCislo(Optional Cislo)

Poznámka: Je jasné, že může jít i o kombinaci povinných a volitelných parametrů.

Popis - nápověda vlastní funkce - Object Browser

Budete-li chtít k funkci doplnit popis. Jednodušší možnost je následující (osobně doporučuji využít následující pomocí VBA kódu).

Ve VBA Alt + F11 zobrazíte Object Browser přes menu view - Object Browser (F2)

V Members of naleznete váš název funkce. Při volbě Propertes doplnit popis funkce a OK

Poznámka: Pokud se Vám výpis všech proměnných, zdá moc dlouhý tak v Object Browser zvolte VBAProject a vidíte své funkce.

Popis - nápověda vlastní funkce - VBA MacroOptions

Pomocí VBA kódu doplníte funkci popis, odkazy na nápovědu. K tomu nám slouží MacroOptions

Application.MacroOptions - Syntaxe

Poznámka: Všechny proměnné jsou volitelné, datový typ Variant (kromě ArgumentDescriptions což je Array).

• Macro - Jméno definované funkce (UDF)
• Description - Popis makra
• HasMenu - Tento argument je ignorován
• MenuText - Tento argument je ignorován
• HasShortcutKey - Log hodnota True/False
  • TRUE - Pravda - makru přiřadí klávesovou zkratku
  • FALSE - Nepravda - výchozí, nebere se v potaz klávesová zkratka i když je nastavena
• ShortcutKey - Nastavení klávesové zkratky. Pokud HasShortcutKey = TRUE je tato hodnota povinná - jinak je argument ignorován.
• Category - určuje kategorií do které bude funkce zařazena - viz dále.
• StatusBar - stavový řádek pro funkci (makro)
• HelpContextID - celé číslo, které určuje ID tématu z nápovědy přiřazené k vlastní funkci (makru).
• HelpFile - Název souboru nápovědy, která obsahuje téma (ID) definované v HelpContextID.
• ArgumentDescriptions -

Kategorie funkcí

Kategorie funkcí jak jsou k dispozici v Excelu:

• 1 - Finanční - Financial
• 2 - Datum a čas - Date & Time -
• 3 - Matematické - Math & Trig
• 4 - Statistické -Statistical
• 5 - Vyhledávací a referenční - Lookup & Reference
• 6 - Databázové - Database
• 7 - Textové - Text
• 8 - Logické Logical
• 9 - Informační - Information
• 10 - - Commands
• 11 - - Customizing
• 12 - - Macro Control
• 13 - - DDE/External
• 14 - Uživatelem definované - User Defined
• 15 - První uživatelská kategorie - First custom category
• .. - .... - .....
• 32 Osmnáctá uživatelská kategorie - Eighteenth custom category

VBA kód pro popis a nastavení nápovědy funkce

Máme například takovouto jednoduchou funkci:

Function OBJEMKVADRU(Delka, Sirka, Hloubka) ObjemKvadru = Delka * Sirka * Hloubka End Function

Kód, který stačí spustit jednou, pak již nemusí být součásti sešitu ;)

Sub PopisNasiVlastniFunkce() Dim FunkceJmeno As String Dim FunkcePopis As String Dim Kategorie As String Dim Argumenty(1 To 3) As String FunkceJmeno = "OBJEMKVADRU" FunkcePopis = "Tato naše vlastní funkce vratí objem kvádru." Kategorie = 14 'Uživatelem definovaná funkce Argumenty(1) = "Zadej stranu a - délka" Argumenty(2) = "Zadej stranu b - šířka" Argumenty(3) = "Zadej stranu c - hloubka" Application.MacroOptions _ Macro:=FunkceJmeno, _ Description:=FunkcePopis, _ Category:=Kategorie, _ ArgumentDescriptions:=Argumenty End Sub

Výsledek

Pokud proběhlo správně ve vlastních funkcích naleznete vaši funkci OBJEMKVADRU.

Včetně popisu argumentů

Nápověda

Jak doplnit vlastní funkci o odkaz do vlastní nápovědy? viz další kapitola.

Jak na vlastní Help

Jak si pro vlastní funkci napsat nápovědu (help).

Software pro její tvorbu

Osobně používám freeware: HelpNDoc v kterém vytvořím vlastní nápovědu (*.chm). Sice se do každé stránky přidá reklamní link, což pro ukázkové a demnostrační příklad nevadí.

Poznámka: Máte možnost je vytvořit i starší *.hlp soubor nápovědy.

Doplnění VBA kódu a nápovědu

Výše uvedený kód je potřeba doplnit o odkaz na soubor nápovědy a ID konkrétní kapitoly (článku) z nápovědy.

HelpFile:=ThisWorkbook.Path & "\vlastni-funkce.chm", _ HelpContextID:=200

Nápověda ke stažení

Ukázková nápověda ke stažení.

Soubor Vlastní funkce - nápověda ke stažení zdarma.

Další možnosti

Ukázky možných funkcí

Function CestakSouboru() CestakSouboru = ThisWorkbook.Path End Function

V případě dotazů v komentářích se pokusím článek doplnit.

Napřílad:

• Vlastní maticové funkce

Dle dotazů v komentářích se pokusím článek rozšířit (doplnit).

Ke stažení

Soubor Vlastní funkce Microsoft Excelu ke stažení zdarma. Soubor využívá makra.

Závěrem

Používáte vlastní funkce? Máte nějakou zajímavou funkci o kterou se rádi podělíte? Nebo tvoříte raději kompilace z několika standartních funkcí přímo v listech Excelu?

Doplněno 20.4.2013, použita nápověda od Microsoftu

Článek byl aktualizován: 19.09.2020 11:06

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře