Nem sűrűn, sőt szerintem szinte szökőévenként van arra szükség, hogy egy Powerpoint-előadásba makrót csempésszünk, de legalább annak tudatosításáért, hogy erre van lehetőségünk, egy rövid kis VBA-kódot mutatnék meg, amelyet bevethetünk prezentációnk során. Hangsúlyozni kell persze már a poszt elején, hogy erre a most felvázolt kis történetre van más megoldás is, sőt akár egy-egy plusz dia beszúrásával is elérhetjük a célunkat, de talán van valaki, akinek pont ilyen kódra van szüksége.

Az esemény, amit körbe fogunk járni, nem más, mint egy üzenet kiírása a képernyőre egy bizonyos dia megjelenése esetén, amivel akár jelezhetjük a szünetet is hallgatóságunknak és magunknak, de akár ellenőrző kérdéseket is tehetünk be vele.

Adott egy roppant egyszerű, szimplán szöveget tartalmazó színtelen kis prezentációnk (semmi extra, hiszen most nem is ez a lényeg):

Azt szeretnénk most elérni, hogy a második slide levetítése után a képernyőn egy üzenet jelenjen meg, amely figyelmeztet minket, hogy itt a szünet ideje és ennek elokézása után folytatni tudjuk a vetítést.

Szépen menjünk át a VBA-editorba a megszokott módokon - billentyűkombinációval vagy a Developer fülről, majd itt hozzunk létre egy új modult:

Aztán már jöhet is szépen a kódunk, amelyhez a Microsoft által a kezünkbe adott OnSlideShowPageChange() eseményt fogjuk felhasználni, ez ugyanis minden egyes slide megjelenésekor elindul, ebből pedig értelemszerűen kikövetkeztethető, hogy itt meg is határozhatjuk, hogy melyik diánál mi történjen a képernyőn.

Nem csak üzenetet írathatunk ki egyébként, akár meg is változtathatjuk a kurzorunk alakját vagy a diánk háttérszínét, de azt is beiktathatjuk, hogy még egy kattintást várjon tőlünk a Powerpoint, mielőtt indítja az eseményünket - a lehetőségeink nagyjából korlátlanok. Az OnslideShowPageChange() esemény paramétere a SlideShowWindow érték, azaz ez egy olyan set, amelynek az értékét más funkciókkal már nem tudjuk meghivatkozni.

Innentől pedig már csak egyetlen tulajdonságot kell ismernünk, ami nem más, mint a View.CurrentShowPosition tulajdonság, ami visszaadja a vetítésünkben az aktuális diánk helyzetét, ami éppen a képernyőn van. Ha pedig ez az aktuális dia éppen a második, akkor jöhet az üzenetünk:

Sub OnSlideShowPageChange(ByVal SSW As SlideShowWindow)
If SSW.View.CurrentShowPosition = 2 Then
MsgBox "Itt az idő egy kis szünetre!", vbOKOnly, "Fontos üzenet"
End If
End Sub

A hétvége lezárásaként a mai nap nagy kérdésére keressük a választ, azaz a Word alapvetően elviselhető, de hosszú távon idegesítő és bizonyos szempontból még munkát is lassító, "Pick up where you left off:" extráját hogyan is kell kikapcsolni. Ugyebár ez az a funkció, amely szövegszerkesztőnk bezárása után a legközelebbi indításnál oda fog navigálni minket, ahol legutoljára jártunk, tehát lehetővé teszi, hogy ott folytassuk a munkánkat, ahol abbahagytuk.

A Microsoft hivatalos kommunikációja szerint ez egy beépített funkció, amely bizonyos szempontból a Word könyv- és dokumentumolvasóként való felhasználását igyekszik segíteni és amely nem is kapcsolható ki nagyon egyszerűen - valószínűleg egyébként a cég ezzel a mobilplatformok és a könyvolvasók felé igyekszik nyitni.

Persze azért nem szabad feladnunk, ha nem is közvetlenül a Word applikációban, de van mód arra, hogy eltávolítsuk ezt a funkciót a programból. Sajnos ehhez adminisztrátornak kell lennünk a gépen azaz vagy otthon tudjuk csak ezt megcsinálni vagy jól össze kell barátkoznunk a cégnél a rendszergazdával, mert ellenkező esetben, jogosultságok hiányában kénytelenek leszünk vele együttélni.

Szóval vegyük a legjobb opciót, azaz van adminisztrátori jogunk a gépen, így egyszerűen csak indítsuk el mondjuk a Start menü parancssora segítségével a regeditet:

Itt szépen a fastruktúrában keressük meg a következő elérési úton szereplő kulcsot: HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Word\Reading Locations

Emlékezzünk rá, hogy a HKEY_CURRENT_USER alatt ha változtatunk bármit is, az kizárólag azt a felhasználót fogja érinteni, aki éppen be van jelentkezve a számítógépre.

