Egyéni függvények létrehozása az Excelben

Az egyéni függvények, csakúgy mint a makrók, a VBA programozási nyelvet használják. Két lényeges módon azonban különböznek a makróktól. Először is Function típusú eljárásokat használnak Sub típusú eljárások helyett. Vagyis Function utasítással kezdődnek, nem pedig Sub utasítással, és End Function utasítással végződnek, nem pedig End Sub utasítással. Másodszor is az egyéni függvények számításokat végeznek, nem pedig műveleteket. Az utasítások bizonyos típusai, például a tartományokat kijelölő és formázó utasítások, nem használhatók az egyéni függvényekben. A jelen cikkből megtudhatja, hogyan hozhat létre és használhat egyéni függvényeket. A függvények és makrók létrehozásához a Visual Basic Editor (VBE) szerkesztőt kell használnia, mely egy, az Excel ablakától különálló ablakban nyílik meg.

Tegyük fel, hogy a vállalata 10%-os mennyiségi engedményt nyújt a termékek értékesítésekor, ha a rendelés mennyisége meghaladja a 100 egységet. Az alábbiakból megtudhatja, hogyan számíthatja ki ezt az engedményt egy egyéni függvény segítségével.

Az alábbi példában egy olyan rendelési űrlap látható, amely felsorolja az egyes tételeket, azok mennyiségét, egységárát és engedményét (ha érvényes), illetve az ezekből kiszámított árat.

Hozzon létre a munkafüzetben egy DISCOUNT nevű egyéni függvényt:

1. Nyomja le az Alt+F11 billentyűkombinációt (Macen az FN+ALT+F11 billentyűkombinációt) a Visual Basic Editor szerkesztő megnyitásához, majd válassza az Insert (Beszúrás) > Module (Modul) lehetőséget. Ekkor megjelenik egy új modulablak a Visual Basic Editor ablakának jobb oldalán.

2. Másolja az alábbi kódot a vágólapra, és illessze be az új modulba.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Készen is áll az új DISCOUNT függvény használatára. Zárja be a Visual Basic Editort, válassza a G7 cellát, és írja be a következőt:

=DISCOUNT(D7;E7)

Az Excel ekkor kiszámítja a 200 darab, 4750 forint egységárú tétel 10%-os engedményét, és visszaadja a 95 000 forint értéket.

A VBA-kód első sorában a DISCOUNT(mennyiség, ár) függvény azt jelezte, hogy a DISCOUNT függvényhez két argumentum szükséges: mennyiség és ár. Amikor meghívja a függvényt egy munkalapcellában, fel kell vennie ezt a két argumentumot. Az =ÁRENGEDMÉNY(D7;E7) képletben a D7 a mennyiség argumentum, az E7 pedig az ár argumentum. Most már átmásolhatja a DISCOUNT képletet a G8:G13 fájlba az alább látható eredmények lekéréséhez.

Tekintsük át, hogyan értelmezi az Excel ezt a függvényeljárást. Az Enter billentyű lenyomásakor az Excel megkeresi a DISCOUNT nevet az aktuális munkafüzetben, és megállapítja, hogy ez egy egyéni függvény egy VBA-modulban. A zárójelek közé foglalt argumentumnevek, a mennyiség és az ár, azok az értékek helyőrzői, amelyeken a kedvezmény kiszámítása alapul.

Az alábbi kódblokkban az „If” utasítás megvizsgálja a quantity argumentumot, és megállapítja, hogy a tételek eladott száma meghaladja-e a 100 értéket:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Ha az eladott tételek száma 100 vagy annál nagyobb, a VBA végrehajtja a következő utasítást, mely megszorozza a quantity értéket a price értékkel, majd az így kapott eredményt megszorozza a 0,1 értékkel:

Discount = quantity * price * 0.1

Az eredményt a kód a Discount nevű változóban tárolja. Azokat a VBA-utasításokat, amelyek egy értéket egy változóban tárolnak, hozzárendelés típusú utasításoknak nevezzük, mert kiértékelik az egyenlőségjel jobb oldalán lévő kifejezést, és hozzárendelik annak eredményét a bal oldalon megnevezett változóhoz. Mivel a Discount változó neve megegyezik a függvényeljárás nevével, az e változóban tárolt értéket visszaadja a program a DISCOUNT függvényt meghívó munkafüzetképletnek.

Ha a quantity értéke kevesebb mint 100, a VBA a következő utasítást hajtja végre:

Discount = 0

Ezután az alábbi utasítás kerekíti a Discount változóhoz társított értéket két tizedesjegy pontosságúra:

Discount = Application.Round(Discount, 2)

A VBA-ban nem található kerekítési függvény, az Excelben azonban igen. Ezért ez az utasítás a kerekítés végrehajtásához az Excelhez tartozó „Application” objektum „Round” (Kerekítés) metódusának használatára utasítja a VBA-t. Ezt úgy éri el, hogy hozzáadja az Application megnevezést a Round szó elé. Használja Ön is ezt a szintaxist, ha Excel-függvényt szeretne elérni egy VBA-modulból.

Az egyéni függvényeknek „Function” utasítással kell kezdődniük, és „End Function” utasítással kell végződniük. A függvény neve mellett a „Function” utasítás általában egy vagy több argumentumot is megad. Hozhat létre azonban argumentumok nélküli függvényt is. Az Excel számos olyan beépített függvényt tartalmaz, amelynek nincs argumentuma, ilyen például a VÉL és a MOST függvény.

