Programování v Excelu 2000, 2002, 2003

# "#

$ % # " $ "!

)%#% $"!), ,##, !% %+%+ !*"$ ("# #( (")% "$,$"##$# ,$)% $! %/#/%% %+%#% '# %#%-/#%,#%+ $ #% %&%+%"#"#

$"#"##"$ /# $ .#% !"( ) #+%"% %$%% )"#"#,#%'-*)$'/( # "#% ( +-$ #% +%,"%"%)% )$%#, "&/ #" $" "/% $!

Úvod.................................................................................. 10

1

Události v Excelu ................................................................ 13 1.1

Využití událostí ve vlastních aplikacích............................. 15 Umíst÷ní procedur událostí........................................... 15 Jak zabránit výskytu událostí ........................................ 18

1.2

Události sešitu........................................................... 19 PĠehled událostí sešitu ................................................ 19 PĠíklady využití základních událostí sešitu......................... 21

1.3

Události listİ ............................................................. 23 PĠehled událostí listu ................................................... 24 PĠehled událostí grafİ (listu typu graf)............................ 28

1.4

2

Události aplikace Excel ................................................ 31

Práce s grafy pomocí jazyka VBA ......................................... 33 2.1

Úvod………………………………………………... ................. 34 Grafy v objektovém modelu VBA ................................... 34

2.2

Základní postupy pĠi práci s grafy .................................. 37 VytvoĠení nového grafu pomocí kódu VBA ....................... 37

2.3

Práce s datovými Ġadami ............................................. 42 Kolekce SeriesCollection .............................................. 42 Práce s jednotlivými datovými Ġadami ............................. 45 Pokroçilé techniky práce s grafy.................................... 48 Automatické pĠizpİsobení hodnot osy Y .......................... 48

2.4 2.5

Formátování grafu ...................................................... 51 PĠehled vlastností a metod objektİ ................................ 52 Vlastnosti objektu ChartObject ..................................... 52 Metody objektu ChartObject ........................................ 54 Vlastnosti objektu Chart .............................................. 54 Metody objektu Chart ................................................. 56

3

TĠídy objektİ a moduly tĠíd .................................................. 57 3.1

Proç, k çemu, jak ....................................................... 58

3.2

VytvoĠení nové tĠídy a konkrétní instance........................ 59 První pokus ............................................................... 60 VytváĠení vlastností a metod ........................................ 62

Programování v Excelu 2000, 2002, 2003

Obsah

Zachycení událostí objektu Application pomocí tĠídy ........... 67 Zachycení událostí vložených grafİ ................................. 68 PĠíklad: použití událostí u vloženého grafu ........................ 69

OBSAH

5

Programování v Excelu 2000, 2002, 2003 6

4

FormuláĠe .......................................................................... 73 4.1

VytvoĠení formuláĠe, jeho zobrazení a ukonçení pomocí VBA ............................................ 75 Modul kódu formuláĠe..................................................76 Toolbox (souprava nástrojİ)..........................................76 Spušt÷ní formuláĠe .....................................................77 Modální a nemodální formuláĠe ......................................78 UzavĠení formuláĠe .....................................................79

4.2 4.3

Okno Properties .........................................................80 Ovládací prvky ...........................................................82 PĠidání ovládacího prvku do formuláĠe .............................82 Výb÷r ovládacího prvku na formuláĠi ...............................84 Konvence pro názvy formuláĠİ a ovládacích prvkİ .............85 PoĠadí ovládacích prvkİ na formuláĠi ..............................86 Dostupnost ovládacích prvkİ a pĠesun mezi nimi ...............88 Úprava ovládacích prvkİ ve formuláĠi .............................89

4.4

PĠehled typİ ovládacích prvkİ .......................................91 PĠíkazové tlaçítko (CommandButton) ..............................91 Popisek (Label)...........................................................92 Textové pole (TextBox) ................................................93 Zaškrtávací políçko (CheckBox) .....................................94 PĠepínaç (OptionButton) ..............................................95 Seznam (ListBox) .......................................................96 Rozvírací seznam (ComboBox) .......................................99 Rámeçek (Frame) .....................................................100 PĠepínací tlaçítko (ToggleButton) .................................101 åíselník (SpinButton) ................................................. 101 Posuvník (ScrollBar) ..................................................102 RefEdit .................................................................102 Vícenásobná stránka (MultiPage) .................................102 Karty (TabStrip) .......................................................103 Obraz (Image) ..........................................................104