Két feladatunk lesz itt:

- elsőként a Reading Locations alatt látható dokumentumlista (Document 0, Document 1 stb.) elemeit kell egyesével kitörölnünk, ezek ugyanis azok a dokumentumok, amelyeket megnyitottunk már
- ha ezzel megvagyunk, akkor a Reading Locations kulcsra kattintsunk jobb gombbal, majd válasszuk ki a Permissions menüpontot

A Permissions alatt kattintsunk az Advanced gombra és a megnyíló Advanced Security Settings for Reading Locations ablakban minden hozzáférést módosítsunk le Full Controlról sima Read jogra, így már semmilyen program nem tudja majd írni ezt a kulcsot, ergó elimináltuk a Word számára a lehetőséget, hogy tárolja, hol is tartottunk a dokumentumunkban.

Ezzel kész is vagyunk. Egy dolgot viszont azért jobb ha észben tartunk: a registryben módosítani és egyáltalán, kóvályogni nem annyira tanácsos ha nem igazán tudjuk mit is akarunk pontosan.

Úgy látszik, hogy ez most már csak egy ilyen hét lesz, ugyanis a mai téma sem más, mint VBA és ezúttal is egy olvasói kérdést fogunk körbejárni, de a korábbi posztokban átnézett levélküldési megoldások ismeretében ezzel már relatíve gyorsan fogok végezni.

Szóval kedves Olvasóm a következőt szeretné elérni: van egy hatalmas Excel-fájlja rengeteg adattal és ebben rendszeresen szokott szűrni (mondjuk azt, hogy például vevőnév alapján), majd a leszűrt állományt egy másik Excelbe rakja át, amit aztán levélben továbbküld. A cél ennek elérése úgy, hogy a szűrésen kívül semmilyen egyéb feladata ne legyen már.

A kérdés pont kapóra jött, hiszen mostanában ilyen témákat jártam körül, tehát maga a levélküldés önmagában nem okozhat már gondot, amiről még nem beszéltünk az az új workbookok létrehozása, törlése és szűrt állományok másolása.

Első lépésként létrehoztam egy gombot az adatállományunk sheetjén és ehhez rendelem majd hozzá a kódot:

A kódot értelemszerűen a változók deklarálásával kezdjük, mivel e-mailt küldeni fogunk, ezért a megszokott módon, objektum típussal deklarálunk egy outlook és egy mail nevű változót, hiszen így majd ezen osztályok bármely tulajdonságához, metódusához hozzáférhetünk később.

Egy lendülettel állítsuk is be a két változó kezdő értékét, az outlook maga az Outlook applikáció elindítása, a mail pedig egy új e-mail létrehozására vonatkozó utasítás, amelyet az outlook változó utasításaként hívunk meg.

Workbooks.Add utasítás segítségével tudunk létrehozni egy új Excel-munkafüzetet, amit aztán le is mentünk a megfelelő helyre. Mivel akkor már az új munkafüzetünk az aktív munkafüzet, így bátran használhatjuk az ActiveWorkbook.SaveAs utasítást:

Workbooks.Add
ActiveWorkbook.SaveAs "C:\...az uj fajl neve"

Ezután jön rövid kódunk kulcsmomentuma, hiszen egyrészt a Workbooks("...xls").Activate utasítással ismét az adatállományt tartalmazó munkafüzetünket tesszük aktívvá, majd ennek első munkalapján kezdünk dolgozni.

Szóval ismét csak írjuk le gondolatban, mit is akarunk tenni: az első munkalapunk (Worksheets("Sheet1")) összes adatot tartalmazó cellájának (ez a Worksheet.UsedRange tulajdonság) azon speciális halmazára van szükségünk (itt a Range.SpecialCells metódust vetjük be, amelynek paraméterei között van az a paraméter, ami most kell nekünk), amelyek éppen láthatóak a táblában (ez a Range.SpecialCell metódus xlCellTypeVisible tulajdonsága - a részletes tulajdonságlista a Microsoft leírásában megtalálható), majd ezt rakjuk be a vágólapra (.Copy). A .Copy metódusnak egyetlen paramétere van, ez a beillesztési paraméter (Destination), amely után := megadásával tudunk felvinni célt, jelen esetben az új fájlunk (Workbooks("az uj fajl.xls")) első munkalapjának (Sheets("Sheet1")) A1 cellájába illessze be állományunkat (.Range("A1")).

Workbooks("Ahol az adathalmaz van.xls").Activate
Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Workbooks("az uj fajlom").Sheets("Sheet1").Range("A1")

