Ebben a leckében megismerkedsz az Excel VBA egyik legerősebb és leginkább „profi” eszközével: a funkciókkal. A funkciók segítségével a kódodat kisebb, jól elkülöníthető egységekre bonthatod, amelyeket később újra és újra felhasználhatsz – akár makróban, akár Excel-képletként.
Megtanulod, mi a különbség a Sub és a Function között, hogyan lehet saját VBA funkciókat létrehozni, és miként tudsz velük értéket visszaadni. A lecke során azt is látni fogod, hogy a saját funkcióid ugyanúgy működhetnek, mint az Excel beépített függvényei, csak éppen te határozod meg a logikájukat.
Ez a lecke fontos mérföldkő az Excel automatizálásban: a funkciók használatával a kódod átláthatóbb, karbantarthatóbb és skálázhatóbb lesz. Innentől nem csak makrókat írsz, hanem újrafelhasználható építőelemeket, amelyekkel gyorsabban és tisztábban tudsz dolgozni VBA-ban.
Funkciók
Funkciónak nevezzük VBA-ban a teljes programunk azon részeit, amelyeket egy speciális feladat ellátására hoztunk létre, és valamilyen értéket adnak vissza.
Gyakorlatban ez úgy néz ki, hogy létrehozunk egy funkciót, kvázi egy kis programot, amit később, máshol felhasználunk. Így a funkcióknak 2 része van, egyik ahol definiáljuk, másik pedig az a forma, ahogy hivatkozunk rá.
Funkciók segítségével, akár saját képleteket is létrehozhatunk.
Nézzünk is egy egyszerű gyakorlati példát, hogy érthető legyen, miről is beszélünk.
Példa 1.
Nyissunk egy üres táblázatot, A1 cella értéke legyen 200, B1 cella értéke legyen 300. Létre fogunk most hozni egy saját képletet, ami összeadja ezt a két számot.
Hozzunk létre egy új makrót Makró1 néven.
A VBA ablakban, ezt látjuk:
Megtanultuk és elfogadtuk, hogy a makrónk a Sub keretben van mindig. Eddig csak ezt használtuk, azonban a funkciók nem Sub-ot használnak, hanem Function-t. (A a Sub és a Function között a különbség, hogy a Funcktion egy értéket ad vissza, a Sub pedig nem ad vissza értéket.)
Töröljük a Sub-os kódunkat, és írjuk be a következő kódot:
Function Osszead(a As Integer, b As Integer) Osszead = a + b End Function
Mit is látunk?
A Sub helyett Function a keret. A Makró1-et átneveztem Osszead-ra, majd azt követően egy zárójelben definiáltam két változót, agyiknek „a” a neve, másiknak „b”. Mind a kettő Integer, azaz szám. A keretben a programunk pedig összeadja „a”-t és „b”-t, ami az Osszeadnak az értéke lesz.
Ezzel létrehoztunk egy új funkciót, amit most használjunk is fel képletként.
Menjünk vissza a táblázatunkba. C1 cellába írjuk be: =Osszead(A1;B1)
Példa 2.
Mivel a kódos ablakban töröltük a Makró1 nevű Sub keretes makrónkat, így az töröltük is a rendszerből. Viszont létre tudjuk hozni úgy is, ha csak az előző funkciónk alá beírjuk újra. Ekkor a funkció és a sub között egy vonal fog megjelenni, ami segít átláthatóvá tenni a programunkat, valamit el is szeparálja egymástól a két elemet.
A 2. példánkban felhasználjuk az első példában írtakat, viszont nem képletként fogjuk alkalmazni, hanem felhasználjuk a tényleges makrónkban.
Meghatározunk két változót a Sub-os makrónkban, majd meghívjuk benne a funkciónkat.
Function Osszead(a As Integer, b As Integer) Osszead = a + b End Function
Sub Makró1()
'Felveszünk 2 változót
Dim elso_szam As Integer
Dim masodik_szam As Integer
'Értéke adunk ennek a két változónak
elso_szam = 400
masodik_szam = 600
'felhasználjuk a korábban készített funkciónkat, beletesszük a két változónkat
'MsgBox segítségével kiírjuk az értékét
MsgBox Osszead(elso_szam, masodik_szam)
End Sub
Az eredmény, hogy egy párbeszédablakban kiírja, hogy 1000.
Előre beépített funkciók
Ha a funkciók képletszerű alkalmazásából indulunk ki, akkor az Excel függvények is egy előredefiniált funkciók. Azonban ha VBA-ban, a kódunkban szeretnénk ezeket használni akkor ügyelnünk kell rá, hogy angolul használjuk ezeket a függvényeket. Ebben segíthet az angol-magyar, magyar-angol Excel függvény fordítónk is.
Azonban ügyeljünk rá, hogy a magyar és angol képletek között nem csak a függvény neve változik, hanem a magyarban használt pontosvessző helyett, angolban sima vesszőt használnak.
Nézzünk egy példát:
Alkalmazzuk a magyarul BAL függvényt a programunkban. Ez annyit tesz, hogy egy szövegben megnézi mi az első x számú karakter balról nézve.
Ha például a szövegünk kelkáposztafőzelék, és az első 5 karakterre vagyunk kíváncsiak, akkor a képlet így nézne ki:
=BAL(„Kelkáposztafőzelék”;5)
Viszont ha ezt a programunkban kell alkalmazni, akkor ez lesz:
Sub Makró1()
'Felveszünk 1 változót
Dim szoveg As String
'Értéke adunk a változónak
szoveg = "Kelkáposztafőzelék"
'Megnézzük a szoveg változó első 5 karakterét, balról nézve
MsgBox Left(szoveg, 5)
End Sub
Az eredmény „Kelká” lesz.
Gyakorlati feladat
1. Vegyünk fel 5-5 számot „A” és „B” oszlopba.
2. Készítsünk egy új képletet (funkciót), ami kiszámolja, hogy az A oszlop eleme hány százaléka a B oszlop elemének.
3. For ciklus segítségével írjuk ki a C oszlopba az új képletünket
Íme a megoldás:
Function szazalek(a As Integer, b As Integer) szazalek = a / b * 100 End Function
Sub Makró1()
For i = 1 To 5
Cells(i, 3).Value = szazalek(Cells(i, 1).Value, Cells(i, 2).Value)
Next
End Sub
Összefoglalás
– Megtanultuk alkalmazni a funkciókat
– Tudunk már készíteni saját képleteket
– Tudjuk, hogy angolul kell használni a képleteket, ha a programunkban akarjuk alkalmazni azokat.