Pénzügyesként elég gyakran előfordul, hogy egy adathalmazt elemzés vagy prezentáció céljából grafikonok segítségével vizualizálunk. Amennyiben a különböző nézetekre, szűrésekre nem szeretnénk külön-külön grafikont létrehozni, kézenfekvő lehet, ha ezek között a nézetek között vezérlőkkel (Control-okkal) tudunk váltani; ha pedig a kívánt grafikon megfelelő megjelenítéséhez segédtáblákra lenne szükség, akkor ezt megspórolhatjuk azzal, ha az adatok leválogatását egy makróra bízzuk.
Vezérlők létrehozására alapvetően kétféle lehetőségünk van:
Egyrészt létrehozhatunk űrlapokat (UserForm), majd az azon elhelyezett vezérlők (gombok, beviteli mezők, listák, stb.) segítségével vezéreljük meg a programunkat. Ennek előnye, hogy külön felületen megszerkeszthetjük az űrlapunk kinézetét, és makró segítségével a megfelelő pillanatban megjeleníthetjük – illetve eltüntethetjük az űrlapot. Hátránya viszont, hogy amíg egy űrlap aktív (vagyis látható), addig blokkolja az Excel többi funkcióját, vagyis nem módosíthatjuk a munkalapon lévő mezőket, nem válthatunk munkalapot – ez nem biztos, hogy minden szituációban elfogadható.
Másik megoldásként vezérlőket elhelyezhetünk közvetlenül a munkalapon. Ennek előnye, hogy a munkalapunk többi része is használható marad, hátránya a dizájn (néhány vezérlőelem feldobálva a munkalapra – pont így is néz ki). Persze adhatunk neki valami kinézetet magának a munkalapnak a formázásával, de az egész borulhat, ha valami miatt át kell alakítanunk a munkalapot, vagy egy komplexebb vezérlőcsoportot egy az egyben át kell helyeznünk máshová. Ez utóbbi csak úgy lehetséges, ha egyenként kijelöljük a vezérlőket, és az eltüntetés-megjelenítés hasonlóképp csak egyesével történhet.
A fentiek miatt egy harmadik lehetőséget szeretnék most bemutatni – ez ritkábban használatos, a programozása egy fokkal bonyolultabb, de egyesíti a fenti két módszer előnyeit és kiküszöböli a hátrányait.
A példa kedvéért tegyük fel, hogy cégcsoportunk napi záró banki egyenlegeit szeretnénk megjeleníteni. Van két cégünk, akik két banknál vezetnek összesen négy számlát. Vezérlőkkel szeretnénk megoldani, hogy bármilyen kombinációban megjeleníthessük a számlák egyenlegét, akár cégenként, akár bankonként, akár egyesével számlánként. Szintén vezérlővel szeretnénk beállítani a megjelenítendő grafikon időintervallumát. A bankkivonatok Excel exportja számlánként külön-külön munkalapon szerepel egy-egy táblában.
Íme a végeredmény:
Lássuk a megvalósítást lépésenként.
Vezérlőpanel létrehozása
A fent említett harmadik utas megoldásként egy ActiveX keretet helyezünk el a munkalapon (Developer tab → Controls → Insert → More Controls → Microsoft Forms 2.0 Frame). A Properties ablakbot előcsalogatva el is nevezhetjük, hogy a programunkban könnyebben tudjunk rá hivatkozni.
Ezután jöhet a többi vezérlőelem: a vezérlők vizuális elkülönítéséhez (számlák/időszak) sima keretet (Frame) használhatunk, a cég/bank/bankszámla váltáshoz kapcsológombokat (ActiveX ToggleButton), a dátum beállításhoz szövegbeviteli mezőt (ActiveX TextBox), illetve a példa kedvéért helyezzünk el még két vezérlőgombot (ActiveX ControlButton) a legkorábbi rendelkezésre álló dátum, illetve a mai nap beállítására. Ahogy a keretnél, itt is nevezzük el őket a funkciójuknak megfelelően.
A most bemutatásra kerülő ActiveX-es vezérlők bevezetőben említett bonyolultsága abból fakad, hogy míg az űrlapvezérlőkhöz és sima munkalapvezérlőkhöz alapból hozzá vannak rendelve események (például ha egy ControlButton-ra duplán kattintunk, rögtön létrejön a makrószerkesztőben egy ControlButton_Click() esemény, és már programozhatjuk is, hogy mi történjen a gomb megnyomásakor, de a szerkesztőben egy legördülő menü segítségével választhatunk az adott vezérlőhöz rendelkezésre álló események teljes palettájáról), addig az ActiveX vezérlőknél az események hozzárendeléséről nekünk kell gondoskodnunk. Lássuk, hogyan is történik ez.
Események létrehozása
Háromféle eseményünk lesz:
- Kapcsológomb megnyomása – összesen 8 db, ezek kattintáskor igaz (bekapcsolt) és hamis (kikapcsolt) értéket vehetnek fel, és ettől függően más-más feladatuk lesz.
- Vezérlőgomb megnyomása – összesen 2 db, ezek kattintáskor egy meghatározott feladatot fognak elvégezni
- Szövegdobozba kattintás – összesen 2 db, és azt szeretnénk, hogy kattintáskor előugorjon egy dátumválasztó naptár, és az abban kiválasztott dátumot írja be a dobozba.
Az eseményekhez a változókat a WithEvents utasítással tudjuk deklarálni, amivel meghatározzuk, hogy milyen objektum eseményeit rendeljük a vezérlőnkhöz. A deklarációt eljáráson kívüli területen kell megejteni, és jelen esetben célszerű azt a ThisWorkbook munkafüzet modulban megtenni, hogy az inicializáció a Workbook_Open () eljárással a munkalap megnyitásakor automatikusan lefusson, és a vezérlők megnyitás után rögtön működjenek. Vezérlőtípusonként 1-1 példával ez így néz ki:
Private WithEvents TglBtn_Ceg1_XBank_Fo As ToggleButton Private WithEvents CmdBtn_Datumtol As CommandButton Private WithEvents TxtBox_Datumtol As MSForms.TextBox Private Sub Workbook_Open() ' Munkalap neve: "Reporting" ' ActiveX keret neve: "Keret" (amit az elején a Properties-ben adtunk) ' Kapcsológomb neve: "TglBtn_Ceg1_XBank_Fo" ' Vezérlőgomb neve: "CmdBtn_Datumtol" ' Szövegdoboz neve: "TxtBox_Datumtol" Set TglBtn_Ceg1_XBank_Fo = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1_XBank_Fo") Set CmdBtn_Datumtol = Worksheets("Reporting").Keret.Controls("CmdBtn_Datumtol") Set TxtBox_Datumtol = Worksheets("Reporting").Keret.Controls("TxtBox_Datumtol") ' [...] End Sub
Ezután már használatba is vehetjük az eseményeket (még mindig a ThisWorksheet modulba írva):
Private Sub TglBtn_Ceg1_XBank_Fo_Click() '[...] End Sub Private Sub CmdBtn_Datumtol_Click() '[...] End Sub Private Sub TxtBox_Datumtol_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) '[...] End Sub
Kapcsológombok viselkedése
Az alapfunkció – vagyis hogy a kapcsolók állásának megfelelően frissítse az Excel a grafikonunkat – programozásában semmi bonyodalom nincsen, egyszerűen a _Click() esemény eljárásában meghívjuk azt az eljárást, ami majd a grafikon frissítését végzi:
Private Sub TglBtn_Ceg1_XBank_Fo_Click() ' -------------- ChartFrissites ' -------------- End Sub
Viszont mi nem elégszünk meg ennyivel. Szeretnénk, hogy valamelyik “csoportkapcsoló” megnyomására (például Cég1 vagy XBank) a hozzájuk tartozó számlák gombjai is benyomásra kerüljenek (például Cég1 esetében: Cég1_XBank_Fő, Cég1_XBank_Al és Cég1_YBank_Fő; XBank esetében pedig: Cég1_XBank_Fő és Cég1_XBank_Al). Illetve visszafelé is legyen igaz, vagyis ha egy céghez vagy egy bankhoz tartozó összes számla gombja benyomásra kerül, akkor a vonatkozó csoportkapcsoló is benyomásra kerüljön. Valahogy így:
Ennek megoldásához egy kis logikai játékra lesz szükségünk. Szövegesen megfogalmazva az alábbi viselkedést kell a gomboknak produkálniuk:
- HA egy Bank gomb megnyomásakor
- Bank gombot benyomjuk, AKKOR az adott bankhoz tartozó összes számla gombjának is benyomott állapotba kell kerülnie
- Bank gombot kinyomjuk ÉS az adott bankhoz tartozó összes számla gombja be van nyomva, AKKOR az adott bankhoz tartozó összes számla gombjának is kinyomott állapotba kell kerülnie(egyéb esetben nem történik semmi, de a többi feltétel biztosítani fogja, hogy ne legyen egyéb eset)
- HA egy Cég gomb megnyomásakor
- Cég gombot benyomjuk, AKKOR az adott céghez tartozó összes számla gombjának is benyomott állapotba kell kerülnie
- Cég gombot kinyomjuk ÉS az adott céghez tartozó összes számla gombja be van nyomva, AKKOR az adott céghez tartozó összes számla gombjának is kinyomott állapotba kell kerülnie(egyéb esetben nem történik semmi, de a többi feltétel biztosítani fogja, hogy ne legyen egyéb eset)
- HA egy Számla gomb megnyomásakor
- Számla gombot benyomjuk ÉS az adott bankhoz kapcsolódó összes többi számla gombja már benyomott állapotban van, AKKOR az adott Bank gomb kerüljön benyomott állapotba
- Számla gombot benyomjuk ÉS az adott céghez kapcsolódó összes többi számla gombja már benyomott állapotban van, AKKOR az adott Cég gomb kerüljön benyomott állapotba
- Számla gombot kinyomjuk, AKKOR a hozzá tartozó Bank Gomb és Cég gomb is kerüljön kinyomott állapotba (hiszen azok csak akkor lehetnek benyomva, ha az alájuk tartozó összes számla gombja be van nyomva)
Lássuk mindezt VBA kódra lefordítva. Az egyszerű hivatkozás kedvéért az elején változókként deklaráljuk a gombokat – ennek szintaktikája nagyon hasonló lesz a hozzájuk kapcsolódó események deklarálásához, csak “WithEvents” nélkül.
'ToggleButton események deklarálása Private WithEvents TglBtn_XBank As ToggleButton Private WithEvents TglBtn_YBank As ToggleButton Private WithEvents TglBtn_Ceg1 As ToggleButton Private WithEvents TglBtn_Ceg2 As ToggleButton Private WithEvents TglBtn_Ceg1_XBank_Fo As ToggleButton Private WithEvents TglBtn_Ceg1_XBank_Al As ToggleButton Private WithEvents TglBtn_Ceg1_YBank_Fo As ToggleButton Private WithEvents TglBtn_Ceg2_YBank_Fo As ToggleButton 'ToggleButton objektumok deklarálása Public XBank As ToggleButton Public YBank As ToggleButton Public Ceg1 As ToggleButton Public Ceg2 As ToggleButton Public Ceg1_XBank_Fo As ToggleButton Public Ceg1_XBank_Al As ToggleButton Public Ceg1_YBank_Fo As ToggleButton Public Ceg2_YBank_Fo As ToggleButton Private Sub Workbook_Open() ' ToggleButton események inicializálása Set TglBtn_XBank = Worksheets("Reporting").Keret.Controls("TglBtn_XBank") Set TglBtn_YBank = Worksheets("Reporting").Keret.Controls("TglBtn_YBank") Set TglBtn_Ceg1 = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1") Set TglBtn_Ceg2 = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg2") Set TglBtn_Ceg1_XBank_Fo = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1_XBank_Fo") Set TglBtn_Ceg1_XBank_Al = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1_XBank_Al") Set TglBtn_Ceg1_YBank_Fo = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1_YBank_Fo") Set TglBtn_Ceg2_YBank_Fo = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg2_YBank_Fo") ' ToggleButton objektumok inicializálása Set XBank = Worksheets("Reporting").Keret.Controls("TglBtn_XBank") Set YBank = Worksheets("Reporting").Keret.Controls("TglBtn_YBank") Set Ceg1 = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1") Set Ceg2 = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg2") Set Ceg1_XBank_Fo = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1_XBank_Fo") Set Ceg1_XBank_Al = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1_XBank_Al") Set Ceg1_YBank_Fo = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg1_YBank_Fo") Set Ceg2_YBank_Fo = Worksheets("Reporting").Keret.Controls("TglBtn_Ceg2_YBank_Fo") End Sub Private Sub TglBtn_XBank_Click() Select Case XBank Case True If Ceg1_XBank_Fo = False Then Ceg1_XBank_Fo = True End If If Ceg1_XBank_Al = False Then Ceg1_XBank_Al = True End If Case False If Ceg1_XBank_Fo = True And Ceg1_XBank_Al = True Then Ceg1_XBank_Fo = False Ceg1_XBank_Al = False End If End Select End Sub Private Sub TglBtn_YBank_Click() Select Case YBank Case True If Ceg1_YBank_Fo = False Then Ceg1_YBank_Fo = True End If If Ceg2_YBank_Fo = False Then Ceg2_YBank_Fo = True End If Case False If Ceg1_YBank_Fo = True And Ceg2_YBank_Fo = True Then Ceg1_YBank_Fo = False Ceg2_YBank_Fo = False End If End Select End Sub Private Sub TglBtn_Ceg1_Click() Select Case Ceg1 Case True If Ceg1_XBank_Fo = False Then Ceg1_XBank_Fo = True End If If Ceg1_XBank_Al = False Then Ceg1_XBank_Al = True End If If Ceg1_YBank_Fo = False Then Ceg1_YBank_Fo = True End If Case False If Ceg1_XBank_Fo = True And Ceg1_XBank_Al = True And Ceg1_YBank_Fo = True Then Ceg1_XBank_Fo = False Ceg1_XBank_Al = False Ceg1_YBank_Fo = False End If End Select End Sub Private Sub TglBtn_Ceg2_Click() Select Case Ceg2 Case True If Ceg2_YBank_Fo = False Then Ceg2_YBank_Fo = True End If Case False If Ceg2_YBank_Fo = True Then Ceg2_YBank_Fo = False End If End Select End Sub Private Sub TglBtn_Ceg1_XBank_Fo _Click() If Ceg1_XBank_Fo = True And Ceg1_XBank_Al = True Then XBank = True End If If Ceg1_XBank_Fo = True And Ceg1_XBank_Al = True And Ceg1_YBank_Fo = True Then Ceg1 = True End If If Ceg1_XBank_Fo = False Then XBank = False Ceg1 = False End If '-------------------------------------- ChartFrissites '-------------------------------------- End Sub Private Sub TglBtn_Ceg1_XBank_Al _Click() If Ceg1_XBank_Fo = True And Ceg1_XBank_Al = True Then XBank = True End If If Ceg1_XBank_Fo = True And Ceg1_XBank_Al = True And Ceg1_YBank_Fo = True Then Ceg1 = True End If If Ceg1_XBank_Al = False Then XBank = False Ceg1 = False End If '-------------------------------------- ChartFrissites '-------------------------------------- End Sub Private Sub TglBtn_Ceg1_YBank_Fo _Click() If Ceg1_YBank_Fo = True And Ceg2_YBank_Fo = True Then YBank = True End If If Ceg1_XBank_Fo = True And Ceg1_XBank_Al = True And Ceg1_YBank_Fo = True Then Ceg1 = True End If If Ceg1_YBank_Fo = False Then YBank = False Ceg1 = False End If '-------------------------------------- ChartFrissites '-------------------------------------- End Sub Private Sub TglBtn_Ceg2_YBank_Fo _Click() If Ceg1_YBank_Fo = True And Ceg2_YBank_Fo = True Then YBank = True End If If Ceg1_YBank_Fo = True And Ceg2_YBank_Fo = True Then Ceg2 = True End If If Ceg2_YBank_Fo = False Then YBank = False Ceg2 = False End If '-------------------------------------- ChartFrissites '-------------------------------------- End Sub
Dátumválasztás naptárral
A grafikonunk időhorizontjának állításához TextBox-ot használunk, illetve a dátumbevitel elősegítésére egy naptár felugró ablakot. A bevezető animációban bemutatott működés az alábbiakból áll:
- A szövegdobozokban kezdőértékként be van állítva az utolsó hónap, vagyis egy hónappal ezelőttől a mai napig terjedő időszak.
- Bármelyik egérgombbal a szövegdobozba kattintva előugrik egy naptár. A naptár kezdeti értéke mindig megegyezik a szövegdobozban látható dátummal.
- A naptárban egy dátum kiválasztása után az OK gombra kattintva a dátum beállításra kerül a megfelelő szövegdobozba és az Excel frissíti a grafikonunkat. A lezárás (x) gomb megnyomásakor a naptár bezárul, de nem frissül a szövegdoboz dátuma.
Először is hozzuk létre a naptárunkat. Ehhez egy UserForm-ot fogunk használni, és arra illesztünk egy naptár vezérlőt, illetve egy vezérlőgombot (CommandButton). A naptár vezérlő alapból nem látszik a rendelkezésre álló vezérlők között, de a makrószerkesztőben a Tools → Additional Controls… menüpontban tudjuk hozzáadni. Excel verziónként eltér, hogy milyen naptár vezérlő(k) áll(nak) rendelkezésre, ezek kinézetben különböznek, de programozásban elég hasonlóak. Jelen példában a Calendar Control 12.0 vezérlőt használjuk.
Adjunk egy nevet a UserForm-nak (jelen példában “PopupCalendar”), a naptár vezérlőnek (“NaptarInput”) és az OK gombnak (“Naptar_OK”), a makrókban ezen a néven tudunk majd hivatkozni rájuk.
A dátumkezeléshez egyrészt létre kell hoznunk a szövegdobozhoz tartozó eseményeket, másrészt deklaránunk kell néhány globális változót, amivel adatokat tudunk átadni a szövegdoboz, a UserForm és a grafikonfrissítő eljárások között. Az eseményekkel kapcsolatos deklarációk és eljárások most is a ThisWorkbook modulba menjenek, a globális változók mehetnek abba a modulba, amiben majd a grafikonfrissítést fogjuk programozni. Ezután jöhet a dátumbeviteli mezők kezdeti értékeinek meghatározása.
Modulba:
Public Datumtol As Long Public Datumig As Long
ThisWorkbook-ba:
'[...ToggleButton deklarációk után...] 'TextBox események deklarálása Private WithEvents TextBox_Datumtol As MSForms.TextBox Private WithEvents TextBox_Datumig As MSForms.TextBox Private Sub Workbook_Open() '[...ToggleButton inicializálások után...] ' TextBox események inicializálása Set TextBox_Datumtol = Worksheets("Reporting").Keret.Controls("TextBox_Datumtol") Set TextBox_Datumig = Worksheets("Reporting").Keret.Controls("TextBox_Datumig") ' Dátummezők kezdeti értékei Datumtol = DateSerial(Year(Date), Month(Date) - 1, Day(Date)) TextBox_Datumtol.Value = Format(Datumtol, "yyyy.mm.dd") Datumig = DateSerial(Year(Date), Month(Date), Day(Date)) TextBox_Datumig.Value = Format(Datumig, "yyyy.mm.dd") End Sub
Megjegyzések a fentiekhez:
Az Excel a dátumokat számokként tárolja, ezért a Datumtol és Datumig változókat Long típusú változóként deklaráljuk. Az aktuális nap dátumát a Date függvénnyel kapjuk meg. A szövegdobozunk viszont String típusú szöveget jelenít meg, ezért ha nem egy ötszámjegyű számot szeretnénk látni, hanem egy dátumot, akkor a Format függvénnyel át kell alakítanunk a dátumunkat.
A naptár UserForm alkalmazásához még egy nehézséget át kell hidalnunk: egy űrlapunk van a dátumbevitelre, viszont két lehetséges dátummezőnk. Tehát biztosítanunk kell, hogy a programunk “tudja”, hogy melyik mezőre kattintva hoztuk elő a naptárat, és abba a mezőbe kell visszaírnunk a kiválasztott dátumot. Ezt egy változó beiktatásával tudjuk megtenni.
Lássuk, hogy néznek ki a dátumbevitellel kapcsolatos események:
Modulba:
'Datum választóhoz annak eldöntésére, hogy melyik mezőből indították Public Enum AktivBox_Type [_First] = 1 Box_Datumtol = 1 Box_Datumig = 2 [_Last] = 2 End Enum Public AktivBox As AktivBox_Type
ThisWorkbook-ba:
Private Sub TextBox_Datumtol_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) AktivBox = Box_Datumtol PopupCalendar.Show TextBox_Datumtol.Value = Format(Datumtol, "yyyy.mm.dd") '-------------------------------------- ChartFrissites '-------------------------------------- End Sub Private Sub TextBox_Datumig_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) AktivBox = Box_Datumig PopupCalendar.Show TextBox_Datumig.Value = Format(Datumig, "yyyy.mm.dd") '-------------------------------------- ChartFrissites '-------------------------------------- End Sub
UserForm kódjába:
Private Sub UserForm_Activate() 'ez a kód akkor fut le, amikor megjelenítjük a UserForm-ot ' dátumválasztó kezdeti értéke Select Case AktivBox Case Is = Box_Datumtol NaptarInput.Value = Datumtol Case Is = Box_Datumig NaptarInput.Value = Datumig End Select End Sub Private Sub Naptar_OK_Click() 'ez a kód akkor fut le, amikor az OK gombra kattintunk Select Case AktivBox Case Is = Box_Datumtol Datumtol = NaptarInput.Value Case Is = Box_Datumig Datumig = NaptarInput.Value End Select Me.Hide End Sub
Az Enum deklarációval létrehozzuk az “AktivBox_Type” nevű változótípust, és meghatározzuk annak lehetséges értékeit. Amikor valamelyik dátumbeviteli szövegdobozra kattintunk, az “AktivBox” változóba elmentjük, melyik dobozból indítottuk a dátumválasztást.
A PopupCalendar.Show utasítással megjelenítjük a dátumválasztó űrlapunkat, és egyben el is indítjuk a hozzá tartozó UserForm_Activate() rutint. Itt a NaptarInput.Value utasítással beállítjuk a naptárunk kezdeti értékének azt a dátumot, ami a szövegdobozban szerepel. A _Click() rutinban változóba kiírjuk a naptárban kiválasztott dátumot, a PopupCalendar.Hide utasítással eltüntetjük a naptár ablakot, majd visszatérve a ThisWorkbook-ban futó eljárásba a TextBox_[…].Value utasítással módosítjuk a szövegdobozban megjelenített dátumot arra, amit előzőleg kiválasztottunk és változóba eltároltunk.
Eddig megnéztük, hogy vezérlőkkel hogyan tudjuk a grafikon frissítéséhez szükséges inputokat előállítani. A következő rész témája pedig magának a grafikonnak az előállítása és formázása lesz.