Grafikonok vezérlése és formázása – 2. rész

Az előző bejegyzésben elkezdtük egy vezérlőpanellel vezérelt grafikon felépítését. A gondolatmenetet ott hagytuk abba, hogy elkészült képzeletbeli esetünkhöz a vezérlőpanel, amin kapcsológombok segítségével ki tudjuk választani, hogy két cég két banknál vezetett bankszámlái közül  melyik számlák látszódjanak a grafikonon, egy dátumválasztó segítségével pedig ki tudjuk választani, hogy mely időszakot jelenítse meg a grafikon. A mai bejegyzésből kiderül, hogy makrók és az előzőekben felépített vezérlők segítségével hogyan tudjuk a grafikont létrehozni, a megfelelő adatokat hozzárendelni, és az alapvető formázásokat megejteni.

Mielőtt belemennénk a részletekbe, tekintsük át , hogyan épül fel a grafikonok objektummodellje.

ChartObject objektum

Amennyiben nem egy Chart típusú lapon helyezkedik el a grafikonunk, hanem egy munkalapra illesztjük be (ún. beágyazott diagram vagy embedded chart-ként), akkor a hierarchiában a legfelsőbb szintű objektumunk a ChartObject lesz, illetve ezek kollekciója, a ChartObjects. Úgy kell elképzelni, hogy ez maga a keret, ami a diagramot tartalmazza. Legfontosabb metódusai és tulajdonságai:

• ChartObjects.Add – ezzel a metódussal tudunk egy új elemet a ChartObjects kollekcióhoz hozzáadni, magyarul egy új beágyazott diagramot létrehozni. Kötelező paraméterként meg kell adni a kezdő pozíciót (Left és Top, az A1 cella bal felső sarkától számítva, pontban meghatározva), valamint a kezdő méretet (Width és Hight, szintén pontban meghatározva). Továbbá ahhoz hogy ne kapjunk hibaüzenetet, vagy egy nevet kell neki adnunk a .Name tulajdonsággal, vagy egy ChartObject típusú változó értékeként kell a .Add metódust megadnunk – ez utóbbi esetben, ha nem adunk az objektumnak nevet, akkor az Excel generál hozzá egyet (pl.: “Chart 3”). Még egy fontos gondolat: a ChartObject objektum nem önmagában létezik, hanem annak a munkalapnak a gyerek objektuma, amelyik lapon létrehozzuk/létrehoztuk. Ezért hivatkozni rá mindig a munkalappal együtt tudunk. Példákkal illusztrálva az eddigieket:

  ActiveSheet.ChartObjects.Add( _
     Left:=50, _
     Top:= 20, _
     Width:= 200, _
     Height:= 100) _
     .Name="ChtObj"
  

  ugyanez implicit módon megadott paraméterekkel:

  ActiveSheet.ChartObjects.Add(50, 20, 200, 100).Name="ChtObj"
  

  vagy változóval:

  dim UjDiagram as ChartObject
   
  Set UjDiagram = ActiveSheet.ChartObjects.Add(50, 20, 200, 100)
      UjDiagram.Name="ChtObj"
  

• ChartObjects.Count – ez a tulajdonság megadja, hogy hány elemünk van az adott munkalap ChartObjects gyűjteményében. A ChartObjects(ChartObjects.Count) utasítással pedig az adott munkalapon utoljára létrehozott ChartObject objektumra tudunk hivatkozni.
• ChartObject.Delete – ezzel az eljárással a hivatkozott objektumot tudjuk törölni. Például kombinálva a .Count tulajdonsággal, a következő utasítás az utoljára létrehozott beágyazott diagramunkat törli:

  ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Delete
  

• ChartObject.Index – ez a tulajdonság a hivatkozott objektum gyűjteményen belüli sorszámát adja meg. Vegyük például a következő állítást:

  ActiveSheet.ChartObjects("ChtObj").Index = 1
  

  Az állítás igaz (True értéket eredményez), ha a “ChtObj” nevű diagramot elsőként hoztuk létre az aktív munkalapon, hamis (False értéket eredményez), ha létrehoztuk, de nem elsőként, és hibaüzenetet kapunk, ha ilyen néven nincs diagram az aktív munkalapon.

• ChartObject.Top; ChartObject.Left; ChartObject.Width; ChartObject.Height – a diagram pozícióját és méreteit meghatározó tulajdonságokat már érintettük az Add metódus kapcsán. Ezeket a paramétereket nem csak létrehozáskor adhatjuk meg, hanem a későbbiekben is bármikor megváltoztathatjuk ezekkel a tulajdonságokkal.
• ChartObject.Name – ez a tulajdonság a hivatkozott objektum nevét adja eredményül. Például az alábbi utasítással a másodikként létrehozott diagram nevét egy String típusú változóban tároljuk el:

  Dim DiagramNev as String
   
  DiagramNev = ActiveSheet.ChartObjects(2).Name
  