A „Function” utasítást követően a függvényeljárásnak egy vagy több olyan VBA-utasítást kell tartalmaznia, amely döntéseket hoz és számításokat végez a függvénynek átadott argumentumok használatával. Végül, a függvényeljárás egy tetszőleges pontján el kell helyeznie egy utasítást, mely hozzárendel egy értéket a függvény nevével megegyező nevű változóhoz. Ezt az értéket adja vissza a program a függvényt meghívó képletnek.

Az egyéni függvényekben kevesebb VBA-kulcsszót tud használni, mint a makrókban. Az egyéni függvények számára mindössze annyi engedélyezett, hogy visszaadjanak egy értéket a munkafüzetben lévő képletnek vagy egy másik VBA-makróban vagy -függvényben szereplő kifejezésnek. Az egyéni függvények például nem méretezhetnek át ablakokat, nem szerkeszthetik a cellákban lévő képleteket, illetve nem módosíthatják a cellaszövegek betűtípusát, színét és kitöltési beállításait. Ha ehhez hasonló „műveleti” kódot helyez el egy függvényeljárásban, a függvény #ÉRTÉK! hibával tér vissza.

A függvényművelet egyetlen művelete (a számításokon kívül) egy párbeszédpanel megjelenítése. Az InputBox utasítást egyéni függvényekben használhatja a függvényt végrehajtó felhasználó bemenetének lekérésére. Az MsgBox utasítással információkat továbbíthat a felhasználónak. Használhat egyéni párbeszédpaneleket vagy UserFormokat is, de ez a témakör túlmutat a bevezetés hatókörén.

Még az egyszerűnek tűnő makrókat és egyéni függvényeket is nehéz lehet később megérteni. Könnyebben olvashatóvá teheti őket úgy, hogy magyarázatot fűz hozzájuk megjegyzések formájában. Megjegyzés hozzáadásához írja be a magyarázó szöveget, és helyezzen el elé egy aposztróf karaktert. Az alábbi példa a DISCOUNT függvényhez hozzáadott megjegyzéseket szemlélteti. Ha ehhez hasonló megjegyzéseket ad hozzá, azzal megkönnyítheti a VBA-kód későbbi szerkesztését saját maga és mások számára. Ha később módosítania kell a kódot, egyszerűbb lesz megérteni, hogy a kód milyen műveleteket végez, milyen célból.

Az aposztróf karakter arra utasítja az Excelt, hogy hagyja figyelmen kívül az adott sorban az aposztróftól jobbra található összes többi karaktert, így nemcsak teljes sort használó megjegyzéseket tud létrehozni, hanem a VBA-kódsorok jobb szélére is írhat megjegyzést. Célszerű lehet a hosszabb kódblokkokat egy megjegyzéssorral kezdeni, mely ismerteti a kódblokk általános célját, majd további megjegyzéseket hozzáadni az egyes utasításoktól jobbra.

A makrók és egyéni függvények dokumentálásának másik módja az, ha leíró neveket használ. Például a Címkék név helyett használhatja a Hónapcímkék nevet, mely pontosabban leírja a makró rendeltetését. A leíró nevek használata különösképpen nagy segítség lehet akkor, ha sok olyan eljárást kell létrehoznia, amelyek hasonló, de nem teljesen azonos célt szolgálnak.

A makrók és egyéni függvények dokumentálásának pontos módja egyéni ízlés kérdése. A legfontosabb az, hogy a használt dokumentálási módszert egységesen alkalmazza a kódban.

Az egyéni függvények használatához meg kell nyitnia azt a munkafüzetet, amelyben a függvényt tartalmazó modul található. Ha ez a munkafüzet nincs megnyitva, akkor a #NÉV? hibát kapja, amikor megkísérli használni a függvény. Ha egy másik munkafüzetben lévő függvényre hivatkozik, akkor a függvény neve előtt meg kell adnia a függvényt tartalmazó munkafüzet nevét. Ha például létrehoz egy DISCOUNT nevű függvényt egy Személyes.xlsb nevű munkafüzetben, és egy másik munkafüzetből szeretné meghívni ezt a függvényt, akkor a =személyes.xlsb!discount() képletet kell használnia, nem pedig egyszerűen a =discount() képletet.

Csökkentheti a beírandó szöveg mennyiségét (és elkerülheti az esetleges hibákat), ha az egyéni függvényt a Függvény beszúrása párbeszédpanelen választja ki. Az egyéni függvények a Felhasználói kategóriában jelennek meg:

Egyszerűbben is elérheti bárhonnan az egyéni függvényeit, ha egy külön munkafüzetben tárolja őket, majd menti az adott munkafüzetet bővítményként. Ezután a bővítményt beállíthatja úgy, hogy mindig elérhető legyen az Excel futtatásakor. Ezt a következő módon teheti meg:

A fenti lépések végrehajtása után az egyéni függvényei mindig elérhetők lesznek az Excel használatakor. Ha szeretné bővíteni a függvénytárát, térjen vissza a Visual Basic Editorba. A Visual Basic Editor projektböngészőjének VBAProject csoportjában megtalálhatja a bővítményfájlja nevét. A bővítménye az .xlam kiterjesztést fogja használni.

Ha duplán kattint erre a modulra a projektböngészőben, a Visual Basic Editor megjeleníti az egyéni függvényei kódját. Új függvény hozzáadásához helyezze a beszúrási pontot a kódablakban az utolsó függvény „End Function” záró utasítása mögé, és kezdje el beírni az új függvényt. Ezzel a módszerrel bármennyi függvényt hozzáadhat, melyek mindig elérhetők lesznek a Függvény beszúrása párbeszédpanel „Felhasználói” kategóriájában.

Ezt a tartalmat eredetileg Mark Dodge és Craig Stinson írta a Microsoft Office Excel 2007 Inside Out című könyv részeként. A tartalom frissítve lett az Excel újabb verzióinak megfelelően.