Ugrás a fő tartalomra

VBA alapok Excel makró 2






Az excel makró egy Visual Basic modulban tárolt program, vagy parancs- és függvénysorozat, előre rögzített tevékenységek sora mely elindulása eseményhez kötött.

A makrók a dokumentumhoz kapcsolódva tárolódnak, szerves részét képezik, ezért vírusok formájában veszélyt is jelenthetnek.

Makrók készítésének legegyszerűbb megoldása a makrók rögzítése. Felvételt készítünk egy tevékenységsorozatról
( lépésenként eltároljuk az egyes parancsok végrehajtásának adatait),
 majd visszajátszással újra és újra elvégezzük a kívánt művelet sort.

 Előnyök:
 – Az Excel sokkal gyorsabban képes végrehajtani a megadott
parancsokat.
– Minden alkalommal hibátlanul végzi el a rábízott feladatokat.
– Parancsbillentyűk hozzárendelésével még hatékonyabbá tehetjük a
makrók visszajátszását, és ezzel jelentősen meggyorsítjuk az
amúgy több, mint két billentyűleütést igénylő feladatok elvégzését.




Változók

Változók deklarációja (csak Option Explicit esetén)
{Dim|Public|Private|Protected|Static} vá1tozó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.



Táblázat és makró kapcsolata
Excel elnevezés                            Makró kódból hivatkozva
A B3 cella                                    Cells(3,2) vagy
                                                      Range(“B3”)
A C4:G6 tartomány                       Range(”C4:G6”) vagy
                                                       Range(Cells(4,”C”),Cells(6,”G”))
A B oszlop                                       Columns(2)
A H,I,J oszlopokból álló tartomány  Range(Columns(8),Columns(10))
A 2. sor                                           Rows(2)
A 13-tól 16. sorig tartó tartomány Range(Rows(13),Rows(16))
A munkalap összes cellája             Cells
Meghatározott munkalap cellái     Sheets(”Munka1”).Cells vagy
                                                       Sheets(1).Cells
Az éppen aktuális cella                   ActiveCell
Az éppen kijelölt objektum             Selection
Egy cella kijelölése                          Range(“A1”).Select
Kijelölt cellák tartalmának törlése   Selection.ClearContents

Betűtípus Arial-ra állítása      Cells(1,1).Font.Name = ”Arial”
Betűszín pirosra állítása         Cells(1,1).Font.Color = vbRed
                                               Cells(1,1).Font.ColorIndex = 3
Betűméret 12-esre állítása       Cells(1,1).Font.Size = 12
Félkövér betűre                       Cells(1,1).Font.Bold = True
Dőlt betűre                              Cells(1,1).Font.Italic = True


Példák:
Cells(1,1) = "Lajos
Range("B2")=1962




Ü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 InputBox függvényt, míg üzenetek, válaszok küldésére a MsgBox függvényt használhatjuk.


Az InputBox szintaxisa:
változó = InputBox("szöveg","címke")

A MsgBox szintaxisa:
MsgBox („üzenet”) vagy MsgBox „üzenet”
változó = MsgBox ("üzenet", paneltípus)
vbOKOnly - OK gomb;
vbOKCancel - Ok és Mégse gombok;
vbAbortRetryIgnore -  Leállítás, Ismét és Kihagyás gombok;
vbYesNoCancel - Igen, Nem és Mégse gombok;
vbYesNo - Igen és Nem gombok




-- Számokkal való műveletek:
– Aritmetikai:
+
-
*
\ egész osztás
/ osztás, az eredmény
valós mod maradékot adja:
a mod 2

– Relációk:
>
<
>=
<=
<>

– Standard függvények:
Int(d) – a d valós adat egészrésze.
Round(d) – a d valós adat egészre kerekített értéke.
Sqr(d) – d négyzetgyöke

– Rnd – véletlenszám a [0..1) intervallumból Randomize – Véletlenszám generátor inicializálása.
– IsNumeric(kifejezés)
– IsEmpty(kifejezés)
– IsNull(kifejezés)
– IsDate(kifejezés)
– Val(string) – szövegből számot konvertál

-- Műveletek Szöveg változókkal

– Standard függvények:
Chr(egész)
Asc(string)
Len(string)
Left, Right, Mid
Ltrim(string)
Rtrim(string)
Trim(string)

- Szövegek összefűzése:
&
vigyázat, szóközök közé tanácsos írni!


- Relációk:
(mint számoknál)

Logikai változókkal
Numerikus érték: 0 esetén hamis, minden más igaz.

-Logikai műveletek:
And
Or
Not




