Excel konzultáció 3. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22

Excel konzultáció 3. Gyakorlat Dr. Pauler Gábor, egyetemi docens, ev. Adószám: 63673852-3-22 Számlaszám: 50400113-11065546 Telephely: 7666 Pogány, Széchenyi u. 1. Tel: 30/9015-488 E-mail: pauler@t-online.hu

A gyakorlat tartalma 2. Házi feladat ellenőrzése: diagramm forgatás makróból Office Visual Basic makrónyelv 2 • Az objektum-orientált programozás • Kialakulása • Alapfogalmai • Az Excel objektumainak megjelenése a makrókban • Az objektumok szerkesztőfelületei • Az objektumok szintaktikája • Az Excel alapvető objektum hierarchiája • Application objektum • Workbook objektum • Worksheet objektum • Cell objektum • Range objektum • Külső alkalmazások indítása makróból • Makrók indításának módszerei • Forró gombbal • Beágyazott objektumra csatolás • Eseménykezelőből indítás • A Visual Basic Projekt tulajdonságainak és védelmének beállítása • A Visual Basic Editor hibakeresési eszközei • Az Excel cellafüggvények hibakeresési eszközei • Saját Visual Basic függvények használata cellafüggvényként • A makrók memóriaváltozói és a munkalapok közti adatforgalom 3. Házi feladat: számok szöveggé konvertálása

Az objektum-orientált programozás kialakulása Korábban már említettük, hogy az Office Visual Basic objektum-orientált programnyelv (OOP). Hogy az Excel lehetőségeit makrókból teljesen ki tudjuk használni, meg kell ismerkednünk az OOP kialakulásával és fogalmaival: • Egy modern, grafikus felhasználói felületet (Graphic User Inteface, GUI) használó, bonyolult szoftver mögött több százezer vagy millió sornyi forráskód áll, amit ember akkor sem képes egyben áttekinteni, ha szépen van tabulálva és kommenttezve. Még kevésbé képes debugolni. Ezt nevezik spagetti-kód (Spaghetti Code) effektusnak • Ezért a harmadik generációs programnyelvekben bevezették a paraméterezhető eljárások és függvények használatát, ahol a millió sornyi forráskódot több száz vagy ezer függvény hivatkozási hierarchiájába tördelték szét, kezelhető méretű egységekre. • A függvények vagy eljárások lokális változói a függvény lefutása után törlődnek a Stack memóriából, hogy ne foglalják feleslegesen a helyet • Ez viszont gond akkor, ha egy bonyolult szerkezetű tárolót - ami általában valami rekordokból álló, nagy méretű dinamikus tömb (pl. egy reptéri légiforgalmi napló), ami jellemzően a méretesebb Heap memóriában tárolódik – több tucat függvény szolgálna ki (pl. RepülőjáratBeszúrás, Módosítás, Törlés, Archiválás, stb.), akik ezen a tárolón keresztül kommunikálnak egymással. A tároló nyílván nem lehet a lokális változójuk, hanem csak egy globális változó. (Ugyan már tanultuk, hogy a Static opcióval a függvény lokális változóit is benn lehet hagyni a lefutása után a Stack memóriában, de egyrészt a Stack pici, másrészt ilyenkor többé nem lehet felszabadítani az elfoglalt helyet) • Így a legtöbb programnyelv megengedi, hogy egy eljárás vagy függvény akkor is használni tudja a globális változókat, ha nem adtuk át hivatkozással (ByRef) a függvénynek a paraméterlistájában, ezt nevezik implicit/rejtett hivatkozásnak (Implicite/Hidden Reference). Ha ezt nem engedné meg, az általában sok globális változót használó függvények/eljárások paraméterlistája elviselhetetlenül hosszú lenne. • A baj akkor van, ha függvények/eljárások tucatját át szeretném tenni egy másik modul kódjába, újrafelhasználás céljából: egyenként végig kellene bogarászni őket, milyen globális változókat használnak, amelyeket az új helyen is deklarálni kell, mert semmi nem fogja össze a globális változókat és az őket használó függvényeket/eljárásokat. A kód újrafelhasználhatósága így pocsék

Az objektum-orientált programozás alapfogalmai 1 Ezért a 4. generációs programnyelvekben bevezették az objektumokat (Object): • Olyan tároló, amely egy adott témakörhöz (pl. Képernyő-ablak, Médialejátszó, Munkalap stb.) tartozó összetett tárolószerkezetet, rekordot (Record) és a benne lévő mezők (Field) adatait feldolgozó eljárásokat/ függvényeket fogja össze, amiket itt összefoglalólag metódusnak (Method) nevezünk • Az objektum részei még a tulajdonságok (Property), amik átmenetet képeznek a mezők és a metódusok közt: az objektum meghatározott típusú adatmezője, amelynek a kiolvasásakor (Get) és az írásakor (Set) meghatározott metódusok futnak le (Get /Set method). Vannak olyan tulajdonságok, amik csak olvashatók (Read Only). Két dolog miatt jó a tulajdonságok használata: • Úgy tűnik, mintha az objektum intelligensen reagálna a tulajdonságaiban bekövetkező változásokra (pl. a Worksheet.Cells objektum .Interior.Colorindex tulajdonságát átállítom 3-ra, és erre automatikusan lefut egy kód, ami a cella összes háttérpixelét átszínezi pirosra, nem kell foglalkoznom vele, hogy ez hogyan történik) • Az objektum viselkedését úgy tudom befolyásolni, hogy nem kell hosszú paraméterlistával rendelkező, bonyolult, nehezen megjegyezhető függvényeket hívogatnom, hanem 1-1 szám- vagy szöveg értéket állítgatok, csak sok van belőlük • Az objektum részei még az események (Event): olyan logikai (Boolean), igaz/hamis (True/False) értéket felvevő állapotjelzők, amelyek azt jelzik, hogy az objektum valamely metódusa lefutott/nem futott, valamely tulajdonsága egy adott értéket vett fel/nem vett fel, vagy a környezetből az objektumot valami hatás érte. Pl. a felhasználó egérrel kattintott (Click), jobbkattolt (RightClick), duplán kattolt (DoubleClick) rajta, elhúzta (Drag), elengedte (Drop), fölé ment (MouseMove) stb. Az eseményekre eseménykezelő metódusokat (Event Handler) lehet csatolni. • Az események használata akkor hasznos, ha saját kódot szeretnénk futtatni egy objektum működési algoritmusa mellett (pl. azt szeretnénk, hogy egy diagramm újrarajzolásakor (ReDraw, Refresh) automatikusan egy 3. adatsort írjon fel az értékcímkékbe, amire írtunk egy pár soros kódot). Ehhez nem kell ismernünk, és nem is ismerjük azt a több 1000 lépésből álló, bonyolult algoritmust, ahogy az Excel a felhasználó gombnyomásait/ egerezését kiértéktékeli, újraszámol, és elkezdi frissíteni a diagrammokat. Ismerjük ennek a folyamatnak 20-30 kitűntetett pontját – az esemé-nyeket – amikre rá tudjuk kötni a saját kódunkat, a teljes algoritmus ismerete nélkül!

