tartalom
Még ha nem is ismeri a programozást, sok helyen (könyvekben, webhelyeken, fórumokon) talál kész VBA makrókódot rengeteg tipikus Excel feladathoz. Tapasztalataim szerint a legtöbb felhasználó előbb-utóbb összegyűjti személyes makrógyűjteményét, hogy automatizálja a rutinfolyamatokat, legyen szó képletek értékké fordításáról, összegek szavakban történő megjelenítéséről vagy a cellák szín szerinti összegzéséről. És itt felmerül a probléma – a Visual Basic makrókódját valahol el kell tárolni, hogy később a munkában felhasználhassuk.
A legegyszerűbb lehetőség, ha a makrókódot közvetlenül a munkafájlba menti a Visual Basic szerkesztőbe a billentyűparancs segítségével. más+F11 és új üres modul hozzáadása a menün keresztül Beszúrás – Modul:
Ennek a módszernek azonban számos hátránya van:
- Ha sok a működő fájl, és mindenhol szükség van egy makróra, például egy makróra a képletek értékké alakításához, akkor át kell másolnia a kódot minden könyvben.
- Nem szabad elfelejteni mentse a fájlt makró-kompatibilis formátumban (xlsm) vagy bináris könyvformátumban (xlsb).
- Egy ilyen fájl megnyitásakor makró védelem minden alkalommal figyelmeztetést ad ki, amelyet nyugtázni kell (jó, vagy teljesen letiltja a védelmet, ami nem mindig kívánatos).
Elegánsabb megoldás lenne az alkotás saját bővítmény (Excel bővítmény) – egy speciális formátumú (xlam) külön fájl, amely tartalmazza az összes „kedvenc” makrót. Ennek a megközelítésnek az előnyei:
- Elég lesz egyszer csatlakoztassa a kiegészítőt Excelben – és annak VBA-eljárásait és funkcióit bármely fájlban használhatja ezen a számítógépen. A munkafájlok újramentése xlsm- és xlsb-formátumban tehát nem szükséges, mert. a forráskód nem bennük lesz tárolva, hanem a bővítményfájlban.
- Védelem a makrók sem fognak zavarni. a kiegészítők definíció szerint megbízható források.
- Meg tud tenni külön fül az Excel szalagon szép gombokkal a bővítménymakrók futtatásához.
- A bővítmény egy külön fájl. Övé könnyen hordozható számítógépről számítógépre, oszd meg kollégáiddal vagy akár add el 😉
Nézzük meg lépésről lépésre a saját Microsoft Excel-bővítmény létrehozásának teljes folyamatát.
1. lépés: Hozzon létre egy bővítményfájlt
Nyissa meg a Microsoft Excelt egy üres munkafüzettel, és mentse el bármilyen megfelelő néven (pl MyExcelAddin) bővítmény formátumban a paranccsal Fájl – Mentés másként vagy kulcsokat F12, megadva a fájl típusát Excel bővítmény:
Kérjük, vegye figyelembe, hogy az Excel alapértelmezés szerint a C:UsersYour_nameAppDataRoamingMicrosoftAddIns mappában tárolja a bővítményeket, de elvileg bármilyen más mappát megadhat, amely kényelmes az Ön számára.
2. lépés Csatlakoztatjuk a létrehozott bővítményt
Most az utolsó lépésben létrehozott bővítmény MyExcelAddin kapcsolódnia kell az Excelhez. Ehhez lépjen a menübe Fájl – Beállítások – Bővítmények (Fájl — Beállítások — Bővítmények), kattintson a gombra Rólunk (Megy) az ablak alján. A megnyíló ablakban kattintson a gombra Felülvizsgálat (Tallózás) és adja meg a bővítményfájlunk helyét.
Ha mindent jól csináltál, akkor a miénk MyExcelAddin meg kell jelennie az elérhető kiegészítők listájában:
3. lépés: Adjon hozzá makrókat a bővítményhez
Bővítményünk az Excelhez kapcsolódik és sikeresen működik, de még nincs benne egyetlen makró sem. Töltsük fel. Ehhez nyissa meg a Visual Basic szerkesztőt a billentyűparancs segítségével más+F11 vagy gombbal Visual Basic lap fejlesztő (Fejlesztő). Ha lapok fejlesztő nem látható, át lehet jeleníteni Fájl – Beállítások – Szalagbeállítás (Fájl — Beállítások — Szalag testreszabása).
A szerkesztő bal felső sarkában kell lennie egy ablaknak NetPoulSafe projekt (ha nem látható, akkor kapcsolja be a menüből Nézet — Project Explorer):
Ez az ablak megjeleníti az összes megnyitott munkafüzetet és a futó Microsoft Excel-bővítményeket, beleértve a miénket is. VBAProject (MyExcelAddin.xlam) Válassza ki az egérrel, és adjon hozzá egy új modult a menün keresztül Beszúrás – Modul. Ebben a modulban a bővítménymakrók VBA kódját tároljuk.
Beírhatod a kódot a semmiből (ha tudod, hogyan kell programozni), vagy másolhatod valahonnan készen (ami sokkal egyszerűbb). A teszteléshez írjuk be egy egyszerű, de hasznos makró kódját a hozzáadott üres modulba:
A kód beírása után ne felejtsen el kattintani a bal felső sarokban található mentés gombra (lemezre).
A mi makrónk FormulasToValues, amint azt könnyen elképzelheti, a képleteket egy előre kiválasztott tartományban lévő értékekké alakítja. Néha ezeket a makrókat hívják eljárások. A futtatásához ki kell jelölnie a cellákat képletekkel, és meg kell nyitnia egy speciális párbeszédpanelt makrók a lapról fejlesztő (Fejlesztő – Makrók) vagy billentyűparancsot más+F8. Általában ez az ablak az összes megnyitott munkafüzetből elérhető makrókat jeleníti meg, de a bővítménymakrók itt nem láthatók. Ennek ellenére a mezőbe beírhatjuk eljárásunk nevét makró neve (Makró neve)majd kattintson a gombra futás (fuss) – és a makrónk működni fog:
| |
Itt is hozzárendelhet egy billentyűparancsot a makró gyors elindításához – a gomb felelős ezért paraméterek (Lehetőségek) az előző ablakban Macro:
A billentyűk hozzárendelésekor ne feledje, hogy azok megkülönböztetik a kis- és nagybetűket és a billentyűzetkiosztást. Tehát ha olyan kombinációt rendel hozzá, mint a Ctrl+Й, akkor a jövőben meg kell győződnie arról, hogy be van kapcsolva az elrendezés, és meg kell nyomnia a műszakhogy megkapja a nagybetűt.
A kényelem kedvéért az ablak bal felső sarkában található gyorselérési eszköztárhoz is hozzáadhatunk egy gombot a makrónkhoz. Ehhez válassza a lehetőséget Fájl – Beállítások – Gyorselérési eszköztár (Fájl — Beállítások — Gyorselérési eszköztár testreszabása), majd az ablak tetején található legördülő listában a lehetőséget makrók. Ezt követően a makrónk FormulasToValues gombbal a panelre helyezhető hozzáad (Add) és a gombbal válasszon ki egy ikont hozzá Változtat (Edit):
4. lépés: Adjon hozzá funkciókat a bővítményhez
De makroeljárások, vannak még függvénymakrók vagy ahogy hívják UDF (Felhasználó által meghatározott függvény = felhasználó által definiált függvény). Hozzunk létre egy külön modult a bővítményünkben (menü parancs Beszúrás – Modul), és illessze be oda a következő függvény kódját:
Könnyen belátható, hogy ez a funkció szükséges ahhoz, hogy az ÁFA-t tartalmazó összegből áfát vonjunk ki. Természetesen nem a Newton-féle binomiális, de példának mutatjuk be az alapelveket.
Vegye figyelembe, hogy egy függvény szintaxisa eltér az eljárástól:
- konstrukciót használnak Funkció…. Funkció befejezése helyette Sub … End Sub
- a függvény neve után zárójelben vannak feltüntetve argumentumai
- a függvény törzsében elvégzik a szükséges számításokat, majd az eredményt a függvény nevével rendelkező változóhoz rendelik
Vegye figyelembe azt is, hogy erre a funkcióra nincs szükség, és lehetetlen az előző makró eljáráshoz hasonlóan futtatni a párbeszédablakon keresztül makrók és gomb futás. Az ilyen makrófüggvényt szabványos munkalapfüggvényként kell használni (SUM, IF, VLOOKUP…), azaz csak írja be bármelyik cellába, argumentumként megadva az összeg értékét ÁFA-val:
… vagy lépjen be a szabványos párbeszédpanelen egy függvény beszúrásához (gomb fx a képletsorban), kategóriát választva Felhasználó által meghatározott (Felhasználó által meghatározott):
Az egyetlen kellemetlen pillanat itt a funkció szokásos leírásának hiánya az ablak alján. A hozzáadáshoz a következőket kell tennie:
- Nyissa meg a Visual Basic Editort egy billentyűparancs segítségével más+F11
- Válassza ki a bővítményt a Projekt panelen, és nyomja meg a gombot F2az Objektumböngésző ablak megnyitásához
- Válassza ki a bővítményprojektjét az ablak tetején található legördülő listából
- Kattintson a jobb gombbal a megjelenő függvényre, és válassza a parancsot Ingatlanok.
- Írja be a funkció leírását az ablakban Leírás
- Mentse el a bővítményfájlt és indítsa újra az excelt.
Újraindítás után a függvénynek az általunk megadott leírást kell megjelenítenie:
5. lépés: Hozzon létre egy kiegészítő fület a felületen
Az utolsó, bár nem kötelező, de kellemes érintés egy külön fül létrehozása lesz egy gombbal a makrónk futtatásához, amely a bővítményünk csatlakoztatása után megjelenik az Excel felületén.
Az alapértelmezés szerint megjelenített lapokra vonatkozó információkat a könyv tartalmazza, és azokat speciális XML-kódban kell formázni. Az ilyen kód írásának és szerkesztésének legegyszerűbb módja speciális programok - XML-szerkesztők - segítségével. Az egyik legkényelmesebb (és ingyenes) Maxim Novikov programja Szalag XML szerkesztő.
A vele való munkavégzés algoritmusa a következő:
- Zárja be az összes Excel ablakot, hogy ne legyen fájlütközés a bővítmény XML-kódjának szerkesztésekor.
- Indítsa el a Ribbon XML Editor programot, és nyissa meg benne a MyExcelAddin.xlam fájlt
- Gombbal lapok a bal felső sarokban adja hozzá az új lap kódrészletét:
- Üres idézőjeleket kell beírni id lapunkra és csoportunkra (bármilyen egyedi azonosítóra), és in címke – a lapunk és a rajta lévő gombok nevei:
- Gombbal gomb a bal oldali panelen adjon hozzá egy üres kódot a gombhoz, és adjon hozzá címkéket:
— címke a szöveg a gombon
— imageMso — ez a gombon lévő kép feltételes neve. Az AnimationCustomAddExitDialog nevű piros gomb ikont használtam. Az összes elérhető gomb neve (és több száz van belőlük!) az interneten számos webhelyen megtalálható, ha az „imageMso” kulcsszavakra keres. Kezdetnek ide lehet menni.
- onAction – ez a visszahívási eljárás neve – egy speciális rövid makró, amely a fő makrónkat fogja futtatni FormulasToValues. Ezt az eljárást bárminek nevezheti. Kicsit később adjuk hozzá.
- Minden elvégzett művelet helyességét az eszköztár tetején található zöld pipa gombbal ellenőrizheti. Ugyanitt kattintson a hajlékonylemezes gombra az összes változtatás mentéséhez.
- Zárja be a Szalag XML-szerkesztőt
- Nyissa meg az Excelt, lépjen a Visual Basic szerkesztőbe, és adjon hozzá egy visszahívási eljárást a makrónkhoz KillFormulasígy futtatja a fő makrónkat a képletek értékekkel való helyettesítésére.
- Mentjük a változtatásokat, és az Excelbe visszatérve ellenőrizzük az eredményt:
Ez minden – a bővítmény használatra kész. Töltse fel saját eljárásaival és funkcióival, adjon hozzá gyönyörű gombokat – és sokkal könnyebbé válik a makrók használata a munkájában.
- Mik azok a makrók, hogyan használd őket a munkádban, hol szerezhetsz be makrókódot a Visual Basicben.
- Hogyan készítsünk indítóképernyőt munkafüzet megnyitásakor Excelben
- Mi az a személyes makrókönyv és hogyan kell használni