Upozornění pro čtenáře a uživatele této knihy

Všechna práva vyhrazena. Žádná část této tištěné či elektronické knihy nesmí být reprodukována ani šířena v papírové, elektronické či jiné podobě bez předchozího písemného souhlasu nakladatele. Neoprávněné užití této knihy bude trestně stíháno.

Marek Laurenčík

Programování v Excelu 2010 a 2013

záznam, úprava a programování maker

TIRÁŽ TIŠTĚNÉ PUBLIKACE:

Vydala Grada Publishing, a.s.

U Průhonu 22, Praha 7

jako svou 5389. publikaci

Spoluautor Michal Bureš

Odpovědný redaktor Štěpán Böhm

Sazba Tomáš Brejcha

Počet stran 200

První vydání, Praha 2013

© Grada Publishing, a.s., 2013

Cover Photo © fotobanka allphoto

V knize použité názvy programových produktů, firem apod. mohou být ochrannými známkami nebo registrovanými ochrannými známkami příslušných vlastníků.

Vytiskly Tiskárny Havlíčkův Brod, a.s.

ISBN 978-80-247-5033-0

ELEKTRONICKÉ PUBLIKACE:

E-knihu ve formátu EPUB vytvořil Štěpán Böhm

ISBN 978-80-247-8919-4 (ve formátu PDF)

ISBN 978-80-247-8920-0 (ve formátu EPUB)

Úvod

Knížka, kterou právě otvíráte, se zabývá programováním v jazyku Visual Basic for Applications (VBA) v tabulkovém kalkulátoru MS Excel 2013. Nepředpokládáme žádné předběžné znalosti programování, pouze znalost práce s kalkulátorem Excel na uživatelské úrovni. Kniha je sice primárně určena pro Excel verze 2013, avšak programovací postupy, popisované v knize, jsou použitelné i v Excelu 2010 a 2007. Případné odchylky mezi oběma verzemi (kterých je při tvorbě maker poměrně málo) jsou v knize vždy uvedeny.

Přestože většina uživatelů Excelu, kteří se chtějí seznámit s programováním v jazyku VBA, již s makry pracovala, je první kapitola věnována tvorbě maker uživatelským způsobem a různým způsobům jejich spouštění.

Druhá a třetí kapitola popisují základy jazyka Visual Basic. Poznatky, které zde získáte, můžete uplatnit i při programování v jiných produktech kompletu MS Office, zejména v Accessu.

Manipulace se sešity pomocí maker, napsaných v jazyce VBA, je podrobně popisována ve čtvrté až desáté kapitole. Snažili jsme se seřadit látku od jednodušších k náročnějším postupům. Omezený rozsah knihy samozřejmě neumožnil zabývat se všemi možnostmi, které tabulkový kalkulátor Excel poskytuje, včetně pokročilých a speciálních postupů. Nepopisujeme např. programování datových krychlí. Jsme však přesvědčeni, že výběr látky (zahrnující také práci s grafy a kontingenčními tabulkami) pokryje převážnou většinu toho, co budete od programování v Excelu očekávat.

Významné rozšíření možností Excelu představují uživatelské formuláře, a to zejména ve spojení s programovým kódem v jazyku VBA. Této problematice je věnována jedenáctá a dvanáctá kapitola.

Poslední tři kapitoly jsou věnovány doplňkové problematice. Ve třinácté kapitole se dozvíte o možnosti spolupráce Excelu s jinými aplikacemi kompletu MS Office: Wordem, Accessem a Outlookem a o možnosti spolupráce sešitu se soubory v kódu XML. Čtrnáctá kapitola popisuje převod sešitu na samonosnou aplikaci, použitelnou jinými uživateli. Zde se dozvíte, jak vytvářet vlastní tlačítka a další ovládací prvky (jak způsobem použitelným v dřívějších verzích, tak i úpravou a tvorbou ovládacích karet), zajistit kód heslem nebo vytvářet a využívat vlastní doplňky. Poslední kapitola, věnovaná třídám, představuje spíše úvod do programování na vyšší úrovni. I zde však můžete jistě najít zajímavá praktická využití.

Předkládanou knihu nebylo možné vybavit vloženým CD s příklady. Nicméně jsme ke každé kapitole s výjimkou první vytvořili jeden nebo více sešitů s ukázkovými makry, funkcemi a formuláři. Tyto sešity jsou ve formátu Excelu 2013 (XLSM) a můžete si je stáhnout z webových stránek nakladatelství Grada na adrese www.grada.cz. Odkazy na jednotlivé příklady naleznete v jednotlivých kapitolách. Vlastní kód VBA je v sešitech podrobně okomentován, aby byl snadno srozumitelný a mohl sloužit jako zdroj inspirace při vytváření vlastního kódu.

Ať se Vám daří!

Autoři

1.Tvorba makra záznamem

Co se v této kapitole naučíte:

■zaznamenání makra,

■absolutní a relativní odkaz v makru,

■různé způsoby spuštění maker,

■využití maker z jiného sešitu.

1.1Použití maker v Excelu

Makra jsou účinným prostředkem, jak si při práci s tabulkovým kalkulátorem zautomatizovat sérii operací, opakovaných několikrát za sebou. Sešit Excelu je schopen zaznamenat řadu akcí po sobě a podle potřeby tyto akce kdykoliv zopakovat. Tato schopnost Excelu vám ušetří spoustu práce a zabraňuje zbytečným chybám.

Akce, prováděné po spuštění makra, si Excel zaznamená pomocí programovacího jazyka Visual Basic for Applications (VBA), jehož editor je součástí každého sešitu. Excel umožňuje vytvářet makra dvojím způsobem:

■Uživatelsky – v sešitu zapnete zvláštní režim, který všechny provedené akce zaznamená pomocí kódu VBA a tento kód uloží jako součást sešitu.

■Programátorsky – zobrazíte si editor jazyka VBA, ve kterém potřebný kód zapíšete z klávesnice.

Je možné používat i kombinaci obou způsobů – zaznamenat makro uživatelsky a později jej upravit přímým zápisem do kódu.

Při uživatelském přístupu může makro obsahovat pouze akce, které jste schopni provádět i běžným způsobem a slouží tedy k úspoře práce se sešitem. Programátorský způsob tvorby maker poskytuje další možnosti:

■zpracovávat údaje na listu způsobem, který byl při uživatelské práci velmi pracný nebo nemožný;

■vytvářet nové vlastní funkce;

■spouštět automatické akce při otevření a zavření sešitu;

■vytvářet vlastní dialogová okna pro zadávání vstupních údajů.

Se záznamem maker se seznámíte v této kapitole. Využití jazyka VBA je předmětem dalších kapitol.

Pro práci s makry je nutné zobrazit si další kartu, zvanou Vývojář. Jestliže karta není zobrazena, postupujte takto:

1.Klepněte na kartu Soubor.

2.Vpravo dole klepněte na příkaz Možnosti. Tím se zobrazí okno, určené pro nastavení programu.

3.V levé části okna klepněte na příkaz Přizpůsobit pás karet.

4.V pravém seznamu označte volbu Vývojář a potvrďte tlačítkem OK.

Toto nastavení Excelu je trvalé a při jeho příštím spuštění již bude karta Vývojář zobrazena.

Obrázek č. 1.1: Přizpůsobení pásů karet v Excelu 2010

1.2Záznam a spuštění makra

K práci s makry jsou určena tlačítka na kartě Vývojář. Na kartě Zobrazit je také k dispozici tlačítko Makra, umožňující záznam a spuštění makra, práce s kartou Vývojář je však přehlednější.

Obrázek č. 1.2: Karta Vývojář

Pro záznam makra použijte tento postup:

1.Přejděte na kartu Vývojář.

2.Pomocí tlačítka Relativní odkazy určete, jak se má v makru zaznamenat pohyb kurzoru. Jestliže toto tlačítko stisknete, pohyb kurzoru se zaznamená relativně vůči výchozí buňce. Pokud jej ponecháte vypnuté, zaznamená se pevná adresa buňky, na kterou se kurzor během záznamu přemístil.

3.Klepněte na tlačítko Záznam makra. Nápis na tlačítku se změní na Zastavit záznam.

4.V zobrazeném okně vyplňte položku Název makra. Ke tvorbě názvu používejte písmena, číslice nebo podtržítko. Jiné znaky (mezera, čárka, pomlčka atd.) nejsou přípustné.

5.V seznamu Uložit makro do vyberte, kam se má vytvářené makro zapsat (viz dále).

6.Potvrďte tlačítkem OK. Tím spustíte záznam makra.

7.Proveďte jednotlivé kroky, které má makro obsahovat.

8.Klepněte na tlačítko Zastavit záznam. Tím se záznamový režim ukončí a se sešitem je možné normálně pracovat.

Obrázek č. 1.3: Zapnutý a vypnutý relativní záznam

V záznamovém režimu se uloží všechny akce prováděné Excelem, tedy i chyby, kroky zpět a automatická uložení. Při záznamu většinou nezáleží na tom, jak akci provedete: můžete použít tlačítko na kartě, místní nabídku nebo klávesový povel. Výjimkou je nastavení písma. Jestliže pro nastavení některého atributu písma použijete tlačítko nebo klávesový povel, do makra se zaznamená pouze nastavení změny na tučné. Pokud nastavíte písmo pomocí dialogového okna, do makra se uloží všechna nastavení, tedy i font, velikost, barva atd. bez ohledu na to, jestli se jedná o hodnoty výchozí nebo upravené.

Obrázek č. 1.4: Okno pro záznam makra

Uložení makra a vyzkoušení makra

V seznamu Uložit makro do můžete vybrat tři možnosti, kam se bude vytvářené makro ukládat:

■Volba Tento sešit (výchozí) – makro se ukládá do stejného sešitu, ve kterém byly zaznamenávané akce prováděny.

■Volba Nový sešit – založí se nový prázdný sešit, do kterého se makro uloží.