Az objektum-orientált programozás alapfogalmai 2 Az objektumoknak három fontos jellemzője van: • Kívülről zártak (Encapsulation): egy objektum működését a környezete (más objektumok, felhasználó, stb.) csak a nyilvános (Public) tulajdonságainak állításával, kiolavasásával, vagy metódusainak meghívásával tudja befolyásolni, de nem fér hozzá a belső (Private) kódjához és adatmezőihez. Ez két dolog miatt fontos: • Debugolásnál könnyen lehatárolható, hogy egy hiba mely objektumokból eredhet • Így az objektumok könnyebben vihetők át egy másik modulba • Öröklési (Inheritance) kapcsolatban állnak egymással: több bonyolultabb, leszármazott gyermek objektum (Descendant/ Child Object) alkatrészként beágyazva tartalmazhatja (Embedding) ugyanazt az egyszerűbb ős/szülő objektumot (Ascendant/ Parent Object). A gyerek meghívhatja szülő metódusait, fordítva viszont nem. • Pl. Windowsban az űrlap (Frame), munkalap (WorkSheet), vagy rajztábla (DrawingBoard) gyerek objektumok mind tartalmazzák a vászon (Canvas) ősobjektumot, ami metódusaival azt a feladatot látja el, hogy bármely típusú képfájlt megjelenít adott nagyításban egy téglalap alakú képernyőterületen – ez adja be a háttérképeket mindenhol, vagy róla menthető képfájlba a képernyőn látott kép. A gyerek objektumok ehhez adják hozzá a saját funkcionalitásukat (pl. a rajztábla a rajzeszközöket). A több szinten egymásba ágyazódó objektumok öröklési hierarchiát (Inheritance Hierarchy) alkotnak. • Ez látja el a korábban tárgyalt függvény/eljárás hierarchia feladatát a több millió sornyi forráskód tagolásában, csak oly módon, hogy objektumokat könnyű átvinni egy másik modulba. • Polimorfizmus (Polymorphism): a gyerek objektum azonos néven új kódot definiálhat a szülőtől örökölt metódusokhoz, hogy azok a helyi elvárásoknak megfelelően működjenek, minden leszármazott objektumban más formájuk lehet. (pl. a rajztábla objektum saját magán belül újradefiniálja a vászon objektum frissítési metódusát, hogy a rajzeszköz működése közben is frissítsen, ne csak a végén, így látjuk, ahogy a ceruza vonalat húz)

Az objektum-orientált programozás alapfogalmai 3 Mivel egy alkalmazásban egy objektumból nem csak 1db-ra, hanem sok hasonlóra lehet szükség (pl. egy munkalapban sok-sok cella objektum van) az objektumok definiálása két fázisban történik: • Objektum osztály (Class) deklarálása: ez tartalmazza az objektum mezőinek, metódusainak, tulajdonságainak, eseményeinek leírását, de önmagában nem használható, nem fogyaszt memória- és processzor erőforrásokat, csupán egy terv! • Objektum előfordulás (Instance) deklarálása: adott objektum osztály terve alapján létrehoz belőle egy működőképes példányt, ami az erőforrások lefoglalása után már futtatható metódusokkal rendelkezik Egy objektum osztály előfordulásai kollekciót (Collection) alkotnak : ez egy objektumokból álló egydimenziós tömbhöz hasonló indexelt tárolószerkezet. (Pl. munkalap (WorkSheet) objektumok kollekciója egy munkafüzet (WorkBook) objektumba ágyazva: WorkBook.WorkSheets(Index)) A kollekció 4 fontos dologban különbözik a tömbtől: • Egyszerűen bővíthető a végén új elemekkel az .Add metódus segítségével, nem kell hozzá a problematikus Redim Preserve (lásd: Session3), mint a tömbök esetén. Az új elemek egyesével növekvő indexet kapnak (pl. WorkSheets(1), WorkSheets(2)…), ami alapján közvetlenül elérhetők, módosíthatók • Lehet belőle elemeket törölni a memória tényleges felszabadulásával .Delete metódussal, még a tömbből nem. A törölt elem után lévő elemek indexe automatikusan csökken 1-el • Mivel így egy elem indexe a törlések miatt változhat, az elemeket a gyakorlatban inkább a .Name tulajdonságuk alapján érjük el (pl. .WorkSheets(„Munka1”)), amit az .Add metódus automatikusan osztogat (pl. „Munka1”, Munka2”,stb.), de kézzel felülírható (amennyiben az elemek nevei egyediek maradnak) Ezek a különbségek tömb helyett inkább lista (List)-szerűen működő memória-tároló szerkezetet eredményeznek, amely rugalmasabb, de valamivel lassabban működik, mint a tömb. Objektum kollekciók használatára épül az adatbázis-kezelésnek a Lesson1–ben már ismertetett relációs adatbázis melletti ritkább objektum-hierarchia alapú változata: • Mivel egy kollekció objektum-elemei beágyazva tartalmazhatják egyszerűbb objektumok kollekcióját, és azok elemei is beágyazva tartalmazhatnak kollekciókat, az objektum alapú rendszer könnyedén tárol nem fix hosszúságú adatstruktúrákat, még a relációs rendszerben ezt bonyolult tervezéssel és normalizációval lehet elérni • A hátránya az objektum-alapú rendszereknek, hogy nem olyan hatékonyak nagy tömegű adat merevlemezen történő kezelésében, és nincs szabványos lekérdező nyelvük, mint a relációs rendszernél az SQL

3-3.Gyakorló feladat: Kenyérpirító • Az objektum-orientált gondolkodás gyakorlására próbáljuk leírni a Kenyérpirító (Toaster), mint objektum: • Metódusait: név, paraméterlista, visszatérő érték (pl. .FutesKiBe(ByVal Homerseklet As Single) As Boolean), • Tulajdonságait: név, típus (pl. AktualisHomerseklet As Single), • Eseményeit (pl. KenyerKilokes) A megoldás: 3-3GyakorloMegoldas.doc • Szorgalmi feladatként, próbáljuk leírni a Szőke Nő (Blondie) objektumot (pl. Mo- bilTelefon.Szamla.Tullepes, stb.)