• ChartObject.Visible – ez a tulajdonság azt adja eredményül, hogy a hivatkozott diagram látható-e. A tulajdonság makróval változtatható is, vagyis attól függően, hogy True-ra vagy False-ra állítjuk a tulajdonság értékét, tudjuk a hivatkozott diagramot megjeleníteni vagy eltüntetni (ezzel a diagram nem törlődik, csak nem lesz látható). Például:

  ActiveSheet.ChartObjects("ChtObj").Visible = False
  

Chart Objektum

Ez maga a grafikonunk, ezen az objektumon, vagy ennek valamelyik gyerek objektumán tudunk mindent állítani, amit kézzel a grafikon varázslóban, a Ribbon-on vagy a grafikonon jobb klikkel előhívott tulajdonság menüpontokban állítanánk be. Ahogy a ChartObject objektumnál, úgy itt is szülő objektumokkal együtt tudunk a Chart objektumunkra hivatkozni. Lássuk a legfontosabb metódusokat, tulajdonságokat és gyerek objektumokat. A lehetséges beállítások köre igen széles, és semmiképp nem szeretném teljeskörűen és felsorolásszerűen bemutatni mindet. Ebben a bekezdésben inkább csak példák nélkül áttekintjük a leggyakrabban használatosokat, majd a bankszámlás esetünkre visszatérve egy-egy konkrét példát is fogunk rájuk látni:

• Chart.ChartType – ezzel a tulajdonsággal tudjuk beállítani (vagy lekérdezni), hogy milyen típusú diagramunk legyen.
• Chart.SetSourceData – amennyiben grafikonunk adatainak forrása egy Excel táblázat, ezzel tudjuk a rá való hivatkozást beállítani: hogy mely cellákban helyezkednek el az adatok (Source), illetve hogy oszlopok vagy sorok szerint szerepelnek a kategóriák és értékek (PlotBy). Mivel a bankszámlás példában más megoldást fogunk alkalmazni, erre lássunk most egy példát:

  ActiveSheet.ChartObjects("ChtObj").Chart.SetSourceData _
                 Source:=Sheets("Sheet1").Range("A1:D5"), _
                 PlotBy:=xlRows
  

• Chart.Axes – ezzel a metódussal tudunk a tengelyekre, illetve további gyerek objektumok megadásával azok elemeire hivatkozni. Amennyiben nem az összes tengelyre, mint kollekcióra szeretnénk hivatkozni, paraméterként megadhatjuk a tengely hivatkozását: a vízszintes tengelyé xlCategory, a függőlegesé xlValue; ha van elsődleges és másodlagos tengelyünk is, akkor megadható hogy xlPrimary vagy xlSecondary. Például ha két függőleges tengelyünk van, akkor a jobb oldali (másodlagos tengely) hivatkozása: Chart.Axes(xlValue, xlSecondary)
• Chart.SeriesCollection – ezzel a metódussal a diagrammal ábrázolt számsorra tudunk hivatkozni. Mint a neve is mutatja, ez egy gyűjtemény: ha megadunk egy indexszámot, akkor egy konkrét számsorra tudunk hivatkozni, különben pedig a grafikonon ábrázolt összes számsorra.
• Chart.ChartArea – ezzel a tulajdonsággal a diagram külső, PlotArea-n kívüli területére tudunk hivatkozni.
• Chart.PlotArea – ezzel a tulajdonsággal a diagram belső területére tudunk hivatkozni.
• Chart.Legend – ezzel a tulajdonsággal a jelmagyarázatot tartalmazó dobozra tudunk hivatkozni.
• Chart.ChartTitle – ezzel a tulajdonsággal a diagramunk címét tartalmazó dobozra tudunk hivatkozni.
• Chart.SetElement – ezzel a metódussal százas nagyságrendű, előre definiált utasításlistából választva tudunk egy-egy formázási lépést elvégezni – mindezt anélkül, hogy az érintett gyerek objektumot és annak metódusait, tulajdonságait közvetlenül meghívnánk. Például ha a grafikonunkon el szeretnénk tüntetni a függőleges tengelyt, azt megtehetjük így is:

  ActiveSheet.ChartObjects("ChtObj").Chart _
          .Axes(xlValue, xlPrimary).Delete
  

  és így is:

  ActiveSheet.ChartObjects("ChtObj.Chart _
          .SetElement (msoElementPrimaryValueAxisNone)
  

• Chart.ChartGroups illetve Chart.LineGroups, Chart.ColumnGroups, stb… – vannak olyan tulajdonságok, amelyeket nem lehet általánosságban minden típusú grafikonon értelmezni, ezért nem lehetne őket a Chart vagy a SeriesCollection objektumokon végrehajtani. Az ilyen típusspecifikus utasításokat (mint például oszlopdiagramnál az oszlopok közötti távolságot) a ChartGroups kollekció megfelelő elemén, vagy a típusnak megfelelő …Groups kollekció (oszlopdiagramnál ColumnGroups kollekció) megfelelő elemén kell végrehajtani. Például ha a grafikonunk tartalmaz egy vonaldiagramot és egy oszlopdiagramot, és tudjuk, hogy az oszlopdiagram indexe 2, akkor az oszlopdiagramot vagy a Chart.Chartgroups(2) vagy Chart.ColumnGroups(1) metódussal tudjuk hivatkozni.

Az elméleti alapozás után térjünk vissza a bankszámlás esetünkhöz.

Grafikon létrehozása

A célunk az, hogy a munkafüzetünk megnyitásakor a grafikon a helyén legyen. Ha már ott van, akkor jó, ha nincs még ott, akkor létre kell hozni. Egy egyszerű módszer annak ellenőrzésére, hogy már létezik-e a grafikonunk az, hogy megpróbáljuk változóba betölteni.

Először is deklaráljuk a szükséges változókat a modulunk eljárásokon kívül eső területén:

Public ChtObj   as ChartObject
Public Grafikon as Chart

A betöltést / grafikon létrehozást a munkafüzet megnyitásakor szeretnénk megejteni, ezért a ThisWorkbook modulba, a Workbook_Open() eljárásba írjuk a következőket (a vezérlőelemekkel kapcsolatos kódok után):

On Error Resume Next
    Set ChtObj = Worksheets("Reporting").ChartObjects("Chart_Reporting")
On Error GoTo 0

A “Set ChtObj…” utasítássor megpróbálja a “Reporting” nevű lapon lévő “Chart_Reporting” nevű grafikont betölteni a ChtObj változóba. Amennyiben csak ennyi lenne az utasítás, akkor – amennyiben a nevezett grafikon nem létezik – rögtön hibaüzenetet kapunk. Ezért szükségünk van egy kis hibakezelésre: az On Error Resume Next kóddal arra utasítjuk  az eljárásunkat, hogy hiba esetén hagyja figyelmen kívül a hibás sort, és lépjen a következő sorra. Az On Error GoTo 0 kóddal pedig kikapcsoljuk ezt a hibakezelő utasítást, hiszen nem szeretnénk hogy a programmal máshol esetlegesen előforduló hibákkal ugyanezt tegye. Eredményképp, ha létezik a grafikon, akkor a nevezett ChartObject objektumot betöltöttük a ChtObj változóba, amennyiben nem létezik a grafikon, akkor a ChtObj változónk üres marad, vagyis az értéke Nothing marad.

Egy feltételvizsgálat után hozzuk létre a grafikont – ha szükséges:

If GrafikonKeret Is Nothing Then

'   grafikon létrehozása
    With Worksheets("Reporting").ChartObjects.Add( _
        Top:=Worksheets("Reporting").Keret.Top, _
        Left:=Worksheets("Reporting").Keret.Left + Worksheets("Reporting").Keret.Width + 15, _
        Width:=ActiveWindow.VisibleRange.Width - Worksheets("Reporting").Keret.Left - Worksheets("Reporting").Keret.Width - 75, _
        Height:=ActiveWindow.VisibleRange.Height - 30)
        .Name = "Chart_Reporting"
    End With

    Set ChtObj = Worksheets("Reporting").ChartObjects("Chart_Reporting")
    Set Grafikon = ChtObj.Chart

'   [ide még jön pár sor]
 
End If 

A már ismert .Add metódussal hozzuk létre a grafikont. A pozíció és a méretek megadásánál trükközzünk egy kicsit, és a grafikon méretét igazítsuk az Excel ablakunk üres részéhez:

• A grafikonunk felső élét (Top paraméter) a vezérlőpanel felső éléhez igazítjuk.
• A grafikonunk bal oldalát (Left) a vezérlőpanel jobb oldalától 15 pontnyira helyezzük el. Mivel “Right” tulajdonság nem létezik, ezért a vezérlőpanel jobb élének pozícióját úgy kapjuk meg, hogy a bal élének pozíciójához hozzáadjuk a szélességét.
• A grafikonunk jobb oldalát – vagyis a szélesség (Width) paraméterét – a látható képernyő szélességéhez igazítjuk, ehhez az ActiveWindow.VisibleRange méreteit használjuk fel:
  grafikon szélesség = ablak szélesség – vezérlőpanel bal széle – vezérlőpanel szélessége – 75 pont
• A grafikonunk magasságát (Height) szintén a látható képernyőhöz igazítjuk.

Ezután adjunk egy nevet a grafikonnak a .Name tulajdonság megadásával, végezetül az elkészült diagramot a könnyebb hivatkozás érdekében töltsük be változókba: mind a ChartObject, mind a Chart objektumot.

Grafikon formázása – első nekifutás

Az újonnan létrehozott diagramunk még nem tartalmaz adatokat, csak egy üres keretünk van, ezért az adatfüggő formázásokat ebben a lépésben még nem tudjuk megejteni. A legtöbb tulajdonságot azonban már be tudjuk állítani, tegyük is meg (még mindig a ThisWorkbook modul Workbook_Open() eljárásában vagyunk).

A Chart objektumon beállíthatjuk, hogy milyen típusú diagramunk legyen (halmozott oszlop), jelenjen-e meg cím a grafikonon (ne jelenjen):

With Grafikon
    .ChartType = xlColumnStacked
    .HasTitle = False
End With

Jöhet a vízszintes tengely, vagyis a Chart.Axes(xlCategory), ahol beállítjuk a címkét (ne legyen), a formátumot (dátum), a skálabeosztást (automatikus), a skála szövegének irányát (függőleges), valamint a függőleges rácsvonalakat (ne legyen). A minimum-maximum értékeket később tudjuk beállítani, a dátumválasztás után:

With Grafikon.Axes(xlCategory)
    .HasTitle = False
    .CategoryType = xlTimeScale
    .MajorUnitIsAuto = True
    .MinorUnitIsAuto = True
    .BaseUnitIsAuto = True
    .TickLabels.Orientation = xlTickLabelOrientationUpward
    .HasMajorGridlines = False
    .HasMinorGridlines = False
 
    On Error Resume Next
    .TickLabels.NumberFormat = "yyyy.mm.dd"
    .TickLabels.NumberFormat = "éééé.hh.nn"
    On Error GoTo 0
 
End With

Az előző kódrészletben is látható egy kis trükközés. Az Excel nyelvétől és a számítógép regionális beállításaitól függően változhat, hogy a dátumformátumot milyen nyelven kell megadni (angolul y/m/d vagy magyarul é/h/n). A színessel jelölt sorok biztosítják, hogy mindkét verzióban működjön a program.

A függőleges tengelynél – Chart.Axes(xlValue) – beállítjuk a címke szövegét (“Egyenleg”) és méretét, a számformátumot, a minimum-maximum értékeket (automatikus), a skálabeosztást (ezer Ft) és hogy ezt címkével tüntesse is fel, valamint a vízszintes rácsvonalakat (világosszürke):

With Grafikon.Axes(xlValue)
    .HasTitle = True
    With .AxisTitle
        .Caption = "Egyenleg"
        .Font.Name = "arial"
        .Font.Size = 10
    End With
    With .TickLabels
       .NumberFormat = "# ##0"
    End With
    .MinimumScaleIsAuto = True
    .MaximumScaleIsAuto = True
    .MajorUnitIsAuto = True
    .MinorUnitIsAuto = True
    .DisplayUnit = xlThousands
    With .DisplayUnitLabel
        .Caption = "eFt"
    End With
    .HasMajorGridlines = True
    With .MajorGridlines.Border
        .Color = RGB(224, 224, 224)
    End With
End With

Ezután formázzuk meg a diagram felületét, vagyis a Chart.ChartArea és Chart.PlotArea objektumokat: mindkettő legyen átlátszó és ne legyen keretük.

With Grafikon.ChartArea
    With .Border
        .LineStyle = xlLineStyleNone
    End With
    With .Format
        .Fill.Visible = False
       '.Line.Visible = False --> ez ugyanaz mint a .Border.LineStyle = xlLineStyleNone
    End With
End With

With Grafikon.PlotArea
    With .Border
        .LineStyle = xlLineStyleNone
    End With
    With .Format
        .Fill.Visible = False
       '.Line.Visible = False --> ez ugyanaz mint a Border.LineStyle = xlLineStyleNone
    End With
End With

Végezetül állítsuk be az oszlopdiagramunk oszloptávolságát – ezt a Chart.ChartGroup vagy Chart.ColumnGroup objektumon tudjuk elvégezni:

With Grafikon.ColumnGroups(1)
    .GapWidth = 30
End With

Grafikon feltöltése adatokkal

Per pillanat van egy majdnem teljesen megformázott grafikonunk, amiből azonban nem látunk semmit, mivel nincsen még adatokkal feltöltve. Az előző bejegyzésben többször utaltunk a “ChartFrissites” nevű eljárásra, ami többek között ezt az adatfeltöltést végzi el – most lássuk mit is tartalmaz.

Adott a vezérlőpanelünk, amin a kapcsológombok állása (Igaz/Hamis) mondja meg, hogy mely számlákat szeretnénk látni a grafikonon, illetve két dátummező, amelyeknek az értékét a “Datumtol” és “Datumig” változókban tároltuk el.

A megfelelő adatsorok megjelenítésére/eltüntetésére több megközelítés lehetséges. Az egyik megközelítésben betölthetjük az összes adatot a teljes rendelkezésre álló időtávra, majd a tengely minimum-maximum értékének az állításával szabályozhatjuk a megjelenítendő időhorizontot és a számsor színezésével vagy átlátszóvá tételével pedig a számsor láthatóságát – ez egy vonalgrafikonnál működhet, halmozott oszlopgrafikonnál viszont nem. Egy másik megközelítésben végiglépdelhetünk a kapcsolókon, megvizsgálhatjuk a hozzá kapcsolódó sorozat létezését, és ezeknek megfelelően törölhetjük a vezérlőpanelen kikapcsolt, de a grafikonon szereplő sorozatokat, illetve létrehozhatjuk a vezérlőpanelen bekapcsolt, de a grafikonon nem szereplő sorozatokat – az adatokat itt is a teljes rendelkezésre álló időtávra be kell töltenünk, majd a tengely időtávját tudjuk a kívánt értékeknek megfelelően beállítani . Az alábbiakban egy harmadik megközelítést nézünk meg: első lépésben töröljük a grafikonon szereplő összes sorozatot, ezután a kapcsolók és a dátummezők értékének megfelelően betöltjük a kívánt sorozatokat, a kívánt időtávra vonatkozóan.

A fő eljárás

Ehhez első körben hozzunk létre egy gyűjteményt a figyelendő 4 db kapcsológombból. A modulunk eljárásokon kívül eső részén deklaráljuk a szükséges változókat. Mivel tömbökkel dolgozunk és szeretnénk ha az első elemre 1-el hivatkoznánk és nem nullával, állítsuk be ezt is:

Option Base 1
 
Public KapcsoloGombok As Collection
Dim    KapcsoloGomb   As Object

Dim XArray As Variant
Dim YArray As Variant

Const Ceg1_XBank_Fo_Szin As Long = 128       'RGB(128, 0, 0)
Const Ceg1_XBank_Al_Szin As Long = 14772545  'RGB(65, 105, 225)
Const Ceg1_YBank_Fo_Szin As Long = 6053069   'RGB(205, 92, 92)
Const Ceg2_YBank_Fo_Szin As Long = 8036607   'RGB(255, 160, 122)

Majd a ThisWorkbook modul Workbook_Open() eljárásba írjuk a következőket:

'Toggle gombok figyeléséhez gyűjtemény létrehozása
 Set KapcsoloGombok = New Collection
 With KapcsoloGombok
    .Add Ceg1_XBank_Fo
    .Add Ceg1_XBank_Al
    .Add Ceg1_YBank_Fo
    .Add Ceg2_YBank_Fo
 End With

A számsorok törléséhez a “ChartFrissites” eljárásunk első utasításaként betöltjük a grafikonunkat a korábban deklarált változókba, majd végiglépdelünk a SeriesCollection gyűjtemény elemein, és töröljük azokat:

Sub ChartFrissites()

Dim Adatsor As Series

    Set ChtObj = ActiveSheet.ChartObjects("Chart_Reporting")
    Set Grafikon = ChtObj.Chart

'   SeriesCollection ürítése
    For Each Adatsor In Grafikon.SeriesCollection
        Adatsor.Delete
    Next Adatsor

'[...]
End Sub

Ha ez lefutott, jöhet egy ciklus, amivel végiglépdelünk a gombokból létrehozott kollekció elemein: ha az be van kapcsolva, akkor a beállított időszak dátumait tartalmazó tömböt (XArray nevű váltózó), valamint az adott dátumokhoz tartozó napi záró bankszámlaegyenlegeket tartalmazó tömböt (YArray változó) betöltjük a grafikonba, illetve ki is színezzük (a színkódokat konstansként deklaráltuk, lásd feljebb). A tömböket külön eljárásban (“Levalogatas”) töltjük fel, a “ChartFrissites” szubrutinban csak meghívjuk azt az eljárást.

Sub ChartFrissites()

'[SeriesCollection ürítése után:]
 
'--------------------------------------
    AdatDefinicio
'--------------------------------------
 
    For Each KapcsoloGomb In KapcsoloGombok
'   ha a gomb be van kapcsolva, új Series hozzáadása, amúgy kihagyás

        If KapcsoloGomb = True Then
'--------------------------------------
            TempAdatok
'--------------------------------------

'--------------------------------------
            Levalogatas
'--------------------------------------

            With Grafikon.SeriesCollection.NewSeries
                .Name = Felirat
                .Values = YArray
                .XValues = XArray
                .Interior.Color = tmpSzin
            End With

        End If

    Next KapcsoloGomb

'--------------------------------------
    Grafikon_Formazas
'--------------------------------------
 
End Sub

Forrástáblák definiálása

Az előző bejegyzés esetleírásában olvashattuk, hogy a bankkivonatok, amikkel dolgozni szeretnénk, a munkafüzetünk egy-egy lapján helyezkednek el, egy-egy táblázatként formázott, elnevezett táblában (Named Table). Az ilyen táblázatoknál a hivatkozás kicsit másképp működik, mint a Range-eknél: közvetlenül hivatkozhatunk a fejlécre, anélkül, hogy a sor/oszlop koordinátákat megadnánk, hivatkozhatunk  egy-egy oszlopra annak nevével, a koordináták megadása nélkül, hivatkozhatunk az adattartalomra, szintén anélkül, hogy a konkrét cellákra hivatkoznánk. A táblázatok megfelelő adatait az “AdatDefinicio” eljárásban rendeljük hozzá a megfelelő változókhoz.

Ehhez először is deklaráljuk a változókat (az eljárásokon kívül eső részen):

'Adatválogatás változói
Dim Ceg1_XBank_Fo_tbl As ListObject
Dim Ceg1_XBank_Al_tbl As ListObject
Dim Ceg1_YBank_Fo_tbl As ListObject
Dim Ceg2_YBank_Fo_tbl As ListObject
Dim Ceg1_XBank_Fo_arr As Variant
Dim Ceg1_XBank_Al_arr As Variant
Dim Ceg1_YBank_Fo_arr As Variant
Dim Ceg2_YBank_Fo_arr As Variant

Dim Ceg1_XBank_Fo_ErteknapOszlop As Integer
Dim Ceg1_XBank_Fo_OsszegOszlop   As Integer
Dim Ceg1_XBank_Al_ErteknapOszlop As Integer
Dim Ceg1_XBank_Al_OsszegOszlop   As Integer
Dim Ceg1_YBank_Fo_ErteknapOszlop As Integer
Dim Ceg1_YBank_Fo_OsszegOszlop   As Integer
Dim Ceg2_YBank_Fo_ErteknapOszlop As Integer
Dim Ceg2_YBank_Fo_OsszegOszlop   As Integer

Majd az eljárásban végezzük el a hozzárendeléseket:

Sub AdatDefinicio()

'   Bankkivonatok definiálása
    Set Ceg1_XBank_Fo_tbl = ThisWorkbook.Sheets("Ceg1_XBank_Fo").ListObjects("Ceg1_XBank_Fo")
        Ceg1_XBank_Fo_arr = Ceg1_XBank_Fo_tbl.DataBodyRange.Value2
    Set Ceg1_XBank_Al_tbl = ThisWorkbook.Sheets("Ceg1_XBank_Al").ListObjects("Ceg1_XBank_Al")
        Ceg1_XBank_Al_arr = Ceg1_XBank_Al_tbl.DataBodyRange.Value2
    Set Ceg1_YBank_Fo_tbl = ThisWorkbook.Sheets("Ceg1_YBank_Fo").ListObjects("Ceg1_YBank_Fo")
        Ceg1_YBank_Fo_arr = Ceg1_YBank_Fo_tbl.DataBodyRange.Value2
    Set Ceg2_YBank_Fo_tbl = ThisWorkbook.Sheets("Ceg2_YBank_Fo").ListObjects("Ceg2_YBank_Fo")
        Ceg2_YBank_Fo_arr = Ceg2_YBank_Fo_tbl.DataBodyRange.Value2

'   Bankkivonat oszlopok definiálása
    Ceg1_XBank_Fo_ErteknapOszlop = Ceg1_XBank_Fo_tbl.HeaderRowRange.Find(what:="Értéknap").Column
    Ceg1_XBank_Fo_OsszegOszlop = Ceg1_XBank_Fo_tbl.HeaderRowRange.Find(what:="Összeg").Column
    Ceg1_XBank_Al_ErteknapOszlop = Ceg1_XBank_Al_tbl.HeaderRowRange.Find(what:="Értéknap").Column
    Ceg1_XBank_Al_OsszegOszlop = Ceg1_XBank_Al_tbl.HeaderRowRange.Find(what:="Összeg").Column
    Ceg1_YBank_Fo_ErteknapOszlop = Ceg1_YBank_Fo_tbl.HeaderRowRange.Find(what:="Értéknap").Column
    Ceg1_YBank_Fo_OsszegOszlop = Ceg1_YBank_Fo_tbl.HeaderRowRange.Find(what:="Összeg").Column
    Ceg2_YBank_Fo_ErteknapOszlop = Ceg2_YBank_Fo_tbl.HeaderRowRange.Find(what:="Értéknap").Column
    Ceg2_YBank_Fo_OsszegOszlop = Ceg2_YBank_Fo_tbl.HeaderRowRange.Find(what:="Összeg").Column
 
End Sub

Sorozatspecifikus változók definiálása

A “TempAdatok” eljárásban néhány változót feltöltünk az adott kapcsológombra (bankszámlára) vonatkozó értékekkel, hogy azzal a többi eljárásban műveleteket tudjunk végezni.

Először deklaráljuk a szükséges változókat a modul elején:

Dim Felirat           As String
Dim tmpKivonat_arr    As Variant
Dim tmpErteknapOszlop As Integer
Dim tmpOsszegOszlop   As Integer
Dim tmpSzin           As Long

Majd jöhet maga a szubrutin:

Sub TempAdatok()

    Select Case KapcsoloGomb.Name

        Case "Ceg1_XBank_Fo"
             Felirat = "Cég1_XBank_Fő"
             tmpKivonat_arr = Ceg1_XBank_Fo_arr
             tmpErteknapOszlop = Ceg1_XBank_Fo_ErteknapOszlop
             tmpOsszegOszlop = Ceg1_XBank_Fo_OsszegOszlop
             tmpSzin = Ceg1_XBank_Fo_Szin
        Case "Ceg1_XBank_Al"
             Felirat = "Cég1_XBank_Al"
             tmpKivonat_arr = Ceg1_XBank_Al_arr
             tmpErteknapOszlop = Ceg1_XBank_Al_ErteknapOszlop
             tmpOsszegOszlop = Ceg1_XBank_Al_OsszegOszlop
             tmpSzin = Ceg1_XBank_Al_Szin
        Case "Ceg1_YBank_Fo"
             Felirat = "Cég1_YBank_Fő"
             tmpKivonat_arr = Ceg1_YBank_Fo_arr
             tmpErteknapOszlop = Ceg1_YBank_Fo_ErteknapOszlop
             tmpOsszegOszlop = Ceg1_YBank_Fo_OsszegOszlop
             tmpSzin = Ceg1_YBank_Fo_Szin
        Case "Ceg2_YBank_Fo"
             Felirat = "Cég2_YBank_Fő"
             tmpKivonat_arr = Ceg2_YBank_Fo_arr
             tmpErteknapOszlop = Ceg2_YBank_Fo_ErteknapOszlop
             tmpOsszegOszlop = Ceg2_YBank_Fo_OsszegOszlop
             tmpSzin = Ceg2_Ybank_Fo_Szin

    End Select

End Sub

asd

Sorozatspecifikus tömbök feltöltése

A célunk ezzel az eljárással, hogy (minden egyes megjeleníteni kívánt bankszámlához) kapjunk két számsort: az egyik a dátumokat tartalmazza, a másik az adott dátumhoz tartozó egyenleget. A szubrutin működése a következőképp kell hogy kinézzen (zárójelben kékkel a vonatkozó változó neve):

• Vegye a vezérlőpanel kezdődátumát (Datumtol), ez lesz a dátum tömbünk (XArray) első eleme.
• A megfelelő bankkivonat (tmpKivonat_arr) értéknap oszlopában (tmpErteknapOszlop) keresse meg a kezdődátumot,
• majd összegezze az adott dátumon valamint az azelőtt történt mozgásokat a bankkivonat összeg oszlopában (tmpOsszegOszlop) – ez lesz a kezdődátumra vonatkozó egyenlegünk (feltételezzük, hogy a bankkivonat a legkorábbi dátummal tartalmazza a bankszámla nyitóegyenlegét). Az egyenleget tárolja el az érték tömbünk (YArray) első elemeként.
• Ezután növelje a dátumot eggyel, tárolja el a dátum tömb következő elemeként, végezze el az adott dátumra az értékek összegzését, adja hozzá az előző napi záróegyenleghez, ez lesz a napi záró egyenleg. Tárolja el az érték tömb következő elemeként.
• Az előző lépést ismételje addig, amíg el nem érjük a vezérlőpanelen megadott záró dátumot (Datumig).

A mostani bejegyzésnek a tömbök használata nem képzi központi elemét, ezért ahhoz több magyarázatot most nem fűznék, lássuk inkább a programkódot (természetesen, ha valami nem tiszta és kérdésük van, tegyék fel bátran):

Sub Levalogatas()
 
'****** Deklarációk *******************
    Dim DatumSzamlalo As Long
    Dim i As Long
        i = 0
    Dim j As Long
        j = 0
    Dim NapiEgyenleg As Long
    NapiEgyenleg = 0
    ReDim EredmenyYArray(1) 'összeg
    ReDim EredmenyXArray(1) 'dátum
'**************************************

'kezdődátum egyenlegének kiszámítása
    i = 1
    ReDim Preserve EredmenyXArray(i)
    ReDim Preserve EredmenyYArray(i)

    DatumSzamlalo = Datumtol
    EredmenyXArray(i) = DatumSzamlalo

'   Sum összeg, if tranzakciódátum <= Kezdődátum
    NapiEgyenleg = tmpNyitoEgyenleg
    For j = 1 To UBound(tmpKivonat_arr, 1)
        If tmpKivonat_arr(j, tmpErteknapOszlop) <= DatumSzamlalo Then
            NapiEgyenleg = NapiEgyenleg + tmpKivonat_arr(j, tmpOsszegOszlop)
        End If
        EredmenyYArray(i) = NapiEgyenleg
    Next j

'ciklus
    For DatumSzamlalo = Datumtol + 1 To Datumig
 
        i = i + 1
        ReDim Preserve EredmenyXArray(i)
        ReDim Preserve EredmenyYArray(i)

        EredmenyXArray(i) = DatumSzamlalo
 
'       Sum összeg, if tranzakciódátum = dátumszámláló
        For j = 1 To UBound(tmpKivonat_arr, 1)
            If tmpKivonat_arr(j, tmpErteknapOszlop) = DatumSzamlalo Then
                NapiEgyenleg = NapiEgyenleg + tmpKivonat_arr(j, tmpOsszegOszlop)
                'záróegyenleg = előző napi záró + napi mozgás
            End If
        Next j
 
        EredmenyYArray(i) = NapiEgyenleg
 
    Next DatumSzamlalo
 
End Sub

asd

Grafikon formázása – végső simítások

Miután feltöltöttük adatokkal a grafikonunkat, meg tudjuk ejteni azokat a formázásokat is, amikhez szükséges a feltöltött grafikon megléte. A mostani példánkban egy ilyen maradt, méghozzá a vízszintes tengely minimum-maximum értékének beállítása (a grafikonok színét már a létrehozásukkor megadtuk):

Sub Grafikon_Formazas()
 
    With Grafikon.Axes(xlCategory)
        .MinimumScale = Datumtol
        .MaximumScale = Datumig
    End With
 
End Sub

Legkorábbi dátum meghatározása

A vezérlőpanelünkre felraktunk két gombot, aminek a kódolásáról az előző bejegyzésben még nem ejtettünk szót, mivel az eljárás az előző bekezdésekben definiált tömböket használja.

Kezdjük az egyszerűbb témával. A záró dátum meghatározásához szerkesztettünk egy gombot, amivel a mai dátumra lehet a dátummező értékét frissíteni. A mai dátumot a Date függvénnyel kapjuk meg, az eljárásunk annyi lesz, hogy a gomb megnyomásakor a “Datumig” változónkat és a záródátum szövegmezőt állítsa át a mai dátumra (és természetesen rögtön frissítse is a grafikont).

A ThisWorkbook modul eljárásokon kívül eső részén deklaráljuk az eseményeket:

Private WithEvents CmdBtn_Datumtol As CommandButton
Private WithEvents CmdBtn_Datumig  As CommandButton

Majd jöhet a kattintás eseménye:

Private Sub CmdBtn_Datumig_Click()
 
    Datumig = Date
    TextBox_Datumig.Value = Format(Datumig, "yyyy.mm.dd")
'--------------------------------------
    ChartFrissites
'--------------------------------------
 
End Sub

A legkorábbi rendelkezésre álló dátum meghatározása már trükkösebb, hiszen a példánkban 4 külön munkalapon szereplő 4 táblában kell a  dátumokat hozzá ellenőrizni.

A dátum kikeresését külön eljárásba rakjuk, a kattintás eseményébe csak ennyi kerül:

Private Sub CmdBtn_Datumtol_Click()
 
'--------------------------------------
    LegkorabbiDatumtol
'--------------------------------------
 
    Datumtol = LegkorabbiDatum
    TextBox_Datumtol.Value = Format(Datumtol, "yyyy.mm.dd")
 
'--------------------------------------
   ChartFrissites
'--------------------------------------
 
End Sub

Lássuk először szövegesen, mit is várunk a “LegkorabbiDatumtol” eljárásunktól:

• Hozzunk létre egy gyűjteményt a bankkivonatokból, minden elemhez rendeljük hozzá az adott bankkivonat tömbbe betöltött értékeit. Erre szolgál az “AdatDefinicio” eljárásunk, azt hívjuk meg itt is.
• Menjünk végig a gyűjtemény minden egyes elemén, pontosabban a megfelelő bankkivonatokat tartalmazó tömbökön. Minden egyes bankkivonatnál:
• menjünk végig a dátum oszlop elemein (az egyes tranzakciók dátumain), majd keressük ki az adott kivonat legkisebb dátumát. Ezt úgy fogjuk elérni, hogy ha a vizsgált dátum kisebb, mint a “LegkorabbiDatum” változónkban eltárolt dátum, akkor felülírjuk a változó értékét a vizsgált dátummal.
• Miután végiglépdeltünk az összes kivonat összes dátum mezőjén (mivel tömbökkel dolgozunk és nem cellákkal, ezt az Excel a másodperc töredéke alatt elvégzi), a “LegkorabbiDatum” változónk a legkisebb dátumot fogja tartalmazni.

Deklaráljuk a projekt szintű változót a fő modulunk elején:

Public LegkorabbiDatum As Long

Végezetül maga az eljárás:

Sub LegkorabbiDatumtol()
 
'****** Deklarációk *******************
    Dim BankKivonatok As Collection
    Dim BankKivonat   As Variant
    Dim i             As Long
    LegkorabbiDatum = Date
'**************************************
 
'--------------------------------------
    AdatDefinicio
'--------------------------------------

'Bankkivonatokból gyűjtemény létrehozása
    Set BankKivonatok = New Collection
    With BankKivonatok
        .Add "Ceg1_XBank_Fo_arr"
        .Add "Ceg1_XBank_Al_arr"
        .Add "Ceg1_YBank_Fo_arr"
        .Add "Ceg2_YBank_Fo_arr"
    End With
 
    For Each BankKivonat In BankKivonatok
 
        Select Case BankKivonat
            Case "Ceg1_XBank_Fo_arr"
                tmpKivonat_arr = Ceg1_XBank_Fo_arr
                tmpErteknapOszlop = Ceg1_XBank_Fo_ErteknapOszlop
            Case "Ceg1_XBank_Al_arr"
                tmpKivonat_arr = Ceg1_XBank_Al_arr
                tmpErteknapOszlop = Ceg1_XBank_Al_ErteknapOszlop
            Case "Ceg1_YBank_Fo_arr"
                tmpKivonat_arr = Ceg1_YBank_Fo_arr
                tmpErteknapOszlop = Ceg1_YBank_Fo_ErteknapOszlop
            Case "Ceg2_YBank_Fo_arr"
                tmpKivonat_arr = Ceg2_YBank_Fo_arr
                tmpErteknapOszlop = Ceg2_YBank_Fo_ErteknapOszlop
        End Select
 
        For i = 1 To UBound(tmpKivonat_arr, 1)
            If tmpKivonat_arr(i, tmpErteknapOszlop) < LegkorabbiDatum Then
                LegkorabbiDatum = tmpKivonat_arr(i, tmpErteknapOszlop)
            End If
        Next i
 
    Next BankKivonat 
 
End Sub