Office Guru

Diagramok formázgatása megadott feltételek szerint VBA-kóddal

2015. december 24. - Office Guru

Projektes státuszjelentésekben, bevételről szóló beszámolókban időnként hasznos lehet, ha bizonyos státuszú, bizonyos telephelyhez köthető elemeinket kiemelten tudjuk prezentálni diagramos formában, ehhez fog segítséget nyújtani a következőkben bemutatásra kerülő kis makró, ami nem tesz mást, mint az általunk megadott paraméterek alapján egy diagramban bizonyos oszlopok, sorok színét fogja megváltoztatni.

Legyen a kiindulópontunk a következő apró kis táblázat:

620.jpgMost készítsünk ebből egy teljesen egyszerű oszlopdiagramot (persze lehet akár háromdimenziós oszlop vagy "bar" is) mondjuk a lentieknek megfelelően:

621.jpgEnnek az elkészítéséről már nem fogok részletesen írni, korábbi posztokban részletesen taglaltam a diagramok elkészítésének menetét, ha valakinek segítségre lenne szüksége, jelezze nekem valamilyen kommunikációs csatornán. Az igényesebb megvalósítás érdekében (persze azért az igazi igényességtől ez messze lesz) szúrjunk be még a Ribbonunk Developer füle alatt található Controls szekció Insert parancsával egy gombot is, ami kattintással fogja elindítani a kis színezésünket.

622.jpgEhhez fogjuk aztán majd hozzárendelni azt a makrót, ami az x tengelyünk elnevezései közül mondjuk Gyömrő esetében átszínezi az oszlopot, mert például erre a városra szeretnénk nagyon koncentrálni bemutatónkban (de hangsúlyozom, lehet akár érték, akár más paraméter alapján is átdolgozni a makrót).

Kattintsunk jobb gombbal a gombon és View Code parancsot használjuk:

623.jpgÉs máris a VBA-editorban vagyunk, ahol szépen a kattintás eseményünkhöz kidolgozhatjuk a kódot, amit szeretnénk kattintás esetén lefuttatni. Ahogy megszoktuk, első lépésként kezdjük változók deklarálásával, méghozzá kapásból néggyel.

Kell egy változó Chart típussal magának a táblázatunknak, kell egy változó Series típussal a táblázatunk összes adatmező sorozatának tárolására (itt gondoljunk például mondjuk az X vagy Y tengely megnevezéseire, sor illetve oszlopértékeire) illetve még két változó Integer típussal, amelyekre majd a ciklusunkban lesz szükség - hiszen itt is csak ciklussal tudjuk végignézni az összes megnevezésünket a diagramban:

624.jpgMost lássunk hozzá és kezdjük feltölteni a változóinkat, azaz állítsunk be kezdőértéket, legalább a táblázatoshoz illetve az adatmezőshöz. Amikor egy Chart aktív objektum, akkor az ActiveChart tulajdonsággal tudunk rá hivatkozni, így az első esetben c változónk kezdőértéke legyen az ActiveChart, tehát a diagramunk - itt álljunk meg arra a gondolatra, hogy ha nincs éppen kijelölve a diagramunk vagy más diagramok is vannak más sheeteken a táblázatunkban, akkor mindenképpen aktiválni kell a megfelelőt az Activate utasítással. Itt most annyiban leegyszerűsítettem a kódot, hogy ettől eltekintek.

S változónkat pedig ActiveChart objektumunk SeriesCollection(1) tulajdonságával kell feltöltenünk, ami az aktív objektumunk első sheetjén szereplő diagramunk első adatsorának (series - a tengelyeinket szereplő nevek) értékeit fogja visszaadni, jelen esetben városaink nevét töltjük be ide.

625.jpgA ciklusban felhasználásra kerülő l változónkba pedig töltsük be az aktív diagramunk első adatsorának adatmező darabszámát, avagy VBA-ra átfordítva az:

ActiveChart.SeriesCollection(1).Points.Count

utasítást, ami ebben a kódban már meghivatkozható a korábbi változók felhasználásával.

626.jpgSok már nincs hátra, ahogy kitalálhattuk, következik a ciklusunk maga, azaz a másik változónkba töltött egyes kezdőértéktől egészen az l változónkba az előbb már betöltött adatmező darabszámig futtasson egy IF utasítást, méghozzá a következőkben kifejtettet.

