E-kniha: Programování v Excelu 2010 a 2013 -- záznam, úprava a programování maker - Marek Laurenčík

Marek Laurenčík

 Tvorba maker a editor jazyka VBA

 Vlastnosti a metody buněk a oblastí

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

 Pokročilejší programovací postupy

 Spolupráce Excelu s jinými aplikacemi

záznam, úprava

a programování

maker

Programování

v Excelu

www.grada.cz

Jako všechny předchozí verze Excelu, je i nejnovější verze Excel 2013 vybaven

vývojářskými nástroji jazyka Visual Basic for Application včetně editoru kódu. To

umožňuje vytvářet makra pomocí záznamu, a kromě toho vyvíjet vlastní programy

a funkce přímým zápisem kódu v jazyce VBA. Editor jazyka VBA vám umožní také

vytvářet a používat vlastní dialogová okna, doplňovat sešit Excelu o vlastní ovládací

prvky a vytvářet nové vlastní doplňky. Vytváření vlastního programovacího kódu ve

spojení s uživatelskými dialogovými okny vám umožní výrazně rozšířit možnosti

Excelu 2013, a to nejen pro svou potřebu, nýbrž i pro širší okruh uživatelů, např. pro

potřeby  remní sítě.

Tato knížka nepředpokládá žádné předběžné znalosti programování. S tvorbou

maker v prostředí Excelu 2013 se seznámíte od úplného začátku.

O autorech

Vedoucí autor knihy, Ing. Marek Laurenčík se (společně se spoluautorem Michalem

Burešem) výuce a vzdělávání v oblasti výpočetní techniky věnuje od roku 1992. Zabývá

se výukou a speciálními konzultacemi téměř všech běžně používaných kancelářských

programů. V posledních letech je vyhledávaným lektorem odborných manažerských

kurzů projektového řízení a manažerských nástrojů programu MS Excel. V těchto

programech se svými kolegy vyvíjí aplikace použitelné v oblasti obchodu a účetnictví.

Spolu s dalšími autory pravidelně publikuje na internetu v odborných článcích

týkajících se výpočetní techniky a je autorem mnoha publikací z oblasti MS Of ce.

Současně je autorem speciálních e-learningových kurzů doplňujících standardní znalosti

programu Excel (Excel a databáze, Excel a manažer, Excel a makra), práce s digitální

technikou, tvorbou internetových stránek a časopisů MS Of ce a Excel při ruce. Je

vedoucím týmu publikace Microsoft Excel v příkladech a Microsoft Of ce v příkladech.

Marek Laurenčík je současně spolumajitelem vzdělávací společnosti JUBELA, s.r.o.,

která k tématům uvedené v této knize pořádá celou řadu odborných kurzů – odzačá

tečnických, přes pokračovací až po specializované manažerské kurzy projektového

řízení a rozhodování. A tyto kurzy by vám, vážení čtenáři, prostřednictvím tohoto

svého díla rád nabídnul.

Ing. Michal Bureš, CSc. působí v oblasti školení počítačových kurzů od roku

1992. Má za sebou dlouholetou pedagogickou praxi na VŠCHT, následně jako letor IT

kurzů a dlouholetou praxi při školení uživatelů výpočetní techniky a tvorbě rozsáhlých

zakázkových přehledů a výkaznictví na bázi MS Excelu. V současnosti aktivně působí

jako lektor počítačových kurzů a věnuje se i publikační činnosti – spoluautor knihy

Microsoft Of ce v příkladech a internetových periodik v oblasti MS Of ce.

Kniha obsahuje tato témata:

 Tvorba maker pomocí záznamu.

 Tvorba vlastních funkcí.

 Práce s buňkami, listy a sešity.

 Pokročilé programovací techniky.

 Práce s grafy a kontingenčními

tabulkami.

 Tvorba uživatelských dialogových

oken.

 Využívání gra ckých prvků na listu.

 Tvorba vlastních ovládacích prvků

a doplňků.

 Práce s adresáři a soubory.

 Komunikace Excelu s dalšímiaplika

cemi MS Of ce.

Grada Publishing, a. s.

U Průhonu 22, 170 00 Praha 7

tel.: +420 234 264 401

fax: +420 234 264 400

e-mail: obchod@grada.cz

www.grada.cz

Programování

v Excelu

2010 a 2013

w ww.grada.cz

Marek

Lauren

č

ík

Programování v Excelu 2010 a 2013

Marek Laurenčík

záznam, úprava

a programování

maker

Programování

v Excelu Programování v Excelu 2010 a 2013 záznam, úprava a programování maker Marek Laurenčík 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 (tištěná verze) ISBN 978-80-247-8919-4 (elektronická verze ve formátu PDF) ISBN 978-80-247-8920-0 (elektronická verze ve formátu EPUB)

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 a šíř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.

Obsah 5

Obsah

1.

2.

3.

4.

5.

Úvod . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Tvorba makra záznamem

1.1 Použití maker v Excelu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

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

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

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

Tvorba uživatelských funkcí

2.1 Práce s editorem Visual Basicu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

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

2.3 Větvení kódu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

2.4 Deklarace proměnných . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

2.5 Textové a datumové hodnoty . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

2.6 Další možnosti funkcí . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

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

3.1 Tvorba makra pomocí kódu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

3.2 Odkazy na buňky a oblasti . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

3.3 Standardní funkce Visual Basicu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

3.4 Objektový model Excelu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

3.5 Další typy odkazů . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

3.6 Standardní dialogy VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

Vlastnosti buněk a oblastí

4.1 Identifikace buňky a oblasti . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

4.2 Vlastnosti pro vzhled buněk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

4.3 Zarovnání a slučování buněk, šířka řádků a výška sloupců . . . . . . . . . . . . . 46

4.4 Numerický formát buněk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

4.5 Tvorba vzorců . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

Metody buněk a oblastí

5.1 Označování a mazání buněk, práce s řádky a sloupci . . . . . . . . . . . . . . . . . . . 52

5.2 Kopírování a přesun . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

5.3 Vyhledávání a nahrazování . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

5.4 Rozdělení textu do sloupců . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

5.5 Plnění oblastí . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

5.6 Řazení a tvorba souhrnu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

6 Programování v Excelu 2010 a 2013

5.7 Filtrace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

5.8 Odstranění duplicitních hodnot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

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

6.1 Práce s listy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

6.2 Práce se sešity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

6.3 Aplikace Excelu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Pokročilé programovací techniky

7.1 Makro s parametry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

7.2 Odchycení chyb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

7.3 Práce se soubory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

7.4 Práce s komentáři a hypertextovými odkazy . . . . . . . . . . . . . . . . . . . . . . . . . . . 87

7.5 Formulářové prvky na listu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

7.6 Podmíněné formátování . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

7.7 Rozšířené řazení . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

7.8 Práce s konci stránek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

Práce s kontingenčními tabulkami

8.1 Objekty v kontingenční tabulce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

8.2 Spojení kontingenční tabulky s daty . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

8.3 Rozmístění polí . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

8.4 Výpočet a formátování hodnot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104

8.5 Tvorba kontingenční tabulky z několika datových oblastí . . . . . . . . . . . . 105

8.6 Tvorba kontingenční tabulky s využitím relací . . . . . . . . . . . . . . . . . . . . . . . 107

Práce s grafy

9.1 Objekty grafu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

9.2 Změna zdroje dat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112

9.3 Úprava os . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

9.4 Popisky v grafu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

9.5 Formátování datových řad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116

9.6 Přidání spojnice trendu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

9.7 Tvorba izolovaného grafu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

9.8 Tvorba minigrafů . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120

Využití událostí

10.1 Události listu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123

10.2 Události sešitu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

10.3 Události aplikace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

6.

7.

8.

9.

10.

Obsah 7

Tvorba uživatelských formulářů

11.1 Tvorba uživatelského formuláře a jeho spuštění . . . . . . . . . . . . . . . . . . . . . 129

