Ez a poszt főként azoknak szól, akik már módosították a makrórögzítő által felvett kódot, illetve saját maguk írnak makrókat. Van néhány íratlan szabály, tipp, hogy miként lehet a VBA kódunkat olvashatóbbá tenni, optimalizálni és nem utolsó sorban gyorsítani.
1. Használd a makrórögzítőt (lásd ezen a linken: „1. Makró rögzítése” szekció)
Legtöbbször nem a legjobb kódot generálja, de óriási segítséget nyújt abban, merre induljunk el, hiszen az Excelben végzett tevékenységeinket konvertálja át kóddá.
2. Option Explicit használata a modul legtetején
– így ugyanis deklarálnunk kell a változókat, pl.: Dim Szoveg as String
– elkerülhetők az elgépelések, hiszen ha a “Szoveg” helyett “Szeveg”-et írunk, hibára fut a kód. Mindamellett a makró legtöbbször gyorsabban fut le, ha a deklarálást elvégezzük.
– beállítása a VBA szerkesztőjében: Tools – Options – Editor fül: pipát tenni a „Require Variable Declaration” elé:
3. Ha túl hosszú a makród, szedd szét kisebb darabokra, hiszen
– a rövidebb kódokat könnyebb olvasni, főként ha megjegyzésben odaírjuk, mit csinál az adott rész
– könnyebbé válik a hibák keresése és azok kijavítása
– egy-egy kódrészletet újra fel lehet használni másutt, mondjuk ha hozzáadjuk az Egyéni Makró Munkafüzethez (link)
4. Mielőtt egyetlen sor kódot is rögzítenél/írnál: készíts egy szimpla tervet vagy legalább egy skiccet, akár papíron.
Bevallom, nem volt könnyű ezt a hozzáállást rendszeresíteni. Herótom volt ugyanis a tervkészítéstől, időpazarlásnak tartottam. Beláttam azonban, hogy olykor igen hasznos, ugyanis:
– egyfajta hivatkozási pontként használhatom (hol is tartok a feladatban)
– motiváló, mikor a listán az egyik sorral végzek, kipipálom vagy a biztonság kedvéért 5X-8X áthúzom (ez mondjuk inkább már a színezés kategória😊) és jöhet a következő, vagyis látom az előrehaladást
5. Bizonyos parancsok ki-be kapcsolgatásával gyorsíthatjuk a makró futását. Amiket én használok:
a. A makró elején kikapcsolni (vagyis az értéket „False”-ra állítani):
|
1 2 3 4 5 6 |
Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.DisplayAlerts = False 'körültekintõen alkalmazzuk Application.EnableEvents = False 'körültekintõen alkalmazzuk Application.Calculation = xlCalculationManual 'körültekintõen alkalmazzuk ActiveSheet.DisplayPageBreaks = False 'ez munkalap-szintû parancs |
b. A makró végén kikapcsolni (vagyis az értéket „True”-ra állítani):
|
1 2 3 4 5 6 |
Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.DisplayAlerts = True 'körültekintõen alkalmazzuk Application.EnableEvents = True 'körültekintõen alkalmazzuk Application.Calculation = xlCalculationAutomatic 'körültekintõen alkalmazzuk ActiveSheet.DisplayPageBreaks = True 'ez munkalap-szintû parancs |
Én leginkább az első két opciót használom (Application.ScreenUpdating és Application.DisplayStatusBar). A többi körültekintést igényel.
Vegyük például az Application.DisplayAlerts-et. Ha ezt kikapcsoljuk, nem lájuk a hibaüzeneteket, azokat sem, melyek kritikusak a kód futásának szempontjából. Ennél a kódnál a munkafüzet bezárásra kerül a mentésre vonatkozó kérdés nélkül:
|
1 2 3 |
Application.DisplayAlerts = False Workbooks("BOOK1.XLSM").Close Application.DisplayAlerts = True |
Vagy az Application.EnableEvents, aminek az eseménykezeléshez van köze, mint a cellamódosítás vagy a munkafüzet mentése. Ha makróval változtatunk meg egy cellát, akkor általában nem kell ezt a fajta változtatást észlelnie a VBA-nak, így az értéket False-ra tehetjük.
Vagy épp az Application.Calculation. Ha manuálisra állítjuk a kód elején, előfordulhat, hogy teljesen inkorrekt eredményt kalkulál a makrónk.
6. „With” és „End With” használata, ha különféle műveleteket hajtunk végre ugyanazon az objektumon.
Így a makrónk egyszerűbb lesz, könnyebben olvasható és gyorsabb. Ráadásul az adott tulajdonságot/eljárást (ami itt az „ActiveCell”) sem kell számtalanszor megismételnünk:
Eredeti kódrészlet:
|
1 2 3 |
ActiveCell.Font.Color = vbWhite ActiveCell.Interior.Color = vbRed ActiveCell.Value = 10 |
Módosított kódrészlet:
|
1 2 3 4 5 |
With ActiveCell .Font.Color = vbWhite .Interior.Color = vbRed .Value = 10 End With |
7. Kerüljük a kijelölést amikor csak lehetséges, ugyanis a felesleges szelektálás rendkívül le tudja lassítani a futási időt:
1. példa: másolás-beillesztés kijelölés nélkül:
Eredeti kódrészlet:
|
1 2 3 4 |
Sheets("Sheet1").Range("a1:d5").Copy Sheets("Sheet1").Range("g1").Select ActiveSheet.Paste Application.CutCopyMode = False |
Módosított kódrészlet, mely ráadásul nem használja a vágólapot:
|
1 |
Sheets("Sheet1").Range("a1:d5").Copy Sheets("Sheet1").Range("g1") |
Vagy akár egyenlővé is tehetjük a két tartományt, ha csak az értékeket akarjuk másolni. Ez esetben sem kerülnek vágólapra az adatok:
|
1 |
Sheets("Sheet1").Range("g1:j5").Value = Sheets("Sheet1").Range("a1:d5").Value |
2. példa: tartomány háttérszínének beállításához sem kell a szelekció:
Eredeti kódrészlet:
|
1 2 |
Sheets("Sheet1").Range("a1:b5").Select Selection.Interior.Color = vbYellow |
Módosított kódrészlet:
|
1 |
Sheets("Sheet1").Range("a1:b5").Interior.Color = vbYellow |
8. Használt tartomány (Used Range) minimalizálása
Igenis sokat számít, hogy a makrónk 3.000 soron megy végig vagy 300.000-en. A kód futtatása előtt töröljük a felesleges sorokat/oszlopokat és csak a szükséges tartományban dolgozzunk. További részleteket a témában ebben a posztomban találsz.
9. Kerüljük az ún. volatile képleteket, mivel ezek minden számításnál újrakalkulálódnak.
Ilyenek pl.: ELTOLÁS (OFFSET), INDIREKT (INDIRECT), MA (TODAY), MOST (NOW), VÉL (RAND), VÉLETLEN.KÖZÖTT (RANDBETWEEN).
10. Ha egy képlet VBA-ban és Excelben is elérhető: használd az utóbbit, mert a beépített Excel formulák gyorsabbak. Pl.:
Ehelyett (VBA képlet):
|
1 |
Application.Sum(Range("a1:b1")) |
Használd ezt (Excel képlet):
|
1 |
Application.WorksheetFunction.Sum(Range("a1:b1")) |
11. Az előző ponthoz kapcsolódik, de külön vettem: minél jobban ismerjük az Excel-t és annak lehetőségeit, annál többször kell alkalmaznunk a beépített funkcióit – a makróban is. Az 10-es pontban említett képletre akár a saját felhasználói képletet (UDF-et) is létre tudunk hozni VBA-ban. Csak nincs sok értelme, ugyanis egyrészt az Excelben van rá megoldás, ami gyorsabb, másrészt minek találnánk fel újra a kereket/spanyolviaszt stb? Csak az időt pazaroljuk vele.
12. Ciklusok (különösen az egymásba ágyazottak) lehetőség szerinti minimalizálása
– ha tudsz ciklus nélküli megoldást, válaszd azt.
– ha mindenképpen ciklus kell (pl. FOR EACH vagy LOOP), akkor legyen egyszerű és tömör a kapcsolódó kód
– példa, amikor nem feltétlenül kell ciklus: a tartományban történő keresés. Ne menjünk végig a tartományon, helyette használjuk a létező funkciókat, mint pl.: FKERES, HOL.VAN, SZÖVEG.KERES, SZÖVEG.TALÁL.
13. Bizonyos időközönként nyiss meg pár régebbi makródat és próbáld meg őket egyszerűsíteni, átírni. Felemelő érzés, mikor az addigi fél oldalas kódot meg tudod írni pár sorban, jót tesz az egónak.
14. Folyamatos fejlődés. Manapság különféle helyekről lehet tanulni vagy épp segítséget kérni. Videótréningek, blogok, fórumok (főként az angol nyelvűek). Kedvenc linkek listája.
15. Ismerd a határokat:
Mit értek ez alatt? Az egyén saját, illetve az Excel/VBA határait. Ha a kódunk pár másodperc alatt lefut és teszi a dolgát, nem feltétlenül szükségszerű optimalizálni. Ha pedig a makró komplexebb és hosszabb ideig fut: előfordulhat, hogy nem tudjuk gyorsítani és nem is mindig lehet. Ilyenkor érdemes elgondolkoznunk, hogy az Excel/VBA-e a legmegfelelőbb eszköz a célra (nem pedig mondjuk az Access vagy épp egy SQL adatbázis, netán az Excel/VBA ezekkel való kombinációja).
16. Szünet, önjutalmazás.
Több, mint öt órája ülsz a gép előtt. Nem találod azt a fffffránya hibát, amitől megáll a makró. Már összemosódnak a karakterek a képernyőn. Erőt kell venni magadon, felállni és szünet. Az a kód még ott lesz 20 perc múlva is és senki nem fogja helyetted megoldani, ugye? Akkor hajrá: kávé, mosdó, kedvenc zeneklip – vagy mindez egyszerre. Bátrabbaknak egy hosszabb séta az utcai 3D-s világban.
+ 1 Tudom, lerágott csont, de könnyű átsiklani felette:
– a makrók használatakor a Visszavonás (Undo) nem fog működni Excelben
– mindig legyen biztonsági mentésünk (backup) az adott fájlról, ha valami balul sülne el (és fog is, higgyétek el)
Bízom benne, hogy tudtam újdonsággal szolgálni. Legyen szép napotok.
