Hogyan írjunk makrókat az Excelhez. Makrók Excelben – Használati útmutató. Makrógombok hozzáadása a lapokhoz

Téma 2.3. Prezentációs szoftverek és irodai programozási alapismeretek

Téma 2.4. Adatbázis-kezelő rendszerek és szakértői rendszerek

2.4.11. Képzési adatbázis a "Training_students" főgombbal - Letöltés

VBA programozás és makrók

2.3. Prezentációs szoftverek és irodai programozási alapismeretek

2.3.7. Az irodai programozás alapjai

BASIC vizuális programozási nyelv alkalmazásokhoz: Visual Basic for Applications (VBA)

A VBA a Visual Basic (VB) vizuális programozási nyelv egy részhalmaza, amely szinte az összes VB-alkalmazások létrehozásához szükséges eszközt tartalmazza.

A VBA abban különbözik a VB programozási nyelvtől, hogy a VBA-t úgy tervezték, hogy közvetlenül az Office-objektumokkal működjön együtt, és nem használható az Office-alkalmazásoktól független projektek létrehozására. Így a VBA-ban a programozási nyelv a VB, a programozási környezet pedig VB szerkesztő formájában valósul meg, amely bármely MS Office alkalmazásból aktiválható.

Például a VBA-szerkesztő PowerPointból való megnyitásához futtassa az Eszközök / Makró / VBA-szerkesztő parancsot. A szerkesztőből az alkalmazásba a Nézet menü Microsoft PowerPoint parancsának kiválasztásával vagy az Alt + F11 billentyűkombináció megnyomásával térhet vissza.

A beépített VBA-szerkesztő vezérlőkészlettel és űrlapszerkesztővel a felhasználó létrehozhat felhasználói felület a fejlesztés alatt álló projekthez képernyő formája. A vezérlőelemek objektumok, és minden objektumnak van egy meghatározott lehetséges eseménykészlete (például kattintás vagy dupla kattintás az egérre, egy billentyű lenyomása, egy objektum húzása stb.).

Minden esemény bizonyos programakciókban (válaszok, reakciók) nyilvánul meg. A felhasználói űrlap lehetővé teszi alkalmazás párbeszédablak létrehozását. A VBA programozási nyelv programkód írására szolgál, például felhasználói függvények létrehozására Excelben.

Az a tény, hogy a VBA programozási rendszert úgy tervezték, hogy az Office objektumokkal működjön, lehetővé teszi a hatékony felhasználást a fejlesztési tevékenységek automatizálására. különféle típusok dokumentumokat.

Nézzük meg a felhasználói függvények VBA-ban való létrehozásának algoritmusát:

1. Nyissa meg a VBA kódszerkesztő ablakot az Eszközök / Makró / Visual Basic Editor parancs végrehajtásával vagy az Alt+F11 lenyomásával.

2. Válassza ki a szerkesztő Beszúrás / Modul menüpontot.

3. Ezután hajtsa végre a Beszúrás / Eljárás műveletet. A megnyíló Eljárás hozzáadása párbeszédpanelen adja meg a függvény nevét (például SUM5), és állítsa be a kapcsolókat: Típus – a Funkció pozícióba; Hatókör (Scope) - a Nyilvános (Általános) pozícióba, és kattintson az OK gombra.

Rizs. 1.

4. A VBA programozás szerkesztő ablakában megjelenik egy függvénysablon: title - Public Function SUM5() és ending - End Function, amelyek közé el kell helyezni a függvény törzskódját.

Rizs. 2.

5. Ezután beírjuk a függvény paramétereinek listáját, például zárójelben megadjuk (x, y, z, i, j), az adattípust (a pontos számításokhoz) és a függvény által visszaadott érték típusát (mi ebben a példában nem írja be). Ezenkívül bevezetjük a függvény törzsét, például SUM5 = x + y + z + i + j. Ennek eredményeként a következő programszöveget kapjuk:

Nyilvános függvény SUM5(x, y, z, i, j)
SZUM5 = x + y + z + i + j
Funkció befejezése

6. Visszatérés az ablakhoz Excel alkalmazások, amelyben például öt értéket kell összeadnunk.

7. Hajtsa végre a „Beszúrás/Funkció” parancsot, és a megnyíló függvényvarázsló ablakában válassza ki a „Felhasználó által definiált” kategóriát, majd a „Funkció kiválasztása” ablakban válassza ki a SUM5-öt, majd kattintson az OK gombra.

Rizs. 3.

Rizs. 4.

Makrók

Makró létrehozása

Hozzunk létre egy makrót, amely automatikusan összead két számot az A1, B1 cellákban, és az eredményt a C1 cellába helyezzük, valamint a C1 cellát türkiz színnel töltjük ki.

Algoritmus makró létrehozásához a feladathoz:

1. Válassza az Eszközök/Makró menü Felvétel indítása menüpontját.

2. A Makró neve mezőben adja meg a makró nevét. A makró nevének első karakterének betűnek kell lennie. A szóközök nem megengedettek a makró nevében; Az aláhúzásjeleket szóelválasztóként használhatja.

3. Makró billentyűparancs segítségével történő futtatásához írjon be egy betűt a Billentyűparancs mezőbe. Használhatja a CTRL+ betű (kisbetűkhöz) vagy CTRL+SHIFT+ betű (nagybetűkhöz) kombinációkat, ahol a betű bármely betűbillentyű a billentyűzeten. Ne válasszon szabványos billentyűkódot, mert a kiválasztott billentyűparancs felülírja a szabványos billentyűparancsokat Microsoft Excel annak idején ezzel a könyvvel.

