Základy programovania VBA v Exceli

Používate v práci Microsoft Excel na dennej báze a niektoré z opakovaných úkonov vám už lezú na nervy… Nie ste sami. To otravné opakovanie je možné vyriešiť a dokonca elegantne. Presne nato sú totiž v Exceli makrá. Čo-to ste sa už o nich na našom blogu dočítali, no tentoraz vám prinášam konkrétne príklady. Pár VBA kódov s vysvetlivkami, ktoré vám môžu nielen ušetriť čas, ale i priniesť inšpiráciu (alebo aspoň podporiť sebavedomie, aby ste sa ich používania nebáli) a uvedomenie, že je to vlastne omnoho jednoduchšie ako to na prvý pohľad vyzerá… Čítajte.

Aby sa nám ľahšie v prostredí MS Office pracovalo, zapneme si zobrazenie karty „Vývojár“ – aby sme mali nástroje potrebné pri vytváraní makra po ruke. V princípe to funguje rovnako vo všetkých najznámejších programoch balíka Microsoft Office, budeme však popisovať postup konkrétne pre Excel.

V prvej položke menu „Súbor“ otvoríme „Možnosti“ a nájdeme sekciu „Prispôsobiť pás s nástrojmi“. V pravom zobrazenom poli s názvami kariet zaklikneme neaktívnu kartu „Vývojár“. Po odkliknutí „OK“ sa karta zobrazí v Exceli na konci hlavného menu.

Väčšinu zázrakov budeme robiť na nej. Sústredíme sa predovšetkým na nástroje „Makro“ a „Visual Basic“.

Aký je vlastne rozdiel medzi týmito dvoma výrazmi? Každý, kto pracuje dlhšie s Excelom, ich už určite niekedy počul a pomyslel si, že to patrí kamsi medzi tzv. high level funkcie Excelu. Pravda je niekde uprostred.

Makrá sú vlastne sady príkazov, pokynov, ktoré vopred zadefinujeme, zoradíme a používame v rámci excelovského zošita na vykonanie nejakých krokov. Tieto príkazy sú napísané v kódovacom jazyku VBA – Visual Basic for Application.
Excel (vlastne celý MS Office) ponúka dve možnosti vytvárania makier. Tou jednoduchšou a základnou možnosťou je nahrávanie makra – zapnete si v rámci Excelu „nahrávač“, vykonáte jednotlivé kroky, vypnete „nahrávač“. Kedykoľvek neskôr túto postupnosť krokov jedným klikom, alebo klávesovou skratkou spustíte. Podobne ako obľúbenú skladbu v počítači.

Ak vás zaujíma takýto postup vytvárania makra, prečítajte si viac v článku o tom ako vybabrať s makrami. V ďalšom sa budeme venovať tej druhej, trošku komplikovanejšej, ale o to zábavnejšej verzii používania makier. Tou je písanie kódu priamo vo vývojárskom prostredí programu. To dáva takmer neobmedzené možnosti realizácie.

Rozhranie pre písanie VBA kódu jednoducho otvoríme aj klávesovou skratkou ALT+F11. Nemusíme hľadať a vyklikávať na karte Vývojár nástroj Visual Basic.

Otvorí sa nám v popredí Excelu samostatné okno, akoby malý program v programe, v ktorom sa dejú programovacie zázraky. Nemusíte sa báť aj keď nemáte skúsenosti s programovaním. Stačí porozumieť základným princípom a výrazom. Keď som to pochopila ja… 🙂

Rozhranie Visual Basic

V hlavnom paneli rozhrania Visual Basic vidíme klasické „microsofťácke“ menu známe zo starých verzií Office, takže takmer nič nové a prekvapivé. Je pravda, že rozhranie vyzerá trošku retro. Veď aj je – prakticky sa za 20 rokov nezmenilo. Ale nedajte sa oklamať výzorom, ukrýva čarovného deduška ?
Okrem klasických možností pre prácu so súborom (File), upravovania (Edit) a výberu zobrazení (View) nás bude zaujímať veľmi užitočná položka Debug – esenciálny výraz pre každého, hoc aj začínajúceho programátora. Debug obsahuje nástroje, ktoré nám pomáhajú nájsť v kóde chyby. Tiež je pre nás zaujímavá nasledujúca položka menu Run, kde nájdeme nástroje na spustenie, zastavenie, či krokovanie svojho pripraveného VBA kódu.

