������������������������ ���������������������� ���������������������������������������������������� ���������������������������������������������������� �������������������������������������������������������� ������������������������������������������������������� ���������������������������
��������������������������������������������������������������������������� ����������������������������������������������������������������������� ����������������������������������������������������������������������� ��������������������������������������������������������������������������� ������������������������������������������������������������������������������� ��������������������������������������������������������������������� ������������������������������������������������������������������� ���������������������������������������������������������������������� �������������������������������������������������������������������������� �������������������������������������������������������������������������� ����������������������������������������������������������������������� ������������������������������������������������������������������������ ��������������������������������������������������������������������������� �����������������������������������������������������������������������
����������������������������������
������������������������ ���������������������� ���������������������������������������������������� ���������������������������������������������������� �������������������������������������������������������� ������������������������������������������������������� ���������������������������
��������������������������������������������������������������������������� ����������������������������������������������������������������������� ����������������������������������������������������������������������� ��������������������������������������������������������������������������� ������������������������������������������������������������������������������� ��������������������������������������������������������������������� ������������������������������������������������������������������� ���������������������������������������������������������������������� �������������������������������������������������������������������������� �������������������������������������������������������������������������� ����������������������������������������������������������������������� ������������������������������������������������������������������������ ��������������������������������������������������������������������������� �����������������������������������������������������������������������
����������������������������������
������������������������ ���������������������� ���������������������������������������������������� ���������������������������������������������������� �������������������������������������������������������� ������������������������������������������������������� ���������������������������
��������������������������������������������������������������������������� ����������������������������������������������������������������������� ����������������������������������������������������������������������� ��������������������������������������������������������������������������� ������������������������������������������������������������������������������� ��������������������������������������������������������������������� ������������������������������������������������������������������� ���������������������������������������������������������������������� �������������������������������������������������������������������������� �������������������������������������������������������������������������� ����������������������������������������������������������������������� ������������������������������������������������������������������������ ��������������������������������������������������������������������������� �����������������������������������������������������������������������
����������������������������������
Úvod.................................................................................. 11
1
1.
Makra v Excelu ............................................................. 14 1.1
Proč je v Excelu obsažen i programovací jazyk.................. 14 Ve kterých případech je vhodné používat makra? .............. 14 Jak se dají makra vytvořit? .......................................... 15 Kdo makra obvykle vytváří? .......................................... 15 Jak se dají hotová makra spouštět? ............................... 15
2
2.
1.2
Něco málo o vývoji Excelu z hlediska programování ............ 16
1.3
Základní údaje o zastaralém makrojazyku XLM ................. 16
1.4
Typy listů v sešitu a jejich vztah k programování ............... 17
Nahrávání maker .......................................................... 20 2.1
Proč makra nahráváme?.............................................. 20
2.2
Začínáme s nahráváním maker ...................................... 20 První nahrané makro: formátování buněk ........................ 21 Zkoumání nahraných maker .......................................... 24 Problém názvů listů či jiných objektů ............................... 27
2.3
3
3.
Relativní a absolutní odkazy na buňky a jejich použití při nahrávání maker ........................................................ 29
První setkání s editorem jazyka Visual Basic ................... 32 3.1
Jak nahraná makra upravit? ......................................... 32
3.2
Spuštění editoru jazyka Visual Basic ............................... 32
3.3
Vzhled editoru jazyka Visual Basic .................................. 34 Kde všude může být kód VBA?...................................... 37
3.4 3.5
Režimy práce v editoru Visual Basicu.............................. 39 Rozbor nahraných maker ............................................. 39 Hlavička makra .......................................................... 41 Komentáře................................................................ 41 Vlastní kód makra Pokus.............................................. 42 Krátce k barvám kódu ................................................. 44
3.6
4
4.
Okno Immediate ......................................................... 44
Úvod do objektů ve VBA ................................................ 48 4.1 4.2
Principy programování v Excelu ..................................... 48 Objekty v jazyku VBA .................................................. 48 Pračka jako typický příklad objektu................................. 49
OBSAH
Excel - záznam, úprava a programování maker
Obsah
5
Excel - záznam, úprava a programování maker 6
Třídy objektu..............................................................49 Kolekce a jejich vztah k samostatným objektům ................50 Kontejnery jako „nádoby“ na jiné objekty a jejich hierarchie...50 Odkazy na objekty v kódu .............................................52 Zkrácené odkazy na objekty ..........................................54 Rozdíl mezi vlastnostmi a metodami v kódu ......................55 4.3 4.4
Události ....................................................................56 Nápověda k jednotlivým objektům...................................56 Kontextová nápověda ..................................................57
5
5.
Začínáme psát vlastní makra.......................................... 60 5.1 5.2
Části standardního modulu kódu ....................................60 Procedury a funkce .....................................................60 Jaký je rozdíl mezi makrem a procedurou?.......................61 Jak vytvoříte novou proceduru či funkci? .........................61
5.3
Jednotlivé části podprogramů .......................................63 Struktura vlastní funkce...............................................64
5.4 5.5
Příkazy a jejich zápis ...................................................64 Proměnné .................................................................65 Přiřazení hodnoty do proměnné .....................................65
5.6
Konstanty .................................................................66
5.7
Názvy objektů, proměnných a konstant ...........................66
5.8
Výrazy ......................................................................67
5.9
Operátory .................................................................67 Ukázky operátorů a jejich vyhodnocování .........................69
5.10 Datové typy proměnných a konstant...............................69 Datové typy proměnných a konstant...............................69 5.11 Rozsah platnosti a životnost .........................................72 Životnost proměnných .................................................73 5.12 Deklarace proměnných ................................................73 Deklarace konstant a jejich typ......................................75 5.13 Praktické ukázky deklarací proměnných, jejich platnosti a životnosti ...................................................75 Platnost proměnných...................................................75 Statické proměnné......................................................77 5.14 Způsoby zápisu hodnot do proměnné...............................77 Textové údaje ............................................................77 Číselné údaje .............................................................78 Datum a čas..............................................................78 Logické údaje .............................................................79
OBSAH
Datový typ Variant ..................................................... 79 Datový typ String ....................................................... 79 Logický typ Boolean .................................................... 80 Převody mezi jednotlivými typy hodnot ............................ 80 5.16 Pole proměnných........................................................ 81 Statická pole ............................................................. 81 Dynamická pole .......................................................... 81 Vícerozměrné pole ...................................................... 82 Přiřazení hodnoty do prvku pole ..................................... 82 Pole proměnných v proměnné typu Variant – funkce Array . 82 5.17 Parametry a jejich druhy.............................................. 82 Parametry ve vlastních procedurách či funkcích ............... 84 5.18 Rozhodovací bloky....................................................... 86 Příkaz Select Case ..................................................... 86 Rozhodovací blok If…Then…Else .................................... 87 5.19 Cykly........................................................................ 89 Cyklus For…Next ....................................................... 89 Cyklus Do…While | Until ............................................. 89 Cyklus For Each…Next ................................................ 90 5.20 Další možnosti pro spouštění procedur ........................... 91 Spouštění procedur přímo v modulu ............................... 91 Spouštění procedur z jiných procedur či funkcí ................. 91 Spouštění maker z panelů nástrojů................................. 92 Spouštění maker z nabídky Excelu .................................. 94 Spouštění maker pomocí příkazových tlačítek v listech ....... 94
6
6.
Často používané příkazy, funkce a konstrukce.................. 98 6.1
Vestavěné příkazy a funkce obecně ................................ 98 Funkce pracovního listu v kódu VBA ............................... 98
6.2
Jak z kódu informovat uživatele – příkaz a funkce MsgBox.................................................................... 99
6.3
Zadávání hodnot – funkce a metoda InputBox ................ 103
6.4
Funkce pro práci s textovými hodnotami ....................... 105 Funkce, které vrací část hodnoty proměnné .................. 105 Funkce pro hledání v textu a porovnání řetězců .............. 106 Převod na velká a malá písmena .................................. 107
6.5
Funkce pro práci s číselnými hodnotami ........................ 107 Zaokrouhlování desetinných čísel.................................. 107 Trigonometrické, logaritmické a jiné funkce ................... 108
6.6
Vlastní funkce, které můžete použít na pracovních listech..... 108
OBSAH
Excel - záznam, úprava a programování maker
5.15 Další poznámky k datovým typům .................................. 79
7
Excel - záznam, úprava a programování maker 8
6.7
Další časté konstrukce ..............................................110 Příkaz With…End With ..............................................110 Odkazy na objekty pomocí proměnných .........................111
7
7.
Pracujeme s objektem Range........................................ 114 7.1
Co všechno může být objektem typu Range....................114
7.2
Jak určit oblast buněk ...............................................114
7.3
Odkazy na objekt typu Range ...................................... 114 Odkazy stylem A1 – vlastnost Range ...........................115 Odkaz vlastností Cells................................................ 116 Vlastnosti Range a Cells, použité na jiných objektech Range .. 117 Odkazy na celé řádky a sloupce – vlastnosti Columns a Rows .... 117 EntireColumn a EntireRow ..........................................118 Odkaz na vybranou oblast buněk – Selection ..................119 Odkaz pomocí vlastnosti Offset ...................................119 Odkaz pomocí vlastnosti CurrentRegion a UsedRange ......119 Odkaz pomocí vlastnosti Areas....................................120 Odkaz na buňky od pevné počáteční buňky do určitého místa.... 121 Odkaz pomocí vlastností TopLeftCell a BottomRightCell ....................................................121
7.4
Pracujeme s oblastmi buněk – základní vlastnosti a metody ..... 121 Nejdůležitější vlastnosti objektu Range ..........................122
7.5
Příklady různých technik pro práci s buňkami..................125 Kopírování buněk ......................................................125 Přesun buněk na jiné místo .........................................125 Vkládání ze schránky jinak – metoda PasteSpecial...........125 Mazání obsahu či formátu buněk ..................................126 Vkládání buněk do listu a jejich odstraňování...................127 Vkládání vzorců do buněk ...........................................127 Práce s komentáři v buňce .........................................127 Automatické vyplnění určité oblasti...............................128 Odkaz na speciální oblasti buněk ..................................128 Prohledávání a zaměňování obsahu buněk ......................130 Setřídění určité oblasti...............................................132 Formátování obsahu buněk .........................................133
8
8.
Pracujeme s objekty Workbook a Worksheet ................. 138 Jak jsou listy v sešitu vyjádřeny objektově?....................138 Odkazy na aktivní sešit či list ......................................138 8.1
Pracovní listy ...........................................................139 Kolekce Worksheets .................................................139 Základní vlastnosti objektu Worksheet ..........................140
OBSAH
Odstranění listu ....................................................... 142 Zkopírování nebo přesun celého listu............................. 142 Vložení obsahu schránky na list ................................... 142 Vložení obsahu schránky na list v určitém formátu .......... 143 Vytištění listu a tiskový náhled .................................... 144 Uložení listu do souboru jiného typu .............................. 144 Ochrana listu proti změnám........................................ 145 8.2
Práce s objektem PageSetup a tisk listů ....................... 147 Změna orientace papíru ............................................. 149 Jak nastavit okraje stránky ........................................ 149 Jak vymezit tiskovou oblast listu.................................. 149 Zvětšení či zmenšení obsahu listu při tisku..................... 149 Vložení ručního konce stránky ..................................... 150
8.3
Pracujeme se sešity ................................................. 150 Kolekce sešitů Workbooks ......................................... 151 Vlastnosti a metody objektu Workbook ......................... 152 Základní postupy pro práci se sešity ............................. 155
8.4
9
9.
Odpověď na hádanku................................................. 159
Objekt Application a jeho význam.................................. 162 9.1
Užitečné metody a vlastnosti objektu Application ............ 162 Jak urychlit makro potlačením vykreslování změn ............ 162 Potlačení některých výstražných dialogů ........................ 163 Zobrazování zpráv o průběhu makra ............................. 163 Změna tvaru ukazatele myši ....................................... 163 Vlastnosti prostředí Excelu ......................................... 164 Metody objektu Application, které jsme již poznali ........... 165 Přechod na jiné místo sešitu....................................... 166 Metoda OnKey – spouštění procedury klávesovou zkratkou .. 166 Metoda OnTime – spuštění procedury v určitý čas.......... 167 Pozastavení makra na určitou dobu .............................. 167 Uložení pracovního prostoru ....................................... 167 Ukončení Excelu ....................................................... 167
9.2
10
Zobrazení vestavěných dialogových oken ....................... 168
10. Jak řešit chyby v makrech ........................................... 172 Chyby během kompilace ............................................. 173 10.1 Chyby za běhu programu............................................ 173 10.2 Chybové rutiny v kódu ............................................... 174 „Vnitřek“ chybové rutiny ............................................ 175 Proč je důležité mít chybové rutiny ve všech procedurách ..176
Excel - záznam, úprava a programování maker
Základní metody objektu Worksheet............................. 141
Konkrétní ukázka chybové rutiny s několika „fígly“ ........... 177
OBSAH
9
Excel - záznam, úprava a programování maker 10
10.3 Ladící nástroje Excelu ................................................179 Stručně o režimu přerušení ........................................180 Rychlé zobrazení hodnoty nějakého výrazu či proměnné ....180 Okna související s laděním chyb ...................................181 Testování hodnot v okně Immediate ..............................181 Jak používat okno Locals ............................................182 Kukátka .................................................................182 Rychlé kukátko .........................................................184 Zarážky (breakpoint) .................................................184 Krokování procedur ...................................................184 Možné vynechání určitých míst v kódu ..........................185 10.4 Stručný přehled nejdůležitějších chyb ............................186 Rejstřík ........................................................................... 189
OBSAH
Kniha se zaměřuje na základy programování v Excelu 2003; pokud máte starší verze Excelu (97, 2000, 2002), můžete se podle ní učit také, z hlediska základů programování se tyto verze neliší. V prvním dílu knihy se věnuji těm záležitostem, které programátor Excelu prostě „musí“ znát, a těm technikám, které bude v Excelu používat nejčastěji. Navazující druhý díl je učebnicí „pokročilých“ programovacích technik a objektů. V celém textu se snažím být maximálně stručný, což je přirozený důsledek dlouholeté zkušeností lektora, který ví, že jeho posluchači (čtenáři) se chtějí něco naučit a ne poslouchat marketingové a jiné podobné bláboly. Sem tam na některé věty či pasáže narazíte vícekrát – opakování je matka moudrosti. Programování maker není záležitostí pro úplné počítačové začátečníky a také ne pro ty, kdo si jen rádi hrají. V celém textu knihy předpokládám, že umíte Excel ovládat a víte, k čemu všemu se dá použít. Např. v části věnované programování grafů rozhodně nenajdete výklad toho, jak se graf vytváří ručně v uživatelském rozhraní … Počítejte také s tím, že bez znalosti angličtiny se toho moc nenaučíte, pro zvládnutí pokročilých témat je nezbytná, ale i u základů se vám bude hodit. Česky toho totiž příliš nevyšlo a spoustu materiálů seženete na internetu jen v angličtině. Schází především česká referenční příručka s kompletním českým překladem nápovědy k objektům, vlastnostem, metodám a událostem, i když se v tomto směru stále dá doporučit výtečná knížečka „Programování Office 97“ od Markéty a Petra Šitinových, kterou vydalo nakladatelství Grada v roce 1998. Je přirozené, že váš pohled na knihu bude jiný než můj. Pokud zjistíte, že v knize schází některá fakta, bez kterých si nevíte rady, napište na adresu
[email protected] a já se pokusím vám stručně vysvětlit souvislosti. Používané konvence Jak jste již z publikací nakladatelství Grada zvyklí, orientaci v textu vám budou usnadňovat různé typografické prvky: Pozor!
Důležité pojmy a pasáže textu, které je třeba zvýraznit, jsou vysazeny tučně.
Název
Názvy firem, softwarových produktů, aplikací a jednotlivých objektů programu označuji kurzívou.
Soubor
Názvy souborů, složek a internetové adresy.
Storno
Texty, které se objevují v uživatelském prostředí aplikací Windows, jsou vysazeny jako běžný text, ale v tučné kurzivě. Takto jsou odlišeny příkazy nabídek, popisky ovládacích tlačítek, názvy dialogových oken a další „citace z obrazovky“.
ÚVOD
Excel - záznam, úprava a programování maker
Úvod
11
Excel - záznam, úprava a programování maker 12
Vložit → Rám
Jednotlivé příkazy v posloupnosti příkazů zadávané v nabídkách, podnabídkách a následně otevíraných dialogových oknech oddělujeme šipkami – např. Nástroje → Možnosti → Zobrazení → Zalomit do okna.
KLÁVESA
Názvy kláves a klávesových zkratek označujeme KAPITÁLKAMI – např. ENTER nebo ALT+S.
Program
Pro výpis zdrojového kódu v příkladech a pro odlišení jednotlivých prvků programu v běžném textu je použito bezpatkové neproporcionální písmo.
V textu se budete často setkávat s odstavci označenými ikonou, která bude charakterizovat druh informace v daném odstavci:
Píšící ruka označuje poznámku, která není nezbytná k pochopení dané problematiky, ale týká se tématu a prozrazuje další souvislosti. „Usměváček“ vás upozorní na různé tipy a triky, kterými si můžete usnadnit některé činnosti nebo které vám umožní dosáhnout efektních výsledků. „Mračoun“ označuje text, v němž vás upozorňuji na něco, co nefunguje tak, jak byste očekávali nebo jak popisuje dokumentace. Varovně vztyčený prst označuje text, který vás upozorňuje na něco, na co byste si měli dát pozor, co vás může nepříjemně překvapit nebo co by vám mohlo způsobit problémy.
Bomba je předzvěstí katastrofy nebo alespoň velkých nepříjemností. Tato ikona totiž označuje text upozorňující na skutečnosti, vedoucí ke ztrátě dat, zhroucení systémů a podobným havarijním stavům.
Takto označené pasáže textu jsou použity v případě, kdy je třeba na chvíli přerušit normální běh výkladu a upozornit na skutečnosti, které je třeba znát pro pochopení další látky, nebo na skutečnosti, které by mohly být pro čtenáře zajímavé, ale které s vykládanou látkou souvisí pouze nepřímo.
ÚVOD
1 1. Makra v Excelu
13
Excel - záznam, úprava a programování maker 14
1. Makra v Excelu Nejen u počítačů, ale i u jiných oborů se vyplatí držet se přísloví „dvakrát měř a jednou řež“. U programování však nejde jen o to, abyste přílišným spěchem nevyrobili příliš mnoho chyb, jak říká původní smysl staré lidové moudrosti. Základní otázkou tu je, zda má vůbec smysl se do něčeho podobného pouštět. A náplní první kapitoly jsou právě odpovědi na otázky: „K čemu je programování? Proč bych to měl dělat? Jak se do toho pustit?“
1.1
Proč je v Excelu obsažen i programovací jazyk
Čím více zkušeností s prací v Excelu budete mít, tím rychleji zjistíte, že mnohdy určité činnosti a operace stále a stále opakujete – a to tak často, že zatoužíte po nějakém „pomocníkovi“, který by tyto operace dělal za vás. V mnoha případech vám bude některá operace připadat příliš složitá – např. zapisování vstupních údajů do složitě strukturovaných tabulek, ve kterých budete muset pro každý nový údaj vyhledat příslušný sloupec či řádek. Ztráta času bude dost velká, a vy opět začnete uvažovat o „pomocníkovi“, který by celou činnost zrychlil. Tyto pocity zažívaly tisíce uživatelů před vámi, takže do většiny populárních kancelářských (i jiných) programů byly začleněny i prostředky pro psaní maker (má je nejen Excel, ale i Word a další programy). Makro je sled příkazů, jehož cílem je automatizovat některé pracovní postupy, vyloučit tak opakované operace a snížit počet případných chyb při zpracování dat. Ve kterých případech je vhodné používat makra? Otázku je možné položit i jinak – co můžete pomocí maker udělat? Možností je mnoho, přiblížíme si alespoň ty nejdůležitější: 1. Automatizace často prováděných úkonů. Zkuste si sami vzpomenout, které činnosti provádíte vždy před odchodem z práce, na konci týdne, na konci měsíce, po vytvoření sešitu … patřit sem může např.: Otevření 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. 2. Vytvoření vlastní funkce či příkazu. Pomocí vlastní funkce se dají zjednodušit některé vzorce, vlastním příkazem můžete zkombinovat několik příkazů z nabídek Excelu. 3. Vytvořit celé nové aplikace. Programovací jazyk většinou nabízí i vlastní dialogová okna nebo panely nástrojů, která výrazně zjednoduší práci s programem.
MAKRA
V
EXCELU
V zásadě jsou možné dva způsoby : Makro si můžete nahrát pomocí záznamníku maker. Při nahrávání makra jsou všechny činnosti, které v Excelu provádíte, automaticky zapisovány do tzv. modulu. Po zastavení záznamníku můžete nahrané makro okamžitě spouštět, ve většině případů je ovšem nutné ho trochu upravit. Výhody a nevýhody záznamníku maker jsou popsány ve druhé kapitole. Makro můžete napsat ručně, ať již celé, nebo pouze upravit jiné, již existující. Tento přístup má mnoho výhod, ale jednu zásadní nevýhodu – musíte ovládat daný programovací jazyk. Kdo makra obvykle vytváří? Každodenní uživatelé Excelu, ovládající programování špatně či vůbec, kteří si ale dokáží makro nahrát a urychlit tak některé úkoly. Vývojáři (programátoři), jejichž úkolem je vytvářet rozsáhlejší tabulkové aplikace pro pracovníky vlastního podniku či zákazníky. Jak se dají hotová makra spouštět? Hotové makro je uloženo v modulu, kde ho lze upravit či spustit, běžný uživatel se ovšem s modulem v praxi přímo nesetká (to by musel otevřít editor jazyka Visual Basic). Proto je nutné, aby mohl spouštět makra přímo z uživatelského prostředí. Excel v tomto ohledu nabízí dostatečné množství variant: Makro můžete vždy spustit z dialogového okna Makro, které otevřete příkazem Nástroje → Makro → Makra (nebo klávesovou zkratkou ALT+F8). V dialogovém okně Makro vidíte seznam všech dostupných maker, pokud některé z nich v seznamu označíte, stačí pak už jen stisknout tlačítko Spustit. Makru můžete přiřadit klávesovou zkratku. Z určitých nepochopitelných důvodů vám však Excel v dialogovém okně možností makra nabízí jen zkratky typu C TRL+PÍSMENO , případně CTRL+S HIFT + PÍSMENO. Neznamená to, že byste makro nemohli spouštět i jinými kombinacemi kláves, ale ty již musíte určitým způsobem naprogramovat. Makro se dá spouštět určitým příkazem nabídky. Příkaz pro spuštění makra se dá přidat do libovolné nabídky, dokonce si můžete vytvořit i vlastní nabídku. Makro můžete přiřadit nějakému tlačítku na panelu nástrojů. Jde o velmi často používanou možnost, protože toto tlačítko můžete graficky upravit tak, aby vizuálně naznačovalo, jaké makro se po jeho stisknutí spustí. Makro dále můžete přiřadit tlačítku umístěnému v listu či na formuláři, nebo libovolnému grafickému objektu. I tato možnost se hojně využívá, zvláště u maker určených k provádění operací na konkrétním listu. Kromě toho umí Excel spustit makro (programový kód) i sám, bez přispění uživatele. Využívá při tom tzv. události, které probíhají při určitých akcích, např. otevření sešitu, tisk sešitu, aktivace jiného listu apod.
MAKRA
V
EXCELU
Excel - záznam, úprava a programování maker
Jak se dají makra vytvořit?
15
Excel - záznam, úprava a programování maker 16
1.2
Něco málo o vývoji Excelu z hlediska programování
Excel má dost bohatou historii verzí, i když v dnešní době ty starší (2, 3, 4, 5, 95) jsou již dávno mimo hru; těžko se najde někdo, kdo by je ještě používal (Microsoft je již také žádným způsobem nepodporuje). Ve starých verzích Excelu byl programovací jazyk XLM (viz další část), v Excelu 5 se poprvé objevil jazyk Visual Basic for Applications (dále VBA). Pro nás je výchozí až Excel 97, který se objevil začátkem roku 1997, buď samostatně nebo jako součást balíku aplikací Office 97. (Někdy je označován jako Excel 8, hlavně v názvech souborů nápověd.) Excel 97 totiž přišel se zcela novým uživatelským rozhraním pro programování – s moduly maker se pracuje ve zvláštním editoru Visual Basicu, starý způsob psaní kódu přímo v uživatelském prostředí Excelu byl opuštěn (moduly jsou ovšem stále ukládány jako součást sešitů XLS). Značně se tak zvýšila efektivita práce programátorů, protože staré nástroje se s pohodlím nového editoru nemohly srovnávat. Kupodivu, tento editor byl v českém Excelu 97 také přeložen do češtiny, zatímco v české verzi Excelu 2000 a novějších zůstal anglický. V dalších verzích Excelu se již nic zásadního nedělo, samozřejmě každá verze přinesla pár novinek, obvykle souvisejících s vylepšenými nebo novými funkcemi.
1.3
Základní údaje o zastaralém makrojazyku XLM
Přestože v této knize se jazyk XLM učit nebudete, vysvětlím stručně, o co šlo. Makra v jazyku se zapisovala do zvláštních typů listů (oficiálně se tento typ listu jmenuje Makro Microsoft Excel 4.0) jako posloupnosti volání jednotlivých funkcí. V Excelu 97 a novějších verzích již nelze makra v jazyku XLM nahrávat, již existující makra však můžete spouštět nebo upravovat (listy s těmito makry se objeví přímo v Excelu, není nutné spouštět editor Visual Basicu). A proč se tu o tomto jazyku vůbec zmiňuji? Inu, máte šanci – asi tak půl promile – na to, že se k vám někdy sešit s makry napsanými v tomto jazyku dostane. Tak abyste nebyli překvapeni. Jak by takové makro vypadalo, vidíte na následujícím obrázku.
Obr. 1.1 Ukázka jednoduchého makra v jazyku XLM.
MAKRA
V
EXCELU
Typy listů v sešitu a jejich vztah k programování
Celkem existuje pět typů listů, které se v sešitu Excelu mohou objevit: pracovní listy (angl. worksheets) listy typu graf (charts) listy maker XLM (zastaralé, stále se však podporují) listy dialogů Microsoft Excel 5.0 (zastaralé, jsou však stále podporovány) mezinárodní list maker (relikt z prastarých verzí Excelu; jeho význam je utajen již v nápovědě k Excelu 95) Nejběžnějším typem listu je tzv. pracovní list. Tento list má většina lidí na mysli, pokud mluví o tabulkách. Každý pracovní list Excelu 97 a vyšších verzí má 256 sloupců a 65 536 řádků (což je čtyřnásobek počtu řádků, který byl k dispozici ve starších verzích). S prvními dvěma typy listů pracujete přímo v sešitu. Makra jazyka VBA jsou ukládána výhradně v modulech, se kterými je možné pracovat jen v programovacím editoru. Kromě maker, která jsou složena jen z kódu, můžete naprogramovat i vlastní dialogová okna, tedy grafický prvek uživatelského rozhraní, který lze zobrazit přímo nad pracovním listem Excelu. Ve starších verzích Excelu se tato dialogová okna vytvářela na speciálních listech dialogů. Novější verze (počínaje Excelem 97) sice stále tyto listy podporují, nabízejí však daleko lepší alternativu: formuláře UserForm.
MAKRA
V
EXCELU
Excel - záznam, úprava a programování maker
1.4
17
*
2 2. Nahrávání maker
19
Excel - záznam, úprava a programování maker 20
2. Nahrávání maker Jak již víte z první kapitoly, 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. Uživatel nebo programátor příkazem Excelu vyvolá okno pro zadání názvu makra, název zapíše a poté spustí záznamník. Excel začne nahrávat všechny akce myši, stisk kláves, vyvolané příkazy, otevřené dialogy atp. Jakmile je vše potřebné nahráno, nahrávání ukončíme.
Nahrávání musíte ukončit, jinak zůstane záznamník zapnut a bude do makra ukládat všechny další činnosti, a to až do ukončení Excelu!
2.1
Proč makra nahráváme?
Co získáte, když budete makra nahrávat? Nahrávání je nezastupitelné během postupného seznamování se s jednotlivými objekty Excelu. V kapitole věnované objektům se dozvíte, že mají velké množství různých vlastností, které jsou sice popsány v nápovědě, nicméně nahráním makra zjistíte nejrychleji, které z těchto vlastností skutečně musíte použít. Jedná se o nejrychlejší způsob vytvoření makra. Na druhé straně stojí (zásadní) nevýhody nahraných maker: Záznamník vždy jednotlivé objekty (buňky, listy, grafy) vybírá, v nahraném makru tedy vždy najdete příkazy pro výběr – pokud si ovšem požadovaný objekt nevyberete sami ještě před spuštěním záznamníku. Výběr objektů ve skutečnosti není nutný a prodlužuje dobu provádění makra. Výběr objektů má ještě následující důsledky: buď si příslušný objekt (buňky, graf) označíte ještě před začátkem nahrávání makra – v takovém případě pak bude makro vždy zpracovávat aktuální výběr. Nebo budete jednotlivé objekty vybírat až při vlastním nahrávání – pak ovšem bude nahrané makro pracovat vždy a právě jen s těmito objekty. Jestli to není jasné, nelamte si s tím hlavu, zopakujeme si to později na příkladech. Způsob, jakým záznamník nahrává prováděné operace, se občas dá těžko pochopit. Kód, který byste ručně napsali mnohem efektivněji, je někdy správně strukturován, někdy ne.
2.2
Začínáme s nahráváním maker
V předchozím výčtu nevýhod nahrávání jsem napsal, že záznamník maker vždy nahraje i veškeré výběry objektů, které při nahrávání provádíte. Nahraný kód navíc ve výchozím nastavení pracuje s absolutními adresami buněk, což také nemusí být vždy to pravé. Při každém nahrávání funkčního makra – makra, která potřebujete jen k učení, nejsou funkční – je tedy třeba si nejdříve rozmyslet, jestli začnete makro nahrávat již s nějakým označeným objektem, nebo ho označíte až během nahrávání. Rozdíl si nejlépe ukážeme na úvodních jednoduchých příkladech.
NAHRÁVÁNÍ
MAKER
Tento typ chyb se dá téměř úplně odstranit již při psaní kódu pomocí automatické kontroly syntaxe v okně Options. Celkově však tyto chyby nepředstavují zvláštní nebezpečí, protože vznikají z neznalosti syntaxe nebo (především) náhodným špatným zápisem. Visual Basic vždy při výskytu takové chyby zobrazí hlášení s udáním její příčiny.
Tyto chyby se projeví buď přímo při psaní kódu (díky automatické kontrole syntaxe) nebo při pokusu o spuštění procedury.
Jako ukázku možné chyby během kompilace vidíte na obrázku špatnou deklaraci proměnné. Protože se v názvu proměnné vyskytuje typová přípona, nemůžete už typ proměnné určit pomocí slova As. Chybové hlášení se objeví při pokusu o přechod na nový řádek a řádek s chybným zápisem bude zároveň označen červeně.
Obr. 10.2 Takto vám Visual Basic ohlásí chybu během kompilace.
10.1 Chyby za běhu programu Dojde-li k nějaké chybě za běhu programu, VBA jí automaticky přiřadí určité číslo. Pro ukládání tohoto čísla a dalších zajímavých údajů vztahujících se ke vzniklé – poslední – chybě má jazyk VBA zvláštní objekt Err. Jeho vlastnost Number tedy obsahuje číslo poslední chyby. Uvedená vlastnost je výchozí vlastností tohoto objektu, a proto skutečné číslo chyby můžete zjišťovat např. takto: If Err = 11 Then ' došlo-li k chybě č.11 (dělení nulou) ' zde bude kód pro ošetření této chyby ElseIf Err = 13 Then ' nesouhlas datových typů proměnných ' zde bude jiný kód pro ošetření chyby č.13 End If
Vlastní korekce chyb tedy bude vycházet z čísla poslední chyby (správný program bude pochopitelně reagovat na každou chybu, proto vás čísla chyb předešlých ani moc zajímat nebudou, protože je již budete mít ošetřeny).
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
Chyby během kompilace
173
Excel - záznam, úprava a programování maker 174
Jak dlouho si objekt Err uchová informace o poslední chybě? Jeho vlastnosti se vynulují nebo nastaví na řetězce nulové délky ("") po příkazu Resume (v jakékoli podobě) nebo On Error a také po příkazech Exit Sub nebo Exit Function. Původní stav objektu Err si můžete vynutit i programově, a to jeho metodou Clear. Kdy se tyto příkazy spouští, se dozvíte později.
10.2 Chybové rutiny v kódu Protože se chyba může vyskytnout v kterémkoli řádku programu (pochopitelně s výjimkou komentářů), vyplatí se je ošetřovat v určitém centrálním místě každého podprogramu. V úvodních pasážích knihy jste se o tom již dočetli: podprogram je možné rozdělit na několik částí, z nichž každá je identifikována nějakým návěštím (label). Do jedné z částí se umístí tzv. chybová rutina, tedy část kódu pro řešení vzniklých chyb.
Toto návěští se píše na samostatný řádek, musí začínat písmenem a končit dvojtečkou, a především musí být zapsáno zcela od levého kraje okna programového kódu!
Ukázka možného rozdělení podprogramu pomocí návěští: Private Sub Form_Click ' ' zde je první část kódu procedury ' konec: ' následuje druhá část oddělená návěštím End Sub
Veškerý kód pro ošetření vzniklých chyb soustředíme do této zvláštní části. Z toho vyplývá, že musíme nějak zajistit, aby při vzniku chyby provedl VBA automaticky skok do této části, a také (to už není bezpodmínečně nutné) aby tato část byla při bezchybném průběhu vynechána. Druhou podmínku splním tak, že před zmíněné návěští napíši tento řádek: Exit Sub
Všechno, co následuje za tímto příkazem, bude vynecháno. Podmínku první, neboli automatický skok do části programu ošetřující chyby, zajistí příkaz: On Error GoTo název_návěští
Příkaz On Error má tři možné podoby. První z nich (uvedená na předchozím řádku) zajišťuje onen odskok do chybové rutiny. Pozor, v příkazu zadáváte název návěští bez dvojtečky na konci! Podoba druhá, On Error Resume Next, při vzniklé chybě prostě pokračuje dalším řádkem – chyba se sice registruje, ale jinak se nic neděje. Toto řešení je vhodné vždy, když případné chyby odstraníte hned v následujícím řádku. Konečně podoba třetí, On Error GoTo 0, zamezuje odskoku na návěští chybové rutiny podprogramu (nula zde není míněna jako návěští!!). Jinými slovy – chyby nejsou nijak ošetřeny. V rámci jedné procedury se může vyskytovat více příkazů On Error, jak si za chvíli ukážeme.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Následující ukázka je jakousi „šablonou“, který můžete použít pro ladění chyb vzniklých za běhu aplikace: Private Sub nějaká_procedura On Error GoTo chybova_rutina ' ' zde bude kód procedury ' Exit Sub
chybova_rutina: ' zde bude kód ošetřující chyby End Sub
V případě, že byste použili příkaz On Error Resume Next, bude kód vypadat asi takto: Private Sub nějaká_procedura On Error Resume Next ' ' zde bude nějaký příkaz
If Err <> 0 Then ' kód pro odstranění chyby End If ' zde bude další příkaz If Err <> 0 Then ' kód pro odstranění chyby End If ' a tak dále a dále ... End Sub
Pozor, v tomto případě samozřejmě nemusíte chyby ošetřovat za každým příkazem! Rozhodovací bloky If Err <> 0 Then použijte jen za těmi příkazy, kde ke vzniku chyby může dojít. „Vnitřek“ chybové rutiny Vlastní chybová rutina se většinou skládá ze třech základních částí – z testování čísla chyby, bloku s příkazy určujícími, jak bude která chyba ošetřena, a konečně z určení toho, co bude následovat po ošetření chyby. Testování čísla chyby lze provést kupříkladu příkazem Select Case: Select Case Err ' testujeme hodnotu Err.Number Case 5 ' kód pro chybu číslo 5 Case 7 ' kód pro řešení další chyby atd. End Select
Ošetření chyby by bylo umístěno v jednotlivých částech bloku Select Case, např.: Case 71 ' 71 = zařízení není připraveno MsgBox "V mechanice není disketa. Vložte ji do mechaniky " & _ "a poté pokračujte", vbExclamation
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
Jak to tedy může vypadat
175
Excel - záznam, úprava a programování maker 176
Každá část bloku bude uzavřena příkazem, který určuje místo, kde bude kód po nápravě chyby pokračovat. Zde se nabízí tyto základní možnosti: Ukončení programu: U chyb, které nelze odstranit a díky kterým není ani možné pokračovat v provádění dalšího kódu, musíte kód zastavit, podle potřeby i odvolat provedené změny. Příkaz Resume: Po jeho provedení bude kód pokračovat řádkem, v němž došlo k chybě, příkaz tedy bude zopakován. Tento příkaz použijete vždy, pokud je chybová rutina schopna problém beze stop odstranit. Příkaz Resume Next: Znamená pokračování programu řádkem nacházejícím se v kódu za tím řádkem, v němž došlo k chybě. Uvedený příkaz musíte použít tam, kde nelze příčinu chyby úplně odstranit a kde nevadí, že chybný řádek kódu nebude proveden. Další obrázek ukazuje názorně oba možné způsoby návratu pomocí příkazů Resume či Resume Next. Pro úplnost dodávám, že existuje čtvrtý, i když výjimečný způsob, jak chybovou rutinu ukončit – Resume název_návěští (pokud máte podprogram rozdělen pomocí návěští na více částí).
Obr. 10.3 Názorný přehled směru provádění kódu v chybové rutině.
Proč je důležité mít chybové rutiny ve všech procedurách Z jedné procedury (jednoho makra) můžete volat makro jiné – jak, to už dávno víte. Dojde-li však k tomuto volání jiného makra, musíte si dávat obzvláštní pozor na to, aby chybové rutiny byly v obou dvou procedurách, nebo alespoň v té, kterou voláte. Proč? Ukážeme si to na hypotetickém příkladu. První procedura bude zpracovávat zadanou oblast buněk a pro každou buňku v oblasti bude volat druhou proceduru, která s buňkou provede nějakou operaci. V první proceduře budete mít chybovou rutinu, ve druhé ne.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Mnohem větší potíž spočívá v tom, že pokud je chyba ošetřena rutinou umístěnou v první proceduře, nepokračuje Visual Basic po ošetření chyby v provádění procedury, kde se chyba objevila, ale v proceduře s touto chybovou rutinou, neboli v proceduře první! Pokud je chybová rutina první procedury ukončena příkazem Resume, pokračuje kód znovu řádkem, v němž je volána druhá procedura, ve které došlo k chybě. Tím máte téměř zajištěno zacyklení programu, protože pokud chybová rutina neumí chybu ošetřit, bude se tato chyba v druhé proceduře neustále opakovat. Končí-li chybová rutina první procedury příkazem Resume Next, pokračuje Visual Basic řádkem následujícím za voláním druhé procedury, což zase může způsobit nedokončení některých (třeba nutných) operací. Jestliže tedy z jedné procedury voláte druhou (případně z druhé voláte třetí atd.), hledá Visual Basic při vzniku chyby za běhu programu chybovou rutinu postupně ve všech nedokončených procedurách. Pokud žádnou nenajde, zobrazí výchozí okno hlášení s popisem chyby. A to je důvod, proč byste měli chybové rutiny psát do každé procedury či funkce, s kterou pracujete. Vyhnete se tak některým nepříjemným překvapením. Konkrétní ukázka chybové rutiny s několika „fígly“ Představme si následující situaci: v určitém sešitu si potřebujete uložit kopii konkrétního listu pro případ, že byste se potřebovali vrátit k původním údajům, které na něm byly uloženy. Dejme tomu, že sešit má mnoho listů, proto si nebudete zálohovat celý sešit, ale jen jediný list. Požadovaná procedura tedy zkopíruje list (pro zjednodušení to bude aktivní list) do nového listu, jehož název bude totožný, jen na jeho začátek přidáte slovo „Záloha “ (s mezerou za tímto slovem). Protože předpokládáte, že zálohu listu budete provádět opakovaně (jinak byste kvůli tomu nepsali proceduru) a jméno kopie listu bude stále stejné, je nutné nejdříve zjistit, jestli už zálohovaný list existuje a pokud ano, tak ho odstranit. Následně list zkopírujete a změníte jeho jméno. Nakonec zálohovaný list skryjete, protože není nutné, aby byl vidět. Jak zjistíte, jestli už zálohovaný list v sešitu existuje? Jednu z možností si asi odvodíte sami – projít kolekcí všech listů a testovat jejich název: puvodni_jmeno = ActiveSheet.Name For Each list In ActiveWorkbook.Worksheets If list.Name = "Záloha " & puvodni_jmeno Then _ list.Delete Next
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
Celý problém se dá popsat takto: jestliže ve druhé proceduře dojde k chybě, hledá VBA chybovou rutinu v této proceduře. Protože ji tam nenajde a protože tato procedura byla volána z jiného makra, přejde Visual Basic zpět do první procedury a hledá chybovou rutinu tam (jinak by totiž musel zobrazit výchozí chybové hlášení). Řekli jsme si, že v první proceduře chybová rutina existuje. Dobře – Visual Basic tuto chybovou rutinu provede, přičemž číslo poslední chyby je pochopitelně číslo chyby vzniklé ve druhé proceduře. To je první problém – chybová rutina ošetřuje chybu z jiné procedury. Chybu stejného čísla v různých procedurách je samozřejmě možné, ba dokonce někdy i nutné ošetřit různým způsobem, záleží na situaci.
177
Excel - záznam, úprava a programování maker 178
V tomto fragmentu kódu si nejdříve uložíte název aktivního listu do proměnné (protože po zkopírování listu bude aktivní nový list!) a poté v cyklu projdete všechny listy. Pokud bude název některého listu odpovídat názvu aktivního listu a na začátku bude mít přidáno slovo „Záloha“, odstraníte ho. Vlastní kopírování listu se provádí metodou Copy, v ní ovšem nemáte možnost ovlivnit jméno nového listu, proto bude v kódu řádek měnící vlastnost Name nového listu. Takže základní kostra požadované procedury může vypadat takto: Sub ZalohaListu() Application.DisplayAlerts = False puvodni_jmeno = ActiveSheet.Name For Each list In ActiveWorkbook.Sheets If list.Name = "Záloha " & puvodni_jmeno Then _ list.Delete Next ActiveSheet.Copy after:=ActiveSheet ActiveSheet.Name = "Záloha " & puvodni_jmeno ActiveSheet.Visible = False Application.DisplayAlerts = True Sheets(puvodni_jmeno).Activate End Sub
V prvním řádku vypínáme vlastnost DisplayAlerts, jinak bychom museli při mazání listu potvrdit jedno z oken hlášení Excelu. V druhém řádku uložíme název aktivního listu, v cyklu najdeme případnou již existující zálohu a odstraníme ji. V šestém řádku zkopírujeme aktivní list. Parametr after zadat musíte, jinak by byl vytvořen nový sešit a list kopírován do něj! Proč kolekce Sheets a ne Worksheets? Může se stát, že proceduru spustíte, když je aktivní list typu graf. V sedmém řádku změníme název nového listu a v následujícím řádku ho skryjeme. Osmý řádek znovu zapíná vlastnost DisplayAlerts. A konečně poslední řádek aktivuje list, který jsme kopírovali – pokud byste ho vynechali, bude po provedení procedury aktivní list umístěn za nově vytvořeným listem. Kde je chybová rutina? Zatím jsme ji do procedury nepřidali. S využitím toho, co jsme si zatím řekli, však můžete celou proceduru podstatně vylepšit. Cyklus, ve kterém procházíte kolekci listů v sešitu, se totiž dá vypustit a místo něj můžete přímo hledaný list smazat! Sheets("Záloha " & puvodni_jmeno).Delete
A co když odstraňovaný list v sešitu ještě nebude (při prvním spuštění procedury nebo kdybyste zálohovaný list smazali ručně)? Pokus o smazání listu, který neexistuje, vyvolá samozřejmě chybu za běhu programu. Protože však máte k dispozici příkaz On Error Resume Next, dá se tato chyba jednoduše zachytit a vyřešit: Sub ZalohaListu() Dim puvodni_jmeno As String Application.DisplayAlerts = False puvodni_jmeno = ActiveSheet.Name
JAK
Ř EŠI T
CHYBY
V
MAKRECH
On Error GoTo chyba_zalohalistu ActiveSheet.Copy after:=ActiveSheet ActiveSheet.Name = "Záloha " & puvodni_jmeno ActiveSheet.Visible = False Application.DisplayAlerts = True Sheets(puvodni_jmeno).Activate Exit Sub chyba_zalohalistu: MsgBox "Záloha listu se nezdařila" Application.DisplayAlerts = True Sheets(puvodni_jmeno).Activate End Sub
Máte dojem, že to je nějaký divný kód? Že tam chybí ošetření chyby vzniklé pokusem o smazání neexistujícího listu? To máte pravdu – ale co je vlastně třeba ošetřit? Prostě se jen nezdařilo smazání listu! Kód bude pokračovat dál a s testováním čísla chyby se vůbec nemusíme zdržovat, protože tuto chybu lze prostě zahodit. Všimněte si dvojího použití příkazu On Error. Ihned za řádkem mazajícím zálohovaný list zapínáme klasické zachytávání chyb s ?odskokem? do chybové rutiny. Je ta procedura dokonalá? Není, samozřejmě. Např. se tu nepočítá . s případem, kdy byste proceduru omylem spustili v okamžiku, ve kterém se nacházíte na listu sloužícím jako záloha (procedura by proběhla bez problému. Výsledkem by byl nový list pojmenovaný např. „Záloha Záloha List3“). Ale to už jsou detaily.
10.3 Ladící nástroje Excelu Každé makro, proceduru či funkci, které hodláte opakovaně používat, je nutné důkladně otestovat, jestli za všech možných okolností pracuje správně. Ve většině případů je selhání makra zaviněno právě nedostatečným testováním, během něhož jste přehlédli některé chyby. Ve skutečnosti byste měli velkou část testování provádět již během vlastního psaní procedur. Každou nově přidanou část makra je potřeba vyzkoušet. V mnoha případech se totiž nesetkáte jen s klasickými chybami vzniklými za běhu programu, ale celé makro může dávat nesmyslné výsledky, protože jste zapomněli na určitou maličkost, která pak ovlivní celý další průběh kódu. V části věnované chybovým rutinám jsme si na příkladu ukázali, jak napravit pokus o smazání neexistujícího listu. Jenže v aplikaci se můžete setkat i se zcela odlišnými typy chyb, které vzniknou např. v okamžiku, kdy určitá proměnná nabude hodnoty, kterou jste nepředpokládali. Typickým příkladem je záporná hodnota místo kladné, dělení nulou, proměnná bez přiřazené hodnoty apod. Editor Visual Basicu vám proto nabízí několik speciálních oken, která můžete použít pro ladění procedur a funkcí. Základním účelem těchto oken je umožnit testovat, jakých hodnot nabývají proměnné nebo celé výrazy během průběhu procedury. Procedura či funkce
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
On Error Resume Next Sheets("Záloha " & puvodni_jmeno).Delete
179
Excel - záznam, úprava a programování maker 180
ovšem normálně po spuštění proběhne najednou až do konce – pokud není přerušena kvůli nějaké chybě. Proto si nyní musíme říci něco více o režimu přerušení, kdy se makro zastaví v určitém místě a zbytek kódu prozatím nebyl proveden.
Stručně o režimu přerušení Režim přerušení (break) jsme si již krátce představili ve třetí kapitole. Můžete ho využívat mnoha způsoby, nejen k testování hodnot proměnných. Lze např.: Modifikovat již napsaný kód – ale dojde-li přitom ke změně, ovlivňující aktuální stav programu (např. chcete změnit název proměnné), objeví se po této změně okno hlášení, jehož potvrzením se vracíte znovu do režimu návrhu, abyste mohli proceduru spustit znovu od začátku. Tlačítkem Storno provedenou změnu zrušíte.
Obr. 10.4 Provedli jste změnu zdrojového kódu, která si vynutí návrat do režimu návrhu.
Zjišťovat, které procedury či funkce nebyly v okamžiku přerušení dokončeny. Měnit další průběh programu – vynecháním určité části kódu. Krokovat aplikaci, tedy vykonat vždy najednou jen jeden řádek kódu (nebo skupinu řádků). Přerušení programu lze dosáhnout několika způsoby – buď přímo z nabídky Editoru Visual Basicu (příkaz Break v nabídce Run či ikona na panelu nástrojů), stiskem kombinace kláves CTRL+BREAK, vložením příkazu Stop nebo tzv. zarážek do kódu. Do tohoto režimu se můžete přepnout i v případě, kdy dojde k chybě za běhu, kterou jste neošetřili (pomocí tlačítka Debug v okně hlášení o chybě), popř. i u chyb ošetřených, máte-li v okně Options editoru zapnutou na kartě General volbu Break On All Errors. Rychlé zobrazení hodnoty nějakého výrazu či proměnné Počínaje Excelem 97 můžete v režimu přerušení zobrazit aktuální hodnotu nějaké proměnné prostě tak, že nad její název v okně kódu najedete myší. Nástroj Auto Quick Info vám hodnotu ukáže ve žlutém rámečku u kurzoru myši.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Prvním nástrojem – a nejčastěji používaným – je okno Immediate, které již znáte. Kromě něj existují ještě tři další – okno Locals, Watches a Call Stack. V prvních dvou můžete sledovat aktuální hodnoty proměnných či vlastností v okamžiku přerušení běhu makra. V okně Locals vidíte hodnoty všech proměnných deklarovaných uvnitř procedury, ve které došlo k přerušení a také hodnoty proměnných deklarovaných na úrovni celého modulu. Okno Watches automaticky nezobrazí nic, ale umožňuje definovat si tzv. kukátka, které umožňují sledovat i hodnoty celých výrazů, nikoli jen proměnných. U rozsáhlejších řešení, kdy jsou jednotlivé procedury spouštěny řetězově (z procedury A zavoláte proceduru B a ta zase volá funkci C), je někdy nutné zjistit, které procedury a funkce nebyly v okamžiku přerušení běhu ukončeny. K tomuto účelu slouží poslední okno Call Stack, ve kterém jsou vidět názvy těchto nedokončených podprogramů. Zcela nahoře je název posledního volaného podprogramu. Všechna okna pro ladění jsou na sobě nezávislá a jejich zobrazení či skrytí se provádí přes nabídku View. Jestliže jsou vidět, najdete je uchycená ke spodní hraně okna kódu, odkud si je samozřejmě můžete přesunout jinam. Testování hodnot v okně Immediate Toto okno je vlastně jen obyčejné textové pole, do kterého můžete z programu vypisovat různá hlášení, ale stejně tak do něj můžete přímo psát příkazy i metody Visual Basicu nebo zjišťovat hodnoty proměnných během režimu přerušení. Přímo z kódu můžete do okna Immediate směrovat výpisy pomocí metody Print objektu Debug: Debug.Print "ActiveCell.Address", ActiveCell.Address
Metoda Print vytiskne do okna Immediate seznam svých parametrů, které ji předáte. Čárka oddělující parametry zajišťuje odskoky do dalších tiskových zón (tisková zóna je široká 14 znaků), je to v podstatě to samé co odskok tabulátorem. Pokud místo čárky použijete středník (nebo mezeru), bude proměnná zapisována bezprostředně za konec prvního řetězce. Na dalším obrázku vidíte obsah okna Immediate po provedení předchozího příkladu kódu. Nejdříve je vytištěn textový popis (abyste věděli, co vlastně vypsané číslo představuje), poté vlastní hodnota vlastnosti.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
Okna související s laděním chyb
181
Excel - záznam, úprava a programování maker 182
Tiskovou podobu výstupu můžete ovlivnit i speciálními klauzulemi Spc(n) nebo Tab(n). Bližší podrobnosti o nich se dozvíte v nápovědě k metodě Print. Hodnoty vlastností však můžete zjišťovat i přímo zápisem, ve kterém na začátku použijete otazník a za ním název vlastnosti. To již znáte ze třetí kapitoly.
Jak používat okno Locals V tomto okně vidíte hodnoty všech lokálních a modulových proměnných. V horní části okna je název procedury či funkce, v níž byl běh programu přerušen. Na obrázku vidíte okno Locals, které zobrazuje hodnoty dvou proměnných na úrovni modulu (soubor a odkaz) a dvou lokálních proměnných deklarovaných v proceduře Faktury. Okno Locals jasně říká, že proměnná soucet typu Variant nebyla inicializována (= nepřiřadili jste jí ještě žádnou hodnotu), takže obsahuje speciální hodnotu Empty. Proměnná soubor také nemusela být inicializována, ale protože je deklarována jako typ String, nedá se to poznat. V prvním sloupci je název proměnné nebo modulu, ve druhém aktuální hodnota, ve třetím datový typ (u typu Variant i podtyp). Vedle názvu modulu je značka osnovy, kterou se dá seznam modulových proměnných rozvinout, jako je tomu i na následujícím obrázku.
Obr. 10.5 Okno Locals se dvěmi lokálními proměnnými a dvěmi proměnnými lokálními v modulu.
Kukátka Pokud aplikace poskytuje výsledky, které neodpovídají zadání, popř. pokud některé proměnné nabývají zakázaných či nesmyslných hodnot, můžete do okna Watches přidat tzv. kukátka, zobrazující konkrétní hodnoty proměnných či vlastností, popř. platnost či neplatnost nějakého výrazu. Výhodou okna Watches je tedy možnost sledovat hodnoty výrazů a vlastností, pro proměnné postačí okno Locals.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Do řádku Expression zadáváte výraz, vlastnost či proměnnou (je-li v otevřeném okně kódu vybrán blok, objeví se v tomto řádku ihned po vyvolání okna). V sekci Context volíte, v jakém rozsahu chcete výraz, vlastnost či proměnnou sledovat. U lokálních proměnných to bude pochopitelně vždy pouze ta procedura, ve které daná proměnná existuje (jinak se místo hodnoty zobrazí v okně Watches hlášení Expression not defined in context). U modulových či veřejných proměnných nebo výrazů a u vlastností objektů už lze kontext volit – máte-li problémy jen v rámci jedné procedury, postačí opět kontext této procedury. Neznáte-li místo výskytu chyb přesněji, zadejte kontext (All procedures), a to buď v jednom modulu, či ve všech modulech, případně v rámci celého sešitu (rozvírací seznam Module). Ve spodní sekci Watch type si můžete vybrat mezi třemi různými typy. Nejčastější je první typ Watch Expression, kdy prostě sledujete hodnotu dané proměnné nebo vlastnosti, případně platnost výrazu. V některých případech však bývají výhodnější zbylé typy kukátek. První z nich (Break When Value Is True) přeruší běh aplikace pokaždé, když sledovaný výraz začne platit (např. hodnota proměnné překročí nastavenou horní či dolní mez nebo když je proměnná inicializována). Druhý typ (Break When Value Changes) přeruší běžící program, když se hodnota proměnné či platnost výrazu změní. Můžete ho využít např. pro kontrolu opakovaných volání nějakého podprogramu, v němž si nadefinujete proměnnou jako sčítač průběhu. Nebo vždy, když se vám neočekávaně mění nějaká hodnota a vy chcete přijít na to, kdy k této změně dojde. Vytvořené kukátko lze kdykoli později upravit pomocí nabídky Debug → Edit Watch … (nebo přes místní nabídku v okně Watches). Odstranění kukátka je také jednoduché – vyberete ho a stisknete klávesu DELETE.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
Obr. 10.6 Dialogové okno pro nastavení vlastností kukátka.
183
Excel - záznam, úprava a programování maker 184
Rychlé kukátko Mimo kukátek trvalého rázu, které jsou vidět v okně Watches, lze v režimu přerušení vytvořit i dočasný náhled na hodnotu nějaké proměnné či výrazu. Stačí k tomu vybrat v okně kódu příslušný výraz či proměnnou do bloku a zadat příkaz Debug → Quick Watch … (klávesová zkratka SHIFT+F9). Objeví se dialogové okno s hodnotou proměnné či údajem o platnosti vybraného příkazu. Stisknutím tlačítka Add lze vybraný výraz či proměnnou změnit na trvalé kukátko. Zarážky (breakpoint) Bude-li aplikace dávat nesmyslné výsledky, je také možné ji rozčlenit pomocí zarážek. Zarážka automaticky přeruší program, jakmile jeho běh dospěje k řádku, na kterém je zarážka umístěna. Tak je možné postupně (pomocí kukátek a dalších nástrojů) zjistit, ve kterém místě je zakleta příčina problému.
Zarážky lze nastavit v režimu návrhu nebo přerušení, a to tak, že na řádek kódu, před jehož provedením se má aplikace zastavit, přesunete kurzor a stisknete klávesu F9. Ekvivalentní příkaz v nabídce Debug se jmenuje Toggle Breakpoint, případně použijte ikonu na panelu nástrojů Debug. Po nastavení zarážky se příslušný řádek podbarví hnědě.
Zarážky zůstávají nastaveny jen během práce s Editorem Visual Basicu, nejsou nikde uloženy!
Místo zarážek lze rovněž použít přímo v kódu příkaz Stop, ten je však nutné z kódu po dokončení testovací fáze odstranit. Krokování procedur V režimu přerušení je možné aplikaci krokovat – neboli provádět vždy jen jeden řádek kódu (odchylky jsou uvedeny níže). V praxi se dá krokování využít v těchto případech: Potřebujete projít celou proceduru či funkci řádek po řádku, protože nejste schopni objevit místo vzniku nějaké chyby nebo chcete blíže prozkoumat, co všechno se v rámci jednotlivých příkazů děje. Krokování v tomto případě začínáte již od prvního výkonného řádku procedury. Většina kódu funguje dobře, jen v určitých místech máte potíže. Pomocí zarážky můžete první část procedury provést najednou a až se zastavíte na určitém řádku, zbytek kódu odkrokujete.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Obr. 10.7 V okně kódu je žlutě podbarven řádek kódu, který bude proveden po stisku klávesy F8.
Základní varianta krokování (Step Into) je charakteristická tím, že je ve všech případech proveden pouze jeden výkonný příkaz – a to i v případě, že se v krokované proceduře nachází volání jiné vlastní procedury či funkce (tedy ne vestavěných příkazů a funkcí Visual Basicu). V takovém případě se po zavolání dané procedury automaticky v okně kódu objeví její definice a vy budete krokovat i tyto funkce či procedury. Další variantou krokování je volba Step Over, kdy se rovněž provádí vždy jeden výkonný příkaz, ovšem s výjimkou volání jiných podprogramů. Ty jsou vykonány najednou a poté je provádění zastaveno před následujícím příkazem v podprogramu, v němž krokujeme. Tato varianta se hodí v těch případech, kdy víte, že kód volaných procedur je v pořádku. Třetí varianta Step Out se dá použít ve volané proceduře. Zajistí provedení zbytku této procedury a návrat do procedury volající, přičemž běh programu se zastaví na řádku následujícím po řádku, ve kterém byla volána procedura, jejíž běh chcete najednou dokončit. Srozumitelně řečeno – jestliže pomocí Step Into krokujete proceduru a přejdete do jiné vlastní procedury, nemusíte ji celou krokovat, ale v určitém okamžiku lze její kód dokončit právě volbou Step Out. Poslední zajímavý příkaz pro spouštění kódu v nabídce Debug se jmenuje Run to Cursor a umožňuje najednou vykonat některá „dlouhá“ místa v kódu (cykly…). Jestliže při krokování kódu dojdete k místu s delším cyklem, můžete nastavit kurzor na první řádek za tímto cyklem a stisknout C TRL+F8 nebo zadat v nabídce Debug uvedený příkaz. Celý cyklus bude proveden najednou a Visual Basic se zastaví na řádku, ve kterém je umístěn kurzor. Možné vynechání určitých míst v kódu Někdy je třeba za běhu programu přeskočit určitou část – o níž víme, že v ní je jakási záhadná chyba, kterou nejsme schopni zatím odhalit – a pokračovat až o kus dále. V takovém případě na začátku krizového místa nastavíte zarážku a po přerušení běhu makra umístíte kurzor na místo, kde má program pokračovat. Poté z nabídky Debug zadejte příkaz Set
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
Krokování procedury se dá zahájit několika způsoby. V dialogovém okně Makro najdete tlačítko Krokovat s vnořením. Přímo v editoru proceduru krokujete buď klávesou F8 (na začátku musí být kurzor kdekoli uvnitř požadované procedury) nebo pomocí příkazu Step Into z nabídky Debug (klávesová zkratka F8). Řádek kódu, který bude proveden po dalším stisku klávesy F8, je v okně kódu zbarven žlutě a v levém šedém pruhu je označen šipkou. Nacházejí-li se na jednom řádku příkazy dva, je podbarven jen ten příkaz, který bude dále proveden.
185
Excel - záznam, úprava a programování maker 186
Next Statement. V tomto okamžiku budete mít v okně kódu dva podbarvené řádky – řádek s nastavenou zarážkou bude hnědý a řádek s kurzorem žlutý. Dále můžete kód běžným způsobem krokovat. Všechny řádky mezi zarážkou a řádkem s kurzorem budou vynechány. Příkaz Show Next Statement v nabídce Debug se používá v jiných situacích – přemístí kurzor do řádku, kde bude aplikace pokračovat. I to se někdy může hodit, zvláště tehdy, když začnete ztrácet v programu orientaci.
10.4 Stručný přehled nejdůležitějších chyb Následující tabulka rozhodně není úplným výčtem možných chyb vzniklých za běhu programu, ale pouze těch, s nimiž se setkáte nejčastěji.
Číslo
Popis a příčiny vzniku
5
Nepovolené volání funkce; příčinou může být parametr mimo povolený rozsah, neplatný index, případně nepovolené použití funkce.
6
Výsledná hodnota je mimo povolený rozsah daného datového typu; typické při návratu hodnoty z funkce nebo při matematických operacích.
7
Málo paměti k dokončení operace (máte příliš mnoho otevřených souborů, rovněž můžete mít deklarováno příliš mnoho veřejných proměnných).
9
Odkaz na neexistující prvek pole (index prvku je mimo deklarovaný rozsah nebo jste uvedli špatný počet rozměrů pole).
11
Dělení nulou. Jedna ze nejčastějších chyb vůbec.
13
Špatný datový typ proměnné nebo vlastnosti. Rovněž velmi častá chyba.
18
Procedura byla přerušena uživatelem (nejspíše stiskem klávesové kombinace CTRL+BREAK).
20
Chyby v rutině na ošetření chyb (špatně umístěný příkaz Resume).
28
Přetečení zásobníku. Možné příčiny: příliš mnoho volaných funkcí či procedur; zacyklené volání podprogramů; příliš mnoho lokálních proměnných (lze řešit jejich deklarováním na úrovni modulu nebo s klauzulí Static).
35
Volaný podprogram nebyl nalezen (špatně zapsaný název procedury).
51
Interní chyba Visual Basicu. Pokud není způsobena voláním příkazu Error nebo metodou Raise, je to špatné, protože neznáte příčinu. Opakovaný výskyt této chyby vyřeší (možná) technická podpora Microsoftu.
53
Hledaný soubor nebyl nalezen.
55
Soubor je již otevřen.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Popis a příčiny vzniku
58
Daný soubor již existuje.
68
Zařízení je nedostupné (vypnuté, porucha na síti apod.).
70
Přístup byl odmítnut (disketa chráněná proti zápisu nebo uzamčený soubor).
71
Disk není připraven (chybí disketa apod.).
76
Složka (adresář) nebyla nalezena.
91
Nesprávně použitá objektová proměnná (objekt neexistuje nebo má hodnotu Nothing).
380
Pokus o přiřazení neplatné hodnoty do vlastnosti.
422
Daná vlastnost objektu neexistuje.
423, 438
Daná vlastnost nebo metoda objektu neexistuje.
424
Chybí odkaz na objekt (název vlastnosti bez názvu objektu, špatně zapsaný název objektu, přiřazení odkazu na objekt bez příkazu Set).
446
Daná metoda objektu nepodporuje pojmenované parametry (musíte použít parametry v přesném pořadí).
448
Špatně zapsaný název pojmenovaného parametru.
449
Nesprávný počet parametrů nebo vynechaný povinný parametr.
460
Chybný formát dat při práci se schránkou.
482
Problémy s tiskem.
JAK
Ř EŠI T
CHYBY
V
MAKRECH
Excel - záznam, úprava a programování maker
Číslo
187
pracovního listu, 102
E
převodní, 84 Right, 110
editor
StrComp, 111
barvy kódu, 47
struktura, 68
kontrola zápisu kódu, 68
trigonometrické, 112
okno Immediate, 47
Trim, 84, 110
popis prostředí, 37
UBound, 86
průzkumník projektu, 37
UCase, 111
režimy práce, 41
vrácení chybové hodnoty, 114
spuštění, 34
vytváření, 68
Excel
základní pojmy, 65
typy listů, 17
Ch
F funkce
chyby druhy chyb, 176
Array, 87
chybové rutiny v kódu, 178
CInt, 111
kompilační, 177
CLng, 111
ve volaných procedurách, 180
Fix, 111
za běhu programu, 177
InputBox, 107 InStr, 110 InStrRev, 110 Int, 111
J jazyk VBA
IsArray, 87
cykly, 93
IsEmpty, 83
historie, 16
IsNull, 84
komentáře, 43
LBound, 86
metody, 59
LCase, 111
odkazy na objekty, 56
Left, 110
předávání parametrů, 88
Len, 109
přehled, 52
logaritmické, 112
rozhodovací bloky, 90
LTrim, 110
typy listů, 17
Match, 136
události, 60
Mid, 110
umístění kódu, 40
MsgBox, 103
vlastnosti, 59
REJST Ř Í K
Excel - záznam, úprava a programování maker
Rejstřík
189
Excel - záznam, úprava a programování maker 190
jazyk XLM, 16
Clear, 130 ClearComments, 130
K
ClearContents, 130
kolekce. viz objekty:kolekce konstanty, 70 deklarace, 79
ClearFormats, 130 Columns, 121 Copy, 129 Copy (list), 146 Cut, 129
L
Delete, 131 End, 125
ladění krokování procedur, 188 kukátka, 186 okna pro ladění, 185 režim přerušení, 184 vynechání části kódu, 190 zarážky, 188
Find, 134 FollowHyperlink, 160 GetOpenFileName, 155 GetSaveFilename, 159 GoTo, 170 InputBox, 107 Insert, 131 LinkSources, 161
M
Move, 146 Offset, 123
makra adresování buněk, 29
OnKey, 170
komentáře, 43
OnTime, 171
nahrávání, 20
Open, 155
místo uložení, 22
Paste, 147
možnosti, 21
PasteSpecial, 130
nevýhody, 20
PasteSpecial (list), 147
Relativní odkaz, 30
PrintOut, 148
názvy maker, 21
Protect, 149, 160
osobní sešit maker, 23
Quit, 172
praktické použití, 14
Range, 119
způsoby spuštění, 15
Replace, 135
způsoby vytvoření, 15
Route, 159
metody
Rows, 121
Areas, 124
SaveAs, 148, 159
AutoFill, 132
SaveCopyAs, 159
AutoFit, 140
SendMail, 159
AutoFormat, 139
Show, 172
Cells, 120
Sort, 136
REJST Ř Í K
RoutingSlip, 160
Unprotect, 151, 161
Selection, 123
UpdateLink, 161
Sheets, 142
Wait, 171
Styles, 139
Excel - záznam, úprava a programování maker
SpecialCells, 132
třídy, 53
moduly
Workbook
části modulů, 64
metody, 157
modul ThisWorkbook, 40
vlastnosti, 156
typy modulů, 40
Workbooks, 155
O
Worksheet metody, 145
objekty
vlastnosti, 144
Application, 166 metody, 169 vlastnosti, 168
Worksheets, 143 odkazy ActiveChart, 142
Borders, 138
ActiveSheet, 142
Dialogs, 172
ActiveWorkbook, 142
DocumentProperties, 162 Err, 177 Font, 138
ThisWorkbook, 142 operátory ampersand, 82
hierarchie, 55
aritmetické, 72
HPageBreaks, VPageBreaks, 154
logické, 72
Charts, 142
priorita, 72
Interior, 138
přehled, 71
kolekce, 54 PageSetup, 151 přehled, 52 Range
P pole proměnných
formátování, 137
dynamické, 85
hledání a záměna obsahu, 134
statické, 85
kopírování buněk, 129
vícerozměrná, 86
metody, 128
základní pojmy, 85
odkazy na buňky, 118
procedury
práce s komentáři, 132
parametry, 87
řazení, 136
struktura, 67
vkládání vzorců, 131
vytváření, 65
vlastnosti, 126
základní pojmy, 65
základní pojmy, 118 zpracování v cyklu, 125
proměnné datové typy, 73
REJST Ř Í K
191
Excel - záznam, úprava a programování maker 192
definice pojmu, 69
Option Base, 85
deklarace
Option Explicit, 78
problémy, 77
pravidla pro názvy, 70
vynucená, 78
Resume, 180
objektového typu, 115
Select Case, 90
pravidla pro názvy, 70
With…End With, 45, 114
přiřazení hodnoty, 69
zápis na více řádků, 69
rozsah platnosti, 76 statické, 77 typ Boolean, 84 typ String, 84 typ Variant, 83 hodnota Null, 83 zápis hodnot, 82 životnost, 77 příkazy Do…While, 94 For Each…Next, 95 For…Next, 93 If…Then…Else, 92 MsgBox, 103 On Error, 178
REJST Ř Í K
V vlastnosti Count, 122 CurrentRegion, 123 Cursor, 167 CutCopyMode, 130 EntireColumn, 122 EntireRow, 122 ScreenUpdating, 166 StatusBar, 167 UsedRange, 123 výrazy základní pojmy, 71