4.5

Odkazy na formuláĠe a ovládací prvky v kódu VBA...........105 PĠíklad – formuláĠ pro zobrazení informací o listu ...........106 Další pĠíklad – seznam všech pojmenovaných názvİ v sešitu.. 108 Ukázka práce se zaškrtávacími políçky a pĠepínaçi ..........111

4.6

Zpracování událostí souvisejících s klávesnicí a myší ........113 Události klávesnice....................................................115

OBSAH

4.7

Používání ovládacích prvkİ pĠímo na pracovním listu ........116

4.8

Základní spoleçné vlastnosti a metody ovládacích prvkİ a uživatelských formuláĠİ ...................................119

Další spoleçné vlastnosti ............................................ 121 Metody formuláĠe nebo ovládacích prvkİ ...................... 121 4.9

PĠehled dİležitých vlastností a metod objektu UserForm .................................................... 122 Vlastnosti ............................................................... 122 Metody ................................................................. 124 Základní události formuláĠe......................................... 124

4.10 Sdílení formuláĠİ ...................................................... 124

5

Práce s externími soubory, export a import souborİ ........... 127 5.1

Práce s externími soubory ......................................... 128 Základní operace se soubory ...................................... 128

5.2

Jednoduché çtení a zápis textových souborİ .................. 132

5.3

Vlastní manipulace s textovými soubory ........................ 136 OtevĠení textového souboru ....................................... 137 Naçtení obsahu textového souboru .............................. 138 Zápis údajİ do textového souboru................................ 139 Import dat do listu Excelu pomocí pĠíkazu Line Input # ..... 139 Naçítání textového souboru pĠíkazem Input # ................. 141 Zápis do souborİ – pĠíkazy Write # a Print # ................ 141 Export oblasti bun÷k do textového souboru pomocí pĠíkazu Write # ............................................. 142 Využití pĠíkazu Write pro zápis údajİ o chybách za b÷hu programu .................................................... 143

5.4

Ukládání rİzných nastavení do registru Windows ............ 144 Použití pĠíkazİ SaveSetting, DeleteSetting a funkcí GetSetting, GetAllSettings.............................. 145 Další možnosti pro ukládání nastavení........................... 145

6

Databáze a seznamy v Excelu ............................................. 147 Základní terminologie ................................................ 149 Ruçní správa seznamu pomocí nabídky Excelu................. 149 6.1

VytvoĠení seznamu ................................................... 149 PoĠizování záznamİ v seznamu.................................... 150

6.2

ğazení, filtrování a další operace s databázemi Excelu pomocí VBA ............................................................ 150

6.3

PĠíklad jednoduché aplikace využívající formuláĠe (AdresáĠ) ............................................................... 155


Vlastnosti ovlivĒující vzhled a chování formuláĠe çi ovládacích prvkİ ................................................... 119

Jak naçítat a ukládat data ......................................... 156 åinnosti pĠi otevĠení formuláĠe.................................... 157

OBSAH

7


Obecné procedury pro naçtení záznamu, uložení pİvodních hodnot a uložení zm÷n ........................158 Tlaçítka pro pĠechod na další (pĠedchozí záznam) ............161

7

Pracovní prostĠedí Excelu – nabídky a penely nástrojİ ........ 165 7.1

åásti pracovní plochy Excelu a jejich zobrazení çi skrytí pomocí VBA .................................................166

7.2

Panely nabídek a panely nástrojİ – možnosti manipulace s nimi ........................................170 Kolekce CommandBars, objekty CommandBar ...............170 Objekt CommandBarControl a objekty vyjadĠující jednotlivé typy ovládacích prvkİ .....................172

7.3

Práce s nabídkami a panely nástrojİ.............................173 VytváĠení a odstran÷ní vlastních panelİ a nabídek ...........173 Úprava existujících panelİ a nabídek .............................175

7.4

Místní nabídky ..........................................................181 VytvoĠení, úprava a odstran÷ní nové místní nabídky .........182

7.5

PĠehled vlastností a metod objektİ CommandBars a CommandBar ...................................183 Vlastnosti kolekce CommandBars ................................183 Metody kolekce CommandBars ...................................183 Vlastnosti objektu CommandBar ..................................184 Metody objektu CommandBar .....................................185 Základní vlastnosti a metody ovládacích prvkİ na panelech pĠíkazİ...................................................186

8

DoplĒky v Excelu............................................................... 187 8.1

Úçel doplĒkİ a pĠehled doplĒkİ dodávaných pĠímo s Excelem ........................................... 189

8.2

Obecné zásady pro vytváĠení doplĒkİ ...........................190

8.3

PĠíklad doplĒku – pĠevod do HTML...............................192 FormuláĠ .................................................................192 Programový kód doplĒku ............................................193

9

Spolupráce s dalšími programy .......................................... 197 9.1

Spoušt÷ní a aktivace jiných aplikací ..............................198 Funkce Shell ............................................................198 Spušt÷ní programİ pomocí technologie Automation .........199 PĠepínání mezi n÷kolika programy ................................202

9.2

Technologie Automation a její využití pro spolupráci s Wordem nebo Outlookem ........................................203 PĠíklady práce s Wordem...........................................203 PĠíklady práce s Outlookem ........................................205

8

OBSAH

Funkce Windows API......................................................... 211 10.1 Co se skrývá za zkratkou API ..................................... 212 Deklarace API funkcí a pĠíkazİ .................................... 212 10.2 Konstanty a uživatelské typy prom÷nných v API ............. 213 Další „lahİdky“ spojené s voláním rutin API ................... 215 N÷kolik varování ....................................................... 216 10.3 Základní pĠíklady na používání Windows API ................... 216 Práce s disky, složkami a soubory ............................... 217 Práce se systémem Windows..................................... 219 API pro práci se sítí .................................................. 221 Další vhodné ukázky .................................................. 222 Kde hledat dále? ...................................................... 223 RejstĠík ........................................................................... 225

OBSAH


10

9


Úvod 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ší. Tato kniha volnČ navazuje na knihu s názvem Excel 2000, 2002, 2003 – záznam, úprava a programování maker (Grada, 2004), v níž 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. Kniha, již však držíte v rukou právČ teć, 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

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 o Možnosti o Zobrazení o 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Ĥ. 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.

ÚVOD


Vložit o Rám

11

1 1. Události v Excelu

13


1.

Události v Excelu

V prvním dílu uþebnice byly události zmínČny jen velmi struþnČ, nyní pĜišel þas vČnovat se jim podrobnČji. V dalších kapitolách se s jejich využitím budete setkávat velmi þasto. PĜipomeĖme si, co už víte – událost (angl. event) je akce vyvolaná uživatelem nebo systémem, kterou objekt (obecnČ, nejenom v Excelu) dokáže rozpoznat. Mezi takové akce patĜí napĜ. otevĜení þi uzavĜení sešitu, klepnutí þi poklepání myší na objekt, zmČna hodnoty v buĖce apod. Každá událost má pĜiĜazenu svoji proceduru (Ĝíká se jí událostní procedura, angl. event procedure), která standardnČ neobsahuje žádný kód. Napíšete-li však nČjaký kód do událostní procedury, bude spuštČn pĜi každém výskytu dané události, tedy napĜ. pĜi otevĜení nČjakého sešitu nebo pĜed jeho uzavĜením, pĜed pĜepoþítáním sešitu, po zmČnČ hodnoty v buĖce apod. Objekty Excelu umí reagovat na velké množství událostí. Rozsah této knihy proto umožní pĜedstavit si jen nČkteré z nich, struþný pĜehled všech událostí je uveden vždy v pĜíslušné þásti kapitoly: Události sešitu: Probíhají v rámci sešitu. Jako pĜíklad lze uvést událost Open (vytvoĜení nebo otevĜení sešitu), BeforeSave (sešit bude uložen), BeforePrint (sešit bude tištČn) nebo NewSheet (je pĜidáván nový list). Události listu: Jsou vyvolány pro urþitý list. Sem patĜí napĜ. událost Activate (list je aktivován), SelectionChange (zmČna výbČru na listu) þi Calculate (list je pĜepoþítáván). Události grafu: Jsou vyvolány pro urþitý list typu graf. PatĜí mezi nČ události Resize (po zmČnČ velikosti grafu) þi SeriesChange (zmČna datového bodu v nČjaké datové ĜadČ). Pro zachytávání událostí v grafech vložených na pracovním listu je nutné použít modul tĜídy (ty budou podrobnČ probrány ve tĜetí kapitole). Události celé aplikace: Probíhají na úrovni celé aplikace (Excelu). Mezi nejvýznamnČjší patĜí napĜ. NewWorkbook (vytvoĜení nového sešitu) nebo SheetChange (probČhla zmČna buĖky v libovolném sešitu). Pro práci s událostmi aplikace je vždy nutný modul tĜídy. Události formuláĝĥ a jejich ovládacích prvkĥ: Vyskytují se v rámci urþitého formuláĜe nebo ovladaþe. Události, které neprobíhají v rámci žádného objektu: Sem patĜí dvČ události na úrovni celé aplikace – OnTime a OnKey. FunkþnČ se odlišují od jiných událostí. NČkteré akce spouští více událostí za sebou. NapĜ. pĜi otevírání sešitu probČhnou postupnČ tyto události sešitu: Open Activate WindowActivate

