Az Excel Makroprogramozsa Vltozk Vltozk deklarcija csak Option

Az Excel Makroprogramozása

Változók • Változók deklarációja (csak Option Explicit esetén) • {Dim|Public|Private|Protected|Static} vá 1 tozó 1 <adattípus>, változó 2 As <adattípus> • A VBA-ban kétféle érvényességi (láthatósági) kört különböztethetünk meg: • eljárásszintű • modulszintű • Eljárásszintű láthatóság esetében a változót kívülről nem érhetjük el, annak a metódusnak a sajátja, amelyben deklarálva van. A deklarációs kulcsszó a Dim vagy Static. A két deklaráció között a különbség a memóriahasználatnál van, míg Static kúlcsszóval deklarált változó az alprogram lefutása után a változó értéke a memóriában marad, míg a Dim kulcsszóval deklarált változó értéke megszűnik. • Modulszintű érvényességi kör esetében private és protected és public érvényességi szintű változókat deklarálhatunk. Private esetben a változó csak az őt tartalmazó modulból érhető el, public esetben pedig bárhonnan. A Protected-nek csak öröklődés esetén van szerepe. • Fontos megjegyezni, hogy a típusok deklarálása sem eljárások sem függvények esetében nem kötelező, az első értékadás határozza meg a változók típusát. 2

Változó típusok • • Integer egész szám Single lebegőpontos szám Double duplapontosságú lebegőpontos szám String karaktersorozat Boolean logikai (true, false) Date dátum Variant szöveg, szám vagy dátum (automatikus típus) Object objektum 3

Vezérlési szerkezetek a programokban • Szekvencia – egymás utáni lépések végrehajtása • Szelekció – elágazás (egyágú, kétágú, háromágú többágú) • Iteráció – ciklus (előírt lépésszámú, feltételes elöl vagy hátultesztelő) 4

Egy – két és háromágú elágazás If 1. feltétel Then 1. utasítások (ha a 1. feltétel igaz) [Else. If 2. feltétel 2. utasítások] (amennyiben a 2. feltétel igaz) [Else 3. utasítások] (minden egyéb esetben) End If 5

Többágú elágazás – értéktől függő Select Case <kifejezés> [Case <1. kifejezés> <1. utasítások>] [Case <2. kifejezés> <2. utasítások>] … [Case Else <különben utasítások>] End Select 6

Feltételes operátorok • Feltételek összeállításához használható operátorok: relációs operátorok: < ; > ; <= ; >= ; <> logikai operátorok: And ; Or ; Not ; Xor 7

Előírt lépésszámú ciklus - For ciklusváltozó =ettől To eddig [Step +/lépésköz] [utasítások] Next [ciklusváltozó] 8

Előírt lépésszámú ciklus – For Each valtozo In Collection [utasítások] Next valtozo 9

Feltételes ciklus - elöltesztelő • Amennyiben nem tudjuk előre megmondani, hogy hányszor kell lefuttatnunk az utasítássorozatot, akkor a feltételes ciklusok valamelyikét kell használnunk. Egy feltételes ciklus lehet elöl- illetve hátultesztelő. • Elöltesztelő ciklusnál a feltétel kiértékelése a ciklus elején a Do While kulcsszavak után történik. Elöltesztelő ciklus esetben a feltétel teljesülése esetén futnak le a ciklusmagban szereplő utasítások. Do While feltétel [utasítások] Loop 10

Feltételes ciklus - hátultesztelő • Hátultesztelő ciklusnál a feltétel kiértékelése a ciklus végén a Loop Until kulcsszavak után történik. Mindaddig történik az ismétlés, amíg a feltétel hamis. A hátultesztelő ciklus esetében a ciklusmagban lévő utasítások legalább egyszer biztosan lefutnak. Do [utasítások] Loop Until feltétel 11

