10. Lecke – VBA programozás alapok összefoglaló

VBA alapok ismétlés

Amiket eddig megtanultunk használni

1. MsgBox parancs
2. Makró rögzítő
3. Worksheets, Range, Cells, Select, Value
4. Változók, matematikai műveletek
5. “If” elágazás, logikai vizsgálatok
6. “For” ciklus
7. Tömbök
8. Több dimenziós tömbök
9. Funkciók

A következő leckék többségében ezeknek az elemeknek a segítségével fogjuk majd felépíteni az automatizált Excel riportjainkat. Aktívan fogjuk használni VBA tudásunkat, és kombinálni a fent említett elemeket.

Azonban mielőtt tovább lépünk gyakoroljunk! Oldjunk a következő gyakorló példát, és áldozzuk ezt az alkalmat az ismétlésnek!

Ismétlő feladat

A következő feladat egy összetett ismétlő feladat lesz, melyben átvesszük a legfontosabb tanultakat. A teljes feladat pontokra van bontva, az egyes pontok megoldását, a lecke végén találod.

0. Előkészület

Töltsük le az alábbi txt fájlt, ami egy előre elkészített adatbázist tartalmaz. Majd másoljuk be a tartalmát egy új Excel táblába, a munkalap neve legyen “Adatforrás” a “Munka1” helyett. Ez lesz a kiinduló pontunk.

LETÖLTÉS

VBA alapok ismétlés - feladat

1. Új munkalap

Hozzunk létre egy új munkalapot, amit nevezzünk el “Irányító panel”-nak. Erre fogjuk tenni a saját gombjainkat, amelyek a makróinkat fogják végrehajtani.
Első feladatunk legyen az, hogy helyezzünk el egy gombot ezen az új munkalapon, amelyet megnyomva automatikusan létrehozunk egy új munkalapot.

Segítség: Makrórögzítőt használva hozzuk létre az új munkalapot, majd az így kapott kódot tudjuk felhasználni. De ha még profibban akarod emgoldani akkor olvasd el ezt a posztot is.

2. ID alapján keresés

Nehezítünk, ha egyedül nem is sikerül megoldani, próbáld meg majd megérteni a működését!

Szeretnénk, hogy ha az “Irányító panel” A1 cellájába beírunk egy ID-t, akkor azt megkeresse nekünk a program. Ha van olyan ID az Adatforrás munkalap első oszlopában, akkor az újonnan létrehozandó munkalap A1 cellájába kiírja az adot ID-hez tatozó teljes nevet, ha nincs, akkor azt írja ki, hogy “Nincs ilyen ID”.

Segítség: Egy “For” ciklus fog kelleni, amivel az Adatforrás első oszlopában futtatjuk végig a sorokat. Minden sorban rákérdezünk egy “If” függvénnyel a keresett ID-re.

10. Lecke - ID keresés

3. Adatlap készítése

Fejlesszük tovább ezt a programot. Az újonnan létrehozott munkalap neve legyen mindig az ID száma, és csak akkor szúrjunk be új munkalapot, ha létezik is az adott ID.

Az új munkalapon továbbra is A1 cella legyen a teljes név, viszont a többi részt töltsük ki ilyen módon:

10. Lecke - Adatlap létrehozása

Az adatlap értékeit az Adatforrásról először egy tömbbe tegyük bele, majd az új munkalapon ebből a tömbből olvassuk ki az adatokat, ezzel gyakorolva a tömbök használatát.

Segítség: Amikor az ID alapján keresünk az Adatbázis lapon, akkor az a megfelelő sor értékeit kell betennünk a tömbbe.

4. Statisztika készítése

A következő részfeladatban hozzunk létre egy új gombot az “Irányító panel” munkalapunkon.
Továbbá hozzunk létre, “Statisztika” néven egy új munkalapot is az “Adatforrás” után, és ott A1-től A4-ig írjuk be a cellákba a 4 féle forrást, ami az “Adatforrás” I oszlopban szerepel: Facebook hirdetés, Adwords hirdetés, Google organikus, Hírlevél.

Feladatunk, hogy az új gombunkra nyomva, a “Statiszika” lapon, a B oszlopban mindegyik forrás kategória mellé kiírjuk, hogy mennyi van belőle.
Így:

10. Lecke - Statisztika

Segítség:Kell 4 változó, és használjunk ElseIf-t!

Megoldás

1. Új munkalap

Bekapcsoljuk a makrórögzítőt és beszúrunk egy új munkalapot. Ekkor a következő kódot kapjuk:

Sub Makró1()
   Sheets.Add After:=ActiveSheet
End Sub

Ezt hozzá is rendelhetjük a gombunkhoz az “Irányító panel” munkalapon. Így a gombot megnyomva, automatikusan beszúr mindig egy új munkalapot.

2. ID alapján keresés

Sub Makró1()
'1. Új munkalap létrehozása
   Sheets.Add After:=ActiveSheet
    
'2. ID alapján keresés
   'Egy változóba felvesszük az "Irányító panel "A1 cellájának értékét, mint keresett ID-t
   Dim keresett_id As Integer
   keresett_id = Worksheets("Irányító panel").Range("A1").Value
    
   'Felveszünk egy változót, amiben azt fogjuk csak nézni, hogy létezik-e a keresett ID, vagy sem. Ennek tényleges értékét a "For" cikluson belül fogjuk felvenni
   Dim van_ilyen As Boolean
    
   'Felveszünk még egy változót, amiben a keresett ID sorát fogjuk tárolni. Későbbiekben ezzel fogunk hivatkozni a szükséges cella sorára.
   Dim keresett_id_sora As Integer
    
   'Most jön ténylegesen a keresés. Végig megyünk az "Adatbázis" sorain az 1. oszlopban, és ha az adott cella értéke egyenlő a keresett ID-val, akkor a van_ilyen változó "true" lesz, és elmentjük az éppen aktuális sor számát a "keresett_id_sora" változóba.
   For i = 2 To 32
      If Worksheets("Adatforrás").Cells(i, 1).Value = keresett_id Then
         van_ilyen = True
         keresett_id_sora = i
      End If
   Next
    
   'Ha pedig "true" a van_ilyen, akkor keresett ID sorát felhasználjuk, és az adott sorban,
 a megfelelő oszlop értékeit kiírjuk az új munkalapunkra. (Az új munkalapunk a 2. munkalap lesz, így hivatkozunk most rá.)
      If van_ilyen = True Then
      Worksheets(2).Range("A1").Value = Worksheets("Adatforrás").Cells(keresett_id_sora, 2).Value & " " & Worksheets("Adatforrás").Cells(keresett_id_sora, 3).Value
   Else
      Worksheets(2).Range("A1").Value = "Nincs ilyen ID"
   End If  
End Sub

3. Adatlap készítése

Íme a továbbfejlesztett kódunk.

Sub Makró1()

Dim keresett_id As Integer
Dim van_ilyen As Boolean
Dim keresett_id_sora As Integer

'Létrehozzuk a tömböt.
'Mivel az Adatforrás lapon 9 oszlopban van érték, így első körben ezeket mind pakoljuk majd bele a tömbünkbe.
Dim adatlap_tomb(1 To 9) As String
 
keresett_id = Worksheets("Irányító panel").Range("A1").Value
 
For i = 2 To 32
   If Worksheets("Adatforrás").Cells(i, 1).Value = keresett_id Then
      van_ilyen = True
      keresett_id_sora = i

      'Itt vagyunk a nekünk szükséges sorban. A sor elemeit tegyük bele a tömbbe.
      ' a "j" itt az tömb elemeinek sorszámát és az Adatforrás munkalap oszlopait is jelenti. Jelen esetben ez a kettő megegyezik egymással, azaz első oszlop a tömb első eleme, a 9. oszlop a tömb 9. eleme lesz.
      For j = 1 To 9
         adatlap_tomb(j) = Worksheets("Adatforrás").Cells(i, j).Value
      Next
   End If
Next
    