14

UDÁLOSTI

V

EXCELU

BeforeClose WindowDeactivate Deactivate Posloupnosti událostí bývají mnohem složitČjší, než si mĤžete pĜedstavit na základČ tČchto pĜíkladĤ. V obou ukázkách šlo navíc jen o události na úrovni sešitu. NapĜ. pĜi pĜidání nového listu do sešitu však probČhnou i události na úrovni listu a celé aplikace. Posloupnost událostí navíc nemusí být vždy zcela logická – pĜi pĜidání nového listu do sešitu probČhne nejdĜíve událost SheetActivate (aktivace listu) a až poté událost WorkbookNewSheet (nový list v sešitu). Další sekvence mĤžete zkoumat sami podle libosti, nejjednodušší je zapsat do každé zkoumané událostní procedury pĜíkaz: Debug.Print "Název_Procedury"

Bližší prĤzkum posloupností, v jakých jednotlivé události probíhají, se ovšem v praxi vyplatí pouze v pĜípadech, kdy využíváte vČtší množství událostí. Chcete-li spustit jednu proceduru pĜi otevĜení sešitu a druhou pĜi pĜepoþtu urþitého listu, není tĜeba se niþeho obávat.

1.1

Využití událostí ve vlastních aplikacích

Aplikace Ĝízené událostmi jsou v systému Windows rozšíĜeny tak, že už si to ani neuvČdomujeme. Každé dialogové okno je typickou ukázkou – po klepnutí na tlaþítko nebo po zmČnČ hodnoty urþitého ovladaþe je okamžitČ provedena nČjaká operace. Klepnutí nebo poklepání myší, pohyb kurzorem nebo stisk klávesy jsou také událostmi, které je každý prvek v dialogovém oknČ schopen rozeznat. Umíst÷ní procedur událostí Velmi þastou chybou mnoha zaþáteþníkĤ (v diskusních konferencích narazíte na spoustu dotazĤ) je fakt, že procedury, které by mČly pĜi vzniku nČjaké události probČhnout, nejsou spuštČny. PĜíþina problému je jednoduchá – událostní procedury je tĜeba vždy zapsat na správné místo. V oknČ Project mĤže mít každý sešit tyto þásti: Objekty jednotlivých listĥ: Jejich poþet odpovídá poþtu pracovních listĤ v sešitu. Objekty jednotlivých listĥ typu graf: PĜidáte-li do sešitu graf na samostatném listu. Objekt ThisWorkbook: PĜedstavuje celý sešit. Standardní moduly VBA ve složce Modules: Do tčchto modulĥ nikdy neumisģujte událostní procedury. Jejich hlaviþky se ve standardních modulech ani nevyskytují, musely byste je tam ruþnČ dopisovat.


PĜi uzavírání sešitu probíhají (minimálnČ) tyto události sešitu:

Moduly tĝíd: Události v modulech tĜíd si vysvČtlíme ve tĜetí kapitole. Formuláĝe: Podrobný výklad událostí u formuláĜĤ je ve þtvrté kapitole.

