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 2013 a 2016
Záznam, úprava a programování maker

Vydala Grada Publishing, a.s.
U Průhonu 22, 170 00  Praha 7
tel.: +420 234 264 401, fax: +420 234 264 400
obchod@grada.cz, www.grada.cz
jako svou 6982. publikaci

Spoluautor Michal Bureš
Odpovědná redaktorka Věra Slavíková
Sazba Petr Somogyi
Formát ePub vytvořil Petr Somogyi
Ikony Freepik (www.flaticon.com)
Počet stran 208
První vydání, Praha 2018
Vytiskly Tiskárny Havlíčkův Brod a.s.

© Grada Publishing, a.s., 2018
Cover Design © Grada Publishing, a. s., 2018
Cover Photo © Depositphotos/mountainpix

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

ISBN 978-80-271-2130-4 (ePub)
ISBN 978-80-271-2129-8 (pdf)
ISBN 978-80-271-0895-4 (print)

Úvod

Knížka, kterou právě otvíráte, se zabývá programováním v jazyku Visual Basic for Applications v tabulkovém kalkulátoru MS Excel 2016. Programovací postupy, popisované v knize, jsou použitelné i v předchozích verzích Excelu (2013, 2010 a 2007), ale také v aktuální verzi 2019. Případné odchylky mezi verzemi (kterých je při tvorbě maker poměrně málo) jsou v knize vždy uvedeny. Nepředpokládáme žádné předběžné znalosti programování, pouze znalost práce s kalkulátorem Excel na uživatelské úrovni.

Kniha vychází již ve třetím vydání; předchozí vydání jsou již rozebrána. To je pro nás na jedné straně potěšitelné, na druhé straně to představuje určitý závazek, aby třetí vydání bylo pro čtenáře co největším přínosem. Text knihy proto nevznikl jednoduchou úpravou na verzi Excelu 2016, ale provedli jsme důkladné přepracování značné části textu a jeho doplnění o pasáže, které jsou podle našich zkušeností v praxi užitečné. Mnoho nového materiálu bylo přidáno a část byla vypuštěna. V knize už nejsou obsaženy příkazy VBA pro tvorbu kontingenčních tabulek z oddělených datových oblastí a tvorbu minigrafů. Vynechali jsme také značně složité příkazy VBA pro tvorbu relací mezi tabulkami; tato akce se bude nepochybně provádět především uživatelským způsobem.

První kapitola věnována tvorbě maker záznamem a různým způsobům jejich spouštění.

Druhá kapitola popisuje základy jazyka Visual Basic. Na rozdíl od předchozích vydání zde uvádíme tvorbu maker a uživatelských funkcí souběžně. Poznatky, které zde získáte, můžete uplatnit i při programování v jiných produktech kompletu MS Office, zejména v Accesu.

Další kapitoly se pak zabývají vlastním použitím jazyka VBA při práci se sešity Excelu. Kapitoly 3 až 7 obsahují základy této práce, kapitoly 8 až 12 popisují pokročilé a speciální postupy, zahrnující také práci s grafy a kontingenčními tabulkami. Oproti předchozím vydáním jsou v knize zařazeny možnosti jazyka VBA pro analýzu a kontrolu dat (kapitola 9) a příkazy pro práci se soubory a vnějšími daty včetně práce s připojenými tabulkami, importu textových a XML souborů a exportu dat do formátu PDF (kapitola 10). Domníváme se, že tento rozsah knihy pokryje převážnou většinu toho, co budete od programování v Excelu očekávat.

Významným rozšířením 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 třináctá a čtrnáctá kapitola.

Poslední tři kapitoly jsou spíše doplňkové. V patnácté kapitole se dozvíte o možnosti spolupráce Excelu s jinými aplikace kompletu MS Office, tj. Wordem, Accessem a Outlookem. Šestná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 úpravě a tvorbě ovládacích karet), zajištění kódu heslem a tvorbě a využití vlastních doplňků. 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í.