Na ľavej strane rozhrania vidíme pod VBA Project (Zošit1) hárky, ktoré máme práve otvorené v zošite Excel. Kliknutím na hárok alebo celý zošit sa dostaneme k obrazovke určenej na písanie kódu. Môžete si všimnúť, že každý hárok má svoje okno, takže ich môžeme mať otvorených viac zároveň. Dávajte si preto pozor, aby ste sa nepoplietli a nepísali kód určený pre jeden hárok do druhého.

VBA kód budeme písať do záložky „Modul“ vľavo. Ak tam nie je, pridáme ho cez druhú ikonu zľava na paneli nástrojov Insert User Form (Module). To nám zabezpečí, že budeme môcť spustiť Makro v rámci celého zošita – nielen v rámci hárku (v tom prípade by sme písali makro do záložky „List“).

Aby sme pochopili, čo vlastne konkrétny makro-kód robí, ukážeme si na konkrétnych príkladoch pár používaných výrazov a princípy.

Nebudeme tentoraz začínať od nuly, respektíve od nuly a jednotky – teda základného binárneho počítačového jazyka – hoci je to výnimočne zaujímavá téma. V prípade, že sa chcete ponoriť do základov programovania, určite navštívte kurz úvod do programovania. Získate vynikajúci rozhľad a zistíte, že to vôbec nie je začarovaná krajina „kockáčov“.

Začnime s funkciami, premennými, rozhodovacími štruktúrami a cyklami. Toto sú 4 základné výrazy, s ktorými sa budete v programátorských kuloároch stretávať dookola.

Čo je funkcia

Veľmi jednoducho povedané – funkciou povieme programu, čo má spraviť. Rôzne jazyky majú rôzne funkcie a rôzne druhy zápisu funkcií. Nakoniec, funkcie v jednej ich podobe už pravdepodobne poznáte z práce v programe Excel.
Každá funkcia musí mať zadefinované parametre (argumenty), s ktorými pracuje a musíme ju zapisovať – ako všetky príkazy VBA – podľa predpísaného spôsobu, aby jej počítač rozumel.

Najznámejšou a najpoužívanejšou preddefinovanou funkciou v programovacom jazyku VBA pre Excel je funkcia „MsgBox“. Asi ste sa s ňou už aj stretli, len o tom neviete. Je to tá funkcia, ktorou vieme vyrobiť v Exceli vyskakovacie okno, do ktorého môžeme buď niečo zapísať, alebo kliknúť na tlačidlo v ňom. Práve o tom sú parametre danej funkcie – čo má byť obsahom nášho vyskakovacieho okna. Niektoré z parametrov sú povinné, niektoré sú nepovinné, minimálne však jeden povinný je. Taký „prompt“ je potrebný na to, aby sme nejaké to vyskakovacie okienko vytvorili, pričom je to parameter, ktorým definujeme text v okne.

Tu je príklad, vyskúšajte si to. Odkopírujte si tento text do Visual Basic Editora, stlačte „Run“ (alebo kláves F5) a uvidíte, čo sa stane.

Sub MsgBoxOKCancel()

MsgBox „Páči sa Vám VBA?“, vbYesNo

End Sub

Poďme si krátko vysvetliť, čo vlastne tento kód znamená.

Každý zápis VBA kódu alebo blok kódu musí mať zadefinovaný začiatok a koniec – Sub a end Sub. Každý blok kódu, teda to, čo sa nachádza medzi značkami Sub a end Sub je vlastne makro. Za každým Sub nasleduje názov makra, respektíve funkcie. Vždy sa snažíme makrá pomenúvať názvami tak, aby sme neskôr vedeli, čo robia. Najčastejšie používame takzvaný ťaví zápis: KazdeSlovoZacinaVelkymPismenomBezMedzierAbezDiakritiky.

Značka Sub je v skutočnosti skratkou anglického slova „Subroutine“ teda „Podprogram“.

