A Microsoft Office termékcsalád a legelterjedtebb irodai szoftvercsomag, a legtöbb vállalati számítógépen megtalálható. Sokrétű irodai feladatok ellátására alkalmas, nagyon széles ismertségnek örvend. A BI közeli területek, elsősorban a pénzügy különösen kedveli az Excel sokrétű szolgáltatásait, számítások, kimutatások, grafikonok és jelentések is könnyen készíthetők benne.

Talán kevesebben ismerik az Office család VBA (Visual Basic for Applications) nevű tagját, mely fejlett makrókészítő és futtató szoftverfejlesztő környezet, automatizálhatók vele az Office komponensei, sőt, a számítógép erőforrásainak igen nagy része is elérhető általa. Talán ez a legelterjedtebb szoftverfejlesztő környezet a világon, habár a legtöbben nem is tudják, hogy fent van a gépükön, hiszen a sztenderd Office telepítésnek része.

Egy blogbejegyzésnek nem lehet célja egy ilyen komplex eszköz használatának részletes bemutatása, sokkal inkább ötletek, részletek, figyelemfelkeltés; megpróbálok néhány kérdést körüljárni, illetve érdekességeket bemutatni, hogy képet kapjon az olvasó, mi mindenre képes ez az eszköz. A Microsoft oldalain részletes dokumentáció található, emellett jónéhány weblap, fórum is foglalkozik VBA programozással.

https://msdn.microsoft.com/en-us/VBA/VBA-Word

https://msdn.microsoft.com/en-us/VBA/office-shared-vba/articles/getting-started-with-vba-in-office

Mi is a VBA?

Egyrészt fejlesztői környezet, illetve nyelv, Visual Basic, mely funkcionálisan nagyon hasonló a .net környezet Visual Basic nyelvéhez, másrészt egy objektumstruktúra melyen keresztül elérhetőek az egyes programkomponensek (Excel tábla, Word dokumentum, email, stb.) azon funkciói melyeket a felhasználói felületről elérhetnek. Fontos megjegyezni, hogy a VBA interpreteresen fut, azaz a parancsok futáskor értékelődnek ki, ami jóval nagyobb rugalmasságot jelent a többi gyakran használt nyelvhez képest, sebességben viszont erős hátrány. Számításigényes problémákra így nem a legalkalmasabb a VBA.

Milyen feladatok végezhetők el VBA-val?

Röviden: szinte bármilyen repetitív feladat.  Néhány széles körben jól ismert funkció tulajdonképpen előre gyártott makró: például körlevél varázsló, solver; az Excel adatkapcsolatai tekinthetők makróknak is.

Néhány példa, ahol a makrózás nagy segítség lehet:

• automatikus formázás: dokumentum beformázása valamilyen logika szerint, megjegyzések eltűntetése
• webes űrlap rendszeresen történő automatikus kitöltése Excel riport adataival; továbbá bizonyos külső szoftverek, pl. iktató, könyvelőprogramok is vezérelhetők makróból, nem kell ugyanazokat az adatokat újra berögzíteni, mert elvégzi egy makró gombnyomásra
• interakció adatbázisokkal; nem csak lekérdezés futtatására és Excelbe töltésére van lehetőség (erre van külön menüpont amúgy is), de szinte bármire: adatbázis elindítása, SQL parancsok futtatása, visszakapott adatok feldolgozása
• „fejlett” körlevél; melléklet csatolása, beérkezett levélre „intelligens” automatikus válasz ami jóval túl mutat az out of office funkción, ’fejlett’ keresés a levelek között, csatolmányokban, beérkező email-adatszolgáltatás feldolgozása; elhelyezése fájlrendszerben, adatbázisban
• fájlrendszerből Office dokumentumok automatikus megnyitása, szerkesztése, összefűzése, kinyomtatása

A  levélküldési lehetőség némileg korlátozva lett, kb. 15-20 évvel ezelőtt voltak időszakok, amikor a globális emailforgalom több mint 99%-a makróvírusokból állt, ezek leginkább rövid üzenetek voltak Word vagy Excel csatolmánnyal, a csatolmányt megnyitva egy egyszerű kis kódocska lefutott és automatikusan és elküldte saját magát minden email címre ami az Outlook címlistájában talált, megnyitásra buzdító üzenet kíséretében, „mellékeltem a kért kimutatást” amit a címzett jó eséllyel megnyitott és így az ő kontaktjai is mind megkapták. Éppen az volt a gond, hogy ezt a feladatot egy  kb. 10 soros kód képes volt elvégezni, megírásához komolyabb tudás/képzettség sem kellett. Most már nem mennének ki a levelek, ellenben figyelmeztetések garmadáját kapná a felhasználó (Potenciálisan veszélyes makrót tartalmaz a dokumentum és letiltottuk, engedélyezi-e? Makró próbálja elérni az Outlookot, emailt próbál küldeni, stb.).

Hogyan érhető el a VBA?

Lehetőség van makró rögzítésére és futtatására az egyes Office programokból is a View/Macro/Record macro illetve View macro menüpontból:

Legegyszerűbben egy megnyitott Office alkalmazáson az Alt-F11 megnyomásával hozható elő a VBA, ekkor megnyílik a szerkesztőfelület,  a fenti ablakon az Edit gombbal is elérhetjük:

Makrók alapvető elemei