4. A "Mentés" mezőben válassza ki azt a munkafüzetet, amelybe menteni szeretné a makrót. Mentse el a makrót az „Ebbe a munkafüzetbe”. Az alkotáshoz rövid leírás makró, írja be a szükséges szöveget a Leírás mezőbe. A képernyőképen egy példa látható a „Makró rögzítése” párbeszédpanel kitöltésére

Rizs. 5.

5. Kattintson az OK gombra.

6. Hajtsa végre a rögzíteni kívánt makróparancsokat.

Rizs. 6.

7. Kattintson a Felvétel leállítása gombra az operációs rendszer lebegő eszköztárán (Rögzítés leállítása) vagy az Eszközök / Makró / Makró leállítása elemre.

Amikor egy makró befejezte a rögzítést, a neve alatt megjelenik a makrók listájában.

A VBA szerkesztő a végrehajtott műveletek alapján automatikusan rögzített egy makróparancsok sorozatát vagy egy programot (7. ábra).

Rizs. 7.

Makró hívásához végre kell hajtania az Eszközök / Makró / Makrók parancsot. Ezt követően a makrók listáját tartalmazó párbeszédpanelen név szerint keresheti meg, és kattintson a Futtatás gombra.

Eszköztár gomb hozzárendelése makró futtatásához

A makró a beépített eszköztár gombjával indítható el, ehhez a következőket kell tennie:

1. Válassza az Eszközök menü Beállítások menüpontját.
2. A Beállítások párbeszédpanelen válassza a Parancsok lapot, a Kategóriák listában válassza a Makrók lehetőséget, majd a Parancsok listában válassza az „Egyéni gomb” lehetőséget.
3. A Parancsok listában az egérrel húzza az egyéni gombot az eszköztárra.
4. Kattintson a jobb gombbal erre a gombra, és válassza a Makró hozzárendelése lehetőséget helyi menü.
5. Adja meg a makró nevét a Makró neve mezőben.

Grafikus objektum területének hozzárendelése makró futtatásához:

1. Hozzon létre egy grafikus objektumot.
2. Alkalmazza a helyi menüt a kiválasztott grafikus objektumra.
3. Válassza ki a Makró hozzárendelése parancsot a helyi menüből.
4. A megjelenő Makró hozzárendelése objektumhoz párbeszédpanelen írja be a makró nevét a Makró neve mezőbe, majd kattintson az OK gombra.

A makró szerkesztése a VBA-szerkesztővel történik, ehhez a következőket kell tennie:

1. Válassza ki az Eszközök / Makrók / Makrók parancsot.
2. Válassza ki a módosítani kívánt makró nevét a Név listából.
3. A Szerkesztés gombra kattintva megnyílik egy Visual Basic ablak, amelyben a kiválasztott makró Visual Basicben írt parancsait szerkeszthetjük.

Makró eltávolítása:

1. Az Eszközök menüben válassza a Makrók, majd a Makrók lehetőséget.
2. Az aktuális munkafüzet makrók listájában ki kell jelölnie a törölni kívánt makrót, majd kattintson a Törlés gombra.

Makró átnevezése

A makró átnevezéséhez makrószerkesztési módba kell lépnie, és meg kell változtatnia a címet a programszövegben. Az új név automatikusan felváltja a régit a makrólistákban, és a gyorsbillentyűk az új névvel hívják meg a makrót.

Először egy kicsit a terminológiáról.

Makró- ez az Excel beépített nyelvén írt kód VBA(Visual Basic alkalmazáshoz). Makrók manuálisan vagy automatikusan rögzíthetők egy úgynevezett makrórögzítő segítségével.

Makró felvevő egy olyan eszköz az Excelben, amely lépésről lépésre rögzít mindent, amit az Excelben csinál, és VBA-kóddá alakítja át. A makrórögzítő nagyon részletes kódot állít elő (amint azt később látni fogjuk), amelyet később szükség esetén szerkeszthet.

A rögzített makró korlátlan számú alkalommal futtatható, és az Excel megismétli az összes rögzített lépést. Ez azt jelenti, hogy még ha nem is tud semmit a VBA-ról, bizonyos feladatokat automatizálhat úgy, hogy egyszerűen rögzíti a lépéseket, majd később újra felhasználja.

Most pedig nézzük meg, hogyan lehet makrót rögzíteni az Excelben.

Jelenítse meg a Fejlesztő lapot a menüszalagon

Makró rögzítése előtt hozzá kell adni egy Fejlesztő lapot az Excel menüszalaghoz. Ehhez kövesse az alábbi lépéseket:

Ennek eredményeként a menüszalagon megjelenik a „Fejlesztő” fül.

Makró rögzítése Excelben

Most írjunk egy nagyon egyszerű makrót, amely kiválaszt egy cellát, és szöveget ír be, például "Excel".

Íme az ilyen makró rögzítésének lépései:

Gratulálunk! Most rögzítette az első makrót Excelben. Bár a makró nem csinál semmi hasznosat, segít megérteni, hogyan működik a makrórögzítő Excelben.

Most nézzük meg a kódot, amelyet a makrórögzítő rögzített. Kövesse az alábbi lépéseket a kódszerkesztő megnyitásához:

Látni fogja, hogy a Futtatás gombra kattintva az "Excel" szöveg bekerül az A2 cellába, és az A3 cella lesz kiválasztva. Ez ezredmásodpercek alatt történik. Valójában azonban a makró egymás után hajtotta végre a rögzített műveleteket.

Jegyzet. A makrót a Ctrl + Shift + N billentyűparancs használatával is futtathatja (tartsa lenyomva a Ctrl és Shift billentyűket, majd nyomja meg az N billentyűt). Ez ugyanaz a címke, amelyet a makróhoz rendeltünk a rögzítéskor.

