Nakoľko je dosť požiadaviek na tému „programovanie v Exceli”, otváram nový kurz pre základy programovania.
Úvod
Je veľa dôvodov, prečo začať programovať. Napr.:
- Sme takí používatelia programu Excel, ktorí už potrebujú dorobiť nové nástroje alebo funkcie do vzorcov.
- Potrebujeme si zautomatizovať niektoré rutinné práce. Ak často opakujeme viac úkonov rovnako za sebou, môžeme na to použiť napr. makro.
- Potrebujeme viac porozumieť makrám, ktoré už používame – teda ich skôr potrebujeme upravovať.
- Ak nám záznam makra nevie poskytnúť to, čo potrebujeme.
Naopak, častý omyl býva potreba nástroja, ktorý sa už v Exceli nachádza. Taký samozrejme nemá význam znovu programovať. Preto odporúčam, pred programovaním nových nástrojov, ovládnuť Excel na vyššej úrovni. Pomôcť môže prejsť si kurz efektívnej práce v Exceli [Otvoriť].
Čo je makro?
Makro je zoznam príkazov, ktoré za nás vykonávajú úlohy, ktoré potrebujeme na automatizované spracovanie cieľa. V Exceli môžeme ľahko nahradiť (uľahčiť si) našu prácu makrom, ak sa jedná o jednoduché opakované príkazy. Makro môžeme vytvoriť:
- Záznamom makra – To si môžete predstaviť, ako keby si Excel vybral kameru a nahrával všetko čo mu ukážeme. Ukazovanie je samozrejme obmedzené na klávesnicu a myš.
- Programovaním – Ide o písanie konkrétnych príkazov, ktoré má Excel vykonávať. Záznam je obmedzený spôsobom ukazovania (myšou a klávesnicou), kde samozrejme nevieme všetko ukázať, napr. nájdenie začiatku tabuľky (ak sa nezačína v bunke A1).
Záznam makra
Príkaz, ktorým spustíme záznam makra nájdeme:
Zobrazí sa nám dialóg so vstupnými nastaveniami:
[Názov makra] – Sem uvedieme jednoduchý názov, vystihujúci jeho účel. Má stanovené pravidlá z programovacieho jazyka „Visual Basic”.
Nakoľko sa tým budeme podrobne zaoberať neskôr, uvediem len stručne: Alfanumerické znaky (abecedné znaky, podľa potreby s číslami) bez špeciálnych znakov a medzery. Neodporúčam diakritiku.
[Klávesová skratka] – Jedna z možností, ako spustiť makro. Nie je povinná. Excel umožňuje akúkoľvek kombináciu [Ctrl] + [niečo] alebo ak napíšeme veľké písmeno bude potrebná kombinácia [Ctrl] + [Shift] + [niečo].
Neodporúčam napr. [Ctrl] + [c]. Program to dovolí preto, lebo už sú použité takmer všetky kombinácie a necháva nám na výber, ktorú chceme dočasne zameniť.
[Makro uložiť v] – Mohli by sme aj povedať „Makro použiteľné v”, teda pre aký zošiť potrebujeme makro používať:
- Tento zošit – Makro je použiteľné iba v aktuálnom súbore. A vo väčšine prípadov, len ak je súbor otvorený.
- Nový zošit – Vytvorí nový súbor, do ktorého zaznamená makro. Teda bude použiteľné opäť pre jeden (nový) súbor. Neplatí to pre všetky nové súbory.
- Osobný zošit makier – Je to vlastne šablóna Excelu, na základe ktorej je štandardne vytvorený každý nový súbor. Je teda otvorený (na pozadí) už pri otvorení Excelu. A tak je k dispozícii stále. Samozrejme je dôležité, aké vlastne makro je zaznamenané. Ak budeme zaznamenávať príkazy, ktoré jasne poukazujú na konkrétny zošiť / hárok, nebude skutočne použiteľné pre iné zošity / hárky. Ale to si ešte podrobnejšie preberieme.
[Popis] – Nakoľko je názov makra dosť obmedzený, v popise môžeme spresniť účel makra. Má to väčší význam, ak budeme používať viac makier. V zozname makier si budeme vyberať podľa názvu a popisu.
Akonáhle dialóg potvrdíme, záznam je spustený. V tom prípade neodporúčam vykonávať príkazy, ktoré nechceme vykonávať makrom. Najčastejšie sú to:
- Posúvanie pohľadu kolečkom myši.
- Klikanie na bunky, ktoré nepotrebujeme v makre vyberať.
- Nepreklikávame zbytočne medzi hárkami, hlavne ak potrebujeme urobiť makro, ktoré má pracovať s akoukoľvek tabuľkou, nie len s tou, na ktorej makro zaznamenávame. V tom prípade si ešte pred spustením makra zobrazíme hárok, na ktorom budeme makro „ukazovať”.
Môžete si vyskúšať porobiť nejaké zmeny v hárku, aby sme videli, ako makro približne funguje. Zvolte si pri zázname napr. klávesovú skratku [Ctrl] + [d], nakoľko sme si ešte nevysvetlili iné možnosti spustenia.
Nezabudnite záznam makra zastaviť. Microsoft to nemá dostatočne ošetrené – v princípe môžeme spustiť makro, ktoré sa ešte zaznamenáva. To v kritických prípadoch môže viesť až k reštartu počítača.
Údržba makier
Na karte [Zobraziť] v kategórii [Makrá] klikneme na ikonu [
Makrá].
alebo
Kombináciou [Alt] + [F8].
V ponuke [Nástroje], ďalej [Makro] a potom voľba [Makrá…].
alebo
Kombináciou [Alt] + [F8].
Zobrazí sa nám dialóg, kde máme rôzne možnosti:
[Názov makra] – Zoznam makier, ktoré máme momentálne k dispozícii. V časti [Makrá v] si môžeme zvoliť filter, ktoré makrá chceme v zozname vidieť.
[Popis] – Podrobnejšie popísaná úloha makra. Popis je voliteľný.
[Spustiť] – Je to jedna z možností, ako spustiť zvolené makro. Je to asi najzložitejšia, resp. najmenej používaná možnosť.
[Po krokoch] – Možnosť spustenia makra po krokoch. Dôležité pre odsledovanie a ladenie makra. Téma na neskôr.
[Úpravy] – Otvorenie makra v editore Visual Basic s možnosťou zmeny. Téma celého seriálu ďalej.
[Odstrániť] – Vymazanie makra. Definitívne.
[Možnosti…] – Dodatočná definícia / zmena klávesovej skratky a popisu makra.
Makro, ktoré sme si skúšali, môžeme odstrániť. Takisto, ak sa pri zázname pomýlime, je potrebné záznam zastaviť, makro odstrániť a zaznamenať nové makro.
Praktická ukážka záznamu makra
Ako ukážku záznamu a použitia jednoduchého makra môžeme použiť vytvorenie medzisúčtu aj so zafarbením. Vezmeme si príklad z kurzu [Otvoriť]. Tu to popíšem len skrátene:
- Ak nie je aktuálny hárok, v ktorom je tabuľka (, kde potrebujeme nahrať makro), prejdeme naňho ešte pred záznamom.
- Spustíme záznam makra.
- Názov [Medzisúčty].
- Klávesová skratka [Ctrl] + [m].
- „Ukážeme” makru, čo má robiť.
- Klikneme do bunky [A1]. Predpokladáme, že tabuľka sa začína na začiatku hárka.
- Tabuľku zoradíme
- podľa [Mesiac],
- potom podľa [Mesto]
- a podľa [NazovFirmy].
- Vytvoríme [Medzisúčty]
- v stĺpci [Mesiac] počet faktúr [FakSuma],
- ďalej v stĺpci [Mesto] maximálna suma [FakSuma]
- a v stĺpci [NazovFirmy] súčet [FakSuma].
- Zafarbíme jednotlivé úrovne. Vyberieme tabuľku kombináciou [Ctrl] + [*].
- Zvolíme napr. šedú farbu písma.
- Prejdeme na predošlú úroveň (4.)
- Nástrojom [Prejsť na…] (napr. klávesou [F5]) vyberieme [Iba viditeľné] bunky.
- Zvolíme inú farbu písma, napr. zelenú
- Opakujeme kroky 2. – 4. až po prvú úroveň. Samozrejme vždy volíme ešte nepoužitú farbu, napr. modrú, červenú a tmavšiu žltú.
- Na záver rozbalíme 5. úroveň medzisúčtov (aby sme videli celú tabuľku). Potom vyberieme bunku [A1] (, aby sme zrušili výber 1. úrovne).
- Zastavíme záznam makra.
Na vyskúšanie funkčnosti nahratého makra je potrebné pripraviť tabuľku do stavu bez medzisúčtov:
- V nástroji [Medzisúčty] zvolíme [Odstrániť všetky].
- Vyberieme tabuľku [Ctrl] + [*] a zmeníme farbu písma na automatickú.
Je na čase vyskúšať makro kombináciou, ktorú sme si zadefinovali: [Ctrl] + [m]. Výsledok uvidíte sami (samozrejme ak ste neurobili chybu pri zázname).
Uloženie makra
Makro je súčasťou súboru (zošitu).
Ak zvolíme pri zázname makra „uložiť makro do tohto zošitu”, je jeho súčasťou. Teda ak uložíme zošit, makro sa uloží s ním.
Od verzie 2007 je potrebné si dať pozor pri ukladaní makra na to, ako zvolíme postup. Máme dve možnosti:
- Ak ukladáme do staršej verzie (2003 a staršie) súboru, jednoducho zvolíme len uložiť.
- A uložíme do aktuálnej verzie, je potrebné si zvoliť zošit podporujúci makrá. Inak tam Excel nebude môcť makro zachovať, teda ho odstráni!
Upozornenie píšem aj preto, že sme si akosi odvykli čítať varovné správy a len ich odsúhlasiť. V praxi väčšinou vytvárame makro do existujúceho súboru (a ten v novej verzii nepodporuje makrá). Ak zvolíme len [Uložiť] (a nie uložiť ako…), Excel nás na to upozorní, že tam nie je možné uložiť makro. Makro bude musieť byť odstránené a predvolené tlačítko dialógu je [Áno], čo znamená, že sme si toho vedomí. Vtedy je potrebné zvoliť [Nie] a následne formát súboru podporujúci makrá.
Zabezpečenie makra
Pri otvorení súboru, ktorý obsahuje makro (alebo akýkoľvek VBA kód), sa zobrazí upozornenie o blokovanom obsahu. To, akým spôsobom, závisí od verzie a aktuálneho nastavenia Excelu:
Medzi [pásom kariet] a [riadkom vzorcov] sa zobrazí upozornenie o blokovanom obsahu (ak samozrejme je blokovaný). Kliknutím na tlačítko [Povoliť obsah] sa obsah trvalo povolí pre daný súbor s jeho umiestnením.
Medzi [pásom kariet] a [riadkom vzorcov] sa zobrazí upozornenie o blokovanom obsahu (ak samozrejme je blokovaný). Kliknutím na tlačítko [Možnosti…] sa zobrazí dialóg, kde máme možnosť dočasne povoliť obsah (makro). Pri ďalšom otvorení bude znovu obsah blokovaný s upozornením.
Pri otvorení súboru, kde sa nachádza makro, Excel zobrazí upozornenie zabezpečenia. Kliknutím na tlačítko [Zapnúť makrá] sa obsah dočasne povolí pre daný súbor. Pri ďalšom otvorení príde upozornenie znovu.
Ak je obsah blokovaný, so súborom sa dá naďalej pracovať, akurát bez makier, resp. VBA kódu. To by na druhej strane mohlo v niektorých prípadoch znefunkčniť používanie súboru.
Môj názor (, s ktorým sa nemusí nikto stotožňovať): Je to len taká „malá” právna hračka. Microsoft nás upozorní, že tam môže byť škodlivý obsah a dá nám na výber, čo s tým. Ak potvrdíme, právna zodpovednosť za prípadné škody sa automaticky prevádza na nás. Ak chceme však makrá používať, obsah musíme povoliť.
Je dobré, že Microsoft zmenil nové verzie súborov na formáty bez a s makrami. Bežný súbor má príponu xlsx alebo xlsb a súbor s podporou makier xlsm. Tým môžeme aj my presne vedieť (ešte pred otvorením), či tam je niečo naprogramované alebo nie. Tak isto je to napríklad v Outlooku. Ak máme v e-maily prílohu súbor Excelu, pri súboroch bez makier nám zobrazí jeho obsah priamo v Outlooku (bez potreby otvorenia v Exceli).
Nastavenie zabezpečenia
Popíšem tie najvýhodnejšie nastavenia, ktoré v praxi používam.
Máme dve možnosti:
- Pri upozornení na blokovaný obsah, presnejšie v dialógu, je vľavo dolu možnosť [Otvoriť centrum dôveryhodnosti].
- Tlačítko [Tlačítko Office
], potom [Možnosti programu Excel]. Vo formulári vyberieme v ľavom zozname [Centrum dôveryhodnosti] a ďalej vpravo dolu tlačítko [Nastavenie centra dôveryhodnosti].
Máme 2 možnosti:
- V ponuke [Nástroje] voľbou [Možnosti…] zobrazíme dialóg pre nastavenia programu. Vyberieme si kartu [Zabezpečenie] a vpravo dolu tlačítko [Zabezpečenie makier].
- V ponuke [Nástroje], potom [Makro] vyberieme voľbu [Zabezpečenie…], kde sa zobrazí ten istý dialóg.
V dialógu nastavujeme úroveň zabezpečenia makier. Ak pracujeme veľa s makrami (vo veľa súboroch), môžeme zmeniť nastavenie na [Nízka], samozrejme s vedomím, že budú povolené aj prípadné vírusy.
Potom vyberieme [Dôveryhodné umiestnenia] (nová funkcia v Exceli). Tu môžeme definovať priečinky (prípadne aj podpriečinky), ktoré má Excel považovať za dôveryhodné. To znamená, že ak budeme z toho umiestnenia otvárať súbory napr. s makrom, makro bude povolené a Excel nebude zobrazovať upozornenie o blokovanom obsahu.
Je možné definovať aj priečinok v sieti. Predtým je však potrebné dolu zvoliť [Povoliť dôveryhodné umiestnenia v osobnej sieti (neodporúča sa)]. Potom bude umožnené označiť priečinok v sieti za dôveryhodný. Je to z toho dôvodu, že je tam väčšie riziko škodlivého kódu.
Možnosti spustenia makra
Klávesová skratka
Túto možnosť sme si už popísali na začiatku.
Použitie: Odporúčam používať pre vlastnú potrebu. Ak budete robiť makro pre niekoho, bude sa vás neustále vypytovať na skratku a ako to má použiť, …
Tlačítko (resp. položka v menu)
Na panely [Rýchly prístup] rozbalíme ponuku a vyberieme voľbu [Ďalšie príkazy…].
Vo formulári rozbalíme zoznam [Oblasť výberu príkazov] a vyberieme [Makrá]. Nižšie sa nám zobrazí zoznam všetkých momentálne dostupných makier.
Vpravo hore je výber [Prispôsobiť panel s nástrojmi na rýchly prístup], kde si môžeme vybrať jednu z možností, kedy chceme mať tlačítko k dispozícii:
- [Pre všetky dokumenty] – Táto voľba je vhodná vtedy, ak potrebujeme mať k dispozícii makro stále. Toto je vhodné vtedy, ak sme makro zaznamenali do osobného zošitu makier. Inak by sa mohlo stať, že Excel makro nenájde. Tiež treba myslieť na to, že ako je makro zaznamenané, či sú univerzálne zadané príkazy pre rôzne súbory / tabuľky.
- [Pre názov otvoreného dokumentu] – Táto voľba je k dispozícii len pre nové verzie súboru, teda pre súbory do verzie 2003 (xls) nie. Tiež pridáme tlačítko ako v predošlej možnosti, ale len pre jeden súbor. Teda ak súbor nie je otvorený, nebude ani tlačítko. Také tlačítka sú potom inak graficky označené (väčšinou v rámiku).
Potom najlepšie dvojklikom na konkrétne makro v ľavom zozname ho pridáme na koniec pravého zoznamu [Rýchly prístup]. Dodatočne môžeme makro vybrať v pravom zozname a tlačítkami vpravo od zoznamu meniť poradie príkazov.
Klikneme pravým tlačítkom myši niekde na [Panely nástrojov] a vyberieme [Prispôsobiť]. Tiež môžeme ísť do ponuky [Zobraziť], potom [Panely s nástrojmi] a vyberieme [Prispôsobiť…]
Vo formulári potom vyberieme v ľavej časti [Makrá] a vpravo sa nám zobrazia dve položky pre umiestnenie makra do ponuky alebo na panel s nástrojmi:
Podľa toho, kam potrebujeme spustenie makra umiestniť, potiahneme položku na požadované miesto. Ak je to tlačítko, tak myšou vezmeme položku [Vlastné tlačítko] a ťaháme ho na niektorý z panelov medzi konkrétne tlačítka (zobrazí sa nám medzi nimi čierna zvislá čiara). Ak je to položka ponuky, tak ju len umiestníme na požadované miesto a to ako novú hlavnú ponuku alebo do existujúcej ponuky (, kde chvíľu počkáme a rozbalí sa) a môžeme ďalej pokračovať v presnom umiestnení, prípadne rovnako rozbaliť podponuku.
Potom môžeme upravovať niektoré vlastnosti tlačítka (aj položky ponuky) kliknutím pravým tlačítkom myši na dané tlačítko (položku). Pre tieto úpravy musí ostať otvorené okno [Prispôsobiť…] Najpodstatnejšie vlastnosti:
- [Priradiť makro…] – Najdôležitejšie. Bez toho tlačítko nefunguje. Excel po zvolení tejto vlastnosti ponúkne na výber zo zoznamu dostupných makier.
- [Názov] – Je to popis (bublinová pomoc) príkazu, ak chvíľu postojíme myšou nad tlačítkom. Symbolom [&] určíme, ktorý znak bude zvolený pre rýchlu voľbu klávesnicou (Alt + písmeno). Napr. pre medzisúčty by mohlo byť [&Medzisúčty], čo znamená [Medzisúčty], čo znamená možné spustenie kombináciou kláves [Alt] + [m]. A tým ma vlastne [Názov] ďalšiu funkciu.
- [Zmeniť tlačidlo], [Upraviť tlačidlo…] – Tu môžeme zmeniť vzhľad (ikonu) tlačítka buď výberom z predvolených možností alebo doslova nakresliť vlastnú.
Odporúčam použiť vtedy, ak sme makro zaznamenali do osobného zošitu makier.
Po zatvorení dialógu sa vrátime v Exceli z „režimu prispôsobenia pracovného prostredia” do „režimu používania”. A tak si môžeme vyskúšať spustiť makro – pritom však myslíme, že tabuľku opäť bude treba dať do pôvodného stavu bez medzisúčtov.
Tlačítko v hárku
- Ak nemáme, je potrebné zobraziť kartu [Vývojár].
- 2010-2013 – v ponuke [Súbor] zvolíme [Možnosti]. Vľavo zvolíme [Prispôsobiť pás s nástrojmi] vyberieme v pravom zozname kartu [Vývojár].
- 2007 - klikneme na [ Tlačítko Office] vyberieme [Možnosti programu Excel]. V oblasti [Obľúbené] vyberieme [Zobraziť kartu Vývojár a páse s nástrojmi].
- Na karte [Vývojár]v kategórii [Ovládacie prvky] rozbalíme tlačítko [Vložiť]. Potom v časti [Ovládacie prvky formulára] vyberieme [Tlačidlo]. Kliknutím-ťahaním-pustením nakreslíme obdĺžnik do požadovaného hárka.
- Excel následne od nás vypýta, ktoré makro má priradiť tlačítku.
- Po odsúhlasení dialógu môžeme upraviť text tlačítka.
- Kliknutím mimo tlačítka sa stane aktívnym na kliknutie, teda bude spúšťať zvolené makro.
- Pravým tlačítkom myši klikneme opäť niekde na panel nástrojov a zvolíme si panel [Formuláre]. Prejdeme do hárka, kde potrebujeme tlačítko.
- Zvolíme si [Tlačidlo] na panely [Formuláre] a kliknutím-ťahaním-pustením nakreslíme obdĺžnik.
- Excel následne od nás vypýta, ktoré makro má priradiť tlačítku.
- Po odsúhlasení dialógu môžeme upraviť text tlačítka.
- Kliknutím mimo tlačítka sa stane aktívnym na kliknutie, teda bude spúšťať zvolené makro.
Automatické spustenie makra
Tu stačí len nazvať makro „Auto_Open”. Makro sa bude spúšťať vždy pri otvorení dokumentu (samozrejme ak sú povolené makrá).