Ďalším zápisom vyššie je definovaná funkcia MsgBox, ktorej sme priradili parameter – text „Páči sa Vám VBA?“ a ďalším parameterom sme predpísali popisy tlačidiel vo vyskakovacom okne – vbYesNo. Ľahké, nie?

Funkcie a ich povinné parametre nie je, samozrejme, nutné ovládať naspamäť, strýčko google je spravidla vždy ochotný pomôcť a ukázať širokú škálu funkcií. Jednoducho ich skopírujeme, vložíme do nášho VBA projektu a upravíme ich parametre podľa potreby.

Čo sú premenné?

Premenné, alebo v angličtine Variables, sú také šuflíčky, do ktorých uložíme informáciu, a keď ju potrebujeme, šuflíček otvoríme a informáciu použijeme. Táto informácia musí mať tiež nejaký parameter – musíme určiť, v akom „formáte“ je informácia uložená. Môže to byť číslo, môže to byť desatinné číslo, môže to byť text, môže to byť dátum, prípadne „boolean“ – teda pravda/nepravda.

Keď premennej priradíme jej typ, respektíve jej formát, hovoríme, že premennú deklarujeme. V každom programovacom jazyka sa premenná deklaruje inak. Nás bude teraz zaujímať výraz DIM (skratka pre Dimension)– týmto príkazom deklarujeme premennú v jazyku VBA.

Ukážeme si to na príklade:

Sub premenna()

Dim cislo As Integer

Cislo = InputBox(„Zadaj číslo od 1-10“, „Otázka“)

MsgBox cislo, , „Zadal si číslo“

End Sub

V tomto príklade sme do bloku kódu ohraničeného značkami Sub a End Sub (a názvom nášho makra) deklarovali premennú s názvom „cislo“ a definovali sme ju ako (As) Integer – teda číslo. Máme v šuflíku uloženú informáciu, že tam môžeme odložiť iba číslo, teda žiadny text, ani dátum, ani inú hodnotu ako celé číslo sa nám do šuflíčku odložiť nepodarí. Následne sme premennej priradili funkciu InputBox, ktorá je preddefinovaná v Exceli a vytvorí vyskakovacie okienko s textovým poľom, do ktorého môžeme zadať číselnú hodnotu. Tejto funkcii sme dali parametre – názov v okne hore (Otázka) a text, respektívne upozornenie, čo má používateľ spraviť. Potom nasleduje funkcia MsgBox, ktorú poznáme z predošlého príkladu – okno, ktoré nám ukáže vloženú hodnotu.

Rozhodovacie štruktúry

Poznáte Ify, Theny a Elsy? Nie, teraz nemyslím Elsu z Ľadového kráľovstva, ale to z angličtiny. If – ak, Then – tak a Else – inak. Ľudovo povedané – AK je splnená zadaná podmienka, TAK vykonaj toto čo ti definujem, INAK urob toto. Pokročilejší užívatelia Excelu tento výraz poznajú zo známej a často používanej funkcie IF. Neexistuje lepšia škola ako príklad, poďme si to rovno ukázať v kóde:

Sub RozhodovacieMakro()

Dim cislo As Integer

Cislo = InputBox(„Zadaj číslo od 1-10“, „Otázka“)

If cislo > 10 Then

InputBox „Zadaj číslo od 1-10“, „Zadal si priveľké číslo!“

Else

MsgBox cislo, , „Zadal si číslo“

End If

End Sub

Opakovanie je matka múdrosti – takže znovu otvoríme blok kódu značkou Sub a názvom makra (za ktorým stále nasledujú zátvorky). Deklarujeme premennú ako Integer – teda číslo. Premennej dáme znovu rovnakú funkciu ako v predošlom prípade – teda výzvu vo vyskakovacom okne na zadanie číslo v rozmedzí od 1 do 10. A teraz prichádza tá zábavná časť – If cislo (teda ak je hodnota vloženej premennej) > (je väčšia ako) 10 Then (urob nasledovné:) v rámci InputBox-u zmeň druhý parameter na upozornenie „Zadal si priveľké číslo“. Takže ak do textového poľa napíšeme akékoľvek číslo väčšie ako 10, program nám ho ofrfle a odpovie, že sme zadali priveľké číslo. V príkaze nasleduje Else (inak) – ak výzvu poslúchneme a zadáme číslo menšie ako 10, program zobrazí známy MsgBox s číslom, ktoré sme zadali.
Pozor, vo VBA vždy keď otvárame štruktúru „If“ musíme ju aj ukončiť „End If“. Nakoniec uzatvoríme blok makra kódom End Sub.