Mit rögzít egy makró?

Most menjünk a kódszerkesztőbe, és nézzük meg, mit kaptunk.

Íme a lépések a VB-szerkesztő megnyitásához Excelben:

1. A Kód csoportban kattintson a Visual Basic gombra.

Használhatja az Alt + F11 billentyűkombinációt is, és lépjen a VBA kódszerkesztőbe.

• Menüsor: Olyan parancsokat tartalmaz, amelyeket a VB-szerkesztővel való munka során használhat.
• Eszköztár- úgy néz ki, mint egy panel gyors hozzáférés Excelben. Hozzáadhat további gyakran használt eszközöket.
• Project Explorer- Itt az Excel felsorolja az összes munkafüzetet és az összes objektumot az egyes munkafüzetekben. Például, ha van egy munkafüzetünk 3 munkalappal, akkor az megjelenik a Project Explorerben. Számos további objektum található itt, például modulok, felhasználói űrlapok és osztálymodulok.
• Kód ablak- maga a VBA kód ebben az ablakban található. A projektböngészőben felsorolt ​​minden objektumhoz tartozik egy kódablak, például munkalapok, munkafüzetek, modulok stb. Ebben az oktatóanyagban látni fogjuk, hogy a rögzített makró a modulkód ablakában van.
• Tulajdonságok ablak- ebben az ablakban láthatja az egyes objektumok tulajdonságait. Gyakran használom ezt az ablakot objektumok címkézésére vagy tulajdonságaik megváltoztatására.
• Azonnali ablak(előnézeti ablak) - A kezdeti szakaszban nem lesz rá szüksége. Akkor hasznos, ha lépéseket szeretne tesztelni vagy hibakeresés közben. Alapértelmezés szerint nem jelenik meg, és a Nézet fülre kattintva és az Azonnali ablak opció kiválasztásával jelenítheti meg.

Amikor felvettük az "EnterText" makrót, a következő dolgok történtek a VB szerkesztőben:

• Új modul került hozzáadásra.
• A makrót az általunk megadott néven rögzítettük - "InputText"
• Egy új eljárás került a kódablakba.

Tehát ha duplán kattint egy modulra (esetünkben az 1. modulra), megjelenik egy kódablak az alábbiak szerint.

Íme a makrórögzítő által rögzített kód:

SubTextEnter() " "TextEnter makró " " Tartomány("A2").Select ActiveCell.FormulaR1C1 = "Excel" Tartomány("A3").Válassza ki az End Sub lehetőséget

A VBA-ban az " (az aposztrófot) követő sorok nem hajtódnak végre. Ez egy megjegyzés, amely csak tájékoztató jellegű. Ha eltávolítja a kód első öt sorát, a makró továbbra is működni fog.

Most menjünk végig a kód minden során, és írjuk le, mit és miért.

A kód ezzel kezdődik Alatti ezt követi a makró neve és az üres zárójelek. A Sub a szubrutin rövidítése. Minden szubrutin (más néven eljárás) a VBA-ban azzal kezdődik Alattiés véget ér Vége Sub.

• Tartomány("A2").Select – Ez a sor az A2 cellát választja ki.
• ActiveCell.FormulaR1C1 = "Excel" - Ez a sor az "Excel" szöveget írja be az aktív cellába. Mivel első lépésként az A2 cellát választottuk, ez lesz az aktív cellánk.
• Tartomány("A3").Select - válassza ki az A3 cellát. Ez akkor fordul elő, ha megnyomjuk az Enter billentyűt olyan szöveg beírása után, amely az A3 cellát eredményezi.

Remélhetőleg rendelkezik alapvető ismeretekkel a makrók Excelben való rögzítéséhez.

Kérjük, vegye figyelembe, hogy a makrórögzítőn keresztül rögzített kód általában nem hatékony vagy optimalizált kód. A makrórögzítő gyakran több felesleges lépést ad hozzá. De ez nem jelenti azt, hogy nem kell makrórögzítőt használnia. Azok számára, akik most tanulják a VBA-t, a makrórögzítő nagyszerű módja annak, hogy elemezze és megértse, hogyan működik minden a VBA-ban.

Abszolút és relatív makró rögzítés

Ismeri már az abszolút és relatív hivatkozásokat az Excelben? Ha abszolút hivatkozást használ egy makró rögzítéséhez, a VBA-kód mindig ugyanazokra a cellákra fog hivatkozni, amelyeket használt. Például, ha kiválasztja az A2 cellát, és beírja az "Excel" szöveget, akkor minden alkalommal - függetlenül attól, hogy hol van a munkalapon, és nem számít, melyik cella van kiválasztva, a kód az "Excel" szöveget írja be az A2 cellába.

Ha a relatív hivatkozás beállítást használja egy makró rögzítéséhez, a VBA nem kötődik egy adott cellacímhez. Ebben az esetben a program „mozog” az aktív cellához képest. Tegyük fel például, hogy már kiválasztotta az A1 cellát, és elkezdi a makró rögzítését relatív link módban. Most kiválasztja az A2 cellát, és írja be Excel szövegés nyomja meg az Entert. Ha most futtatja ezt a makrót, akkor nem tér vissza az A2 cellába, hanem az aktív cellához képest mozog. Például, ha a B3 cella ki van jelölve, az átkerül a B4-be, beírja az "Excel" szöveget, majd a K5 cellába.

Most rögzítsük a makrót relatív link módban:

A relatív link módban lévő makró mentésre kerül.

Most tegye a következőket.

1. Jelöljön ki egy tetszőleges cellát (az A1 kivételével).
2. Lépjen a „Fejlesztő” fülre.
3. A Kód csoportban kattintson a Makrók gombra.
4. A Makró párbeszédpanelen kattintson a mentett Relatív hivatkozások makróra.
5. Kattintson a "Futtatás" gombra.

