Vannak olyan feladataid, amik sokszor ismétlődnek és időigényesek? Van egy olyan kihívás, ami megoldhatatlannak tűnik a „hagyományos” módon az Excelben? Ebben a bejegyzésben ilyen esetekre találhatsz példákat, és hogy ebben hogyan tud a VBA programozás segíteni.

Visual Basic Application (VBA) az Office programok beépített programozási nyelve, tehát nem csak Excelben tudod használni. Lehetővé teszi, hogy olyan parancsokat vagy függvényeket írj, amelyekkel gyorsíthatod, megkönnyítheted a munkádat. 

A mindennapi munkavégzés során talán fel sem tűnik, hogy mennyi idő telik el az ismétlődő feladatok elvégzésével. Egy jól működő informatikai rendszer esetében is, amely megfelelően szolgáltatja a kellő adatokat, felmerülhet az igény, hogy azokat megformázzuk, átalakítsuk, más bontású riportokat készítsünk belőlük.

Ezekre a sokat ismétlődő feladatokra nyújthat megoldást egy automatizálás, amit már az Excel keretein belül is könnyedén megtehetünk.
Vagy olyan feladattal állsz szemben, amelynél úgy tűnik, hogy az Excel hagyományos keretein belül nincsen megoldás? Ebben az esetben is megoldást jelenthet a VBA programozás.

Ebben a bejegyzésben ilyen esetekre találhatsz példákat, hogy megismerhesd a VBA programozás által nyújtott lehetőségek tárházát. Természetesen ez csak egy ízelítő, és sokkal több lehetőség rejlik még benne.

Ahogy egyre magabiztosabb vagy az Excelben, tapasztalhatod, hogy bár rengeteg feladatodra gyors és egyszerű megoldást ad, mégis vannak olyan feladatok, amelyeket már nem tudsz megoldani Excelben, csak ha segítségül hívod a VBA-t. Ezeket ráadásul elég egyszer megírnod egy adott feladatra, majd utána éveken keresztül használhatod a munkádban.

Lássuk tehát az alábbi felsorolásban, hogy mire tudod használni a VBA-t!

Az adatok legtöbb esetben nyers formában állnak rendelkezésünkre egy rendszer által, de nekünk egy formázott, rendezett riportra lenne szükségünk. Ebben nyújthat segítséget az automatizálás.

A következő példában rendelkezésünkre áll egy riport a könyvelési programból, de a további adatfeldolgozás előtt még munkát igényel az átalakítása.

Egy makró segítségével az adatok könnyedén rendezhetők, formázhatók és számított mezők is kerültek a táblázatba. Ahhoz pedig, hogy az adattömb igazán jól kezelhető legyen az üres cellák is feltöltésre kerülnek a megfelelő adatokkal.

Sokszor előfordul, hogy az adatok rendelkezésünkre állnak egy vagy több rendszerből, de a felsővezetés felé történő riportáláshoz szükségünk van az adatok összefoglalásához, megformázásához.

Ebben az esetben több rendszerből származó adatokat szükségesek egy riport elkészítéséhez. Automatizálással ekkor is nagymértékben felgyorsítható a riport elkészítése.

A különböző adatforrásokra példa:

Az adatok összesítése, rendezése után pedig egy konszolidált riportot kapunk, automatikusan.

A középvezetőknek pedig egy részletesebb riport is rendelkezésre áll:

Az adatmennyiség és az elkészítésével töltött idő ebben a konkrét esetben:

• 3 különböző rendszerből származó,
• 8 különböző riport lehívása,
• 200 különböző projekt adatai az adott évre, és a projektek teljes élettartamára
• 1 konszolidált riport a felsővezetés részére
• 3 részletes riport a középvezetők részére
• A rendszerből származó adatok lekérése 10-15 perc
• A makrók futása, a kész riportok elkészülése 5 perc

Az adatok bevitelének standard és automatizált formája is megvalósítható a VBA által nyújtott lehetőségekkel, űrlapok használatával.