katt Excel objektumok megjelenése makrókban 1 katt katt Az Excel Office Visual Basic makrónyelvében az Excel összes-, a telepített ActiveX vezérlők (lásd: Session2) publikus-, valamint saját objektumokat használhatunk. A megnézésükhöz a Visual Basic kódszerkesztő ablakban: • A View|Object browser menüvel,F2-vel,vagy a ( )gomb-ra kattintva megnyithatjuk az objektum nézegetőt: • Ebben baloldalt a létező objektumok( ) listája, jobbol-dalt a tulajdonságaik( ), metódusaik( ), eseményeik ( ) listája látható, alul a kiválasztott elem definíciója • Az objektumlista első eleme a globális deklarációk (Globals, ), ahol az Excel VB függvényeit, eljárásait, konstansait tekinthetjük meg, valamint félkövérrel szedve a saját globális konstansainkat, típusainkat, változóinkat, eljárásainkat, függvényeinket • A nézegető alapban nem tartalmazza az ActiveX vezérlőkhöz tartozó, telepített *.DLL fájlokban tá- rolt típuskönyvtárak (Library) objektumait, de a Tools|References menünél a Reference ablakban bejelölhető, hogy mutassa őket is • A nézegetőben ( )gombra jön fel súgó az adott elemhez (ha van) és ( )gombra vagy Shift+F2 -re tekinthetjük át a forráskódját (Definition) (ha nyílt forráskódú, illetve saját), ( )gombbal pedig keresni tudunk az elemek közt Az alkalmazásunkba épített, aktív objektumokat és tulajdonságaikat a következő módon tekinthetjük át: • A View|Project explorer menüvel, vagy Ctrl+R- re, vagy ( )gombra feljön a projekt tartalomjegy- zék, ahol duplakattolunk a megfelelő modulon • A View|Properties window menüvel, F4-re, ( ) gombra feljön a tulajdonságnézegető ablak, ahol a listából kiválasztjuk a megfelelő objektumot, és láthatjuk a tulajdonságait, valamint ezek aktuális értékeit, és az írhatóakat szerkeszthetjük is katt katt katt katt katt katt katt katt kat- kat katt

Excel objektumok megjelenése makrókban 2 Az objektumok használatának szintaktikai elemei: • Objektum tulajdonság értékének kiolvasása: Valtozo=Objektum.Reszobjektum.Tulajdonsag • Objektum írható tulajdonságának állítása: Objektum.Reszobjektum.Tulajdonsag=Ertek • Objektum metódusának meghívása: • Ha eljárás: Objektum.Reszobjektum.MetodusParam1:=Ertek,Param26:=Ertek • Ha függvény: Valtozo=Objektum.Reszobjektum.Metodus(Param1:=Ertek,Param26:=Ertek) • Objektumokra hivatkozás egyszerűsítése, rövidítése: • With kulcsszóval egy blokkra beállíthatjuk az alapértelmezett objektumot, nem kell mindig kiírni a teljes hivatkozást (az ajánlott kommentezéssel együtt mutatjuk): WithObjektum .Tulajdonsag=Ertek‘Blokkon belül a tulajdonságokra és metódusokra közvetlenül hivatkozhatunk .MetodusParam1:=Ertek MasikObjektum.Tulajdonsag=Ertek‘Természetesen, használhatunk más objektumot is blokkon belül, de teljes hivatkozással ‘A With blokkok egymásba dobozolhatók: With .ReszObjektum‘Of Objektum .Tulajdonsag=Ertek‘Ez most az Objektum.Reszobjektum.Tulajdonsag End With‘Objektum.Reszobjektum End With‘Objektum • Me kulcsszóval bárhol a kódban arra az objektumra tudunk hivatkozni, amihez a kód tartozik, nem kell folyton kiírni a rá vonatkozó teljes hivatkozást: Pl. a C:\Proba.xls fájl Module1 moduljában a [C:\Proba.xls].Module1.Name és a Me.Name egyenértékűen használható • Saját objektum létrehozása valamely létező objektum kollekció új elemére rádefiniálással: DimAzEnUjMunkalapomAs Object‘Általános objektum típusa van a változónak, így még nem fogyaszt erőforrásokat, de nem is használható semmire SetAzEnUjMunkalapom=WorkSheets.AddName:=„UjMunkalap”‘Set-tel rádefiniálom (lásd: Session3) egy újonnan létrehozott munkalapra Ettől kezdve AzEnUjMunkalapom.Cells(1,1) egyenértékűen használható a WorkSheets(„UjMunkalap”).Cells(1,1)-el, csak gyorsabban működik nála

Excel objektumok megjelenése makrókban 3 • Saját objektum létrehozása létező Excel, vagy bármely telepített, és a Tools|References ben meghívott ActiveX vezérlő létező objektum osztályából: DimAzEnUjObjektumomAs NewObjektumOsztaly • A New kulcsszóval egy lépésben működő, a szükséges erőforrásokat lefoglaló új objektumot deklarálunk, nincs szükség Set utasítás kiadására • Ezt akkor használjuk, ha pl. ActiveX-es médialejátszót, internetes eszközöket, stb. szeretnénk kódból létrehozni és működtetni az alkalmazásunkban • Saját objektum osztály (User Defined Class) létrehozása: akkor használjuk, ha bonyolultabb programozási feladatokat igazán struktúráltan szeretnénk megoldani. A saját osztály kifejlesztésébe feketetett munka a kód újrafelhasználhatóságában térül meg. • 1db saját fejlesztésű objektum osztályt 1db speciális fajtájú modulban, a Class Module-ban írhatunk le, amit Insert|Class module menüvel tudunk létrehozni, majd a Project tartalomjegyzékben duplán kattintva a Alkalma- zas Nev.xls| Class modules| Class1 elemen, szerkeszthetjük a tartalmát • Az osztályunk metódusait ugyanúgy írjuk le, mint egy sima modul eljárásait és függvényeit. Vigyázzunk rá, hogy csak a publikusra (Public) állított eljárások és függvények hívhatók kívülről metódus- ként (ez az alapbeállítás)! • Osztályunk tulajdonságait property eljárások(Property Procedures) segítségével írhatjuk le. Emlékezzünk rá, hogy a tulajdonság az objektum olyan adatmezője amelynek módosítására/ kiolvasására adott programkód fut le, ami a bevitt értéket ellenőrzi,és ha helyes, reagál rá (pl. egy cella háttérszínének pirosra állításakor a színezést végző kód). Ezért 1db tulajdonság definiálásához a class modulban kell: • 1db globális, de nem publikus változó, ami a tulajdonság értékét tárolja, a class modulban minden eljárás láthatja, de kívülről nem írható és olvasható: Private DimRejtettTulajd1AsTulajd1Tipus • Mindig kell 2 db property eljárás,amiken keresztül a külvilág a tulajdonsághoz fér: • 1db kiolvasáskor lefutó eljárás: Public Property GetTulajd1(ByValPar1AsParTip) AsTulajd1Tipus EztCsinaldOlvasasra‘Itt írjuk le, ha kell valamit kiolvasáskor csinálni Tulajd1=RejtettTulajd1 End Property‘Get Tulajd1 katt kat- kat

