Excel - makra - IT lektor MS Office | Jaroslav Nedoma ... Makra.pdf · Skripta Vás provedou...

Microsoft Office 2010

Excel - makra Automatizace práce v MS Excel

Autor: Jaroslav Nedoma

© Jaroslav Nedoma

Jaroslav Nedoma – Microsoft Excel 2010 - makra 2

O KURZU

Excel 2010 – makra

Publikace je věnovaná všem, kteří si chtějí maximálně usnadnit práci v tabulkách aplikace

Excel. Tento materiál je připraven pro začátečníky z pohledu maker, avšak pro pokročilé uživatele

programu Excel po stránce běžných nástrojů tohoto programu.

Autor má za cíl provést čtenáře co možná nejjednodušší cestou přes úskalí tohoto programu.

Měli bychom si na školení ukázat řadu zjednodušujících cest, které vedou mnohdy na to stejné místo

jako dlouhá krkolomná řešení. Pravidlo zní, stručně, jasně a výstižně seznámit čtenáře s každou

kapitolou. Skripta Vás provedou spoustou zajímavých kapitol. Budeme se věnovat tvorbě maker od

úplného začátku a projdeme spolu tedy například rozdíly mezi absolutním a relativním záznamem

makra, tvorbou podmínek, cyklů a naučíme se tak vytvářet vlastní makra také pomocí

programovacího prostředí Visual Basic for Applications.

Je nutné poznamenat, že se nejedná o zcela vyčerpávající materiál, ale pouze o doprovodná

skripta ke školení. Předpokládá se tedy, že po absolvování školení budete schopni tato skripta využít

na 100 % bez sebemenší překážky spolu s upřesňujícími poznámkami pořízenými na semináři.

O AUTOROVI

Jaroslav Nedoma

IT lektor Microsoft Office Jaroslav Nedoma úspěšně proškolil ke konci roku 2015

přes 5 000 osob v 6 000 hodinách výuky. Přitom školením se zabývá od roku 2009, kdy začínal jako

lektor kancelářského balíčku MS Office 2003. Dnes školí zaměstnance firem výhradně pro práci

verzemi MS Office 2010, 2013 a 2016. Jako lektor IT kurzů MS Office proškolil přes 90 velkých

a malých firem v rámci celé ČR.

Účastníci kurzů různých věkových skupin se pod vedením pana Nedomy naučili ovládat

kancelářské programy Word, Excel, Outlook, Access nebo PowerPoint a prakticky využívat všechny

funkce, které jim pomáhají řešit úkoly nejen v zaměstnání, ale i v domácnosti a volném čase.

Zaměstnavatelé oceňují zvýšení kvalifikace i efektivity jejich zaměstnanců.

Veškeré připomínky, dotazy, nápady k obsahu těchto skript směřujte přímo na autora

přes následující kontakty:

mob.: +420 724 782 336

e-mail: [email protected]

web: www.lektornedoma.cz

© Jaroslav Nedoma


OBSAH

O KURZU .................................................................................................................................................. 2

O AUTOROVI ............................................................................................................................................ 2

SEZNÁMENÍ S MAKRY .............................................................................................................................. 5

Využití maker ....................................................................................................................................... 5

Karta Vývojář ....................................................................................................................................... 5

Zabezpečení maker ............................................................................................................................. 6

Uložení sešitů s makry ......................................................................................................................... 7

ZÁZNAM MAKER ...................................................................................................................................... 8

Absolutní záznam ................................................................................................................................ 8

Relativní záznam.................................................................................................................................. 9

Přiřazení makra tlačítku ...................................................................................................................... 9

EDITOR JAZYKA VBA .............................................................................................................................. 11

Spouštění editoru jazyka Visual Basic for Applications ..................................................................... 11

Kde všude může být kód VBA ............................................................................................................ 11

Rozbor nahraných maker .................................................................................................................. 12

Hlavička makra .............................................................................................................................. 12

Komentáře ..................................................................................................................................... 12

Okno Immediate ................................................................................................................................ 12

Krokování........................................................................................................................................... 13

ÚVOD DO OBJEKTŮ VE VBA ................................................................................................................... 14

Principy programování v Excelu ........................................................................................................ 14

Odkazy na objekty v kódu ................................................................................................................. 14

Zkrácené odkazy na objekty .............................................................................................................. 14

Události ............................................................................................................................................. 15

ZÁKLADY JAZYKA VBA ............................................................................................................................ 16

Procedury a funkce ........................................................................................................................... 16

Příkazy a jejich zápis .......................................................................................................................... 16

Názvy objektů, proměnných a konstant - pravidla ....................................................................... 16

Datové typy proměnných a konstant ................................................................................................ 17

Datový typ ..................................................................................................................................... 17

Rozsah platnosti a životnost .......................................................................................................... 18

Deklarace proměnných ................................................................................................................. 18

Datový typ String ........................................................................................................................... 18

© Jaroslav Nedoma