UDÁLOSTI

V

EXCELU

15


Obrázek 1.1 RĤzné þásti projektu Excelu.

Když myší poklepete na objekt listu nebo objekt ThisWorkbook, otevĜe se okno kódu daného objektu. V jeho horní þásti jsou dva rozevírací seznamy. Levý seznam obsahuje Ĝádek (General) a Ĝádky Worksheet, Chart nebo Workbook. ýást General znáte z klasických modulĤ: slouží pro deklarování promČnných, které mají svĤj rozsah platnosti na úrovni modulu, v praxi tu tedy budete deklarovat promČnné, jež budete používat ve více událostech, probíhajících v daném modulu. ҏ

Pozor na to, že promČnné, které deklarujete v modulu ThisWorkbook nebo v modulech listĤ jako veĜejné (Public), nejsou dostupné z žádného jiného modulu, tedy ani z jiného modulu listu nebo ze standardních modulĤ! VeĜejné promČnné musíte vždy deklarovat klíþovým slovem Public ve standardním modulu! Stejné pravidlo platí i pro všeobecné procedury – ty musí být umístČny také ve standardním modulu. VBA vám sice umožĖuje vkládat je i do modulĤ listĤ nebo do modulu ThisWorkbook, ale takové procedury je možné spouštČt jen v rámci daného modulu! ҏ

Po krátké odboþce se vraĢme zpČt k oknu kódu pro moduly listĤ þi modul ThisWorkbook. Vyberete-li v levém seznamu druhý Ĝádek (Worksheet, Chart nebo Workbook), objeví se v pravém seznamu všechny události, které mĤžete pro list þi sešit využít. Jakmile v pravém rozvíracího seznamu vyberete nČjakou událost, vloží editor VBA do okna modulu hlaviþku odpovídající (prázdné) procedury: Private Sub Workbook_Activate() End Sub

Veškerý kód zapsaný mezi tyto dva Ĝádky bude proveden pĜi každém vyvolání dané události.

16

UDÁLOSTI

V

EXCELU

NČkteré událostní procedury obsahují v závorce za názvem procedury ještČ seznam parametrĤ. Ty jsou vestavčny a vy jejich deklarace v žádném pĜípadČ nemĤžete mČnit! StejnČ tak nelze do seznamu parametrĤ pĜidávat parametry nové. Parametry událostí se vyskytují tam, kde jedna událost mĤže probČhnout pro více rĤzných objektĤ, nebo kde vám systém pĜedává ještČ dodateþné údaje k dané události. NapĜ. u události pohybu myší nad objektem (MouseMove) dostanete pĜedány souĜadnice kurzoru (pĜi události stisknutí tlaþítka myši – MouseDown – zase údaj o tom, které tlaþítko bylo stisknuto). Dalším pĜíkladem je událost SheetActivate (je na úrovni sešitu, proto ji najdete v modulu ThisWorkbook) probČhne pĜi aktivaci nČkterého listu v sešitu. Aby bylo možné urþit, který list byl aktivován, je odkaz na aktivovaný list (tedy na objekt Worksheet nebo Chart, deklarovaný ovšem jako Object) pĜedán jako parametr procedury: Private Sub Workbook_SheetActivate(ByVal Sh As Object) End Sub

Deklarace parametru jako typ Object má v tomto pĜípadČ urþité nepĜíjemné dĤsledky. Musíte totiž rozlišovat, zda byl aktivován list typu graf nebo pracovní list. NapĜ. následující kód probČhne v poĜádku, bude-li aktivován pracovní list: Private Sub Workbook_SheetActivate(ByVal Sh As Object) MsgBox Sh.Name ' název listu MsgBox Sh.Rows.Count ' poþet ĜádkĤ v listu End Sub

PĜi aktivaci listu typu graf by pochopitelnČ došlo k chybČ za bČhu programu ve druhém Ĝádku, protože list typu graf neobsahuje žádné Ĝádky a tudíž odkaz na kolekci Rows není platný. Jak si mĤžete ovČĜit, který typ listu byl aktivován? Slouží k tomu operátor TypeOf, volaný napĜ. v bloku If … End If: ' zde bude operace, kterou je možné provádČt jen pro pracovní listy Private Sub Workbook_SheetActivate(ByVal Sh As Object) If TypeOf Sh Is Worksheet Then ElseIf TypeOf Sh Is Chart ' a zde operace pro listy typu graf End If End Sub