Üzenet ablakok • A program futása során a felhasználóval történő kapcsolattartás legpraktikusabb módja az üzenőablakok használata. • Adatbekérésre az Input. Box függvényt, míg üzenetek, válaszok küldésére a Msg. Box függvényt használhatjuk. • Az Input. Box szintaxisa: • változó = Input. Box("szöveg", "címke") • A Msg. Box szintaxisa: • Msg. Box („üzenet”) vagy Msg. Box „üzenet” • változó = Msg. Box ("üzenet", paneltípus) • vb. OKOnly - OK gomb; vb. OKCancel - Ok és Mégse gombok; vb. Abort. Retry. Ignore - Leállítás, Ismét és Kihagyás gombok; vb. Yes. No. Cancel - Igen, Nem és Mégse gombok; vb. Yes. No - Igen és Nem gombok 12

Eljárások • Mivel a VBA egy objektumorientált nyelv, utasításainkat eljárásokba és függvényekbe szervezhetjük. • Minden egyes eljárást a Sub utasítással kell kezdenünk, majd a makró nevét kell megadnunk és végül zárójelek között a futáshoz szükséges paraméterlistát. Az eljárásokat az End Sub utasítással zárjuk. Sub <eljárásnév>([paraméterlista]) [<deklarációk>] <utasítások> End Sub Eljárás hívása: [Call] <eljárásnév> ([aktuális paraméterlista]) 13

Függvények • Minden egyes függvényt a Function utasítással kell kezdenünk, majd a függvény nevét kell megadnunk végül zárójelek között a futáshoz szükséges paraméterlistát. A függvényeket az End Function utasítással zárjuk. • Minden függvénynek kötelezően tartalmaznia kell legalább egy olyan értékadó utasítást, amelynek bal oldalán a függvény neve szerepel. • Function <függvénynév>([paraméterlista]) • [<deklarációk>] • <utasítások> • függvénynév = (legalább egy értékadó utasítás) • End Function • Függvény hívása: <függvénynév> ([aktuális paraméterlista]) 14

Megjegyzések • Megjegyzéseket a program kódjába a sor elején vagy akár sorban elhelyezett aposztróffal (’) tehetünk. • A Visual Basic zöld színnel jelöli a megjegyzéseket. Célszerű minél több, és minél részletesebb megjegyzést elhelyezni a programkódban, hiszen ezzel egyrészt áttekinthetőbbé tehetjük, másrészt megkönnyíthetjük a kód későbbi esetleges újraértelmezését. 15

Makro rögzítése 16

Makro szerkesztő ablak • Amennyiben a Szerkesztés gombra kattintunk a Visual Basic szerkesztőben megjelenik a makrónk VBA kódja. Váltás szerkesztőnézetbe (Visual Basic Editor-ba) az Alt + F 11 billentyűkombinációval is történhet. 17

Visual Basic editor kezelőfelülete 18

Munkafüzetek kezelése • Munkafüzet megnyitása: • Workbooks. Open (file) • Munkafüzet nevének megadása felhasználóbarát módszerrel: • file = Application. Get. Open. Filename() • Üzenetablak kezelése: If file = "False" Then Msg. Box ("Nem választott vagy adott meg fájlnevet!") /A szöveg zárójel nélkül is megadható Else Workbooks. Open (file) End If 19

Munkafüzetek kezelése • Munkafüzet bezárása: • Workbooks(2). Close (a zárójelben a munkalap indexe, hányadik munkafüzet a megnyitottak közül) • Aktív munkafüzet bezárása: • Active. Workbook. Close 20

Munkalapok kezelése • Munkalapok számának kiírása: • Msg. Box "A munkalapok száma=" & Worksheets. Count • Új munkalap hozzáadása: • Active. Workbook. Sheets. Add Before : =Worksheets(Worksheets. Count) – utolsó munkalap elé • Active. Workbook. Sheets. Add After : =Worksheets(Worksheets. Count) – utolsó munkalap után • Active. Workbook. Sheets. Add Count : =5 - öt új munkalapot szúr be • Munkalap törlése: • Worksheets(index). Delete • Munkalap átnevezése: • Activesheet. Name 21

