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

Excel konzultáció 3. Előadás 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

Az előadás tartalma Excel makrók Office Visual Basicben 1 Miért van rá szükség? • Cellaképlet-alapú és procedurális algoritmus leírás összehasonlítása • Excel objektumok átprogramozása makrók segítségével Office Visual Basic • A makrórögzítő használata • A modulok javasolt szerkezete • Office Visual Basic szintaktika • Primitív változótípusok • Összetett változók • Felhasználói függvények és eljárások • Kódszerkesztési szabályok • Kódvezérlő utasítások • Hibakezelési módszerek • Operátorok • Matematikai • Szöveges • Logikai • Típus-ellenőrző • Beépített függvények • Típuskonverziós • Matematikai • Szöveges • Dátum • Kontroll • Beépített Visual Basic és cellafüggvények viszona Szakirodalom

=HA(ÉS(pi<>"";pj<>""); 'Ha fejléc ki van töltve ( (pi- 'i termék egységára (gj+pi^2*fj))* 'i termék egységköltsége Bi/uii*KITEVŐ(1-SZUM(pi/uii;pj/uij)) 'i termék kereslete )+( (pj- 'j termék egységára (gi+pj^2*fi))* 'j termék egységköltsége Bj/ujj*KITEVŐ(1-SZUM(pi/uji;pj/ujj)) 'j termék kereslete ); "") 'Ha fejléc nincs kitöltve, üres Cellaképlet-alapú és procedurális algoritmus leírás összehasonlítása 1 Vessük össze a 2-1HaziMegoldas.xls-ban a mintafüggvényt a hasonló célra írt Office Visual Basic makró kóddal! Azt láthatjuk, hogy ha egy számítási algoritmust képletekkel írunk le (Formula-Based Algorithm), az az átlagem-ber számára könnyebben érthető, mint a procedurális programkód (Procedural Algo-rithm), mert a cellákban azonnal látja az ösz-szes (rész)eredményt, így nincs szükség ma-gas szintű absztrakciós készségre és nem kell sokat debugolni. Azonban a képlet-alapú leírásnak komoly hátrá-nyai vannak: • Nagyságrendekkel több erőforrást fogyaszt, mint a programkód • Mivel egy cellán belül - a programkóddal ellentétben - nem használhatunk • Lokális segédváltozókat (Local Variable) az előre kiszámított részeredmények tárolására és újrafelhasználására, • Lokális szubrutinokat (Local Subroutine) ismétlődő részfeladatokhoz, beleütközünk a kód multiplikáció (Code Multiplication) problémájába: többszörösen elágazó logikai szerkezeteket tartalmazó algoritmusokban minden ágon külön muszáj leírni ugyanazt a részszámítást kis változtatasokkal: Sub FuggvTabla() Dim i, j As Integer 'Ciklusváltozók Dim pi, pj As Single 'Egységárak Dim Bi, Bj As Single 'Kosárméretek Dim gi, gj As Single 'Min.egys.költségek Dim fi, fj As Single 'Költségcsökkentési % Dim uii, ujj As Single 'Saját hasznosságok Dim uij, uji As Single 'Kereszt hasznosságok With Worksheets("2-1HomSol|2-1HaziMego") 'Paraméterek felszedése Bi = .Cells(6, 15).Value Bj = .Cells(19, 2).Value gi = .Cells(19, 3).Value gj = .Cells(7, 15).Value fi = .Cells(19, 4).Value fj = .Cells(8, 15).Value uii = .Cells(6, 14).Value ujj = .Cells(18, 2).Value uij = .Cells(6, 16).Value uji = .Cells(20, 2).Value For i = 0 To 10 'i termék árszintjei For j = 0 To 10 'j termék árszintjei 'Fejléc kitöltve ellenőrzés If .Cells(6, 3 + i).Value <> "" And _ .Cells(7 + j, 2).Value <> "" Then 'Egységárak felszedése pi = .Cells(6, 3 + i).Value pj = .Cells(7 + j, 2).Value 'Profit kiszámítása és kiíratása .Cells(40 + j, 3 + i).Value = _ ((pi - (gj + pi ^ 2 * fj)) * Bi / uii * Exp(1 - (pi / uii + pj / uij))) + _ ((pj - (gi + pj ^ 2 * fi)) * Bj / ujj * Exp(1 - (pi / uji + pj / ujj))) End If 'Fejléc kitöltve Next j Next i End With End Sub Fejben kell model-lezni kódoláskor: |0.0 1.0 2.0 3.0… 0.0|-181.2 -102.7… 1.0|-102.7 . | . .

=HA(UpLeftX<DnRightX; HA(UpLeftY<DnRightY; "Normal window"; HA(UpLeftY=DnRightY; "No window"; "Topdown window") ); HA(UpLeftX=DnRightX; "No window"; HA(UpLeftY<DnRightY; "Mirrored window"; HA(UpLeftY=DnRightY; "No window"; "Mirrored topdown window" ) ) )) Cellaképlet-alapú/procedurális algoritmusok összehasonlítása 2 3-1.PÉLDA: a 3-1Pelda.xls fájl azt teszteli, hogy a bal felső (UpLeft) és jobb alsó (DnRight) sarok X,Y koordinátáival megadott kis ablak fejen áll-e, tükrözött-e, illetve nem 0 méretű-e: • Látható, hogy a cellaképlet komplexitása „kombinato- rikailag felrobban” az összesen 9 lehetséges logikai elágazás (UpLeftX<,=,>DnRightX) × (UpLeftY<,=,> DnRightY) miatt: a „0 méretű”(„No window”) out- putot három különböző ágon ismételten ki kell adni, és a „Tükrözött” („Mirrored”) jelzést is két helyen. Hiába variáljuk a kódot,mindig lesz többszöröződés! • A probléma procedurális kódja viszont egyszerű,mert a kiszámított részeredményeket lokális változókba (pl. PosHoriz, PosVert) el tudjuk tárolni, így a „0 méretű”(„No window”) és a „Tükrözött” („Mirrored”) jelzést is csak egyszer kell kiadni! • Természetesen, a probléma feloldható lenne oly módon, hogy a számítást számos részeredmény-cellára bontjuk, de ez az Excel erőforrás-fogyasztása szempontjából véres következményekkel jár: pl. Egy munkalap-méretű függvénytábla helyett 8-10db-ot kellene kezelni. És itt adattáblákat sem használhatunk, mert az adattáblamintaképlete a sor/oszlop inputon kívül minden más hivatkozást abszolútként kezel: nem tudja meghivatkozni egy korábban kiszámított részeredmény-tábla adatait,mert képtelen egy relatív hivatkozást automatikusan „végigcsúsztatni” rajta! • A kód-multiplikáció problémája igazán hatékonyan felhasználó által definiált függvények (User-Defined Function) segítségével lesz feloldható: olyan cellafüggvény, amit a fejlesztő által írt Office Visual Basic makró függvény ír le, ahol a programkódban használhatunk tabuláciot, kommenteket, segédváltozókat, szubrutinokat, sőt az Excel beépített cellafüggvényeit is! Sub WindowTester() Dim PosHoriz, PosVert As String With Worksheets("3-1Exmpl|3-1Pelda") If .Range("UpLeftX").Value = _ .Range("DnRightX").Value Or _ .Range("UpLeftY").Value = _ .Range("DnRightY").Value Then .Cells(10, 1) = "No window" Else 'If real window If .Range("UpLeftX").Value < _ .Range("DnRightX").Value Then PosHoriz = "Normal" Else 'If mirrored PosHoriz = "Mirrored" End If 'Horizontal test If .Range("UpLeftY").Value < _ .Range("DnRightY").Value Then PosVert = "" Else 'If mirrored PosVert = "topdown" End If 'Vertical test .Cells(10, 1) = _ PosHoriz + " " + PosVert + " window" End If 'No window test End With End Sub

A makrók másik fontos alkalmazási területe • Az Excel objektumainak nem minden funkciója érhető el a felhasználói felületről, makróból viszont igen, így olyan dolgokra vehetjük rá az Excelt egyszerű, pár soros kódokkal, amire nem is tervezték! Szándékos Excel-lebutítás: A legtöbb Excel diagram nem tud egy harmadik adatsort ábrázolni az értékek címkéiben, csak az x vagy y tengelyen ábrázoltakat. Makróval viszont rávehetjük! Gyakorlati alkalmazási példa: • A ClusterTree.xls fájlban az volt a feladat, hogy olyan bináris fát kell rajzolni egy x, y koordináta rendszerben, ahol a levél-csomópontok mérete, felirata és színezése plusz 3 adatsort jelenít meg. • Ilyen diagramm Excelben természetesen nincs. • Először egy x, ybuborék diagramot (Bubble Chart) próbáltunk meg átalakítani, de az nem tudta az összekötő vonalakat. • Végül egy sima vonal diagrammból (Line Chart) alakítottuk ki, az adatpontok méretének, színének, feliratának makróval történő formázásával.

Office Visual Basic makrók, A makrórögzítő használata 1 Az Excelhez 4. generációs (4th Generation), objektum-orientált (Object Oriented Programming, OOP) Office Visual Basic-nek nevezett makrónyelv tartozik, amely 95%-ban kompatibilis a Visual Basic 6.0 programnyelvvel, és képességeinek kb. 90%-át tudja • Jelen tananyagnak nem célja, hogy megtanítson Visual Basic-ben programozni. Egyrészt a programozási képesség NEM egyenlő valamely programnyelv lexikálisan megtanulható ismeretével, hanem rendszer-független probléma-elemző gondolkodásmód, ami csak hosszas gyakorlással, megoldott valós feladatokkal sajátítható el teljesen, egy átlagos képességű embernél 6 év alatt. • Másrészt, még csak az sem célunk, hogy teljes körű leírást adjunk az Office Visual Basic makrónyelvről. Ezzel kapcsolatban lásd a szakirodalmat. • A célunk az, hogy minimális programozási ismeretek elsajátításával, egyszerű pár soros kódokkal, az Excel beépített elemeire támaszkodva/azokat kicsit átprogramozva olyan látványos és komplex dolgokat tudjunk végrehajtani, amit más programnyelvekben csak nagy programozási gyakorlattal lehet megtenni. Az Excel alapbeállításában biztonsági okokból blokkolja a makrók futtatását, ezért először ezt a tiltást kell feloldanunk az Eszközök|Makró|Biztonság|Alacsony (Tools|Macro|Security|Low) menüvel Az Excel programozásával kapcsolatban az első szabály, hogy igyekszünk elkerülni a kézzel, fejből történő kódolást, mert az Excel objektumainak belső szerkezete bonyolult, és nem is muszáj teljesen értenünk őket feladataink végrehajtásához (példaként lásd: 3-1Pelda.xls): • Ehelyett először az Eszközök|Makró|Makró rögzítése (Tools|Macro|Record new macro) menüvel bekapcsoljuk a makró rögzítőt, és megadjuk a következőket: • A Makró nevét(MacroName),pl. MyFirstMacro • A Helyét(Destination), általában az adott munkafüzet • A Billentyűparancsát (Hot Key) • A makró esetleges Leírását (Description) • Ezután megjelenik a makró Rögzítés eszközsor, és kézzel, egyszer végrehajtjuk Excelben a műveleteket (pl. A10 cella pirosra színezése),amit a programkóddal szeret- nénk (akár ismétlődően) megcsináltatni (pl. sok más cella pirosra színezése kódból). katt katt katt katt katt katt katt

A makrórögzítő használata 2 katt kat- kat • Figyeljünk rá, hogy ne csináljunk felesleges műveleteket közben, mert a rögzítő mindent rögzít (még a cellák kijelölését, vagy külső program indítását is), és telenyomja a kódot felesleges dolgokkal • Ha elkészültünk,a Rögzítés eszközsoron Stoppal( ) állítsuk le a rögzítést • Az Excel erre a megcélzott Munkafüzet (Workbook) objektumba ágyazott Modulok (Modules) objektum kollekcióhoz hozzáad egy új Modul(Module) objektumot (pl. Module2), ami makró-kódok tárolására szolgál, majd ebbe beleírja a rögzített makró(pl.MyFirstMacro) kódját. Ezt így nézhetjük meg: • Az Eszközök|Makró|Visual Basic Editor(Tools|Mac- ro|Visual Basic Editor) menüvel kinyitjuk a Visual Ba- sic fejlesztői környezet ablakot • A bal oldalán látható Project tartalomjegyzékben meg- keressük az új modult és duplán kattintunk rá • Erre a jobb oldalán található kódszerkesztő ablakban megjelenik a rögzített makró Visual Basic kódja • Az automatikusan generált kód tanulmányozásakor rájöhe- tünk, hogy a pl. a cella háttérszínét Visual Basic-ben .Interior.ColorIndex-nek hívják és a piros színkódja a 3-mas, anélkül, hogy bármilyen súgót megnéztünk volna!!! • Mindezek után a kódot átírhatjuk saját céljainkra (pl. MySecond- Macro), úgy, hogy akár az adott feladatot ismétlődően is végez- heti egy For ciklusban (pl. több cellát színez be) • A kész kódot a kódszerkesztő Debug|Compile menüjével fordít- hatjuk le és ellenőrizhetjük szintaktikailag, majd a Run|Run me- nüvel/ ( )gombbal/ munkalapról a makró forró gombjával futtatjuk A makrórögzítő használata abban az esetben is segíthet, ha régi mak- róink az Excel egy újyabb verziójában már nem futnak. Pl. az Office 2002 és a későbbi verziók közt megváltoztatták egy kissé az Excel diagrammjainak belső objektum szer- kezetetét, ezért a 2002-es Office diagrammjait kezelő mak- rók nem futnak le magasabb verziókban. A rögzítővel azonban gyorsan megtalálhatjuk, hogy az adott Excel verzióban éppen hogyan hívják a diagramm használni kívánt részét, és a módosítással a régi makró újra használható lesz katt katt katt katt

3-1.Gyakorló feladat 3D diagramm forgatása makróból A 2-1HaziMegoldas.xls–ből kiindulva írjon makrót makrórögzítő segítségével, ami a diagram-mot a függőleges tengely mentén körbeforgatja 360 fokban a Ctrl+Shift+R forró gombra, hadd legyen egy kis élet a képernyőn! A megoldás: 3-1GyakorloMegoldas.xls Ctrl Shift R

‘1.Global declarations************ ‘1.1.Global Options--------------- Option Explicit’Must declare all Option Base 0’Starting index=0 ‘1.2.Global constants------------- Const SysPath As String=„C:\VB\” ‘1.3.Global types----------------- Type Coord’Coordinate point X As Single Y As Single End Type ‘1.4.Global variables------------- Dim CoordNum As Integer’Coord num. Dim Points(1 To 10) As Coord’Array Dim Matr() As Single’Dynamic array ‘2.Procedures/Functions*********** ‘2.1.Simple functions------------- Function Dist(ByVal P1 As Coord,_ ByVal P2 As Coord) As Single’Eucl.dist. Dim DiffX,DiffY As Single DiffX=Abs(P1.X-P2.X) DiffY=Abs(P1.Y-P2.Y) Dist=Sqrt(DiffX^2+DiffY^2) End Function ‘Dist ‘2.2.Complex procedures----------- Sub DistMatr()’Compute dist.matrix Dim I,J As Integer‘Cycle variable On Error GoTo ErrorHandle1 If CoordNum>0 Then’Runable check ‘Reserve memory dynamic array Redim Matr(1 To CoordNum,_ 1 To CoordNum) For I=1 To CoordNum For J=1 To CoordNum Matr(I,J)=_ Dist(Points(I),Points(J)) Next J Next I End If Exit Sub ErrorHandle1:’Error handler code MsgBox „HIBA!”’Error message Redim Matr(0)’Release memory Resume Next End Sub ‘DistMatr A modulok javasolt szerkezete Bonyolultabb modulok írása során tartsuk be a mellékelt tagolást, sőt emeljük ki decimális sorszámozású komment címsorokkal, hogy a kód áttekinthető, olvasható maradjon: • Globális deklaráciok(Global Declarations): az egész modulban érvényesek • Kapcsolók (Option): a fordító (Compiler) beállításai, • Konstansok (Const): adott típusú rögzített értékek • Adattípusok (Types): primitív (Primitive) típusok (pl. Integer,Long,Single,Double,String,Boolean) vagy összetett típusok, pl.Type-más típusokból álló rekord • Változók (Dim): adott típusú tároló, futás közben kap értéket. Lehet egyedi vagy több érték indexelt tömbje (Array),ez lehet fix méretű, vagy dinamikus(Dynamic) • Eljárások (Sub) és függvények (Function) hierarchiája az egyszerűektől a bonyolultak felé haladva. Az utóbbiak meghívhatják az előzetesen deklarált egyszerűeket, vagy meghívhatnak beépített függvényeket adott részfeladatok végrehajtására. Az eljárásokon/ függvényeken belül: • Fejlécükön (Header) a nevük és adott típusú para-métereik adott sorrendben vannak felsorolva: • Csak értékkel átadott (ByVal) bemenő paraméter • Kívül már deklarált, létező változóra hivatkozó (ByRef), egyszerre be- és kimenő paraméterek • Belsejükben először a helyben látható, lokális (Local) típusokat, konstansokat, változókat deklaráljuk • Majd az algoritmusuk kódját írjuk le: • Futtathatósági feltétel (PreCondition) ellenőrzés • Szükséges Erőforrások (Resource) (pl. dinami-kus tömbnek memória) lefoglalása (Reserve) • Input-output transzformáció (Computation) • Erőforrások felszabadítása (Free, Release) • Felmerült hibák kezelése (ErrorHandle) • A függvények adott típusú visszatérő értéket (Return) adnak vissza, az eljárások nem adnak vissza ilyet

Office Visual Basic szintaktika 1: Változótípusok A mellékelt táblázatban a primitív típusokat látjuk: • Fontos megjegyezni, hogy a Date valójában 8byte-os fix tizedes pontos számként tárolódik, ahol a 0.000 megfelel 1900.01.00 00:00:00- nak, és +1nap = +1.0 (+1 óra = +1/24, stb.), a negatív értékek pedig a kezdődátum előtti idő- ket jelentenek, két dátum kivonható egymás- ból, dátumhoz számot adhatunk/kivonhatunk • A Variant, amely a munkalap cellák típusa, és tárolhat szöveget, számot, dátumot, automatikus tipuskonverziót alkalmaz: ha a beírt szö- veg értelmezhető bármely érvényes szám vagy dátum formátumban, akkor konvertálja Primitív típusú konstansok és változók deklarálása: ConstKonstansNevAsKonstansTipus=Ertek DimValtozonevAsValtozoTipus • Ezek a verem (Stack) nevű RAM-területen foglalnak a típusuk szerinti fix számú byte-ot Primitív típusú konstansok és változók használata a kódban: • Értékadásnál mindig jobbról balra áramlik az információ: Valtozo=Konstans, Valtozo1=Valtozo2, de nem lehetKonstans=Valtozo • Feltételekben történő összehasonlításnál konstans/változó bármely oldalon lehet Több értéket indexelten tároló, tömb tipusú változók deklarálása: DimTombNev(KezdoIndexToVegIndex) AsTombElemTipus ‘Egy dimenziós tömb DimTomb(Kezd1ToVeg1, Kezd2 To Veg2) AsElemTipus ‘Több dimenziós tömb • Ezek a Stack-ben foglalnak elemszám × típusuk szerinti fix számú byte-ot, üresen is! • Akkor is van 0-dik elemük, ha az indexet 1-el kezdjük, ha az Option Base 0-ra van állítva DimDinamTomb() AsElemTipus ‘Dinamikus méretű tömb • Ez üresen csak 4byte-ot foglal, de így nem is használható semmire, Redim utasítással foglaljuk le futás közben az aktuális méretét, a méretesebb halom (Heap) memóriában: RedimDinamTomb(1ToAktualisSorok,1 To AktualisOszlopok) • Ha már nincs rá szükség RedimDinamTomb(0) utasítással felszabadítjuk, a Heap-memóriafoglalást, a dinamikus tömb tartalma ilyenkor elveszik • A tömb utolsó index-tartományát megnövelhetjük úgy is, hogy a tartalma megmarad: Redim PreserveDinamTomb(1ToSorok,1ToSokkalTobbOszlop)

Office Visual Basic szintaktika 2: Összetett változók Szándékos Excel-lebutítás: ha a Redim Preserve-t tömegesen használjuk, a tömb több ezer új elemmel történő egyenkénti bővítésére, a makró kód futása exponenciálisan lelassul, mert a Visual Basic az utólag hozzáfűzött részeken keresztül láncszerűen ugrálva keresi az elemeket, nem közvetlen eléréssel, mint a fix méretű tömb esetén! Ezért nagyobb egységekkel, több 100 elemmel egyszerre bővítsük a tömböt, vagy használjuk helyette a később tárgyalt objektum kollekció tárolót, bár annak kezelése jóval lassabb, mint a tömböké (a tömbök a leggyorsabb tárolószerkezetek) Tömbelemek használata a kódban: TombNev(Index1,Index2) • Egy tömb elemeit jellemzően nem egyenként kezeljük, hanem tömegesen, For ciklusok segítségével, amikor az indexek ciklusváltozók, vagy abból számított képletek • Több dimenziós tömböket több egymásba ágyazott For ciklussal kezelünk • Tartományon kívüli tömbindex használata „Out of index range” hibajelzést okoz Rekordtípusok deklarálása: • Gyakran szükség van rekordnak (Record) nevezett összetett tárolószerkezetekre, ahol egy egyedhez (Entity) (pl. Ember) tartozó különböző típusú adatmezőket (Field) fogunk össze (pl. Név, Cím, Kor): TypeRekordTipus Mezo1NevAsMezo1Tipus Mezo2NevAsMezo2Tipus End Type Egyedi rekord-változók deklarálása: DimRekordValtozoAsRekordTipus Egyedi rekord-változók használata kódban: RekordValtozo.Mezo1Nev=Valami Egy rekord tartalmazhat bármely fajta tömböt, még dinamikusat is: TypeTombosRekordTip NevAs String Tomb(1To10,1To2) As Integer End Type DimTombosRekordAsTombosRekordTip TombosRekord.Tomb(SorIndex,OszlopIndex) =Valami • Ha fix méretű tömböt tartalmaz, figyeljünk rá, hogy a rekordszerkezet mérete nem haladhatja meg a 64K-t

Office Visual Basic szintaktika 3: Összetett változók Bármely fajta tömb - még dinamikus is - állhat rekordokból: DimAdatBazisTabla() AsRekordTipus RedimAdatBazisTabla(1ToAktualisRekordSzam) AdatBazTabla(RekordIndex).Mezo1Nev=Valami RedimAdatBazisTabla(0) • Pl. rekordokból álló dinamikus tömbbel a memóriában is létre tudunk hozni adatbázis tábla-szerű tárolókat, amelyek gyorsabban működnek, mint az adatbázis táblák További tartalmazási szabályok (Nesting Rules): • Rekordtípus mezőként tartalmazhat egy korábban deklarált másik rekordtípust • Bármely tömb elemként tartalmazhat tömb típusú változókat (de inkább használjunk helyette több dimenziós tömböt) • Variant típusú, munkalap cellát megjelenítő változó tartalmazhat tömböt, de rekordot nem Összetett tárolószerkezetek közti érték-átadás két módszere: • A tárolt értékek átadása (Passing By Value): a tárolt adatok egy külön memóriahelyet foglaló másik szerkezetbe másolódnak. A két szerkezet értékei ezután nem függnek egymástól, az eredeti törlése nem hat a másolatra: • Rekordváltozó rekordváltozónak közvetlenül is adhat értéket, nem muszáj mezőnként elvégezni az értékadást: RekordValtozo1=RekordValtozo2 • Mezőnként akkor adunk át értéket, ha nem az összes mezőt akarjuk egyszerre átadni: Rek1.Mezo1 =Rek2.Mezo1 • Tömb azonos dimenzió- és indexszámú másik tömbtől közvetlenül kaphat értéket, nem muszáj elemenként elvégezni az értékadást: Tomb1=Tomb2 • Hivatkozás (Passing By Reference) egy már létező tárolószerkezetre: az új tároló (Valtozo1) nem foglal külön helyet a memóriában, hanem kezdő memóriacíme azonos lesz a másik tárolóval (Valtozo2). A Set kulcsszóval „rádefiniáljuk” az egyik tárolót a másikra, így az átveszi a másik aktuális tartalmát: SetValtozo1=Valtozo2 • A típusuknak és a szerkezetüknek nem muszáj azonos lenni, de célszerű, különben Valtozo1 értelmetlen adatokat fog mutatni • Eltérő típusú tároló rádefiniálásának egy másikra akkor van értelme, ha pl. egy bonyolultabb tárolószerkezet memóriabeli tartalmát byte-onként akarom látni, és ezért rádefiniálok mondjuk egy stringet • Rádefiniált változó leválasztása (nem fog mutatni sehova és használhatatlan lesz): SetValtozo1= Nothing

Office Visual Basic szintaktika 4: Felhasználói függvények és eljárások Felhasználó által definiált függvények (User Defined Function) szintaxisa: • Függvények deklarációja: • A függvények fejlécén (Header): • A függvény neve elé opciókat írhatunk: • Public/Private: a függvény a modulon kívülről használható (pl. cellafüggvényként), publikus (alapértelmezett) / vagy nem, így privát • Static: a függvény lokális változói lefutása után sem törlődnek a Stack me-móriából, ne használjuk! Amit fixen tárolni kell, tároljuk globális változókban! • A függvény neve után zárójelben, vesszővel elválasztva felsoroljuk a bemenő paramétereit, As kulcsszó után típusuk megjelölésével. Két félék lehetnek: • ByVal: érték szerint átadott, csak input paraméter, független a környezettől • ByRef: külső változóra hivatkozás szerint átadott paraméter, lehet input paraméter, de lehet output is, mert visszaírja a hivatkozott változóba! • Ezután jön a függvény visszatérési értékének (Return Value) típusa [Private][Static]FunctionFuggvNev(ByRefErtekAtParamAsParTip, ByValHivatkAtParamAsParTip) AsVisszaterTipus DimLokalisValtozoAsLokalisTipus‘A függvény lokális változói ‘A függvény itt számol… FuggvNev=Kifejezes‘Nem felejtjük el kiszámítani a visszatérő értéket! [Exit Function]‘Kiugorhat vele a függvényből még a véget érése előtt End Function‘A függvény lezárása • Függvények meghívása kódból: • Azonos modulban: Valtozo=FuggvNev(AktParam1,AktParam2) • Másik modulból (csak publikus függvényt hívhat meg!): Valtozo=ModulNev.FuggvenyNev(AktualisParam1,AktualisParam2) • Ha nagyon sok paraméterű a függvény, és nem akarjuk megadni az összest, egyes paramétereire a nevükkel hivatkozunk: Valtozo=FuggvenyNev(Param1Nev:=Ertek,Param26Nev:=Ertek) Felhasználó által definiált eljárások (User Defined Sub) szintaxisa: • Eljárások deklarációja: ugyanolyan, mint a függvényeké, kivéve hogy Function helyett Sub kulcsszót használunk, és nincs visszatérő érték • Eljárások meghívása kódból (figyeljünk rá, hogy itt nem kell zárójel a paraméterek körül): • Azonos modulban: EljarNevAktParam1,AktParam2 • Más modulból, publikus eljárást: ModulNev.EljarNevAktParam1,AktParam2

Office Visual Basic szintaktika 5: Kódszerkesztési szabályok Az azonosító-nevek kiválasztása: • Vigyázzunk, mert az Excel munkalapokkal ellentétben, a Visual basic kódban használt azonosítónevek NEM tartalmazhatnak ékezetes betűket, szóközt, speciális karaktereket és nem kezdődhetnek számmal. A nevesített cellatartomány-nevekkel igazodjunk ehhez! • Az azonosító-nevek ajánlott hossza 8±3 karakter, a könnyebb kódolás végett • Lehetőleg legyenek értelmes angol rövidítések (NE V1, V2, V3, stb.!!!), több tagból álló neveknél nagy kezdőbetűs tagolás ajánlott (pl. DistanceMatrix), de a kisbetű-nagybetű közt nincs különbség. Az azonosító-neveket válasszuk szisztematikusan, mert utólag elég véres munka a kódban valamit átnevezgetni Szerkesztés| Keres&Csere (Edit|Find& Replace) menüből vagy Ctrl+H forró gombra • Az azonosító-nevek a kódszerkesztőben mindig Fekete színnel jelennek meg • Az azonosító-nevek nem lehetnek lefoglalt szavak (pl. Sub, Exit, stb.), amik mindig kékkel jelennek meg a kódszerkesztőben • Az Excel beépített konstansainak azonosító-nevei a jobb elkülönülés végett mindig „xl” előtaggal kezdődnek (pl. xlVisible) • Ha egy egy függvényben egy lokális változó neve megegyezik egy globális változóéval, akkor csak a lokális változót tudja használni: az „rátakar” a globális változóra, ezért ezt a helyzetet kerüljük, pl. a globális változók kezdődjenek „gl” előtaggal: glSysPath • Gyakori probléma, hogy egy rekordváltozót ugyanúgy próbálunk elnevezni mint a rekord típusát, amit természetesen nem lehet. A könnyebbség kedvéért, az összes rekord-típusnevet kezdjük „t” előtaggal, így a típus és a változó neve elég hasonló lehet, pl. DimRepulesiAdatokAstRepulesiAdatok Tabulálási szabályok: • A Visual Basic kódot az áttekinthetőség miatt tabulálni kell: egy sorba csak egy utasítást írunk, a beágyazott dolgokat egy tabulátorral beljebb léptetjük, így dobozoljuk egymásba az utasításokat • A tabulálásra itt NE szóközt használjunk, mint a cellafüggvénynél, hanem Tab-ot • Ha több sort egyszerre kell beljebb/ kijjebb tabulálni, akkor ezt a sorok Shift+Cursor kijelölése után Tab-bal /Shift+Tab-bal tehetjük meg Sortörési szabályok: • A kódszerkesztőben egy sor max. 128 karakter lehet. Ha pl. egy bonyolult függvény hívás nem fér ki egy sorba, akkor a kulcsszavak közt bárhol eltörhetjük a sort „ _” (szóköz+aláhúzás) karakterkombinációval, és a következő sorban folytatjuk. • A fordító minden beírt sort ellenőriz szintaktikailag, és piros színnel jelöli a hibákat

Office Visual Basic szintaktika 6: Kódszerkesztési szabályok • Kommentezés: • Aposztróf (‘) jel után tetszőleges hosszúságú kommentet írhatunk a sorok végére, vagy külön sorba, esetleg több soron keresztül (kommentben nem kell sortörő jel) • A kódszerkesztő zöld színnel jelöli a kommenteket • A kód tartós dokumentása végett minden konstansot, típust, változót, függvényt, eljárást, azon belül minden utasítást kommentezni kell, kivéve a nagyon triviálisakat (pl. I, J, K ciklusváltozók). Ez jelentős plusz munka, de így a kód öndokumentáló, könyvszerűen olvasható lesz, és később is meg fogjuk érteni, újrafelhasználáskor!!! • Automatikus szövegfelismerés (IntelliSense): A kurzorral • As kulcsszó, • Vagy egész-rész kapcsolatot jelölő pont (.) után állva a kódszerkesztő automatikusan fel- hoz egy előugró menüt, ahol felsorolja az adott helyen használható: • Típusokat, • Objektumrészeket, • Rekordmezőket, és innen választhatunk, nem kell a nevüket fejből megjegyeznünk katt katt katt

Office Visual Basic szintaktika 7: Kódvezérlő utasítások Szelekciók (az ajánlott kommentezéssel együtt mutatjuk): az algoritmus elágazásait kezelik • Egy[két]szeres (Dual) elágazású: IfFelteteliKifejezesThen CsinaldEztHaIgaz [Else‘Ha nem FelteteliKifejezes CsinaldEztHaHamis] End If‘FelteteliKifejezes • Többszörös (Multiplex) elágazású: Select CaseFelteteliKifejezes CaseErtek1:‘Ha FelteteliKifejezes = Ertek1 CsinaldEztErtek1Eseten CaseErtek2:‘Ha FelteteliKifejezes = Ertek1 CsinaldEztErtek2Eseten CaseElse‘Ha FelteteliKifejezes más CsinaldEztKulonben End Select‘FelteteliKifejezes Ciklusok (az ajánlott kommentezéssel együtt mutatjuk): a ciklusmagba (Cycle Core) foglalt utasítások ismétlődő végrehajtására szolgának, lehetnek: • Rögzített pörgészámú (Fix Iterations), ciklusmag futása előtt tesztelő(Pretesting) ciklusok: ForCiklusValt=KezdErtekToVegErtek[StepLepesKoz]‘A LepesKoz lehet negatív is visszafele pörgőnél, csak akkor legyen KezdErtek>VegErtek Valami=CiklusValt‘A ciklusváltozó számolásban,tömbindexekben vehet részt [Exit For]‘Ha ezt kiadjuk, a VegErtek elérése előtt kiugrik a ciklusból NextCiklusValtozo • Egy tömb vagy objektum kollekció elemein végiglépegető ciklus, akármennyi van belőlük: [DimIAs Long]‘Figyeljünk rá, hogy nincs ciklusváltozója, külön kell csinálni ha kell [I=0]‘A ciklusváltozónak kezdőértéket adunk, ha kell For EachElemInTombVagyKollekcio [I = I + 1]‘A ciklusváltozó értékét növeljük, ha kell Valami=Elem‘Az aktuális elemmel műveletet végzünk a ciklusmagban [Exit For]‘Ha ezt kiadjuk, az utolsó elem elérése előtt kiugrik a ciklusból NextElem

Office Visual Basic szintaktika 8: Kódvezérlő utasítások • Nem rögzített pörgésszámú (Variable Iterations) ciklusok: • Elöltesztelő (Pretesting), nem rögzített pörgésszámú (Variable Iteration) ciklusok: • Igaz feltételre iteráló (Iterate On True), elöltesztelő, nem rögzített pörgésű ciklus: [DimIAs Long]‘Nincs ciklusváltozója, külön kell csinálni, ha kell [I=0]‘A ciklusváltozónak kezdőértéket adunk, ha kell Do WhileFeltetelIgaz‘Függhet a ciklusváltzótól is, pl. I<=VegErtek [I = I + 1]‘A ciklusváltozó értékét növeljük, ha kell CiklusMag [Exit Do]‘Erre a feltételtől függetlenül kiugrik a ciklusból Loop • Hamis feltételre iteráló (Iterate On False), elöltesztelő, nem rögzített pörgésszámú ciklus: [DimIAs Long]‘Nincs ciklusváltozója, külön kell csinálni, ha kell [I=0]‘A ciklusváltozónak kezdőértéket adunk, ha kell Do UntilFeltetelHamis‘Függhet a cilusváltzótól is, pl. I>VegErtek [I = I + 1]‘A ciklusváltozó értékét növeljük, ha kell CiklusMag Loop • Hátultesztelő (Post Testing), nem rögzített pörgésszámú (Variable Iteration) ciklusok: • Igaz feltételre iteráló (Iterate On True), hátultesztelő,nem rögzített pörgésű ciklus: [DimIAs Long]‘Nincs ciklusváltozója, külön kell csinálni, ha kell [I=0]‘A ciklusváltozónak kezdőértéket adunk, ha kell Do [I = I + 1]‘A ciklusváltozó értékét növeljük, ha kell CiklusMag ‘Egyszer mindenképpen lefut, mert a teszt utána van! Loop WhileFeltetelIgaz‘Függhet a ciklusváltzótól is,pl. I<=VegErtek • Hamis feltételre iteráló (Iterate On False), hátultesztelő, nem rögzített pörgésszámú ciklus: [DimIAs Long]‘Nincs ciklusváltozója, külön kell csinálni, ha kell [I=0]‘A ciklusváltozónak kezdőértéket adunk, ha kell Do [I = I + 1]‘A ciklusváltozó értékét növeljük, ha kell CiklusMag ‘Egyszer mindenképpen lefut, mert a teszt utána van! Loop UntilFeltetelHamis‘Függhet a ciklusváltzótól is,pl. I>VegErtek

Office Visual Basic szintaktika 9: Hibakezelési módszerek Egy programkódnak védve kell lenni (Fool Proof) az inputokon keresztül érkező felhasználói hülyeségek ellen. Ennek két módszere van: 3-2.PÉLDA: bemutatásukra tegyük fel, hogy egy függvényben 3 egymásra épülő tevékeny-séget kell elvégezni (Tev1, Tev2, Tev3), amelyek értelmetlenek az előző nélkül, mind a 3-nak van készültség jelzője (OK1,..OK3), és mind a 3 lefoglal memóriát (Mem1..Mem3) A, Hagyományos hibakezelés: a hagymahéj-modell (Onion-Skin Model): a tevékenységek: 1.Futtatási feltételei ellenőrzését, 2.A memóriafoglalását, 3.A műveleteiket, 4.Memória- felszabadítást, 5.Hibaellenőrzést, egymásba dobozolt If Then Else-„héjakban” végezzük: FunctionProba(ByRefOK1,OK2,OK3As Boolean) As String DimMem1(), Mem2(), Mem3()As Long Redim Mem1(1000) Tev1 OK1 If OK1 Then Redim Mem2(1000) Tev2 OK2 If OK2 Then Redim Mem3(1000) Tev3 OK3 If OK3 Then Proba = „Minden nagyon szép, minden nagyon jó!” Else Proba = „Tev1, Tev2 megvan, Tev3 elszállt!” End If Redim Mem3(0) Else Proba = „Tev1 megvan, Tev2 elszállt, Tev3 törölve!” End If Redim Mem2(0) Else Proba = „Tev1 elszállt, Tev2 és 3 törölve!” End If Redim Mem1(0) End Function • Értékelése: ez a megoldás precíz, bombabiztos, viszont hosszadalmas megírni

Office Visual Basic szintaktika 10: Hibakezelési módszerek B, Újhullámos microfostos kifogás kezelő-alapú (Exception Handler Based) hibakezelés: nem ellenőrzünk előre semmit, hanem On Error GoTo utasítással nyitunk egy hibakezelő shellt, majd vakon eleresztjük a három tevékenységet. Ha hiba történik közben, a hibakezelő részben próbáljuk utólag kivakarni, mi szállhatott el a 3 dolog közül, majd adunk egy ostoba, semmitmondó hibaüzenetet, és az erőforrások felszabadításáról elegánsan megfeledkezünk, hadd fogyjon a memória, hátha vesz új gépet rá a balek: FunctionProba() As String On Error GoToHibaKezelo1‘Figyel, ha hiba van, kiugratja a hibakezelőhöz RedimMem1(1000) Tev1 RedimMem2(1000) Tev2 RedimMem3(1000) Tev3 Proba=„Minden szép és jó!” RedimMem3(0) RedimMem2(0) RedimMem1(0) Exit Function‘Ez azért kell, hogy ha minden OK, ne menjen rá a hibakezelőre HibaKezelo1:‘Itt próbálja utólag kivakarni, milyen hiba történt Proba=Error(Err)‘Lekérdezi a hibaüzenet szövegét IfProba=„Fatal error” Then‘Ha a hiba túl súlyos… MsgBox „Bekaphatod, most meghalok!”‘Felhasználói élmény fokozása End‘Program leáll, erőforrásokat nem szabadítja fel, mert nehéz kivakarni, mi lefoglalt még és mi nem End If Resume Next‘Ha le tudta kezelni a hibát, próbálja folytatni End Function • Értékelése: ezt „eccerű megírni, mint a faék”, és gyorsan megvan, akár „indiai informatikusokkal” is, de használd bármely Microsoft terméket, és magad is meglátod!!!

Office Visual Basic szintaktika 11: Operátorok, Beépített függvények Matematikai operátorok: • X+Y, X-Y, X*Y, X/Y, X\Y:maradékos osztás, X mod Y:maradék, =, <, >, <>, <=, >= Szövegkezelő operátorok: • A+B: összefűzés (figyeljünk rá, hogy ez cellafüggvényeknél A&B) • True/False = A Like MaszkString: részleges szövegösszehasonlítás, ahol a maszk-stringben: ?: 1 karakter, *: sok karakter, #: 1 számjegy, [acef]: valamelyik karaktert tartalmazza a,c,e,f közül, [!acef]: nem tartalmazhatja a,c,e,f karaktereket Logikai operátorok: • U And V, U Or V, U Xor V, Not U Típus-ellenőrző operátorok és függvények: mivel a munkalap cellák, illetve a nekik megfelelő Variant típusú változók esetén nem lehetünk benne biztosak, hogy éppen milyen típusú adatot tárolnak, esetleg üresek, esetleg valamely munkalap hibaüzenet van bennük, szükségessé válhat ezeknek tesztelése: • True/False = Valtozo1 Is Valtozo2: a két változó típusa azonos • String = TypeName(Valtozo): a változó típusát adja vissza szövegesen • True/False = IsNumeric(Valtozo): a változó tartalma numerikusan értelmezhető • True/False = IsError(Valtozo): a Variant változó tartalma hibajelzés • True/False = IsEmpty(Valtozo): a Variant változó tartalma üres cella, de használható számításokban 0 értékként, vagy „” üres stringként • True/False = IsNull(Valtozo): a Variant változó tartalmának nincs memória lefoglalva, ezért számolásokban nem használható Típus-konverziós függvények: a Visual Basic automatikus típuskonverziót végez különböző típusú változók együttes használata esetén. A megengedett konverziók: BooleanLong • IntegerLong, SingleDouble, IntegerSingle, LongDouble, CurrencyDouble • Számként értelmezhető String (pl. „-123”, „-123.3”, „-0.123e-8”)  Long, Double • Dátum-időként értelmezhető String (pl. „1971.05.11 22:30:14”)  Date  Double (mert dátumot törtszámként tárol). De ha közvetlenül dátum típusú változónak adunk értéket a kódban, akkor így kell: KezdDatum= #1971.05.11 22:30:14# • Ha nem tudja automatikusan konvertálni, például csak szövegként értelmezhető dologgal próbálunk számolni „Type Mismatch” hibajelzést ad A konverzió egértelművé tételére használhatunk típuskonverziós függvényeket: • Integer = CInt(BarmiAmiLehet), Double = CDouble(BarmiAmiLehet) • String = CStr(Barmi), formázva: String = CStr(Format(Kifejezes,FormatumKod)) • A FormatumKod lehet:Fixed, Standard, Percent, Scientific, LongDate, LongTime,stb.

Office Visual Basic szintaktika 12: Beépített függvények Matematikai függvények: • Abs(), Atn(), Cos(), Exp(), Fix():egészérték, negatívnál a kisebb, Int(): egészérték, negatívnál a nagyobb, Log(), Rnd(), Sgn(), Sin(), Sqr(), Tan() Szövegkezelő függvények: • Szam = Asc(String): 1 karakter ASCII kódja • String = Chr(Szam): számból ASCII karakter • Szam = Len(String): a string hossza • True/False = Instr(KezdKarakter,MibenKeresi,MitKeres,0/1): MitKeres string keresése MibenKeres stringben, 0:pontos egyezés, 1:nagybetűt-kisbetűt azonosnak tekint • String = Mid(String,KezdoKar,Hossz): stringrészlet kivágása • String = Left/Right(String,Karakter): string bal/jobb vége • String = L/RTrim(String): string bal/jobb végéről szóközök levágása • String = L/UCase(String): kis/nagybetűs szöveg Dátum kezelő függvények: • DatumIdo = Now(): aktuális rendszer dátum/idő • Application.Wait DatumIdo: a makró futását késleltesse egy adott időpontig • Application.Wait Now()+1/24/3600: a makró futása várjon 1 másodpercet • Szam = Year/Month/Day/Hour/Minute/Second(DatumIdo): a dátum részei számként • Szam = WeekDay(DatumIdo): a hét hányadik napja • (Továbbiakért lásd a szakirodalmat) Beépített kontroll függvények/eljárások: • MsgBox „UzenetString”: szöveges, OK-zandó üzenetdoboz a felhasználónak • Valtozo = InputBox(„Kerdes”,”Cím”, Type:=1): egyedi érték felhasználói inputja modális dialóguson (nem fér hozzá máshoz, amíg be nem csukja) A beépített Visual Basic és cellafüggvények viszonya: • Fontos megjegyezni, hogy a hasonló célú Visual Basic és cellafüggvény neve és paraméterezése általában NEM egyezik meg! • A helyzetet bonyolítja, hogy a Visual Basic kódban használhatjuk a cellafüggvényeket is, de csak az angol nevükön, hiába más nyelvű az Excelünk. Pl. az e5.89 kiszámítható: • Visual Basic függvényként: Valtozo = Exp(5.89) • Meghívott cellafügvényként: Valtozo = Application.WorkSheetFunction.Exp(5.89) (és nem .Kitevő(5.89)!!!), csak ez utóbbi sokkal lassabb. Cellafüggvényt akkor van értelme meghívni, ha valami komplexebb feladatot lát el, amit sokáig kellene kézzel programozni Visual Basicben (pl. Korreláció (Correl), Szórás (Stdev), Variancia (Var), stb.)

3-2.Gyakorló Feladat: Sakktábla színezés makróból • Az alapvető makró-programozás gyakorlására egy munkalapon kérjen be két 1..12 közti értéket, mint sorok és oszlopok számát, és színezzen ki sakktábla-szerűen egy megadott méretű táblázatot a cellák alapszínével! Megoldás: 3-2GyakorloMegoldas.xls Ctrl Shift R

Szakirodalom • MSDN Visual Basic for Applications Help: http://msdn.microsoft.com/hu-hu/library/aa269683(en-us,office.10).aspx

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