UDÁLOSTI

V

EXCELU


Obrázek 1.2 V pravém rozevíracím seznamu okna kódu vidíte události, které mĤžete zachytávat na úrovni listu þi sešitu.

17


V nČkterých událostních procedurách se objevuje logický parametr Cancel. NapĜ. deklarace událostní procedury BeforeClose v modulu ThisWorkbook vypadá takto: Private Sub Workbook_BeforeClose(Cancel As Boolean) End Sub

Výchozí hodnota pĜedaného parametru Cancel je False. Jestliže však ve vlastním kódu procedury nastavíte tento parametr na True, nebude sešit uzavĜen. Ukázku možného využití pĜedstavuje zákaz uzavĜení sešitu, zapomnČl-li uživatel vytisknout denní pĜehled: Private Sub Workbook_BeforeClose(Cancel As Boolean) dotaz = "Vytisknul jste denní pĜehled zmČn?" odpoved = MsgBox(dotaz, vbYesNo, "Ukonþení práce...") If odpoved = vbNo Then Cancel = True End Sub

Procedura Workbook_BeforeClose je spuštČna po vydání pĜíkazu k uzavĜení sešitu (Soubor o ZavĜít, Soubor o Konec apod.). Procedura nejdĜíve zobrazí okno hlášení a odpoví-li uživatel zápornČ klepnutím na tlaþítko Ne, bude parametr Cancel nastaven na hodnotu True. UzavĜení sešitu je poté zrušeno. Jak zabránit výskytu událostí V praxi se setkáte se situacemi, kdy máte pro urþitou událost napsán kód, ale tento kód mĤže být provádČn jen nČkdy. NapĜ. pĜi otevírání sešitu nebude z nČjakého dĤvodu žádoucí, aby byl proveden kód události Activate. PĜi každé další aktivaci daného sešitu však už kód proveden být musí. ObdobnČ se mĤžete potkat s pĜípady, kdy v kódu událostní procedury provedete nČco, þím danou událost znovu spustíte. ZmČníte-li napĜ. v události listu Change hodnotu buĖky v tomto listu (pĜíklad si ukážeme za chvíli), bude událost Change generována znovu. V takovém pĜípadČ ovšem hrozí nekoneþné zacyklení kódu. Existuje dvojí možné Ĝešení tČchto situací. Buć zachytávání událostí zcela vypnete, nebo použijete logickou promČnnou, která ukonþí provádČní kódu. První Ĝešení je „lepší“, protože událost vĤbec není vyvolána, ovšem jeho nevýhodou je fakt, že neprobČhnou vĤbec žádné události. Logická promČnná nic nepotlaþuje, jen bezprostĜednČ ukonþí provádČní kódu v událostní proceduĜe. Zachytávání událostí vypnete takto: Debug.Print "Název_Procedury"

Vlastnost EnableEvents má logické hodnoty True/False, za Ĝádkem, který by vyvolal nežádoucí generování událostí, zase zachytávání zapnete. Ukázka druhého pĜístupu pomocí logické promČnné potlaþí generování události Activate pĜi otevĜení sešitu:

UDÁLOSTI

V

EXCELU

Private Sub Workbook_Activate() If lPotlaceni Then ' pĜi otevĜení sešitu probČhne tato þást lPotlaceni = False Exit Sub Else ' zde bude kód, ' který bude proveden pĜi dalších aktivacích sešitu End If End Sub Private Sub Workbook_Open() lPotlaceni = True End Sub

1.2

Události sešitu

Tyto události probíhají v rámci konkrétního (jednoho) sešitu. Jejich událostní procedury jsou uloženy v modulu kódu pro objekt ThisWorkbook. Úplný seznam událostí je uveden dále, po nČm se zamČĜíme na nejdĤležitČjší události, které se v praxi používají. Jestliže potĜebujete zachytávat události pro libovolný otevĜený sešit, musíte používat události na úrovni celé aplikace. Tato problematika je popsána v tĜetí kapitole této knihy, seznam událostí objektu Application najdete v závČru této kapitoly. PĠehled událostí sešitu ҏ

