5. lecke: Az Excel programozása
| Cél: Az eddigi leckék általános ismereteket adtak a Visual Basic nyelvről, a Visual Basic Editor fejlesztőkörnyezetről, a vizuális tervezésről és általában a fejlesztés mikéntjéről, de nem használták az Excel objektumait, a VBA-ban "készen kapott" objektumhierarchia lehetőségeit. Ebben a fejezetben a fontosabb Excel objektumokról és azok forráskódból történő használatáról lesz szó. |
Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha képes |
- az Excel fontosabb objektumait Visual Basic programból használni.
|
Időszükséglet: A tananyag elsajátításához (a feladatok megoldásával együtt) hozzávetőlegesen 8 órára lesz szüksége. |
Kulcsfogalmak |
- Application
- Gyűjtemények
- Workbook
- Worksheet
- Range
- Cells
- A1 stílus
- R1C1 stílus
|
A használható objektumok, objektumtípusok |
Az objektumokhoz kötődő alapfogalmakról már olvashattunk (lásd előző lecke), most az Excel VBA környezetben használható objektumokról és objektumtípusokról lesz szó. |
A fejlesztőkörnyezetben egységesen használható osztályokat (illetve modulokat, szubrutinokat, típusokat) az őket tároló fájlok (illetve rendszerfájlok) csoportosítják (lásd 1. lecke 24. ábra ): |
- Excel osztályok (pl. Workbooks, Range, Chart, Dialogs)
- MS Forms osztályok (pl. TextBox, ListBox, UserForm)
- MS Office osztályok (pl. CommandBar, CommandBarControl)
- VBA osztályok (pl. Collection)
- VBA Project osztályok (az adott projektben definiált osztályok)
|
Megjegyzés |
- Az általunk használt objektumok (pl. Application, Debug) az Excel futásakor már létező (azaz hivatkozható) objektumok.
- Futásidőben is létrehozhatók új objektumok (pl. Worksheets.Add), illetve már létező objektumok megszüntethetők (pl. Charts(1).Delete).
- Az objektumtípusokat hasonlóan kezelhetjük, mint a VBA többi típusát
(pl. Dim r As Range). - Az Excel-munkafüzetbe (pl. *.xlsm) mentett makrók (pl. formok, modulok) jelszóval védhetők. A makrók csak futtathatók (ha azt nyitáskor engedélyezzük), de azok nem láthatók és nem is módosíthatók addig, amíg a megfelelő jelszót meg nem adjuk. A védelem a VBE Tools menüpontjának VBAProject properties... funkciójával a Protection lapon állítható be, és a dokumentum bezárása majd újranyitása után lép érvénybe.
|
Az Application objektum |
Az Application objektum az Excel alkalmazás objektuma. Segítségével az alkalmazáshoz tartozó összes objektum (pl. a nyitott munkafüzetek, projekt objektumok) elérhető. Az Application objektum feladata az alkalmazásszintű beállítások, tevékenységek végrehajtása is (pl. DisplayAlerts, ReferenceStyle). |
Az Application objektum néhány fontosabb tulajdonsága: Workbooks, Worksheets, Charts, Sheets, ActiveWorkbook, ActiveSheet, ActiveCell, ActiveChart, Range, Cells, Rows, Columns, Selection. |
Ha egy objektumot hivatkozunk, akkor meg kell adni az ő helyét az objektum hierarchiában (pl. Application.Workbooks("Munkafüzet1.xlsx")). Az objektum (a példában ez most az Application) és a hozzá tartozó, hivatkozott tulajdonság (a példában ez a Workbooks gyűjtemény) vagy metódus közé pontot kell tenni. Egy hivatkozás akár több objektumot is érinthet (pl. Workbooks("Munkafüzet1.xlsx").WorkSheets("Munka1").Range("A1")). |
Ha elhagyjuk az objektumot a hivatkozásból, akkor az Excel az aktuális (aktív) objektumokat használja. Ha például tudjuk, hogy az aktuális munkafüzet a Munkafüzet1, akkor az előző hivatkozást rövidebben is megadhatjuk: WorkSheets("Munka1").Range("A1"). Ha az aktuális munkalap éppen a Munka1, akkor a hivatkozás még rövidebb lehet: Range("A1"). |
Megjegyzés |
- A Workbooks tulajdonság az Application objektum megadása nélkül is használható (mert ugyanazt, az Application.Workbooks tulajdonságot hivatkozza), ezért többnyire így használjuk (lásd az előző példákat).
- Egy objektumra történő hivatkozás rendszerint többféleképpen is megadható a VBA-ban (pl. az aktuális munkalap aktuális cellája a következő hivatkozások bármelyikével hivatkozható: ActiveCell, Application.ActiveCell, ActiveWindow.ActiveCell, Application.ActiveWindow.ActiveCell).
- A Selection olyan speciális tulajdonság, amely az aktuálisan kijelölt objektumot adja eredményül, így az eredmény típusa attól függ, hogy éppen mi van kijelölve (pl. cellatartomány esetén egy Range objektum, diagram esetén egy Chart objektum lesz az eredmény).
|
Gyűjtemények |
A gyűjtemények azonos típusú objektumok egy összessége. Egy gyűjtemény elemeire indexekkel (1-től induló sorszámokkal) hivatkozhatunk (pl. Workbooks(1).Worksheets(1).Cells(1,1)). Ha egy gyűjtemény elemei rendelkeznek név (Name) tulajdonsággal, akkor az elemekre a nevükkel is hivatkozhatunk (pl. Workbooks("Munkafüzet1.xlsm").Worksheets("Munka1").Cells(1,1)). |
A gyűjtemények rendelkeznek Count tulajdonsággal, amivel a gyűjtemény elemeinek száma kapható meg, és többnyire Add metódussal is, amivel a gyűjtemény egy újabb elemmel bővíthető. A további tulajdonságok és metódusok (pl. az elemek törlésére szolgáló Delete metódus) létezése az egyes gyűjteményektől függ (pl. a Charts gyűjteménynek van ilyen metódusa, a Workbooks gyűjteménynek nincs). |
A fontosabb gyűjtemények: |
- Munkafüzetek: Workbooks gyűjtemény (munkafüzet (Workbook) objektumokból).
- Munkalapok: Worksheets gyűjtemény (munkalap (Worksheet) objektumokból).
- Diagramok: Charts gyűjtemény (diagram (Chart) objektumokból).
- Lapok (munkalapok, önálló lapon lévő diagramok stb.): Sheets gyűjtemény.
- Párbeszédablakok: Dialogs gyűjtemény (beépített párbeszédablak (Dialog) objektumokból).
- Menük: CommandBars gyűjtemény (menüsor (CommandBar) objektumokból).
|
Az Excel VBA megkülönbözteti tehát a lapokat (Sheets) és a munkalapokat (Worksheets), amit a magyar "szaknyelv" (pl. az Excel magyar nyelvű súgója) "összemos" (azaz minden lapot egyszerűen munkalapnak nevez), pedig a munkalapok csak azok a lapok, amelyek cellákat tartalmaznak. |
Megjegyzés |
- A gyűjteményeket könnyű felismerni a nevük végén lévő "s" betűről (angol többes szám).
- A Sheets gyűjtemény elemeinek (lapjainak) típusa a Type tulajdonsággal kapható meg (Pl. Sheets(1).Type).
- Az önálló lapon lévő diagramok a Charts gyűjteménnyel, míg az egyes munkalapokon lévő diagramok az adott munkalap ChartObjects gyűjteményével (pl. Worksheets(1).ChartObjects) kezelhetők.
|
A Range objektum |
A Range objektum cellatartomány kezelésére használatos. Az alábbiakban felsoroljuk a Range objektum néhány fontosabb tulajdonságát és metódusát, majd mintapéldákkal szemléltetjük ezek használatát. |
Tulajdonságok: Address, Areas, Cells, Column, Columns, ColumnWidth, Count, CurrentRegion, Font, Formula, FormulaLocal, FormulaR1C1, FormulaR1C1Local, Height, Name, NumberFormat, NumberFormatLocal, Offset, Range, Resize, Row, RowHeight, Rows, Value, Width. |
Metódusok: AutoFill, Clear, ClearFormats, Copy, Delete, Find, PasteSpecial, Select, Sort. |
Megjegyzés: A cellatartományra a blokk szó is használatos (mert rövidebb), de a két dolog nem pontosan ugyanazt jelenti. Blokkon ugyanis egy összefüggő, téglalap alakú cellatartományt értünk. Egy cellatartomány állhat több blokkból is, de ez fordítva nem igaz. |
Hivatkozás |
- Az A1 stílusú hivatkozás: a cellatartományt egy sztringben megadott kifejezéssel hivatkozzuk.
Pl. Range("A1"), Range("c2"), Range("A2:B3"), Range("A:A"), Range("1:1"), Range("A1:A5,C1:C5"), Range("A:A,C:D"). - Az R1C1 stílusú hivatkozás: a cellatartományt a sor- és oszlopindexek segítségével hivatkozzuk.
Pl. Cells(1,1), Cells(2,3), Range(Cells(2,1), Cells(3,2)), Columns(1), Rows(1).
|
Megjegyzés |
- A Range tulajdonság objektum nélkül használata (hasonlóan az ActiveCell tulajdonsághoz) hibát eredményez, ha az aktuális lap nem munkalap (WorkSheet).
- A Range tulajdonság paramétere egy cellatartomány neve is lehet (pl.
Range("Adatok"), ahol az Adatok egy (pl. a névkezelővel vagy egy cellatartomány Name tulajdonságával) definiált név. - Ha a Range tulajdonsággal R1C1 stílussal egy cellára szeretnénk hivatkozni (amit a Cells tulajdonsággal egyszerűbb hivatkozni, pl. Cells(3,2)), akkor is két cellát (a hivatkozott cellát, mint egy blokk két átellenes celláját) kell megadni
(pl. Range(Cells(3,2), Cells(3,2))).
|
Adatok kezelése: Value tulajdonság |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! A zöld színű megjegyzések csak magyarázó célzattal szerepelnek (és noha ugyanígy begépelhetők, felesleges őket az Immediate ablakban megadni)! |
'Az első munkafüzet első munkalapján az A1-es cellába adatot teszünk Application.Workbooks(1).Worksheets(1).Range("A1").Value = 5 'Az Adatok nevű (nyitott) munkafüzet aktuális (aktív) munkalapján 'a C2-es cellában lévő adat kiírása (az Immediate ablakba) Debug.Print Workbooks("Adatok").ActiveSheet.Range("c2").Value 'Az aktuális munkalap celláiba teszünk adatokat Range("A1").Value = "Maci Laci" 'Egy szöveg Range("a2").Value = 2*3 'Egy szám Cells(3,1).Value = Date 'Az aktuális dátum Cells(4,1).Value = CDate("2012.12.24") 'Egy adott dátum Cells(4,1).Value = "" 'Adat törlése |
Megjegyzés |
- A Value tulajdonság el is hagyható (pl. Range("A2") = 3.14).
- Egy cella üressége az IsEmpty függvénnyel kérdezhető le.
|
Tartalom törlése: ClearContents metódus |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap egy blokkjának tartalmát töröljük Range("A1:A4").ClearContents |
Formátum törlése: ClearFormats metódus |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap egy blokkjának formátumát töröljük Range("A1:A4").ClearFormats |
Tartalom és formátum törlése: Clear metódus |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap egy blokkjának tartalmát és formátumát töröljük Range("A1:A4").Clear |
Cellák törlése: Delete metódus |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap egy blokkjának celláit töröljük Range("A1:A4").Delete 'A jobbra lévő cellák balra lépnek Range("A1:D1").Delete 'A lenti cellák feljebb lépnek |
Megjegyzés: A Delete metódusnak van egy opcionális paramétere, amellyel megadható, hogy a törölt cellák helyére hogyan lépjenek be (jobbról vagy lentről) a szomszédos cellák. Ha a paramétert nem adjuk meg (mint a példában), akkor ezt a törölt objektum alakja alapján dönti el az Excel. |
Formátum beállítása: NumberFormat, NumberFormatLocal tulajdonság |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap A oszlopára dátum formátumot Range("A:A").NumberFormat = "yyyy.mm.dd" 'Ez ugyanazt csinálja, mint az előző sor Columns(1).NumberFormatLocal = "éééé.hh.nn" 'Az aktuális munkalap C és D oszlopára pénznem formátumot Range("C:D").NumberFormat = "#,##0 $" |
Kijelölés: Select metódus |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap A1:B3-as blokkjának kijelölése Application.ActiveSheet.Range("A1:B3").Select 'Ugyanaz rövidebben (kisbetűs hivatkozással) Range("a1:b3").Select |
Megjegyzés: Egy cellatartomány Select metódusa csak az aktuális munkafüzet aktuális munkalapján tud kijelölni, egyébként (más munkafüzetre vagy munkalapra hivatkozva) hibaüzenetet ad. |
Egy cellát tartalmazó összefüggő blokk: CurrentRegion tulajdonság |
Ezt a tulajdonságot rendszerint akkor használjuk, ha egy cellát tartalmazó összefüggő blokkra van szükségünk. Az összefüggő blokkban egy-egy cella lehet üres, de nem lehet benne teljesen üres sor/oszlop. |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap A1-es celláját tartalmazó blokk méretei s = Range("A1").CurrentRegion.Rows.Count 'Sorok száma o = Range("A1").CurrentRegion.Columns.Count 'Oszlopok száma |
Megjegyzés: A CurrentRegion tulajdonság blokkra is használható, ekkor a blokk bal felső sarokcelláját tartalmazó összefüggő blokkot kapjuk eredményül. |
Amint azt láttuk, az Excel munkalapokon tárolt adatok a cellák Value tulajdonságával elérhetők, azaz adatokat tudunk a cellákba tenni, és azokat fel tudjuk használni. Ha az adatokból valamilyen eredményadatokat szeretnénk meghatározni, akkor ezt általában nem programozzuk (pl. egy összeg esetén egy összegző algoritmussal), hanem az Excelt "kérjük meg" az eredmények kiszámítására (pl. használjuk a SZUM függvényt). Ennek két oka is van. Egyrészt az Excel-ben nagyon sok beépített függvény használható, másrészt ez a megoldás követi az adatok esetleges megváltozását (ha a képletek automatikus számítása (Fájl, Beállítások, Képletek, Számítási beállítások, Munkafüzet kiszámítása, Automatikus választógomb) be van kapcsolva, de ez általában bekapcsolt állapotú az Excelben). A programmal kiszámolt eredmények "frissüléséhez" ugyanis az eredményt számító forráskódot újra kell futtatni. |
Ahhoz, hogy forráskódból olyan Excel képleteket tudjunk az egyes cellákba tenni, amelyek a kívánt feladatot elvégzik (kiszámolják a megfelelő eredményeket a megfelelően hivatkozott cellatartományok adataiból), szükségünk van az Excel cellahivatkozásainak ismeretére. |
A cellahivatkozások stílusa az Excelben beállítható (lásd Fájl, Beállítások, Képletek, S1O1 hivatkozási stílus jelölőnégyzet). Általánosan elterjedt az A1 stílusú cellahivatkozás, amely az oszlopokat egy vagy több betűvel, míg a sorokat sorszámokkal jelöli. Beállítható azonban az ún. S1O1 stílusú cellahivatkozás is, ahol az oszlopok is sorszámokkal azonosíthatók. |
Megjegyzés: Az S1O1 stílusú hivatkozásban az S betű a sort, az O betű az oszlopot jelenti. Az S1O1 cellahivatkozási stílus angol elnevezésében (R1C1 style) R jelenti a sort (row), C pedig az oszlopot (column). |
A VBA környezetben az Excel cellahivatkozási stílusát az Application objektum ReferenceStyle tulajdonsága tárolja, így a cellahivatkozási stílus lekérdezése, illetve beállítása ezzel a tulajdonsággal történhet. |
Pl. |
'Lekérdezés If Application.ReferenceStyle = xlR1C1 Then MsgBox ("Az Excel S1O1 stílusú cellahivatkozást használ") Else MsgBox ("Az Excel A1 stílusú cellahivatkozást használ") End If 'Beállítás Application.ReferenceStyle = xlA1 |
Az S1O1 cellahivatkozási stílusban a cellákra a sorok és oszlopok sorszámával hivatkozunk. A cellahivatkozások ugyanúgy lehetnek abszolút, relatív és vegyes hivatkozások, mint az A1 stílus esetén. A relatív hivatkozásnál a megadott sorszámokat szögletes zárójelbe kell tenni, az abszolút hivatkozásnál nem. A relatív hivatkozás mindig a hivatkozást tartalmazó cellához képest relatív, ahhoz viszonyítva értendő. Ha a hivatkozást tartalmazó cella sorát, illetve oszlopát szeretnénk hivatkozni, akkor nem kell sorszámot megadnunk. |
Az alábbiakban az S1O1 cellahivatkozási stílusokra adunk példákat (ahol a ~ karakter után megadjuk az S1O1 stílusú cellahivatkozás A1 stílusú megfelelőjét). |
- Abszolút hivatkozás (pl. S1O1 ~ $A$1; S12O3 ~ $C$12).
- Relatív hivatkozás (pl. SO[1] ~ ugyanaz a sor, az oszlop az eggyel jobbra lévő; S[-1]O ~ a sor az eggyel feljebb lévő, az oszlop ugyanaz).
- Vegyes hivatkozás (pl. S2O[1] ~ abszolút sor (a második), relatív oszlop (az eggyel jobbra lévő); SO1 ~ relatív sor (ugyanaz a sor), abszolút oszlop (az első)).
|
Egy cellatartomány hivatkozásának lekérdezése: Address tulajdonság |
Az Address tulajdonság használatának (egyszerűsített) szintaktikája: |
expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, RelativeTo) |
RowAbsolute | Az eredményhivatkozás sor abszolút legyen-e (alapértelmezésben True). | ColumnAbsolute | Az eredményhivatkozás oszlop abszolút legyen-e (alapértelmezésben True). | ReferenceStyle | Az eredményhivatkozás stílusa (xlA1 vagy xlR1C1, alapértelmezésben xlA1). | RelativeTo | Az R1C1 típusú eredményhivatkozás viszonyítási Range objektuma. |
|
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Az aktuális munkalap egy cellájának hivatkozása A1 stílusban MsgBox Cells(2,3).Address '$C$2 MsgBox Cells(2,3).Address(True, False) 'C$2 MsgBox Cells(2,3).Address(False, True) '$C2 MsgBox Cells(2,3).Address(False, False) 'C2 |
'Az aktuális munkalap egy cellájának címe R1C1 stílusban MsgBox Cells(2,3).Address(ReferenceStyle:=xlR1C1) 'R2C3 MsgBox Cells(2,3).Address(ReferenceStyle:=xlR1C1, _ RowAbsolute:=False, ColumnAbsolute:=False, _ RelativeTo:=Cells(1,1)) 'R[1]C[2] |
Megjegyzés: Ha egy utasítást több sorba írunk, akkor az alulvonás karaktert (_) kell azon sorok végére tenni, amelyek még folytatódnak (lásd a fenti példában). |
Képletek használata: Formula, FormulaLocal tulajdonságok |
Mindkét tulajdonság képlet megadására használatos. A képletet egy sztringkifejezéssel definiálhatjuk, lényegében ugyanúgy, mint ahogy azt az Excelben is megadnánk. |
A Formula tulajdonságban az Excel függvényeire az angol nevükkel kell hivatkozni (az Excel súgója tartalmazza a függvények angol megnevezését és szintaxisát is), a FormulaLocal tulajdonság esetén pedig azon a nyelven, amit az Excelben is használunk. |
A képletek könnyen megadhatók abban az esetben, ha tudjuk, hogy melyik cellába akarjuk a képletet elhelyezni, és ha az alkalmazni kívánt képletben ismerjük az esetlegesen hivatkozott cellatartományt. Nehezebb a dolgunk, ha nem tudjuk a képletet tartalmazó cella címét akkor, amikor a forráskódot írjuk (mert pl. ez függ a munkalapon lévő adatsorok, adatoszlopok számától). |
A következőkben mindkét esetre mutatunk példát. Az első két utasítás ismert célcellába ismert cellatartományra hivatkozó képletet tesz. A másik két képlet elhelyezésekor nem tudjuk az adatokat tartalmazó blokk méretét, csak azt, hogy az A1-es cellától kezdődik az az összefüggő blokk, amelyre ki akarunk számolni valamit (példánkban átlagot és összeget számolunk). A CurrentRegion tulajdonság segítségével megtudható az adatokat tartalmazó blokk mérete. Az Address tulajdonság segítségével megkaphatók azok a (relatív hivatkozású) cellacímek, amelyek a képletekhez (az első oszlop átlagához és az első sor összegéhez) kellenek. |
Az aktuális munkalapon az A1:D4 blokkba gépeljen be számokat, majd próbálja ki az alábbi utasítások végrehajtását egy szubrutin segítségével! Futtassa le többször a szubrutint és nézze meg a munkalap változásait (a keletkező új képleteket)! |
'Az aktuális munkalapon fix képletet fix helyre Range("C5").Formula = "=SUM(C1:C4)" Range("D5").FormulaLocal = "=SZUM(D1:D4)" 'Az A1-es cellát tartalmazó blokk alá az első oszlopba s = Range("A1").CurrentRegion.Rows.Count st = Cells(s, 1).Address(False, False) Cells(s + 1, 1).FormulaLocal = "=ÁTLAG(A1:" + st + ")" 'Az A1-es cellát tartalmazó blokk mellé az első sorba o = Range("A1").CurrentRegion.Columns.Count st = Cells(1, o).Address(False, False) Cells(1, o + 1).FormulaLocal = "=SZUM(A1:" + st + ")" |
Képletek használata: FormulaR1C1, FormulaR1C1Local tulajdonságok |
Ez a két tulajdonság olyan képletek megadására használható, amelyekben a cellatartományokra az R1C1 cellahivatkozási stílussal hivatkozunk. A következő példák mindegyike a C2-es cellába helyez el egy képletet, amely egy cella tartalmának kétszeresét számolja ki. A cella, amire hivatkozunk, a hivatkozástól függően változik. A C2-es cellába kerülő A1 hivatkozási stílusú képletet a sorok végén található megjegyzésekben láthatjuk. |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását, majd minden egyes értékadás után nézze meg a C2-es cellába kerülő képletet (az aktuális munkalapon)! |
'Egy egyszerű képletet a C2-es cellába Range("C2").FormulaR1C1 = "=R1C1*2" ' "=$A$1*2" Range("C2").FormulaR1C1 = "=RC1*2" ' "=$A2*2" Range("C2").FormulaR1C1 = "=R1C*2" ' "=C$1*2" Range("C2").FormulaR1C1 = "=R[1]C[-1]*2" ' "=B3*2" 'A FormulaR1C1Local tulajdonságban az S és O betűk szerepelnek Range("C2").FormulaR1C1Local = "=S1O1*2" ' "=$A$1*2" Range("C2").FormulaR1C1Local = "=S[1]O[-1]*2" ' "=B3*2" |
Megjegyzés |
- A példákban most nem használtuk az Address tulajdonságot, de a hivatkozott cella R1C1 stílusú címe ezzel is előállítható lett volna.
- Az Excelben a függvénynevek magyarul (helyi [local] nyelven) jelennek meg akkor is, ha a Formula, illetve FormulaR1C1 tulajdonságokkal definiáljuk őket.
- A képleteket kezelő tulajdonságok (Formula, FormulaLocal, FormulaR1C1, FormulaR1C1Local) egyikének megadásával mindegyik definiálódik.
|
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
Range("C2").FormulaLocal = "=ÁTLAG(A2:B2)" MsgBox Range("C2").FormulaR1C1 ' =AVERAGE(RC[-2]:RC[-1] |
Képletek másolása: Copy, AutoFill, PasteSpecial metódusok |
A képleteket tartalmazó cellákat gyakran másoljuk, hogy ne kelljen azokat többször megadni. A másolás az Excelben többféle módon is elvégezhető (pl. a vágóasztal segíségével, a másolandó cella jobb alsó sarkánál lévő kitöltőjel segítségével), így ezt forráskódból is többféleképpen végezhetjük. |
Egészítse ki a korábban készített (lásd Képletek használata) szubrutin tartalmát a következő programrészlettel, majd futtassa a szubrutint (akár lépésenkénti végrehajtással (pl. F8 billentyű), a változók (pl. s,o) tartalmának megfigyelésével)! Próbálja ki a szubrutin működését úgy is, hogy a C5 és D5 cellákba képletet tévő utasításokat kitörli vagy megjegyzésbe rakja! |
'Az s+1. sor 1. oszlopában lévő képlet másolása az s+1. sor 'oszlopaiba a 2. oszloptól az o. oszlopig Cells(s + 1, 1).Copy Destination:= _ Range(Cells(s + 1, 2), Cells(s + 1, o)) 'Az 1. sor o+1. oszlopában lévő képlet másolása az o+1. oszlop 'soraiba a 2. sortól az s. sorig 'AutoFill esetén a céltartománynak a forrást is tartalmaznia kell Cells(1, o + 1).AutoFill Destination:= _ Range(Cells(1, o + 1), Cells(s, o + 1)) 'Mint az előző, csak másolással és beillesztéssel Cells(1, o + 1).Copy 'Beillesztés Range(Cells(2, o + 1), Cells(s, o + 1)).PasteSpecial 'A forrástartományt jelölő (villogó) szegély levétele Application.CutCopyMode = False |
Egyéb lehetőségek: Rows, Columns, Cells, Range, Offset, Resize |
A Range objektum fenti tulajdonságai Range objektumot adnak eredményül. Az első négy tulajdonság jelentése és használata értelemszerű (csak most az adott blokkra és nem az aktuális munkalapra vonatkoznak, mint ahogyan azt korábban használtuk); az Offset tulajdonsággal egy, az adott Range objektumhoz képest relatív elmozdulással nyert Range objektum hivatkozható; a Resize tulajdonsággal pedig átméretezhető egy adott blokk. A példákban az egyes tulajdonságok eredményeként kapott Range objektumoknak a Select metódusát hívjuk meg (amellyel látható lesz az eredmény). Az utolsó példa az A1-es cella egy feleslegesen cifra hivatkozását szemlélteti. |
Próbálja ki az Immediate ablak segítségével a következők végrehajtását! |
'Egy Range objektum egy sora, illetve egy oszlopa Range("C3:D6").Rows(2).Select Range("C3:D6").Columns(2).Select 'Egy Range objektum egy cellája Range("C3:D6").Cells(1, 2).Select 'Ugyanazt jelöli ki, mint az előző Range(Cells(3, 3), Cells(6, 4)).Range("B1").Select 'Egy Range objektum egy Range objektuma Range("C3:D6").Range("A2:B2").Select 'Egy Range objektumból relatív elmozdulással nyert Range objektum Range("C3:D6").Offset(-1, 1).Select 'Egy Range objektum átméretezése Cells(2,2).Resize(3,2).Select 'Range objektumok egyesítése Application.Union(Columns(1), Columns(4)).Select 'Az A1-es cella egy feleslegesen cifra hivatkozása Range("A1").Cells(1,1).Offset(0,0).Resize(1,1).Cells(1).Select |
Megjegyzés: A Cells tulajdonság az eddig látott két index (sor-, illetve oszlopindex) helyett egy index megadásával is használható, akkor az index a cellák sorfolytonos, azon belül oszlopfolytonos sorrendje szerint értendő (pl. a Range("C3:D6").Cells(3) hivatkozás a munkalap C4-es celláját hivatkozza). |
A WorksheetFunction objektum |
Az előző fejezetben láttuk, hogyan helyezhetünk el képleteket az egyes cellákba. Ezzel a megoldással az Excel végzi a számolásokat és az eredmények megjelenítését (a képleteket tartalmazó cellákban). Ekkor tehát bizonyos cellák tartalma módosul. Előfordulhat azonban olyan eset, amikor ez a megoldás nehezebben realizálható (pl. ha az adatokat tartalmazó munkalap védett, akkor egy másik (nem védett, akár egy másik munkafüzethez tartozó) munkalap szükséges a megoldáshoz). |
Az Excel munkalapfüggvényei azonban nemcsak a cellákban elhelyezett képletekben használhatók. A forráskódból meghívható munkalapfüggvényeket a WorkSheetFunction tároló (container) objektum foglalja egy logikai egységbe. Az objektum függvénymetódusai az egyes munkalapfüggvények, amelyek paraméterei egyaránt lehetnek munkalapok Range objektumai, illetve memóriaváltozók (pl. egy tömbváltozó). |
A függvénymetódusok hasonlóan hívhatók, mint a VB függvényei (a hívás bárhol állhat, ahol az eredmény típusának megfelelő érték állhat), így az eredmény megkapható a cellák módosítása nélkül is (igaz, az adatok módosulása esetén a számolást végző forráskódot újra kell futtatni). |
A forráskódban az objektumtípusok hasonlóan használhatók, mint a VB többi adattípusa. Az objektumok értékadó utasítása a Set (nem opcionális) kulcsszóval kezdődik (szemben az eddig használt értékadás Let kulcsszavával, ami elhagyható volt). |
Az objektumok értékadó utasításának (egyszerűsített) szintaktikája: |
Set objectvar = objectexpression |
Az értéket kapó objektumváltozónak (objectvar) ugyanolyan típusúnak kell lennie, mint az objektumkifejezés (objectexpression) eredményeként előálló objektumnak. |
Az alábbi példa a WorksheetFunction objektum használata mellett az objektumok értékadását is bemutatja. |
Ellenőrizze, hogy legyen a munkafüzetben egy "Munka1" nevű munkalap, majd egy modul segítségével próbálja ki az alábbi szubrutinok működését! |
Az első szubrutint futtassa különböző adatok (pl. számok, szövegek, dátumok) megadása mellett, majd úgy is, hogy nincsenek adatok az A1:C5 blokkban! Írja át a szubrutint (az eredeti működés megtartása mellett) úgy, hogy nem használja a Range típusú objektumváltozót (r), és a hivatkozásokat a lehető legrövidebben adja meg! |
A második szubrutint módosítsa úgy, hogy más egész számokat (ne 1-től 10-ig az egész számokat, hanem pl. input adatokat), és más függvényt (mint a Sum) használ! |
'Munkalapfüggvény használata cellatartományra Sub MunkalapFgv1() Dim r As Range, min As Variant Set r = Application.Worksheets("Munka1").Range("A1:C5") min = Application.WorkSheetFunction.Min(r) MsgBox min End Sub 'Munkalapfüggvény használata változókra Sub MunkalapFgv2() Dim a(1 To 10) As Integer, i As Integer For i = 1 To 10: a(i) = i: Next MsgBox WorkSheetFunction.Sum(a) '55 End Sub |
Megjegyzés |
- Az egyes munkalapokra kérhető olyan beállítás is, amely a képleteket tartalmazó cellákban magát a képletet és nem az eredmény értékét jeleníti meg (lásd Fájl, Beállítások, Speciális, Beállítások megjelenítése ehhez a munkalaphoz, Számított eredmények helyett képletek megjelenítése a cellákban jelölőnégyzet).
- Általában akkor használunk külön objektumváltozót (a példában r), ha az adott objektummal több dolgot is el szeretnénk végezni. A példában csak a Min munkalapfüggvényt hívjuk meg egy adott blokkra, ami az objektumváltozó nélkül is megtehető lett volna.
- A példában szereplő min változó Variant típusú, mert az eredmény típusa a megfelelő cellákban (A1:C5) található adatok típusától függ.
|
Feladatok |
1. A súgó használata |
Indítsuk el a Visual Basic Editorban található súgót, majd az Excel Object Model Reference témakörben nézzük meg következő objektumokhoz tartozó oldalakat: Application, Workbook, Worksheet, Range, Chart. Az objektumok tulajdonságaihoz (Properties) és metódusaihoz (Methods) tartozó oldalakba is nézzünk bele! A következő gyűjtemények súgóoldalait is nézzük meg: Workbooks, Worksheets, Sheets, Charts! |
2. Az Application objektum |
Az Application objektum segítségével jelenítsük meg a következő adatokat: a nyitott munkafüzetek száma, az aktuális munkafüzet munkalapjainak száma, az első munkalap neve, az aktuális munkafüzet neve; az aktuális munkalap neve. Használjuk az Immediate ablakot, illetve készítsünk szubrutint a megoldásra! |
3. A Range objektum |
Range objektumok segítségével végezzük el az alábbi feladatokat! Az aktuális munkafüzet aktuális munkalapján dolgozzunk! Hasonlóan az előző feladathoz, próbáljuk ki az utasítások közvetlen végrehajtásával (Immediate ablak), illetve a szubrutinnal történő megoldást is! Az adatok megjelenítéséhez a MsgBox függvényt vagy a Debug.Print metódust használjuk! |
1. Jelöljük ki a következő cellatartományokat mind a két hivatkozási stílus (A1, illetve R1C1) használatával! A C5-ös cella; az 5. sor 3. oszlopbeli cella; a C3:D5 blokk; a 2. sor 3. oszlop bal felső sarkú és az 5. sor 6. oszlop jobb alsó sarkú blokk; az első sor; a 35. oszlop; a 3. sor; az A és a D oszlop. |
2. Tegyünk a cellákba: számot; szöveget; dátumot; az aktuális dátumot; különböző kifejezések eredményét, majd jelenítsük meg a cellák értékét! |
3. Állítsunk be: egy tetszés szerinti dátumformát; pénznem formátumot; százalékos megjelenítést; általános formátumot, illetve jelenítsünk meg cellaformátumokat! |
4. Töröljünk adatot, formátumot, illetve mindkettőt megadott cellatartományokban! Próbáljuk ki a Range objektum Delete metódusát is! |
5. Jelenítsük meg különböző cellatartományok Address tulajdonságát! Nézzük meg az A1 stílusú hivatkozás mind a négy lehetséges esetét! |
6. Helyezzünk el képleteket egy-egy cellába az alábbiak szerint! Jelenítsük meg egy képletet tartalmazó cella képletét (az összes képlet tulajdonság kipróbálásával), illetve a cellában lévő eredményértéket! |
- Egy adott cellába egy olyan képletet, amely: nem használ cellahivatkozást; használ cellahivatkozást; munkalapfüggvényt használ.
- Az A1-es cellát tartalmazó összefüggő blokk első sorának legkisebb elemét a blokk utáni oszlop első sorába.
- Az A1-es cellát tartalmazó összefüggő blokk első oszlopának legnagyobb elemét a blokk utáni sor első oszlopába.
|
7. Az előző feladatban (dinamikusan) elhelyezett képleteket másoljuk végig a képlet sorában, illetve oszlopában úgy, hogy az A1-es cellát tartalmazó összefüggő blokk összes sorára megkapjuk a sorminimumokat, illetve az összes oszlopára megkapjuk az oszlopmaximokat! |
4. Munkalapfüggvények |
Számoljuk ki az Application objektum WorksheetFunction tulajdonságának segítségével egy adott cellatartomány adataira a következőket, és az eredményeket jelenítsük meg! |
1. Elemek átlaga; elemek összege; legkisebb elem; legnagyobb elem. |