Ke každé kapitole s výjimkou první jsme 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 2016 a můžete si je stáhnout v sekci této knihy na webu nakladatelství Grada (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.

V knize jsou použita následující typografická označení:

■Příkazy jazyka VBA jsou odlišeny písmem Courier.

■Příkazy Excelu a editoru jazyka VBA jsou zvýrazněny tučným písmem.

■Názvy karet, souborů a složek jsou zvýrazněny tučnou kurzívou.

■Klávesové zkratky jsou zvýrazněny KAPITÁLKAMI.

Ať se Vám daří!

Autoři

1.Tvorba maker 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 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ě, uložit je pod zvoleným názvem a podle potřeby tyto akce kdykoliv zopakovat. Tato schopnost Excelu vám ušetří spoustu práce a zabrání zbytečným chybám.

Akce, prováděné při záznamu makra, si Excel převede do programového kódu v jazyce Visual Basic for Application (VBA), jehož editor je součástí každého sešitu[1]. Makro můžete vytvořit 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 zapíšete potřebný kód.

Je možné používat i kombinaci obou způsobů – zaznamenat makro uživatelsky a později jej pozměnit úpravou vytvořeného kódu.

Při uživatelském přístupu může makro obsahovat pouze akce, které jste schopni provádět běžným způsobem. Programátorský přístup poskytuje další možnosti:

■Využívat ke zpracování údajů způsoby, který by byly při běžné práci 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 potřebná karta Vývojář. Není-li karta zobrazena, postupujte takto:

1.Klepněte na kartu Soubor.

2.Vpravo dole klepněte na příkaz Možnosti. Tím se zobrazí okno 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.

Obrázek 1.1: Zobrazení karty Vývojář

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

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

K práci s makry jsou určena tlačítka, která jsou na kartě Vývojář umístěna vlevo ve skupině Kód. 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áznam makra ukončí a obnoví se normální režim práce se sešitem.

Obrázek 1.3: Vypnutý a zapnutý relativní záznam makra

Makro zpravidla zahrnuje i pohyb kurzoru a bude používáno u různých buněk, proto většinu maker vytvoříte se zapnutým relativním záznamem. Tlačítko Relativní odkazy je po spuštění Excelu vypnuté a musíte jej stisknout před zahájením vlastního záznamu.

Při záznamu makra 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 toto nastavení (např. změna písma 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

1.2.1Ulož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.xlsb 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.xlsb (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. Pokud vytvářené makro uložíte do tohoto sešitu a nechcete, aby bylo všude přístupné, jedinou možností je sešit Personal.xlsb vymazat.

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: Okno se seznamem maker

1.2.2Ulož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í. To platí pro nově vytvořený sešit i pro sešit s příponou XLSX, ve kterém chcete dodatečně vytvořit makro.

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. Toto povolení se do sešitů s makrem zapíše, 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 v sešitu

1.2.3Odstraně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 zobrazíte seznam maker, v seznamu označte potřebné 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. Jestliže nechcete makro upravit přímo v kódu, musíte jej odstranit a zaznamenat znovu.

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.

1.3.1Spuš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.

V klávesových zkratkách 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 prakticky celou abecedu. Klávesová zkratka CTRL + Q, která spolehlivě fungovala v dřívějších verzích Excelu, není od verze 2013 již 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.

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

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.

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

1.3.2Tlačí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 makro a klepněte na tlačítko Přidat.

5.V rozvíracím seznamu v pravé horní části okna nastavte, kde se má vytvořené tlačítko zobrazovat. Výchozí volbou je Pro všechny dokumenty (výchozí), pak se tlačítko zobrazení při každém spuštění Excelu. Při volbě Pro dokument + název sešitu se tlačítko zobrazí na panelu Rychlý přístup jen při otevření sešitu, kde bylo makro vytvořeno.

6.Potvrďte tlačítkem OK.

U vytvářeného tlačítka můžete také změnit ikonu a upravit text bublinkové nápovědy, která se u tlačítka zobrazuje. Před uzavřením okna pro úpravu panelu Rychlý přístup označte v pravém seznamu přidané makro a klepněte na tlačítko Změnit. V dalším zobrazeném okně vyberte vhodnou ikonu, podle potřeby upravte zobrazovaný text a potvrďte tlačítkem OK.

Obrázek 1.9: Změna ikony a textu u tlačítka

1.3.3Př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 rozvíracím seznamu Zvolit příkazy nastavte volbu Makra a tlačítkem Přidat zařaďte do skupiny nové tlačítko.

6.Vytvořené tlačítko označte a klepněte na tlačítko Přejmenovat. Vyberete 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í.

1.3.4Př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.

Na vytvořené tlačítko 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í.

Obrázek 1.11: Nabídka pro vytvoření prvku na listu

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

Makro, vytvořené v jednom z otevřených sešitů je možné spustit v kterémkoliv jiném sešitu, otevřeném v rámci jednoho běhu Excelu. 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í. Sešit s makrem však nesmí být přejmenován nebo přesunut do jiného umístění.

1Mezi jazykem VBA v jednotlivých verzích Excelu, počínaje verzí 2007, není téměř žádný rozdíl. Proto makra, zaznamenaná v jedné verzi lze zpravidla bez problémů spouštět ve verzi druhé.

2.Základy jazyka VBA

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

■práci s editorem VBA,

■vytvoření makra a uživatelské funkce a jejich využití,

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

■práci s proměnnými a tvorbu vzorců,

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

■použití cyklů.

Příklady v této kapitole naleznete v sešitu Zaklady_VBA.xlsm.

2.1Práce s editorem VBA

Záznamník maker vytváří kód v programovacím jazyku Visual Basic for Application (VBA). V tomto jazyku můžete vytvářet také vlastní kódy dvojího typu:

■Procedury (Sub), které použijete stejně jako makra, vytvořené záznamem.

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

Aby bylo možné v sešitu vytvořit nějaký kód, musíte nejprve do sešitu vložit programový modul. Při tvorbě makra pomocí záznamu se programový modul vloží do sešitu automaticky, při tvorbě kódu zápisem použijte tento postup:

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.V okně použijte příkaz Insert→Module.

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

5.Klepněte vpravo do podokna modulu. Tím je editor kódu připraven k práci.

Pro všechny názvy v editoru VBA (moduly, makra, funkce, proměnné atd.) používejte pouze písmena bez české diakritiky, číslice nebo podtržítko. Nepoužívejte mezery a další interpunkční znaky.

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

■Podokno projektu (vlevo nahoře) obsahuje seznam listů v sešitu, položku ThisWorkbook (což představuje celý sešit), složku Modules, popř. složku Forms s uživatelskými formuláři. Jestliže chcete zobrazit kód některého z modulů, poklepejte na jeho název.

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

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

Obrázek 2.2: Okno editoru VBA

Editor VBA není počeštěn. 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č v seznamu používejte čárku (nikoliv středník).

Tato pravidla platí v anglické i české verzi Excelu.

Při zápisu kódu postupujete obdobně, jako při práci s poznámkovým blokem. Jednotlivé příkazy ukončíte klávesou ENTER. Prázdné řádky a odsazení od levého okraje (mezery, tabulátor) jsou ignorovány. Klávesové zkratky CTRL+C, CTRL+X a CTRL+V můžete použít ke zkopírování, vyjmutí a vložení označeného textu.

Při rozdělení příkazu na dva řádky ukončete první část mezerou a podtržítkem a další řádek je pak chápán jako pokračování. Řádky uvozené apostrofem jsou komentáře a při provádění kódu se ignorují. 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 maker, funkcí, proměnných a odkazy na prvky v sešitu (buňky, listy atd.).

■Zelenou barvou komentáře.

■Červenou barvou chybné řádky.

Obrázek 2.3: Barvy v kódu VBA

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

2.2Tvorba kódu VBA

Vložení kódu do modulu provedete těmito kroky:

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

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

3.V zobrazeném okně označte typ vkládaného kódu (Sub – makro).

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

5.Potvrďte tlačítkem OK.

Obrázek 2.4: Vložení kódu do modulu

Tím se do modulu vloží první a poslední řádek kódu makra:

Public Sub Nazev_makra()

End Sub

V jednom modulu lze vytvořit více maker nebo funkcí a jejich kód je oddělen vodorovnou čárou.

Mezi první a poslední řádek zapište vlastní výkonné příkazy. Při psaní kódu je vhodné dodržovat následující pravidla:

■Všechny příkazy pište malými písmeny. Pokud je příkaz zapsán správně, po opuštění zapisovaného řádku se počáteční písmena klíčových slov jazyku VBA změní na velká.

■Pokud je možné kód rozdělit do několika logických celků, oddělte je prázdnou řádkou.

■Jednotlivé příkazy kódu jsou velmi často vnořovány do sebe. Vnořené příkazy odsaďte odleva pomocí mezer nebo tabulátoru.

■Při tvorbě vnořených příkazů zapište nejprve začátek a konec a teprve potom vnořené řádky. Jedna z nejčastějších chyb totiž je, že při psaní vnořených příkazů zapomenete na konec.

■Do kódu si hned zapisujte komentáře; využívejte přitom možnosti psát komentář za příkaz do jednoho řádku.

■Pokud chcete některý příkaz vyřadit, je mnohdy výhodnější jej nemazat, ale zapsat před něj apostrof. Tím se z něho stane komentář. Odstraněním apostrofu se příkaz znovu aktivuje.

Makro zpravidla pracuje s buňkami; jak se na buňky odvolat, se dozvíte v následující kapitole. Pro tvorbu jednoduchého makra zatím postačí zpráva na obrazovku, který má tvar:

MsgBox „Text zprávy“

Příkaz MsgBox zobrazí okno s textem zprávy a tlačítkem OK. Pomocí tohoto příkazu je možné zobrazovat i hodnotu nějaké proměnné:

promenna = 10

MsgBoxpromenna

Tuto možnost příkazu MsgBox využijete zejména při ladění maker.

Vytvořené makro spustíte stejně, jako při tvorbě makra záznamem, např. tlačítkem Makra na kartě Vývojář. Další možností je spuštění makra přímo z editoru VBA: postavte kurzor do kódu makra a na panelu nástrojů klepněte na tlačítko Run s obrázkem zelené šipky. Po provedení makra se zobrazí opět okno editoru VBA.

Obrázek 2.5: Ovládací panel editoru VBA

2.2.1Chyba při provádění makra

Pokud po spuštění makra nastane chyba, zobrazí se okno s chybovou zprávou. Klepnutím na tlačítko End běh makra ukončíte. Klepnutím na tlačítko Debug zobrazíte okno editoru VBA a řádek, ve kterém běh makra zhavaroval, je zvýrazněn žlutým pozadím.

Obrázek 2.6: Chybové hlášení při běhu makra

Obrázek 2.7: Kód VBA v režimu Debug

Tímto zobrazením editor VBA signalizuje speciální chybový režim, ve kterém můžete kód makra opravovat, ale nemůžete makro znovu spustit. Pro opětovné spuštění opraveného makra nejprve klepněte na tlačítko Reset s ikonou modrého čtverečku. Obrázek šipky a žluté pozadí příkazu zmizí a nastaví se standardní režim, umožňující spouštění maker.

2.2.2Tvorba uživatelské funkce

Při tvorbě uživatelské funkce postupujte stejně, jako při tvorbě makra, pouze v okně pro vložení kódu označte volbu Function. Do okna kódu se vloží první a poslední řádek kódu funkce:

Public Function Nazev_funkce()

End Function

Funkce má zpravidla jeden nebo více vstupních parametrů. Zapíšete je do závorek v prvním příkazu funkce a oddě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 v kódu funkce:

Nazev_funkce = 20

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

Uživatelská funkce se nezařadí do seznamu maker. Do buňky ji vložíte obdobným způsobem, jako standardní funkci Excelu; v okně pro vložení funkce zvolíte skupinu Vlastní. Uživatelské funkce se však nezobrazují ve skupině Naposledy použité. Vzorce v buň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 a musíte si jej vynutit klávesou F9.

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

Vytvořenou funkci si můžete vyzkoušet také přímo v okně VBA. K tomu použijte příkaz View→Immediate Window. V zobrazeném ladícím okně zapište otazník, název funkce a hodnoty parametrů v závorkách. Řádek ukončete klávesou ENTER. Na dalším řádku se objeví výsledek.

Obrázek 2.9: Funkce v ladícím okně

2.2.3Nepovinné parametry ve funkci

Ve funkcích je možné používat také nepovinné parametry, uvozené klauzulí Optional. Za název parametru zapište přiřazení výchozí hodnoty:

Public Function Vypocet(castka, Optional clo = 0.12)

Pokud při vkládání funkce do buňky vynecháte druhý parametr, použije se hodnota 0,12. Nepovinné parametry musí být zapsány jako poslední.

2.2.4Přidání nápovědy k už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.V dalším zobrazeném okně klepněte do položky Popis, zapište text nápovědy a potvrďte tlačítkem OK.

5.Zavřete okno se seznamem maker.

Obrázek 2.10: Tvorba nápovědy k vlastní funkci

2.3Zápis vzorců

Tvorba vzorců ve Visual Basicu je obdobná, jako v Excelu. Použijte běžná znaménka +, -, *, /, ^ (umocňování), popř. kulaté závorky. V kódu můžete používat i různé proměnné, sloužící jako mezivýsledky. Jejich názvy vytvořte podle stejných pravidel, jako názvy modulů, maker nebo funkcí, tj. použijte písmena bez diakritiky, číslice nebo podtržítko. Vzorce vytvářené ve VBA se od vzorců v Excelu liší ve dvou bodech:

■Desetinná čísla zapisujete s tečkou.

■Visual Basic nepovoluje používat znak procent.

Např. funkce pro výpočet DPH by mohla vypadat takto:

Public Function DPH(castka)

vysledek = castka * 0.21

DPH = vysledek

End Function

2.3.1Datumové a časové hodnoty

Aktuální datum vrací proměnná Date. Tato proměnná je ve Visual Basicu automaticky vytvořená při spuštění Excelu. Zápis pevné datumové hodnoty proveďte ve tvaru „#měsíc/den/rok#”. Křížky v zápisu jsou nutné, jinak se jako výsledek použije dvojí dělení:

datum1 = 18 / 4 / 2018 ' Chybný zápis

datum2 = #4/18/2018# ' Správný zápis

Časovou hodnotu zadejte ve tvaru „#hodina:minuta:vteřina#”.

K datumové hodnotě je možné přičíst nebo odečíst určitý počet dnů. Je také možné odečíst dvě datumové hodnoty od sebe:

Public Sub Datum()

splatnost = Date + 15

MsgBox splatnost

od_zacatku_roku = Date - #1/1/2018#

MsgBoxod_zacatku_roku

End Sub

2.3.2Textové hodnoty

Textové konstanty zapište v uvozovkách:

obec = "Smržovka"

MsgBox obec

Slučování více textů dohromady proveďte znaménkem plus (+) nebo ampesandem (&). Slučování se provádí „na těsno“, proto je často třeba vložit do sloučeného textu ještě mezeru. Příklad:

obec = "Smržovka"

slouceno = obec + " v Jizerských horách"

MsgBox slouceno

Není však možné slučovat dohromady textové a číselné, popř. textové a datumové hodnoty. V takovémto případě musíte převést číslo nebo datum na text pomocí vhodné standardní funkce VBA.

2.3.3Logické hodnoty

Visual Basic používá dvě logické hodnoty: True (ano) a False (ne). V Excelu tomu odpovídají hodnoty PRAVDA a NEPRAVDA. Visual Basic může (obdobně jako v Excelu) využívat také vzorce, které mají charakter podmínky. Výsledkem vzorce je pak hodnota True nebo False podle toho, zdali je podmínka splněna nebo ne. Příklad:

Public Sub Zobraz_splatnost()

splatnost = #18/7/2018#

v_terminu = splatnost >Date

MsgBox v_terminu

End Sub

Vytvořte funkci, která počítá DPH a cenu s DPH ze zadané ceny a sazby DPH. Sazba DPH je nepovinná, výchozí hodnota je 21 %. V sešitu Zaklady_VBA.xlsm je tato funkce vytvořena pod názvem DPH.

2.4Deklarace proměnných

Proměnnou v kódu můžete použít, aniž byste se starali o její charakter. Typ proměnné se určí při prvním dosazení hodnoty. Je však také možné dopředu určit typ proměnné pomocí její deklarace. Deklaraci zapíšete příkazem:

Dim <proměnná> As <typ>

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

Obrázek 2.11: Kontextové menu

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

Typ

Význam

Rozsah hodnot

Byte

Celé číslo

0 až 255

Integer

Celé číslo

-32 768 až 32 767

Long

Celé číslo

± 2 147 483 647

Single

Desetinné číslo

± 3,4.10^38

Double

Desetinné číslo

± 1,79.10^308

Currency

Měna

Přibližně ± 9 bilionů

String

Text

Date

Datum

Boolean

True – False

Deklaraci proměnné je možné provést na libovolném místě kódu před jejím první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 v ně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ě. Toho můžete využít při zápisu pevného datumu do proměnné. Proměnnou nadeklarujete jako datumovou a pak do ní zapíšete textový řetězec s datumem. Datum může být v tomto případě zapsáno podle českých zvyklostí:

Dim datum As Date

datum = "18.7.2018"

Příkaz Dim může obsahovat více deklarací, ale nemůže deklarovat několik proměnných stejného typu najednou. Příkaz

Dim kod1, kod2 as Integer

je chybný, zatímco příkaz

Dimkod1 as Integer, kod2 As Integer

je správný. Deklaraci pomocí klauzule As je možné zapsat i do prvního příkazu funkce a deklarovat vstupní parametr jako proměnnou daného typu:

Public Function Lhuta_splatnosti(datum_vystaveni As Date)

2.4.1Povinná deklarace proměnných

Jestliže na začátek modulu (před zápis první 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. Jestliže je v sešitu vytvořeno více programových modulů, povinnost deklarovat všechny proměnné platí pouze v modulu, ve kterém je na začátku uvedený příkaz.

Práci editoru VBA je možné přizpůsobit tak, aby se uvedený příkaz vložil automaticky na začátek každého vytvářeného modulu:

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

2.Klepněte na záložku první karty Editor.

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

Obrázek 2.12: Nastavení editoru VBA

Rozhodnutí, zdali používat povinné deklarace proměnných nebo ne, je spíše věcí zvyku. V řadě případů však deklarace typu proměnné usnadňuje práci, i když povinnou deklaraci nepoužíváte.

2.4.2Rozsah proměnných

Všechny proměnné jsou lokální v rá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 tato 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živatelské funkce mají automaticky rozsah Public. Je možné je využívat nejen v buňkách, ale také v dalších funkcích nebo makrech. Jestliže v prvním řádku kódu funkce nahradíte klauzuli Public klauzulí Private, funkce bude existovat pouze v rámci programového modulu, kde byla zapsána. Jinde ji použít nelze a její název nebude zobrazen v okně pro vložení funkce do buňky. Příklad:

Private Function DPH_lokalní_v_modulu(castka)

DPH_lokalni_v_modulu = castka * 0.2

End Function

2.4.3Proměnné s indexem

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 a v deklaraci určit povolený rozsah indexu, např.:

Dim marze(1 To 3)

marze(1) = 0.12

marze(2) = 0.23

marze(3) = 0.29

vypoctena_marze = cena * marze(2)

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

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

Vytvoření indexované proměnné a její naplnění hodnotami můžete provést v jediném řádku s využitím příkazu Array, který má tvar:

promenna = Array(hodnota1, hodnota2, … )

Tento příkaz nahradí deklaraci Dim a potřebné dosazovací příkazy. Indexování proměnné, vytvořené příkazem Array, se počítá od nuly. Výpočet marže by potom vypadal takto:

marze = Array(0.12, 0.23, 0.29)

vypoctena_marze = cena * marze(2)

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

2.4.4Funkce, počítající několik hodnot

Proměnnou s indexem využijete také ve funkci, která vrací několik hodnot najednou. Výsledkem je maticový vzorec, zabírající dvě nebo více buněk vedle sebe. Pro vložení této funkce označte potřebné buňky, vyhledejte potřebnou funkci a vložení potvrďte s podrženými klávesami CTRL + SHIFT.

Při tvorbě tohoto typu funkce nejprve deklarujte indexovanou proměnnou o potřebném počtu složek. Návratové hodnoty zapište do jednotlivých částí této proměnné a do názvu funkce dosaďte tuto proměnnou jako celek (bez indexu):

Public Function Vraci_dve_hodnoty(vstup)

Dim vysledek(1 To 2)

vysledek(1) = vstup * 2

vysledek(2) = vstup * 4

Vraci_dve_hodnoty = vysledek

End Function

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

2.4.5Funkce s typem

První příkaz v definici uživatelské funkce může obsahovat klauzuli As a datový typ také za závorkami. Vracená hodnota má potom tento datový typ:

Vážení čtenáři, právě jste dočetli ukázku z knihy Programování v Excelu 2013 a 2016.
Pokud se Vám líbila, celou knihu si můžete zakoupit v našem e-shopu.