Amint látni fogja, a makró nem írta be az "Excel" szöveget az A2 cellákba. Ez azért történt, mert a makrót relatív referencia módban rögzítette. Így a kurzor az aktív cellához képest mozog. Például, ha ezt a B3 cella kijelölése közben teszi meg, az Excel szövegbe kerül - B4 cellába, és végül a B5 cellát választja ki.

Íme a kód, amelyet a makrórögzítő rögzített:

Vegye figyelembe, hogy a kód nem hivatkozik a B3 vagy B4 cellára. A makró az Activecell segítségével hivatkozik az aktuális cellára és a cellához viszonyított eltolásra.

Hagyja figyelmen kívül a kód Range ("A1") részét. Ez azon esetek egyike, amikor a makrórögzítő felesleges kódot ad hozzá, aminek nincs értelme, és eltávolítható. Enélkül a kód jól működik.

Mit nem lehet csinálni egy makrórögzítővel?

A makrórögzítő nagyszerű az Excelben, és pontosan rögzíti a lépéseket, de nem biztos, hogy a megfelelő választás az Ön számára, ha ennél többet kell tennie.

• Nem hajthat végre kódot objektum kiválasztása nélkül. Például, ha azt szeretné, hogy a makró a következő munkalapra ugorjon, és az A oszlopban lévő összes kitöltött cellát kijelölje anélkül, hogy elhagyná az aktuális munkalapot, a makrórögzítő ezt nem fogja tudni megtenni. Ilyen esetekben manuálisan kell szerkesztenie a kódot.
• Makrórögzítővel nem hozhat létre egyéni funkciót. A VBA segítségével egyéni függvényeket hozhat létre, amelyek normál függvényként használhatók a munkalapokon.
• A makrórögzítővel nem hozhat létre hurkokat. De rögzíthet egy műveletet, és manuálisan is hozzáadhat egy ciklust a kódszerkesztőben.
• A feltételeket nem lehet elemezni: a feltételeket kódban ellenőrizheti egy makrórögzítővel. Ha kézzel ír VBA-kódot, akkor az IF Then Else utasításokkal kiértékelheti a feltételt, és futtathatja a kódot, ha igaz (vagy más kódot, ha hamis).

A makrókat tartalmazó Excel-fájlok kiterjesztése

Amikor makrót rögzít vagy manuálisan ír VBA-kódot az Excelben, a fájlt makró-kompatibilis fájlkiterjesztéssel (.xlsm) kell mentenie.

Az Excel 2007 előtt egyetlen fájlformátum is elegendő volt - .xls. De 2007 óta az .xlsx szabványos fájlkiterjesztésként került bevezetésre. A .xlsx formátumban mentett fájlok nem tartalmazhatnak makrót. Tehát ha van egy .xlsx kiterjesztésű fájlja, és ír/rögzít egy makrót, majd elmenti, akkor figyelmezteti Önt, hogy makróképes formátumba kell mentenie, és a következő párbeszédpanel jelenik meg:

Ha a Nem lehetőséget választja, az Excel makróképes formátumban menti a fájlt. Ha azonban az Igen gombra kattint, az Excel automatikusan eltávolítja az összes kódot a munkafüzetből, és a fájlt .xlsx formátumú munkafüzetként menti. Tehát ha van makró a munkafüzetében, akkor azt .xlsm formátumban kell mentenie a makró mentéséhez.

Mi az a makró? Makró egy mentett műveletsorozat vagy egy VBA-ban (Visual Basic for Application) létrehozott program. Gyakran Ismételt kérdés hogyan lehet makrót írni Excelben? Éppen.

Azok. ha ugyanazokat a műveleteket többször kell végrehajtanunk, ezeket a műveleteket megjegyezhetjük és egyetlen gombbal lefuttathatjuk. Ebben a cikkben nem kényszerítem a VBA megtanulására, és nem is javasolok sok szabványos makrót. Valójában bárki létrehozhat/írhat makrót Excelben. Erre a célra az Excel legérdekesebb és legszokatlanabb funkciója van - Makró felvevő(műveleteinek rögzítése kód formájában). Azok. felveheti a cselekvéseit, például videón, és lefordíthatja őket kódba (szekvenciába).

Röviden, ha minden nap ugyanazokat a műveleteket hajtja végre, érdemes kitalálni, hogyan automatizálhatja ezt a folyamatot. Olvasson tovább, ha megtudja, hogyan írhat makrót Excelben?

1. Makrók engedélyezése

Menü (kerek gomb a bal felső sarokban) - Excel-beállítások— Bizalmi központ — Bizalmi központ beállításai — Makróbeállítások. Jelölje be az "Összes makró engedélyezése" jelölőnégyzetet.

Vagy tegye ugyanezt a Fejlesztő lapon

2. Engedélyezze a Fejlesztői menüt gyors munka makróval

Menü (kerek gomb a bal felső sarokban) - Excel-beállítások - Általános - Fejlesztői lap megjelenítése.

Ahhoz, hogy a létrehozott makró a munkafüzetbe kerüljön, el kell mentenie a fájlt .xlsm vagy .xlsb. Kattintson a Mentés másként – Makró engedélyezése Excel-munkafüzet vagy bináris munkafüzet elemre.

Szóval, hogyan lehet makrót írni az Excelben?

Ez egyszerű. Menjünk az Excelhez. Az oldal alján, a lapcímkék alatt található a „Makró rögzítése” gomb.

