Obsah Předmluva
13
Začínáme
15
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
15 15 17 18 19 19 19 20 20 21 21 22 24 27 28 31 32 33 34 38 40 43 46 49
Úvod Jmenné konvence pro názvy proměnných a objektů Deklarace proměnných Úplné odkazování Odkazování a objektové proměnné DoEvents Uživatelem definované funkce tabulek Vypnutí výpočtů Vypnutí aktualizace obrazovky Zabránění používání metody Select Procházení seznamů objektů Indexy barev Barvy RGB Získávání podílů jednotlivých složek barev z hodnoty RGB Dialog pro nastavení barev systému RGB Převod hodnoty RGB na hodnotu ColorIndex Převod hodnoty ColorIndex na hodnotu RGB Generování náhodných čísel Třídění metodou Quicksort Náhodné seřazení Popisky pro vlastní funkce Vkládání komentářů Komentáře jako místa pro zobrazení obrázků Získání systémových informací pomocí WMI
Obecné
53
25 26 27 28 29 30 31 32 33 34 35 36 37
53 55 56 58 60 62 64 64 65 66 70 71 72
K1692.indd 3
Vzorce s cyklickými odkazy Vzorce s cyklickými odkazy pomocí VBA Kumulování hodnot v buňce Které buňky jsou uzamčeny? Změna barvy pozadí uzamčených buněk Uzamknutí buněk se vzorci Automatický filtr při uzamčeném listu Vytvoření osnovy při uzamčeném listu Řazení dat při uzamčeném listu Zjištění možností ochrany listu Nastavení ochrany listu Označení a formátování odemčených buněk Formátování odemčených buněk (od Excelu 2002)
14.7.2009 16:14
4
38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
Obsah
Formátování odemčených buněk (všechny verze) Změna nastavení ochrany listu Oblasti s povolením úprav dle uživatelů Odstranění oblastí s povolením úprav dle uživatelů Odemknutí oblastí s povolením úprav dle uživatelů Uzamknutí oblastí s povolením úprav dle uživatelů Oblasti s povolením úprav dle uživatelů – změna hesla Oblasti s povolením úprav dle uživatelů – změna názvu Oblasti s povolením úprav dle uživatelů – změna oblasti Povolení úpravy vybraných oblastí Zakázání odstranění listů Zavření souboru s atributem pouze pro čtení bez zobrazení otázky o uložení změn Ignorování odstranění atributu pouze pro čtení Ochrana uživatelsky definovaných zobrazení Zjištění informací operačního systému Zjištění dvou uživatelských jmen Odstranění osobních údajů z vlastností souboru Odstranění dat a informací pomocí funkce Kontrola metadat
Datum a čas 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
K1692.indd 4
Nastavení formátu data a času Který den v týdnu jste se narodili? Zobrazení aktuálního data v systému Určení přestupného roku Uchovávání informací o přístupu k souboru Sledování stavu zásob Zjednodušené zadávání data Zadávání času pomocí automatických oprav Zadávání času bez znaku dvojtečky Nalezení aktuálního data Vyhledání libovolného data Zobrazení pracovní doby v dialogovém okně Zjištění celkového počtu odpracovaných hodin Určení kalendářního týdne podle normy DIN 1355 Zjištění počtu kalendářních týdnů Počítání data svátků pomocí vzorce Zobrazení vzorců v tabulce Funkce pro zobrazení vzorců Zjišťování svátků pomocí VBA Vytvoření kalendáře Stoletý kalendář a tabulka pro evidenci pracovní doby Výpočet mzdy Počítání se zápornými časovými údaji Práce se zápornými časovými údaji pomocí funkce Záporné časové údaje v kalendářním systému 1904
72 73 75 77 77 78 79 79 81 84 87 88 90 90 93 95 96 98
101 101 105 105 106 107 109 111 112 113 116 117 118 119 121 123 124 125 126 127 128 131 135 136 137 138
14.7.2009 16:14
Obsah
81 82 83 84 85 86 87 88 89 90 91 92 93 94
Zobrazení nastaveného kalendářního systému ve stavovém řádku Zadání záporných časových údajů Kopírování dat v kalendářním systému 1904 pomocí VBA Import dat z jiného kalendářního systému Funkce pro práci s daty v kalendářním systému 1904 Výpočet rozdílu dvou dat Výpočet rozdílu dvou dat pomocí funkce DateDiff ve VBA Procedura jazyka VBA nahrazující funkci DateDif dostupnou v listu sešitu Funkce umožňující spočítat věk Stopky v Excelu Excel dokáže čekat Zvukový signál Vydávání zvuků při výskytu dnešního data v buňce Zobrazení dialogového okna po omezenou dobu
Zpracování dat 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
Úvod Odstranění duplicitních záznamů v Excelu 2007 Odstranění duplicitních záznamů ve starších verzích Excelu Porovnávání listů (nalezené odlišnosti se umístí do zvláštního listu) Označení buněk se stejným obsahem Vypsání vzorců do listu sešitu Podmíněné formátování Automatický filtr Automatické filtrování podle data Filtrování podle barev Vytvoření kontingenční tabulky Formátování existující kontingenční tabulky Šablony pro formátování kontingenčních tabulek Připojení kontingenční tabulky k cizí databázi s ADO Zpracování cizích databází získaných přes připojení dat v kontingenčních tabulkách
Ovládací prvky 110 111 112 113 114 115 116 117 118 119 120 121
K1692.indd 5
Úvod Ovládací prvky panelu nástrojů Formuláře Vytvoření tlačítka z panelu nástrojů Formuláře Odstranění tlačítka vytvořeného pomocí panelu nástrojů FORMULÁŘE Přiřazení makra tlačítku z panelu nástrojů FORMULÁŘE Soubor nástrojů OVLÁDACÍ PRVKY Vytvoření ovládacích prvků Odstranění ovládacího prvku Odstranění všech prvků na listu Odstranění ovládacích prvků jednoho typu Nastavování ovládacích prvků jako aktivních Změna hodnoty zaškrtávacího políčka (prvku Checkbox)
5
140 142 142 144 145 147 148 151 154 155 158 158 159 159
161 161 161 162 164 167 170 175 189 194 196 199 205 207 209 212
217 217 218 220 221 221 222 226 227 228 228 228 229
14.7.2009 16:14
6
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
Obsah
Načtení hodnoty zaškrtávacího políčka (prvku CheckBox) Přenos pravdivostní hodnoty zaškrtávacího políčka Změna vlastností ovládacího prvku Manipulace s přepínacím tlačítkem Seskupování prvků Sledování hodnot přepínačů Nastavení původních hodnot u přepínačů a zaškrtávacích políček Výběr listů ze seznamu Vyplnění seznamu pohybem myší Zobrazení měsíců v prvku Seznam Změna položek seznamu při jejich výběru Vyplnění seznamu o více sloupcích Vyplnění pole se seznamem na základě volby uživatele Skrytí hesla pomocí hvězdiček Převod hodnot v textovém poli do jiných formátů Vložení zalomení řádku do textového pole (prvek TextBox) Změna měsíce pomocí číselníku (prvek SpinButton) Změna data pomocí posuvníku (prvek ScrollBar) Povolení ovládání prvků pomocí klávesnice Nastavení přístupu k listům sešitu pomocí ovládacích prvků
Panely 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164
K1692.indd 6
Úvod Získání seznamu integrovaných panelů nástrojů Získání kontextových menu Deaktivování kontextových menu Uvedení kontextových menu do původního stavu Získání seznamu nabídek a ovládacích prvků Přidání vlastních panelů nástrojů Odstranění vlastních panelů nástrojů Odstranění skupiny Vlastní panely nástrojů Tlačítka v integrovaných panelech nástrojů Vytvoření vlastních panelů nástrojů Odstranění vlastních panelů nástrojů Odstranění sekcí na záložce Doplňky Vyhledání souboru pro panely nástrojů Vytvoření kontextového menu s datem a časem Vytvoření nové nabídky se seznamem Vyvolání ovládacího prvku Kopírování ovládacích prvků Vytvoření panelů nástrojů známých z Excelu 2003 Použití kontextových menu jako panelů nástrojů Položky menu s hypertextovými odkazy Přidání tlačítka do menu Obarvení ouška záložky listu pomocí prvku pole se seznamem
231 231 232 233 234 236 238 240 241 242 244 245 247 249 251 252 253 255 257 259
267 267 268 270 271 275 275 277 280 281 282 283 286 286 287 289 291 296 297 300 301 305 308 309
14.7.2009 16:14
Obsah
165 166 167 168 169
Zobrazení a změna zamknutí buněk v kontextovém menu Nahrazení kontextového menu kaskádovým menu Které kontextové menu bylo vyvoláno? Vložení symbolů pomocí kontextového menu Současné zobrazení všech FaceID
Objekty 170 171 172 173 174 175 176 177 178 179 180
Úvod Převod oblasti na obrázek Export oblasti ve formátu obrázku Oblast jako pozadí ovládacího prvku Získání ikonek obsažených v souborech Mapa Přepočítávání souřadnic Pomocník MSAgent Animace Přehrávání zvukových souborů a souborů videa Získávání ikonek z prvků typu Control
Grafy 181 182 183 184 185 186 187 188 189 190 191 192 193
Úvod Zobrazení všech existujících typů grafů Nadpis grafu Legenda Formátování tabulky dat Datové řady Datové body Osy a čáry mřížky Značky Popisky dat Spojnice trendu Oblast grafu a zobrazovaná oblast Stěny, podstavy a zobrazení prostorového grafu
Události 194 195 196 197 198 199 200 201 202 203
K1692.indd 7
Obecné procedury Procedury zpracovávající události Události typu Workbook Zobrazení listu s aktuálním měsícem Ignorování událostí Vypnutí a zapnutí zpracovávání událostí Soubor Excelu nepoužitelný bez podpory maker Zobrazení souboru pouze s podporou maker Nové přečíslování oušek listů Pojmenování nového listu
7
315 318 321 322 326
327 327 327 329 333 337 343 350 354 359 362 363
369 369 376 385 389 392 395 399 402 413 415 418 425 430
437 437 439 440 443 445 447 448 456 457 460
14.7.2009 16:14
8
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232
Obsah
Odstranění grafu po jeho prohlédnutí Události pro list sešitu (Worksheet) Zakázání vyvolání kontextového menu buněk Na stopě proměnné Target Zakázání výběru oblasti Zaznamenávání pohybů myši Zvětšení oblasti označených buněk Změna ukazatele myši Zobrazení vzorců v komentářích Zobrazení kalendáře s aktuálním měsícem při změně listu Zámek bez zámku Podmíněné formátování s využitím událostí Podmíněné formátování s využitím událostí fungujících i u buněk se vzorci Spuštění makra prostřednictvím hypertextového odkazu Zjištění adresy hypertextového odkazu Automatické procedury Otevření souboru a spuštění procedury Auto_Open Uzavření souboru a spuštění procedury Auto_Close Pozdrav podle jednotlivých částí dne Nastavení lupy podle rozlišení monitoru Funkce Lupa ve spojení s Workbook_Open Načasování spuštění makra Trvalé zobrazení hodin ve stavovém řádku Nastavení zobrazení hodin ve stavovém řádku Hodiny v dialogovém okně Vytvoření klávesových zkratek Navigace pomocí nitkového kříže Vrácení akce provedené makrem Povolení příkazu Opakovat
UserForm 233 234 235 236 237 238 239 240 241 242 243 244 245 246
K1692.indd 8
Úvod Zobrazení uživatelského formuláře Události týkající se uživatelských formulářů Příkazové tlačítko (CommandButton) Popisek (Label) Textové pole (TextBox) Skupinový rámeček (Frame) Prvek Seznam (ListBox) s jedním sloupcem Prvek Seznam (ListBox) s více sloupci Prvek Pole se seznamem (ComboBox) Prvek Přepínač (OptionButton) Zaškrtávací políčko (CheckBox) Prvek RefEdit (RefEdit) Prvek Záložka (TabStrip)
464 466 467 468 470 470 471 472 473 474 476 478 481 483 485 485 486 487 488 489 490 491 493 495 496 496 500 503 504
507 507 511 512 514 517 518 520 522 525 532 539 542 546 550
14.7.2009 16:14
Obsah
247 248 249 250 251 252 253
Prvek Vícenásobná stránka (Multipage) Prvek Obrázek (Image) Prvek Posuvník (Scrollbar) Prvek Číselník (SpinButton) Prvek Přepínací tlačítko (ToggleButton) Prvek Kalendář (Calendar) Přidání funkcí pro minimalizaci, maximalizaci a přesunutí oken do uživatelských formulářů 254 Dynamické vytváření ovládacích prvků
Internet a e-mail 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270
Úvod Dialog pro odesílání e-mailů Odeslání listů a oblastí Odesílání e-mailu s přílohou Vnitřní hypertextové odkazy Hypertextový odkaz na soubor Hypertextový odkaz na internetovou stránku Hypertextový odkaz na e-mailovou adresu Seznam všech hypertextových odkazů Tabulky XML 2003 Vytvoření souboru XML pomocí standardních součástí Windows Vytvoření souboru XML pomocí DOM Integrace internetového prohlížeče Vytvoření webového dotazu (Query) Webový dotaz pomocí internetového prohlížeče Inteligentní značky (Smart Tags)
Externí aplikace 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286
K1692.indd 9
Úvod Spuštění Průzkumníka se zobrazením vámi zadané složky Příkazy VBA pro práci se systémem souborů Vytvoření složky Vytvoření složky pomocí API Vytvoření souboru Otevírání souborů Odstraňování souborů Odstranění složky Kopírování či přesouvání souboru nebo složky Seznamy souborů Objekt FileSearch OLE FileSystemObject Vytvoření seznamu souborů pomocí FSO Seznam diskových jednotek
9
552 555 557 560 563 565 567 569
573 573 573 575 580 586 589 592 594 596 597 599 603 606 608 613 619
631 631 631 632 633 634 635 636 637 639 641 643 648 648 650 652 656
14.7.2009 16:14
10
287 288 289 290 291 292 293 294 295 296 297 298
Obsah
Načtení časových údajů týkajících se souborů Změna časových údajů u souborů GetAttr/SetAttr Outlook Načtení všech dostupných složek v Outlooku Načtení názvů vlastností položek Outlooku Výpis všech vlastností všech prvků složky Databázový dotaz Query Načtení databází ve formátu ACCDB pomocí ADO Vytvoření databáze MDB Vytváření souborů CSV CSV pomocí ADO
658 659 664 665 667 668 670 672 676 678 681 683
Na přiloženém CD naleznete Programování v prostředí VBE 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327
K1692.indd 10
Vývojové prostředí editoru jazyka Visual Basic Načtení odkazů Aktivování odkazů Deaktivování odkazů Načtení vlastností projektu Načtení a přejmenování názvu projektu Je zobrazení projektu jazyka Visual Basic zamčeno? Odstranění uzamčení projektu jazyka Visual Basic Zobrazení a skrytí vývojového prostředí Výpis seznamu oken s moduly Zobrazit okno modulu Odstranění textu v okně Immediate Načtení všech projektů Načtení modulů a procedur aktivního projektu VBA Načtení kódu procedury Načtení názvu a kódového názvu listu Změna kódového názvu listu Nastavení stejného kódového názvu, jako je název listu v Excelu Vytvoření modulu Vytvoření modulu se jménem zadaným uživatelem Odstranění modulu Kontrola existence modulů Kontrola existence procedur Zapsání procedury do modulu a její spuštění Zápis a spuštění procedury ve vlastním modulu Vytvoření procedury reagující na událost Odstranění procedury Odstranění kódu z modulu Vytvoření menu v panelu nabídek editoru jazyka Visual Basic
687 687 688 690 691 691 693 694 694 696 697 698 699 700 701 703 704 706 709 710 711 711 712 713 713 714 715 717 718 718
14.7.2009 16:14
Obsah
328 329 330 331 332 333 334 335 336 337 338 339 340
Vytvoření procedur reagujících na integrované události Hlášení o všech změnách pozic buňky Doplňky (Add-Ins) Přidání a instalace doplňků Kopírování doplňku z paměťového média na pevný disk Výpis doplňků Instalace doplňků pomocí programu Průzkumník Odinstalování doplňku a jeho odstranění ze seznamu doplňků Přidání názvu a popisu k doplňku Uložení změn ve zdrojovém kódu doplňku Integrace funkcí do doplňku Volání maker obsažených v doplňku Spuštění maker v doplňku pomocí tlačítek
Speciality 341 342 343 344 345 346 347 348 349 350 351 352 353
Úvod Skládačka (puzzle) Vytvoření sudoku Vyřešení sudoku Vyplnění sázenky do loterie (Array) Plánování tras s programem Virtual Earth Převod čísel na slova Výpis všech druhů písem Výuka slovíček Přehled listů v panelu nabídek Prezentace v Excelu Panel s nabídkami pro nastavení vlastností okna Rozdělení součtu do několika částí
RibbonX 354 355 356 357 358 359 360 361 362 363 364 365 366 367
K1692.indd 11
Úvod Zobrazení a skrytí pásu karet (VBA) Volání příkazu z pásu karet Skrytí uživatelského rozhraní Excelu Skrytí záložky Skrytí sekce Skrytí záložky objevující se při použití určitého prvku (kontextově orientované záložky) Skrytí sekce u kontextově orientované záložky Zakázání příkazu integrovaného v pásu karet Přiřazení příkazu tlačítku integrovanému v pásu karet Vytvoření nové záložky Vytvoření integrované sekce Upravení integrované sekce Úprava nabídky Office
11
722 728 730 732 734 734 735 737 739 740 741 742 743
749 749 749 755 764 771 776 782 784 786 790 795 797 802
809 809 813 814 816 817 818 819 820 821 822 824 826 827 828
14.7.2009 16:14
12
368 369 370 371 372 373 374 375
Obsah
Použití integrovaných a vlastních ikonek Spouštění maker pomocí Callback Handler Průběžná změna ikonek Vložení tlačítka do panelu nástrojů Rychlý přístup Průběžné skrývání a zobrazování záložek Vytvoření statického prvku Rozevírací seznam Vytvoření dynamicky vyplňovaného prvku typu Rozevírací seznam Přepínání mezi listy pomocí prvku Seznam
Dodatek 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397
K1692.indd 12
Přehled anglických ekvivalentů českých názvů funkcí Konstanty funkce MsgBox Metoda Application.InputBox Operátory porovnávání Operátory pro práci s numerickými hodnotami Spojovací operátory Metoda SpecialCells Funkce RGB Vlastnost ColorIndex Objekt ColorFormat (barevné efekty) Objekt FormatCondition (podmíněné formátování) Formátovací kódy pro záhlaví a zápatí Datové typy Funkce pro konverzi datových typů Uživatelsky definované formátování data a času (funkce Format) Funkce DateDiff Metoda OnKey Vlastnost Attributes Různé konstanty Vlastnost ClipboardFormats Specifikace a omezení Excelu Přehled prvků XML a atributů pro pás karet
830 833 837 843 846 847 848 850
855 855 862 863 864 865 866 867 868 869 869 870 872 873 874 875 876 878 879 880 880 882 884
14.7.2009 16:14
Předmluva Uvedením nové verze kancelářského balíku Office 2007 došlo u Excelu mimo jiné i k zásadním změnám ve vzhledu či ovládání. Tak například najednou zmizel panel nabídek (menu), na který byli uživatelé po léta zvyklí, a namísto něj se objevil pás karet. Ovšem i jinak se Excel zdá být jakýsi intuitivnější, bohatší a uživatelsky přívětivější. Microsoft v nové verzi Excelu také vyslyšel přání uživatelů a například výrazně vylepšil funkci Podmíněného formátování. Zdokonalení se dočkala i funkce automatického filtrování, takže nyní může uživatel filtrovat i podle formátování, a to dokonce i podle takového formátování, které se nastaví přes funkci podmíněného formátování. V nové verzi již ani neplatí dosud používaná struktura souborů s názvem BIFF (Binary která představovala omezení velikosti listu na 256 sloupců a 65 536 řádků nebo umožňovala použití pouze omezeného počtu barev. Nyní tak můžete vedle sebe bez obav umístit na jeden list například všechny dny v roce a pracovat v něm s více než 65 536 datovými položkami v listu. V buňkách pak je možné použít a poté natrvalo ukládat libovolné barvy barevného formátu RGB.
Interchange File Format),
Soubory, které vytvoříte v Excelu 2007, jsou ve skutečnosti archivy ve formátu ZIP. Jedná se víceméně o komprimovanou složku obsahující řadu souborů ve formátu XML. Tyto soubory se pak používají pro ukládání dat a v případě potřeby se dají zobrazit v libovolném textovém editoru. Výjimku představují pouze některá data, například zdrojový kód skriptů napsaných v jazyku VBA. Ten můžete využít například k vytvoření vlastních skupin symbolů a ovládacích prvků. Úpravou souborů XML nyní také můžete symbolům odebrat jejich původní funkci a přiřadit jim vámi vytvořenou proceduru. I když se neustále objevují poplašné zprávy a fámy, že je jazyk VBA uvedením Office 2007 v podstatě mrtvý a na jeho místo se vší slávou nastupuje platforma Dotnet, pro oblast programování to tak doslova určitě neplatí. Jazyk VBA stále existuje, a dokonce i jeho syntaxe zůstala v podstatě stejná jako předtím. Pro uživatele přecházející ze starších verzí to svým způsobem určitě je výhoda, která se nedá opominout. Pro profesionály s ambicemi na platformu Dotnet tu ostatně stále ještě je Visual Studio for Office (VSTO). Navíc každý, kdo programoval v jazyku VBA v dřívějších verzích Excelu, najde i v Excelu 2007 vývojové prostředí naprosto stejné, protože Microsoft jej v podstatě ponechal tak, jak bylo. Některé objekty, jako je například objekt FileSearch, však již nenajdete, protože namísto nich se objevily objekty nové. Nové třídy objektů se objevily zejména v oblasti formátování – typickým příkladem je třída ChartFormat, která umožňuje provádět taková formátování, jež v minulých verzích vůbec nebyla myslitelná, ovšem na druhou stranu stále můžete používat všechny vlastnosti, na něž jste byli zvyklí z minulých verzí. Tímto novým vydáním knihy jsme provedli velmi důkladné „zúčtování“ a celou knihu v podstatě od základů přepracovali. Všechny návody z velmi úspěšného posledního vydání jsme podrobili důkladnému testování, upravili jsme je podle nových podmínek, zastaralá řešení jsme doslova hodili přes palubu a přidali nová. Zřejmě jste si všimli, že s novým vydáním opět došlo i ke změně autorů. Hlavní příčinou bylo poněkud jiné stanovení priorit této knihy. Jako nový autor by člověk samozřejmě rád převzal všechna osvědčená řešení, ovšem vzhledem k novým možnostem Excelu 2007 jsme museli přistoupit k důkladnému přepracování knihy.
K1692.indd 13
14.7.2009 16:14
14
Předmluva
Cílovou skupinou, pro niž je tato kniha určena, jsou především pokročilí uživatelé až profesionálové pracující v programu Excel 2007. Přitom kniha není pouhopouhou učebnicí, ale spíše určitým druhem lexikonu, který vám má posloužit i jako příručka. Najdete zde velmi rozsáhlý soubor zdrojových kódů, které můžete používat a upravovat dle svých potřeb. Samozřejmě jsme se snažili kromě rozsáhlejších zdrojových kódů představit i kratší řešení, která umožní blíže se seznámit s jazykem VBA i naprostému začátečníkovi. Pro pokročilé uživatele pak zde máme řadu poměrně složitých řešení.
K1692.indd 14
14.7.2009 16:14