11.2 Základní prvky na formuláři . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

11.3 Vzhled prvků na formuláři . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132

11.4 Další prvky na formuláři . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133

11.5 Tvorba seznamů . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

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

12.1 Události prvků na formuláři . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

12.2 Události a vlastnosti formuláře . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142

12.3 Vlastnosti prvků na formuláři . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

12.4 Přepínač a prvek RefEdit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146

12.5 Programování seznamů . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

12.6 Tvorba průvodce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152

12.7 Ovládání formuláře z kódu makra . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153

Spolupráce Excelu s jinými programy

13.1 Spuštění jiného programu v makru . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

13.2 Spolupráce Excelu s Wordem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

13.3 Spolupráce Excelu s Accessem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163

13.4 Spolupráce Accessu s Outlookem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167

13.5 Výměna dat ve formátu XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

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

14.1 Ochrana projektu heslem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

14.2 Tvorba a úprava ovládacích panelů . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176

14.3 Práce s pásem karet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179

14.4 Tvorba doplňku . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184

Úvod do tříd

15.1 Vytvoření třídy a její instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

15.2 Příklad využití třídy v kódu makra . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190

15.3 Příklad využití třídy v kódu formuláře . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

15.4 Využití modulu třídy pro programování událostí . . . . . . . . . . . . . . . . . . . . . 191

Závěr . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195

Rejstřík . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197

11.

12.

13.

14.

15.

Obsah 9

Ú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 programo -

vá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ůžeteuplat

nit 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ímitabul

kami) 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íteo 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ékapito

le 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áneknakladatel

ství Grada na adrese www.grada.cz. Odkazy na jednotlivé příklady naleznete v  jednotlivých kapi

tolá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

Tvorba makra záznamem 11

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.1 Použ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 Ba - sic 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.

12 Programování v Excelu 2010 a 2013

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

1.2 Zá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 jejponechá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.

Tvorba makra záznamem 13

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  auto -

matická 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šechnanastavení, 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 sevytvář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 Settingsuživatelské jménoData aplikací

MicrosoftExcelXLStart. Ve Windows 8 je sešit Personal uložen ve složce c:Users

uživatelské jménoappdataRoamingMicrosoftExcelXLSTART. Programování v Excelu 2010 a 2013 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 jejichnechtěnému spuštění. Klepnutím na tlačítko Makra zobrazte seznam maker, v seznamu označtekonkré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šakneumožní opakovat záznam makra, ale zobrazí jeho kód, zapsaný v jazyce Visual Basic.

Tvorba makra záznamem 15

1.3 Rů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. K lepně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. 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. K lepněte na kartu Soubor a vpravo dole klepněte na příkaz Možnosti.

2. V le vé čá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 pra vé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. P otvrďte dvakrát tlačítkem OK.

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

+

16 Programování v Excelu 2010 a 2013

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.

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ázevtlačí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.9: Změna ikony a názvu u tlačítka

Tvorba makra záznamem 17

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 sezař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. 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

Obrázek č. 1.11: Nabídka

pro vytvoření ovládacího

prvku na listu

18 Programování v Excelu 2010 a 2013

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.4 Spuš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 jednomz otevře

ný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éfun

guje 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 anikláve

sová 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 akceprove

dené 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,zaznamena

ná v jedné verzi, zpravidla bez problémů spouštět ve verzi druhé.

Tvorba uživatelských funkcí 19

2.

Tvorba uživatelských

funkcí

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

❚ práci s editorem VBA,

❚ vytvoření uživatelské funkce a její využití,

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

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

❚ práci s proměnnými.

Příklady v této kapitole naleznete v sešitu Uzivatelske_funkce.xlsm. 2.1 Práce s editorem Visual Basicu Makra, vytvářená záznamem, se v Excelu ukládají v programovacím jazyku Visual Basic forApplications (VBA). V tomto jazyku můžete vytvářet i vlastní 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. V tomto 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 do podokna modulu (vpravo). Tím je editor připraven k práci.

Obrázek č. 2.2: Okno editoru VBA