Vytváranie vlastných funkcií v Exceli

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.

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
   

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 vyžaduje dva argumenty, množstvo a cenu. Keď zavoláte funkciu do bunky hárka, musíte zahrnúť tieto dva argumenty. Vo vzorci =DISCOUNT(D7;E7) je D7 argumentom množstva a E7 je cenový argument. Teraz môžete skopírovať vzorec DISCOUNT do G8:G13 a získať 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žstvo a cena, sú zástupnými objektmi pre hodnoty, ktoré sú potrebné na výpočet zľavy.

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. Okrem názvu funkcie príkaz Function zvyčajne určuje jeden alebo viac argumentov. Môžete však vytvoriť funkciu bez argumentov. Excel obsahuje niekoľko vstavaných funkcií, napríklad RAND a NOW, ktoré nepoužívajú argumenty.

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.

Počet kľúčových slov VBA, ktoré môžete použiť vo vlastných funkciách, je menší ako počet, ktorý môžete použiť v makrách. Vlastné funkcie nemôžu robiť nič iné ako vrátiť hodnotu vzorcu v hárku alebo výrazu použitému v inom makre alebo funkcii VBA. Vlastné funkcie napríklad nemôžu meniť veľkosť okien, upravovať vzorec v bunke ani meniť písmo, farbu alebo možnosti vzorky textu v bunke. Ak do funkčnej procedúry zahrniete kód akcie tohto druhu, funkcia vráti #VALUE! Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!.

Jedna akcia, ktorú môže vykonávať funkčná procedúra (okrem vykonávania výpočtov), je zobrazenie dialógového okna. Príkaz InputBox môžete použiť vo vlastnej funkcii ako prostriedok na získanie vstupu od používateľa, ktorý vykonáva funkciu. Príkaz MsgBox môžete použiť ako prostriedok na sprostredkovanie informácií používateľovi. Môžete použiť aj vlastné dialógové okná alebo používateľské formuláre, ale to je predmet mimo rozsahu tohto úvodu.

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.

Apostrof hovorí Excelu, aby ignoroval všetko napravo v rovnakom riadku, takže komentáre môžete vytvárať buď samostatne, alebo na pravej strane riadkov obsahujúcich kód VBA. Môžete začať pomerne dlhý blok kódu s komentárom, ktorý vysvetľuje jeho celkový účel, a potom použiť vnorené komentáre na dokumentovanie jednotlivých vyhlásení.

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, zošit obsahujúci modul, v ktorom ste funkciu vytvorili, musí byť otvorený. Ak tento zošit nie je otvorený, zobrazí sa #NAME? pri pokuse o použitie funkcie. Ak na funkciu odkazujete v inom zošite, pred názov funkcie je nutné uviesť názov zošita, v ktorom sa funkcia nachádza. Ak napríklad vytvoríte funkciu s názvom DISCOUNT v zošite s názvom Personal.xlsb a túto funkciu zavoláte z iného zošita, musíte zadať výraz =personal.xlsb!discount(), nie jednoducho =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:

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:

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.

Dvojitým kliknutím na modul v Prieskumníkovi projektu sa v programe Visual Basic Editor zobrazí kód funkcie. Ak chcete pridať novú funkciu, umiestnite kurzor za príkaz End Function, ktorý ukončí poslednú funkciu v okne Kód a začnite písať. Týmto spôsobom môžete vytvoriť ľubovoľný počet funkcií a budú vždy k dispozícii v kategórii Definované používateľom v dialógovom okne Vloženie funkcie .

Tento obsah pôvodne vytvoril Mark Dodge a Craig Stinson ako súčasť svojej knihy Microsoft Office Excel 2007 Inside Out. Odvtedy bola aktualizovaná tak, aby sa vzťahovala aj na novšie verzie Excelu.