■Volba Osobní sešit maker – založí se speciální sešit s název Personal.xlsb, do kterého se vytvářené makro uloží. Pokud tento sešit již existuje, vytvářené makro se do sešitu přidá.

Sešit Personal funguje tak, že se při každém dalším spuštění Excelu automaticky otevře jako skrytý. To vede k tomu, že všechna makra, uložená v tomto sešitu, jsou přístupná vždy po spuštění Excelu.

Uložení makra v sešitu Personal (a tedy jeho zpřístupnění ve všech otevřených sešitech) může vést k nechtěnému spuštění makra. Ve Windows 7 je sešit Personal uložen ve složce C:\Documents and Settings\uživatelské jméno\Data aplikací\Microsoft\Excel\XLStart. Ve Windows 8 je sešit Personal uložen ve složce c:\Users\ uživatelské jméno\appdata\Roaming\Microsoft\Excel\XLSTART\.

Pro vyzkoušení makra klepněte na tlačítko Makra na kartě Vývojář, v zobrazeném okně označte vytvořené makro a klepněte na tlačítko Spustit. Po ukončení běhu makra je tlačítko Zpět, umístěné na panelu Rychlý přístup, znepřístupněno. To znamená, že akce provedené makrem jsou vždy nevratné.

Obrázek č. 1.5: Seznam maker

Uložení a otevření sešitu s makrem

Excel rozlišuje dva typy sešitů: obyčejné sešity (přípona XLSX) a sešity s podporou maker (přípona XLSM). Při uložení sešitu se zaznamenaným makrem jej musíte uložit ve formátu XLSM. Při pokusu o uložení běžným způsobem se zobrazí varovné hlášení.

Jestliže se pokusíte uložit sešit se zaznamenaným makrem ve formátu XLSX a přes varovné hlášení pokračujete v uložení, sešit se uloží, avšak zaznamenané makro se ze sešitu odstraní.

Při otevření sešitu s makrem se nad řádkem vzorců zobrazí výstražné hlášení s tlačítkem Povolit obsah. Po klepnutí na tlačítko se sešit otevře. Excel 2010 a 2013 si toto povolení u sešitů s makrem pamatuje, takže při dalším otevření stejného sešitu se již hlášení nezobrazí a sešit se ihned otevře. Jestliže sešit přejmenujete nebo přesunete do jiného umístění, výstražné hlášení se objeví znovu.

Obrázek č. 1.6: Povolení maker u Excelu

Odstranění makra ze sešitu

Nepotřebná nebo chybně pracující makra je záhodno ze sešitu odstranit, aby nedošlo k jejich nechtěnému spuštění. Klepnutím na tlačítko Makra zobrazte seznam maker, v seznamu označte konkrétní makro a použijte tlačítko Odstranit. Akce je jištěna zpětným dotazem a je nevratná.

V dialogovém okně pro seznam maker je k dispozici také tlačítko Upravit. Toto tlačítko však neumožní opakovat záznam makra, ale zobrazí jeho kód, zapsaný v jazyce Visual Basic.

1.3Různé způsoby spuštění makra

Spuštění makra ze seznamu není pro rutinní práci příliš pohodlné. Excel proto nabízí několik dalších způsobů, jak makra spouštět:

■klávesovou zkratkou,

■tlačítkem na panelu Rychlý přístup,

■tlačítkem na některé z karet,

■tlačítkem na listu.

Spuštění makra klávesovou zkratkou

Přiřazení klávesové zkratky makru je možné provést už při záznamu makra nebo dodatečně:

1.Tlačítkem Makra na kartě Vývojář zobrazte seznam maker a v něm označte potřebné makro.

2.Klepněte na tlačítko Možnosti.

3.Do položky Klávesová zkratka zapište písmeno, které spolu s klávesou CTRL bude tvořit klávesovou zkratku.

4.Potvrďte tlačítkem OK.

Obrázek č. 1.7: Přiřazení klávesové zkratky k makru

Při zadávání klávesové zkratky se rozlišují malá a velká písmena. Jestliže do položky Klávesová zkratka zapíšete malé písmeno, klávesovou zkratkou bude klávesa CTRL spolu s písmenem. Pokud zapíšete velké písmeno, klávesovou zkratkou bude kombinace CTRL+SHIFT+písmeno.

Pro klávesovou zkratku se třeba zvolit písmeno, které není použito ve standardních klávesových zkratkách Excelu. To je ovšem problém, protože tyto zkratky již vyčerpaly skoro celou abecedu. Klávesová zkratka CTRL+Q, která spolehlivě fungovala v dřívějších verzích Excelu, není ve verzi 2013 použitelná, protože je rezervována pro rychlou analýzu dat. Řešením je použití velkých písmen, protože standardní klávesové zkratky Excelu používají vesměs malá písmena.

V seznamu maker nejsou přiřazené klávesové zkratky zobrazeny, pro jejich zjištění musíte použít tlačítko Možnosti.