Cykly

Alebo po anglicky a v jazyku programátorov Loops teda „Loopy“. Ako už názov napovedá, jedná sa o algoritmus, ktorý bude vykonávať nami zadefinovanú činnosť v cykle, dookola, až kým mu nepovieme, že stačilo. Existuje niekoľko rôznych druhov loopov, poďme sa spoločne pozrieť na tie najzákladnejšie. Ako prvý si predstavíme takzvaný „For each“ cyklus, teda „pre každý“. Čo pre každý? Napríklad pre každý hárok v otvorenom zošite urob to čo ti poviem. Jednoduchý príklad:

Sub CyklusHarky()

Dim harok As Worksheet

For Each harok In Worksheets

harok.Visible = True

Next

End Sub

Veľmi šikovný a užitočný kúsok kódu, ktorý naraz odkryje všetky poskrývané hárky zošita. Premenná je zadefinovaná ako hodnota Worksheet – teda zošit. Cyklus v tomto prípade prechádza celým zošitom, hárok po hárku, pričom pre každý hárok nastavuje boolean hodnotu „True“ (pravda) k parametru Visible (viditeľný) – teda hárok bude viditeľný. Keď parameter nastaví na prvom zošite, ide na Next (ďalší), až kým nepríde na koniec hárkov v zošite. A potom jednoducho skončí. Urobí, čo má, za sekundu. Super pomôcka. Oveľa jednoduchšie ako odkrývať po jednom skryté hárky Excelu.

Ďalším známym a často používaným cyklom je takzvaný „Do while“ loop, teda „rob pokiaľ“ …je podmienka splnená. Príklad, ktorý sem teraz pridám, nie je v bežnom živote veľmi užitočný, nenapadá mi v akom scenári by sa dal použiť, každopádne sa na ňom veľmi jednoducho vysvetľuje logika „Do while“ loopu. Šikovných čitateľov niečo iste napadne. Ak vám tento cyklus vyriešil nejakú problematiku vo vašom Exceli, dajte vedieť na e-mail!

Sub DoWhileLoop ()

Dim cislo As Integer

cislo = 1

Do While cislo < 11

MsgBox cislo

cislo = cislo + 1

Čo máme v tomto príklade medzi otváracím a zatváracím príkazom? Znovu si definujeme premennú s názvom „cislo“ ako Integer a následne zadefinujeme jej počiatočnú hodnotu = 1. Nastavíme cyklus, teda „rob kým“ cislo < 11 a ďalej definujeme, čo má Excel robiť – zobraz MsgBox s premennou, pričom bude hodnota premennej „cislo“ postupne narastať o 1. Takže budeme mať okienko s číslom 1, keď ho odklikneme, vyskočí okienko s číslom 2 a tak ďalej – až po číslo 10, ktoré je posledné menšie ako naša zadefinovaná 11-tka.

Tak, to by sme mali nejaké príklady, na ktorých sme si vysvetlili hlavné programátorské termíny.

Syntax vo VBA

Čo je to syntax spomínaná v úvode? Teraz si trošku viac vysvetlíme čo je to syntax, pretože bez nej nám VBA nebude fungovať.
Syntax by sa dala jednoducho popísať ako programátorská gramatika – teda súbor pravidiel, ktoré pri písaní kódu musíme dodržiavať, inak napísaný kód nebude fungovať. A dostaneme za päť ako keď máme zlú gramatiku v diktáte.

Moja základná rada ako programátora začiatočníka znie: Google je môj najlepší kamarát. Akékoľvek funkcie, či prvky si jednoducho vygúglime a použijeme v našom VBA kóde. Nikto predsa učený z neba nespadol a poviem vám tajomstvo – robí to prakticky každý, kto začína. Nikto nesype funkcie z rukáva, väčšinou len skopíruje overené texty so správnou syntaxou a upravuje ich parametre.