If van_ilyen = True Then
   '3.1 Új munkalap beszúrása és átnevezése
   'Lehoztuk ide az új munkalap beszúrását. Ha nem létezik a keresett ID, akkor ne is szúrjunk be új munkalapot
   Sheets.Add After:=ActiveSheet
   Worksheets(2).Name = keresett_id

   'Teljes név kiírása A1 cellába
   Worksheets(2).Range("A1").Value = Worksheets("Adatforrás").Cells(keresett_id_sora, 2).Value & " " & 
   Worksheets("Adatforrás").Cells(keresett_id_sora, 3).Value

   '3.2 Adatlap kitöltése
   'Először is nevezzük el az első oszlop címkéit. Ezeket manuálisan tudjuk megtenni:
   Worksheets(2).Range("A2").Value = "Neme:"
   Worksheets(2).Range("A3").Value = "Vásárlás száma:"
   Worksheets(2).Range("A4").Value = "Összes költés:"
   Worksheets(2).Range("A5").Value = "Utolsó vásárlás értéke:"
   Worksheets(2).Range("A6").Value = "Utolsó vásárlás dátuma:"
   Worksheets(2).Range("A7").Value = "Átlag vásárlás értéke:"
   Worksheets(2).Range("A8").Value = "Forrás:"

   'Ezután a második oszlop celláit fel tudjuk tölteni a megfelelő értékkel, amit a tömbünkből fogunk kinyerni
   Worksheets(2).Range("B2").Value = adatlap_tomb(4)
   Worksheets(2).Range("B3").Value = adatlap_tomb(5)
   Worksheets(2).Range("B4").Value = adatlap_tomb(6)
   Worksheets(2).Range("B5").Value = adatlap_tomb(7)
   Worksheets(2).Range("B6").Value = adatlap_tomb(8)
   'Az átlag vásárlás értékénél a tömbünk két elemével végzünk műveletet
   Worksheets(2).Range("B7").Value = adatlap_tomb(6) / adatlap_tomb(5)
   Worksheets(2).Range("B8").Value = adatlap_tomb(9)

Else
   'Ha nem létezik az ID, akkor kiírjuk, hogy "Nincs ilyen ID"
   MsgBox "Nincs ilyen ID"
End If  
End Sub

4. Statisztika készítése

Sub Statisztika_gomb()
'Létrehozok 4 változót, amiben az adott kategória darabszámát fogom számolni
Dim facebook As Integer
Dim adwords As Integer
Dim google As Integer
Dim hirlevel As Integer

'Ezeknek mind 0-t adok kezdőértéknek
facebook = 0
adwords = 0
google = 0
hirlevel = 0

'Elkezdek végig menni a I oszlopban, ha amelyik kategóriával egyezik a cella értéke, annak a változóját növelem eggyel.
For i = 2 To 32
   If Worksheets("Adatforrás").Cells(i, 9).Value = "Facebook hirdetés" Then
      facebook = facebook + 1
   ElseIf Worksheets("Adatforrás").Cells(i, 9).Value = "Adwords hirdetés" Then
      adwords = adwords + 1
   ElseIf Worksheets("Adatforrás").Cells(i, 9).Value = "Google organikus" Then
      google = google + 1
   ElseIf Worksheets("Adatforrás").Cells(i, 9).Value = "Hírlevél" Then
      hirlevel = hirlevel + 1
   End If
Next

'Ha végig futott az összes soron, akkor kiírom a változók értékét a "Statisztika" lapon a megfelelő helyre.
Worksheets("Statisztika").Range("B1").Value = facebook
Worksheets("Statisztika").Range("B2").Value = adwords
Worksheets("Statisztika").Range("B3").Value = google
Worksheets("Statisztika").Range("B4").Value = hirlevel
End Sub

Zárószó

Ezzel befejeztük az első nagy fejezetet, melyben a VBA alapokkal ismerkedtünk. Persze ettől még nem lettél profi programozó, de nem is az volt a célunk. Hanem, hogy megismerd az Excel egy új oldalát, és alapdolgokat meg tudj oldani VBA-ban. Sok gyakorlás kell még, ezért javaslom, hogy ha van időd, fusd át újra az első 10 leckét, vagy a tanfolyam további részében is bátran nézd vissza azt a részt, amire szükséged van.

Egyelőre ennyi, remélem hasznos volt a 10 lecke. Bármilyen észrevételed, megjegyzésed, javaslatod van, bátran írj! A Kapcsolat oldalon fel tudod venni a kapcsolatot velem.

Mennyire találtad hasznosnak ezt cikket?
[Összes szavazat: 9 Átlag értékelés: 5]