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

Konec ukázky

Table of Contents

Tiráž

Ú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

2. Tvorba uživatelských funkcí

3. Tvorba makra kódem a jeho spuštění

4. Vlastnosti buněk a oblastí

5. Metody buněk a oblastí

6. Práce s listy, sešity a aplikací Excelu

7. Pokročilé programovací techniky

8. Práce s kontingenčními tabulkami

9. Práce s grafy

10. Využití událostí

11. Tvorba uživatelských formulářů

12. Použití kódu ve formuláři

13. Spolupráce Excelu s jinými programy

14. Úprava sešitu pro využití jinými uživateli

15. Úvod do tříd