Előbb azonban két dolgot kell megértenünk, az egyik az ActiveChart.SeriesCollection(1).XValues tulajdonság, ami konkrétan az aktív táblázatunk első adatsorozatából adja vissza az x tengely értékét a megadott helyre vonatkozóan, a másik pedig a Right funkció, ami teljesen ugyanúgy működik VBA-ban, mint az Excelben, azaz a paramétereként megadott darabszámnyi karaktert fog nekünk visszadobni a megadott szöveg jobb oldaláról.

Így pedig össze is állt nagyjából If utasításunk első sora:

If Right(s.XValues(i), 40) = "Gyömrő" Then

Ha az ActiveChart.SeriesCollection(1) x tengelyének i helyén szereplő érték utolsó negyven karaktere az mondjuk, hogy Gyömrő akkor ugrik a második sorra, ahol az

s.Points(i).Interior.Color = RGB(255, 0, 0)

utasítással fogjuk szépen az ActiveChart.SeriesCollection(1) i-edik pontjának színét átállítani RGB(255, 0, 0) színre, ami a piros.

Fekete: RGB(0, 0, 0)
Fehér: RGB(255, 255, 255)
Zöld: RGB(0, 255, 0)
Kék: RGB(0, 0, 255)

És ha ezzel megvagyunk, akkor csak le kell zárnunk az IF-et és a FOR ciklust:

627.jpg

Most futtassuk le a makrót. Oppá, hibaüzenetet kaptunk:

629.jpgEzt a részt könnyedén kivehettem volna a posztból, de úgy gondoltam talán hasznos lehet egy kis közös problémamegoldás is, szóval mit jelenthet az üzenet? Az üzenet szerint egyértelműen valamelyik változónkkal lehet a probléma. Na de melyikkel? Ahogy végigfutjuk ezt a tényleg roppant aprócska kis kódot, egyből szemünkbe ötlik a fent már emlegetett megoldás, azaz c változónk kezdőértékének ActiveChartra való beállítása. Hiszen ha a gombra kattintunk, akkor nem lehet kijelölve a diagramunk!

Azaz egyetlen egy plusz sorra van szükségünk a kód elején még (mindenképpen a Set c = ActiveChart elé), ami nem tesz mást, mint a gomb lenyomása után kijelöli a táblánkat, valahogy így:

ActiveSheet.ChartObjects("Chart 4").Activate

Azaz aktív sheetünk Chart 4 névre hallgató diagramjának (ezt a nevet diagramunk tulajdonságai közül tudjuk kinyerni) aktiválása.

És most már szépen lefut:

628.jpg

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr138195348

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

FikkFakk 2015.12.25. 00:20:19

Nagyon tetszenek az irasaid, de ezzel most elvi sikon egy picit vitatkoznek.

Nem szep megoldas, hogy hardcode-oltuk gyomrot a ciklusba. Nem is celszeru, mivel ha kiderul, h Gyomro mellett mondjuk Debrecen oszlopat zoldre szeretnenk szinezni, mindjart nehezkes a dolog.

Vezessunk be inkabb egy 0. oszlopot, abba opcionalisan vigyuk fel a szint, es azt alkalmazzuk egy-egy oszlopra. Es mindjart szabadabban szinezgetunk, nem?

Amugy miert kell a RIGHT, mert nem lehet egy az egyben gyomrore ellenorizni

Office Guru 2015.12.25. 10:14:15

@FikkFakk: Teljesen jogos a komment, igazából csak a kezdő lépést kívántam megadni ezzel a poszttal, hiszen általában az ilyesmit én is a usertől kérném be valamilyen felugró ablakkal, mezővel stb., hiszen hardcode-olni bármit is, elég nagy hülyeség.

Koholmány 2015.12.26. 11:17:59

sziasztok. tudtok nekem ajánlani access vba programozás könyvet? pròbáltam neten keresni de amit találzam az nem rendelhető. Lehetséges hogy én vagyok hülye, tisztában vagyok vele! :D Köszi :)

Office Guru 2015.12.30. 18:31:55

@Koholmány: Én a különösen nagytudású Kovalcsik Géza tanár úr Az Excel Programozása című művét ajánlanám kezdőpontnak.

delkreta 2017.01.20. 12:39:20

sziasztok!
Nagyon hasznos ez az oldal, sok jó dolgot találok. Most kezdenék ismerkedni VBA-ban a grafikonokkal és abban kérném a segítséget, hogy hol találok kifejezetten a grafikonok utasításkészletére egy gyűjteményt, -magyarázatokkal (melyik paraméter mit változtat...), sok oldalon sokféle dolog van, de ez egy hangyaszedegetés....
Segítségeteket előre is köszönöm!
süti beállítások módosítása