Excel objektumok megjelenése makrókban 4 • 1db módosításkor lefutó eljárás, amelynek két alváltozata lehetséges: • Ha a tulajdonságnak valamilyen kifejezéssel számított értéket adnak: Public Property LetTulajd1(ByValPar1AsParTip) ErtekEllenorzes‘Itt írjuk le, ha kell ellenőrzést csinálni RejtettTulajd1= Par1 End Property‘Let Tulajd1 • Ha a tulajdonság is objektum típusú (beágyazott objektum) és Set-tel rádefiniálják valamilyen objektumra: Public Property SetTulajd1(ByValPar1AsParTip, ByRef Obj1 As Object) Obj1Ellenorzes‘Itt írjuk le, ha kell ellenőrzést csinálni SetRejtettTulajd1= Obj1 End Property‘Set Tulajd1 • A Property Get, illetve Property Let/Set eljáráspár paraméterlistája (ha van) meg kell, hogy egyezzen egymással, kivéve, hogy a Get-nél van visszatérő érték, a Set-nél pedig van plusz egy objektum, amire definiálunk • Osztályunkhoz eseményeket is definiálhatunk. Emlékezzünk rá, hogy ezek logikai (Boolean) típusú jelző (Flag) értékek, amelyek azt jelzik, hogy az osztályhoz tartozó bonyolult algoritmus egy részlete lefutott-e vagy nem, és arra szolgálnak, hogy kívülről más kódok lefutását szinkronizálni lehessen az osztályunk működésével. • Így az esemény deklarálása nagyon hasonlít egy eljárás deklarálásához: Public EventEsem1(ByValPar1AsParTip) IttCsinalValamit End Event‘Esem1 • Az eseményhez tartozó eljárás így hívható meg kódból (ez igaz (True)-ra állítja az eseményjelző értékét): RaiseEventEsem1(Par1) • Osztályunk metódusainak kódjában gyakran van szükség olyan típusú változókra, ahol néhány számkódhoz nevek társulnak (pl.1:férfi,2:nő vagy 0:fekete,1:fehér stb.), ezt nevezzük felsorolt (Enumerated) típusnak és a következőképpen adjuk meg: EnumTipusNev ErtekNev1=Ertek1 ErtekNev2=Ertek2 End Enum

Excel objektumok megjelenése makrókban 5 Az Excel alapvető objektum hierarchiája: • Az Excel legösszetettebb, legmagasabb szintű leszármazott objektuma az objektum hierarchiában az alkalmazás (Application). Ennek a következő részei érdekesek makró-programozás szempontjából: • .Calculation = xlManual/xlAutomatic tulajdonság: az Excel automatikusan számolja a cellafüggvényeket változás esetén, vagy csak felhasználói kérésre (F9 gomb), illetve kódból a .Calculate metódus meghívására. Olyan makróban, ahol tömegesen írunk munkalap cellákba, állítsuk először manuálisra, majd a végén automatikusra, különben minden egyes cella után újraszámol és baromi lassú lesz: ‘Cella számolás-mentes blokk nyit Application.Calculation=xlManual ForI=1To100 ActiveSheet.Cells(I,1).Value=1 NextI 'Cella számolás-mentes blokk zár Application.Calculation=xlAutomatic‘Csak erre fog számolni a végén! • .StatusBar = String tulajdonság: mi legyen a státuszsor szövege az ablak alján • .WorkSheetFunction alobjektum: ezen keresztül hívhatjuk makrókban az Excel cellafüggvényeit, mint arról már szó volt (pl. Application.WorkSheetFunction. CountA(Range), ha nincs elnevezési konfliktus Visual Basic vagy felhasználói függvénnyel, akkor kihagyható: Application.CountA(Range)) • .Save metódus: a megnyitott munkafüzetek mentése. Ezt a következő módon használjuk (általában arra, hogy a makró által munkalapra írt outputokat automatikusan belementsük a munkafüzetbe) On Error Resume Next‘Ha hiba van, lépjen a következő utasításra Kill"Resume.xlw"‘Törölje le a biztonsági mentés *.xlw fájlt, ha létezik Application.Save‘Mentse a munkafüzeteket • Az alkalmazás több megnyitott munkafüzet (WorkBook) objektum kollekcióját tartalmazhatja (WorkBooks(„FajlNev.xls”)), ActiveWorkbook a neve annak a munkafüzetnek, amit az Excel éppen mutat, fókuszál (OnFocus). Ha egy utasításban nem határozzuk meg a munkafüzetet, akkor automatikusan erre értődik