Abban az esetben, ha egy adott feladatot több ember is ellát és Excelben dolgoznak, akkor igen nehéz megoldani, hogy minden adat egyező formában kerüljön a rendszerbe. A későbbi adatfeldolgozás szempontjából viszont kritikus, hogy ne kellejen adattisztítást végezni, és a lehető leghatékonyabban nyerhessük ki a kellő információkat a rendszerből.

Ebben az esetben űrlapok és bevitel szabályok segítségével áthidalható a probléma, és biztosak lehetünk abban, hogy az adatok a megfelelő formában kerültek a rendszerbe.

A következő példa egy szőlőfelvásárlási program (Excel fájl) része, amiben folyamatosan követik a mérlegjegyek alapján az adott termelő által leadni kívánt szőlőmennyiséget, és minden hozzá szükséges adatot.  A legördülő listákból választható minden adat, a dátum, a mennyiség és a cukorfok mezők megadott szabály szerint tölthetőek csak ki, így egy mindenki által könnyen használható felületen biztosítva van az adatok pontos rögzítése.

Sok esetben előfordulhat, hogy egy az Excelben történő bizonyos eseményre reagálva kellene, hogy történjen valami. Ezeket általában manuálisan tesszük meg, de az eseménykezeléssel ennek automatizálására is van lehetőségünk.

Nagyobb adatmennyiség kezelése közben előfordulhat, hogy az Excel lefagy és elveszítjük a munkánk egy részét. Ezért (is) mindig azt javaslom, hogy a lehető legtöbbször mentsük el a fájlt, hogy a lehető legkevesebbet kelljen esetleg újra elvégezni.

Ennek kiküszöbölésére írhatunk egy makrót, ami bizonyos események esetén készít egy biztonsági mentést az adott fájlról. Ezt megtehetjük úgy is, hogy egy kritikus feladat, mint például a teljes munkafüzet újraszámolása előtt készít automatikusan egy másolatot a fájlról. Ez a háttérben lefut, nekünk nem kell tennünk semmit.

A munkafüzet megnyitása után rögtön is ellenőrizheti, hogy mikori az utolsó mentés, és készíthet róla az előző napi dátummal egy másolatot.

Az is nagyon gyakran előfordul, hogy egy adott munkalapon történő változásra kellene, hogy reagáljon az Excel és elvégezzen egy műveletet.
Erre egy klasszikus példa, hogy ha van egy dinamikusan változó listánk és hozzáadunk vagy elveszünk belőle elemeket, akkor is arra lenne szükség, hogy ABC sorrendbe legyen rendezve.

Tegyük fel, hogy egy legördülő listában egy névsort használunk. Ehhez a névsorhoz viszont bármikor hozzájöhet egy új név, vagy ki kell törölni belőle egyet. Ahhoz, hogy a legördülő listából könnyen ki lehessen választani a kellő nevet, szükséges, hogy rendezett legyen a lista. Ekkor egy makró tud reagálni arra az eseményre, hogy az adott munkalapon változás történt, és magától a háttérben sorbarendezi a névsort.

Vannak olyan függvények, amelyek ha külső munkafüzetre hivatkoznak, akkor csak akkor frissítik az eredményüket, amennyiben az adott külső munkafüzet is nyitva van. Ilyenek függvények például a SZUMHATÖBB (SUMIFS), DARABHATÖBB (COUNTIFS). Ezek ilyenkor egy #ÉRTÉK! (#REF) hibaüzenetet adnak vissza és ez felboríthatja az egész modellünket.

Amennyiben nem találunk olyan alternatív függvényeket, amelyekkel ez a probléma nem áll fenn, akkor ezeket a fájlokat minden esetben meg kell nyitnunk, ha használni szeretnénk az adott modellünket.

Ennek megkönnyítésére írhatunk egy olyan makrót, amely a munkafüzet megnyitásakor automatikusan felismeri, hogy mely fájlokra hivatkozik a munkafüzet, megnyitja a háttérben azokat és befrissíti az adatokat.

Az előzőekben pár példát láthattunk a lehetőségekre. Álljon itt még egy rövid felsorolás azokról a sokszor előforduló eseményekről, amelyekre automatizált folyamatokat írhatunk:

• Activate/Deactivate – Munkafüzet aktiválása/deaktiválása
• BeforeClose – Mielőtt bezárjuk a munkafüzetet
• BeforePrint – Mielőtt nyomtatunk
• BeforeSave – Mielőtt elmenti a munkafüzetet
• NewChart – Új diagramm beszúrása esetén
• NewSheet – Új munkalap beszúrása esetén
• SheetBeforeDelete – Mielőtt kitörlődik egy munkalap
• SheetActivate/Deactivate – Munkalap aktiválása/deaktiválása
• SheetPivotTableUpdate – Pivot táblák frissítése esetén
• stb…..

Ez a téma nem kapcsolódik közvetlenül az eseménykezeléshez, de lehetőségünk van arra is, hogy egy bizonyos billentyűkombinációhoz (lehetőleg olyanhoz, ami nem beépített) hozzárendeljünk egy műveletet.

Tegyük fel, hogy van egy olyan művelet, amit nagyon sokszor végzünk el a munkánk során, és vagy nincsen rá parancsgomb vagy pedig túl sokat kellene kattintgatnunk érte, akkor egy makró segítségével hozzá tudjuk rendelni egy billentyűkombinációhoz.

Ilyen lehet például egy irányított kijelölés, ahol csak a logikai értékeket tartalmazó képleteket szeretnénk megjelölni.

Vannak olyan feladatok, amikor úgy gondolhatjuk, hogy ha nem találunk rá az Excelben megoldást, akkor csak manuálisan tudjuk megoldani őket. Ezekben az esetekben is segítséget nyújthat a VBA.

Egyik ügyfelünknél fordult elő egy olyan probléma, hogy egy forrásfájlban az adatok egy része egy cellában található meg, egy speciális karakterrel elválasztva. Ahhoz, hogy az adatbázis kezelhető legyen, ezeknek és a hozzá tartozó többi adatnak soronként kellene megjelennie.

Természetesen a szövegből oszlopok parancs segítségével az Excelben is megoldható a feladat egy része, de abban az esetben a cellák tartalma oszlopokba rendeződne és azt még át kell alakítani. A teljes adatbázisnak több száz sora van, így ez igazán időigényes lenne.

Egy algoritmus segítségével könnyedén szétválaszthatók az adatok, és a többi oszlopban található többi adat is hozzárendelhető.

Az eredmény a következő:

Tegyük fel, hogy vannak olyan számítások, amelyeket nem tudunk az Excel saját beépített függvényeivel megoldani vagy pedig túl bonyolult lenne az egybeágyazott függvény. Ebben az esetben tudunk saját függvényt írni a VBA segítségével és ugyanúgy használni, mint a többi függvényt.

Egy egyszerű példában, tegyük fel, hogy egy kereskedő cég 100 db felett 10% kedvezményt ad az árból. Ennek a kedvezménynek a kiszámítására írható egy függvény a VBA-ban.

Egy megoldása a következő:

Így kapunk egy függvényt, amit már az Excel-en belül bármikor használni tudunk. Két argumentuma van. A mennyiség és az ár, és a adott kedvezményt adja vissza eredményül, mint a következő példában:

A munkalapokkal rengeteg műveletet tudunk végezni Csoport módban, de vannak olyan műveletek, amelyek sajnos nem végezhetők el egyszerre több munkalapon.

Képzeljünk el egy munkafüzetet, ahol a különböző munkavállalóknak a saját nevükhez rendelt munkalapot kell kitölteniük. Azt nem szeretnénk, hogy valaki más adataiba bele tudjon nyúlni, ezért le szeretnénk védeni a többi munkalapot, vagy el akarnák rejteni mindegyiket, ami nem az adott munkavállalóhoz tartozik.

Ilyen műveleteket is könnyedén elvégeztethetünk a VBA-val, mind például:

• Összes (vagy kiválasztott) munkalap(ok) elrejtése, felfedése
• Összes (vagy kiválasztott) munkalap(ok) lapvédelémének ki-/bekapcsolása
• Munkalapok ABC sorrendbe rendezése

Bízom benne, hogy ezzel az átfogó bejegyzéssel kaptál egy képet arról, hogy mire is tudod használni a VBA programozást.

Csatlakozz az Excelneked.hu tanulóihoz, és jelentkezz az online Excel makró (VBA) tanfolyamunkra!