Excel rozumí, přepínám...

Tento článek lehce předbíhá dobu a bude pro mnohé z vás prvním seznámením se s makry a programováním. Proto je řada příkladů spíše lehčího ražení a má za úkol "navnadit". Jednoduchý způsob vytváření maker najdete na úplném konci dnešní části. Přiložený sešit pak obsahuje širokou škálu příkladů.

Cílem následujících odstavců je nastínit možnosti v případě opakujících se operací v Excelu a rozšířit obzory v oblasti řízení. Využijeme přitom interaktivních schopností určitých objektů a spojení takových objektů s automatickou činností (od jednoduchého vkládání hodnoty do propojené buňky až po složitější makra). Jinými slovy, půjde nám o takové prvky, které jsou schopny vazby akce-reakce v hlubším významu. Impulsy pro nastartování přitom nejčastěji dávají vstupní zařízení jako je klávesnice a myš. Na výběr máme tyto prvky:

• Formulářové prvky (Zobrazit / Panely nástrojů / Formuláře)
• Ovládací prvky ActiveX (Zobrazit / Panely nástrojů / Ovládací prvky)
• Vkládané objekty (Vložit / Objekt)
• Kreslené objekty (Zobrazit / Panely nástrojů / Kreslení)
• Prvky panelů nástrojů (viz jiný článek)

Programátoři namítnou, že do reakceschopných prvků objektového modelu Excelu patří i okna, sešity, listy, grafy atd. A mají pravdu. Popud k reakci nemusí nutně vzejít z klávesnice nebo myši. Reakce mohou být spojeny například s otevíráním sešitu, změnou buňky, aktualizací grafu, nebo třeba s dosažením časového limitu. Ačkoliv jsou v příloze ukázány i tyto případy, není cílem tohoto článku je dopodrobna probírat, neboť u nich převažuje "programová stránka" věci. Předmětem zájmu budou dále především prvky Formuláře a Ovládací prvky.

Poznámka: Obecně lze za reakceschopné objekty listu považovat ty, které v příručním menu (klepnutí pravým tlačítkem myši) obsahují volbu Přiřadit makro. Zvláštní pozici pak zaujímají hypertextové odkazy (menu Vložit / Hypertextový odkaz)

Zdánlivě je většina prvků ActiveX totožná s těmi z panelu Formuláře. Na obou panelech nástrojů najdeme zaškrtávací políčko, tlačítko, přepínač, popisek atd. Proč tedy dvojí vydání? Formulářové prvky pocházejí z doby, kdy ještě technologie ActiveX byla v plenkách a zůstávají v Excelu z důvodu zpětné kompatibility.

Poznámka
Panely jsou znázorňovány v podobě, jak je běžně Excel nabízí. Znervózňuje-li vás nečitelnost popisků panelu, čapněte panely za svislou hranu a roztáhněte je.

Názvy prvků

Pro dobrou orientaci je třeba znát anglické výrazy používané u prvků typu ActiveX:

• Tlačítku odpovídá název CommandButton.
• (Zaškrtávací) políčko je pojmenováno jako CheckBox.
• Přepínači se říká OptionButton.
• Seznam pochází z anglického ListBox.
• Poli se seznamem odpovídá ComboBox.
• Posuvník nese název ScrollBar.
• Číselník je v angličtině pojmenován jako SpinButton.
• Popisek je totéž co Label.

Panel Ovládací prvky obsahuje kromě jiného i další standardní prvky.

• Přepínací tlačítko alias ToggleButton. (Klasické tlačítko lze přirovnat k domovnímu zvonku, zatímco přepínací tlačítko se chová jako dvoupolohový vypínač světla.)
• Prvek Image nebo-li prvek obrazového typu.

Další prvky ActiveX najdeme pod ikonkou s nářadím.

Tip
Jako prvky vhodné pro zkoumání doporučuji Microsoft Data and Time Picker, Microsoft ProgressBar nebo třeba prvek Calendar (ten je součástí MS Access a musí se navolit při instalaci MS Office - viz obrázek). Názvy se mohou malinko lišit podle verzí Excelu.

Nelze jednoduše říci, že novější ActiveX jsou lepší. Porovnání a možnosti uvádí následující tabulka.

Práce s prvky Formuláře se odehrává v českém prostředí a neměli byste narazit problém, u ActiveX osvětlím několik běžných vlastností, na které narazíte v okně Properties. Příklad se týká prvku TextBox.

Vlastnosti obsahující slovíčka Color, Font, Style, Shadow nebo třeba Effect asi nemusím rozebírat. Vlastnost Enabled povolí nebo naopak zmrazí ovládací prvek (hodnota True, resp. False). Vlastnosti Height a Width určují výšku a šířku prvku. Vlastnosti Top a Left učují polohu prvku vzhledem k levému hornímu rohu okna (odshora, zleva). Vlastnost LinkedCell obsahuje případný odkaz na buňku listu, ve kterém se prvek nachází (zápis je třeba provést ručně ve známém tvaru, např. C5). Stejně jako u prvku Formuláře je i zde komunikace obousměrná. Maxlength značí maximální povolený počet vkládaných znaků a PasswordChar obsahuje znak, který se zobrazí, chceme-li skrýt skutečné znaky (např. hvězdička). MultiLine a WordWrap umožní zalamování řádku v textovém poli, ScrollBars skrývá či zobrazuje posuvníky a Value (jakožto zde i Text) přednastavuje hodnotu prvku. Funkce vlastnosti PrintObject je zřejmá.

Ne všechny vlastnosti se zobrazují u všech prvků ActiveX. Některé jsou navíc, některé chybí. Důležitá je vlastnost Caption, která by měla obsahovat "lidský název" prvku (vlastnost Name obsahuje interní název používáný v kódu maker).

Poznámka 1
Hodnoty vlastností zpravidla vybíráme z rozbalovacího menu. Přesto se někde menu neobjevuje. Pak postupujeme intuitivně. Například u vlastnosti Value uvážíme, zda-li Excel očekává číslo, text nebo pravdivostní hodnotu (dvoustavový prvek; True, False).

Příklad 1. Test znalostí (bez maker)

Obrázek výše ukazuje, jak lze využít přepínačů (v daném případě z panelu Formuláře) k vytvoření testu znalostí. Každá trojice přepínačů je obalena prvkem Skupinový rámeček (popisek rámečku využit k zadání otázky). Pokud bychom totiž rozmístili všechny přepínače v listu volně, mohl by být vybrán jen jeden z nich! Rámeček jakoby ruší "radiové vysílání" prvků uvnitř a prvky "za zdí" o jejich partyzánské činnosti neví.

Další obrázek (dialog Formát / Ovládací prvek) znázorňuje provázání ovládacího prvku panelu Formuláře s buňkou listu a volbu stavu přepínače. Provázaná buňka přitom nejeví žádné známky vazby (neobjevuje se vzorec nebo něco podobného). Komunikace prvek-buňka je oboustranná, tzn. že změna v jednom elementu se projeví ve druhém. Z toho vyplývá, že změna stavu prvku vyvolá změnu v buňce (zobrazí se pořadová hodnota aktivního prvku), ale i to, že ručně zadaná hodnota v buňce změní stav prvku (skupiny prvků). Jak asi tušíte, manuální změna hodnoty na 1, 2 nebo 3 v buňce N10 aktivuje jeden ze tří prvků první skupiny přepínačů. Nula deaktivuje všechny přepínače skupiny, ostatní hodnoty jsou ignorovány. Tím, že prvky patří do téže skupiny, nastavení vazby na buňku u jednoho přepínače se promítne i do ostatních prvků skupiny a není tedy nutné připojovat každý prvek skupiny zvlášť.

Mechanismus sčítání je postaven na funkci STEJNÉ, jež vrací PRAVDA (hodnota 1) nebo NEPRAVDA (hodnota 0). Příklad je možné rozšířit. Klíč samozřejmě nemusí být obsažen v testu nebo může být vhodně skryt. Lze realizovat i myšlenku, kdy po uplynutí časového limitu se soubor zahesluje a uzavře (nebo třeba odešle e-mailem).

To, co bylo řečeno o rámečku okolo přepínačů panelu Formuláře, by mělo platit i u přepínačů z panelu Ovládací prvky (zde nazývaných OptionButtons). Ale ouha, už na první pohled je zřetelné, že prvek rámeček (Frame) na panelu Ovládací prvky chybí. Zkusme tedy pátrat v archivu dalších ovládacích prvků. Nalezneme prvek Microsoft Forms 2.0 Frame

Radost je předčasná, vložený element Frame odmítá s prvky přepínač (OptionButton) spolupracovat. Máte-li chuť pátrat dále, vyzkoušejte z příručního menu pro tento element volbu Objekt Frame / Edit. Zobrazí se panel nástrojů běžný při vytváření tzv. UserForms v editoru VBA. Z tohoto panelu lze sice vkládat také prvky OptionButton (zřetelně se liší od těch z panelu Ovládací prvky průhledností), ale nastavit vlastnosti lze opět pouze z příručního menu volbou Properties. Zde už nastupuje "tvrdá" angličtina a evidentně nikdo nepočítal, že se sem uživatel dostane (okno Properties v této podobě jsem sám viděl poprvé v době psaní tohoto článku a vypadá, jako by pocházelo z doby "raz dva"). Každopádně je pro náš účel nepoužitelné. Tohle klubko prostě nerozmotáme a asi jediným doporučením je: Používejte přepínače z panelu Formuláře.

