Előfordulhat, hogy egy makró projekt kapcsán szembejön az a probléma, hogy nem tudjuk előre azt, hogy hol is kezdődik a feldolgozandó tartomány, vagy az éppen mindig változó. Emiatt jól jöhet ez az egy soros snippet a probléma megoldására.
[vb]
Range("A1").Select
Cells.Find(What:="*").Activate
[/vb]
Az első sor az aktuális munkalap első celláját aktiválja, hogy onnan kezdődjön a keresés. A második pedig megkeresi az első kitöltött cellát, amiben bármilyen érték lehet, szám, vagy szöveg, akár képlet.
A fenti kód csak akkor működik, ha az aktuális munkalap nem teljesen üres. Ellenkező esetben hibát fog jelezni.
Ezért csak akkor használjuk így, ha biztosan tudjuk, hogy van kitöltött cella a vizsgált munkalapon.
Munkalap ürességének ellenőrzése
Készítünk egy függvényt, ami paraméterként a vizsgálandó munkalapot kapja meg, vissza pedig egy logikai értéket ad.
Ha egy munkalap üres, akkor a használt tartomány címe az ‘A1’ lesz. És ha az üres, akkor a munkalapot üresnek tekinthetjük.
[vb]
Function is_empty_sheet(sname As Worksheet) As Boolean
is_empty_sheet = sname.UsedRange.Address = "$A$1" And IsEmpty(sname.Range("A1"))
End Function
[/vb]
A javított kód
[vb]
Function is_empty_sheet(sname As Worksheet) As Boolean
is_empty_sheet = sname.UsedRange.Address = "$A$1" And IsEmpty(sname.Range("A1"))
End Function
Sub find_first_filled()
If Not is_empty_sheet(ActiveSheet) Then
Range("A1").Select
Cells.Find(What:="*").Activate
End If
End Sub
[/vb]
Így az első kitöltött cella keresése csak akkor fut le, ha az aktuális munkalap nem üres.
A munkalap nem csak cellákat, hanem egyéb elemet is tartalmazhat, pl.: Grafikonok, rajz elemek, képek, stb..
Az egyéni makró munkafüzet (Personal Macro Workbook) egy speciális munkafüzet, amit leginkább arra használhatunk, hogy kisebb, speciális vagy általános segéd makrókat tárolunk benne, és a napi munkánk során ezeket a makrókat használva felgyorsíthatjuk a munkánkat. Néhány példa:
A mai dátum beillesztése a kijelölt cellákba
Az aktív munkafüzet útvonalának kiíratása (honnan nyitottuk meg)
Aktuális kijelölés képleteinek értékké alakítása
A kijelölés közepére igazítás
Aktuális táblázat formázása adott sablon szerint
stb…
Hol van a helye? – XLSTART
A nevében is benne van, hogy személyes, azaz ha egy gépet többen használunk, akkor mindenkinek lehet saját ilyen makró füzete. Emiatt a személyes felhasználói profilunkban van a helye.
A saját gépünkön aktuális helyet meg is kérdezhetjük az Exceltől:
ALT-F11-el lépjünk át a Visual Basic szerkesztőbe
Ctrl-G kombinációval hívjuk elő az Excel parancssorát (Immediate Window)
Írjuk be a következőt: ?Application.StartupPath
Válaszként megkapjuk a helyes útvonalat
Az AppData mappa alapból rejtett, ezért ha meg is szeretnénk nézni ennek a mappának a tartalmát, akkor be kell kapcsolni a rejtett fájlok és mappák megjelenítését.
Mi ez a mappa?
Ez a mappa arra való, hogy az ide helyezett munkafüzetek, vagy azokra mutató parancsikonokhoz tartozó munkafüzetek az Excel indításakor automatikusan megnyílnak.
Ha ezen a helyen létrehozunk egy PERSONAL.XLSB fájlt ( bináris munkafüzet), akkor az Excel ezt felismeri, és tud bele rögzíteni makrókat, illetve tudunk belőle futtatni makrólat attól függetlenül, hogy különben éppen melyik munkafüzettel dolgozunk.
Az XLSTART mappa az Exel telepítésekor legtöbbször automatikusan létrejön, és üres. Ritkábban, de előfordul, hogy az XLSTART mappa sem jön létre telepítéskor. A személyes makró munkafüzet tehát alapból nem létezik, azt nekünk kell létrehozni.
A PERSONAL.XLSB létrehozása
Erre három módszert is használhatunk
Egy üres munkafüzetet nyitunk, és elmentjük bináris fájlként, PERSONAL néven az előbb megadott helyre
Rögzítünk egy akármilyen makrót a személyes makró füzetbe. Ilyenkor az excel létrehozza a fájlt.
Egy másik felhasználó profiljából vagy akár egy másik gépről származó fájlt bemásolunk ide
A PERSONAL.XLSB rejtetten nyílik meg, azaz nem látszódik az ablaka, csak a Visual Basic felületen látható, hogy nyitva van. A rejtettségét mi tudjuk beállítani a Nézet (View) szalagon az elrejtés/felfedés (hide/unhide) parancsokkal.
Példa makró
Legyen itt egy példa makró, amit a videó alapján létrehozott PERSONAL.XLSB fájlba illesztve ki is tudunk próbálni. A makró egy ablakba kiírja az aktuális munkafüzet elérési útját.
Egy korábbi anyagban arról volt szó, hogy ha lehetséges, kerüljük a cella egyesítést (Merge Cells), és helyette inkább a kijelölés közepére igazítást (Center Across Selection) használjuk.
Mivel ennek a menüpontnak az elérése több kattintást igényel, így érdemes lehet a PERSONAL makrómunkafüzetbe tenni egy olyan makrót, ami elvégzi az igazítást.
Az egyéni makró munkafüzet (PERSONAL.XLS(B)
Az egyéni makró munkafüzetről egy külön oktatóanyagban van szó. Röviden, ez egy olyan speciális makrófájl, ami ha létezik, akkor az Excel indításakor automatikusan megnyílik, de rejtett ablakban. A benne lévő makrók pedig láthatóvá, használhatóvá válnak. Kisebb segéd makrókat szoktunk bele tenni, amik felgyorsítanak egyes műveleteket, és hagyományos módon csak körülményesen tudnánk megcsinálni. Ilyen például egy cella tartalmának a kijelölés közepére igazítása.
Lépjünk át a Visual Basic Editor felületére (ALT+F11)
Ha nem látszódik, Kapcsoljuk be a Project Explorer ablakot (Ctrl+R). Ha még nem létezik a PERSONAL makrófüzet, akkor létre kell hozni az említett tutorial alapján.
A PERSONAL makrófüzetbe illesszük be a következő kódot.
[vb]
Sub WrapAcross()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub
[/vb]
Használata
Egy cellába beírjuk a szöveget, majd kijelöljük azt a tartományt, amin belül középre szeretnénk igazítani.
Lefuttatjuk a makrót. Rendelhetünk hozzá billentyűkombinációt, vagy gyors elérési eszköztár gombot is . Nem egyesíti a cellákat, mégis középen látszik.
Az egész folyamatot megtekinthetjük a VIDEÓ anyagban
Gyakori feladat, hogy egy szűrt táblázatban az egyik oszlopban az adatokat teljesen vagy részben másikra szeretnénk cserélni. Ezt kézi szerkesztéssel nagyon egyszerű megtenni, de ha a feladatot makró kódon belül szeretnénk megoldani (mert pl. egy nagyobb feladat része), akkor már nem annyira egyértelmű a helyzet.
Volt már egy korábbi anyag arról, hogy ha szűrt tartományba szeretnénk a vágólapról beilleszteni adatokat, de most nem erről van szó, hanem egyszerű felülírásról.
Kézi módszer
Ez nagyon egyszerű, hiszen már jópár verzió óta az Excel kiválóan kezeli ezt a problémát.
Leszűrjük a táblát
A módosítandó oszlopban az első elemet átírjuk
Lehúzzuk az aljáig
Ha megszüntetjük a szűrést, akkor látni fogjuk, hogy csak az előzőleg szűrt adatok módosultak, a közbensők, amik a szűréskor rejtve voltak, nem változtak.
Makró – VBA módszer
Ha az előző műveletsort rögzítjük makróval, akkor a kódból nagyon gyorsan kiderül, hogy általánosan nem használható, tehát változó adattartalom esetén, ahol a szűrés más-más eredményt adna. Emiatt ezt a problémát kicsit komolyabban kell leprogramozni.
Elméleti háttér
Ha egy oszlop szűrt adatokat mutat, és abban módosítani szeretnénk, akkor előbb ki kell jelölni CSAK a látható cellákat.
Kijelöljük a teljes, szűrt táblázatot (Ctl-A)
Speciális kijelölés -> Csak a látható cellák
Így viszont az oszlopban lévő adatok szétválnak egymástól, és ha újra kinyitjuk, azaz megszüntetjük a szűrést, akkor több, nem összefüggő tartományt fogunk látni.
Tehát a makróval a kategória oszlopban lévő, látható tartományokat kell módosítani. Példánkban a „Beverages” szót, másik kategória névvel.
A Selection objektum
Ha több, nem összefüggő tartományt jelölünk ki, vagy hozunk létre a fenti módszerrel, attól az még a SELECTION objektummal lefedhető, és egy lépésben átírható a benne lévő összes cella értéke.
A Fejléc problémája
Mivel előre nem tudjuk, hogy milyen sorok fognak beleesni a szűrésbe, legegyszerűbb, ha a fejlécet is belevesszük a kijelölésbe. Viszont így annak az értéke is felülíródik. Ezt úgy oldjuk meg, hogy a felülírás előtt elmentjük a fejléc eredeti értékét, majd a végén visszaírjuk.
A VBA kód
A példában egy általánosabb megoldást adok, ahol a subrutin argumentumként kapja meg azt, hogy melyik oszlopban kell dolgoznia, és mire kell cserélni a szűrt elemeket.
A futás előtt feltételezi, hogy már a szűrés megtörtént, és az aktív cella a módosítandó oszlop fejlécén áll.
[vb]
Sub Replace_filtered_data(header_cell As Range, new_value As Variant)
Dim h As Variant
‘A fejléc tartalmának megjegyzése
h = header_cell.Value
‘A módosítandó oszlop kijelölése fejléccel együtt
Range(header_cell, header_cell.End(xlDown)).Select
Gyakran felmerül az igény, hogy egy munkalapon a képleteket tartalmazó oszlopokat értékké alakítsuk. Ennek több oka is lehet:
Nem szeretnénk, hogy lássák a képleteket
Az értékké alakítással gyorsítjuk a működést, mert az Excel-nek nem kell állandóan újraszámolni a képleteket
A probléma
A másolás / érték beillesztés műveletet mindenki ismeri. A baj az vele, hogy csak egy, összefüggő tartományban működik. Hiába jelölünk ki több tartományt pl. a Ctrl lenyomása mellett. A másolás is csak akkor működik, ha azonos a kijelölt területek sorainak a száma. De saját magára értékként nem lehet beilleszteni két egymással nem összefüggő területet.
A megoldás
A megoldás egy pár soros makró, ami kijelöli a képleteket tartalmazó cellákat (sorokat/oszlopokat), majd egy ciklussal végiglépked a kijelöléskor képződött területeken, és mindegyiket értékké alakítja.
[vb]
Sub keplet_helyett_ertek()
Range("a1").Select
‘Képleteket tartalmazó tartományok kijelölése
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
‘A keletkezett területek bejárása és képlet-érték csere
For Each akt_range In Selection.Areas
akt_range.Formula = akt_range.Value
Next
End Sub
[/vb]
A fenti makrót másoljuk egy makró-munkafüzet egyik moduljába, vagy a Personal (Egyéni) makró munkafüzet ,megfelelő moduljába, és máris kipróbálhatjuk.
A kapcsolódó videóban láthatjuk a folyamatot és a működést is. Ezen kívül mi van még a videóban?
Speciális kijelölés lehetőségei
Képleteket tartalmazó cellák kijelölése
Egyéni makró munkafüzet létrehozása (ha még nincs)
A makrófejlesztés egyik fontos része a „Bolond állóság” (FoolProof) biztosítása. Emiatt egy igényesebb VBA kód hibaellenőrzéseket is tartalmaz.
Az egyik ilyen eset lehet, hogy ellenőrizzük, az aktív cella egy korábban már táblázattá (Lista) alakított tartományban van-e?
Íme egy erre szolgáló függvény
[vb]
Function in_table() As String
Dim tname As String
tname = ""
On Error Resume Next
tname = ActiveCell.ListObject.Name
in_table = tname
End Function
[/vb]
Úgy működik, hogy ha az aktív cella benne áll egy táblázatban (listában), akkor létezik a .name tulajdonsága. Különben hibát ad, amit kezelünk.
A függvény a táblázat nevét, vagy üres stringet ad vissza.
Az előző részben azt mutattam meg, hogy hogyan lehet egy szűrt, azaz rejtett sorokat is tartalmazó tartományba adatokat beilleszteni makró használata nélkül.
Arra jutottunk, hogy ez csak akkor lehetséges, és úgy sem egyszerűen, ha ugyanazon szűrt táblázat egyik oszlopából szeretnénk másolni adatokat egy másik oszlopba. Mivel egy táblában vannak garantált, hogy a szűrés hatására mindkét oszlopban ugyanazok a sorok leszek rejtve.
A példában a szűrés után a C oszlop adatait másoljuk a B oszlopba.
De mi van akkor, ha a másolandó és a céltartomány nem ugyanabban a táblázatban van?
Esetleg másik lapon, ami akár nem is szűrt?
Ez esetben makrót kell használni. Szerencsétek van, mert elkészítettem, és itt közzéteszem a programot. Futtatáskor csak ki kell jelölni a forrás tartományt, majd a céltartomány első celláját, és a többit rábízni a makróra.
A makró használatát és működését Videóban is megmutatom.
A makró kódja
[vb]
‘Beillesztés szűrt tartományba, 1 oszlopos megoldás
‘A forrástartományt ki kell jelölni, a Céltartománynak csak a kezdőcelláját kell megadni
‘A céltartomány legyen/lehet szűrt oszlopban
‘Az átmásolás értékként történik
Sub Paste2VisRows2()
Dim rFrom As Range, rTo As Range
Dim i As Long, Ofset As Long
Set rFrom = Application.InputBox(Prompt:="Please select copy area", Title:="Area Selection", Type:=8)
Set rTo = Application.InputBox(Prompt:="Please select the first cell of paste area", Title:="Area Selection", Type:=8)
If rFrom.Columns.Count > 1 Or rTo.Columns.Count > 1 Then
MsgBox "Both source and destination ranges must be one column wide!"
Exit Sub
End If
If rTo.Cells.Count <> 1 Then
MsgBox "Select only one cell as the beginning of the paste area!"
Exit Sub
End If
Application.ScreenUpdating = False
Ofset = 0
For i = 1 To rFrom.Rows.Count
If Not rFrom.Rows(i).Hidden Then
Do Until Not rTo.Offset(Ofset).EntireRow.Hidden
Ofset = Ofset + 1
Loop
rFrom.Cells(i).Copy
rTo.Offset(Ofset).PasteSpecial xlPasteValues ‘Átmásolja az aktuális cellát a forráshelyről a célhelyre
Ofset = Ofset + 1
End If
Next i
Application.ScreenUpdating = True
End Sub
[/vb]
Ha egy tartományban képlettel számolunk, akkor nem tudunk visszaadni üres cellát. Leggyakrabban nullát, vagy üres stringet adunk vissza, amit az Excel másképp kezel bizonyos esetekben, mint az üres cellát.
A példában, ha az A1-ben nulla van, akkor üres stringet, különben 1-et adunk vissza:
=IF(A1=0,"",1)
Tegyük fel, hogy ez a képlet sok soron keresztül megy, és a végén grafikont kell belőle rajzolni. A problémát az okozza, hoyg az Excel grafikon motorja csak a valóban üres cellákat tekinti annak, és azokat nem rajzolja ki a diagramra, de az üres stringet nullának tekinti, és kirajzolja.
Egyik megoldás az lehet, hogy a módosítjuk a képletet, és az üres string helyett hibát adunk, amit szintén figyelmen kívül hagy a grafikon.
=IF(A1=0,NA(),1)
Az NA() függvény a #N/A eredményt adja. Magyar megfelelője a HIÁNYZIK() függvény.
Másik lehetőség, hogy kijelöljük az oszlopot, sé egy kis makróval töröljük az összes üres stringet tartalmazó cella tartalmát.
Üres stringeket tartalmazó cellák kiürítése VBA kóddal
[vb]
‘A kijeloles cellái közül törli a tartalmat, ha üres stringet talál benne
Sub delete_empty_strings()
Dim u As Range
Dim cur As Range
For Each cur In Selection.Cells
If cur.Value = "" Then cur.ClearContents
Next
Ha Excelben egy számtartomány minden értékét szeretnénk szorozni egy adott konstans értékkel, akkor a következő lehetőségeink vannak:
Egy segédoszlopban mellette elvégezzük a számítást, majd értékként visszatesszük az eredményt az eredetire
Speciális vágólap beillesztéssel, szorzás műveletet választva
Alkalmazzuk a következő makrót, ami a bekért számmal szorozza az előzőleg kijelölt tartomány számait.
[vb]
Sub multiply_range()
‘A kijelölt tartomány számait szorozza az Inputbox-ban megadott számmal
a = InputBox("value to multiply with:")
Selection.Value = Evaluate(Selection.Address & "*" & a)
End Sub
A nagy méretű, sok képletet tartalmazó munkafüzetekkel bizony meggyűlhet a bajunk. A képletek állandó újraszámolása sok időbe telhet, és ez gátolhatja a munkavégzést.
Ilyenkor ki szoktuk kapcsolni az automatikus újraszámolást, és áttérünk manuális újraszámolásra, amihez a következő billentyűkombinációkat használhatjuk:
F9 – minden nyitvalévő munkafüzetben újraszámolja azokat a képleteket, amelyeknek az elődcellái változtak , tehát csak az általa szükségesnek vélt képleteket számolja újra.
(VBA: Application.Calculate)
Shift+F9 – Az előzőt teszi, de csak az aktuális munkalappal.
(VBA: ActiveSheet.Calculate)
Ctrl+Alt+F9 – Minden nyitott munkafüzetben minden képletet újraszámol.
(VBA: Application.CalculateFull)
Ctrl+Shift+Alt+F9 – Minden nyitott munkafüzetben minden képletet újraszámol, és ellenőrzi, illetve újraépíti a függőségeket.
(VBA: Application.CalculateFullRebuild)
Ha számít a sebesség, és egy problémára többféle megoldási ötletünk is van, akkor érdemes lehet a gyorsabbat választani. A sebesség vagy egyértelműen látszik, vagy mérni kell. Például kevesebb mennyiségű teszt adaton végzett mérés rámutathat a majdani sok adattal történő viselkedésre.
Megadnak ott egy VBA kódot, amivel a fenti újrakalkulációs időket lehet számolni. Elég, ha a kódot tartalmazó munkafüzetet megnyitjuk, és máris használhatjuk a mérést. A kódokat tartalmazó munkafüzet letölthető az excel-bazis.hu-ról is.
A MICROTIMER() függvény Windows API hívásokkal éri el az operációs rendszer óráját, így akár mikroszekundumokat is képes mérni. A mérés úgy működik, hogy kikapcsolja az automatikus újrakalkulációt, majd a választott kalkulációs móddal újraszámol, közben pedig megméri az eltelt időt. A mérés végén az eredményt másodpercben egy üzenetben megjeleníti, majd beírja azt az aktuális cellába.
Fontos: Mivel a Windows nem Real Time rendszer, a mérések pontosságának fokozása érdekében érdemes 3-4 alkalommal megismételni, és a kapott eredményeket átlagolni.