Pole proměnných .............................................................................................................................. 19

Statická pole .................................................................................................................................. 19

Dynamická pole ............................................................................................................................. 19

Vícerozměrná pole ........................................................................................................................ 19

Operátory .......................................................................................................................................... 19

Aritmetické operátory ................................................................................................................... 19

Relační operátory .......................................................................................................................... 20

Spojovací operátory ...................................................................................................................... 20

Logické operátory .......................................................................................................................... 20

Rozhodovací bloky............................................................................................................................. 21

Příkaz Select-Case .......................................................................................................................... 21

Rozhodovací blok If-Then-Else ...................................................................................................... 21

Cykly .................................................................................................................................................. 23

Cykly For – Next ............................................................................................................................. 23

Cykly Do While .............................................................................................................................. 24

Speciální makra Auto_Open a Auto_Close........................................................................................ 25

Makro Auto_Open ......................................................................................................................... 25

Makro Auto_Close ......................................................................................................................... 26

MsgBox a InputBox................................................................................................................................ 27

MsgBox .............................................................................................................................................. 27

InputBox ............................................................................................................................................ 28

ZÁVĚR .................................................................................................................................................... 31

© Jaroslav Nedoma


SEZNÁMENÍ S MAKRY

Pokud v aplikaci Microsoft Excel opakovaně provádíte nějakou úlohu, můžete ji zautomatizovat

pomocí makra.

Makro je řada příkazů a funkcí uložených v modulu. Jde o kód vytvořený v jazyce Visual Basic for

Applications a lze ho spustit kdykoliv chcete danou úlohu provést.

Funkce je předepsaný vzorec, který převezme hodnotu nebo hodnoty, provede operaci a vrátí

hodnotu nebo hodnoty. Použitím funkcí se zjednoduší a zkrátí vzorce v listu, zvláště takové, které

provádějí dlouhé a složité výpočty. Funkce jsou tak šikovným pomocníkem, jak rozšířit seznam běžně

dostupných tabulkových funkcí o Vaše nové.

Modul je kolekce deklarací, příkazů a procedur uložená společně jako jedna pojmenovaná jednotka.

Existují dva typy modulů: standardní moduly a moduly tříd.

VYUŽITÍ MAKER

Makra využijeme, jak jsme si již vysvětlili v předchozích odstavcích, k automatizaci opakujících se akcí

(úkolů), například k:

Otevírání sešitu, vytištění jeho části a následné uzavření.

Otevření více sešitů najednou, sloučení jejich obsahu do nového sešitu a jeho uložení.

Vytváření grafů.

Stejné formátování buněk.

Tisk určité části sešitu.

Vytvoření vlastní funkce či příkazu.

Vytvořit celé nové aplikace.

KARTA VÝVOJÁŘ

Abychom mohli v Excelu s makry pracovat, je zapotřebí vyvolat na pásu karet kartu Vývojář

následovně:

1. Klepneme na kartu Soubor.

2. V levé části zvolíme volbu Možnosti.

3. Zobrazí se dialogové okno, ve kterém:

a. Zvolíme možnost Přizpůsobit pás karet (viz Obr. 1).

b. V pravé části okna zatrhneme položku Vývojář (viz Obr. 1).

c. Klepneme na tlačítko OK (viz Obr. 1).

© Jaroslav Nedoma


Obr. 1 Zobrazení karty Vývojář na pásu karet

ZABEZPEČENÍ MAKER

V rámci maker lze přechovávat také škodlivé programy, které mohou ohrozit Váš počítač. Je důležité

si rozmyslet, jak se má Excel zachovat v momentě, kdy otevíráme sešit s makry. Toto chování lze

nastavit následovně:

1. Na kartě Vývojář ve skupině Kód klepneme na tlačítko Zabezpečení maker.

2. Zobrazí se dialogové okno, ve kterém klepneme na tlačítko Nastavení maker a v pravé části

okna zvolíme požadovaný stupeň zabezpečení (viz Obr. 2):

a. Zakázat všechna makra bez oznámení.

b. Zakázat všechna makra s oznámením.

c. Zakázat všechna makra kromě digitálně podepsaných maker.

d. Povolit všechna makra (nedoporučuje se – viz odstavec výše).

© Jaroslav Nedoma


Obr. 2 Stupeň zabezpečení maker

ULOŽENÍ SEŠITŮ S MAKRY

Jestliže do sešitu zaznamenáme nebo naprogramujeme makra, musíme tento sešit uložit do formátu

Sešit aplikace Excel s povolenými makry (s příponou *.xlsm):

1. Klepneme na kartu Soubor.

2. V levé části klikneme na položku Uložit jako.

3. Sešit aplikace Excel s povolenými makry.

4. Zobrazí se dialogové okno, ve kterém:

a) Vybereme složku, do které chceme sešit uložit.

b) Do řádky Název souboru vložíme název sešitu.

c) Na řádku Uložit jako typ zvolíme možnost Sešit aplikace Excel s podporou maker.

d) Klepneme na tlačítko Uložit.

© Jaroslav Nedoma


ZÁZNAM MAKER

Záznam je jednodušší cestou k vytvoření vlastního makra bez složitých znalostí programování.

Při nahrávání maker je zaznamenávána veškerá činnost, kterou v Excelu provádíte. Pro nahrávání

maker má Excel vestavěn zvláštní záznamník. Excel nahrává všechny akce myši, stisk kláves, vyvolané

příkazy, otevřené dialogy atp.

Způsoby záznamu:

Absolutně – jestliže vyberete nějakou buňku, bude si Excel pamatovat její přesnou adresu.

Relativně – zaznamenává posun (např. o dvě buňky vpravo).

ABSOLUTNÍ ZÁZNAM

Příklad absolutního záznamu:

1. spusťte záznam maker;

2. aktivujte buňku A1;

3. zadejte do buňky A1 pondělí;

4. přesuňte se na buňku A2 a zadejte úterý;

5. tento postup opakujte, dokud nebudete mít v oblasti A1:A7 zadáno v šech sedm dní týdne;

6. klepnutím znovu aktivujete buňku A1;

7. zastavte záznam makra.

Vygenerovaný kód Excelem:

Sub Makro1()

Range("A1").Select

ActiveCell.FormulaR1C1 = "Pondělí"

Range("A2").Select

ActiveCell.FormulaR1C1 = "Úterý"

Range("A3").Select

ActiveCell.FormulaR1C1 = "Středa"

Range("A4").Select

ActiveCell.FormulaR1C1 = "Čtvrtek"

Range("A5").Select

ActiveCell.FormulaR1C1 = "Pátek"

Range("A6").Select

ActiveCell.FormulaR1C1 = "Sobota"

Range("A7").Select

ActiveCell.FormulaR1C1 = "Neděle"

Range("A1").Select

End Sub

© Jaroslav Nedoma


RELATIVNÍ ZÁZNAM

Příklad relativního záznamu:

V některých případech budete chtít, aby vaše makro pracovalo s umístěním buněk v relativním

smyslu. Mohli byste chtít, aby takové makro začalo vyplňovat názvy dní v té buňce, která je zrovna

aktivní. V takovém případě budete potřebovat záznam makra s relativními odkazy na buňky.

Postup bude následující:

1. aktivujte buňku A1;

2. spusťte záznam maker;

3. klepněte na tlačítko Relativní odkaz na kartě Vývojář, čímž změníme režim záznamu

na relativní;

4. do oblasti A1:A7 zadejte názvy dní v týdnu stejně jako v prvním případě;

5. vyberte buňku A1;

6. zastavte záznam.

Vygenerovaný kód Excelem:

Sub Makro2()

ActiveCell.FormulaR1C1 = "Pondělí"

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "Úterý"


ActiveCell.FormulaR1C1 = "Středa"


ActiveCell.FormulaR1C1 = "Čtvrtek"


ActiveCell.FormulaR1C1 = "Pátek"


ActiveCell.FormulaR1C1 = "Sobota"


ActiveCell.FormulaR1C1 = "Neděle"

ActiveCell.Offset(-5, 0).Range("A1").Select

End Sub

PŘIŘAZENÍ MAKRA TLAČÍTKU

Abychom nemuseli spouštět makro přes nástroj Makra na záložce Vývojář, můžeme tuto činnost

přenechat tlačítku či jakémukoliv objektu následovně:

1. Klepnutím na tlačítko nebo grafický ovládací prvek zobrazíte úchyty pro změnu velikosti.

2. Jestliže je objekt vybrán, klepněte na něj pravým tlačítkem myši.

3. Klepněte v místní nabídce na příkaz Přiřadit makro.

4. Proveďte jeden z následujících kroků:

a. Chcete-li k tlačítku nebo grafickému objektu přiřadit existující makro, zadejte název

makra do pole Název makra a klikněte na tlačítko OK.

© Jaroslav Nedoma


b. Klepnutím na tlačítko Záznam zaznamenáte nové makro a přiřadíte ho k vybranému

grafickému objektu. Po dokončení nahrávání makra klepněte na pásu karet Vývojář

v oblasti tlačítek Kód na tlačítko Zastavit záznam.

c. Pokud chcete vytvořit nové makro v editoru jazyka Visual Basic for Applications,

klepněte na tlačítko Nové.

d. Jestliže chcete upravit existující makro, klepněte v seznamu Název makra na název

makra a potom na tlačítko Upravit.

© Jaroslav Nedoma


EDITOR JAZYKA VBA

Je program, který má uživatelům usnadnit zápis a úpravu kódu makra.

SPOUŠTĚNÍ EDITORU JAZYKA VISUAL BASIC FOR APPLICATIONS

Editor jazyka Visual Basic for Applications (Obr. 3) se spouští na kartě Vývojář ve skupině Kód volbou

Visual Basic nebo stiskem klávesová kombinace ALT + F11.

Obr. 3 Editor jazyka VBA

KDE VŠUDE MŮŽE BÝT KÓD VBA

V modulech kódu pro jednotlivé listy: Může obsahovat speciální procedury, vázané

na konkrétní list, jež jsou spouštěny při výskytu nějaké události.

V modulu kódu ThisWorkbook: může obsahovat speciální makra, která se automaticky

spustí při otevření sešitu, před jeho vytištěním nebo před uzavřením.

V modulech tříd.

Ve formulářích.

© Jaroslav Nedoma


ROZBOR NAHRANÝCH MAKER

Máme spuštěný editor, v okně kódu vidíme naše nahraná makra a konečně tedy můžeme

prozkoumat, čím se navzájem liší a jak vlastně takový programový kód vypadá.

Text maker má různé barvy: toto nám usnadňuje orientaci v programovém kódu.

Téměř vše je anglicky: na češtinu narazíte jen ve vlastních názvech maker a v komentářích.

Hlavička makra

Hlavičku makra tvoří dva řádky, které jsou v makru vždy. Je to první a poslední řádek.

Sub Pokus()

End Sub

Název makra (v našem případě Pokus) je vždy doplněn závorkami. Do nich se zapisují

tzv. formální parametry.

Komentáře

První řádky makra jsou zeleně. Tato barva je implicitní barvou komentářů – neboli té části makra,

která slouží jako nápověda a vysvětlivky. Komentáře se při vykonávání makra ignorují.

Začátek komentáře je vždy vyznačen znakem apostrofu.

OKNO IMMEDIATE

Pro testy chování jednotlivých příkazů, funkcí či procedur máte dvě možnosti. Buď příkaz

či funkci umístíte do nějakého makra a tu pak spustíte, nebo to uděláte přímo v okně Immediate.

Okno Immediate otevřeme příkazem nabídky View – Immediate. Okno je standardně ukotveno

k dolnímu okraji okna editoru (Obr. 4).

Obr. 4 Okno Immediate

Zkuste zde napsat Beep a stisknout Enter. Jestliže máte v počítači zapojený reproduktor, ozve

se pípnutí.

Vestavěné příkazy ani vlastní procedury obvykle do okna Immediate nic nevypisují.

© Jaroslav Nedoma


KROKOVÁNÍ

Krokování programu se využívá jako základní prostředek nalezení chyb v programu. Během

krokování můžeme sledovat hodnoty proměnných v programu, správnost vyhodnocení

proměnných atd. Do režimu krokování se nejjednodušeji dostaneme zmáčknutím klávesy F8.

Během krokování se vždy provede jeden příkaz (viz Obr. 5 – žluté znázornění). Pokud chceme

vykonat následující příkaz, znovu zmáčkneme klávesu F8. Režim ladění je možné kdykoliv ukončit

tlačítkem Reset.

Obr. 5 Krokování programu

© Jaroslav Nedoma


ÚVOD DO OBJEKTŮ VE VBA

PRINCIPY PROGRAMOVÁNÍ V EXCELU

Veškerý kód jazyka VBA je uložen v modulech. Moduly jsou fyzicky uloženy v sešitech.

V modulu jsou jednotlivé procedury a funkce, které jsou nejmenší možnou jednotkou

obsahující programový kód.

Procedura nebo funkce provádí činnosti.

Na rozdíl od procedury, vrací funkce nějakou hodnotu, kterou můžete dále použít.

Pomocí jazyka VBA pracujete především s objekty, které aplikace obsahuje.

Jednotlivé třídy objektů dodržují určitou hierarchii, která je vyjádřena objektovým

modelem.

Objekty mohou vystupovat i jako kontejnery obsahující jiné objekty.

Některé objekty jsou obsaženy v kolekcích. Pojem kolekce označuje skupinu objektů

stejného typu.

ODKAZY NA OBJEKTY V KÓDU

Je jasné, že při práci s objekty pomocí kódu VBA musíte vždy zadat název objektu. To však

nestačí. Objekt se vždy vyskytuje na nějaké úrovni objektového modelu a v kódu je proto nutné

zadat i toto umístění.

Jednotlivé úrovně objektového modelu se v kódu vyjadřují pomocí tečkové konvence, kdy je

každá úroveň oddělena tečkou. Na nejvyšší úrovni stojí objekt Application.

Application.Workbooks(“pokus.xls“) 'odkaz na sešit pokus.xls

ZKRÁCENÉ ODKAZY NA OBJEKTY

Úplný odkaz na objekt má několik nevýhod:

Ve většině případů byste se upsali k smrti.

Orientace v takovém kódu je značně ztížená.

Z tohoto důvodu není nutné uvádět v odkazu na objekt všechny jeho „nadřízené“ objekty.

Jestliže však použijete zkrácený výpis, musíte si uvědomit, že Excel si vynechané objekty do

dotazu doplní sám, a to podle určitých pravidel.

U kolekce Workbook se není třeba ničeho obávat, protože nad ní je v odkazu už jen objekt