-- Program (vezérlési) szerkezetek:


  • 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átul tesztelő)



If feltétel Then
    utasítások
Else
    utasítások
End If


Select Case valasztas
      Case 1: ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
      Case 2: ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
      Case 3: darab = InputBox("Hány új munalapot adjak hozzá?", "Hozzáaad", "5")
                                 ActiveWorkbook.Sheets.Add Count:=Val(darab)
      Case Else: MsgBox "1-3 közötti számot adjon meg!"

End Select


For ciklusváltozó = kezd To veg Step lepes
   utasítások
Next ciklusváltozó




Do While feltétel
   utasítás(ok)
Loop




Do
    utasítás(ok)
Loop Until feltétel




-- Program egységek

-- Eljárás
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])




-- Függvény
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])


Megjegyzéseket a program kódjába a sor elején vagy akár sorban elhelyezett aposztróffal (’) tehetünk. 






Példák:
Sub van_e2()
' van-e nulla a 
Do Until ActiveCell.Value = "" Or ActiveCell.Value = "0"
    ActiveCell.Offset(1, 0).Select
Loop

If ActiveCell.Value = "0" Then
MsgBox ("Van nulla!")
Else
MsgBox ("Nincs nulla!")
End if

End Sub

---
Sub megszamolas()
' 100 -nál nagyobb számok megszámolása
Dim db As Integer 'a db nevű egész szám típusú változó definiálása

Do Until ActiveCell.Value = ""
    If ActiveCell.Value > 100 Then db = db + 1
    ActiveCell.Offset(1, 0).Select
Loop
MsgBox (db & " darab 100-nál nagyobb szám van.")
End Sub


---
 'szines cellák összege  
   osszeg = 0
   For i = 1 To 12
     If Cells(i, 15).Font.Color = vbRed Then
        osszeg = osszeg + Cells(i, 15).Value
     End If
   Next i
   MsgBox "Az O-oszlop piros számainak összege = " & osszeg


---

Sub maxkivalasztas()
" legnaygobb kiválasztása
Dim max As Integer
max = -1 'kezdőérték adás, aminél remélhetőleg cask nagyobb elemek vannak a tömbben
Do Until ActiveCell.Value = ""
    If ActiveCell.Value > max Then max = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
Loop
MsgBox (max & " a legnagyobb szám a tömbben.")

End Sub

---
' üres cellák törlése 
 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

   ' Tartomány sorbarendezése
   Set Rng = Range("S1").CurrentRegion
   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(i) = Rng.Cells(j)
            Rng.Cells(j) = temp
        End If
     Next j
   Next i

   
   ' Ismétlődések megkeresése oszlopban és eltávolítás
 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


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


'Adatok fájlba írása
fajlnev = Application.DefaultFilePath & "\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

   
   ' Adatok fájlból olvasása
   i = 35
   fajlnev = Application.GetOpenFilename()
   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 = InStr(sor, ",")
        elso = Left(sor, pozicio - 1)    //Vesszőt, mai az elválasztó megkeressük és annál elvágjuk az adatokat egymástól
        masodik = Right(sor, Len(sor) - pozicio)
        Cells(i, 1).Value = Val(elso)
        Cells(i, 2).Value = Val(masodik)
        i = i + 1
    Loop

   Close #1


------------------------
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:
ActiveWorkbook.Close

Munkalapok számának kiírása:
MsgBox "A munkalapok száma=" & Worksheets.Count

Új munkalap hozzáadása:
ActiveWorkbook.Sheets.Add Before :=Worksheets(Worksheets.Count) – utolsó munkalap elé
ActiveWorkbook.Sheets.Add After :=Worksheets(Worksheets.Count) – utolsó munkalap után
ActiveWorkbook.Sheets.Add Count :=5 - öt új munkalapot szúr be

Munkalap törlése:
Worksheets(index).Delete

Munkalap átnevezése:
Activesheet.Name

Application.ScreenUpdating = False     //Kiíráskor ne villogjon a képernyő
Application.ScreenUpdating = True     //Visszakapcsolás


   Range("A1:A2").Select
   Selection.Copy

   Range("G15").Select
   ActiveSheet.Paste
   
   Range("G17:G18").Value = Range("A1:A2").Value 'Ugyanez egy sorban

   
   Keretezés
   Range("F1:H6").Interior.Color = vbGreen
   Range("F1:H6").Borders.Weight = xlThick
   Range("F1:H6").Borders.LineStyle = xlSolid  

   //xlDash vagy xlDot is lehet, ha nincs semmi, akkor xlNone




Megjegyzések