Kattintson a gombra, és megnyílik a „Makró rögzítése” ablak. A Makró neve: mezőben tetszés szerint elnevezzük a makrót. Beállítjuk azt a billentyűkombinációt, amellyel később hívni fogjuk (nem kötelező).

Kattintson az OK gombra. A makrórögzítő megkezdte a felvételt.

Minden. Most elvégezzük azokat a műveleteket, amelyeket makróként kell megjegyeznünk. Például törölnünk kell egy sort, és az A1 cellát sárgára kell színeznünk.

Ezen manipulációk során a lapok alatti „Makró rögzítése” gomb helyett egy négyzet világít, amelyre kattintva a makró végrehajtása leáll.

Hogyan indítsuk el a történteket? Kattintson a gombra, megjelenik a Makró kiválasztása ablak, válassza ki a kívánt makrót, majd kattintson a Futtatás gombra.

Hogyan láthatom, mi történt? Kattintson a gombra. Válassza ki a kívánt makrót, majd kattintson a Szerkesztés gombra. Megnyílik a Makró rögzítése ablak (VBA ablak).

A makrókódnak valahogy így kell kinéznie.

Sub example1() " Példa1 Makró sorok("2:2").Select Selection.Delete Shift:=xlUp Range("A1").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic.Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End with End Sub

‘ egy megjegyzés szimbólum, azaz. ez a sor nem szerepel a kódban. A makróknak névvel (Sub example1()) és végződéssel (End Sub) kell rendelkezniük.

Elolvashatja, hogyan hozhat létre gombot egy makróhoz.

Tehát bármelyik cselekedetét rögzítheti, hogy annyiszor ismételje meg, ahányszor csak akarja!

Elolvashatja, hogyan menthet makrót a számítógépén lévő összes könyvhez.

Ehhez használja a Személyes.

Elolvashatja, hogyan futtathat makrót feltétel alapján

Sok sikert, biztos vagyok benne, hogy nagyon hasznosnak találod majd!

Oszd meg cikkünket közösségi oldaladon:

A táblák vagy más Office-objektumok esetében gyakran előfordulnak olyan helyzetek, amikor ugyanazokat a műveleteket kell végrehajtania. Ez nem csak több időt vesz igénybe, hanem nagyon bosszantó is. Szerencsére vannak makrók a probléma megoldására.

A VBA-környezetet makrók létrehozására tervezték, de nem kell programozónak lennie, vagy nem kell tanulnia a Visual Basic for Application-t ahhoz, hogy megtanulja megírni azokat. Erre a célra speciálisan kifejlesztett eszközök állnak rendelkezésre, amelyek az Ön parancsára VBA-kódot hoznak létre anélkül, hogy további ismereteket igényelnének. Ennek a nyelvnek az elsajátítása azonban nem olyan nehéz.

Makrók létrehozása alkalmazásokban rögzítési eszközök segítségével történik. Ebben az esetben egy bizonyos billentyűkombinációhoz hozzá van rendelve a kívánt műveletsor. Nyisson meg egy Office-alkalmazást. Válassza ki a formázni kívánt töredéket.

Válassza az "Eszközök" -> "Makró" -> "Rögzítés indítása" menüpontot (Office 2007-ben - "Nézet" -> "Makrók" -> "Makró rögzítése"). A megjelenő „Makró rögzítése” ablakban állítsa be az új makró nevét, alapértelmezés szerint „Makró1”, de célszerűbb nevet adni, különösen, ha több makró is lesz. Maximális méret névmezők – 255 karakter, pont és szóköz karakterek nem megengedettek.

Döntse el, hogy a makrója melyik gombot vagy billentyűkombinációt fogja használni a jövőben. Próbálja meg kiválasztani a legkényelmesebb lehetőséget, különösen, ha gyakori használatra szánják. Válassza ki a megfelelő elemet a „Makró hozzárendelése” mezőben: „button” vagy „keys”.

Ha a "gombot" választja, megnyílik a "Gyorsbeállítások" ablak. Amikor kiválasztja a „billentyűket”, csak be kell írnia a kombinációt a billentyűzeten. Tekintse át a „Jelenlegi kombinációkat” az ismétlődés elkerülése érdekében. Kattintson a "Hozzárendelés" gombra.

A Wordben és PowerPointban létrehozott makró a jövőben minden dokumentumra érvényes lesz. Ha az Excel-makrót minden dokumentum számára elérhetővé szeretné tenni, mentse el a personal.xls fájlba, amely az alkalmazás megnyitásakor automatikusan lefut. Hajtsa végre a „Window” -> „Display” parancsot, és a megjelenő ablakban válassza ki a personal.xls fájl nevét tartalmazó sort.

Írja be a makró leírását a Leírás mezőbe. Kattintson az OK gombra, és visszatér a dokumentumba, de az egérkurzoron most egy felvétel ikon látható. Formázza a szöveget az automatizálni kívánt műveletsorral. Legyen nagyon óvatos, és ne végezzen felesleges műveleteket, mivel a makró mindegyiket rögzíti, és ez befolyásolja a végrehajtási idejét a jövőben.

Hajtsa végre az "Eszközök" -> "Makró" -> "Rögzítés leállítása" parancsot. Létrehozott egy VBA-objektumot anélkül, hogy egyetlen kódsort is írt volna. Ha azonban továbbra is manuálisan kell módosítania, adja meg az objektumot a „Makrók” részben, a „Módosítás” paranccsal vagy az Alt+F8 billentyűkombinációval.

Kevesen tudják, hogy a népszerű Microsoft Excel termék első verziója 1985-ben jelent meg. Azóta számos módosításon ment keresztül, és világszerte több millió felhasználó keresi. Ugyanakkor sokan ennek a táblázatkezelő processzornak a képességeinek csak töredékével dolgoznak, és nem is sejtik, hogyan könnyítheti meg az életét az Excelben való programozás.

Mi az a VBA

Az Excel programozás a Visual Basic for Application programozási nyelvvel történik, amelyet eredetileg a Microsoft leghíresebb táblázatkezelő processzorába építettek be.

A szakértők a tanulás viszonylagos könnyűségét az előnyeinek tulajdonítják. A gyakorlat azt mutatja, hogy még a professzionális programozási ismeretekkel nem rendelkező felhasználók is elsajátíthatják a VBA alapjait. A VBA szolgáltatásai közé tartozik a parancsfájlok irodai alkalmazási környezetben történő végrehajtása.

A program hátránya a különböző verziók kompatibilitásával kapcsolatos problémák. Ezeket az okozza, hogy a VBA programkód hozzáfér funkcionalitás, amelyek jelen vannak új verzió termék, de hiányzik a régiből. A hátrányok közé tartozik továbbá a kód túlzottan nyitottsága a kívülálló általi változtatásokra. Mindazonáltal Microsoft Office, valamint az IBM Lotus Symphony lehetővé teszi a felhasználó számára a kezdeti kód titkosítását, és jelszó beállítását a megtekintéshez.

Objektumok, gyűjtemények, tulajdonságok és módszerek

Ezeket a fogalmakat meg kell érteniük azoknak, akik VBA környezetben fognak dolgozni. Először is meg kell értened, mi az objektum. Az Excelben ezek a függvények a lap, a munkafüzet, a cella és a tartomány. Ezek az objektumok speciális hierarchiával rendelkeznek, pl. engedelmeskedjenek egymásnak.

A fő az Alkalmazás, amely megfelel a Excel program. Aztán jönnek a munkafüzetek, a munkalapok és a tartomány. Például egy adott munkalap A1 cellájának eléréséhez meg kell adnia egy elérési utat, amely figyelembe veszi a hierarchiát.

Ami a „gyűjtemény” fogalmát illeti, ez egy azonos osztályba tartozó objektumok csoportja, amely a bejegyzésben ChartObjects alakú. Egyedi elemei is tárgyak.

A következő fogalom a tulajdonságok. Minden tárgy szükséges jellemzői. Például a Tartomány esetében ez érték vagy képlet.

A metódusok olyan parancsok, amelyek jelzik, hogy mit kell tenni. Amikor VBA-ban kódot ír, ezeket ponttal kell elválasztani az objektumtól. Például, ahogy a későbbiekben látni fogjuk, az Excel programozása során nagyon gyakran a Cells(1,1).Select parancsot használják. Ez azt jelenti, hogy ki kell választania egy cellát koordinátákkal

A Selection.ClearContents gyakran együtt használatos vele. Ennek végrehajtása a kijelölt cella tartalmának törlését jelenti.

Hogyan kezdjük

Ezután el kell lépnie a VB alkalmazásba, amelyhez csak az „Alt” és az „F11” billentyűkombinációt kell használnia. További:

• az ablak tetején található menüsorban kattintson az Excel ikon melletti ikonra;
• válassza ki a Mudule parancsot;
• mentés a képpel ellátott ikonra kattintva;
• írnak mondjuk a kódex tervezetét.

Ez így néz ki:

alprogram()

"A mi kódunk

Felhívjuk figyelmét, hogy a „Kódunk” sor más színnel (zöld) lesz kiemelve, ennek oka a sor elején elhelyezett aposztróf, amely azt jelzi, hogy megjegyzés következik.

Mostantól bármilyen kódot írhat, és létrehozhat egy új eszközt magának VBA Excel(lásd alább a példákat a programokra). Persze azoknak sokkal könnyebb dolguk lesz, akik ismerik a Visual Basic alapjait. Azonban még az is gyorsan megszokhatja, ha akarja.

Makrók Excelben

Ez a név elrejti a Visual Basic for Application programban írt programokat. Így az Excel programozása makrók létrehozását jelenti a szükséges kódot. Ennek a funkciónak köszönhetően a Microsoft táblázatkezelő processzora önmagát fejleszti, alkalmazkodik egy adott felhasználó követelményeihez. Miután rájött, hogyan hozhat létre modulokat a makrók írásához, elkezdheti a mérlegelést konkrét példák VBA Excel programok. A legjobb a legalapvetőbb kódokkal kezdeni.

1. példa

Feladat: írjon egy programot, amely átmásolja az egyik cella tartalmának értékét, majd beírja egy másikba.

Ezért:

• nyissa meg a „Nézet” lapot;
• lépjen a „Makrók” ikonra;
• kattintson a „Makró rögzítése” gombra;
• töltse ki a megnyíló űrlapot.

Az egyszerűség kedvéért a „Makró neve” mezőben hagyjuk a „Makró1”-t, a „Billentyűparancs” mezőbe pedig szúrjuk be például a hh-t (ez azt jelenti, hogy a „Ctrl+h” gyorsparancs segítségével indíthatja el a programot). Nyomd meg az Entert.

Most, hogy a makrórögzítés már elindult, egy cella tartalma átmásolódik egy másikba. Visszatérés az eredeti ikonhoz. Kattintson a „Makró rögzítése” gombra. Ez a művelet a program végét jelenti.

• lépjen vissza a „Makrók” sorhoz;
• válassza ki a „Makró 1” lehetőséget a listából;
• kattintson a „Futtatás” gombra (ugyanezt a műveletet indítja el a „Ctrl+hh” billentyűkombináció elindításával).

Ennek eredményeként a makró rögzítése közben végrehajtott művelet megtörténik.