Többágú elágazások kezelése Select Case valasz Case 1: Active. Workbook. Sheets. Add Before: =Worksheets(Worksheets. Count) Case 2: Active. Workbook. Sheets. Add After: =Worksheets(Worksheets. Count) Case 3: darab = Input. Box("Hány új munalapot adjak hozzá? ", "Hozzáaad", "5") Active. Workbook. Sheets. Add Count: =Val(darab) Case Else: Msg. Box "1 -3 közötti számot adjon meg!" End Select 22

Munkafüzetben lévő munkalapok kiírása For Each munkafuzet In Workbooks szoveg = szoveg & "Munkafüzet: " & munkafuzet. Name & vb. New. Line & "Munkalapok: " & vb. New. Line For Each lap In munkafuzet. Worksheets szoveg = szoveg & lap. Name & vb. New. Line Next lap Next munkafuzet Msg. Box szoveg 23

Képernyő villogás ideiglenes kikapcsolása kiírás idejére • Application. Screen. Updating = False //Kiíráskor ne villogjon a képernyő • Application. Screen. Updating = True //Visszakapcsolás 24

Könyvtár- és fájlkezelés • Könyvtár megadása: • konyvtar = "C: UsersvaralDocuments„ • Fájl megadása: • Fajlnev = Dir(konyvtar & "*. xl*") • Munkafüzet megnyitása az adott könyvtárban: • Workbooks. Open (konyvtar & Fajlnev) • Ciklusban végigjárható a könyvtár és megkeresi az Excel fájlokat: For Each Munkalap In Workbooks(Fajlnev). Worksheets Cells(i, j). Value = Munkalap. Name j = j + 1 Next Munkalap 25

Tartomány és cellaműveletek For i = 1 To 25 Cells(i, 1). Value = i // az aktuális ciklusváltozó értékével tölti fel az 1. oszlopot Range("B 1: B 25"). Value = i //25 -tel tölti fel a végén a B oszlopot Range("C 1: C 25"). Value = "=Rand. Between(1, 25)” // véletlen számokkal tölti fel a C oszlopot Next i Range(Cells(1, 4), Cells(7, 4)). Value = "=Rand. Between(10, 20)” // 10 -20 közötti számokkal tölti fel a D oszlop első 26 7 sorát

Tartomány és cellaműveletek Range("Árak"). Value = "=Rand. Between(100, 250)" //Előtte E 1: E 10 tartományt elnevezni "Áraknak„ Set minta = Range("F 1: H 6") 'Tartomány deklaráció minta. Value = 14 minta. Select 'Tartomány kijelölése 27

Sor illetve oszlop kijelölés • Set probasor = Range("A 1: C 25") • probasor. Rows(Val(sor)). Select //Tartományon sor kijelölés, ha nincs ott a probasor, akkor az egész sort kijelöli! Set probaoszlop = Range("A 1: C 25") probaoszlop. Columns(Val(oszlop)). Select //Tartományon oszlop kijelölés, ha nincs ott a probaoszlop, akkor az egész sort kijelöli! 28

Tartomány másolása Range("A 1: A 2"). Selection. Copy Range("G 15"). Select Active. Sheet. Paste Range("G 17: G 18"). Value = Range("A 1: A 2"). Value 'Ugyanez egy sorban 29

Kijelölt tartomány celláinak megszámlálása Set tartomany = Range("A 1: C 25"). Select Msg. Box ("A tartomány celláinak száma=" & tartomany. Count) Msg. Box ("Sorok száma a tartományban=" & tartomany. Rows. Count) //Sorok száma Msg. Box ("Oszlopok száma a tartományban=" & tartomany. Columns. Count) //Oszlopok száma 30

