E42-101
VBA mintafeladat
Mi van az Excel látható felülete mögött? 1. Milyen nyelveket ért meg az Excel? Alapvetően kétféle nyelven társaloghatunk az Excellel. Vagy jelnyelvvel, vagy a Visual Basic programozási nyelven. Az első esetben vagy az egeret ( ) vagy a BILLENTYŰZETET ( ) használhatjuk erre a célra. Ez utóbbi nagyon fontos, mert a gyorsbillentyűkkel sok olyan dolog elvégezhető, ami a billentyűzettel nem!!! Feltétlenül érdemes megtanulni néhány gyorsbillentyűt: Az aktív cella melletti jobb cellába lépés Az aktív cella melletti bal cellába lépés Az aktív cella alatti cellába lépés Az aktív cella feletti cellába lépés Ugrás az aktív sor utolsó érvényes cellájába Ugrás az aktív sor első érvényes cellájába Ugrás az aktív oszlop legfelső érvényes cellájába Ugrás az aktív oszlop legalsó érvényes cellájába Ugrás az A oszlopba Ugrás az A1 cellába A menüt a billentyűzetről úgy használhatjuk, hogy az billentyűt lenyomva tartva megnyomjuk a File menüt nyitja le. A lenyíló menüben a azt a betűt, amelyik aláhúzva látható. Például kurzormozgató nyilakkal ( , ) mozoghatunk le- illetve fölfele, majd a megfelelőn tartózkodva az -t lenyomjuk, illetve a megfelelő aláhúzott betűt lenyomva (itt most az nélkül!!!) rögtön -os a megfelelő alpontot nyithatjuk meg. Az esetlegesen feljövő mellékablakban szintén az billentyűpárossal válogathatunk vagy a -bal járhatjuk körbe az egyes mezőket, nyomógombokat. A jelnyelv minden esetben szegényesebb, mint egy beszélt nyelv. Az igaz az Excel esetében is! ☺ Az Office táblázatkezelője esetében a gazdagabb, árnyaltabb kommunikációra a Visual Basic nyelv nyújt lehetőséget. Segítségével mindazon műveletek elvégezhetők, mint amik a menürendszerben megtalálhatók, de emellett bonyolultabb algoritmusokat (elágazások, ciklusok) is megszervezhetünk. Lásd a Visual Basic alapok című segédletet!
D E B C GD GE GC GB R GR
L
F Ff
BC
I
F
F
2. Tanítsuk az Excelt! Az Excel képes megjegyezni, hogy mit csinálunk egy adott probléma megoldása közben. Az Office táblázatkezelőjének fogalmai szerint makrót rögzíthetünk, és ezt a makrót bármikor újból lejátszhatjuk. Mivel a makró műveleteit semmi sem felügyeli, ezért veszélyesek is lehetnek az ismeretlen rutinok (makróvírus!!!). Emiatt alaphelyzetben az Excel nem engedi meg makrók futtatását. Ezt külön kell engedélyeznünk: Eszközök Makró Biztonság…. Ajánlatos a Közepes szintet beállítani. Ilyen esetben az Excel rákérdez a makrók végrehajtása előtt az engedélyre. 1. ábra: Makrófuttatás engedélyezése
E42-101
VBA mintafeladat
Példaképpen tanítsuk meg az Excelt osztályozni! Az alaptáblázat (IF_Osztalyozo_v.0.xls) tizenöt nevet és a hozzájuk tartozó pontszámban megmutatkozó eredményeket tartalmazza. Ezeket az értékeket kell százalékos teljesítménnyé konvertálni, mert az érdemjegyet a százalékos teljesítmény alapján állapítjuk meg. A teljesítményt egészre kerekített százalékos értékben mérjük. A kerekítést a matematika szabályai szerint végezzük. A kerekítésre létezik egy Excelfüggvény a KEREKÍT(x1;x2). Az első paraméterként a kerekítendő számot, másodikként egy nem negatív egész értéket kell megadnunk. Ez a második paraméter azt mutatja meg, hogy hány tizedesre kerekítünk – ha 0-t adunk meg, akkor egészre történik meg a kerekítés. Számítsuk ki ezt az értéket az első személynél, és rögzítsük is, hogy mit csináltunk. A számítás elvégzése előtt kapcsoljuk be a makrórögzítőt: Eszközök Makró Új makró rögzítése…. A rögzítést megelőzően meg kell adni a makró paramétereit: Makrónév: ez alapján tudjuk megkülönböztetni a makróinkat, ha egy munkafüzetben többet is létrehozunk. Jó, ha a makró neve „beszédes”, vagyis utal arra, hogy mit is csinál a rutin. Billentyűparancs: gyorsbillentyű a makróhoz, melynek segítségével nem csak menüből válogatva, hanem a megadott billentyűkombinációt leütve is elindul a rutin. Alaphelyzetben csak a Ctrl+ látható, de célszerűbb a Ctrl+Shift-et használni, mert a Ctrl-kombiná2. ábra: Makró inicializálása ciók már foglaltak a Windows-os felületen, és ha újradefiniáljuk azokat, akkor a régi jelentésük elveszik. A makró helye: meghatározza azt, hogy elérhetik-e más Excel munkafüzetek is a megírt makrónkat vagy csak arra a m,unkafüzetre korlátozzuk az érvényességét, amelyben rögzítettük. Leírás: a rögzítő személyére és a rögzítés időpontját ponotsíthatjuk. A makró inicializálása után megjelenik a kezelőpanel: . A jobb oldali nyomógombbal beállíthatjuk, hogy a használt cellahivatkozások abszolútak vagy realítvak legyenek-e. Alaphelyzetben abszolút hivatkozásokat rögzít a makrókezelő, bekapcsolva (narancsos háttér) pedig relatívakat. Mi ez utóbbit válasszuk ki! Írjuk be a képletet: =KEREKÍT(B5/$I$5*100;0). Majd a kezelőpanel bal oldali gombjára kattintva fejezzük be a makró rögzítését! Ellenőrizzük le a makrólistát: Eszközök Makró Makrók…. Valóban a listában ott van az Osztályoz, és az gombra kattintva újból lefut a műveletsorozat, és a második személynél is megjelenik a teljesítményszázalék. A –t lenyomva is működik a dolog!!! De mi is működik? Nézzünk az Excel lepedője mögé!
HGo
3. Nézzünk az Excel lepedője mögé! Az Excel 256 oszlopból és 65 536 sorból álló hatalmas, lepedőnyi méretű íve sok mindent eltakar, de van segítség, hogy mögéje nézzünk: Eszközök Makró Visual Basic Editor. A megnyíló panelben ezt olvashatjuk: Option Explicit Sub Osztályoz() ' Osztályoz Makró ' Rögzítette: Facskó Ferenc, dátum: ' Billentyűparancs: Ctrl+Shift+O ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]/R5C9*100,0)" ActiveCell.Offset(1, 0).Range("A1").Select End Sub
Mit is csináltunk manuálisan? Létrehoztunk egy Osztályoz nevű makrót. Ennek az eredménye lett a Sub - End Sub utasításpár. 2
Osztályoz()
E42-101
VBA mintafeladat
A makró paraméterei a zölddel színezett komment-sorokban vannak:
' Osztályoz Makró ' Rögzítette: Facskó Ferenc, dátum: 2007.10.28. ' Billentyűparancs: Ctrl+Shift+O A beírt képletet a ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]/R5C9*100,0)"
sor tartalmazza. A Visual Basic objektum alapú nyelv. Ezekben a nyelvekben minden objektum egy csomó tulajdonsággal bír, és egy csomó dolgot tud megcsinálni. Ennél az utasításnál az aktív cellába (ahol a cellakurzor áll, amelyik cella ki van választva) (ActiveCell) egy képletet (formulát) írunk, mely úgy néz ki, hogy: =ROUND(RC[-1]/R5C9*100,0). A ROUND a KEREKÍT angol nyelvű megfelelője. A programozási nyelvekben az egyes elemeket nem szokás lefordítani, itt tehát a magyar nyelvű excel-függvény lefordítódott angol nyelvű VBA függvénnyé. A ROUND ugyanúgy paraméterezendő, mint a KEREKÍT. Csak a cellahivatkozások változtak meg. A „szokásos” excel-stílus, amikor az oszlop egy betűvel a sort pedig egy számmal azonosítjuk átvált egy másik rendszerre. Egyszerűbb elsőre megérteni az R5C9 hivatkozást. Ebben az esetben a sort (Row) és az oszlopot (Column) is számmal azonosítjuk. Tehát az R5C9 az ötödik sort és kilencedik oszlopot jelöli (abszolút módon!). He ezt visszakeressük az Excel-táblázatban, akkor ez I5 mezőnek felel meg, ahol a maximális pontszámot helyeztük el. Az RC[-1] hivatkozás egy relatív hivatkozás. Azt jelöli, hogy egy olyan celláról van szó, amelyik ugyanabban a sorban van, mint az aktív cella (R után nincs semmi), de eggyel előtte lévő oszlopban helyezkedik el (az oszlop C koordinátája eggyel kisebb). Az angol nyelvű függvényeknél a paraméterlistát nem pontosvesszővel, hanem vesszővel szeparáljuk. A képlet beírása után, amikor az –t lenyomtuk egy sorral lejjebb ment a cellakurzor: ActiveCell.Offset(1, 0).Range("A1").Select. Megint csak az aktív cellával csinálunk valamit (ActiveCell). Mit is csináltunk? Pontosabban fogalmazva: mi is csinálódott? Mozdult (Offset) egy sorral lefele, de oldalra az oszlopok között nem: ((1, 0)). Az elmozdulást tehát paramétereznünk kell. A mozgásnak két paramétere van. Az első a sorok közötti mozgás (felülről lefele pozitív az irány – alulról felfele negatív), a második az oszlopok közötti mozgás (balról jobbra pozitív az irány – jobbról balra negatív). A Range("A1") a relatív mozgást jelenti. A Select az új cella aktívvá tételét jelenti.
L
4. Tanítsuk meg többre az Excelünket! A makrónk egyetlen cellára tudja kiszámítani a teljesítményértéket. Miért ne mondanánk meg neki, hogy ezt csinálja meg tizenötször? Töltse be az IF_Osztalyozo_v.1.xls fájlt, és nyissa meg a Visual Basic szerkesztőt! Egészítse ki az Osztályozó makrót a következőképpen: Option Explicit Sub Osztályoz() ' Osztályoz Makró ' Rögzítette: Facskó Ferenc, dátum: 2007.10.28. ' Billentyűparancs: Ctrl+Shift+O Dim I As Integer For I = 1 To 15 ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]/R5C9*100,0)" ActiveCell.Offset(1, 0).Range("A1").Select Next I End Sub
A megváltozott sorokat piros nyilacskákkal jelöltem meg. Az algoritmus lényegi része a For – Next utasításpár. A For-ban megmondjuk, hogy hányszor kell megismételni egy műveletsort. Annyiszor, amíg az I értéke megváltozik 1-ről 15-re (egyesével lépegetve – erre vonatkozóan nem adtunk külön utasítást, és mivel ez nem történt meg, ezért lép érvénybe az egyesével történő lépegetés). A Next mutatja meg, hogy meddig tart az a programrészlet, amit ismételgetni kell. 3
E42-101
VBA mintafeladat
A Dim sor amiatt kell, mert a For-ban használunk egy memóriaterületet a RAM-ból. Hogy ne kelljen megjegyeznünk a memóriarekesz sorszámát, ezért egy szimbolikus nevet (I) rendelünk hozzá a Dim-mel. A Dim-ben azt is megadjuk, hogy mekkora legyen az I változónak lefoglalt terület. Esetünkben csak egész számokat akarunk használni, tehát megfelelő számunkra az Integer típus. –ra, akkor (wow!) minden személyre előállítódik a teljesítHa most rácsapunk a mény-érték. ☺ Hogyan dolgoznánk ezek után manuálisan. Fölfele lépnénk egy mezővel, majd a -lel felugranánk az oszlop tetejére. Az első lépés VBA kódját kikövetkeztethetjük. Ismerjük a lefele lépés utasítását: ActiveCell.Offset(1, 0).Range("A1").Select . A felfele lépés nem lehet más, mint a ActiveCell.Offset(-1, 0).Range("A1").Select. A módosított kódunk:
HGo
GC
Option Explicit Sub Osztályoz() ' Osztályoz Makró ' Rögzítette: Facskó Ferenc, dátum: ' Billentyűparancs: Ctrl+Shift+O Dim I As Integer For I = 1 To 15 ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]/R5C9*100,0)" ActiveCell.Offset(1, 0).Range("A1").Select Next I ActiveCell.Offset(-1, 0).Range("A1").Select End Sub
Mentsünk, futtassunk. ☺ Mi lehet vajon az UgorjunkAzOszlopLegfelsőElemére művelet utasítása? Meg lehet keresni a VBA kézikönyvben, de meg is kérdezhetjük az Exceltől. Rögzítsünk egy új makrót, amely nem csinál mást, mint a tetőre ugrik. Ebből kiderül, hogy az általunk keresett utasítás a Selection.End(xlUp).Select. Tehát a módosított kód: Option Explicit Sub Osztályoz() ' Osztályoz Makró ' Rögzítette: Facskó Ferenc, dátum: ' Billentyűparancs: Ctrl+Shift+O Dim I As Integer For I = 1 To 15 ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]/R5C9*100,0)" ActiveCell.Offset(1, 0).Range("A1").Select Next I ActiveCell.Offset(-1, 0).Range("A1").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select End Sub Az ActiveCell.Offset(1, 0).Range("A1").Select azért került be a kódba, mert a Selection.End(xlUp).Select fejléc soráig repítette a cellamutatót, de nekünk az értékeket
tartalmazó sorokkal lesz dolgunk, tehát eggyel lejjebb kellett szállnunk.
4
E42-101
VBA mintafeladat
5. Osztályozzunk végre! Hogyan végeznénk el az osztályozás manuálisan? 1. Megnézzük a teljesítmény-százalékot. 2. Jobbra lépünk az érdemjegy-cellába. 3. Ha a teljesítmény nem nagyobb, mint 50%, akkor beírunk egy elégtelent; ha a teljesítmény 51% és 65% közötti, akkor beírunk egy elégségest ha a teljesítmény 66% és 75% közötti, akkor beírunk egy közepest ha a teljesítmény 76% és 90% közötti, akkor beírunk egy jót ha a teljesítmény 90% fölötti, akkor beírunk egy jelest. 4. Mindezt ismételjük, amíg minden személyre be nem írtuk az érdemjegyet. Hogy is néz ki ez VBA kódban? Töltse be az IF_Osztalyozo_v.3.xls fájlt, és nézze meg! Option Explicit Sub Osztályoz() ' Osztályoz Makró ' Rögzítette: Facskó Ferenc, dátum: ' Billentyűparancs: Ctrl+Shift+O Dim I As Integer Dim Telj As Integer For I = 1 To 15 ActiveCell.FormulaR1C1 = "=ROUND(RC[-1]/R5C9*100,0)" ActiveCell.Offset(1, 0).Range("A1").Select Next I ActiveCell.Offset(-1, 0).Range("A1").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select Telj = ActiveCell Do While Telj <> 0 ActiveCell.Offset(0, 1).Range("A1").Select If Telj <= 50 Then ActiveCell = "elégtelen (1)" End If If (Telj > 50 And Telj <= 65) Then ActiveCell = "elégséges (2)" End If If (Telj > 65 And Telj <= 75) Then ActiveCell = "közepes (3)" End If If (Telj > 75 And Telj <= 90) Then ActiveCell = "jó (4)" End If If Telj > 90 Then ActiveCell = "jeles (5)" End If ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.Offset(0, -1).Range("A1").Select Telj = ActiveCell Loop End Sub
Hogyan működik és mit csinál a kód? Nézzük meg! Helyezzük el az excel-táblát és a VBA editort, hogy ez utóbbi legyen felül, de azért lássuk a táblázatot is. Nyomjuk le az -at. Ettől kezdve a Visual Basic lépésenként hajtja végre a kódot. A végrehajtás alatt lévő sor hátterét sárgával kiszínezi. A következő utasítást az újbóli lenyomására hajtja végre. Vegyük észre, hogy lesznek olyan sorok, amelyekre nem ugrik rá a sárga sáv (’, Dim), ezek ugyanis nem végrehajtható utasítások. Közben azt is ellenőrizhetjük, hogy az egyes változók épp milyen értéket tárolnak. Ezt úgy tekinthetjük meg, ha az egérkurzort a változó neve fölött megpihentetjük.
5
E42-101
VBA mintafeladat
Ebben a kódrészletben szintén ismételnünk kell egy utasítás-sorozatot, de ennek megszervezésére most egy másik eszközt vetetettünk be, Do While - Loop-ot. Itt nem alkalmaztunk számlálót, ami megmondja a számítógépnek, hogy hányszor kell ismételni. Itt egy végfeltételt adtunk meg: Telj <> 0, vagyis mindaddig újra és újra végre kell hajtani a Do While… és a Loop közötti részt, amíg teljesül az a föltétel, hogy a Telj értéke nullától különböző. Persze ez föltételezi azt, hogy a Telj értékét valahol a cikluson belül beállítjuk, változtatjuk. Vannak olyan műveletek, amit nem kell feltétlenül végrehajtani. Az elégtelen (1) szöveget csak akkor kell beírni a meghatározott cellába, ha a teljesítmény nem haladja meg az 50%-ot. Ezt így fogalmazhatjuk meg a Visual Basic nyelvén: If Telj <= 50 Then ActiveCell = "elégtelen (1)" End If
Az If szócska után adjuk meg a feltételt. Ha a feltétel kiértékelés igaz értéket ad eredményként, akkor végre kell hajtani a Then szócska utáni utasítást vagy utasításokat. A feltételes utasítás végét az End If jelzi. Az If mögött nem csak egyetlen feltétel állhat, hanem több is. Ebben az esetben az egyes feltételeket logikai művelettel kell összekötni. Például: Telj > 65 And Telj <= 75. Eléggé általános a kódunk? Sajnos nem. Nézzük meg, milyen hibái vannak: 1. Csak akkor működik helyesen, ha az Osztályoz makró indításakor a cellakurzor az első személy Teljesítmény celláján áll. 2. A makró csak tizenöt személyre végzi el a Teljesítmény értékének kiszámolását, mert a For ciklusban ezt fogalmaztuk meg. 3. Az érdemjegy beírása megáll, ha egy Teljesítmény értéke 0% (0 pont). Javítsuk ki ezeket a hibákat! Töltse be az IF_Osztalyozo_v.4.xls fájlt, és nézze meg! 1. Nevezzük el azokat a cellákat, amelyek kitüntetettek: a. A maximális pontszám legyen Max. b. A kezdő név legyen Start. 2. A teljesítmény értékének kiszámolását tegyük be a Do While – Loop ciklusba. 3. A ciklus végét ne az üres numerikus mező (egyenlő a 0 értékű numerikusmezővel), hanem az üres Név-mező jelezze. Az ugyanis elképzelhető, hogy egy dolgozat 0 pont értékű, de névtelen vizsgázó nem lehet. ☺ Ennek megfelelően a javított kódunk: Option Explicit Sub Osztályoz() ' Osztályoz Makró ' Rögzítette: Facskó Ferenc, dátum: 2007.10.28. ' Billentyűparancs: Ctrl+Shift+O ' ' Több, azonos típusú változót egyetlen Dim-ben is lefoglalhatunk: Dim Telj, Maximum, Pont As Integer Dim Nev As String ' Abszolút ugrás az általunk Max-nak nevezett cellára: Range("Max").Select ' Az ide beírt maximális pontszám eltárolása: Maximum = ActiveCell ' Abszolút ugrás a táblázat legelső nevére: Range("Start").Select Nev = ActiveCell Do While Nev <> "" ' Lépés a Név mellé jobbra, a pontszámot tartalmazó mezőre: ActiveCell.Offset(0, 1).Range("A1").Select ' A dolgozat pontszámának eltárolása: Pont = ActiveCell ' Lépés jobbra az üres mezőre, ahova a teljesítményszázalék kerül: ActiveCell.Offset(0, 1).Range("A1").Select
6
E42-101
VBA mintafeladat
' Teljesítmény kiszámolása: Telj = Round(Pont / Maximum * 100, 0) ' Teljesítmény értékének beírása az aktív cellába: ActiveCell = Telj ' Lépés jobbra, az üres mezőbe, ahova az érdemjegy kerül: ActiveCell.Offset(0, 1).Range("A1").Select ' Érdemjegy megállpítása és beírása: If Telj <= 50 Then ActiveCell = "elégtelen (1)" End If If (Telj > 50 And Telj <= 65) Then ActiveCell = "elégséges (2)" End If If (Telj > 65 And Telj <= 75) Then ActiveCell = "közepes (3)" End If If (Telj > 75 And Telj <= 90) Then ActiveCell = "jó (4)" End If If Telj > 90 Then ActiveCell = "jeles (5)" End If ' Lépés egy sorral lejjebb és három cellát balra a következő névre: ActiveCell.Offset(1, -3).Range("A1").Select Nev = ActiveCell Loop End Sub
6. Nehéz megtanulni programozni?
Nem. DE: gyakorolni, GYAKOROLNI, GYAKOROLNI!!! A Visual Basic ad segítséget: Színezi a programkódot, és ha egy általunk helyesnek vélt utasítás nem kékül ki, akkor az nem helyes! Az utasítás begépelése közben az -et lenyomva helyzet-érzékeny súgót kapunk. Az utasítás begépelése közben felbukkanó felhőben mindig láthatjuk az adott pozíción lehetséges elemeket. A programkód látványa mutatja az algoritmus szerkezetét. Nyomkövetés lehetősége ( ).
Ajánlott irodalom: Visual Basic Alapok (a tantárgy honlapján a 9. oktatási hét segédlete) Kovalcsik Géza (2007): Az Excel programozása. ComputerBooks Kiadó Kft., Budapest
7