Érdemes megnézni, hogyan néz ki a kód. Ehhez lépjen vissza a „Makrók” sorhoz, és kattintson a „Módosítás” vagy a „Bejelentkezés” gombra. Ennek eredményeként a VBA környezetben találják magukat. Valójában maga a makrókód a Sub Macro1() és End Sub sorok között található.

Ha például az A1 cellából a C1 cellába másolt, akkor az egyik kódsor így fog kinézni: Tartomány(“C1”). Válassza ki. Lefordítva úgy néz ki, mint „Tartomány(“C1”).Select, vagyis a VBA Excelbe, a C1 cellába kerül.

A kód aktív részét az ActiveSheet.Paste parancs fejezi be. Ez azt jelenti, hogy a kijelölt cella (jelen esetben A1) tartalmát a kijelölt C1 cellába írjuk.

2. példa

A VBA hurkok segítenek különféle makrók létrehozásában az Excelben.

A VBA hurkok segítenek különféle makrók létrehozásában. Tegyük fel, hogy van egy y=x + x 2 + 3x 3 - cos(x) függvényünk. Létre kell hoznia egy makrót, hogy megkapja a grafikonját. Ezt csak VBA hurkok segítségével lehet megtenni.

A függvény argumentumának kezdeti és végső értéke x1=0 és x2=10. Ezenkívül meg kell adnia egy állandót - az argumentumváltoztatási lépés értékét és a számláló kezdeti értékét.

Az összes VBA Excel makrópélda a fent bemutatott eljárással készült. Ebben az esetben a kód így néz ki:

alprogram()

lépés = 0,1

Do While x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Érték = x1 (az x1 értéket az (i,1) koordinátákkal rendelkező cellába írjuk)

Cells(i, 2).Érték = y (az y értéket az (i,2) koordinátákkal rendelkező cellába írjuk)

i = i + 1 (a számláló érvényben van);

x1 = x1 + shag (az argumentum a lépésértékkel változik);

Vége Sub.

Ennek a makrónak az Excelben való futtatásának eredményeként két oszlopot kapunk, amelyek közül az első az x, a második az y értékeit tartalmazza.

Ezután az Excel számára szabványos módon gráfot építenek belőlük.

3. példa

A hurkok megvalósításához a VBA Excel 2010-ben, mint más verziókban, a már megadott Do While konstrukcióval együtt a For használatos.

Vegyünk egy olyan programot, amely oszlopot hoz létre. Minden cellájába beírjuk a megfelelő sorszám négyzetét. A For konstrukció használatával nagyon röviden, számláló használata nélkül írhatja le.

Először létre kell hoznia egy makrót a fent leírtak szerint. Ezután magát a kódot írjuk le. Feltételezzük, hogy 10 cella értékei érdekelnek bennünket. A kód így néz ki.

i = 1-től 10-ig Következő

A parancsot „emberi” nyelvre fordítják: „Ismétlés 1-től 10-ig egy lépésben”.

Ha a feladat az, hogy egy oszlopot kapjunk négyzetekkel, például minden páratlan számmal az 1-től 11-ig terjedő tartományban, akkor ezt írjuk:

i = 1-től 10-ig 1. lépés Következő.

Itt a lépés egy lépés. Ebben az esetben kettővel egyenlő. Alapértelmezés szerint ennek a szónak a hiánya a ciklusban azt jelenti, hogy a lépés egyetlen.

A kapott eredményeket (i,1) sorszámú cellákba kell menteni. Ezután minden alkalommal, amikor a ciklus elindul, amikor i lépéssel növekszik, a sorszám automatikusan növekszik. Így a kód optimalizálva lesz.

Összességében a kód így fog kinézni:

alprogram()

Ha i = 1-től 10-ig, 1. lépés (egyszerűen beírhatja, hogy i = 1-től 10-ig)