Tartomány törlése valasz = Msg. Box("Valóban törölni akasr minden elemet? ", vb. OKCancel + vb. Question + vb. Default. Button 2, "Kérdés") If valasz = vb. OK Then Range("A 1: C 25"). Clear. Contents 31

Számolás és cellába írás Range("D 26"). Value = "Összeg" Range("D 27"). Value = "Átlag" Range("D 28"). Value = "Darab" Range("D 29"). Value = "Minimum" Range("D 30"). Value = "Maximum" Range("A 26"). Value = "=SUM(A 1: A 25)" Range("A 27"). Value = "=AVERAGE(A 1: A 25)" Range("A 28"). Value = "=COUNT(A 1: A 25)" Range("A 29"). Value = "=MIN(A 1: A 25)" Range("A 30"). Value = "=MAX(A 1: A 25)" 32

Keretezés és színezés Range("F 1: H 6"). Interior. Color = vb. Green Range("F 1: H 6"). Borders. Weight = xl. Thick Range("F 1: H 6"). Borders. Line. Style = xl. Solid //xl. Dash vagy xl. Dot is lehet, ha nincs semmi, akkor xl. None 33

Színes betűs cellák összege osszeg = 0 For i = 1 To 12 If Cells(i, 15). Font. Color = vb. Red Then osszeg = osszeg + Cells(i, 15). Value End If Next i Msg. Box "Az O-oszlop piros számainak összege = " & osszeg 34

Üres cellák törlése nem folytonos oszlopból szamlalo = 0 For i = 1 To 24 If Cells(i, 16). Value <> "" Then Cells(szamlalo + 1, 17). Value = Cells(i, 16). Value szamlalo = szamlalo + 1 End If Next i 35

Tartomány sorbarendezése Set Rng = Range("S 1"). Current. Region For i = 1 To Rng. Count For j = i + 1 To Rng. Count If Rng. Cells(j) < Rng. Cells(i) Then temp = Rng. Cells(i) = Rng. Cells(j) = temp End If Next j Next i 36

Ismétlődések megkeresése oszlopban és eltávolításuk Cells(1, 22). Value = Cells(1, 21). Value egyediszam = 1 hozzaad = True For i = 2 To 10 For j = 1 To egyediszam If Cells(i, 21). Value = Cells(j, 22). Value Then hozzaad = False End If Next j If hozzaad = True Then Cells(egyediszam + 1, 22). Value = Cells(i, 21). Value egyediszam = egyediszam + 1 End If hozzaad = True Next i 37

Lépésközök For ciklusban (STEP) For i = 1 To 16 Step 2 Cells(i, 25). Value = 100 Next i For j = 25 To 13 Step -2 Cells(20, j). Value = 50 Next j 38

Hibakezelés Set Rng = Selection For Each cell In Rng On Error Resume Next cell. Value = Sqr(cell. Value) Next cell 39

Adatok fájlba írása fajlnev = Application. Default. File. Path & "adatok. txt" Set tartomany = Selection Open fajlnev For Output As #1 For i = 1 To tartomany. Rows. Count For j = 1 To tartomany. Columns. Count cellaertek = tartomany. Cells(i, j). Value If j = tartomany. Columns. Count Then Write #1, cellaertek Else Write #1, cellaertek, End If Next j Next i Close #1 40

Adatok fájlból olvasása • • i = 35 • • • masodik = Right(sor, Len(sor) - pozicio) fajlnev = Application. Get. Open. Filename() Open fajlnev For Input As #1 Do While Not EOF(1) //Fájl vége jelig olvasunk, mert nem tudjuk hány adat van benne Line Input #1, sor pozicio = In. Str(sor, ", ") elso = Left(sor, pozicio - 1) //Vesszőt, mai az elválasztó megkeressük és annál elvágjuk az adatokat egymástól Cells(i, 1). Value = Val(elso) Cells(i, 2). Value = Val(masodik) i = i + 1 Loop Close #1 41