Událost

Akce, která vyvolá událost

Activate

OtevĜení sešitu a pĜechod do libovolného okna sešitu z jiného sešitu. Událost neprobČhne pĜi vytváĜení nového okna sešitu ani pĜi pĜechodu mezi dvČma okny téhož (v tom pĜípadČ však probČhne událost WindowActivate).

AddinInstall

Sešit je instalován do prostĜedí Excelu jako doplnČk.

AddinUninstall

Odinstalování sešitu, který je nainstalován jako doplnČk.

AfterXmlExport

Uložení nebo export dat ze sešitu do datového souboru XML.

AfterXmlImport

Nové naþtení dat pomocí existujícího datového pĜipojení, nebo import dat XML do sešitu.

BeforeClose

Probíhá pĜed uzavĜením sešitu (= byl vydán pĜíkaz k uzavĜení sešitu).

UDÁLOSTI

V

EXCELU


' promČnnou deklarujeme v modulu ThisWorkbook - jinde ji nepotĜebujeme Private lPotlaceni As Boolean

19


-

20

Událost


BeforePrint

Probíhá pĜed vytištČním sešitu (nebo jeho þásti).

BeforeSave

Probíhá pĜed uložením sešitu.

BeforeXmlExport

ProbČhne pĜedtím, než Excel uloží nebo exportuje data ze sešitu do datového souboru XML.

BeforeXmlImport

ProbČhne pĜed novým naþtením dat pomocí existujícího datového pĜipojení, nebo po naimportování dat XML do sešitu.

Deactivate

PĜi deaktivaci sešitu (probíhá též pĜi uzavírání sešitu, ale až po události BeforeClose).

NewSheet

Vložení nového listu do sešitu. Odkaz na nový list je do události pĜedán jako parametr Sh.

Open

Otvírání sešitu. Po této události probíhá ještČ událost Activate.

PivotTableCloseConnection

ProbČhne poté, co kontingenþní tabulka uzavĜe pĜipojení ke zdroji dat.

PivotTableOpenConnection

ProbČhne poté, co kontingenþní tabulka otevĜe pĜipojení ke zdroji dat.

SheetActivate

Aktivace libovolného listu v sešitu.

SheetBeforeDoubleClick

PĜi poklepání na libovolném listu. Událost probíhá pĜed výchozí akcí pro poklepání.

SheetBeforeRightClick

Po klepnutí pravým tlaþítkem myši na libovolném listu. Událost probíhá pĜed výchozí akcí pro klepnutí pravým tlaþítkem myši.

SheetCalculate

PĜi pĜepoþítání obsahu libovolného listu.

SheetChange

Obsah libovolného sešitu je mČnČn uživatelem nebo aktualizací vnČjšího propojení.

SheetDeactivate

PĜi deaktivaci libovolného listu v sešitu.

SheetFollowHyperlink

PĜi klepnutí myší na libovolný hypertextový odkaz v libovolném listu.

SheetPivotTableUpdate

Po aktualizaci listu s kontingenþní tabulkou.

SheetSelectionChange

ZmČna výbČru na libovolném listu, s výjimkou listĤ typu graf.

UDÁLOSTI

V

EXCELU


Sync

Synchronizace lokální kopie pracovního listu, která je souþástí pracovního prostoru dokumentu, s kopií na serveru.

WindowActivate

Aktivace kteréhokoli okna sešitu.

WindowDeactivate

Deaktivace kteréhokoli okna sešitu.

WindowResize

ZmČna velikosti kteréhokoli okna sešitu.

ҏ

PĠíklady využití základních událostí sešitu Událost Open

Tato událost je využívána zĜejmČ nejþastČji. ZpĤsoby jejího využití jsou skuteþnČ bohaté: OtevĜení dalších potĜebných sešitĤ, pĜípadnČ doplĖkĤ. Nastavení vlastních panelĤ nástrojĤ þi nabídek, pĜípadnČ klávesových zkratek. PĜechod na urþitý list. Zobrazení úvodního formuláĜe, sloužícího jako uživatelské rozhraní. Automatické provedení urþitých výpoþtĤ. Zápis údajĤ do protokolu, ve kterém je sledována þinnost uživatele (ti zkušenČjší z vás samozĜejmČ vČdí, že k tČmto úþelĤm se dá využít i Deník v Outlooku nebo zvláštní aplikace …). Nastavení vlastnosti listu ScrollArea (ta není ukládána spolu se sešitem). ҏ