Cells(i, 1).Érték = i ^ 2 (azaz az i négyzet értéke az (i,1) cellába van írva

Következő (bizonyos értelemben a számláló szerepét tölti be, és a hurok újabb kezdetét jelenti)

Vége Sub.

Ha minden megfelelően megtörtént, beleértve a makró rögzítését és futtatását (lásd a fenti utasításokat), akkor a meghívásakor minden alkalommal egy megadott méretű (jelen esetben 10 cellából álló) oszlopot kap.

4. példa

BAN BEN Mindennapi élet Gyakran felmerül az igény, hogy valamilyen körülménytől függően ilyen vagy másik döntést hozzunk. A VBA Excelben nem nélkülözheti őket. Példák azokra a programokra, ahol az algoritmus további végrehajtásának menete meg van választva, és nem eleve előre meghatározott, leggyakrabban az If ...Then konstrukciót (bonyolult esetekben) If ...Then ...END If.

Nézzünk egy konkrét esetet. Tegyük fel, hogy létre kell hoznia egy makrót az Excel számára úgy, hogy a következőt írja be az (1,1) koordinátákkal rendelkező cellába:

1, ha az argumentum pozitív;

0, ha az argumentum nulla;

-1, ha az argumentum negatív.

Megkezdődik egy ilyen makró létrehozása az Excel számára szabványos módon, az Alt és az F11 gyorsbillentyűk használatával. Ezután a következő kód kerül kiírásra:

alprogram()

x= Cells(1, 1). Value (ez a parancs x-hez rendeli a cella tartalmának értékét az (1, 1) koordinátákon)

Ha x>0 akkor Cells(1, 1).Érték = 1

Ha x=0, akkor Cells(1, 1).Érték = 0

Ha x<0 Then Cells(1, 1).Value = -1

Vége Sub.

Már csak le kell futtatni a makrót, és lekérni az argumentum szükséges értékét az Excelben.

VBA funkciók

Amint azt már észrevette, a Microsoft leghíresebb táblázatkezelő processzorában nem olyan nehéz programozni. Főleg, ha megtanulod a VBA függvények használatát. Összességében ez a programozási nyelv, amelyet kifejezetten alkalmazások írására Excelben és Wordben hoztak létre, körülbelül 160 funkcióval rendelkezik. Több nagy csoportra oszthatók. Ez:

• Matematikai függvények. Ezeket az argumentumra alkalmazva megkapják a koszinusz, a természetes logaritmus, az egész rész stb. értékét.
• Pénzügyi funkciók. Elérhetőségüknek és az Excel programozásnak köszönhetően hatékony eszközöket kaphat számviteli és pénzügyi számításokhoz.
• Tömbfeldolgozási funkciók. Ide tartozik az Array, IsArray; LBound; UBound.
• Excel VBA függvények karakterlánchoz. Ez egy meglehetősen nagy csoport. Ide tartoznak például a Space függvények egy karakterlánc létrehozásához, amelynek a szóközök száma megegyezik az egész argumentummal, vagy az Asc a karakterek ANSI-kóddá alakításához. Mindegyiket széles körben használják, és lehetővé teszik az Excel soraival való munkát, olyan alkalmazásokat hozva létre, amelyek nagyban megkönnyítik a táblázatokkal való munkát.
• Adattípus-konverziós függvények. Például a CVar az Expression argumentum értékét adja vissza úgy, hogy azt Variant adattípusra konvertálja.
• A dátumokkal való munkavégzés funkciói. Jelentősen kibővítik a szabványosakat, így a WeekdayName függvény a hét napjának (teljes vagy részleges) nevét a számával adja vissza. Még hasznosabb az időzítő. Megadja, hány másodperc telt el éjféltől a nap egy bizonyos pontjáig.
• Függvények egy numerikus argumentum különböző számrendszerekké alakításához. Például az Oct a számokat oktálisan adja ki.
• Formázási funkciók. Ezek közül a legfontosabb a formátum. Egy Variant értéket ad vissza a formátumdeklarációban megadott utasítások szerint formázott kifejezéssel.
• stb.

Ezen függvények tulajdonságainak és alkalmazásuk tanulmányozása jelentősen kibővíti az Excel hatókörét.

5. példa

Próbáljunk meg áttérni az összetettebb problémák megoldására. Például:

Megadják a vállalkozás tényleges költségszintjét bemutató papíralapú dokumentumot. Kívánt:

• sablonrészének fejlesztése az Excel táblázat segítségével;
• hozzon létre egy VBA programot, amely kiindulási adatokat kér a kitöltéshez, elvégzi a szükséges számításokat, és kitölti velük a megfelelő sabloncellákat.

Tekintsük az egyik megoldási lehetőséget.

Hozzon létre egy sablont

Minden műveletet az Excel szabványos lapján hajtanak végre. A szabad cellák a fogyasztói cég nevére, a költségek összegére, mértékére és a forgalomra vonatkozó adatok megadására vannak fenntartva. Mivel azon cégek (cégek) száma, amelyekre a jelentés készül, nincs rögzítve, az eredmények és a szakember neve alapján az értékek bevitelére szolgáló cellák nincsenek előre lefoglalva. A munkalap új nevet kap. Például "jelentések".

Változók

A sablon automatikus kitöltésére szolgáló program írásához ki kell választania a jelöléseket. Ezeket a változókhoz használják:

• NN - az aktuális táblázat sorának száma;
• TP és TF - tervezett és tényleges kereskedelmi forgalom;
• SF és SP - a költségek tényleges és tervezett összege;
• IP és IF - tervezett és tényleges költségek szintje.

Használjuk ugyanazokat a betűket, de az Itog „előtaggal”, hogy jelöljük az oszlop összegének felhalmozódását. Például az ItogTP - a táblázat „tervezett forgalom” oszlopára utal.

Probléma megoldása VBA programozással

A bevezetett jelölések felhasználásával eltérési képleteket kapunk. Ha %-ban kell számolnia, akkor (F - P) / P * 100, és összesen - (F - P).

Ezeknek a számításoknak az eredményeit legjobban közvetlenül az Excel táblázat megfelelő celláiba lehet bevinni.

A tényleges és előrejelzett összegeket az ItogP=ItogP + P és ItogF=ItogF+ F képletekkel kapjuk meg.

Eltérés esetén használja az = (ItogF - ItogP) / ItogP * 100 értéket, ha a számítást százalékban végezzük, összérték esetén pedig - (ItogF - ItogP).

Az eredmények ismét azonnal a megfelelő cellákba kerülnek, így nem kell változókhoz rendelni.

A létrehozott program futtatása előtt el kell mentenie a munkafüzetet, például "Jelentés1.xls" néven.

A „Jelentéstáblázat létrehozása” gombot csak egyszer kell megnyomni a fejléc információinak megadása után. Vannak más szabályok is, amelyeket tudnia kell. Különösen a „Sor hozzáadása” gombra kell kattintani minden alkalommal, miután az egyes tevékenységtípusokhoz tartozó értékeket beírta a táblázatba. Az összes adat megadása után kattintson a „Befejezés” gombra, majd váltson át az Excel ablakra.

Most már tudja, hogyan lehet makrók segítségével megoldani az Excel-problémákat. A vba excel használatának képességére (lásd fentebb a példákat a programokra) is szükség lehet a pillanatnyilag legnépszerűbb szövegszerkesztő, a Word használatához. Különösen menügombokat hozhat létre írással, amint az a cikk elején látható, vagy kódírással, aminek köszönhetően számos szöveges művelet elvégezhető a köteles gombok megnyomásával vagy a „Nézet” fülön keresztül. a „Makrók” ikonra.