Podotýkám, že kombinovat prvek rámeček z panelu Formuláře a přepínače ActiveX nejde.

Poznámka
Je dobré mít při vytváření kolekcí prvků zobrazen panel nástrojů Kreslení. Využijete především nástroje výběru a zarovnání. Velmi užitečný je i přepínač zobrazení mřížky na panelu Formuláře.

Příklad 2. Výuka funkcí (bez maker)

Ukázka na první pohled jednoduchého využití číselníku asi nenechá na pochybách nikoho z vás. Číselník propojený na buňku mění její hodnotu. Ta představuje první až pátou mocninu funkce. Změna se bezprostředně promítne v datech stejně jako v grafu.

Příklad 3. Dynamické vyhledávání (makra)

V tomto příkladu bylo využito textového pole panelu Ovládací prvky při vyhledávání v záznamech, a to v reálném čase. Volně dostupné makro umožňuje nalistovat záznam s nalezenou skupinou doposud zadaných textových znaků. První řádek s ovládacím prvkem je ukotven s pomocí menu Okno / Ukotvit příčky a řádky se záznamy jsou podsouvány pod něj.

Chování je naznačeno níže. Zatímco skupina znaků "sk" je nalezena prvně v položce "Bradský", slabika "sko" už předloží "Skokan". Odstranění písmene "o" slabiky "sko" navrátí výběr opět na položku "Bradský". Úplné vymazání pak zobrazí kompletní seznam v nesrolované podobě. Výhoda tohoto postupu je v tom, že je po ruce a jasně definuje oblast hledání (sloupec). Často je totiž k zlosti přednastavená volba vyhledávání "po řádcích" standardního nástroje Excelu (menu Úpravy / Najít, Ctrl+F).

Příklad 4. Kolo štěstí (makra)

Další příklad je ukázkou hravosti, která snad není až tak účelná, jako spíše povzbuzující a dokazující, že "to jde". Použito bylo koláčového grafu (zdrojová data jsou schována pod samotným grafem). Přidružené makro pak ve smyčce ukončené náhodným číslem mění barvu výsečí v časovém sledu daném metodou Application.OnTime. Výsledné chování v čase je ovlivněno nejen touto metodou, ale i rychlostí zpracování vlastního kódu a závisí na výkonu počítače. (Zkusit můžete pozměnit v kódu zlomkovou část následující za funkcí Now řádku Application.OnTime.)

Poznámka
Je třeba si uvědomit, že Excel není určen pro parádní animační efekty a chybí mu nejen kvalitní vykreslování (viz například průhlednost Excelu 2000), ale i solidní "časovač" (dá se naprogramovat coby externí knihovna). Je možné kupříkladu vytvořit hodiny (i analogové), bohužel ale samotné překreslování objektů spojené s "cukaturou" po uplynutí jedné sekundy bude kazit dojem.

Převážná většina ovládacích prvků má smysl ve spojení s makry, tedy jakousi posloupností příkazů, kterou můžeme jedním mávnutím proutku opakovaně aplikovat. Makra lze vytvářet i bez znalosti VBA. Jdeme na to.

Veškerá naše práce se bude odkazovat na menu Nástroje / Makro.

Jako první nastavíme úroveň zabezpečení maker na přijatelnou střední úroveň (Nástroje / Makro / Zabezpečení / karta Úroveň zabezpečení, volba Střední). Následně už přejdeme k samotnému nahrání makra. Zvolíme Nástroje / Makro / Záznam nového makra. Makru dáme vhodný název a další volby ponechámy v tuto chvíli beze změn. Dialog potvrdíme.

Objeví se panel nástrojů Zastavit záznam a od této chvíle je převážná část naší činnosti, kterou budeme v Excelu provádět, nahrávána. Pro zastavení záznamu slouží ikona plného čtverečku na zmíněném panelu nástrojů.

Námi nahrané makro najdeme v seznamu pod menu Nástroje / Makro / Makra. Otevřený dialog Makro je tentýž, který se zobrazuje, když chceme ovládacímu prvku makro přiřadit.

Nechtěné dílko vymaže tlačítko Odstranit. Je-li naopak naším přáním prohlédnout si kód makra, klepneme na tlačítko Upravit. Otevře se okno editoru VBA, které zobrazí programové řádky našeho výtvoru. A možná si to neuvědomujete, ale právě tento okamžik je momentem prvního krůčku na cestě za titulem "programátor Visual Basicu for Application". Gratuluji!