A következőkben szakzsargontól mentesen nézzük át a VBA/Excel makró írás legfontosabb elemeit.

Mi a VBA?

Visual Basic for Applications. Az Office alkalmazásainak (Excel, Word, Access, Power Point, Outlook) programozási nyelve. Mivel a Microsoft saját alkalmazásaihoz készítette a VBA-t, így ennek a nyelvnek viszonylag egyszerű szabályai és szerkezete van, közérthető angolt használ.

Példának okáért, ha ezt a VBA kódot olvasod…

… akkor kitalálható – még ha nincs is a VBA-ban tapasztalatod – hogy a kód a B4-es tartomány értékét állítja 5-re.

Mi a makró?

Kódok olyan csoportja, amely parancsok sorozatát hajtja végre egy alkalmazáson (pl. Excelen) belül. Ha ehhez a VBA nyelvet használja, nevezhetjük VBA kódnak is. A makrók segítségével automatizálhatunk ismétlődő feladatokat, de akár saját függvényeket is írhatunk.

1. Példa makróra: szubrutin (Subroutine):

2. Példa makróra: felhasználói függvény (Function):

Az Excelben így néz ki az „Ertekes” függvény: =Ertekes(3)

az eredmény az A1-es cellában látható.

A makrót is tartalmazni képes fájlformátum a 2003-as Excelben a „Excel 97-2003-as munkafüzet” (.xls) kiterjesztés.

Az Excel 2007-es verziójától:
– „Makróbarát Excel-munkafüzet” (.xlsm) kiterjesztés a makró változatot
– „Bináris Excel-munkafüzet” (.xlsb) ami szintén képes makrókat kezelni
– „Excel-munkafüzet” (.xlsx) kiterjesztés a makrómentes változatot jelenti

Tehát ha a „Munka1” munkafüzet makrót tartalmaz, akkor azt „Munka1.xls” vagy „Munka1.xlsm” néven kell elmentenünk, esetleg „Munka1.xlsb” néven. A legyakoribb a „Munka1.xlsm”.

Fontos! Ha lefuttatunk egy makrót, akkor a Visszavonás (Undo) gomb nem működik, vagyis nem lehet visszavonni a műveletet, illetve visszaállítani a makró előtti állapotot, kivéve ha az ehhez szükséges kódot mi magunk külön meg nem írjuk. Ezért a makró futtatása előtt készítsünk másolatot a munkafüzetről vagy legalább mentsük el. Ha valami balul sülne el a makró futtatása közben/után, akkor zárjuk be a munkafüzetet mentés nélkül majd nyissuk meg újra. (Újabban az Excel már akkor is egészen jól helyre tudja állítani a fájlt, ha futás közben lefagyna és újra kell indítanunk az egészet.)

 

A VBA szerkesztői felülete (VBE vagyis Visual Basic Editor):

Itt tudjuk a VBA kódot, vagyis makrót létrehozni. A VBE kétféle módon érhető el:

1. a Fejlesztőeszközök menüben a „Visual Basic” gombra kattintunk:

Ha nincs ilyen menü, hozzá kell adni Excel-ben. Lásd az alábbi videómat 0:49-től 1:16-ig:

2. ha az Excel-ben vagyunk: ALT+F11-es billentyű-kombinációval

VBE kinézete és főbb részei:

1: Project (Projekt) ablak: itt található az összes megnyitott munkafüzet, fa szerkezetű elrendezésben. A fenti képen a „VBA_oldal_MakroMintak.xlsm”-hez tartozó projekt látható. Ezen belül három almappa található:

• Microsoft Excel Object (Excel objektumok): ezen belül lehet „ThisWorkbook” vagy az egyes munkalapokhoz (itt: „Sheet1”) kapcsolódó kódolási hely
• Űrlapok (Forms)
• Modulok (Modules): itt a „Module1”. A modul mappa tartalmazza a makrókat és felhasználói függvényeket.

2: Properties (tulajdonságok) ablak: az objektumok, modulok, űrlapok bizonyos tulajdonságait itt tudjuk megváltoztatni, mint pl. a modul nevét (2/A)

3: kódolási terület: itt történik a VBA kód vagyis makró létrehozása, szerkesztése. Ha „Subroutine”-ról vagyis szubrutinról van szó, akkor a makrónak „Sub”-bal kell kezdődnie és „End Sub”-bal végződnie, mint a fenti kép 1-es pontjánál. Ha pedig Functionról vagyis felhasználói függvényről van szó, akkor a makrónak „Function”-nal kell kezdődnie és „End Function”-nal végződnie.

4: Immediate ablak: a makró lefutásának eredményeit tudjuk itt ellenőrizni, illetve utasításokat is végrehajthatunk közvetlenül

5: Watches (Figyelő) ablak: makrók nyomon követésére, változók értékeinek megfigyelésére és hibakeresésre használható

Makrót kétféleképpen lehet létrehozni: rögzíteni vagy kreálni.

1. Makró rögzítése:

Az Excel (és a Word) képes a felhasználó által végzett tevékenységeket rögzíteni és azokat makró formájában visszaadni. A rögzített kód egy modulban tárolódik.

A rögzítés kétféleképpen indíthatjuk el: vagy a „Fejlesztőeszközök” menün belül a „Makró rögzítése” gombra kattintunk rá…

… vagy a munkalap bal alsó sarkában lévő makrórögzítés ikonra:

A megjelenő ablakban megadhatjuk a makró nevét, billentyűparancsot, a makró helyét és leírást:

Tegyük fel, hogy egy cella értékét meg akarom változtatni és ezt makrórögzítővel felvenni. Egy animált gif-en keresztül szemléltetem:

Makrórögzítő – előnyök:

• Semmilyen VBA tudás (vagy legfeljebb minimális) kell a használatához, illetve ahhoz, hogy a tevékenységeket makrók formájában kapjuk vissza
• a VBA guruk is használják: ha ellenőrizni akarnak egy parancsot, sokszor egyszerűbb a makrót rögzíteni, mint a súgót vagy a Google-t használni
• Word-höz is elérhető

Makrórögzítő – hátrányok:

• Csak az Excel-nél és a Word-nél áll rendelkezésre
• Felesleges dolgokat is rögzít, mint a görgetéseket, cellán való kattintásokat. Ennek eredményeképpen igen terjedelmes kódot kapunk, amit egyszerűsítenünk kell
• Egyes tevékenységeket nem rögzít
• Ismétlődő (ciklusos) feladatokat nem tudunk felvenni
• Csak szubrutint rögzíthetünk vele, felhasználói függvényt nem
• Komplex kódokat ne ezzel generáljunk

A makró rögzítésénél lehetőség van „Egyéni makró-munkafüzetben” (angolul Personal Macro Workbook= Personal.xlsb) tárolni a kódunkat:

Az egyéni makró-munkafüzetről az alábbi videómat ajánlom:

Ez egy speciális munkafüzet, amely az Excel indításakor automatikusan megnyílik és alapesetben rejtve marad. Az ebben tárol makrókat használhatjuk bármelyik munkafüzetnél. Ha mondjuk szeretnénk látni az éppen aktív munkafüzet elérési útját, akkor csak lefuttatjuk a kódot a Personal.xlsb-ből. Az alábbi videóm bemutatja, miként adjunk hozzá kódot az egyéni makró-munkafüzethez hogy aztán lefuttassuk:

2. Makró írása:

Elöljáróban annyit, hogy egy makró rögzítése általában nem a legtömörebb, legegyszerűbb és letisztultabb kódot adja vissza. Tegyük fel, hogy a B2-es cellát pirosra színezzük. Ilyen kódot generál az alkalmazás:

Eléggé terjedelmesnek tűnik egy egyszerű színezéshez képest. Kijelölhetnénk a kódban levő parancsokat (pl. Pattern= Minta) és F1-gyel megvizsgálhatnánk a súgó leírásokat. Azonban aki kicsit már jártas a VBA-ban az tudja, ha az alap mintát, árnyalatot stb. elfogadjuk a cellánál és csak a színét akarjuk pirosra állítani, akkor egyetlen sor is elegendő (itt az éberség kedvéért már A2-es cella van:)):

Mennyivel letisztultabb, nemde? A makró neve itt már „Macro2” mivel kétszer nem használhatjuk ugyanazt a nevet egy modulon belül.

A makró írásához elengedhetetlen a VBA felépítését átvennünk – dióhéjban:

Objektumok (Objects):

Senkit sem akarok elrettenteni, de az Excel teljes objektumhierarchiája valahogy így néz ki (egyébként nem is olyan könnyű egy épkézláb ábrát találni róla):

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Kép forrása: https://www.programering.com/a/MDM5ADMwATM.html

Az Alkalmazás Objektum (Application Object, ami az Excel) más objektumokat tartalmaz. Példának okáért: Munkafüzetek (Workbooks): ez a munkafüzet (Workbook) objektumainak összessége

Néhány objektum további objektumokat tartalmazhat.

Pl. az előbb említett Munkafüzetek (Workbooks) gyűjtemény az összes megnyitott Munkafüzet (Workbook) objektumot magában foglalja. A Munkafüzet (Workbook) pedig például tartalmazhatja:

Munkalapok (Worksheets): ez a munkalap (Worksheet) objektumainak összessége

A VBA-ban az objektumhierarchia az alábbiak szerint néz ki:

I. Alkalmazás (Application) objektum

II. Munkafüzet (Workbook) objektum

III. Munkalap (Worksheet) objektum

IV. Tartomány (Range) objektum

Kissé felhasználóbarátabb módon:

 

A fenti példánál így néz ki egy teljes VBA hivatkozás az A1-es cellára (a színek megkönnyítik a megértést a képnél használt magyarázattal):

