Přeskočit navigaci | Přeskočit na novinky

Nový videokurz na Seduo: Excel pro začátečníky

     

Jste zde: Úvodní stránka » excel » vba-funkce-vzorce » vlastni-funkce-excel-VBA

Vlastní funkce - Excel VBA


pokud vám nestačí 400 funkcí, které jsou součásti Microsoft Excelu - vytvořte si vlastní například na obsah kruhu :)

Doplněno 21.4.2013: Popis a nápověda pro vlastní funkce.

Co se v článku dozvíte

logo VBA

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í 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).

MS Excel 2010 - vlastní funkce

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:

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

MS Excel 2010 - VBA

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 MS Excel 2010 - VBA

Otestovat

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

=ObsahKruh(A1) =ObjemKvadru(A1;A2;A3) MS Excel 2010 - vlastní funkce

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)

Object Browser

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

Popis funkce

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.

Object Browser

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

MacroOptions(Macro, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile, ArgumentDescriptions)

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.

MS Excel 2010 - vlastní funkce

Včetně popisu argumentů

MS Excel 2010 - vlastní funkce

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í.

HelpNDoc

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 soubor ve formátu *.chm 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 soubor ve formátu *.xlsm 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: 26.01.2015 17:23

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



Komentáře


Radek

Přidáno: 19.04.13 12:15

Ahoj, chtěl bych se zeptat jak funkci doplnit o informace: K čemu funkce slouží, a napovědu k použitým argumentům. Díky moc za odpověď.

Pavel Lasák

Přidáno: 19.04.13 17:48

To Radek: Uložit komentář k funkci. V Object Browser pravým tlačítkem na funkci vybereme Properties... vyplnit.

Pavel Lasák

Přidáno: 20.04.13 09:36

To Radek: Tak jsem doplnil info do článku.

Petr Pecháček

Přidáno: 21.04.13 17:29

Pro doplnění: Argumenty funkce (ArgumentDescriptions) je možné popisovat až v Excelu 2010, dřívější verze toto neumí.

Petr Pecháček

Přidáno: 21.04.13 17:35

A ještě drobnůstka: Hodnotu Pí lze bez použití funkce listu vyjádřit jako = 4 * Atn(1), kde Atn je standardní funkce VBA, resp. VB.

gabo

Přidáno: 06.06.13 12:13

Následující fce nefunguje v ex 2002. Nevíte proč? Function Slovy(Cis As Double) As String Dim StrCis As String Dim LenCis As Byte, Rad As Integer, Ofs As Byte Dim Pol As Byte, pom As String, pom1 As String, pom2 As String Dim Jedn As Variant, Des1 As Variant, Des As Variant, Sta As Variant Dim JednTM As Variant, Tis As Variant, Mil As Variant ' If IsEmpty(Cis) Then End ' Jedn = Array("", "jedna", "dvě", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět") Des1 = Array("deset", "jedenáct", "dvanáct", "třináct", "čtrnáct", "patnáct", "šestnáct", "sedmnáct", "osmnáct", "devatenáct") Des = Array("", "", "dvacet", "třicet", "čtyřicet", "padesát", "šedesát", "sedmdesát", "osmdesát", "devadesát") Sta = Array("", "jednosto", "dvěsta", "třista", "čtyřista", "pětset", "šestset", "sedmset", "osmset", "devětset") Tis = Array("tisíc", "tisíc", "tisíce", "tisíce", "tisíce", "tisíc", "tisíc", "tisíc", "tisíc", "tisíc") JednTM = Array("", "jeden", "dva", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět") Mil = Array("milionů", "milion", "miliony", "miliony", "miliony", "milionů", "milionů", "milionů", "milionů", "milionů") ' ' StrCis = CStr(Format(Cis, "0.00")) Pol = InStr(StrCis, ",") - 1 ' poloha radu jednotek v cisle If Pol > 9 Then Slovy = ">999 999 999": Exit Function Rad = 0 ' rad cislice v cisle Slovy = "" Do pom = Mid(StrCis, Pol, 1) If Pol > 1 Then pom1 = Mid(StrCis, Pol - 1, 1) Else pom1 = "0" End If ' Select Case Rad Case 0 pom2 = IIf(pom1 <> 1, Jedn(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2) Case 1 pom2 = Des(pom): Ofs = 1 Case 2 pom2 = Sta(pom): Ofs = 1 Case 3 pom2 = IIf(pom1 <> 1, JednTM(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2) If Pol > 3 Then ' kdyz zustavaji jeste >3 cislice If Mid(StrCis, Pol - 2, 3) <> "000" Then pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ") ' a jsou i tisice -> vlozeni slova tisic Else Ofs = 3 ' preskoci na rad 6 - miliony End If Else ' kdyz zustava jeste <3 cislice -> vlozeni slova tisic pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ") End If Case 4 pom2 = Des(pom): Ofs = 1 Case 5 pom2 = Sta(pom): Ofs = 1 Case 6 pom2 = IIf(pom1 <> 1, JednTM(pom) & Mil(pom), Des1(pom) & "milionů"): Ofs = IIf(pom1 <> 1, 1, 2) Case 7 pom2 = Des(pom): Ofs = 1 Case 8 pom2 = Sta(pom): Ofs = 1 End Select ' Slovy = pom2 & Slovy Pol = Pol - Ofs: Rad = Rad + Ofs ' Loop While Pol > 0 Slovy = Trim(Slovy) ' & " " & Right(StrCis, 2) ' pridani destinne casti End Function