Jestliže se pokusíte přiřadit tutéž klávesovou zkratku dvěma různým makrům v jednom sešitu, Excel nezobrazí žádné upozornění a duplicitní přiřazení provede.

Tlačítko na panelu Rychlý přístup

Přidání tlačítka pro spouštění makra na panel Rychlý přístup provedete následujícím způsobem:

1.Klepněte na kartu Soubor a vpravo dole klepněte na příkaz Možnosti.

2.V levé části zobrazeného okna klepněte na příkaz Panel nástrojů Rychlý přístup.

3.V seznamu Zvolit příkazy nastavte položku Makra.

4.Označte potřebné makro a klepněte na tlačítko Přidat.

5.V pravém seznamu označte přidané makro a klepněte na tlačítko Změnit.

6.V dalším zobrazeném okně vyberte vhodnou ikonu a podle potřeby upravte zobrazovaný název. Tento název se zobrazí u vytvořeného tlačítka jako bublinová nápověda.

7.Potvrďte dvakrát tlačítkem OK.

Obrázek č. 1.8: Úprava panelu Rychlý přístup

Takto vytvořené tlačítko bude na panelu Rychlý přístup zobrazeno při každém spuštění Excelu. Druhou možností je použít rozevírací seznam v pravé horní části okna a místo volby Pro všechny dokumenty (výchozí) nastavit volbu Pro dokument+název sešitu. V takovémto případě se tlačítko pro spuštění makra zobrazí na panelu Rychlý přístup jen při otevření sešitu, kde bylo makro vytvořeno.

Obrázek č. 1.9: Změna ikony a názvu u tlačítka

Přidání tlačítka na kartu

Tlačítko pro spuštění makra můžete také přidat na některou z karet nebo si vytvořit kartu vlastní. Postupujte takto:

1.Klepněte na kartu Soubor a vpravo dole klepněte na příkaz Možnosti.

2.V levé části zobrazeného okna klepněte na příkaz Přizpůsobit pás karet.

3.Klepněte na tlačítko Nová karta. Tím se v pravém seznamu vytvoří nová karta a v ní nová skupina.

4.Označte postupně novou kartu a novou skupinu a pomocí tlačítka Přejmenovat zadejte vhodné názvy.

5.Označte novou skupinu. V rozevíracím seznamu Zvolit příkazy nastavte volbu Makra a tlačítkem Přidat zařaďte do skupiny nové tlačítko.

6.Označte vytvořené tlačítko a použijte tlačítko Přejmenovat. Vyberte ikonu a změňte název tlačítka.

7.Polohu nové karty můžete upravit pomocí tlačítek se šipkami nahoru a dolů.

8.Potvrďte tlačítkem OK.

Obrázek č. 1.10: Přidání tlačítka na kartu

Jestliže označíte některou se standardních karet a použijete tlačítko Nová skupina, do karty se zařadí nová prázdná skupina. Do této skupiny je možné přidávat popsaným způsobem nová tlačítka. U standardních skupin to možné není.

Přidávání nových tlačítek do karet není omezeno jen na makra. V seznamu Zvolit příkazy je možné nastavit také jinou volbu, např. Příkazy mimo pás karet, a tím si libovolnou kartu doplnit tlačítky podle potřeby.

Přidání tlačítka na list

Tlačítko pro spouštění makra je možné vložit také na některý z listů. Postup je následující:

1.Na kartě Vývojář klepněte na šipku u tlačítka Vložit. Tím se zobrazí nabídka ovládacích prvků, které je možné na listu vytvářet.

