Az Excel makró -rögzítőjének nagy ereje van, de vannak korlátai. Amint azt egy másik cikk tárgyalja, a makró rögzítő gyakran rögzít felesleges kódot, és nem tud rögzíteni olyan dolgokat, mint a logika vagy más programokkal való interakció. Hosszabb makrók esetén is nehezen használható - előfordulhat, hogy előtte forgatókönyvet készít a cselekvésekről, hogy elkerülje a költséges hibákat.
Ennek a cikknek a célja, hogy segítsen elkezdeni a makrók kódolását a semmiből a VBA -ban. Megtanulja a makrók tárolási helyét, írhat egy alapvető makrót, és megtanulhatja a VBA programozásának alapjait változók, logika és ciklusok használatával.
Elkezdeni
VBA és a Visual Basic Editor
A VBA vagy a Visual Basic for Applications az a nyelv, amelyen a makrók íródnak. Minden makró VBA-kódként kerül tárolásra, függetlenül attól, hogy kézzel kódolt vagy a makró rögzítővel készült.
A munkafüzet összes VBA -kódját a Visual Basic Editor segítségével érheti el. Ez egy speciális szövegszerkesztő és hibakereső, amely minden irodai alkalmazásba beépített, beleértve az Excel-t is. Ezt a szerkesztőt általában a ALT+F11 billentyűparancsot az Excelben, de az Excelből is elérheti Fejlesztő lapon, ha engedélyezve van.
A Project Explorer
Az Project Explorer egy ablak a VB szerkesztőben, amely megmutatja az összes olyan elemet, amely tartalmazhat VBA kódot. Ha nem látja ezt az ablakot, nyomja meg a gombot F5 megjelenítéséhez vagy kiválasztásához Project Explorer tól Kilátás menü.
Ha duplán kattint egy elemre a Project Explorerben, megjelenik az adott elem kódja. A Project Explorerben többféle elem jelenhet meg:
- Munkafüzetek
- Feladatlapok
- UserForms
- Osztálymodulok
- Modulok (a makrók ezekben az elemekben vannak tárolva)
Bár ezek az elemtípusok tartalmazhatnak VBA kódot, a legjobb gyakorlat a makrók kódolása a modulokban.
Az első makró elkészítése
A makrólista használata
A makrók listája a munkafüzet összes makróját mutatja. A listából szerkeszthet egy meglévő makrót, vagy újat hozhat létre.
Új makró létrehozása a Makrók lista használatával:
- Válassza a Fejlesztő lapot, majd kattintson a gombra Makrók (vagy nyomja meg ALT+F8)
- Írja be a makró új nevét, majd kattintson a „Létrehozás” gombra
A „Létrehozás” gombra kattintás után megjelenik a VB szerkesztő, amely az újonnan létrehozott makrót mutatja. Az Excel szükség esetén új modult hoz létre a makró számára.
Manuálisan a VB szerkesztőben
Új makrót manuálisan is hozzáadhat a Makrók lista nélkül. Ez a jobb megoldás, ha meg szeretné adni azt a modult, amelybe a makró mentésre került.
Makró manuális hozzáadásához:
- Nyissa meg a VB szerkesztőt (ALT+F11)
- Bármelyik:
- Új modul hozzáadása a gombra kattintva Beszúrás> Modul a menüben (a modul automatikusan megnyílik)
-
- VAGY kattintson duplán egy meglévő modulra a Project Explorerben annak megnyitásához
- A modulban írja be az új makró kódját
Sub MyMacro () End Sub
Ez a két sor jelzi a „MyMacro” nevű makró kezdetét és végét (vegye figyelembe a kötelező zárójeleket). Ez megjelenik az Excel „Makrók megtekintése” párbeszédpanelén, és hozzárendelhető egy gombhoz (annak ellenére, hogy ez még nem tesz semmit).
Adjon hozzá néhány kódot a makróhoz
Most adjunk hozzá néhány kódot a „Sub” és „End Sub” sorok közé, hogy ez a makró valóban tegyen valamit:
Sub MyMacro () tartomány („A1”). Érték = „Hello World!” End Sub
Alap kódstruktúrák
A tartomány objektum
Az Excel VBA a Range objektumot használja a munkalap celláinak ábrázolására. A fenti példában egy Range objektum jön létre a kóddal Tartomány („A1”) az A1 cella értékének eléréséhez.
A tartományobjektumokat elsősorban cellaértékek beállítására használják:
Tartomány („A1”). Érték = 1
Tartomány („A1”). Érték = „Első cella”
Vegye figyelembe, hogy amikor a cellaértékeket számként határozza meg, akkor csak írja be a számot, de szövegbevitelkor idézőjelek közé kell helyeznie a szöveget.
A tartományokkal a cellák számos tulajdonsága is elérhető, például betűtípusuk, szegélyeik, képleteik stb.
Például egy cella betűtípusát félkövérre állíthatja be az alábbiak szerint:
Tartomány („A1”). Betűtípus. Félkövér = igaz
Beállíthatja a cella képletét is:
Tartomány („A1”). Képlet = „= összeg (A2: A10)”
Az Excelben kiválaszthat egy cellablokkot a kurzorral (mondjuk A1 -től D10 -ig), és félkövérre állíthatja őket. A tartományobjektumok a következő cellablokkokhoz férhetnek hozzá:
Tartomány („A1: D10”). Betűtípus. Félkövér = igaz
Több cellára/blokkra is hivatkozhat egyszerre:
Tartomány („A1: D10, A12: D12, G1”). Font.Bold = Igaz
Ennek formátuma megegyezik azzal a formátummal, amelyet az Excel SUM () képletének celláinak kiválasztásakor használna. Mindegyik blokkot vessző választja el egymástól, a blokkokat pedig a bal felső és a jobb alsó cella jelöli kettősponttal.
Végül a Range objektumok beépített módszerekkel rendelkeznek a munkalapon végzett gyakori műveletek végrehajtásához. Például érdemes átmásolni néhány adatot egyik helyről a másikra. Íme egy példa:
Tartomány („A1: D10”). Másolási tartomány („F1”). PasteSpecial xlPasteValues Range („F1”). PasteSpecial xlPasteFormats
Ezzel az A1: D10 cellákat a vágólapra másolja, majd a C1 cellában kezdődő PasteSpecial () parancsot készít - ugyanúgy, mint manuálisan az Excelben. Jegyezze meg, hogy ez a példa bemutatja, hogyan kell a PasteSpecial () használatával csak értékeket és formátumokat beilleszteni - vannak paraméterek az összes beállításhoz, amelyeket a Speciális beillesztés párbeszédpanelen látna.
Íme egy példa az „Összes” beillesztésére egy másik munkalapra:
Tartomány („A1: D10”). Lapok másolása („Sheet2”). Tartomány („A1”). PasteSpecial xlPasteAll
Ha Nyilatkozatok
Egy valamivel Ha kijelentés, akkor a kód egy részét csak akkor futtathatja, ha „egy” állítás igaz.
Például érdemes lehet egy cellát félkövérré tenni, és pirosra színezni, de csak akkor, ha a cella értéke kevesebb, mint 100.
Ha tartomány („A4”). Érték <100, akkor tartomány („A4”). Betűtípus. Félkövér = valódi tartomány („A4”). Belső. Szín = vb
Az If utasítás megfelelő felépítése a következő (szögletes zárójelek az opcionális összetevőket jelzik):
Ha akkor
[Különben Ha akkor]
[Más]
Vége Ha
Annyit is felvehet Máskülönben blokkok, ahogy szeretné tesztelni több feltételt. Hozzáadhat egy Más blokk, amely csak akkor fut, ha az If utasítás többi feltétele nem teljesül.
Íme egy másik példa az előző alapján, ahol a cella az érték függvényében többféleképpen formázható:
Ha tartomány ("A4"). Érték <100, majd tartomány ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Érték <200 Then Range ( "A4"). Font.Bold = False Range ("A4"). Interior.Color = vb
A fenti példában a cella félkövéren van szedve azokban az ElseIf blokkokban, ahol az érték nem 100 alatt. fészek Ha a kódok megkettőzésének elkerülése érdekében kijelentéseket kell tennie, például:
If Range ("A4"). Value <100 then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' a betűtípust csak egyszer oldja fel Ha a tartomány ("A4"). Érték <200 Akkor a tartomány ("A4"). Interior.Color = vbSárga más tartomány ("A4"). Interior.Color = vbGreen End If End If
Változók
A Változó egy memória, amelyet ideiglenes információk tárolására használnak makró futása közben. Gyakran használják ciklusokban iterátorként, vagy egy olyan művelet eredményének megőrzésére, amelyet többször szeretne használni a makró során.
Íme egy példa egy változóra és annak használatára:
Sub ExtractSerialNumber () Dim strSerial As String 'ez a változó deklaráció' 'String' azt jelenti, hogy ez a változó a szöveget hivatott tárolni 'beállítva egy sorozatszámot: Tartomány ("A4"). Value = "serial# 804567-88 "'Elemezze a sorozatszámot az A4 -es cellából, és rendelje hozzá az strSerial = Mid (tartomány (" A4 "). Érték, 9) változóhoz, most kétszer használja a változót, ahelyett, hogy kétszer kellene elemeznie a sorozatszámot. B4 ”). Érték = strSerial MsgBox strSerial End Sub
Ebben az alapvető példában az „strSerial” változót a sorozatszám kivonására használják az A4 -es cellából a Mid () függvénnyel, majd két másik helyen is használják.
A szokásos módja annak kijelent egy változó a következő:
Homályos bármilyen név [Mint típus]
- bármilyen név ez az a név, amelyet úgy dönt, hogy megadja a változót
- típus a változó adattípusa
A „[As típus] ”Rész elhagyható - ha igen, akkor a változót Variant típusként deklarálják, amely bármilyen adatot képes tárolni. Bár tökéletesen érvényes, a variáns típusokat kerülni kell, mivel váratlan eredményekhez vezethetnek, ha nem vigyáz.
Vannak szabályokat változónevekhez. Vagy betűvel vagy aláhúzással kell kezdődniük, nem tartalmazhatnak szóközöket, pontokat, vesszőket, idézőjeleket vagy karaktereket! " @ & $ #”.
Íme néhány példa a változó deklarációkra:
Dim strFilename As String 'jó névstílus - leíró és előtagot használ Dim i As Long' rossz névstílus - csak néhány iterátor számára elfogadható - nem túl leíró, előtagot használ, nincs adattípus
Mindezek a példák némileg eltérő elnevezési sémákat használnak, de mindegyik érvényes. Nem rossz ötlet, ha a változó nevét adattípusának rövid formájával kell előtagolni (néhány ilyen példa szerint), mivel így egy pillanat alatt olvashatóbbá válik a kód.
A VBA sok alapot tartalmaz adattípusok. A legnépszerűbbek közé tartozik:
- Húr (szöveges adatok tárolására szolgál)
- Hosszú (egész számok tárolására szolgálnak, azaz tizedesjegyek nélkül)
- Kettős (lebegőpontos számok, azaz tizedesjegyek tárolására szolgál)
A VBA belső adattípusainak teljes listája itt található: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Tartományobjektum -változók
Lehetőség van olyan változók létrehozására is, amelyek a tartomány -objektumokra hivatkoznak. Ez akkor hasznos, ha több helyen szeretne hivatkozni egy adott tartományra a kódban - így ha módosítani kell a tartományt, akkor csak egy helyen kell módosítania.
Amikor létrehoz egy Range objektumváltozót, akkor azt egy „Range” példányra kell állítania. Például:
Dim rMyRange mint tartománykészlet rMyRange = Tartomány („A1: A10; D1: J10”)
Ha a „Set” utasítást kihagyja a Range változó hozzárendelésekor, akkor hiba lép fel.
Hurok
A hurkok olyan blokkok, amelyek bizonyos számú alkalommal megismétlik a bennük lévő kódot. Hasznosak, ha csökkentik az írandó kód mennyiségét, és lehetővé teszik egy kódrészlet írását, amely ugyanazokat a műveleteket hajtja végre sok különböző kapcsolódó elemen.
For-Next
A For-Next blokk egy ciklus, amely bizonyos számú alkalommal ismétlődik. Egy változót használ iterátor hogy hányszor futott, és ez az iterátor változó használható a cikluson belül. Ezáltal a For-Next ciklusok nagyon hasznosak a cellákban vagy tömbökben való iteráláshoz.
Íme egy példa, amely az 1–100. Sorok 1. oszlopában lévő cellák között lapoz, és értékeiket az iterátor változó értékére állítja be:
Dim i Ameddig i = 1 - 100 sejt (i, 1). Érték = i Következő i
A „For i = 1 to 100” sor azt jelenti, hogy a ciklus 1 -től kezdődik és 100 után ér véget. Beállíthat tetszőleges kezdő és befejező számokat; ezekhez a számokhoz változókat is használhat.
Alapértelmezés szerint a For-Next hurkok 1-gyel számolnak. Ha más számmal szeretne számolni, akkor a cikket explicit módon írhatja Lépés kikötés:
I = 5 -től 100 -ig 5. lépés
Ez a ciklus 5 -kor kezdődik, majd minden egyes ciklus ismétléskor 5 -öt ad az „i” -hez (tehát az „i” 10 lesz a második ismétlésnél, 15 a harmadiknál stb.).
Használata Lépés, a ciklusokat visszafelé is beállíthatja:
I = 100 -tól 1 -ig 1. lépés
Te is fészek For-Next hurkok. Minden blokkhoz saját változó kell, hogy számoljon, de ezeket a változókat bárhol használhatja. Íme egy példa arra, hogy ez mennyire hasznos az Excel VBA -ban:
Dim i Olyan hosszú, j Olyan hosszú For i = 1 to 100 For j = 1 to 100 Cells (i, j). Value = i * j Next j Next i
Ez lehetővé teszi a sorok és az oszlopok közötti hurkolást.
FIGYELEM: bár ez megengedett, SOHA ne módosítsa az iterátor változót a For-Next mondaton belül, mivel ezt az iterátort használja a ciklus követésére. Az iterátor módosítása végtelen ciklushoz vezethet, és leállíthatja a makrót. Például:
I = 1 -től 100 -ig i = 1 Következő i
Ebben a ciklusban az „én” soha nem éri el a 2 -t, mielőtt visszaállítja az 1 -et, és a ciklus örökre megismétlődik.
Az egyes
Az egyes blokkok nagyon hasonlítanak a For-Next blokkokhoz, kivéve, ha nem számlálóval határozzák meg, hányszor ciklusoznak. Ehelyett a For-Every blokk objektumok „gyűjteményét” veszi fel (például egy sejttartományt), és annyiszor fut, ahány objektum van a gyűjteményben.
Íme egy példa:
Dim r Mint tartomány minden r tartományban ("A15: J54") Ha r.Érték> 0 Akkor r.Font.Bold = True End If Next r
Figyelje meg az „r” Range objektumváltozó használatát. Ez a For -Every ciklusban használt iterátorváltozó - a cikluson keresztül minden alkalommal az „r” hivatkozást kap a tartomány következő cellájára.
Az Excel VBA For-Every hurkok használatának előnye, hogy a tartomány összes celláját ciklusok beágyazása nélkül hurkolhatja végig. Ez hasznos lehet, ha át kell tekintenie az összes cellát egy komplex tartományban, mint például Tartomány („A1: D12, J13, M1: Y12”).
A For-Every ciklusok egyik hátránya, hogy nincs szabályozása a cellák feldolgozásának sorrendje felett. Habár gyakorlatban Az Excel sorrendben végigmegy a cellákon, elméletben teljesen véletlenszerű sorrendben tudta feldolgozni a sejteket. Ha a cellákat egy adott sorrendben kell feldolgoznia, akkor inkább a For-Next ciklusokat használja.
Do-Loop
Míg a For-Next blokkok számlálók segítségével tudják, mikor kell megállni, Do-Loop blokkok addig futnak, amíg egy feltétel teljesül. Ehhez használjon egy Amíg záradék a blokk elején vagy végén, amely teszteli az állapotot, és a ciklus leállását eredményezi, amikor ez a feltétel teljesül.
Példa:
Dim str As String str = "Buffalo" Do Before str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Érték = str
Ebben a ciklusban a „Bivaly” a cikluson keresztül minden alkalommal „str” -ba van kapcsolva, amíg meg nem felel a várt mondatnak. Ebben az esetben a tesztet a ciklus elején hajtják végre - ha az 'str' már a várt mondat volt (ami nem azért van, mert nem így kezdtük, de ha), akkor a ciklus nem is futna .
A ciklus legalább egyszer futtathatóvá válik azáltal, hogy a végére mozgatja a záradékot, így:
Végezze el a str = str & "" & "Buffalo" hurkot, amíg str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
A makró bármelyik verzióját használhatja.
FIGYELEM: végtelen ciklusokat okozhat Do-Loop blokkkal, ha a Feltétel soha nem teljesül. Mindig írja be a kódot, hogy az ilyen típusú hurkok használatakor a feltétel biztosan teljesüljön.
Mi a következő lépés?
Ha már megértette az alapokat, miért nem próbálja meg megtanulni néhány fejlettebb technikát? Oktatóanyagunk a https://easyexcel.net/excel/learn-vba-tutorial/ webhelyen mindenre épít, amit itt tanult, és kibővíti készségeit az Eseményekkel, a UserFormokkal, a kódoptimalizálással és még sok mással!