Application.Workbooks(“VBA_oldal_MakroMintak.xlsm”).Worksheets(“Sheet1”).Range (“A1”)

A pontok segítségével hivatkozunk a hierarchia elemeire. Balról jobbra haladva a kódban a hierarchia fentről lefelé halad. Vagyis az „Application” a legmagasabb szint és a „Workbooks” ennek a tagja ami alatta van a rangsorban.

Az egyes szintek kibontva:

I. Alkalmazás (Application)

Ha pl. Excelben írjuk meg a makrót és itt is tároljuk, akkor ezt elhagyhatjuk. Vagyis a fenti példánál használt kód „Application” nélkül is működik:
Workbooks(“VBA_oldal_MakroMintak.xlsm”).Worksheets(“Sheet1”).Range (“A1”)

II. Munkafüzet (Workbook)

Az alkalmazás tisztázása után meg kell mondani az Excelnek, hogy melyik munkafüzetet (Workbook) akarjuk módosítani. Ezt többféleképpen tehetjük meg:

ThisWorkbook

Azt a munkafüzetet jelenti, amelyben a makrót írjuk. Ezt akkor használom, ha több munkafüzet is meg van nyitva és a kódot tartalmazó fájlra kell hivatkozni.

Workbooks(“VBA_oldal_MakroMintak.xlsm”)

Így hivatkozhatunk név szerint egy munkafüzetre. Ritkán használom, mert ha valaki átnevezi a munkafüzetet akkor hibára fut a kód.

ActiveWorkbook

Ezzel arra a munkafüzetre hivatkozunk, melyet a képernyőn éppen látunk. Akkor használom, ha a kódnak az éppen aktív munkafüzetnél kell lefutnia.

III. Munkalap (Worksheet)

A munkafüzet tisztázása után meg kell mondanunk, melyik munkalapot akarjuk használni. Itt is többféleképpen hivatkozhatunk:

ActiveSheet

Azt a munkalapot jelenti melyet a képernyőn látunk.

Worksheets(“Sheet1”)

Név szerint hivatkozás egy munkalapra. Hátránya, hogy ha a munkalapot átnevezzük, hibára fut a makró.

Worksheets(2)

A kódot tartalmazó munkafüzetben lévő munkalapra hivatkozik, balról kezdve a sorrendet: jelen esetben balról a második munkalapra

IV. Tartomány (Range)

A munkalapra történő hivatkozás után a tartomány megadása következik, mely lehet tartomány vagy cella.

Tartomány:

Range(“A1”)

A1-es tartományra vonatkozik.

Range(“A1:B3”)

A1:B3-as tartományra utal.

Cella:

Cells(1, 1)

A tartomány első (bal felső) celláját jelöli (1-es sor és 1-es oszlop).

Range(Cells(1, 1), Cells(3, 2))

A cells(1,1) (1-es sor és 1-es oszlop) a tartomány bal felső celláját, míg a cells(3,2) (3-as sor és 2-es oszlop) a tartomány jobb alsó celláját jelenti.

Ez adja meg a tartományt, ami itt az A1:B3-as lesz:

Eljutottunk tehát a legalsó szintig, a tartomány objektumig, ahol a tartományokat, cellákat tudjuk módosítani.

Objektum – fogalmak:

Tulajdonság (Property):

Az objektumot jellemzi, illetve információkat tárol róla, mint a munkalap neve vagy a cella színe. Pl. a cella háttérszíne:

Eljárás (Method):

Olyan tevékenység, melyet az objektum végre tud hajtani. Pl. az A1-es cella kijelölése:

Osztály (Class) vagy Osztály Modul (Class Module):

A VBA modul egy speciális típusa, melyet beszúrhatunk a VBA projektbe:

A munkafüzet is egy osztály, mivel vannak tulajdonságai (properties) és metódusai (methods). Az osztály modul segítségével új objektumtípusokat definiálhatunk a hozzájuk kapcsolódó tulajdonságokkal, metódusokkal és eseményekkel együtt.

 

Charles H. Pearson (alias Chip Pearson) idézetével számomra sokkal érthetőbbé és megjegyezhetőbbé váltak ezek a fogalmak (http://www.cpearson.com/Excel/Classes.aspx):

„If a class is analogous to a noun, a property is like an adjective — it describes the object. A method is like a verb — it carries out an action.”

Vagyis:

„Ha az Osztály (Class) a főnévhez hasonlatos, akkor a Tulajdonság (Property) olyan, mint a melléknév – leírást ad az objektumról. A Metódus (Method) pedig olyan, mint az ige – egy cselekvést hajt végre.”

Bízom benne, hogy a fenti ismertető hasznos és érthető volt a VBA/Excel makró téma alapjainak tisztázásában. Ha kérdésed, javaslatod van, ne habozz: szólj hozzá alul vagy lépj kapcsolatba velem itt.