2.V nabídce klepněte na tlačítko, umístěné v první řadě zcela vlevo (bublina „Tlačítko (ovládací prvek formuláře)“.

3.V listu klepněte na místo, kde má být tlačítko umístěno.

4.Po klepnutí se zobrazí okno se seznamem maker. Označte potřebné makro a klepněte na tlačítko OK.

Obrázek č. 1.11: Nabídka pro vytvoření ovládacího prvku na listu

Pro úpravu tlačítka na něj klepněte pravým tlačítkem myši, z místní nabídky použijte příkaz Upravit text a přepište nápis na tlačítko. Jestliže na tlačítko klepnete pravým tlačítkem myši a poté použijete klávesu ESC, tlačítko zůstane ohraničeno úchyty obdobně jako označený obrázek. V tomto režimu je možné tahem za úchyty měnit velikost tlačítka, měnit jeho polohu (myší nebo klávesovými šipkami) a na kartě Domů nastavit tučné písmo nebo kurzívu. Tlačítko pro nastavení barvy písma je nepřístupné. Je však možné na tlačítko klepnout pravým tlačítkem myši, z místní nabídky použít příkaz Formát ovládacího prvku a nastavit potřebnou změnu na kartě Písmo. Klávesou DELETE se označené tlačítko z listu odstraní.

1.4Spuštění makra v jiném sešitu

Jestliže otevřete dva nebo více sešitů v jednom běhu Excelu, makro vytvořené v jednom z otevřených sešitů je možné spustit v kterémkoliv z otevřených sešitů. Ke spuštění můžete použít seznam maker (před názvem makra je v tomto případě zobrazen ještě název sešitu, oddělený vykřičníkem), klávesovou zkratku, tlačítko na panelu Rychlý přístup nebo tlačítko na některé z karet. Makro lze tímto způsobem spouštět i v sešitu bez podpory maker (přípona XLSX). Tímto způsobem také funguje zmíněný sešit Personal, který je otevřen automaticky jako skrytý.

Pokud sešit s makrem otevřen není, seznam maker se zobrazí jako prázdný a nefunguje ani klávesová zkratka. Tlačítko na panelu Rychlý přístup a tlačítko na některé z karet, určené ke spouštění makra, však automaticky sešit s makrem otevřou a makro spustí. Protože jsou všechny akce provedené makrem nevratné, nechtěné spuštění makra může mít nepříjemné následky. Proto je často výhodnější používat ke spuštění makra tlačítko, umístěné přímo na listu.

Mezi jazykem VBA v Excelu 2007, 2010 a 2013 není téměř žádný rozdíl. Proto lze makra, zaznamenaná v jedné verzi, zpravidla bez problémů spouštět ve verzi druhé.

2.Tvorba uživatelských funkcí

Co se vtéto kapitole naučíte:

■práci seditorem VBA,

■vytvoření uživatelské funkce ajejí využití,

■přidání nápovědy kvytvořené funkci,

■větvení výpočtu na několik částí,

■práci sproměnnými.

Příklady vtéto kapitole naleznete vsešitu Uzivatelske_funkce.xlsm.

2.1Práce seditorem Visual Basicu

Makra, vytvářená záznamem, se vExcelu ukládají vprogramovacím jazyku Visual Basic for Applications (VBA). Vtomto jazyku můžete vytvářet ivlastní kódy dvojího typu:

■uživatelské funkce (/Function), které lze vkládat do buněk jako vzorce;

■procedury (Sub), které mají obdobné použití jako makra, vytvářená záznamem.

Vytvářený kód se ukládá do programového modulu, který vytvoříte tímto způsobem:

1.Klepněte na záložku karty Vývojář.

Obrázek č.2.1: Karta Vývojář

2.Na této kartě klepněte na tlačítko Visual Basic. Tím si zobrazíte okno editoru VBA.

3.Vtomto okně použijte příkaz Insert Module.

4.Klepněte do podokna Properties avlastnost Name přepište na vhodné pojmenování (výchozí je Module1).

5.Klepněte do podokna modulu (vpravo). Tím je editor připraven kpráci.

Obrázek č.2.2: Okno editoru VBA

Pro název modulu ivšechny další názvy veditoru VBA (funkce, procedury, proměnné atd.) používejte pouze písmena bez české diakritiky, číslice nebo podtržítko. Nepoužívejte mezery adalší interpunkční znaky.

Práce vokně VBA

Editor VBA vytvoří na hlavním panelu samostatné tlačítko. Vokně editoru naleznete tři podokna:

■Podokno projektu (vlevo nahoře) obsahuje seznam listů vsešitu, seznam modulů (složka Modules), popř. seznam uživatelských formulářů. Jestliže chcete zobrazit kód některého zmodulů, poklepejte na jeho název.

■Podokno Properties (vlevo dole) obsahuje umodulu pouze jeho název (Name). Hlavní použití tohoto podokna je při tvorbě formulářů.

■Okno kódu (vpravo) slouží kvlastnímu zápisu kódu.

Editor VBA není počeštěn avše je vangličtině. Tomu musíte přizpůsobit také zápis kódu:

■jako oddělovač desetinných míst používejte tečku (nikoliv čárku),

■jako oddělovač vseznamu používejte čárku (nikoliv středník).

Tato pravidla platí vanglické ičeské verzi Excelu.

Podokno kódu funguje obdobně, jako poznámkový blok. Jednotlivé příkazy ukončíte klávesou ENTER; prázdné řádky jsou ignorovány. Klávesové zkratky CTRL+C, CTRL+X aCTRL+Vmůžete použít ke zkopírování, vyjmutí avložení označeného textu.

Při rozdělení příkazu na dva řádky ukončete první část mezerou apodtržítkem adalší řádek je pak chápán jako pokračování. Řádky uvozené apostrofem jsou komentáře apři provádění kódu se ignorují. Delší komentáře je možné rozdělit do více řádků pomocí mezery apodtržítka. Komentáře začínající apostrofem je také možno připsat za výkonný příkaz.

Editor VBA zobrazuje zapsaný kód pomocí čtyř barev:

■modrou barvou klíčová slova jazyku Visual Basic,

■černou barvou názvy funkcí aproměnných,

■zelenou barvou komentáře,

■červenou barvou chybné řádky.

Použití nápovědy

Pro zobrazení okna nápovědy použijte příkaz Help Microsoft Visual Basic for Applications Help. Jestliže chcete zobrazit konkrétní nápovědu pro některý zpříkazů jazyka VBA, označte jej jako blok apoužijte klávesu F1.

2.2Tvorba uživatelské funkce ajejí použití

Vytvoření nové uživatelské funkce provedete těmito kroky:

1.Vokně editoru VBA klepněte do podokna kódu.

2.Použijte příkaz Insert Procedure.

3.Vzobrazeném okně označte volbu Function.

4.Vyplňte název funkce (Name).

5.Potvrďte tlačítkem OK.

Obrázek č.2.3: Vložení nové funkce do kódu VBA

Tím se do okna kódu vloží první aposlední řádek kódu funkce:

Public Function Nazev_funkce()

End Function

Vjednom modulu lze vytvořit více funkcí ajejich kód je oddělen vodorovnou čárou.

Funkce má zpravidla jeden nebo více vstupních parametrů. Zapíšete je do závorek vprvním příkazu funkce aoddělíte čárkami. Návrat vypočtené hodnoty zajistíte jejím dosazením do názvu funkce, což je nejčastěji předposlední příkaz vkódu funkce:

Nazev_funkce = 20

Jako návratovou hodnotu lze použít číslo, text (vuvozovkách), datumovou hodnotu, proměnnou nebo vzorec.

Pro tvorbu vzorců použijete běžná znaménka +, -, *, /, ^, popř. kulaté závorky. Vkódu funkce můžete používat idalší proměnné, sloužící jako mezivýsledky. Na rozdíl od vzorců vExcelu však Visual Basic nepovoluje používat procenta. Např. funkce pro výpočet DPH by mohla vypadat takto:

Public Function DPH(castka)

vysledek = castka * 0.2

DPH = vysledek

End Function

Použití uživatelské funkce

Vytvořenou funkci můžete vyzkoušet přímo vokně VBA. Ktomu použijte příkaz View Immediate Window. Vzobrazeném ladícím okně zapište otazník, název funkce ahodnoty parametrů vzávorkách. Řádek ukončete klávesou ENTER. Na dalším řádku se objeví výsledek.

Obrázek č.2.4: Okno Immediate Window pro kontrolu vytvořené funkce

Uživatelskou funkci vložíte do buňky stejným způsobem jako standardní funkci Excelu avokně pro vložení funkce zvolíte skupinu Vlastní. Uživatelské funkce se však nezobrazují ve skupině Naposledy použité.

Obrázek č.2.5: Vložení uživatelské funkce do buňky

Vzorce vbuňkách, využívající vlastní funkce, se po změně vstupní hodnoty automaticky přepočítají. Jestliže změníte kód funkce, přepočet se neprovede amusíte si jej vynutit klávesou F9.

Přidání nápovědy kuživatelské funkci

Uživatelskou funkci můžete opatřit také stručnou nápovědou:

1.Na kartě Vývojář klepněte na tlačítko Makra.

2.Do položky pro název makra zapište název funkce.

3.Klepněte na tlačítko Možnosti.

4.Vdalším zobrazeném okně zapište do položky Popis text nápovědy apotvrďte tlačítkem OK.

5.Zavřete okno se seznamem maker.

Obrázek č.2.6: Přidání nápovědy kuživatelské funkci

2.3Větvení kódu

Uživatelské funkce často využívají několika variant výpočtu. Větvení kódu je možné provést dvěma různými způsoby: podmíněným příkazem If apřepínačem Select.

Podmíněný příkaz

Jednoduchý podmíněný příkaz má tvar:

If <podmínka> Then

<příkazy>

End If

Jako podmínku použijete libovolný výraz, nabývající hodnot True nebo False (pravda nebo nepravda). Při zápisu podmínek použijete relační operátory „<“, „<=“, „>“, „>=“, popř. rovnítko. Výraz „nerovná se“ zapište jako „<>“. Příkazů, prováděných vzávislosti na podmínce, může být ivíce.

Takto zapsaný podmíněný příkaz má jednu větev, tj. zapsané příkazy se vzávislosti na podmínce provedou, nebo ne. Druhou možností je podmíněný příkaz se dvěma větvemi, ukterého se provede jedna ze dvou sekvencí příkazů:

If <podmínka> Then

<příkazy1>

Else

<příkazy2>

End If

Je-li podmínka splněna, provedou se příkazy číslo 1, není-li splněna, příkazy číslo 2. Příklad:

If Vstupni_hodnota < 100 Then

Nazev_funkce = Vstupni_hodnota * 0.2

Else

Nazev_funkce = Vstupni_hodnota * 0.1

End If

Příkazy, prováděné vzávislosti na podmínce, je vhodné odsadit trochu doprava; kód je přehlednější. Všechna klíčová slova Visual Basicu lze psát malými písmeny, velká počáteční písmena doplní editor automaticky.

Podmínka vpříkazu If může být složena zněkolika výrazů, spojených operátory And (obě podmínky platí současně) nebo Or (platí aspoň jedna zobou podmínek). Utřech avíce výrazů je potřeba uvážit, že operátor And svazuje výrazy těsněji než operátor Or. Například podmínku „Parametr1 je větší než 10 azároveň je Parametr2 roven dvěma nebo třem“ zapíšete takto:

If Parametr1>10 And (Parametr2 = 2 Or Parametr2 = 3) Then

Pokud byste podmínku zapsali bez závorek, tedy použili výraz

If Parametr1>10 And Parametr2 = 2 Or Parametr2 = 3 Then

interpretovala by se jako „Buď je Parametr1 větší než 10 azároveň je Parametr2 roven dvěma, nebo je Parametr2 roven třem“.

Podmínku je možné také zapsat do logické proměnné, která pak obsahuje hodnotu True nebo False podle toho, zdali je podmínka splněna, nebo ne. Předchozí příklady by bylo možno tedy zapsat také takto:

podmínka = Parametr1>10 And (Parametr2 = 2 Or Parametr2 = 3)

If podmínka Then

Podmíněný příkaz If umožňuje použít ivíce větví:

If Vstupni_hodnota < 100 Then

Nazev_funkce = Vstupni_hodnota * 0.2

ElseIf Vstupni_hodnota < 1000 Then

Nazev_funkce = Vstupni_hodnota * 0.1

Else

Nazev_funkce = Vstupni_hodnota * 0.05

End If

Příkaz If svíce větvemi funguje tak, že jakmile je nějaká podmínka splněna, provedou se odpovídající příkazy adalší větve se již nezkoumají. Proto musíte dbát na správné pořadí podmínek: jestliže vpodmínkách používáte operátor <, musíte jednotlivé varianty psát „zezdola nahoru“, pokud používáte operátor >, tak „seshora dolů“.

Přepínač

Jestliže je zapotřebí rozdělit výpočet na několik větví vzávislosti na hodnotě jedné proměnné, je možné použít příkaz Select, který má tento tvar:

Select Case <proměnná>

Case <výraz1>

<příkazy1>

Case <výraz2>

<příkazy2>

Case Else

<poslední příkazy>

End Select

Do výrazu pro podmínky se již název proměnné nepíše, konkrétní hodnota znamená rovnost (znaménko „=“ není třeba zapisovat). Poslední větev, uvozená klauzulí Case Else, se provede tehdy, jestliže není splněna ani jedna zpředchozích podmínek. Při použití příkazu Select by předchozí příkaz vypadal takto:

Select Case Vstupni_hodnota

Case Is < 100

Nazev_funkce = Vstupni_hodnota * 0.2

Case Is < 1000

Nazev_funkce = Vstupni_hodnota * 0.1

Case Else

Nazev_funkce = Vstupni_hodnota * 0.05

End Select

Klíčové slovo Is není třeba zapisovat, editor jej doplní automaticky.

Příkaz Select funguje obdobně jako příkaz If svíce větvemi, aproto je třeba dbát na správné pořadí podmínek.

Vytvořte funkci, počítající odměnu exekutora zvymáhané částky tímto způsobem:

Částka je menší než 3 miliony: 15%

Částka je mezi 3 a40 miliony: 10% pro částku nad 3 miliony + 450000

Částka je mezi 40 a50 miliony: 5% pro částku nad 40 milionů + 4150000

Částka je mezi 50 a250 miliony: 1% pro částku nad 50 milionů + 4650000

Částka je nad 250 milionů: 6650000

Minimální výše odměny bez ohledu na vymáhanou částku činí 3000.

Vsešitu Uzivatelske_funkce.xlsm je tato funkce vytvořena pod názvem

Odmena_exekutora.

2.4Deklarace proměnných

Proměnné, používané jako vstupní parametry funkce nebo jako mezivýsledky mohou obsahovat celá nebo desetinná čísla, textové řetězce, datum nebo hodnoty True aFalse (Ano/Ne). Typ proměnné se určí při prvém dosazení hodnoty nebo je možné určit jej dopředu pomocí deklarace proměnné. Deklaraci zapíšete příkazem:

Dim <proměnná> As <typ>

Jestliže zapíšete klauzuli As amezeru, editor VBA nabídne kontextové menu, ze kterého je možné potřebný typ vybrat klepnutím myší. Hledání vnabídce usnadňuje zápis prvního písmena.

Obrázek č.2.7: Kontextová nabídka editoru VBA

Deklaraci proměnné je možné provést na libovolném místě kódu před jejím prvým použitím. Hodnota, dosazovaná do deklarované proměnné, musí odpovídat deklarovanému typu. Pokud tomu tak není, nastane chyba. Visual Basic je však schopen vněkterých případech převést text na odpovídající hodnotu, např. příkazy

Dim pocet as Integer

pocet = "200"

budou fungovat správně.

Tabulka č.2.1: Základní typy proměnných

Typ

Význam

Rozsah hodnot

Byte

Celé číslo

0 až 255

Integer

Celé číslo

-32768 až 32767

Long

Celé číslo

± 2147483647

Single

Desetinné číslo

± 3,4.1038

Double

Desetinné číslo

± 1,79.10308

Currency

Měna

Přibližně ± 9 bilionů

String

Text

Date

Datum

Boolean

True–False

Příkaz Dim může obsahovat více deklarací, ale nemůže deklarovat několik proměnných stejného typu najednou. Tedy příkaz Dim kod1, kod2 as Integer je chybný amusíte zapsat příkaz Dim kod1 as Integer, kod2 As Integer.

Deklaraci pomocí klauzule As je možné zapsat ido prvého příkazu funkce adeklarovat vstupní parametr jako proměnnou daného typu:

Public Function Lhuta_splatnosti(datum_vystaveni As Date)

Jestliže na začátek modulu (před zápis prvé funkce nebo makra) zapíšete příkaz Option Explicit, všechny používané proměnné musí být deklarovány, jinak editor VBA ohlásí chybu. Editor VBA můžete nastavit tak, aby se uvedený příkaz vložil automaticky na začátek každého modulu:

1.Použijte příkaz Tools → Option.

2.Klepněte na záložku prvé karty Editor.

3.Označte volbu Require Variable Declaration apotvrďte tlačítkem OK.

Obrázek č.2.8: Volby editoru VBA

Rozsah proměnných

Všechny proměnné jsou lokální vrámci kódu (funkce nebo makra), kde jsou používány. Mimo tento kód neexistují. Je tedy možné používat např. ve dvou různých funkcích dvě proměnné se stejným názvem.

Jestliže na začátek modulu zapíšete příkaz

Public <název proměnné>,

je takto deklarovaná proměnná společná pro celý sešit, tj. všechny moduly. Obdobně příkaz Static deklaruje proměnnou, která je uvnitř funkce nebo makra lokální, avšak po ukončení kódu si svou hodnotu zachová. Obyčejné proměnné po ukončení kódu svou hodnotu ztrácí.

Uživatelskou funkci můžete využívat nejen vbuňkách, ale také vkódu VBA, tedy vdalších funkcích nebo makrech. Jestliže vprvním řádku kódu funkce nahradíte klauzuli Public klauzulí Private, funkce bude existovat pouze vrámci programového modulu, kde byla zapsána. Jinde ji použít nelze ajejí název nebude zobrazen vokně pro vložení funkce do buňky. Příklad:

Private Function DPH_lokalni_v_modulu(castka)

DPH_lokalni_v_modulu = castka * 0.2

End Function

Proměnné sindexem

Ve Visual Basic můžete používat také proměnné, obsahující několik hodnot. Potřebnou část proměnné určíte pomocí celočíselného indexu. Jako hodnotu indexu můžete také použít proměnnou nebo aritmetický výraz.

Indexovanou proměnnou je nutné vždy deklarovat avdeklaraci určit povolený rozsah indexu, např.:

Dim sazby_DPH(1 To 2)

sazby_DPH(1) = 0.1

sazby_DPH(2) = 0.2

vypoctena_DPH = vstup * sazby_DPH(2)

Proměnná může mít ivíce indexů ajejich rozsah může obsahovat libovolné celé číslo, např.:

Dim promenna(1 To 5, -2 To 2)

Pro vytvoření indexované proměnné obsahující konstanty je možné použít také příkaz Array, který má tvar:

promenna = Array(hodnota1, hodnota2, … )

Tímto jediným příkazem nahradíte deklaraci Dim apotřebné dosazovací příkazy. Indexování proměnné, vytvořené příkazem Array, se počítá od nuly. Výpočet DPH by potom vypadal takto:

sazby_DPH = Array(0.1, 0.2)

vypoctena_DPH = vstup * sazby_DPH(1)

Automatické indexování od nuly je nezvyklé ačasto vede kchybám. Ve Visual Basic je však velmi rozšířené aje třeba si na něj zvyknout.

Vytvořte funkci, která počítá rovnoměrný odpis pro zadanou hodnotu investice aodpisovou skupinu. Vprvním roce se odepisuje určený podíl, vdalších letech poměrná část zbytku. Pro jednotlivé odpisové skupiny jsou doby odpisu 4, 6, 12, 20 a30 let apodíly pro výpočet odpisu vprvém roce jsou 14,2%, 8,5%, 4,3%, 2,15% a1,4%. Vsešitu Uzivatelske_funkce.xlsm je tato funkce vytvořena pod názvem Odpis_rovnomerny.

2.5Textové adatumové hodnoty

Vkódu funkcí amaker je možné používat také textové hodnoty, uváděné vuvozovkách:

mesic = "leden"

Pro sloučení dvou textů použijte znak & nebo znaménko sčítání:

obdobi = mesic+" 2010"

2.6Další možnosti funkcí

Table of Contents

Úvod

1. Tvorba makra záznamem

1.1 Použití maker v Excelu

1.2 Záznam a spuštění makra

1.3 Různé způsoby spuštění makra

1.4 Spuštění makra v jiném sešitu

2. Tvorba uživatelských funkcí

2.1 Práce s editorem Visual Basicu

2.2 Tvorba uživatelské funkce a její použití

2.3 Větvení kódu

2.4 Deklarace proměnných

2.5 Textové a datumové hodnoty

2.6 Další možnosti funkcí