Jojo

Přidáno: 22.07.14 09:30

To Gabo: Exc2003Sub Test() MsgBox Slovy(123456789.5) End Sub Function Slovy(Cis As Double) As String 'Suma(Číslo) -> slovom Dim StrCis As String Dim LenCis As Byte, Rad As Integer, Ofs As Byte Dim Pol As Byte, pom As String, pom1 As String, pom2 As String Dim Jedn As Variant, Des1 As Variant, Des As Variant, Sta As Variant Dim JednTM As Variant, Tis As Variant, Mil As Variant If IsEmpty(Cis) Then End Jedn = Array("", "jedna", "dvě", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět") Des1 = Array("deset", "jedenáct", "dvanáct", "třináct", "čtrnáct", "patnáct", "šestnáct", "sedmnáct", "osmnáct", "devatenáct") Des = Array("", "", "dvacet", "třicet", "čtyřicet", "padesát", "šedesát", "sedmdesát", "osmdesát", "devadesát") Sta = Array("", "jednosto", "dvěsta", "třista", "čtyřista", "pětset", "šestset", "sedmset", "osmset", "devětset") Tis = Array("tisíc", "tisíc", "tisíce", "tisíce", "tisíce", "tisíc", "tisíc", "tisíc", "tisíc", "tisíc") JednTM = Array("", "jeden", "dva", "tři", "čtyři", "pět", "šest", "sedm", "osm", "devět") Mil = Array("milionů", "milion", "miliony", "miliony", "miliony", "milionů", "milionů", "milionů", "milionů", "milionů") ' StrCis = CStr(Format(Cis, "0.00")) Pol = InStr(StrCis, ",") - 1 ' poloha radu jednotek v cisle If Pol > 9 Then Slovy = ">999 999 999": Exit Function Rad = 0 ' rad cislice v cisle Slovy = "" Do pom = Mid(StrCis, Pol, 1) If Pol > 1 Then pom1 = Mid(StrCis, Pol - 1, 1) Else pom1 = "0" End If Select Case Rad Case 0 pom2 = IIf(pom1 <> 1, Jedn(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2) Case 1 pom2 = Des(pom): Ofs = 1 Case 2 pom2 = Sta(pom): Ofs = 1 Case 3 pom2 = IIf(pom1 <> 1, JednTM(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2) If Pol > 3 Then ' kdyz zustavaji jeste >3 cislice If Mid(StrCis, Pol - 2, 3) <> "000" Then pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ") ' a jsou i tisice -> vlozeni slova tisic Else: Ofs = 3 ' preskoci na rad 6 - miliony End If Else ' kdyz zustava jeste <3 cislice -> vlozeni slova tisic pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), " tisíc ") End If Case 4 pom2 = Des(pom): Ofs = 1 Case 5 pom2 = Sta(pom): Ofs = 1 Case 6 pom2 = IIf(pom1 <> 1, JednTM(pom) & Mil(pom), Des1(pom) & "milionů"): Ofs = IIf(pom1 <> 1, 1, 2) Case 7 pom2 = Des(pom): Ofs = 1 Case 8 pom2 = Sta(pom): Ofs = 1 End Select Slovy = pom2 & Slovy Pol = Pol - Ofs: Rad = Rad + Ofs Loop While Pol > 0 Slovy = Trim(Slovy) & " " & Right(StrCis, 2) ' pridani destinne casti End Function

martin

Přidáno: 17.12.14 08:47

Zdravím Vás, začínám s VBA a mám potíže s matematiskou funkcí logaritmu "log()". Když zadám log(10) ve VBA spočte se mi 2,3..., což odpovídá funkci ln(10). Nevím čím to může být, pokud by někdo věděl, moc prosím o radu. PS.: zvláštní je, že v klasickém zadání vzorců v excelu, to je v pořádku log(10) = 1 a ln(10)=2,3..

Lord M

Přidáno: 17.12.14 09:25

definul dekadický logaritmus pomocí přirozeného : log10(x)=ln(x)/ln(10) HOTOVO ;-)

Jakub

Přidáno: 23.01.15 17:29

Dobrý den, při pokusu vložit nápovědu k funkci mne VBA zastaví v místě := . Resp HelpFile:= Píše mi to tu - expected expression. Kde je chyba v proměnné? HelpFile:=ThisWorkbook.Path & "\super.chm", _ HelpContextID:=200

Jakub

Přidáno: 23.01.15 18:15

Tak už jsem na to přišel. Doplnil jsem kod s nápovědou, ale zapomněl jsem ukončit předešlý příkaz ArgumentDescriptions:=Argumenty Správně ArgumentDescriptions:=Argumenty, _ A zajímá mě jedna věc. Lze nějakým způsobem docílit toho, aby mi excel při vkládání funkce zobrazoval nápovědu? Podobně jako když napíšu SUMA( - zobrazí se poté číslo1;číslo2...

Frantino

Přidáno: 25.01.15 01:26

Zdravím, potřeboval bych poradit. V sešitu mám několik listů a potřebuji ve VBA napsat podmínku pro přechod na daný list při stisku tlačítka. Přesněji, rozeznávám plátce DPH a hodnotu. V buňce L6 se zaznamenává ANO/NE pokud je plátce a v buňce L8 zda zapaltil či ne. Pokud zvololím jednu z variant (celkem jsou 4) potřebuji se po stisku tlačítka dostat na list, který s těmito údaji pracuje. Zkusil jsemnapsat zatim toto: Private Sub CommandButton21_Click() If Range("L6") = "NE" Then Sheets("NC pro plátce DPH NEM").Select Else Sheets("NC pro neplátce DPH NEM").Selec End If End Sub Ale nefunguje to. Poradíte prosím. Děkuji

Pavel Lasák

Přidáno: 25.01.15 09:47

To Frantino: nenačte se hodnota z buňky, správně je např ... Range("L6").Text = "NE" ...více o načítání buněk jsme popsal: Načítaní hodnot z buněk rozdíl mezi Text, Value, Value2 upravte kód na Private Sub CommandButton21_Click() If Range("L6").Text = "NE" Then Sheets("NC pro plátce DPH NEM").Select Else Sheets("NC pro neplátce DPH NEM").Select End If End Sub

Milan

Přidáno: 11.03.15 12:27

Dobrý den, řeším ten samý problém jako Jakub: Lze nějakým způsobem docílit toho, aby mi excel při vkládání funkce zobrazoval nápovědu? Podobně jako když napíšu SUMA( - zobrazí se poté číslo1;číslo2... Jde mi hlavně o to, aby uživatel při použití vlastní funkce viděl, kolik má zadat parametrů. Děkuji za odpověď

Hynek

Přidáno: 11.03.15 16:28

Dobrý den, potřeboval bych funkci, která by do buňky vrátila hodnotu - název souboru(toho konkrétního excelu) a pak další která by vložila název adresáře ve kterém je právě umístěn.

Silvie

Přidáno: 03.04.15 13:52

Dobrý den, potřebovala bych poradit: pomocí VBA projectu jsem vložila následující funkce pro sčítání buněk označených určitou barvou Function ColorSummer(RangeToSum As Range, ColorToSum As Integer) As Double 'scita obsah bunek z rozsahu RangeToSum podbarvenych barvou ColorToSum Dim tmp As Double tmp = 0 For Each cell In RangeToSum If cell.Interior.ColorIndex = ColorToSum Then If IsNumeric(cell.Value) Then tmp = tmp + cell.Value End If Next ColorSummer = tmp End Function Function ColorPicker(CellToPickColorFrom As Range) As Integer 'Pokud nechceme hledat cislo barvy vydobneme si je z konkretni bunky ColorPicker = CellToPickColorFrom.Interior.ColorIndex End Function Jak bych měla změnit toto zadání pokud nechci součet buněk, ale počet buněk označených určitou barvou... Děkuji za odpověď


Přidání nového komentáře

Poznámka: Z časových důvodu není v mých sílách zodpovědět všechny dotazy. Děkuji za pochopení.




(vlož číslo padesátpět)

Z bezpečnostních důvodu dočasně nemůžete použít HTML značky, kromě <code> a </code> (pro vkládání VBA kódu). Děkuji za pochopení.
Děkuji, za Vaše komentáře.

Za obsah komentářů neodpovídám, jelikož jsou komentáře publikovány ihned po jejich napsání čtenářem. Toto nemohu nijak ovlivnit. Přesto si vyhrazuji možnost jakýkoli neslušný komentář smazat bez udání důvodu. V komentářích si tykáme. ;-)






Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2016 | 21588

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.