Vytváranie vlastných funkcií v Exceli

Hoci Excel ponúka množstvo vstavaných funkcií hárka, môže sa stať, že nemá funkciu pre typ výpočtu, ktorý potrebujete vykonať. Návrhári Excelu nemohli vopred odhadnúť potreby každého používateľa. Práve preto Excel poskytuje možnosť vytvárať vlastné funkcie. Viac sa o nich dozviete v tomto článku.

Vlastné funkcie, podobne ako makrá, využívajú programovací jazyk Visual Basic for Applications (VBA). Od makier sa odlišujú v dvoch zásadných vlastnostiach. V prvom rade využívajú procedúry Function namiesto procedúr Sub. Znamená to, že sa začínajú príkazom Function namiesto príkazu Sub a končia sa príkazom End Function namiesto príkazu End Sub. Druhou odlišnosťou je, že namiesto akcií vykonávajú výpočty. Niektoré druhy príkazov, napríklad tie, ktoré vyberajú a formátujú rozsahy, nie je možné vo vlastných funkciách využívať. V tomto článku sa dozviete, ako môžete vytvárať a používať vlastné funkcie. Funkcie a makrá budete vytvárať v editore jazyka Visual Basic (VBE), ktorý sa otvorí v samostatnom okne mimo Excelu.

Predpokladajme, že vaša spoločnosť ponúka množstvovú zľavu vo výške 10 % na určitý výrobok pri objednaní viac ako 100 jednotiek. V nasledujúcich odsekoch nájdete popis funkcie na výpočet tejto zľavy.

V príklade nižšie je zobrazený objednávkový formulár, v ktorom sú uvedené jednotlivé položky, množstvo, cena, zľava (ak sa uplatňuje) a výsledná navýšená cena.

Príklad formulára objednávky bez vlastnej funkcie

Ak chcete v tomto zošite vytvoriť vlastnú funkciu DISCOUNT, postupujte takto:

  1. Stlačením kombinácie klávesov Alt + F11 otvorte editor jazyka Visual Basic (v Macu stlačte kombináciu klávesov FN + ALT + F11) a kliknite na položky Vložiť > Modul. Na pravej strane editora jazyka Visual Basic sa zobrazí okno nového modulu.

  2. Skopírujte a prilepte nasledujúci kód do nového modulu.

    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

Poznámka: Ak chcete, aby bol kód lepšie čitateľný, môžete pomocou tabulátora odsadiť riadky. Vytvorenie zarážok je voliteľné. Slúžia len pre vašu potrebu, keďže kód bude funkčný aj bez nich. Po zadaní odsadeného riadka editor jazyka Visual Basic predpokladá, že aj nasledujúci riadok bude odsadený. Ak sa chcete presunúť o jeden znak tabulátora (t. j. vľavo), stlačte kombináciu klávesov Shift + Tab.

Teraz ste pripravení na používanie novej funkcie DISCOUNT. Zavrite editor jazyka Visual Basic, vyberte bunku G7 a zadajte nasledujúci vzorec:

=DISCOUNT(D7;E7)

Excel vypočíta zľavu vo výške 10 % na 200 jednotiek pri cene 47,50 EUR za jednotku a vráti sumu 950,00 EUR.

V prvom riadku kódu VBA, funkcia DISCOUNT(množstvo; cena), ste uviedli, že funkcia DISCOUNT si vyžaduje dva argumenty, množstvocena. Keď v bunke hárka použijete túto funkciu, musíte zadať obidva argumenty. Vo vzorci =DISCOUNT(D7;E7) predstavuje D7 argument množstvo a E7 argument cena. Teraz môžete skopírovať vzorec DISCOUNT do buniek G8 až G13. Získate výsledky zobrazené nižšie.

Poďme sa pozrieť na to, ako Excel interpretuje túto funkčnú procedúru. Keď stlačíte kláves Enter, Excel začne v aktuálnom zošite hľadať názov DISCOUNT a zistí, že ide o vlastnú funkciu v module VBA. Názvy argumentov v zátvorkách, množstvocena, sú zástupnými objektmi pre hodnoty, ktoré sú potrebné na výpočet zľavy.

Príklad formulára objednávky s vlastnou funkciou

Príkaz If v nasledujúcom bloku kódu slúži na preskúmanie argumentu množstvo. Určuje, či je počet predaných položiek väčší alebo rovný 100:

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

Ak je počet predaných položiek väčší alebo rovný 100, jazyk VBA spustí nasledujúci príkaz, ktorý vynásobí hodnotu množstvo hodnotou cena a výsledok vynásobí hodnotou 0,1:

Discount = quantity * price * 0.1

Výsledok sa uloží ako premenná Discount. Príkaz v jazyku VBA, ktorý ukladá hodnotu do premennej, sa nazýva príkaz priradenia, pretože jeho úlohou je vyhodnotiť výraz na pravej strane rovnosti a výsledok priradiť k názvu premennej na ľavej strane. Keďže premenná Discount má rovnaký názov ako funkčná procedúra, hodnota uložená v premennej sa vráti do vzorca hárka, ktorý spustil funkciu DISCOUNT.

Ak je množstvo menšie ako 100, jazyk VBA spustí nasledujúci príkaz:

Discount = 0

Nakoniec nasledujúci príkaz zaokrúhli hodnotu priradenú premennej Discount na dve desatinné miesta:

Discount = Application.Round(Discount, 2)

Jazyk VBA neponúka funkciu ROUND, ale Excel ju má. Ak teda chcete v tomto príkaze použiť funkciu ROUND, musíte jazyku VBA prikázať vyhľadať metódu zaokrúhlenia (funkciu) v objekte Application (Excel). Môžete to urobiť pridaním slova Application pred slovo Round. Túto syntax použite vždy, keď potrebujete získať prístup k funkcii Excelu z modulu VBA.

Vlastná funkcia sa musí začínať príkazom Function a končiť príkazom End Function. Príkaz Function okrem názvu funkcie zvyčajne špecifikuje aj jeden alebo dva argumenty. Môžete však vytvoriť aj funkciu bez argumentov. Excel obsahuje niekoľko vstavaných funkcií, ktoré nevyužívajú argumenty. Ide napríklad o funkcie RAND a NOW.

Po príkaze Function nasleduje vo funkčnej procedúre jeden alebo viac príkazy VBA, ktoré robia rozhodnutia a vykonávajú výpočty pomocou argumentov použitých vo funkcii. Nakoniec musíte niekde do funkcie zadať príkaz, ktorý priradí hodnotu k premennej s rovnakým názvom ako funkcia. Táto hodnota sa vráti do vzorca, ktorý vyvoláva funkciu.

Vo vlastných funkciách môžete použiť menej kľúčových slov VBA ako v makrách. Vlastné funkcie môžu len vracať hodnotu do vzorca v hárku alebo do výrazu použitého v inom makre alebo funkcii jazyka VBA. Vlastné funkcie nemôžu napríklad meniť veľkosť okien, upravovať vzorec v bunke ani meniť písmo, farbu či možnosti vzoru textu v bunke. Ak vo funkčnej procedúre použijete kód akcie tohto typu, funkcia vráti chybu #HODNOTA!.

Jedinou akciou, ktorú funkčná procedúra môže vykonávať (okrem vykonávania výpočtov), je zobrazovanie dialógových okien. Vo vlastnej funkcii môžete použiť príkaz InputBox, čím získate vstup od používateľa, ktorý spúšťa funkciu. Na odovzdanie informácií používateľovi môžete použiť príkaz MsgBox. Môžete použiť aj vlastné dialógové okná alebo používateľské formuláre. To však už nie je predmetom tohto článku.

Niekedy môžu byť aj jednoduché makrá a vlastné funkcie ťažko čitateľné. Ak chcete, aby boli zrozumiteľnejšie, môžete použiť popisný text vo forme komentárov. Komentáre môžete pridávať zadaním apostrofu pred popisný text. Nasledujúci príklad zobrazuje funkciu DISCOUNT s komentármi. Pridaním takýchto komentárov uľahčite sebe aj ostatným dlhodobé udržiavanie kódu VBA. Ak niekedy budete potrebovať niečo v kóde zmeniť, komentáre vám pomôžu pochopiť pôvodný kód.

Príklad funkcie VBA s komentármi

Excel považuje apostrof za príkaz ignorovať všetko, čo sa v tom istom riadku nachádza napravo od apostrofu. Komentáre preto môžete zadávať do samostatných riadkov alebo na pravú stranu riadkov obsahujúcich kód VBA. Dlhší blok kódu môžete uviesť komentárom, ktorý popisuje jeho účel. Jednotlivé príkazy potom podrobnejšie popíšte vo vnorených komentároch.

Makrá a vlastné funkcie môžete dokumentovať aj tak, že im priradíte popisné názvy. Makru napríklad môžete namiesto názvu Označenia priradiť názov OznačeniaMesiacov, čím presnejšie popíšete účel, na ktorý toto makro slúži. Používanie popisných názvov makier a vlastných funkcií je užitočné najmä v prípade, že ste vytvorili veľa procedúr, obzvlášť takých, ktoré slúžia na podobné, ale nie rovnaké účely.

Spôsob dokumentácie makier a vlastných funkcií je otázkou osobnej voľby. Dôležité je zvoliť si určitú formu dokumentácie a konzistentne ju používať.

Ak chcete použiť vlastnú funkciu, musíte mať otvorený zošit obsahujúci modul, v ktorom ste túto funkciu vytvorili. Ak zošit nie je otvorený, pri pokuse o použitie funkcie sa zobrazí chyba #NÁZOV?. Ak na danú funkciu odkazujete v inom zošite, pred názvom funkcie musíte uviesť názov zošita, v ktorom sa funkcia nachádza. Ak napríklad vytvoríte funkciu s názvom DISCOUNT v zošite, ktorý ste nazvali Osobne.xlsb, a túto funkciu voláte z iného zošita, musíte zadať reťazec =osobne.xlsb!discount(), nie iba =discount().

Zbytočnému písaniu (a prípadným chybám) sa môžete vyhnúť tak, že si vlastné funkcie vyberiete z dialógového okna Vloženie funkcie. Vaše vlastné funkcie sa zobrazia v kategórii Definované používateľom:

Dialógové okno Vloženie funkcie

Jednoduchším spôsobom, ako získať prístup k svojim vlastným funkciám kedykoľvek, je uložiť ich v samostatnom zošite a tento zošit uložiť ako doplnok. Doplnok budete mať k dispozícii pri každom spustení Excelu. Postupujte takto:

  1. Po vytvorení potrebných funkcií kliknite na položky Súbor > Uložiť ako.

    V programe Excel 2007 kliknite na tlačidlo Microsoft Office a potom na položku Uložiť ako.

  2. V dialógovom okne Uložiť ako otvorte rozbaľovací zoznam Uložiť vo formáte a vyberte možnosť Doplnok programu Excel. Zošit uložte pod rozpoznateľným názvom, napríklad MojeFunkcie, do priečinka Doplnky. Dialógové okno Uložiť ako navrhne tento priečinok, takže stačí prijať predvolené umiestnenie.

  3. Po uložení zošita kliknite na položky Súbor > Možnosti programu Excel.

    V programe Excel 2007 kliknite na tlačidlo Microsoft Office a potom na položku Možnosti programu Excel.

  4. V dialógovom okne Program Excel – možnosti kliknite na kategóriu Doplnky.

  5. V rozbaľovacom zozname Správa vyberte položku Doplnky programu Excel. Potom kliknite na tlačidlo Spustiť.

  6. V dialógovom okne Doplnky začiarknite políčko vedľa názvu, pod ktorým ste uložili zošit, ako je znázornené nižšie.

    Dialógové okno Doplnky

  1. Po vytvorení potrebných funkcií kliknite na položky Súbor > Uložiť ako.

  2. V dialógovom okne Uložiť ako otvorte rozbaľovací zoznam Uložiť vo formáte a vyberte možnosť Doplnok programu Excel. Zošit uložte pod rozpoznateľným názvom, napríklad MojeFunkcie.

  3. Po uložení zošita kliknite na položky Nástroje > Doplnky pre Excel.

  4. V dialógovom okne Doplnky vyberte tlačidlo Prehľadávať a nájdite svoj doplnok. Kliknite na položku Otvoriť a potom začiarknite políčko vedľa svojho doplnku v poli Dostupné doplnky.

Po vykonaní týchto krokov budete mať svoje vlastné funkcie k dispozícii pri každom spustení Excelu. Ak chcete pridať ďalšie funkcie do svojej knižnice funkcií, vráťte sa do editora jazyka Visual Basic. Keď sa v editore jazyka Visual Basic pozriete do Prieskumníka projektu pod nadpisom projektu VBA, uvidíte modul nazvaný podľa súboru vášho doplnku. Váš doplnok bude mať príponu .xlam.

Pomenovaný modul vo vbe

Keď dvakrát kliknete na modul v Prieskumníkovi projektu, editor jazyka Visual Basic zobrazí kód funkcie. Ak chcete pridať novú funkciu, umiestnite kurzor za príkaz End Function, ktorým sa končí posledná funkcia v okne kódu, a začnite písať. Týmto spôsobom môžete vytvoriť ľubovoľný počet funkcií. Funkcie budú vždy k dispozícii v kategórii Definované používateľom v dialógovom okne Vloženie funkcie.

Autormi pôvodnej verzie tohto obsahu sú Mark Dodge a Craig Stinson. Vytvorili ju ako súčasť svojej knihy Microsoft Office Excel 2007 Inside Out. Pôvodná verzia bola aktualizovaná aj pre novšie verzie Excelu.

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite technikov pre Excel, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×