Ezután pedig mondhatni megszokott részek következnek, hiszen With...End With felhasználásával az újonnan létrehozott levelünket készítjük elő (ehhez deklaráltuk és állítottuk be a mail változót), méghozzá mondjuk egy címzett (.To), egy tárgy (.Subject), egy szöveg (.Body) megadásával, majd az eddig még nem használt .Attachments.Add ("C:\...az uj fajl.xls") metódussal becsatoljuk a leszűrt állományt tartalmazó fájlunkat és küldés előtt még átnézzük (.Display).

With mail
.To = "officeguruhelp@gmail.com"
.Subject = "OfficeGuru"
.Body = "Csatolva küldöm a részleteket!"
.Attachments.Add ("C:\...az uj fajl neve.xls")
.Display
End With

Ezután még töröljük ki fájlunkat egy Kill (C:\...az uj fajl neve.xls") utasítással, ürítsük ki a változóinkat és kész is vagyunk, a gombra kattintva elérjük a célt, amit olvasóm is szeretett volna.

Íme a kód szövegként is:

Private Sub CommandButton1_Click()
Dim outlook As Object
Dim mail As Object
Set outlook = CreateObject("Outlook.Application")
Set mail = outlook.CreateItem(0)
Workbooks.Add
ActiveWorkbook.SaveAs "C:\...az uj fajl neve"
Workbooks("Ahol az adathalmaz van.xls").Activate
Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Workbooks("az uj fajlom").Sheets("Sheet1").Range("A1")
With mail
.To = "officeguruhelp@gmail.com"
.Subject = "OfficeGuru"
.Body = "Csatolva küldöm a részleteket!"
.Attachments.Add ("C:\...az uj fajl neve.xls")
.Display
End With
Set mail = Nothing
Set outlook = Nothing
End Sub
End Sub

Ha már az előző posztban is VBA-ról volt szó, akkor még melegében folytassuk tovább egy VBA-s olvasói kérdés megválaszolásával, amely nagyjából egyetlen felvetésre keresi a választ: hogyan érhetjük el azt, hogy egy rendszeresen legenerált automatikus e-mailünkbe a szöveget egy WORD dokumentumból illesszük be?

Ebben a posztban egy bizonyos megoldást fogok bemutatni, de ahogy általában mindig, most is léteznek más megoldási utak, ráadásul az én javaslatom a maga módján még egyszerű is, hiszen csak konstans szöveget használok, nem fogom megspékelni folyton változó címzettekkel vagy szövegrészekkel.

VBA-editorba lépve (ALT+F11 legyen jó barátunk), szubrutinunkat a szokásoknak megfelelően kezdjük változók deklarálásával, a hivatalos Microsoft leírásnak megfelelően legyen két változónk a meghívott applikációkra:

Dim word As word.Application
Dim outlook As outlook.Application

Majd legyen két másik változónk egy Word-dokumentum és egy Outlook-mail objektumra:

Dim doc As word.Document
Dim mail As outlook.MailItem

Ha ez megvan, akkor állítsuk is be outlook illetve word változónk kezdő értékét a szintén a hivatalos Microsoft leírásból is kileshető CreateObject funkció segítségével, amely lehetővé teszi, hogy egy másik applikációval dolgozzunk:

Set outlook = CreateObject("Outlook.Application")
Set word = CreateObject("Word.Application")

Ahhoz, hogy lássuk is a másik applikáció sessionjét, az adott applikáció Visible tulajdonságát kell True-ra állítanunk:

word.Visible = True

A fentebb deklarált WORD dokumentumváltozó értékének állítsuk be a lementett, levélszöveget tartalmazó WORD fájlunk írásvédettként megnyitott változatát - a Read-Only paramétert a word.Documents.Open metódus paramétereként tudjuk beállítani.

Set doc = word.Documents.Open(Filename:="C:\......docx", ReadOnly:=True)

Most már nagyon közel a feladat vége, ugyanis most milyen feladat vár még ránk? Meg kell fognunk ebből a WORD doksiból a szövegünket, majd be kell raknunk a vágólapra és utána beilleszteni egy Outlookban megnyitott üzenetbe.

Elsőként fogjuk a doc változónkban beállított WORD-dokumentumunkat, majd Content.Select utasítás segítségével jelöljük ki a tartalmát és a hivatalos leírás szerinti Selection.Copy utasítással pakoljuk a vágólapra:

Mi a következő lépés? A WORD tehát már készen áll és be is fejeztük a használatát, most átmegyünk Outlookba, megnyitjuk az e-mailünket, beillesztjük a vágólapról a tartalmat, amit szeretnénk és kész is vagyunk. Elsőként a WORD dokumentumhoz hasonló módon a mail változóba beállított outlook.CreateItem(olMailItem) utasítás segítségével megnyitunk egy e-mailt, amelyet egy With...End With utasítás segítségével formára szabunk:

- megjelenítjük a képernyőn
- beállítunk egy címzettet
- adunk egy tárgyat
- és a legfontosabb lépésként a Word applikációra beállított word változó felhasználásával levelünk Body részét egyenlővé tesszük a WORD-ben kijelölt majd kimásolt tartalommal

Mi van még hátra? A biztonság kedvéért zárjuk be a WORD-dokumentumunkat a Close utasítás segítségével, majd az összes változónkat ürítsük ki:

És itt a kód szövegként is:

Sub Doksilevel()
Dim word As word.Application
Dim doc As word.Document
Dim outlook As outlook.Application
Dim mail As outlook.MailItem
Set outlook = CreateObject("Outlook.Application")
Set word = CreateObject("Word.Application")
word.Visible = True
Set doc = word.Documents.Open(Filename:="C:\........docx", ReadOnly:=True)
doc.Content.Select
word.Selection.Copy
Set mail = outlook.CreateItem(olMailItem)
With mail
.Display
.To = "officeguruhelp@gmail.com"
.Subject = "Megoldás"
.Body = word.Selection
End With
doc.Close
Set doc = Nothing
Set mail = Nothing
Set outlook = Nothing
Set word = Nothing
End Sub

És ezzel adtunk egy választ egy bizonyos kérdésre, de hangsúlyozom, a fenti egy roppantul leegyszerűsített megoldás, hiszen nincsenek benne hibakezelési utasítások, nincsenek változó részek a szövegben és így tovább, de ennek ellenére ahogy mindig, most is remélem, hogy vannak, akiknek ez segítség lehet. És persze tördeljük szépen a kódunkat, ne úgy ömlesszük, ahogy én tettem fentebb.

Rövidebb kihagyás után egy, Exceles automatizációk során elengedhetetlen elemmel fogunk megismerkedni, mert a UserFormok használata jelentősen javíthatja a felhasználóinkkal az interakciót, sőt, ezzel instruálhatjuk is őket bizonyos lépések végrehajtására.

Hogy kezdjünk neki? Első lépésként lépjünk a Visual Basic Editorba a Ribbonról vagy az Alt+F11 billentyűkombináció lenyomásával:

Ha bent vagyunk az Editorban, akkor az Insert menü UserForm parancsával szúrjuk be a felületünket:

Most majd erre a formra fogjuk elhelyezni mezőinket illetve azok neveit. A mezők elnevezéseit értelemszerűen a Label control gombjára való kattintással tudjuk beszúrni:

Ezután következhet a nevekhez tartozó, adatbevitelt lehetővé tevő mező, azaz a Textbox:

Értelemszerűen jobb gombbal kattintva formunk bármely elemén, annak tulajdonságait a megjelenő Properties ablakban változtatni tudjuk:

A UserFormmal kapcsolatban lehetünk teljesen kreatívak, olyat készítünk, amilyet csak akarunk vagy amilyenre szükségünk van, én most még egy gombot fogok elhelyezni ezen a formon, amivel a megadott adatokat egy Excel-táblázatba fogom rögzíteni. Ezt a Controlok közül a CommandButton beszúrásával érem el:

Így néz ki most a form:

Ha a felület elkészült, meg kell határoznunk a mögötte lévő kódot, ami jelen esetben a Hozzáadás gombra való kattintás esetén futna le. Ezt a CommandButtonon jobb gombbal katintva előhívható menüből a View Code parancs használatával határozhatjuk meg:

Mit is várunk el a kódtól a gombra való kattintás után? Az elvárásaink szerint meghatározott sorok meghatározott celláiba a UserFormon megadott értékeket rögzítse be. Ehhez nagyjából tudnunk kell, hogy hol van a következő üres sor a táblában majd a kitöltés után értelemszerűen egyenlővé kell tennünk a megfelelő cellákat a megfelelő mezőkbe felvitt értékekkel.

Kezdésként definiáljunk két változót, az elsőt sor néven az aktuális, még üres sorunk meghatározására Integer típussal, a másodikat pedig ws néven Worksheet típussal, hiszen ez fogja meghatározni, hogy melyik munkalapon fogja a formunk frissíteni a cellákat. Ez utóbbi változónak már be is állíthatjuk az értékét a Sheet1 munkalapra.

Sor változónk kezdő értékének meghatározásához a Range.Find metódust, jelen esetünkben a ws.Cells.Find metódust kell használnunk, hiszen a Sheet1 cellái között keressük azokat, amelyek tartalmaznak valamit (What:="*"), soronként keressük (SearchOrder:=xlRows - a másik opciónk az xlColumns lehetne) az utolsó (SearchDirection:=xlPrevious - ez még xlNext lehetne, ha a következőt keresnénk) értéket (LookIn:=xlValues) tartalmazó sort, majd ehhez a számhoz hozzáadunk egyet, hogy megkapjuk az első üres sort:

sor = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Innentől kezdve pedig alapvetően egyszerű a dolgunk, hiszen a mezőkbe beírt értékeinkkel kell egyenlővé tenni a sor változónkban lévő sorszám megfelelő számú celláinak értékét.

Mezőink nevét a UserFormon előhívott Properties ablakból is megtudhatjuk, ha esetleg nem standard elnevezéseket használtunk végig.

Azaz ws.Cells(sor, 1).Value (ez Sheet1 első üres sorának első oszlopában lévő cella) legyen egyenlő Me.TextBox1.Value értékkel, ami az első mezőnkbe beadott érték. Me minden esetben arra a szülő objektumra utal, amiben a kódunk "van", jelen esetben a UserFormra utal, de ha egy Sheeten írnánk a kódunkat, akkor arra utalna.

Itt célszerű esetleg informálni a usert arról, hogy mi történt a bevitt adatával, tehát az MsgBox utasítás segítségével írjunk ki neki egy üzenetet. Ezen a felugró üzenetablakon most csak egy OK gomb legyen (vbOKOnly paraméter), de ezt értelemszerűen az MsgBox további paraméterei segítségével úgy tuningoljuk, ahogy akarjuk.

Ha ez megvan, akkor szépen töröljük ki mezőinkből a bevitt értékeket (hiszen azok már a megfelelő cellákban vannak) egyszerűen úgy, hogy az Me.TextBox1.Value értékét a semmivel tesszük egyenlővé. Ezután pedig a SetFocus paraméter segítségével célszerű visszaugrasztani a kurzort az első mezőbe.

Alapvetően minden kis kódot a lehetséges hibák minimalizálásával célszerű megírni, ezért érdemes esetleg arra beszúrni egy If elágazást, hogy üresen hagyott mezők esetén ne csináljon semmit a UserForm. Hogy csináljuk ezt meg? Egyszerűen fordítsuk át gondolatainkat a kódra, azaz ha (If) a szóközök eltávolítása (Trim) után első mezőnk értéke (Me.TextBox1.Value) egyenlő a nagy semmivel (= "") akkor (Then) írjon ki egy üzenetet (MsgBox "A form nincs kitöltve!"), majd lépjen ki (Exit Sub) és ezzel le is zárhatjuk az If elágazást (End If).

If Trim(Me.TextBox1.Value) = "" Then
MsgBox "A form nincs kitöltve!"
Exit Sub
End If

Itt a kód szövegként:

Private Sub CommandButton1_Click()
Dim sor As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
sor = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(sor, 1).Value = Me.TextBox1.Value
ws.Cells(sor, 2).Value = Me.TextBox2.Value
ws.Cells(sor, 3).Value = Me.TextBox3.Value
MsgBox "Berögzítve", vbOKOnly
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox1.SetFocus
End Sub

Mi van még hátra? Hát annak meghatározása, hogy mi "triggerelje" a kis kódunkat, azaz hogy induljon el. Itt beállíthatunk eseményeket vagy billentyűkombinációt is akár, én most szimplán hozzárakom egy gombhoz a kód indulását. A Ribbonunk Developer füle alatt található Controls szekció Insert utasítása segítségével szúrjunk be egy gombot:

Design Mode-ban duplán kattintva a gombra átjutunk a Visual Basic Editorba, ahol a kattintás eseményéhez mindössze az általam Adatbevitelformként elnevezett UserForm megmutatására (Show utasítás) van szükség:

És ezzel kész is vagyunk, a gombra való kattintással feljön a UserForm, amelynek kitöltése után az adatok bekerülnek a megfelelő cellákba.

Ez most egy roppant módon lebutított kis példa volt, de remélhetőleg elég ötletet és támaszt adott egy-két Olvasónak, hogy komolyabb irányba induljon saját kis projektjével. Kritika, javaslat, ötlet, mint mindig, most is szívesen látott. 

Miután általában mindannyian megint újra gyűrjük a munkás hétköznapokat, ennek megfelelően itt a blogon ismét egy olvasói kérdés megválaszolásával folytatom a posztolást és a megszokott módon ezúttal is egy egyszerűnek tűnő Excel-kérdés kerül terítékre, amelynek megoldása történhet akár formulákkal is, de a probléma dimenziójából adódóan itt talán már célszerűbb VBA-kódhoz nyúlni.

A kérdés a következő: adott néhány cella, amelyben szóközzel (de akár bármi mással) elválasztva különböző szavak vannak - hogyan érjük el azt, hogy ezek a szavak külön cellákba, egymás alá kerüljenek szépen sorban?

A következő megoldás csak egy ötlet, szívesen olvasok más javaslatokat is, de a mostani posztban kezdjük azzal, hogy a Developer fül alatt lévő Visual Basic parancs segítségével (vagy a billentyűkombinációval) menjünk át a VBA-editorba, hiszen itt fogunk rövid kódunkon dolgozni.

Ebben a megközelítésben én most két részre bontottam a problémát, első lépésként az összes kijelölt cellát összefűzöm egyetlen cellába, majd ha ez megvan, akkor a kód második részével ezt a szóközök mentén feldarabolom és egymás alatti cellákba rendezem.

Két változó és egy konstans deklarálásával kezdem (ha a rendszeres olvasók jól figyelnek, észrevehetik, hogy egy ilyen típusú kódról már volt itt szó a múltban):

- lesz egy "concatenated" nevű változóm mint String, ide fűzöm össze a cellák tartalmát
- lesz egy konstans "dm" nevű érték, amely a szóközt fogja megtestesíteni, hiszen az összefűzött értékek között jó, ha van szóköz
- és végül lesz egy "splitter" névre hallgató Variant típusú változóm, ami akár lehetne más típus is, a Varianttal azt kívánom elérni, hogy nagyjából bármi bekerülhessen ide

Ezután a már megismert For Each..Next ciklussal folytatódik a kód, hiszen végig kell mennünk a kijelölt celláink mindegyikén, majd szépen be kell őket fűznünk a "concatenated" változóba.

Látható, hogy a legelső körben, a "concatenated" változó még üres, majd ehhez az üres értékhez adjuk hozzá az első cellánk értékét, plusz a dm konstansban deklarált szóközt és ugrunk a következő cellára, ahol a "concatenated" változóban már várakozó első cellánk értékéhez adjuk hozzá a következőt.

Ezután egy egyszerű With segítségével fogjuk a kijelölt tartományunkat, a benne szereplő összes cellát kitöröljük, majd a kijelölés első cellájába beletesszük a "concatenated" változó értékét.

A With utasítást ne felejtsük el lezárni egy End With paranccsal.

Ezután jön a kódunk második része, amelyben első lépésként feltöltjük a fentebb deklarált "splitter" változónkat az előbb megszületett első cellánkban szereplő érték feldarabolásával megalkotott tömbünkkel, ami nem másból fog állni, mint a szóközök mentén elválasztott szavainkból.

A kódból most már csak egyetlen sorunk van hátra, méghozzá az egész művelet kulcsa. Ehhez először meg kell ismernünk a Range.Resize tulajdonságot, amely nem tesz mást, mint a megadott cellatartományt méretezi át a mögötte zárójelben megadott sorszám és oszlopszám szerinti tartományba.

Ismernünk kell továbbá az UBound és az LBound funkciókat is, előbbi segítségével egy tömb legnagyobb elemét illetve elemeinek számát tudjuk meghatározni, utóbbi segítségével pedig egy tömb legkisebb elemét. Figyelni kell arra, hogy a sorszámozás tömbünkben mindig a nulláról indul, azaz az UBound mindig eggyel többet hoz ki, mint ahány elemünk van a tömbben, az LBound pedig mindig nullát ad alapesetben.

Ergó mit fogunk tenni? Az egyes számú cellánkat úgy méretezzük át, hogy annyi sorra dobáljuk szét, amennyi szóból a "splitter" változónk áll, de ezt még meg kell bolondítanunk egy

Application.Transpose(splitter)

utasítással, ami a szétdarabolt első cellánk értékeit sorokba rendezi, hasonlóan az Excelből elérhető Transpose típusú beillesztéshez.

És ha ezt lefuttatjuk kijelölt celláinkra, máris látjuk az eredményt:

Amennyire meg lehet ítélni az internet segítségével, ezen a vasárnapon az ország legnagyobb részén egészen szép időnk van és ebből adódóan odakint célszerű tölteni legalább néhány órát - konkluzióként pedig ma a blogon mindössze egy egyszerű kis feladvánnyal foglalkozom, amely nem is biztos, hogy a mindennapokban előfordul, de azért fejtörőnek tökéletes.

A kérdés tényleg egyszerű: van három cellánk (vagy bármennyi), hogyan tudjuk eldönteni róluk, hogy ugyanazokat az értékeket (szöveget, számot vagy bármit) tartalmazzák-e?

Értelemszerűen ha tudjuk, hogy csak számokról lehet szó, akkor használhatjuk az IF és az AND függvényeket is, ez nem lehet kérdés. De mit tegyünk szövegek esetén?

Az biztos, hogy első körben a COUNTIF függvény juthat eszünkbe, hiszen ez az, ami az első paramétereként megadott tartományban megszámolja, hogy hány olyan érték van, amelyik megfelel a második paramétereként megadott feltételnek.

Tehát mostani példánk esetén a

=COUNTIF(E3:E5,E3)

megmondaná, hogy hány darab olyan mező van a tartományban, amely az E3-ban szereplő értéket tartalmazza.

De ezzel most hova jutottunk, volt ennek értelme? Hát igazából már tudjuk, hogy hány olyan cellánk van a tartományban, amely az első értékkel megegyezik, majd ezt már csak az egész tartomány sorszámával kell összehasonlítanunk és meg is kapjuk az IGAZ/HAMIS válaszunkat.

Hiszen tudjuk, ha szimplán beírjuk egy cellába, hogy =A1=A2, akkor vagy TRUE vagy FALSE értéket kapunk, attól függően, hogy egyeznek-e egymással vagy sem. Ezt fogjuk most is itt felhasználni, amikor beírjuk, hogy

=COUNTIF(E3:E5,E3)=COUNTA(E3:E5)

Használhatjuk a COUNTA parancsot is, amely megadja, hogy hány nem üres cella van a paramétereként megadott tartományban, de használhatjuk akár a ROWS parancsot is, amely egy tartományunk sorainak számát adja meg.

Aztán már csak annyit kell tennünk, hogy bevetünk egy IF parancsot, hogy szimpla TRUE és FALSE helyett érthető szövegünk legyen a cellában és kész is vagyunk:

Ez azonban csak egyetlen egy megoldási út, fejtörőről lévén szó, szívesen látom mások ötleteit is, akár arra a verzióra is, hogy hogyan mondjuk meg azt, hogy legalább két azonos érték van-e a tartományunkban.

A Slicer (Szeletelő) egy olyan lehetőség Excelben, amelyet vagy valaki ismer már és akkor igen jó barátságot kötött vele, vagy valaki nem ismer, de akkor mindenképpen hatékony megismernie, hiszen olyan szűrési lehetőségeket kapunk kézhez vele a táblázatkezelő újabb verzióiban, amelyeket vétek lenne elszalasztani.

A következőben egy remélhetőleg többször visszatérő posztsorozat első állomásaként mutatom be nagyon egyszerűen ezt a kis alkalmazást, rávilágítva egy-két olyan opcióra is, amellyel dashboardjainkba, elemzéseinkbe is jobban be tudjuk építeni.

Szóval nézzük csak a következő táblázatot, amely városonként bevétel információkat tartalmaz:

Készítsünk ebből egy Pivot-táblát a Ribbonunk Insert füle alatti Tables szekcióban található PivotTable funkció segítségével:

Meg is vagyunk, ez talán az egyik leggyakrabban ismételt mozdulatsor Excelben napjainkban (persze nem ilyen táblaméreteknél főként):

A példa egyszerűségétől eltekintve jöjjön a Slicer bevetése, amit szintén az Insert fül alatt, a Filters szekcióban találunk:Ez konkrétan olyasmi, mint egy szűrő, tehát meghatározhatjuk, hogy milyen feltétel(ek) mentén akarjuk adatunkat rendezni, jelen esetben mi most válasszuk ki a várost - és már látjuk is a szépen rendezett kis szeletelőnket, amely értelemszerűen jóval több oszlop és jóval több adat esetén nyújt igazi segítséget:

Ha ez így nekünk nem elég, akkor a Slicerre kattintva megjelenő Slicer Tools Options Ribbon-fül alatt színezgethetjük, formázgathatjuk, módosíthatjuk:Ezen fül alatt találjuk a Buttons szekciót, amelyben méretezhetjük a Slicer gombjait/oszlopait, tehát ha például nem egymás alatt, hanem egymás mellett akarjuk megjeleníteni őket, akkor itt kell garázdálkodnunk, illetve akkor is, ha mondjuk nem ilyen formában, hanem mondjuk gombsorként akarjuk megjeleníteni. Erre mostani példánknál nincs nagy szükség, de ha mondjuk értékelésekre, jegyekre akarunk szűrni, akkor már látható, hogy lehet annak is értelme.

Ha jobb gombbal kattintunk a kis Slicer eszközünkön, akkor a Slicer settings alatt még további beállítási lehetőségeink is vannak:El tudjuk távolítani a megnevezést, változtathatunk a sorba rendezésen és az adatot nem tartalmazó elemeinket akár ki is dobálhatjuk:

Lehetőségeink szinte korlátlanok, de erre még majd visszatérünk későbbi posztokban.

Általában ha relációs adatbázisokról, táblákról beszélünk vagy ilyen jellegű feladattal állunk szemben, akkor az Office Access modulja jut többnyire eszünkbe kapásból, pedig a maga módján Excelből is elérhetünk ilyen opciót, ha közvetlenül már nem is az Excel segítségével. Ismét egy olvasói kérdést fogunk körbejárni, amelynek célja egyébként roppant érthető és a megoldási javaslatok között az, amiről most írni fogok, nem biztos, hogy előkerül, de vonatkoztassunk majd el példám egyszerűségétől és lássuk meg benne azt a lehetőséget, amelyet sok ezer adatot sok-sok sheeten tartalmazó táblázatunk kezelésére nyújt.

A kérdés roppant egyszerű: adott a lenti képen látható két sheet, mindkettőn különböző adatok, amelyeket egyetlen elsődleges kulcs, az Azonosító köt össze. Hogyan érjük el azt, hogy egy harmadik sheeten az azonosító alapján összekötve az általunk kiválasztott oszlopok adatai az általunk meghatározott sorrendben jelenjenek meg? Példám egyszerűségétől ismételten csak tekintsünk el.

Az én megoldási javaslatom első lépéseként a Ribbonunk Data füle alatt található Get External Data szekcióból kattinsunk a From Other Sources opcióra, azon belül pedig válasszuk ki a From Microsoft Query opciót:

Itt válasszuk az Excel fájl opciót, hiszen Excelben található mindkettő sheetünk (vagy bármennyi sheetünk):

A felugró kis Browse ablakban keressük be azt a fájlt, amelyben az egyesíteni kívánt sheetjeink vannak, majd kattintsunk az OK gombra. A következő ablakhoz jutunk:

A bal oldalt látható Available tables and columns struktúrából szépen pakoljuk át azokat az oszlopokat a Columns in your query nevet viselő jobb oldali mezőbe, amelyeket szeretnénk egy sheeten látni együtt:

Ha megvagyunk, Nextre kattintva egy felugró üzenettel szembesülünk, amely félkövér betűtípusával sokkol minket és figyelmeztet, hogy egy azonosítóval össze kell majd kapcsolnunk tábláinkat (sheetjeinket) - ezt simán üssük el egy okéval és már egy szuperül kezelhető kis adatbázis-struktúrába jutunk:

Az egyik legfontosabb lépés jön most, össze kell kapcsolnunk tábláinkat (pontosan mintha Accessben ügyködnénk), egyszerűen fogjuk meg az egyik tábla Azonosító mezőjét és húzzuk rá a másik tábla Azonosító mezőjére:

Már majdnem kész is vagyunk, hiszen az előnézetben látjuk, hogy mi lesz az eredmény és itt még értelemszerűen módosíthatunk is, de ha egyetértünk azzal, amit látunk, akkor a File menü Return Data to Microsoft Excel utasításával visszavihetjük az egészet Excelbe:

Tegyük ezt aztán új sheetre és már láthatjuk is az eredményt, amit szerettünk volna elérni:

A poszt végén csak ismételten hangsúlyozni szeretném példám egyszerűségét, egy ilyen jellegű megoldásnak komplexebb fájlok, adatstruktúrák esetén van értelme, hiszen azért a VLOOKUP/INDEX formulák hasznosságát sem szabad elfelejteni.

Folytatva az elmúlt időszakban eléggé felgyülemlett olvasói és egyéb kérdések megválaszolását (és itt meg is nyugtatnék mindenkit, hogy előbb-utóbb az összes kérdés megválaszolásra kerül), ma ismét elszakadunk az Office-berkeken belül legtöbb témát szolgáltató Exceltől és egy egyszerűnek tűnő Outlook kérdésre fogjuk megkapni a választ a mai posztból.

A nevezett kérdés nem más, mint hogy például az Outlook 2007-ben (vagy akár példaként még korábbi verziót is felhozhatnék) már egészen megszokott lehetőség, hogy a levélben közvetlenül tudjuk módosítani a csatolmányunkat, létezik-e még az Outlook későbbi verzióiban, nevezetesen a 2013-ban? Átfordítva, ha van egy levelünk, amit kaptunk valakitől egy csatolmánnyal együtt, de a csatolmányt később új információk vagy adatok ismeretében módosítani szeretnénk az eredeti levélben, akkor azt milyen úton érhetjük el az Outlook 2013-as verziójában?

Alapesetben ha megpróbáljuk ezt a módosítást megtenni, kapunk egy szép hibaüzenetet, miszerint fájlunk Read-Only és így nem is tudunk módosítást eszközölni rajta:

Szerencsére a fejlesztők nem vették el tőlünk a lehetőséget, hanem biztonsági okokból ezt nem tették automatikussá, azaz ha szeretnénk módosítani a csatolmányon, akkor nem kell mást tennünk, mint a megnyitott levelünkön belül a Ribbonunkban az Actions gombocskára kattintva be kell kapcsolnunk a szerkesztési lehetőséget az Edit Message parancs segítségével:

Ha ezt megtettük, akkor innentől kezdve már szabad a gazda és úgy tudjuk módosítani a csatolmányunkat, ahogy szeretnénk, csak értelemszerűen ne felejtsünk el rámenteni a csatolmányra, majd utána a levélre is: