KURZUS: Számítási módszerek

MODUL: Excel programozás

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)
RowAbsoluteAz eredményhivatkozás sor abszolút legyen-e (alapértelmezésben True).
ColumnAbsoluteAz eredményhivatkozás oszlop abszolút legyen-e (alapértelmezésben True).
ReferenceStyleAz eredményhivatkozás stílusa (xlA1 vagy xlR1C1, alapértelmezésben xlA1).
RelativeToAz 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.

Önellenőrző kérdések
1. Az alábbi állítások közül melyek igazak az Excel VBA környezetben használható objektumokkal és objektumtípusokkal kapcsolatosan?
Az osztályok egységesen használhatók, függetlenül attól, hogy melyik rendszerfájlban találhatók.
A gyűjtemények azonos típusú objektumok összessége.
Az olyan gyűjteményekben, amelyben az elemeknek van Name tulajdonsága, az elemek a nevükkel is kiválaszthatók.
A WorkSheets gyűjtemény magában foglalja a Sheets gyűjteményt.
A Sheets gyűjtemény magában foglalja a Charts gyűjteményt.
A Charts gyűjtemény magában foglalja a ChartObjects gyűjteményt.
A Range szó osztályt is és tulajdonságot is jelöl.
2. Az alábbi állítások közül melyek igazak a Range objektummal kapcsolatosan?
Az A1 stílusú hivatkozásban a cellatartományt a sor- és oszlopindexekkel hivatkozzuk.
A Range objektumnak létezik Range tulajdonsága, amely egy Range objektumot ad eredményül.
A Cells gyűjtemény egy eleme egy index segítségével is hivatkozható.
A Cells gyűjtemény egy eleme Range típusú objektum.
A Formula tulajdonság megadásakor az Excel függvények angol neveit kell használnunk.
A választ kisbetűvel adja meg, felesleges szóközök nélkül!
3. Adja meg az alábbi adat hivatkozását!

Az aktuális munkalapon a C3:D5 blokk sorainak száma.

FONTOS: Az adatmegadásnál csak kisbetűket használjon! Ne használjon felesleges, a hivatkozásból elhagyható részeket! A blokkot a bal felső és a jobb alsó sarokcellájával (ebben a sorrendben) R1C1 stílussal hivatkozza!

A hivatkozás:

A választ kisbetűvel adja meg, felesleges szóközök nélkül!
4. Adjon metódushívást az alábbi tevékenység végrehajtására!

Az aktuális munkalapon a B3:D4 blokk formátumának (és csak annak) a törlése.

FONTOS: Az adatmegadásnál csak kisbetűket használjon! Ne használjon felesleges, a hivatkozásból elhagyható részeket! A blokkot a bal felső és a jobb alsó sarokcellájával (ebben a sorrendben) A1 stílussal hivatkozza!

A metódushivás: