Obsah
Aj keď neviete programovať, existuje veľa miest (knihy, webové stránky, fóra), kde nájdete hotový kód makra VBA pre obrovské množstvo typických úloh v Exceli. Podľa mojich skúseností väčšina používateľov skôr či neskôr zhromaždí svoju osobnú zbierku makier na automatizáciu rutinných procesov, či už ide o prekladanie vzorcov na hodnoty, zobrazovanie súčtov v slovách alebo sčítanie buniek podľa farieb. A tu nastáva problém – kód makra vo Visual Basicu je potrebné niekde uložiť, aby sa dal neskôr použiť v práci.
Najjednoduchšou možnosťou je uložiť kód makra priamo do pracovného súboru prechodom do editora Visual Basic pomocou klávesovej skratky ostatné+F11 a pridaním nového prázdneho modulu cez menu Vložiť – Modul:
Táto metóda má však niekoľko nevýhod:
- Ak existuje veľa pracovných súborov a všade je potrebné makro, napríklad makro na konverziu vzorcov na hodnoty, potom budete musieť skopírovať kód v každej knihe.
- Nesmie sa zabudnúť uložiť súbor vo formáte s povoleným makrom (xlsm) alebo v binárnom formáte knihy (xlsb).
- Pri otvorení takéhoto súboru makro ochrana zakaždým vydá varovanie, ktoré je potrebné potvrdiť (dobre alebo úplne vypnúť ochranu, čo nemusí byť vždy žiaduce).
Elegantnejším riešením by bolo vytvoriť váš vlastný doplnok (doplnok Excel) – samostatný súbor špeciálneho formátu (xlam) obsahujúci všetky vaše „obľúbené“ makrá. Výhody tohto prístupu:
- Bude to stačiť pripojte doplnok raz v Exceli – a jeho postupy a funkcie VBA môžete použiť v akomkoľvek súbore na tomto počítači. Opätovné ukladanie pracovných súborov vo formátoch xlsm a xlsb preto nie je potrebné, pretože. zdrojový kód nebude uložený v nich, ale v súbore doplnkov.
- ochrana nebudú vás obťažovať ani makrá. doplnky sú podľa definície dôveryhodné zdroje.
- Môcť urobiť samostatná karta na páse kariet Excel s peknými tlačidlami na spustenie doplnkových makier.
- Doplnok je samostatný súbor. Jeho ľahko sa prenáša z počítača do počítača, podeliť sa oň s kolegami alebo ho aj predať 😉
Prejdime si krok za krokom celým procesom vytvárania vlastného doplnku Microsoft Excel.
Krok 1. Vytvorte doplnkový súbor
Otvorte Microsoft Excel s prázdnym zošitom a uložte ho pod akýmkoľvek vhodným názvom (napr MyExcelAddin) vo formáte doplnku s príkazom Súbor – Uložiť ako alebo kľúče F12, s uvedením typu súboru Doplnok Excel:
Upozorňujeme, že v predvolenom nastavení Excel ukladá doplnky do priečinka C:UsersYour_nameAppDataRoamingMicrosoftAddIns, ale v zásade môžete zadať ľubovoľný iný priečinok, ktorý je pre vás vhodný.
Krok 2. Pripojíme vytvorený doplnok
Teraz doplnok, ktorý sme vytvorili v poslednom kroku MyExcelAddin musí byť pripojený k Excelu. Ak to chcete urobiť, prejdite do ponuky Súbor – Možnosti – Doplnky (Súbor — Možnosti — Doplnky), kliknite na tlačidlo O nás (choď) v spodnej časti okna. V okne, ktoré sa otvorí, kliknite na tlačidlo preskúmanie (prehliadať) a zadajte umiestnenie nášho súboru doplnkov.
Ak ste urobili všetko správne, potom náš MyExcelAddin by sa mali objaviť v zozname dostupných doplnkov:
Krok 3. Pridajte makrá do doplnku
Náš doplnok je prepojený s Excelom a úspešne funguje, no zatiaľ v ňom nie je ani jedno makro. Poďme naplniť. Ak to chcete urobiť, otvorte editor jazyka Visual Basic pomocou klávesovej skratky ostatné+F11 alebo tlačidlom Visual Basic pútko vývojka (vývojár). Ak karty vývojka nie je vidieť, dá sa zobraziť cez Súbor – Možnosti – Nastavenie pásu (Súbor — Možnosti — Prispôsobiť pás s nástrojmi).
V ľavom hornom rohu editora by malo byť okno projekt (ak to nie je viditeľné, zapnite ho cez ponuku Zobraziť — Project Explorer):
Toto okno zobrazuje všetky otvorené zošity a spustené doplnky programu Microsoft Excel vrátane našich. VBAProject (MyExcelAddin.xlam) Vyberte ho myšou a cez menu doň pridajte nový modul Vložiť – Modul. V tomto module uložíme VBA kód našich doplnkových makier.
Môžete buď napísať kód od začiatku (ak viete, ako programovať), alebo ho skopírovať odniekiaľ pripravený (čo je oveľa jednoduchšie). Poďme na testovanie zadať kód jednoduchého, ale užitočného makra do pridaného prázdneho modulu:
Po zadaní kódu nezabudnite kliknúť na tlačidlo uložiť (disketa) v ľavom hornom rohu.
Naše makro VzorceToValues, ako si viete ľahko predstaviť, prevádza vzorce na hodnoty vo vopred zvolenom rozsahu. Niekedy sa tieto makrá nazývajú aj tzv postupy. Ak ho chcete spustiť, musíte vybrať bunky so vzorcami a otvoriť špeciálne dialógové okno Makrá z karty vývojka (Vývojár – makrá) alebo klávesová skratka ostatné+F8. Za normálnych okolností toto okno zobrazuje dostupné makrá zo všetkých otvorených zošitov, ale doplnkové makrá tu nie sú viditeľné. Napriek tomu môžeme do poľa zadať názov nášho postupu názov makra (názov makra)a potom kliknite na tlačidlo beh (beh) – a naše makro bude fungovať:
| |
Tu môžete tiež priradiť klávesovú skratku na rýchle spustenie makra – za to je zodpovedné tlačidlo parametre (Možnosti) v predchádzajúcom okne Makro:
Pri priraďovaní kláves nezabúdajte, že rozlišujú veľké a malé písmená a rozloženie klávesnice. Ak teda priradíte kombináciu like ctrl+Й, potom sa vlastne v budúcnosti budete musieť uistiť, že máte zapnuté rozloženie a stlačiť dodatočne smenadostať veľké písmeno.
Pre pohodlie môžeme tiež pridať tlačidlo pre naše makro na panel nástrojov rýchleho prístupu v ľavom hornom rohu okna. Ak to chcete urobiť, vyberte Súbor – Možnosti – Panel s nástrojmi Rýchly prístup (Súbor — Možnosti — Prispôsobiť panel s nástrojmi Rýchly prístup)a potom v rozbaľovacom zozname v hornej časti okna možnosť Makrá. Potom naše makro VzorceToValues možno umiestniť na panel pomocou tlačidla pridať (Add) a pomocou tlačidla vyberte ikonu Zmena (Edit):
Krok 4. Pridajte funkcie do doplnku
Ale makro-postupy, sú tiež funkčné makrá alebo ako sa im hovorí UDF (Používateľom definovaná funkcia = používateľom definovaná funkcia). Vytvorme samostatný modul v našom doplnku (príkaz menu Vložiť – Modul) a vložte tam kód nasledujúcej funkcie:
Je ľahké vidieť, že táto funkcia je potrebná na odpočítanie DPH zo sumy vrátane DPH. Nie Newtonov binom, samozrejme, ale poslúži nám ako príklad, ktorý ukáže základné princípy.
Všimnite si, že syntax funkcie sa líši od procedúry:
- používa sa konštrukcia Funkcia…. End Function namiesto toho Sub … Koniec Sub
- za názvom funkcie sú jej argumenty uvedené v zátvorkách
- v tele funkcie sa vykonajú potrebné výpočty a následne sa výsledok priradí do premennej s názvom funkcie
Všimnite si tiež, že táto funkcia nie je potrebná a nie je možné ju spustiť ako predchádzajúci postup makra cez dialógové okno Makrá a tlačidlo beh. Takáto funkcia makra by sa mala používať ako štandardná funkcia pracovného hárka (SUM, IF, VLOOKUP...), teda stačí zadať do ľubovoľnej bunky, pričom ako argument uveďte hodnotu sumy s DPH:
… alebo zadajte cez štandardné dialógové okno na vloženie funkcie (tlačidlo fx v riadku vzorcov) výberom kategórie Definované užívateľom (Definované užívateľom):
Jediným nepríjemným momentom je tu absencia bežného popisu funkcie v spodnej časti okna. Ak ho chcete pridať, musíte urobiť nasledovné:
- Otvorte editor jazyka Visual Basic pomocou klávesovej skratky ostatné+F11
- Vyberte doplnok na paneli Projekt a stlačte kláves F2otvorte okno Prehliadač objektov
- Vyberte svoj doplnkový projekt z rozbaľovacieho zoznamu v hornej časti okna
- Kliknite pravým tlačidlom myši na funkciu, ktorá sa zobrazí, a vyberte príkaz vlastnosti.
- V okne zadajte popis funkcie Popis
- Uložte súbor doplnku a reštartujte excel.
Po reštarte by funkcia mala zobraziť popis, ktorý sme zadali:
Krok 5. Vytvorte kartu doplnku v rozhraní
Posledným, síce nie povinným, no príjemným dotykom bude vytvorenie samostatnej záložky s tlačidlom na spustenie nášho makra, ktoré sa objaví v rozhraní Excelu po pripojení nášho doplnku.
Informácie o kartách, ktoré sa štandardne zobrazujú, sú obsiahnuté v knihe a musia byť naformátované v špeciálnom kóde XML. Najjednoduchší spôsob, ako napísať a upraviť takýto kód, je pomocou špeciálnych programov – editorov XML. Jedným z najpohodlnejších (a bezplatných) je program Maxima Novikova Ribbon XML Editor.
Algoritmus na prácu s ním je nasledujúci:
- Zatvorte všetky okná programu Excel, aby pri úprave kódu XML doplnku nedošlo ku konfliktu súborov.
- Spustite program Ribbon XML Editor a otvorte v ňom náš súbor MyExcelAddin.xlam
- S tlačidlom karty v ľavom hornom rohu pridajte útržok kódu pre novú kartu:
- Musíte zadať prázdne úvodzovky id našej karte a skupine (akékoľvek jedinečné identifikátory) a v štítok – názvy našej karty a skupiny tlačidiel na nej:
- S tlačidlom gombík na ľavom paneli pridajte prázdny kód pre tlačidlo a pridajte k nemu značky:
— štítok je text na tlačidle
— imageMso — toto je podmienený názov obrázka na tlačidle. Použil som ikonu červeného tlačidla s názvom AnimationCustomAddExitDialog. Názvy všetkých dostupných tlačidiel (a je ich niekoľko stoviek!) možno nájsť na veľkom množstve stránok na internete, ak vyhľadáte kľúčové slová „imageMso“. Na začiatok môžete ísť sem.
- onAction – to je názov procedúry spätného volania – špeciálneho krátkeho makra, ktoré spustí naše hlavné makro VzorceToValues. Tento postup môžete nazvať ako chcete. Pridáme o niečo neskôr.
- Správnosť všetkého vykonaného môžete skontrolovať pomocou tlačidla so zeleným začiarknutím v hornej časti panela nástrojov. Na tom istom mieste uložte všetky zmeny kliknutím na tlačidlo s disketou.
- Zatvorte Ribbon XML Editor
- Otvorte Excel, prejdite do editora Visual Basic a pridajte do nášho makra procedúru spätného volania KillFormulastakže spustí naše hlavné makro na nahradenie vzorcov hodnotami.
- Uložíme zmeny a po návrate do Excelu skontrolujeme výsledok:
To je všetko – doplnok je pripravený na použitie. Naplňte ho vlastnými postupmi a funkciami, pridajte nádherné tlačidlá – a používanie makier vo vašej práci bude oveľa jednoduchšie.
- Čo sú makrá, ako ich používať vo svojej práci, kde získať kód makra vo Visual Basicu.
- Ako vytvoriť úvodnú obrazovku pri otvorení zošita v Exceli
- Čo je to Osobná kniha makier a ako ju používať