Képletek VBA-ban

Azzal nem árulok el nagy titkot, hogy Excelben a képletek is makrózhatók. Elég egyszer „megszenvedni” a korrekt formuláért, utána pedig ezt is automatizálhatjuk. Viszonylag könnyű belekeveredni: rögzítővel felveszünk egy képletet, és a kód jó eséllyel le is fut utána. De mi van mondjuk akkor, ha a VBA-ban megírt képletnek dinamikusnak kell lennie (a forrástartomány változása esetén is működjön)? Mikor kell .Formula-t és mikor .FormulaR1C1-et használni? Exceles vagy VBA-s függvényt alkalmazzunk?

Lássunk egy kis összegzést a témában.

VBA-ban a képletek beírása ugyebár angolul (azon belül is amerikai angol) történik.

Az összegzés összegzése:

I. Hivatkozási típusok (link)
II. Változók használata képletekben (link)
III. Excel és VBA képletek alkalmazása (link)
IV. Felhasználói függvény (UDF = User Defined Function) (link)
V. Képletek frissítése VBA-ban (link)

_

 

I. Hivatkozási típusok

A tartománynak (range) két fő tulajdonsága (property) VBA-ban van a képletek kapcsán:
1 .Formula
2 .FormulaR1C1

1 .Formula

– ez esetben konkrét képletet adunk meg, fix/bedrótozott hivatkozásokkal
– ún. A1-stílusú hivatkozás (mint Excelben, pl. A5-ös vagy C10:D25-ös tartomány)
– a makrórögzítő nem vesz fel ilyen megoldást (ugyanis a Formula helyett mindig a FormulaR1C1 változatot használja)
– egyetlen cellához adunk hozzá képletet
– lehet relatív vagy abszolút a hivatkozás a „$” jelektől függően – lásd a makrókban lévő kommenteket

Példa #1-01: képlet beírása egy cellába, FIX forrástartománnyal

Ha itt módosítani akarjuk pl. az ÁTLAG függvénynél a hivatkozott tartományt, azt itt kell átírni:

Ez akkor lehet macerás, ha a képlet többször is előfordul, ilyenkor minden egyes előfordulásnál meg kell ejteni a cserét (manuálisan vagy CTRL + H-val).

Példa #1-02: képlet beírása egy cellába, változóba berakott FIX forrástartománnyal

Látható, hogy ebből:

Ez lett:

Vagyis az „A1:A2”-t váltottuk ki a „Tart.Address”-szel. A „Tart.Address” előtti és után kódot pedig hozzáadtuk szövegként, ezért vannak „”-ek között.
Ha itt módosítani akarjuk pl. az ÁTLAG függvénynél a hivatkozott tartományt, azt csak egyszer kell átírni, ahol a „Tart” változóban megadtuk a tartományt:

Példa #2: képlet beírása egy cellába, változóba berakott DINAMIKUS forrástartománnyal

Itt tehát egyáltalán nem kell a kódban módosítani a hivatkozott tartományt a „CurrentRegion” miatt. Magyarán, ha az „A” oszlopban csökken/bővül a kitöltött cellák száma (és nincsenek üres cellák), ez a VBA kód akkor is helyesen fog működni, mégpedig változtatás nélkül.

.Formula két fő alkalmazása:
– VBA-ban beírjuk a képletet
– Excelből kimásoljuk a képletet, majd beillesztjük a makróba, és szükséges esetén módosítjuk. Azt, hogy miket kell(het) átírni, az alábbi példán keresztül szemléltetem:

2 .FormulaR1C1

– a makrórögzítő mindig a FormulaR1C1 változatot használja és különbséget tesz relatív és abszolút hivatkozás között
(Rögzítő indítása -> képletet beírni relatív/abszolút hivatkozással Excelben -> rögzítő leállítása).
– R1C1-nél az R és C jelentése: Row (Sor) és Column (Oszlop)
– R1C1 hivatkozás: sorok és oszlopok számával hivatkozunk egy cellára vagy tartományra.
Pl. a B3-as cella ugye alapból így néz ki:

Itt még B3-at látunk a cella címénél.

Excelben ideiglenesen állítsuk be az R1C1 (magyarul S1O1 azaz Sor-Oszlop) hivatkozást (pl. O365-ben):
Fájl menü -> Beállítások -> Képletek -> pipát betenni az „S1O1 hivatkozási stílus” elé -> OK:

Ezt követően Excelben az oszlopazonosítók is számok lesznek – vagyis kollégák ugratására is használható ez a beállítás 😊
Az iménti B3-as cella most már „S3O2”-ként szerepel (S3O2 angolul R3C2: a 3. sor és 2. oszlop metszéspontja):

Nézzük a hivatkozási típusokat a FormulaR1C1-nél:

2/1. Abszolút hivatkozás:
– korábban volt róla szó, pl. a B3-as (pontosabban $B$3) cella R1C1-es abszolút hivatkozása: R3C2
– ez a hivatkozás másolásnál/áthelyezésnél nem változik, marad R3C2
– itt az R és C után nincs [] (kapcsos zárójel)

2/2. Relatív hivatkozás:
– másolásnál/áthelyezésnél változik
– maradjunk a B3-as példánál -> ez így néz ki relatívan, ha pl. a E1-es-es cellából hivatkozunk rá: R[2]C[-3]

– látható, hogy a kapcsos zárójelekben adjuk meg, merre történjen a relatív elmozdulás
– itt az R[2]C[-3] jelentése: E1-es cellától mozduljon el 2 sort lefelé és 3 oszlopot balra
Elmozdulások:
– „R” utáni [ ]-ben pozitív szám: annyi sort mozduljon lefelé
– „R” utáni [ ]-ben negatív szám: annyi sort mozduljon felfelé
– „C” utáni [ ]-ben pozitív szám: annyi oszlopot mozduljon jobbra
– „C” utáni [ ]-ben negatív szám: annyi oszlopot mozduljon balra

– néhány további példa – itt a K15-ös cellából indulok ki:

Az alábbi kép jobban szemlélteti az előző kód lényegét: a K15-ös cella az aktív és innen hivatkozunk négy különböző cellára (sárga színnel jelölve):

2/3. Vegyes hivatkozás:
– a hivatkozások tartalmaznak abszolút és relatív részeket
– a relatív részeknél az „R” vagy a „C” után [] (kapcsos zárójel) van
– ha a sor relatív hivatkozású, akkor az oszlop abszolút hivatkozású és fordítva:

_

 

II. Változók használata képletekben:
—————————————————————————————————————————————————————————-
Aranyszabály:
Konstans szöveget és a változót „&” jellel kell összefűzni.
Konstans szöveget ilyenkor “”-ek közé kell tenni.

Illusztrálva:

—————————————————————————————————————————————————————————-

1 .Formula-s hivatkozás:

– Példa #1: itt az “=AVERAGE(A1:A2)”-t rakjuk be a Keplet változóba:

– Példa #2: a fentebb ismertetett „Sub Sample02_FormulaProperty_DynamicSourceRange” makró, ahol a Tart változó használtuk. Releváns sor:

2 .FormulaR1C1-es hivatkozás:

Példa:
– megadunk egy range típusú változót (neve: Rng) és feltöltjük a hozzárendelt tartomány (itt: J1:J6) celláit FKERES képlettel
– az FKERES képlet Táblázat argumentuma dinamikus, amit szintén változóval (Szoveg néven) adunk meg

 

_

 

III. Excel és VBA képletek alkalmazása:

1. Excel függvények meghívása VBA-ban (Excelben lévő és VBA-ban is használhatók): Application.WorksheetFunction

– a legtöbb Excelben használt képletet VBA-ban is alkalmazhatjuk, mégpedig az Application.WorksheetFunction segítségével
– ez az „Application” (vagyis itt az Excel) objektum eljárása (method)
Próbáljuk ki: VBA szerkesztőben írjuk be: Application.WorksheetFunction. -> a „.” Lenyomása után megkapjuk a súgónkat, mely függvények érhetők el:

– az alábbi kódban egy sima Szum függvényt használunk:

– a fenti kód mindig értéket ad vissza, függetlenül attól, hogy a Range(“E1”) után „.Value”-t vagy „.Formula”-t alkalmazunk. Ha mégis képletet akarunk látni a cellában, akkor ekképp módosítsunk:

– a cellában lévő képlet megjelenítéséről már volt szó fentebb, a kapcsoló makrók:

Sub Sample01_FormulaProperty_FixedSourceRange_01
Sub Sample01_FormulaProperty_FixedSourceRange_02
Sub Sample02_FormulaProperty_DynamicSourceRange
Sub Sample01_FormulaProperty_FixedSourceRange_03

– Application.WorksheetFunction-nel használható képleteket (methods) lásd itt.

Képletek listája az iménti linkről:

– bizonyos képleteknél az Application.WorksheetFunction-nél a WorksheetFunction elhagyható és akkor is működik, vagyis a VBA saját képletével is használható. Az előző kódnál ez így néz ki:

Ha egy képlet VBA-ban és Excelben is elérhető: használd az utóbbit (Application.WorksheetFunction-ös változatot), mert a beépített Excel formulák gyorsabbak.

2. Excel-es függvények VBA-s változatai:

– vba kezdetűek (de a vba elhagyható) – pl.:

vagy

– bizonyos Excel függvényeket nem tudunk VBA-ban használni. Példának okáért ez a sor nem fog működni:

Hiába van Excelben TODAY (vagyis MA) függvény, ha a fenti kódot lefuttatjuk, hibát kapunk:

„Run-time error ’438’: Object doesn’t support this property or method”

– ebben a kivonatban látható, melyik Excel függvénynek mi a megfelelője VBA-ban (ez nem azt jelenti, hogy az alternatív képlet ugyanúgy számol vagy ugyanazt az eredményt hozza): – forrás

Nézzünk egy példát: ha az Excel előbb említett TODAY (MA) függvényét akarom VBA-ban, akkor az előbbi lista alapján a DATE-et kell alkalmaznom helyette, azaz:

3.csak VBA-ban lévő függvények:

– itt nyilván van átfedés az előző (2.) ponttal
– vba kezdetűek (de a vba elhagyható): lásd példát a 2. pontnál
– link a teljes listához itt. A lényeg képként:

_

 

IV. Felhasználói függvény (UDF = User Defined Function)

Ha
– nem lenne számunkra megfelelő az Excelben elérhető csaknem 500 függvény (link) közül és/vagy
– nem tudjuk, hogy létezik egy vagy több olyan Excel függvény, mely megoldaná a problémánkat és/vagy
– van egy pöpec új függvény (pl. az O365-ben és Excel web-en elérhető XKERES), de az ügyfelek/kollégák egy része régebbi Excelen van – vagyis olyan megoldás kell, ami a korábbi verziókban is működik…
… akkor saját függvényeket is írhatunk VBA-val.

Egy-két keresetlen szó a felhasználói függvényekről:
– angoul UDF-nek (User Defined Function) hívjuk
– modulban tároljuk őket
– eljárás, mely értéket ad vissza. Vagyis olyan működő UDF-et nem tudunk írni, mely pl. a cella háttérszínét változtatja. Ez ugyanis hibára fut:

– az eddig megszokott „Sub” helyett „Function”-nal kezdődik és „End Function”-nal végződik
– általában vannak bemeneti arguementumai, de nem mindig (Excelben sincs mindig argumentum, pl. a MA() függvénynél)
– önmagában nem futtatható
– VBA-ban meghívható egy másik eljárásból vagy közvetlenül használható Excelben (be kell gépelni az „=”-et és a függvény nevét, hasonlóan, mint ha Excelben mondjuk egy SZUM függvényt használnánk)
– ugyanúgy másolható a UDF-es képlet, mint a beépített Excel-es képlet

Nézzünk is pár példát:

1. Argumentum nélküli verzió, mely a Windows-os bejelentkezési felhasználónevet adja vissza

Kis magyarázat:

2. Egy argumentumos változat.

2/1: annak ellenőrzése, hogy egy adott fájl létezik-e (ehhez meg kell adnunk a teljes elérési utat, mely tartalmazza a fájlnevet is):

Magyarázat:

2/2: cella megjegyzés kiíratás másik cellába.
Infó: Office 365-ben a korábban ismert „Megjegyzés” neve „Jegyzet, lásd a kapcsolódó posztomat itt.
Itt argumentumként objektum típusú változót (Cella) adtunk meg.
Kód:

Print screen:

3. Több argumentumos változat: kötelező + opcionális argumentum.

Számos Excel függvény tartalmaz opcionális paramétereket. Például az FKERES, ahol a „Tartományi_keresés”-t nem kötelező megadni (abból is látszik, hogy a megnevezés nem félkövérrel van írva):

Azzal viszont mindig legyünk tisztában, hogy ha az opcionálisnál nem adunk meg értéket, akkor mi fog történni/mi az alapértelmezett érték. Jelen esetben ha a „Tartományi_keresés”-t üresen hagyjuk, akkor az FKERES nem pontos, hanem közelítő egyezést hajt végre.

UDF-nél is tudunk ilyent hozzáadni. Néhány keresetlen szó az opcionális argumentumokról:
– ugyebár nem kötelező megadni a képlet beírásakor
– „Optional” kulcsszó után kell megadni a változó nevét, pl.:

– többet is megadhatunk, ám ezen paramétereknek mindig az utolsóknak kell lenniük a ()-ek között, pl.:

– az „IsMissing” paranccsal tudjuk ellenőrizni, meg lett-e adva a nem kötelező rész
– megadható alapérték is neki, pl.:

– deklarálásuknál lehetőleg semmilyen (akkor szintén variant lesz) vagy variant típust használjunk („IsMissing” parancs ugyanis ezeknél fog működni). Példák:

Nézzünk egy teljes példát:

Némi magyarázat:

Az előbbi kódban látszik, hogy az opcionális paraméter meglétét az „IsMissing” paranccsal ellenőrizzük. Ez akkor működik jól, ha az opcionális paraméter variant típusú, mint ahogy itt is, hiszen a „Kedvezmeny” után nem adtunk meg típust:

4. UDF: hibakeresés (debugging):

A UDF ugye önmagában nem futtatható, vagyis hiába nyomkodjuk bőszen mondjuk az F8-at, nem tudjuk lépésről lépésre futtatani és megnézni mit művel vagy épp megkeresni a hiba okát. Módszerek debugging-ra/re:

4/1: MsgBox hozzáadása a UDF-hez: ugyanis az msgbox megjelenik, így ellenőrizhetjük pl. egy változó értékét -> példa:

4/2: Hívjuk meg a UDF-et egy makróból (Sub procedure):

Kezdjük el lépésenként (F8) futtatni a „Sub UDF_meghivasa” makrót. A „Call” rész után a futtatás átmegy a UDF-be, vagyis innen…

…Ide jutunk:

Innentől pedig F8-cal már végig lehet menni a kódon.

4/3: Töréspont (breakpoint) hozzáadása
Töréspont (breakpoint) hozzáadása a UDF-ben -> Excelben: szerkesztő mód (pl. F2-vel) a UDF cellájában -> Enter -> visszajutunk a VBA szerkesztőbe, ahol az adott sor ki van sárgítva -> most már F8-cal már végig lehet menni a kódon
Vagyis:

_

 

V. Képletek frissítése VBA-ban

Ha egy munkafüzet tele van komplex képletekkel (főleg olyanokkal, melyek indig újrakalkulálódnak) és makrót futtatunk: érdemes megfontolni a számolási beállításoknál a Manuális opciót.
Ez Excelben itt található: Képletek menü -> Számolási beállítások: 3 opcióból választhatunk

Ugyanez a 3 opció VBA-ban: Application.Calculation:

Az Application.Calculation-ben az Application magát az Excelt jelenti, vagyis ez a parancs nem egy adott tartományra/munkalapra/munkafüzetre, hanem az összes megnyitott munkafüzetre vonatkozik.

Ebből adódhatnak problémák, nézzünk is rá két példát:
1. A kalkulációs módot átállítjuk manuálisra (Excel menüből vagy VBA-ban) és nem rakjuk vissza automatikusra: a többi (megnyitott) munkafüzetben sem fogja újraszámolni a képleteket, melyből elég komoly félreértések, érték-eltérések stb. adódhatnak (kivéve, ha második harmadik stb példányt indítunk Excelben, de ebbe inkább most nem mennék bele).
2. Makróban a kód elején átállítjuk manuálisra és a végén vissza automatikusra, DE: a kód hibára fut, megáll és nem jut el addig, ahol visszaállítja automatikusra.

Ha az újraszámolást forszírozni akarjuk Excelben (összes megnyitott munkafüzetben), akkor ezt a parancsot használjuk:

Vagy az Application el is hagyható:

Ezt egyébként nem csak applikáció (Excel) szintjén lehet alkalmazni, hanem munkalap (worksheet) és tartomány (range) szintjén is:

Azoknak, akik még mindig olvassák: remélem hasznos és érthető volt ez a bitangul hosszú poszt, mely a mai világban már-már felér egy kisebb epossszal 🙂

Inspiráció:

– https://www.automateexcel.com/vba/formula-formular1c1#VBA_Formula_Property

– https://www.automateexcel.com/vba/worksheet-functions-in-macro/

– https://support.microsoft.com/en-us/topic/not-all-excel-worksheet-functions-are-supported-as-methods-of-the-application-object-in-visual-basic-for-applications-14f231ca-5378-6bfa-a848-5ecb8d1e1ab0

– https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction

– http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx

– https://wellsr.com/vba/2020/excel/excel-vba-manual-calculation/