Rögzítettem egy kis makrót, épp ezt a szöveget gépelem. Lefuttatva a kurzor helyén begépelné ezt a szöveget, pont úgy viselkedne a Word mintha elkezdenék gépelni – igaz, sokkal-sokkal gyorsan gépel mint amire ember képes. Itt láthatóak a nyelv legalapvetőbb elemei:

• Sub – alprogram, eljárás, mögötte a neve (Macro1) illetve zárójelben a paraméterei, ez esetben nincsenek paraméterek. Az alprogram végét End Sub parancs jelzi.
• ’ jel mögé megjegyzések kerülhetnek, ezek nem futnak le
• Selection - ez a kurzor, illetve a kurzorral kijelölt szöveg. Ha csinálni akarok valamit vele akkor mint objektumnak meghívom valamely eljárását, jelen esetben TypeText-et, azaz gépelni fogok, amit gépelek az lesz a Text nevű paramétere a kérdéses eljárásnak. Mivel a TypeText-nek csak ez az egy paramétere van, kiadható lenne a parancs így is:

Selection.TypeText (" view/macro/record macro menüpontból érhető el (fent felület")

• _ - sortörés, vagyis a parancs a következő sorban folytatódik. A VBA-ban minden parancs egy sor, _-val lehet hosszú utasításokat tördelni több sorba

A nyelv „félig” objektumorientált, ami azt jelenti hogy van egy kész objektumstruktúra, amiből példányokat hozhat létre a makró – ez a gyakorlatban azt jelenti hogy elindul egy alkalmazás, lesz az Excelnek egy új füle, létrejön egy adatbáziskapcsolat, bekerül a dokumentumba egy új bekezdés. Új objektumtípust viszont nem lehet definiálni, sem leszármaztatni. Lehetőség van események definiálására, amikor ez bekövetkezik a hozzátartozó kód lefut: pl. kattintok, kijelölök egy cellát, megnyomok egy billentyűkombinációt, megnyitok egy állományt stb. Maga az alkalmazás, amihez a makró tartozik automatikusan elérhető bizonyos objektumokon keresztül, ezek eleve léteznek, pl. Excelnél a Sheets("Sheet1")lesz a Sheet1 nevű munkalap, a Sheets("Sheet1").Cells(1,1) a Sheet1 munkalap A1 cellája. Alább az A1 cella karaktertípusát Arial-ra állítja Excelnél, de ha egy word dokumentumon futtatom hibaüzenet jön, nincs Worksheets("Sheet1").

Worksheets("Sheet1").Cells(1.1).Font.Name = "Arial"

Létre hozhatóak objektum példányok, majd ezek változókhoz rendelhetőek. Változót a dim parancs hoz létre, hasonlóan működik más nyelveknél is, két speciális típus kivételével:

object – objektum változó, ami bármilyen objektum lehet, egyszerű adattípus nem. Értékadáskor kap típust, pl. megnyithatok egy excel táblát, létrehozhatok adatbáziskapcsolatot ezen keresztül.

variant – joker, ami tényleg bármi lehet, értékadáskor kap egyúttal típust is. Az alábbi kódnál V először 8 lesz, vagyis szám, majd adatbáziskapcsolat. Ez a kód így lefut. Mint ADODB.Connection rendelkezik .open metódussal a v, integerként nem rendelkezik, azaz ha a set-tel kezdődő sort törölném már nem futna a kód.

Dim v As Variant

v = 8

Set v = New ADODB.Connection

v.Open "Provider=SQLOLEDB;Initial Catalog=demo;Integrated Security=SSPI;"

v.Close

Onnantól kezdve hogy v-t adatbáziskapcsolatként definiáltam csatlakozhatok vele adatbázisokhoz. Ez a kódrészlet a laptopomon lévő demó adatbázishoz próbál csatlakozni a Windowsos felhasználómmal, nem kell külön jelszót megadjak. Ha odaadnám ezt a Word dokumentumot egy kollégámnak – vagy bárkinek -  ha lefuttatná a makrót az ő számítógépén lévő demó adatbázist próbálná elérni az ő felhasználójával, amennyiben van a gépén ilyen adatbázis sikerrel is járna. A fájlrendszerhez is hozzáfér a makró, mint ahogy a számítógép szinte összes erőforrásához. Nem is biztos értené mi történik a kolléga, hiszen kapott egy blogbejegyzést hogy nézze át.

Szerencsére a dolog nem ilyen egyszerű; először is kapna egy értesítést arról hogy a dokumentum makrót tartalmaz amit az Office keretrendszer egyből letiltana. Sőt a legutolsó Office változatban csak „makróbarát” formátumba menthető makró, a normál docx -be, xlsx-be nem. Hátha az xlsm (és a kis felkiáltójel) felkelti a figyelmét.

Kérdés, hol van a helye az Office-nak (illetve az Excelnek) a gyorsan fejlődő BI piacon; a Tableau és a Power BI professzionális szolgáltatást nyújt, viszont a felhasználóknak meg kell ismerniük, az Excel ellenben sokkal komolyabb múlttal és ismertséggel rendelkezik, a pénzügyi területeken nagyon elterjedt, ráadásul legújabb verzióiban helyet kaptak BI képességeket nyújtó eszközök is, első sorban a Power Pivot-ra gondolok, ami a Power BI alapja is.