Tiež je dôležité zarovnanie kódu, teda vnáranie jednotlivých príkazov do blokov kódu tak, aby ste sa v nich vy i prípadní upravovatelia makra ľahšie orientovali. Nezabudnite tiež na jednoznačné názvy blokov makro kódu a používanie takzvaného TaviehoZapisu – bez medzier a diakritiky.

Príkazové tlačidlo

Veľmi užitočná vecička je príkazové tlačidlo alebo – command button, ktorý môžete mať na hárku a jednoduchým kliknutím naň vyvoláte makro akciu, ktorú potrebujete.

Toto najrýchlejšie vytvoríme v Exceli pomocou nástroja na karte Vývojár. Na paneli Ovládacie prvky kliknite na ikonu tašky Vložiť a vyberte prvý nástroj Tlačidlo (ovládací prvok formulára). Pri jeho vytváraní dostanete možnosť priradiť mu niektoré z vašich vytvorených makier – je to extrémne jednoduchá a veľmi užitočná funkcia. Skúste si napríklad tento VBA kód – z hárku Excelu ním otvoríte aplikáciu Kalkulačka z operačného systému počítača.

Sub OpenCalculator()

Application.ActivateMicrosoftApp Index:=0

End Sub

Farby a formátovanie

Excel má síce zabudovanú svoju klasickú farebnú škálu, ale občas chceme farbičkovať a formátovať trošku viac elegantne. Keďže už máme nejaké tie základy za sebou, povedzme si ako formátovať bunky v škále RGB farieb. RGB – teda skratka Red (červená) Green (zelená) a Blue (modrá) sú tri základné farby, v ktorých vieme v digitálnom svete namiešať 16,8 mil. odtieňov rôznych farieb.
Princíp je jednoduchý, zápisom (0, 0, 0) dostaneme farbu čiernu – teda na škále ani jedna zo základných troch farieb nie je zastúpená. Pri zápise (255, 0, 0) dostaneme plnú červenú a tak ďalej a tak ďalej. Na internete je kopec „miešačiek farieb“, kde si vyberiete farbu, ktorá sa vám páči, vygenerujete si jej RGB kód a ten následne použijete na farbenie vo vašom VBA kóde.
Pozrite sa napríklad sem: https://www.w3schools.com/colors/colors_rgb.asp

Ukážeme si dva rôzne zápisy VBA kódu v prípade, že chceme nastaviť výplň konkrétneho rozsahu (oblasti) buniek – v angličtine Range.

Sub farba()

Range(„A1:A50“).Interior.Color = vbRed

End Sub

Tento príkaz zafarbí pozadie buniek v stĺpci A od A1 až po A50 preddefinovanou červenou.
Toto isté môžeme urobiť sofistikovanejšie, avšak s krajšou červenou farbou takto:

Sub farba()

Range(„A1:A50“).Interior.Color = RGB(183, 64, 71)

End Sub

Dáva nám to väčší priestor, keď sa chceme s formátovaním buniek „vyhrať“, lebo už máme plné zuby prednastavených farieb.
Zdá sa vám to komplikované? Vôbec nie je, nebojte sa! Používajte príkazové tlačidlo, priraďte mu toto farbičkové makro a pôjde vám to jedna radosť.

Už to počujem… To je zložité! OMG.
Chcela vám dodať odvahu s používaním kódov pri práci v Exceli. Vyzerá to zložito. Nie je.
Mimochodom, programátorský jazyk VBA je kompatibilný naprieč celým balíkom MS Office – teda ho vieme použiť aj pre Word, Outlook či Powerpoint. Naozaj nie je komplikované ho používať. Jediné, čo potrebujete, je sadnúť si k VBA a začať. A verte mi, budete za frajerov, keď budete vedieť pracovať hoci len so základmi VBA.

Určite odporúčam zúčastniť sa kurzu, ktorý som absolvovala. Odkryl mi veľkú časť netušených možností Excelu. Má dokonca aj pokračovanie – dve časti. A ja plánujem ísť aj ďalej, aby som vedela využiť potenciál Excelu naplno. Nižšie nájdete odkaz na kurz VBA a Makro, takže ak vás tento článok zaujal, určite si ho nenechajte ujsť!