Ezek a bemutató megjegyzéseim a TechTV Canada 33-as epizódjában való megjelenésemből.
VBA makró felvevő
Az Excel 1995 óta eladott minden példánya tartalmazza a cellák mögé bújó, hihetetlenül erős Visual Basic for Applications (VBA) alkalmazást. Ha van Excel, akkor VBA van.
A VBA lehetővé teszi, hogy automatizáljon bármit, amit az Excel programban megtehet, valamint további dolgokat is végezhet, amelyek általában nem lehetségesek az Excel programban.
Szerencsénkre a Microsoft makrórögzítőt tartalmazott az Excel programmal. Ez lehetővé teszi VBA kód írását anélkül, hogy programozó lenne. Sajnos a Macro Recorder hibás. Nem fog létrehozni minden alkalommal működő kódot. A makrofelvevő közelebb visz, de sok esetben meg kell javítania egy kicsit a kódot, hogy megbízhatóan működjön. Más esetekben tudnia kell, hogy mikor használja a Relatív felvételt a makró működéséhez.
Tracy Syrstad és én VBA-t és makrókat írtunk a Microsoft Excel 2016-ból annak érdekében, hogy segítsünk megérteni a rögzített kód korlátait, és megtanítsuk, hogyan kell rögzíteni a rögzített kódot olyanná, ami mindig működik.
A műsorban bemutattam egy makró rögzítésének folyamatát, amely tökéletesen fog működni, ha tudja, hogyan kell használni a Relatív hivatkozás gombot. Valaki adott nekem egy Excel munkalapot, több száz névvel és címmel. Levelezési címkéket akartak. Ha valaha is használta a Microsoft Word levélösszevonási funkcióját, akkor tudja, hogy a munkalap új oszlopában minden mezőre szüksége van. Ehelyett ebben a munkalapon az adatok az A oszlopba kerültek.
Az egyetlen címke javításának folyamata elég unalmas.
- Kezdje a cellamutatóval az A oszlop nevén.
- A lefelé mutató nyíl lenyomásával lépjen a címre
- Ctrl + x a vágáshoz
- Fel nyíl, jobb nyíl a név melletti B oszlopra lépéshez
- Ctrl + v a beillesztéshez
- Lefelé mutató nyíl kétszer, a bal nyíl egyszer a városba lépéshez
- Ctrl + x a vágáshoz
- Kétszer felfelé nyíl, háromszor jobbra nyíl
- Ctrl + v a beillesztéshez
- A balra mutató nyíl kétszer, a lefelé mutató nyíl egyszer az üres sorra lépéshez.
- Tartsa lenyomva a shift billentyűt, miközben kétszer lenyomja a lefelé mutató nyilat
- Alt + edr az üres sorok törléséhez
- Felfelé és lefelé mutató nyíl a név ismételt kiválasztásához.
Itt található az alábbi lépéseket bemutató animáció:
Az Excel beállítása a makrók engedélyezéséhez
Bár az Excel minden példánya VBA-t tartalmaz, alapértelmezés szerint a Microsoft kikapcsolja a makrók futtatásának lehetőségét. A makrók futtatásához egyszeri beállítást kell végrehajtania. Nyissa meg az Excel programot. A menüből válassza az Eszközök> Makró> Biztonság lehetőséget. Ha a makróbiztonsági szint jelenleg magasra van állítva, akkor állítsa közepesre.
Felkészülés a makró rögzítésére
Gondoljon a feladat eléréséhez szükséges lépésekre. Győződjön meg arról, hogy a név cellamutatójával kezdi, és a következő néven fejezi be. Ez lehetővé teszi a makró sokszoros futtatását. Ha készen áll a lépésekre, kapcsolja be a makrórögzítőt. A menüből válassza az Eszközök> Makró> Új makró rögzítése lehetőséget.
Ha sok napig fogja futtatni ezt a makrót, akkor adjon hasznos nevet a makrónak. Ha egyszeri makró egy egyszeri feladat automatizálása, akkor a Makró név Macro1 néven hagyása valószínűleg rendben van. A fontos mező itt a gyorsbillentyű mező. Ha egyszeri használatra készít makrót, rendelje hozzá a makrót egy gyorsbillentyűhöz, például a Ctrl + a - elég könnyű többször lenyomni a Ctrl + a billentyűt, mivel a billentyűk közel vannak egymáshoz. Ha olyan makrót fog létrehozni, amelyet minden nap használni fog, akkor hozzá kell rendelnie a makrót egy olyan kulcshoz, amely még nem fontos gyorsbillentyűkombináció. A Ctrl + j vagy a Ctrl + k jó választás.
Az egyszer használatos makrókat a ThisWorkbook-ban kell tárolni. Ha a makrót többféle munkafüzeten kell ismételten használni, akkor a makrót a Személyes makró munkafüzetben tárolhatja.
Most az egész Excel legapróbb eszköztárát mutatja be; a Felvétel leállítása eszköztár. Csak két ikonja van, és így néz ki:
Ha ez az eszköztár az Ön útjába kerül, ne nyomja meg az X gombot a bezárásához. Ehelyett megragadja a kék sávot, és húzza az eszköztárat egy új helyre. Az eszköztár mozgatását a makró nem rögzíti. Ha véletlenül bezárja az eszköztárat, szerezze vissza a Nézet> Eszköztárak> Felvétel leállítása menüpontból. Ezt a műveletet azonban rögzítik.
Az eszköztár első gombja a "Felvétel leállítása" gomb. Ez magától értetődő. Ha végzett a makró rögzítésével, nyomja meg a rögzítés leállítása eszköztárat.
A legfontosabb (és ritkán érthető) gomb a "Relatív referencia" gomb.
Jelen példánkban meg kell nyomnia a Relatív hivatkozás gombot, mielőtt elkezdené. Ha olyan makrót rögzít, amelyben a Relatív referenciák be vannak kapcsolva, az Excel a következő lépéseket rögzíti:
- Mozgasson egy cellát lefelé
- Vágja ki az aktuális cellát
- Mozgasson egy cellát feljebb
- Mozgasson egy cellát jobbra
- Paszta
- stb.
Ha ehelyett relatív hivatkozások nélkül rögzítené a makrót, akkor a makró a következő lépéseket rögzíti:
- Ugrás az A4 cellára
- Vágja ki az A4 cellát
- Ugrás az A3 cellára
- Ugrás a B3 cellára
- Illessze be a B3 cellába
- stb.
Ez szörnyen helytelen lenne - szükségünk van a makróra, hogy olyan cellákon dolgozzon, amelyek 1 és 2 cellák a makró kezdőpontjától. A makró újra és újra futtatásakor a kiindulási pont a 4., 5., 6., stb. Sor lesz. Ha a makrót relatív hivatkozások nélkül kapcsolja be, akkor a makró mindig a 3. sorban indul kiindulópontként.
Kovesd ezeket a lepeseket:
- Válassza a Relatív referencia gombot a Felvétel leállítása eszköztáron
- A cellamutatónak már szerepelnie kell egy néven az A oszlopban.
- A lefelé mutató nyíl lenyomásával lépjen a címre
- Ctrl + x a vágáshoz
- Fel nyíl, jobb nyíl a név melletti B oszlopra lépéshez
- Ctrl + v a beillesztéshez
- Lefelé mutató nyíl kétszer, a bal nyíl egyszer a városba lépéshez
- Ctrl + x a vágáshoz
- Kétszer felfelé nyíl, háromszor jobbra nyíl
- Ctrl + v a beillesztéshez
- A balra mutató nyíl kétszer, a lefelé mutató nyíl egyszer az üres sorra lépéshez.
- Tartsa lenyomva a shift billentyűt, miközben kétszer lenyomja a lefelé mutató nyilat
- Alt + edr az üres sorok törléséhez
- Felfelé és lefelé mutató nyíl a lista következő nevének kiválasztásához.
- Kattintson a Felvétel leállítása eszköztárra
- Mentse el a munkafüzetet
- Tesztelje a makrót a fent hozzárendelt parancsikon megnyomásával. Tökéletesen rögzítenie kell a lista következő nevét
Miután látta, hogy a makró a kívánt módon működik, tartsa lenyomva a Ctrl + a billentyűt, hogy másodpercenként gyorsan rögzítsen néhány nevet. Az 500 névből álló teljes lista egy-két perc alatt kijavítható.
Bónusztipp - Nincs a műsorban
Könnyedén bepakolhatja a makrót egy egyszerű ciklusba annak érdekében, hogy az megjavítsa mind az 500 nevet, anélkül, hogy több százszor kellene megnyomnia a Ctrl + billentyűt.
Nyomja meg az Alt + F11 billentyűt a makrószerkesztő megnyitásához.
Ha nem látja a Projekt - VBAProject ablaktáblát, nyomja meg a Ctrl + r billentyűkombinációt.
Kattintson a jobb gombbal a Modul1 elemre, és válassza a Kód megtekintése lehetőséget. Látni fogja a következő kódot:
Most nem kell megértenie, hogy mit csinál ez a kód, de azt tudja, hogy mindent meg akarunk futtatni ebben a makróban minden egyes névnél. Ha tudja, hogy 462 név van, akkor hozzáadhat 2 sort a kód 462-szeres futtatásához. A makró tetejére illesszen be egy új sort a " For i = 1 to 462" szavakkal . A makró aljára illesszen be egy sort, amely a következőt írja: " Next i". Ez azt mondja a VBA-nak, hogy 462-szer futtassa a kódot.
Következtetés
Ezen egyszerű makró után mutattam egy példát egy nagyon összetett makró műsorán. Ez a makró pivot táblákat és diagramokat hozott létre egy vállalat 46 részlegéhez. Ez a jelentés havonta 40 órát vett igénybe. A VBA makró futtatja és kevesebb mint 2 perc alatt létrehozza mind a 46 jelentést.
A VBA és makrók című könyv, a Microsoft Excel 2016 végigvezeti Önt a tanulási görbén, így eljuthat az ehhez hasonló egyszerű makrók rögzítésétől a nagyon összetett jelentések futtatásáig, amelyek évente több száz órát takaríthatnak meg. Természetesen, ha nem akarja megtanulni a VBA-t, vegyen fel egy Excel tanácsadót, aki jelentést készít Önnek.