Application, a ten Excel nemůže nahradit ničím jiným. Takže následující dva řádky kódu jsou

významově shodné:

Application.Workbook(“pokus.xls“)

Workbook(“pokus.xls“)

© Jaroslav Nedoma


UDÁLOSTI

Událost je akce vyvolaná uživatelem nebo systémem, na kterou objekt dokáže zareagovat. Mezi

takové akce patří například otevření či uzavření sešitu, klepnutí či poklepání myší na objekt,

změna hodnoty v buňce apod.

© Jaroslav Nedoma


ZÁKLADY JAZYKA VBA

PROCEDURY A FUNKCE

Podprogram: je to sled příkazů, vykonávaný jako celek a tvořící uzavřenou jednotku. Běh spuštěného

programu je možné přerušit voláním jiného, po jehož vykonání se řízení vrací zpět do nedokončeného

volajícího programu.

Procedura: jedná se o podprogram, který provede sérii příkazů a skončí. Jedná se o libovolný sled

příkazů mezi řádky Sub a End Sub.

Příklad procedury:

Sub Test()

Sum = 1 + 1

MsgBox “Výsledek je “ & Sum

End Sub

Funkce: je to podprogram, který kromě toho, že provede nějaké příkazy, také vrací určitou hodnotu.

Tuto hodnotu můžete uložit do paměti počítače nebo ji zpracovat jiným způsobem, případně ji

ignorovat. Jedná se o libovolný sled příkazů mezi řádky Function a End Function.

Příklad funkce:

Function Secti(arg1, arg2)

Secti = arg1 + arg2

End Function

PŘÍKAZY A JEJICH ZÁPIS

Konvence říká, že na jeden řádek v kódu píšeme jeden příkaz. Je to nejpřehlednější způsob a vylučuje

některé chyby vzniklé zápisem.

Je-li příkaz moc dlouhý, lze jej rozdělit na více řádků. Na konci každého řádku je nutné napsat znak

pokračování řádku, což je mezera následovaná podtržítkem.

Názvy objektů, proměnných a konstant - pravidla

Musí začínat písmenem.

Nesmí obsahovat tyto znaky: mezera, &, #, @, $, %, !, čárka, tečka.

Neměli by obsahovat akcentované znaky.

Názvy nesmí být delší než 255 znaků, ale v praxi nepoužívejte více než 32 znaků.

Nemůžete použít dva stejné názvy v jednom rozsahu platnosti proměnné, či objektu.

© Jaroslav Nedoma


DATOVÉ TYPY PROMĚNNÝCH A KONSTANT

Název: viz výše.

Datový typ: vyjadřuje jaký typ hodnoty, můžeme do proměnné umístit a kolik místa v paměti zabere.

Platnost: určuje, které části aplikace mohou s proměnnou pracovat.

Životnost: specifikuje dobu existence proměnné.

Datový typ

Datový typ určuje, co všechno můžete do proměnné uložit, a také způsob, jakým budou data uložena

v paměti. Jestliže datový typ nezadáte, použije VBA výchozí typ Variant, který povoluje zadání všech

možných hodnot.

Typ Velikost Rozsah hodnot a význam

Byte 1 bajt Číslo 0 až 255; používá se pro ukládání binárních dat.

Integer 2 bajty Čísla v rozsahu -32 768 až 32 767. Základní typ pro práci s celými čísly.

Long 4 bajty Celá čísla v rozsahu -2 147 483 648 až 2 147 483 647.

Single 4 bajty Desetinná čísla s přesností na 6 desetinných míst. Rozsah možných hodnot je uveden v nápovědě.

Double 8 bajtů Desetinná čísla s dvojnásobnou přesností při výpočtu. Rozsah možných hodnot je uveden v nápovědě.

Currency 8 bajtů Číslo s pevným počtem 4 desetinných míst. Rozsah možných hodnot je uveden v nápovědě. Výpočty s tímto typem probíhají rychleji než u typu Single či Double.

Decimal 14 bajtů Tento typ není možné deklarovat, proměnnou tohoto typu lze získat jen převodem proměnné typu Variant. Tento typ je určen pro čísla s extrémním počtem desetinných míst.

String Různá Textové řetězce.

Boolean 2 bajty Logická hodnota True nebo False.

Date 8 bajtů Datum v rozsahu 1. leden 100 až 31. prosinec 9999.

Object 4 bajty Jakýkoliv odkaz na objekt.

Variant různá Základní typ, může obsahovat speciální hodnoty

Tab. 1 Datové typy

© Jaroslav Nedoma


Rozsah platnosti a životnost

Každá proměnná a konstanta má svůj rozsah platnosti určující, které podprogramy jsou schopny

s touto proměnnou pracovat (které ji vidí).

Lokální proměnné: mohou být použity pouze v tom podprogramu, v němž se vyskytují.

Modulové proměnné: jsou dostupné v rámci celého modulu a lze je proto využít třeba k uložení

hodnoty, která má být dostupná někdy v jiné proceduře.

Globální proměnné: mohou být použity ve všech modulech, neboli v celém sešitu.

Deklarace proměnných

Deklarací rozumíme určení jména, typu a platnosti proměnné předtím, než tuto proměnnou v kódu

použijeme. Ve VBA nemusíme lokálním proměnnou deklarovat, místo toho ji lze přímo v kódu

přiřadit nějakou hodnotu. Vystavujeme se však několika problémům:

Pokud není proměnná deklarována, je jí přiřazen typ Variant. To znamená vyšší spotřebu

místa v operační paměti.

Kdykoliv, když při zápisu jména proměnné uděláme chybu, vznikne nová proměnná.

Je výhodné nastavit editor VBA tak, aby deklaraci lokálních proměnných vynutil. Toto vynucením

zajistíte zapnutím volby Require Variable Declaration, kterou najdete v okně možností Tools,

Options na kartě Editor.

Vlastní deklaraci zajistí příkaz Dim:

Dim jmeno_promenne [As typ_promenne]

Public Sub Prvni()

Dim jmeno As String

jmeno = “Helena Obříková“

Druha

End Sub

Public Sub Druha()

MsgBox jmeno

End Sub

Datový typ String

Proměnlivá délka: Dim Ostrov As String

Pevná délka 20 znaků: Dim Zachrance As String * 20

© Jaroslav Nedoma


POLE PROMĚNNÝCH

Pole proměnných (array) je skupina prvků stejného datového typu, které mají stejný název.

Statická pole

Při deklaraci statických polí se udává v závorkách velikost pole (rozsah indexů pole):

Dim Osoba(15)

Dim Projekt(20)

Dim Údržba(1 To 20)

Dim Teplo(-100 To 100)

Dynamická pole

U dynamických polí se při deklaraci neuvádí velikost:

Dim DynamickePole()

Před použitím takového pole se zadá příkaz ReDim s konkrétním rozsahem indexů.

ReDim DynamickePole(1 To 100)

ReDim DynamickePole(zacatek To konec)

Vícerozměrná pole

Pole mohou mít více dimenzí. Můžete tedy používat dvojrozměrné či vícerozměrné pole.

Deklarace:

Dim ViceRozmeru(-20 To 20, 1 To 15)

Přiřazení hodnoty do prvku pole:

Udrzba(4) = “Horák“

OPERÁTORY

VBA obsahuje několik typů operátorů (aritmetické, logické, relační a spojovací). V následujících

tabulkách (Tab. 2, Tab. 3, Tab. 4, Tab. 5) uvedu pouze jejich přehled a příklad použití.

Aritmetické operátory

Operátor Význam

^ - umocnění (výsledek = číslo ^ exponent)

* - násobení (výsledek = číslo1 * číslo2)

/ - dělení (výsledek = číslo1 / číslo2)

© Jaroslav Nedoma


\ - celočíselné dělení (výsledek = číslo1 \ číslo2)

Mod - zbytek po celočíselném dělení (výsledek = číslo1 Mod číslo2)

+ - sčítání (výsledek = výraz1 + výraz2)

- - odečítání (výsledek = číslo1 – číslo2)

Tab. 2 Aritmetické operátory

Relační operátory

Operátor Význam

< - menší než

<= - menší nebo rovno

> - větší než

>= - větší nebo rovno

<> - nerovná se

= - rovná se

Is - používá se k porovnání odkazů na objekty ve dvou proměnných.

Like - používá se k porovnání dvou řetězců.

Tab. 3 Relační operátory

Spojovací operátory

Operátor Význam

& - používá se k vynucení spojení řetězců dvou výrazů (výsledek = výraz1 & výraz2)

+ - spojení dvou výrazů, pokud alespoň jeden typu string (výsledek = výraz1 + výraz2)

Tab. 4 Spojovací operátory

Logické operátory

Operátor Význam

And - logický součin dvou výrazů. Podmínka je True, pokud oba výrazy jsou True

Eqv - logická shoda dvou výrazů. Podmínka je True, pokud jsou oba výrazy stejné

© Jaroslav Nedoma


Not - logická negace výrazu. Podmínka je True, pokud výraz je False

Or - logické nebo dvou výrazů. Podmínka je True, pokud alespoň jeden výraz je True

Xor - exluzivní logické nebo. Podmínka je True, pokud jeden výraz je True, druhý False

Tab. 5 Logické operátory

ROZHODOVACÍ BLOKY

V mnoha případech je nutné vykonávat v kódu různé věci v závislosti na určité podmínce.

Příkaz Select-Case

V tomto případě učiníte vyhodnocení určitého výrazu a v závislosti na tom, jakou má tento výraz

hodnotu, provedete jednotlivé větve.

' procedura s jedním vstupním parametrem

Public Sub Vypocet(x As Integer)

Select Case x

Case 0, 1, 2

' zde bude jedna skupina příkazů

Case 3

' zde bude další skupina příkazů

Case Else

' zde bude poslední skupina příkazů

End Select

End Sub

Rozhodovací blok If-Then-Else

Nejčastěji používanou skupinou příkazů ve VBA je If – Then (Obr. 6). Tato běžná instrukce představuje

jeden ze způsobů jak vaši aplikaci opatřit „schopností rozhodování“.

Syntaxe:

If podmínka Then

[příkazy_pro_splněno]

[Else If podmínka – n Then]

[alternativní_příkazy – n]

[Else]

[příkazy_pro_standardní_případ]

End If

© Jaroslav Nedoma


Obr. 6 Podmínka If-Then-Else

Příklad:

Sub Pozdrav()

If Time < 0.5 Then

MsgBox “Dobré dopoledne“

ElseIf Time >= 0.5 And Time < 0.75 Then


ElseIf Time >= 0.75 Then

MsgBox “Dobrý večer“

End If

End Sub

Ekvivalentní zápis:

Sub Pozdrav()

If Time <0.5 Then


Else

If Time >= 0.5 And Time < 0.75 Then


Else

If Time >= 0.75 Then

MsgBox “Dobrý večer“

End If

End If

End If

End Sub

© Jaroslav Nedoma


CYKLY

Potřebujeme-li určitou část kódu provádět opakovaně, uděláme to nejlépe pomocí cyklu.

Cykly For – Next

Známe-li počet požadovaných opakování, použijeme příkaz For…Next (Obr. 7).

Cyklus s pevným počtem opakování:

Obr. 7 Cyklus For-Next

Syntaxe:

For počítadlo = počátek To konec [Step krok]

[příkazy]

Next [počítadlo]

Příklad:

Potřebujeme sečíst hodnoty z padesáti buněk ve sloupci A počínaje buňkou A1.

Sub SoucetHodnot()

Range("A1").Select

soucet = 0

For i = 1 To 50

a = ActiveCell.Value

soucet = soucet + a

ActiveCell.Offset(1, 0).Select

Next i

MsgBox soucet

End Sub

© Jaroslav Nedoma


Cykly Do While

Tento cyklus se provádí, dokud je splněna podmínka.

Cyklus s podmínkou na začátku:

Obr. 8 Cyklus Do-While s podmínkou na začátku

Cyklus s podmínkou na konci:

Obr. 9 Cyklus For-Next s podmínkou na konci

Syntaxe:

Do [While podmínka]

[příkazy]

Loop

nebo:

Do

[příkazy]

Loop [While podmínka]

© Jaroslav Nedoma


Příklad:

Potřebujeme vyplnit všechny prázdné buňky hodnotou nula. Pokud makro narazí na vyplněnou

buňku, skončí.

Sub DoWhileDemo()

Do While IsEmpty(ActiveCell)

ActiveCell.Value = 0

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Příklad:

Obdoba předchozího příkladu, avšak aktivní buňka na začátku budou nulou vyplněna vždy, podmínka

se bude testovat až při posunu o buňku níže.

Sub DoWhileDemo2()

Do While

ActiveCell.Value = 0

Aktive.Cell.Offset(1, 0).Select

Loop IsEmpty(ActiveCell)

End Sub

SPECIÁLNÍ MAKRA AUTO_OPEN A AUTO_CLOSE

Pokus vyžadujete, aby Excel při otevření, případně zavření, sešitu vykonal jakoukoliv akci či skupinu

akcí, můžete využít speciálních maker Auto_Open a Auto_Close, která jsou k tomuto účelu přímo

určená.

Makro Auto_Open

Makro se spustí okamžitě po otevření sešitu a provede operace, které jsou zaznamenány v těle

makra.

Může se využít např.:

k nastavení písma;

k nastavení šířky sloupců, výšky řádků;

k nastavení buňkového kurzoru na konkrétní buňku;

k pozdravu uživatele apod.

© Jaroslav Nedoma


Příklad využití makra Auto_Open:

Při každém spuštění sešitu budeme vyžadovat zobrazení informační zprávy. Budeme postupovat

následovně:

1. Otevřeme Modul v prostředí editoru VBA.

2. Napíšeme následující kód:

Sub Auto_Open()

MsgBox “Vítejte v tabulkách firmy ABCDEF Computers, a.s.“

End Sub

3. A nyní nás již při každém otevření sešitu bude Excel „otravovat“ s touto zprávou.

Makro Auto_Close

Makro se spustí těsně před zavřením sešitu a provede operace, které jsou zaznamenány v těle makra.

Může se využít např.:

k uložení sešitu;

k vytištění listu;

k provedení standardních nastavení, které byly změněné makrem Auto_Open apod.

Příklad využití makra Auto_Close:

Bez dotazu programu Excel budeme chtít automaticky ukládat sešit při jeho zavření se všemi jeho

změnami. Budeme postupovat následovně:

1. Otevřeme Modul v prostředí editoru VBA.

2. Napíšeme následující kód:

Sub Auto_Close()

ActiveWorkbook.Save

End Sub

3. A nyní již při každém zavření sešitu bude Excel dokument automaticky ukládat.

© Jaroslav Nedoma


MsgBox a InputBox

MSGBOX

Pokud budete chtít uživateli Vašeho sešitu sdělit konkrétní informaci, využijete zcela jistě funkce

MsgBox, neboli okna se zprávou.

Funkce MsgBox má celkem pět parametrů, z toho jen jeden povinný.

Syntaxe InputBox je následující:

MsgBox(prompt [, buttons] [, title] [, HelpFile] [, Context])

Syntaxe je rozebrána v tabulce níže:

Parametr Význam

prompt Zpráva zobrazená v okně hlášení (povinný).

buttons Stanoví druh a počet tlačítek v okně (volitelný).

title Text v titulkovém pruhu (volitelný).

HelpFile Název souboru nápovědy, který je přidružen oknu hlášení (volitelný).

Context Identifikátor kontextového tématu nápovědy (volitelný).

Příklady použití InputBox:

Potřebujeme informovat uživatele o nemožnosti výpočtu (viz Obr. 10).

MsgBox “Nelze vypočítat“

Obr. 10 MsgBox – příklad 1

© Jaroslav Nedoma


Oproti prvnímu případu navíc vyplníme nadpis okna (titulkový řádek) a doplníme výstražnou ikonu

(viz Obr. 11).

MsgBox “Nelze vypočítat“, vbCritical, “Přehled za rok 2004“


Zeptáme se uživatele, zda chce opravdu pokračovat. V případě, že uživatel zvolí odpověď Ano,

program bude pokračovat, pokud zvolí odpověď Ne, makro se zastaví. Tlačítko Ne bude navíc

označené jako výchozí volba (viz Obr. 12).

Odpověď = MsgBox(“Chcete pokračovat?“, vbYesNo + vbQuestion + _

vbDefaultButton2)

If Odpověď = vbNo Then Exit Sub


INPUTBOX

Funkce i metoda InputBox představují standardní způsob, jak uživatele Excelu požádat o zadání

nějaké hodnoty, kterou makro vyžaduje pro svou činnost.

Základní rozdíl mezi funkcí a metodou InputBox spočívá v tom, že metoda InputBox si dokáže vynutit

typ zadávané hodnoty pomocí parametru type.

Syntaxe InputBox je následující:

InputBox(prompt [, title] [, default] [, left] [, top] [, helpfile]_ [,

helpcontextid] [, type])

© Jaroslav Nedoma


Syntaxe je rozebrána v tabulce níže (Tab. 6):

Parametr Význam

prompt Zpráva zobrazená v okně hlášení (povinný).

title Titulek okna (volitelný).

default Výchozí hodnota, která bude v InputBox zobrazena ihned po jeho otevření a vybrána do bloku, aby ji uživatel mohl přímo přepsat (volitelný).

left, top Určují pozici okna InputBox (volitelné).

helpfile Název souboru nápovědy a identifikátor příslušného hesla.

helpcontextid (volitelné)

type Jen u metody InputBox. Definuje typ zadané hodnoty, který musí být dodržen (volitelný). Možné hodnoty jsou:

0 – vzorec; 1 – číslo; 2 – text; 4 – logická hodnota True nebo False; 8 – odkaz na buňku ve formě objektu Range; 16 – chybová hodnota; 64 – pole hodnot.

Tab. 6 MsgBox – syntaxe

Příklady použití InputBox:

Potřebujeme po uživateli zadat číslo a zkontrolovat, zda jde skutečně o číslo (viz Obr. 13).

x = Application.InputBox("Zadej číslo", Type:=1)

Obr. 13 InputBox – příklad 1

© Jaroslav Nedoma


Potřebujeme po uživateli zadat číslo, které v základu předvyplníme na 1,25 z důvodu vysoké

frekvence výskytu a okno pojmenujeme nadpisem Přehledy (viz Obr. 14).

x = Application.InputBox("Zadej koeficient růstu", "Přehledy", 1.25)

Obr. 14 InputBox – příklad 2

© Jaroslav Nedoma


ZÁVĚR

Dostali jsme se až na samotný závěr publikace. Naučili jsme se pracovat s makry a zjistili jsme, že jsou

velkým pomocníkem Excelu. Pokud jste se dočetli až sem, můžete o sobě prohlásit, že jste právě

vstoupili do světa programátorů maker a dokážete si tak usnadnit každodenní práci v Excelu.

Budu se na Vás těšit zase na příštím školení, na kterém se seznámíme s dalšími zajímavými nástroji

a to v jakémkoli jiném programu sady Microsoft Office.

Excel - makra - IT lektor MS Office | Jaroslav Nedoma ... Makra.pdf · Skripta Vás provedou...

Documents

Transcript of Excel - makra - IT lektor MS Office | Jaroslav Nedoma ... Makra.pdf · Skripta Vás provedou...