Budete-li pĜi otevírání sešitu držet stisknutou klávesu SHIFT, není událostní procedura Workbook_Open spuštČna (jde o jednu z možností, jak se bránit makrovirĤm). Ukážeme si jednoduchý kód, uložený v sešitu složeném z 13 listĤ (prvních 12 listĤ pro jednotlivé mČsíce a poslední list pro souhrnné údaje). PĜi otevírání sešitu je zkontrolováno datum a podle aktuálního mČsíce je aktivován pĜíslušný list. ' v deklaraþní þásti modulu ThisWorkbook pĜíkaz Option Base 1 ' zpĤsobí þíslování prvkĤ pole od jedniþky, nikoli od nuly Option Base 1 Private Sub Workbook_Open() Dim varMesice varMesice = Array("Leden", "Únor", "BĜezen", "Duben", _ "KvČten", "ýerven","ýervenec", "Srpen", "ZáĜí", "ěíjen", _ "Listopad", "Prosinec") Worksheets(varMesice(Month(Date))).Activate End Sub


Událost

Názvy listĤ jsou pomocí funkce Array uloženy do pole varMesice a metoda Activate pĜi otevĜení sešitu zobrazí požadovaný list.

UDÁLOSTI

V

EXCELU

21


Události Activate, Deactivate

Událost Activate je vyvolána pĜi aktivaci sešitu (jeho libovolného okna). Využití této události je opČt pestré: Úprava velikosti aktivovaného okna. Zobrazení þi skrytí nČkteré nabídky nebo panelu nástrojĤ, která/ý je platná/ý jen pro daný sešit. Aktualizace údajĤ ve stavovém Ĝádku. Ukázka kódu pro tuto událost obsahuje maximalizaci okna sešitu pĜi jeho aktivaci a zobrazení speciálního panelu nástrojĤ. Private Sub Workbook_Activate() ActiveWindow.WindowState = xlMaximized CommandBars("Statistika").Visible = True End Sub

Událost Deactivate probČhne pĜi pĜechodu do jiného sešitu, pĜi skrytí sešitu nebo pĜed jeho uzavĜením, tedy v pĜípadech, kdy sešit pĜestává být aktivní. Její využití je obdobné jako u události Activate (zobrazení þi skrytí nČkteré nabídky nebo panelu nástrojĤ). Private Sub Workbook_Deactivate() CommandBars("Statistika").Visible = False End Sub Událost BeforeSave

Tato událost probČhne tČsnČ pĜed skuteþným fyzickým uložením sešitu. Pomocí parametru SaveAsUI je možné v této události rozlišit, zda bude þi nebude zobrazeno dialogové okno Uložit jako (to se zobrazí v pĜípadech, kdy sešit ještČ nebyl uložen, byl otevĜen pouze pro þtení nebo když uživatel chce sešit uložit pod jiným názvem (pĜíkaz Soubor o Uložit jako). Parametr Cancel umožĖuje zrušit uložení souboru. ' kód, který se provede pĜed zobrazením dialogového okna Uložit jako Private Sub Workbook_BeforeSave _ ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then End If End Sub Událost BeforePrint

Je spuštČna pĜed vytištČním dokumentu nebo zobrazením tiskového náhledu (poté, co uživatel zadal pĜíslušný pĜíkaz). V této události se vyskytuje již probraný parametr Cancel, takže tisk sešitu nebo jeho þásti lze zrušit nastavením hodnoty tohoto parametru na True. Jednoduchá ukázka využití této procedury zobrazí okno hlášení, upozorĖující uživatele, aby se pĜesvČdþil, zda má založen správný typ papíru: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim odpoved As Integer odpoved = MsgBox("Tento sešit musí být tištČn na listy formátu A3." _ & " Máte v tiskárnČ tyto listy pĜipraveny?", vbYesNo) If odpoved = vbNo Then Cancel = True End Sub

UDÁLOSTI

V

EXCELU

Programování v Excelu 2000, 2002, 2003

Recommend Documents