Excel objektumok megjelenése makrókban 6 • Egy munkafüzet több munkalap (WorkSheet) objektum kollekcióját tartalmazhatja (WorkSheets(„MunkalapNev”)), az éppen mutatott munkalap az ActiveWorkbook, ha a munkalap fülön ki van jelölve egy/több munkalap, akkor ezek lesznek a Selection objektum aktuális tartalma • Egy munkalap több cella (Cell) objektum kollekcióját tartalmazza, amelyen belül egy cellára a sora/oszlopa megadásával hivatkozhatok, többféle módon: • .Cells(SorIndex,OszlopIndex): sor/oszlop inedexszel történő hivatkozás, amely borzasztó gyors, az indexek lehetnek számítások eredményei (pl. ciklusváltozóval), tehát paraméterezhető. Hátránya, hogy a sor és oszlop sorrendje fordított a közkeletűen használt „A1” stílusú hivatkozáshoz képest, és az ABC betűit át kell számolni sorszámra. Mégis, előnyei miatt makró programozáskor ezt használjuk! • .(Cells(„1”,”A”): sor/oszlop hivatkozás nevekkel: hátránya, hogy nagyon nehezen paraméterezhető, lassabb, fordítva van, a hivatkozási stílus megváltoztatásával (lásd: Lesson2) érvénytelenné válik, ne használjuk! • [A1]: egyszerűsített hivatkozás, ugyanaz igaz rá, mint az előzőre A cella objektum következő részeit használjuk legyakrabban makró programozásban: • .Value = Variant tulajdonság: a cella kiszámított vagy konstansként tárolt értéke. Mivel Variant típusú, értéket adhat neki bármilyen primitív tipusú (lásd: Session3) Visual Basic változó, visszafele viszont ez nem igaz: ha a cella értékét olyan programváltozónak próbáljuk átadni, amibe nem tud automatikusan konvertálni, „Type Mismatch” hibajelzést kapunk! Ennek elkerülésére 3 megoldás van: • A, A programváltozókat is Variantként definiáljuk, és IsNumber, IsDate, stb. függvényekkel tesztelgetjük, hogy használható értéket kaptak-e. De a Variant-ok sok helyet fogyasztanak és kezelésük lassú, ezért minden változót Variantként definiálni óriási pazarlás, csak indokolt esetben használjuk! • B,String változóba szedünk be cellából mindent, és CInt, CDouble, stb. konverziós függvényekkel próbáljuk adott primitív tipusú változóba konvertálni az értéket egy On Error Goto hibakezelő shell belsejében. Ez gyors és hatékony megoldás, de sok kézi kódolással jár • C, A munkalapon a cella Adatok|Érvényesítés (Data|Validation) formázásaival (lásd: Session2) vagy kódból a .Validation tulajdonságnál pontosan meghatározzuk, milyen értéket vehet fel a cella, így azután biztosan tudja konvertálni a primitív típusú programváltozóba. Ez a leggyorsabb, legegyszerűbb és semmit nem kell hozzá kézzel kódolni!

Excel objektumok megjelenése makrókban 7 • .Formula = String tulajdonság: a cellaképlet stringként, string manipulációs függvényekkel kódból is szerkeszthető, feltéve, hogy utána az Excel értelmezni tudja • .Border: határolók, .Interior: belterület, .MergeCells: cellaegyesítés, .NumberFormat: számformátum, stb. tulajdonságokkal a cella már jól ismert stílusformázásai írhatók/olvashatók kódból, konkrét szintaxisukat könnyen kideríthetjük a makró rögzítővel • Több cellából összeállított kollekció a cellatartomány (Range) objektum, ami lehet: • 1db cella: pl. ActiveSheet.Cells(1,1)ActiveSheet.Range(„A1”) Tulajdonképpen olyan, hogy cella objektum, önmagában nem létezik, csak didaktikai okok miatt használjuk! Egy cella az valójában egy 1 cellából álló Range, és a cellánál felsorolt tulajdonságok valójában a Range tulajdonságai! • 1 teljes oszlop: ActiveSheet.Range(„A”)ActiveSheet.Columns(„A”) ActiveSheet.Columns(1), főleg cellaformázási utasításokban használjuk • 1 teljes sor: ActiveSheet.Range(„1”)ActiveSheet.Rows(„1”) ActiveSheet.Rows(1), főleg cellaformázási utasításokban használjuk • 1 téglalap alakú munkalapterület: ActiveSheet.Range(„A1:B3”) ActiveSheet.Range(ActiveSheet.Cells(1,1),ActiveSheet.Cells(3,2)), általában olyan függvények inputjait adjuk meg így, amik több cellából számolnak valamit: Count, CountA, Min, Max, Sum, stb. • Bármely (akár dinamikus) nevesített cellatartomány (lásd: Lesson2): a fenti szenvedgetés helyett sokkal elegánsabb a munkalapon, felhasználói felületről csinálni egy dinamikus nevesített cellatartományt Ofszet (Offset)-tel, ami mindig pont annyi adatot szed fel, amennyit a felhasználó épp bevisz, és ezek után egy pici kis névvel hivatkozhatunk az egészre: ActiveSheet.Range(„DinamTartomNev”). • Ezen belül azután a .Cells(I, J), .Rows(I), .Columns(J)-vel navigálhatunk (a sor/oszlop indexek a tartományon belül relatívak!!!) • .Rows.Count, .Columns.Count adja meg az aktuális sor/oszlopszámot, ami I, J ciklusváltozók értékhatára lehet: For I = 1To .Rows.Count • Vagy For Each Cell/Row/Column In ActiveSheet.Range(„DinamTartomNev”)-tipusú ciklusoknál még az értékhatárokkal sem kell foglalkozni

Excel objektumok megjelenése makrókban 8 • A munkalapterületre rádefiniált memóriaváltozó: ha egy Range típusú Visual Basic változót rádefiniálunk egy munkalap cellatartományra, még a fentinél is egyszerűbben és gyorsabban tudunk dolgozni: DimDinamTartomAs Range ‘Üres, működésképtelen Range változó deklarálása Set DinamTartom=ActiveSheet.Range(„DinamTartNev”)‘Rádefinálom WithDinamTartom .Cells(1,1).Value =100‘Borzasztó sebesen dolgozok vele End With‘DinamTartom • Több téglalap alakú munkalapterület: perverzeknek eláruljuk, hogy a Range objektum más Range objektumokat tartalmazhat önmagába ágyazva az .Areas nevű kollekciójában. Ezen a módon képes össze nem függő cellatartományokat ábrázolni, mint amilyeneket pl. kézzel a munkalapon Ctrl+Cellakattintás-sal kapunk. Kódból ez a következőképp megy: ActiveSheet.Range(„A1:B2;D4:E5”) Tartomany = Union(ActiveSheet.Range(„A1:B2”),ActiveSheet.Range(„D4:E5”)) • Teljes munkalap: maga a WorkSheet is a Range leszármazottja Külső alkalmazások indítása makróból: külső program makróból a Shell utasítással indítható: Shell „Meghajt:\Ut\Fajlnev.exe -Kapcsolo1 -Kapcsolo2”, vbNormal/ModalFocus • A Normal/Modal beállítás azt jelenti, hogy a program normális ablakban fusson, vagy modálisban, amikor a felhasználó nem férhet hozzá más ablakokhoz, amíg a futót be nem sikerült zárnia Szándékos Excel-lebutítás: igen zavaró hiba, hogy a Shell csak abszolút elé- rési útról tudja meghívni a külső programokat, ezért az alkalmazás másik gépre átvitelekor kézzel kell állítani az utat, vagy telepítőt kell csinálni Makró felhasználói indításának módozatai: • Forró gombbal: a makrórögzítőnél már láttuk, hogy forró gomb rendelhető benne a makrókhoz. Ez az Excel ablak Eszközök|Makró|Makrók|Egyebek (Tools|Macro| Macros|Options) menüben módosítható. A forró gombot azonban csak tapasztalt felhasználó és kis futási idő igényű makró esetén javasoljuk indításra. Hátránya az is, hogy Ctrl+Shift+Betű tipusú forrógomb összeakadhat más szoftverek forró gombjaival. • Beágyazott objektumra (Embedded Object) csatolva: az Excelben lehetőség van makrót rendelni bármely diagrammhoz (Chart), vagy Beszúrás|Objektum|Fájlból (Insert|Ob-ject|From file) menüvel munkalapba ágyazott Word, Pdf, kép, hang, mozi, stb. objektum-hoz, amely csak és kizárólag rákattintáskor fut le. Ez a következőképpen működik:

3-4.Gyakorló feladat: saját objektum definiálása • Készítsen egy class modulban egy objektumot, ami egy munkalapon kő-olló-papír játékot játszik a felhasználóval és számolja az eredményeket! • A megoldás: 3-4GyakorloMegoldas.xls

Makrók felhasználói indításának módozatai 1: beágyazott objektumra csatolás katt húz • Először is, rendelkeznünk kell egy előre megírt, lefordított, futtatható makróval (Macro): ez bár- mely nem class modul bármely bemenő para-méterek nélküli eljárása lehet. De ez természe-tesen a belsejében már meghívhat paramétere- zett eljárásokat/függvényeket – olyan eljárásról van szó, amely egy eljárás/függvény hierarchia csúcsán csücsül, és egy hosszabb folyamatot indít el. • Ezután szükségünk van 1 beágyazott objektumra: • Ez leggyakrabban a Nézet|Eszközök|Űrlap (View| Tools|Forms) menüből elérhető nyomó-gomb (PushButton), amit egérrel a munkala-pon kihúzva, automatikusan elindul a makró-hozzárendelés (Assign Macro) dialógus • Vagy lehet diagramm/kép/hang/szöveg, amihez a jobbkattra felugró menüből adhatunk makrót • A makró hozzárendelése után a beágyazott objek-tum viselkedése megváltozik: • Sima kattintásra, indítja a makrót • Ctrl+kattra az objektum szerkesztő módba lép: • Ahol Dupla kattra az objektum formázható (pl. diagramm), vagy elindul a lejátszása (szöveg, kép, hang, mozi) • Jobb kattra az előugró menüben megvál-toztatható a hozzá csatolt makró • A makró indításnak ez a módszere egyszerű • Hátránya viszont, hogy csak rákattintásra történő makró indítás érhető el vele, és ez sokszor nem megfelelő: pl. ha egy diagramm címkéibe írunk fel makróval plusz adatsorokat. akkor ez fusson le már a munkalap számolásakor, ne kelljen külön ezért a diagrammokra kattintani, amit a felhasználó elfelejthet és teljesen rossz adatokat fog látni!!! • Ez a hozzárendelés VB kódból nem programozható katt Ctrl katt jobb katt katt Ctrl katt katt jobb katt katt Ctrl katt jobb katt katt

Makrók felhasználói indításának módozatai 2: eseménykezelők katt katt katt katt húz • A makró indításának sokkal kifinomultabb módsze-re, ha valamely objektum vagy ActiveX vezérlő (lásd: Session2) eseménykezelője (Event Handler) indítja: • Például a Nézet|Eszközök|Vezérlők (View|Tools| Controls) menüből egérrel húzzunk ki a munkalapra egy parancsgomb (CommandButton) vezérlőt • Ilyenkor az Excel automatikusan a vezérlők tervezési módjába (Design Mode) vált ( ): nem futnak, hanem a tulajdonságaik állíthatók ( ) gombbal: • BackColor: gomb háttérszín (ajánlott szürke) • Caption: a gomb szövege • ForeColor: betűszín (ajánlott kék) • Font: betűtípus (ajánlott Arial Narrow) • Locked=True: zárolás, a felhasználó tudja hasz-nálni lezárt munkalapon, de szerkeszteni nem!!! • Picture: a gomb képe, ha van, bármely képfájl • PicturePosition: hol legyen a kép a gombon Szándékos Excel-lebutítás: a gomb margója nem állítható, ezért pici gombokat nem lehet vele jól csi-nálni, használjunk helyette Űrlapok|Nyomógombot • Ha a vezérlő formázása kész, nyomjuk meg a ( ) gombot, ami eseménykezelő kódot készít neki: • Az Excel nemcsak modulokban tárolhat VB kód-ot, hanem a munkafüzet/lapok is megjelennek a VB Projekt tartalomjegyzékben, mint kódtárolók, ahova eseménykezelő eljárások írhatók. Lehet őket modulokba is írni, de így célszerűbb, mert a munkalap másik munkafüzetbe másolásakor viszi magával az objektumai eseménykezelőit! • A kódszerkesztőben a bal legördülő listából a munkalap objektuma (pl. CommandButton1), a jobból az eseménye (pl. kattintás Click) választ-ható ki, amire a kezelőt írjuk, erre ad egy fejlécet • Majd a kezelő belsejében hívjuk a kívánt makrót katt katt katt kat kat katt katt katt katt

Makrók felhasználói indításának módozatai 3: eseménykezelők • Végezetül a ( ) gombbal futtatási módba (Run Mode) kapcsoljuk a vezérlőket, és működnek • A makróindításnak az eseménykezelős mód-szere jóval bonyolultabb, mint az előző  Viszont sokkal rugalmasabban indíthatók vele makrók. Ugyanis nemcsak kattintásra csatol-ható makró, hanem a munkalapba ágyazott objektumok és vezérlők bármely eseményére 3-4.PÉLDA: a 2-1SzorgalmiMegoldas.xls fájlban az volt a feladat, hogy az egyik input cella értékének az állítása makróból frissítse a többi input cella validációjának paraméterezendő hibaüzenetét (mert az egyik cella inputja a másik cella inputjának az értékhatárát befo-lyásolta). Rém kellemetlen lenne, ha a felhasználónak minden input után kattintani kellene valahova a hibaüzenet-szövegek frissítéséhez. Ehelyett a makró kódját beírtuk a munkalap (WorkSheet) újraszámolás (Calculate) eseménykezelőjébe. Ide célszerű írni a diagrammokat módosító kódot is, ha van. A Visual Basic Projekt tulajdonságainak és védel-mének beállítása: • Ha nem akarjuk, hogy a felhasználó megnéz-hesse és módosíthassa a VB kódjainkat, viszont futtatni tudja őket (többnyire ez a helyzet), akkor a fejlesztés végén a VB Editor ablak Tools|Project properties menüjében: • A General fülnél megadhatjuk a projekt leíró adatait • Protection fülnél betekintési jelszót adhatunk meg egyszerre az összes class és nem class modulra, valamunt munkafüzet/ lap kódra kat kat katt katt katt katt katt

katt katt Debugolás a Visual Basic Editorban katt katt katt katt katt A VB Editor hibakeresésének (Debug) 3 szintje van: • A kódszerkesztő egyszerű szintaktikai ellenőrzése • Debug|Compile menüvel az összetett szintaktikai és fordítási hibák ellenőrzése • Futtatás Run|Run menüvel vagy ( ) gombbal. Fu-tás közbeni hiba (Runtime Error) esetén az Excel hibakereső módba (Debug Mode) vált, besárgítva a sort, ahol elakadt. Ekkor a Debug menüben: • Toggle breakpointtal, vagy a bal margóra kattintva töréspontot (BreakPoint) definiálha-tunk, ahol a futás majd megáll, hogy megfi-gyelhessük a kód köztes állapotát: • Ha az egeret bármely változó fölé húzzuk, kiírja az aktuális értékét • Egy változót a kódban Shift+húzással kijelöl-ve, majd Quick Watch menüvel vagy Shift+ F9-el figyelőt (Watch) definiálunk, ami beke- rül a figyelő ablakba (Watches) • Bonyolultabb, több változóból álló kifejezések figyeléséhez Add Watch|Expression menü-vel definiálunk figyelőt • Ezután ( )gombbal újraindíthatjuk a futást • Vagy Step Into-val léphetünk kézzel a következő utasításra, akkor is ha az egy meghívott eljárásban/függvényben van • Vagy Step Over-rel lépünk azonos blokkon belül a következő utasításra (tehát nem lép bele a meghívott eljárások/függvények kódjába) • Vagy Step Out-tal kilépünk abba az eljárásba/ függvénybe, amely az aktuálisat meghívta • Ha megtaláltuk a hibát, ( )gombbal kiléphe-tünk debug módból lezárva a programfutást, és nekikezdhetünk a kód javításának katt katt katt katt katt shift húz húz

Debug tevékenység munkalapokon Munkalapon keresztüli figyelés: • A VB Editor ablak Debug eszközeinek gyenge pontja, hogy összetett adatszerkezetek (több dimenziós tömbök, rekordokból álló tömbök,rekordba ágyazott rekordok, stb.) a figyelőben csak nehézkesen tekinthetők át. • Ezért alternatív stratégiaként kihasználhatjuk az Excel előnyeit: a fejlesztési fázisban pl. 2 dimenziós memóriatömbök helyett tároljuk az adatokat munkalap cellákban, tömbként használ-va a munkalapot. Ez persze 100-szor lassabb és több erőforrást fogyaszt, mint a memóriatömb, viszont a bonyolult hibák könnyebben észreve-hetők benne. A hibák kijavítása után a munkalapot visszacserélhetjük memóriatömbre Cellafüggvények debugolása: • A cellafüggvények a VB Editortól különálló, önálló debug eszközökkel rendelkeznek. Ezek használatára jóval ritkábban van szükség, mint a VB kód debugolására, feltéve, hogy betartjuk a képletek írására és a nevesített cellatartományok használatára vonatkozó, a Lesson2–ben tárgyalt ajánlásokat. A cellaképletek debug eszközei az Excel ablak Eszközök|Képletvizsgálat (Tools|Analyze formula) menüből érhetünk el: • Elődök/utódok mutatása (Show Precedents/Descendants): a kijelölt, képletet tartalmazó cellá(k)ra bekapcsolja, hogy hivatkozási nyilakat ( ) húzzon az hivatkozott inputokból a cellába /illetve a cellából a rá hivatko- zókba. A nyilak a következő munkalap frissítésig maradnak fent: • A Képletkiértékelő (Formula Analyzer) egy varázslót jelenít meg, ahol a Kiérté-kelés (Evaluate) gombbal a képlet részeredményeit láthatjuk egymásba dobozolt részenként, Belelépéssel (Step Into) beugorhatunk a beágyazott függvénybe, Visszalépéssel (Step Out) kiugorhatunk belőle: • A Figyelőablakban (Watch Window) a Figyelőpont elhelyezése (Add Watch) gombbal rakhatjuk a mega- dott cellá(k) értékét a figyeltek közé katt katt katt katt katt katt katt

Saját Visual Basic függvények használata cellafüggvényként 1 Az Excelnek megvan az a kellemes tulajdonsága, hogy bármely VB modulban definiált bármely saját függvény (User Defined Function) felhasználható cellafüggvényként, ha: • Publikusnak (Public) van deklarálva • Paraméter-listájában az összetett változók mind Range típusúak. Tehát, nincs a paraméterlistájában tömb, vagy rekord, a több, indexelt értékből álló paraméterek mind cellatartományként adódnak át neki (az egyedi értékű paraméterek meg bármely, a cellák által kezelt típusként, vagy Variantként), érték (ByVal) vagy referencia (ByRef) szerint • A visszatérő értéke lehet bármely cella által tárolt típus vagy Variant • A függvény csak visszatérő értékén keresztül ad vissza outputot a munkalapnak, vagyis: • Nem próbál referenciaként (ByRef) átadott cellatartomány paraméterbe visszaírni • Nem próbál lokális vagy globális VB Range változót bármely munkalapra definiálni, vagy bármely munkalap értékét módosítani Az ilyen bűnös dolgokat művelő függvények VB-ből meghívva vidáman, hiba nélkül lefutnak! Cellafüggvényként meghívva azonban #ÉRTÉK! (#VALUE!) cella-hibát eredményeznek, mert az Excel cella-kiértékelési algoritmusa nem viseli el, ha egy függvény öntevékenyen a saját eredmény-celláján kívül máshova is írni akar Szándékos Excel-lebutítás: Sajnos, emiatt letiltották, hogy több értéket visz- szaadó saját VB függvényt tömbképletként meg lehessen hívni, pedig a több értéket szépen vissza tudná adni Range típusú visszatérő értékben. 3-5.PÉLDA: Tegyük fel, hogy egy munkalapon az A oszlopban sok egymás alatti cellában +/- számok vannak. Ha B oszlopba tömbképletként beírjuk az abszolútérték (Abs()) függvényt az A oszlop celláira hivatkozva, akkor szép intelligensen felismeri, hogy bár az Abs() egy bemenő paraméterű függvény, most egy cellatömböt kapott, ezért cellatömböt is hajlandó visszadni outputként. Vagyis 1db függvénnyel megcsinálhatjuk ugyanazt a műveletet egy teljes cellatartományra, ami sokkal gyorsabb mint külön függvényekkel. Saját gyártmányú függvényeknél ugyanezt semmiképp nem hajlandó megtenni Mindezen korlátozások ellenére, a saját gyártmányú cellafüggvények nagy hasznunkra lesznek a Session3–ban említett kód multiplikációs probléma feloldásában, mert egyfajta „hintapolitikát” (Swinger Strategy) folytatunk a cellafüggvényekkel, illetve a Visual Basic-ben történő programozás közt, a könnyebb kódolás érdekében: • Az egyszerűbb dolgokra nyílván nem írunk makrót, hanem megoldjuk cellafüggvényekkel, mert ezeket kevesebb programozási tapasztalattal is sokkal könnyebb debugolni • Ha az algoritmus elbonyolódik annyira, hogy többszörös logikai elágazások lesznek benne, akkor nem küzdünk megtöbbszörözött cellaképlet-szörnyek előállításával… ={Abs(A1:A5}

Saját Visual Basic függvények használata cellafüggvényként 2 • Ehelyett, elegánsan átváltunk Visual Basic saját függvény írására, amit majd cellafüggvényként használunk. Ezzel elkerüljük a kód-többszöröződést, mert itt használhatunk a részeredmények tárolására lokális változókat. • A Visual Basic kódon belül azonban a rutinszerűen ismétlődő részfeladatokat (maximum/minimumkeresés, maximum/minimum érték indexének kiszámítása, összegzés, átlag, szórás, egyéb statisztikák számolása) NEM kézzel, VB-ben programozzuk - mert ez programozói gyakorlatot igényel, és hibalehetőségek forrása – hanem meghívjuk VB-ből a megfelelő cellafüggvényt rá: Application.WorkSheet-Function.Max(Range) /.Min(Range) /.Match(KeresésiÉrték, KeresésiTartomány, 0) /.Average(Range) /.Stdev(Range) stb. • Végül, nem Visual Basic-ben próbáljuk egymásba ágyazott ciklusokkal összeügyeskedni, hogy ugyanazt a fajta számolást több munkalapcellában is csinálja meg, hanem jól megírunk és letesztelünk 1db saját cellafüggvényt, majd adattáblát alkalmazunk rá (lásd: Lesson2), hogy egy teljes függvénytáblára kiszámolja az értékét. Lássunk egy példát: 3-6.PÉLDA: a Lesson2–ben már bemutattuk a 2-1HivatkozasMinta.xls fájlban, hogyan oldja meg a „vegyük két szám szorzatát, és ha <25-nél, akkor vonjuk le 1-ből, egyébként adjuk hozzá”-függvénytáblát cellaképlettel és adattáblával: Látható, hogy a SorInput*OszlInput kódrészlet kellemetlenül multiplikálódik a cellakép-letben (3× kell leírni), annak ellenére, hogy a példa ilyen kis primitív. Ez egyetlen szorzás-nál nem nagy ügy, de ha valami bonyolult dolog lenne, ugyanígy 3× kellene leírni… • Most nézzük ugyanezt saját gyártmányú cellafüggvénnyel: =HA(SorInput*OszlInput<25;1-SorInput*OszlInput;1+SorInput*OszlInput) {=TÁBLA(A73;B72)}

'Function for special product of two integers 'Függvény két szám különös szorzatára PublicFunction MyTwistProd(X, Y AsVariant)As Variant Dim Prod As Double'Temp variable for product|Szorzatváltozó 'Compute product calling cell function once 'A szorzat kiszámítása cellafüggvény egyszeri meghívásával Prod = Application.WorksheetFunction.Product(X, Y) If Prod < 25 Then'If product<25|Ha szorzat<25 MyTwistProd = 1 - Prod 'Subtract from 1|Vond le 1-ből Else'If product<25|Ha szorzat<25 MyTwistProd = 1 + Prod 'Add to 1|Add hozzá 1-hez End If'If product<25|Ha szorzat<25 End Function'MyTwistProd Saját Visual Basic függvények használata cellafüggvényként 3 Először is, a Modul1-ben meg-írjuk a MyTwistProd nevű függvényt, ami elvégzi a kívánt számításokat: • Figyeljük meg, hogy a szor-zás háromszori elvégzését úgy kerüljük el, hogy deklarálunk egy Prod nevű lokális változót,amiben eltároljuk az eredményét • Tegyük fel hogy a szorzás olyan bonyolult részfeladat, hogy azt már nem tudjuk leprogramozni, hanem cellafüggvényt hívunk meg rá (Application.Worksheet-Function.Product) • Ezután elrendezzük, hogy mit kell kiszámolni az algoritmus különböző logikai ágain, és visszadjuk az eredményt A második lépésben, a munka-lapon előállítjuk az adat-táblát saját =MyTwistProd(X, Y) cellafüggvényünkre alapozva  Ezen a módon minimális makró programozási ismeretek felhasználásával nagyon hatékony, gyorsan számoló alkalmazást hoztunk létre =MyTwistProd(MySorInput;MyOszlInput) {=TÁBLA(A87;B86)}

A makrók memóriaváltozói és a munkalapok közti adatforgalom 1 • Az olyan alkalmazásokban, ahol a makró nagy tömegű adatokon végez számítást, komoly adatmennyiségeket kell átmozgatni a munkalapok és a makró memória változói közt az input és az output során. Lássuk ennek módszereit: A makró inputja a munkalapról: • A makró dolgozhat közvetlenül valamely cellatartományra definiált Range változóból: DimMunkaTartomAs Range SetMunkaTartom=ActiveSheet.Range(„InputTabla”) Valami=MunkaTartom.Cells(1,1) • Ez egyszerű megoldás, de az a hátránya, hogy ha a makró sokszor olvassa ugyanazt az adatot, ez elég lassú ahhoz képest, ha memóriatömbből dolgozhatna. Ezért sokszor megéri átmásolni a cellatartomány tartalmát egy kétdimenziós tömb memóriaváltozóba: DimMemBuffer() As Double‘Dinamikus tömb DimI, JAs Long‘Ciklusváltozók WithMunkaTartom ‘A tömb legyen akkora, mint a cellatartomány: RedimMemBuffer(1To.Rows.Count,1To.Columns.Count) ForI=1To .Rows.Count ForJ=1To .Columns.Count MemBuffer(I,J) = .Cells(I,J)’Másolás NextJ NextI End With‘MunkaTartom A makró outputja a munkalap felé: • Legegyszerűbben, a másolás visszafele is lejátszható. Ekkor a makró nyomja (Push Strategy) az outputokat a munkalapra. Ügyeljünk rá, hogy ezidőre az automatikus számolás ki legyen kapcsolva, különben minden egyes cella után frissíteni akarna: Application.Calculation = xlManual ForI=1ToMunkaTartom.Row ForJ=1ToMunkaTartom.Column MunkaTartom.Cells(I,J)= MemBuffer(I,J) NextJ NextI Application.Calculation = xlAutomatic RedimMemBuffer(0)‘A dinamikus tömböt szabadítsuk fel, ha már nem kell

A makrók memóriaváltozói és a munkalapok közti adatforgalom 2 Szándékos Excel-lebutítás: sajnos, több 100 cellára ez még így is baromi lassú lesz (pár cellánál elmegy), mert minden egyes cellába íráskor a teljes munkalap ablak grafikusan MINDIG frissül, akkor is ha a .Calculation ki van kapcsolva, akkor is, ha a munkalapot időlegesen elrejtjük (WorkSheet.Visilble = False). Ezt nem lehet kikapcsolni, hadd szívjuk meg! 3-7.PÉLDA: a Case5.xls fájlban táblázatokat írunk ki makróval az Output munkalapra (a makrót a Proces- sing munkalapon lé- vő RUN gombbal lehet indítani). A szá- molás szemvillanás alatt megtörténik, a sok homokórázás az output kiírása… És így kerülhető ki: • Tegyük fel, hogy van a Modul1-ben egy BaziNagyTomb nevű publikus, globális 2 dimenziós tömbünk, amit egy korábban lefutott makró már feltöltött számítási eredményekkel, és ennek megtörténtét a BaziNagyKesz globális logikai változó jelzi • Írjunk egy BaziNagyElem függvényt, ami semmi mást nem csinál, mint a tömb adott elemét visszaadja, ha ki van számolva (ha nincs, üres értéket ad vissza): Public FunctionBaziNagyElem(ByValSor,OszlopAs Long) As Variant IfBaziNagyKeszThen‘Ha ki van számolva BaziNagyElem=BaziNagyTomb(Sor,Oszlop) Else‘Nincs kiszámolva BaziNagyElem= „” ‘Üres érték End If‘Ki van számolva? End Function‘BaziNagyElem • Hívjuk meg cellafüggvényként a munkalapon, és dobjunk rá egy adattáblát, amelynek paraméterei a Sor és az Oszlop, tehát bemásolja szépen a tömb értékeit az adattáblába • Ilyenkor az Excel cellaképlet-frissítési algoritmusán fut át a dolog, ami csak a végén frissíti a diagrammokat és a grafikai dolgokat, ezért borzasztó gyors katt

3-1.Házi feladat: Számból szöveg • Készítsen saját cellafüggvényt, amely egy (-999,999,999.0000..999,999,999.0000) közti törtszámot kiír szöveges formában. A megoldás: 3-1HaziMegoldas.xls • Szorgalmi feladatként, oldja meg, hogy egy paramétertől függően magyarul/angolul írja ki =MyNumTextConv(NumInp;WordSetHu)

Excel konzultáció 3. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22