Kako posneti makro v Excelu - vodnik po korakih

Tudi če ste začetnik v svetu Excela VBA, lahko preprosto posnamete makro in avtomatizirate nekaj svojega dela.

V tem podrobnem priročniku bom zajel vse, kar morate vedeti, da začnete s snemanjem in uporabo makrov v Excelu.

Če vas zanima učenje VBA na enostaven način, si oglejte moj Spletno usposabljanje Excel VBA.

Kaj je makro?

Če ste novinec v VBA, vam najprej povem, kaj je makro - navsezadnje bom ta izraz še naprej uporabljal v celotni vadnici.

Makro je koda, napisana v VBA (Visual Basic for Applications), ki vam omogoča, da zaženete kos kode, kadar koli se izvede.

Pogosto boste ugotovili, da ljudje (vključno z mano) označujejo kodo VBA kot makro - ne glede na to, ali je ustvarjen z uporabo snemalnika makrov ali je bil napisan ročno.

Ko posnamete makro, Excel pozorno spremlja korake, ki jih izvajate, in jih zabeleži v jeziku, ki ga razume - to je VBA.

Ker je Excel res dober zapiskovalec, ustvari zelo podrobno kodo (kot bomo videli kasneje v tem vodiču).

Zdaj, ko ustavite snemanje, shranite makro in ga zaženete, se Excel preprosto vrne na kodo VBA, ki jo je ustvaril, in sledi popolnoma istim korakom.

To pomeni, da tudi če ne veste nič o VBA, lahko nekatere naloge avtomatizirate tako, da Excelu dovolite, da enkrat zabeleži vaše korake, nato pa jih znova uporabite.

Zdaj pa se potopimo in poglejmo, kako posneti makro v Excelu.

Pridobivanje zavihka za razvijalce na traku

Prvi korak pri snemanju makra je, da na trak odprete kartico Razvijalec.

Če na traku že vidite zavihek razvijalca, pojdite na naslednji razdelek, sicer sledite spodnjim korakom:

• Z desno miškino tipko kliknite kateri koli obstoječi zavihek na traku in kliknite možnost »Prilagodi trak«. Odprlo se bo pogovorno okno Možnosti Excel.
• V pogovornem oknu Možnosti Excela boste imeli možnosti Prilagodi trak. Na desni strani v podoknu Glavni zavihki preverite možnost Razvijalec.
• Kliknite V redu.

Zgornji koraki bi omogočili zavihek Razvijalec na območju traku.

Snemanje makra v Excelu

Zdaj, ko imamo vse na mestu, se naučimo, kako posneti makro v Excelu.

Posnemimo zelo preprost makro - tisti, ki izbere celico in vanj vnese besedilo »Excel«. Med snemanjem tega makra uporabljam besedilo "Excel", vendar vnesite svoje ime ali katero koli drugo besedilo, ki vam je všeč.

Tu so koraki za snemanje tega makra:

1. Kliknite zavihek Razvijalec.
2. V skupini Koda kliknite gumb Makro. S tem se odpre pogovorno okno 'Record Macro'.
3. V pogovornem oknu Record Macro vnesite ime svojega makra. Uporabljam ime EnterText. Pri poimenovanju makra morate upoštevati nekatere pogoje poimenovanja. Na primer, vmes ne morete uporabljati presledkov. Ponavadi raje obdržim imena makrov kot eno besedo z različnimi deli z veliko začetnico. Podčrtaj lahko uporabite tudi za ločevanje dveh besed - na primer Enter_Text.
4. (Izbirni korak) Če želite, lahko dodelite bližnjico na tipkovnici. V tem primeru bomo uporabili bližnjico Control + Shift + N. Ne pozabite, da bi bližnjica, ki jo tukaj dodelite, preglasila vse obstoječe bližnjice v vašem delovnem zvezku. Če na primer dodelite bližnjico Control + S, tega ne boste mogli uporabiti za shranjevanje delovnega zvezka (namesto tega bo vsakič, ko ga uporabite, izvedel makro).
5. Pri možnosti »Shrani makro v« izberite »Ta delovni zvezek«. Ta korak zagotavlja, da je makro del delovnega zvezka. Na voljo bo, ko ga shranite in znova odprete, ali pa če ga delite z nekom.
6. (Izbirni korak) Vnesite opis. Običajno tega ne počnem, če pa ste zelo organizirani, boste morda želeli dodati, za kaj gre za makro.
7. Kliknite V redu. Takoj, ko kliknete V redu, se začnejo snemati vaša dejanja v Excelu. Na zavihku Razvijalec lahko vidite gumb »Ustavi snemanje«, ki označuje, da je snemanje makra v teku.
8. Izberite celico A2.
9. Vnesite besedilo Excel (ali pa uporabite svoje ime).
10. Pritisnite tipko Enter. S tem boste izbrali celico A3.
11. Kliknite gumb Ustavi snemanje zavihek Razvijalec.

Čestitamo!

Pravkar ste posneli svoj prvi makro v Excelu. Niste več makro devica.

Čeprav makro ne naredi nič koristnega, bo služil svojemu namenu pri razlagi delovanja makro snemalnika v Excelu.

Zdaj pa pojdimo naprej in preizkusimo ta makro.

Za preizkus makra sledite spodnjim korakom:

1. Izbrišite besedilo v celici A2. S tem preizkusimo, ali makro vstavi besedilo v celico A2 ali ne.
2. Izberite katero koli celico - razen A2. To je za preverjanje, ali makro izbere celico A2 ali ne.
3. Kliknite zavihek Razvijalec.
4. V skupini Koda kliknite gumb Makri.
5. V pogovornem oknu Makro kliknite Ime makra - EnterText.
6. Kliknite gumb Zaženi.

Opazili boste, da takoj, ko kliknete gumb Zaženi, se v celico A2 vstavi besedilo »Excel« in izbere celica A3.

Zdaj se lahko vse to zgodi v delčku sekunde, v resnici pa je makro - tako kot poslušni vilenjak - sledil natančnim korakom, ki ste jih pokazali med snemanjem makra.

Tako makro najprej izbere celico A2, nato vnese besedilo Excel v njem in nato izbere celico A3.

Opomba: Makro lahko zaženete tudi z bližnjico na tipkovnici Control + Shift + N (držite tipki Control in Shift in nato pritisnite tipko N). To je ista bližnjica na tipkovnici, ki smo jo pri snemanju dodelili makru.

Kaj snemanje makra počne v zaledju

Zdaj pa pojdimo na zaledje programa Excel - urejevalnik VB - in poglejmo, kaj snemanje makra v resnici počne.

Tukaj je nekaj korakov za odpiranje urejevalnika VB v Excelu:

1. Kliknite zavihek Razvijalec.
2. V skupini Koda kliknite gumb Visual Basic.

Ali pa uporabite bližnjico na tipkovnici - ALT + F11 (držite tipko ALT in pritisnite F11), namesto zgornjih dveh korakov. Ta bližnjica odpre tudi isti urejevalnik VB.

Zdaj, če prvič vidite urejevalnik VB, ne bodite preobremenjeni.

Naj vas na hitro seznanim z anatomijo urejevalnika VB.

• Menijska vrstica: Tu imate vse možnosti urejevalnika VB. Upoštevajte to kot trak VBA. Vsebuje ukaze, ki jih lahko uporabite med delom z urejevalnikom VB.
• Orodna vrstica - To je kot orodna vrstica za hitri dostop urejevalnika VB. Na voljo je z nekaj uporabnimi možnostmi, vanj pa lahko dodate več možnosti. Njegova prednost je, da je možnost v orodni vrstici oddaljena le en klik.
• Okno raziskovalca projekta - Tukaj Excel navede vse delovne zvezke in vse predmete v vsakem delovnem zvezku. Če imamo na primer delovni zvezek s 3 delovnimi listi, bi se prikazal v Raziskovalcu projektov. Tu je nekaj dodatnih predmetov, kot so moduli, uporabniški obrazci in moduli razredov.
• Okno za kodo - Tu se zabeleži ali zapiše koda VBA. Za vsak predmet, ki je naveden v Raziskovalcu projektov, je okno za kodo - na primer delovni listi, delovni zvezki, moduli itd. V nadaljevanju te vadnice bomo videli, da posneti makro preide v kodno okno modula.
• Okno lastnosti - V tem oknu si lahko ogledate lastnosti vsakega predmeta. To okno pogosto uporabljam za poimenovanje predmetov ali spreminjanje skritih lastnosti. Ko odprete urejevalnik VB, tega okna morda ne boste videli. Če želite to prikazati, kliknite zavihek pogled in izberite okno lastnosti.
• Takojšnje okno - Pri pisanju kode pogosto uporabljam neposredno okno. To je uporabno, če želite preizkusiti nekatere stavke ali med odpravljanjem napak. Morda ni privzeto viden in ga lahko prikažete tako, da kliknete zavihek Pogled in izberete možnost Takojšnje okno.

Ko smo posneli makro - EnterText, se je v urejevalniku VB zgodilo naslednje:

• Vstavljen je bil nov modul.
• Zapisan je bil makro z imenom, ki smo ga določili - EnterText
• Koda je bila zapisana v kodnem oknu modula.

Če torej dvokliknete modul (v tem primeru Modul 1), se prikaže okno s kodo, kot je prikazano spodaj.

Tu je koda, ki nam jo je dal makro snemalnik:

Sub EnterText () '' EnterText Macro '' Bližnjica na tipkovnici: Ctrl+Shift+N 'Obseg ("A2"). Izberite ActiveCell.FormulaR1C1 = "Excel" obseg ("A3"). Izberite End Sub

V VBA je vsaka vrstica, ki sledi ‘ (znak apostrofa) ni izveden. To je komentar, ki je zgolj informativne narave. Če odstranite prvih pet vrstic te kode, bo makro še vedno deloval po pričakovanjih.

Zdaj pa naj hitro opišem, kaj počne vsaka vrstica kode:

Koda se začne z Sub, ki ji sledi ime makra in prazne oklepaje. Sub je okrajšava za Podprogram. Vsaka podprogram (imenovan tudi postopek) v VBA se začne z Sub in konča s End Sub.

• Razpon (“A2”). Izberite - Ta vrstica izbere celico A2.
• ActiveCell.FormulaR1C1 = “Excel” - ta vrstica vnese besedilo Excel v aktivno celico. Ker smo za prvi korak izbrali A2, ta postane naša aktivna celica.
• Razpon (“A3”). Izberite - S tem izberete celico A3. To se zgodi, ko po vnosu besedila pritisnemo tipko Enter, kar je povzročilo izbiro celice A3.

Upam, da ste do sedaj že razumeli, kako posneti makro v Excelu.

Upoštevajte, da koda, ki jo je zapisal makro snemalnik, nikakor ni učinkovita.

Makro snemalnik včasih doda veliko kode, ki včasih ni potrebna. Toda to ne pomeni, da ni uporabno. Za nekoga, ki se uči VBA, je lahko makro snemalnik odličen način za analizo, kako stvari delujejo v VBA.

Absolutno Vs Relativno makro snemanje

Že veste za absolutne in relativne reference v Excelu … kajne?

Če ne - najprej preberite to vadnico o referencah.

Preberi? Gremo naprej.

V nadaljevanju tega razdelka bomo videli, kako snemati makre v absolutnih in relativnih referencah. Pred tem pa naj hitro povzamem razliko med absolutno in relativno referenco v VBA (če ste leni in niste prebrali povezave, ki sem jo dal pred nekaj vrsticami):

Če za snemanje makra uporabite absolutno referenčno možnost, se bo koda VBA vedno nanašala na iste celice, ki ste jih uporabili. Če na primer izberete celico A2, vnesite besedilo Excel in vsakič pritisnite Enter - ne glede na to, kje ste na delovnem listu in ne glede na to, katera celica je izbrana, bo vaša koda najprej izbrala celico A2, vnesla besedilo Excel in nato pojdite v celico A3.

Če za snemanje makra uporabite možnost relativne reference, VBA sklicev na celice ne bi trdo kodiral. Namesto tega bi se v primerjavi z aktivno celico osredotočil na gibanje. Recimo, da imate že izbrano celico A1 in začnete snemati makro v relativnem referenčnem načinu.

Zdaj izberete celico A2, vnesete besedilo Excel in pritisnete tipko enter. Ko zaženete ta makro, se ne bo vrnil v celico A2, temveč se bo premaknil glede na aktivno celico. Če je na primer izbrana celica K3, se bo premaknila v K4, vnesla besedilo Excel in nato končno izbrala celico K5.

Zdaj pa vam povem, kako posneti makro v načinu relativnih referenc:

1. Izberite celico A1.
2. Kliknite zavihek Razvijalec.
3. V skupini Koda kliknite gumb »Uporabi relativne reference«. Zasvetil bo, kar pomeni, da je vklopljen.
4. Kliknite gumb Posnemi makro.
5. V pogovornem oknu Record Macro vnesite ime svojega makra. Uporabljam ime EnterTextRelRef.
6. V možnosti Shrani makro v preverite, ali je izbran »Ta delovni zvezek«.
7. Kliknite V redu.
8. Izberite celico A2.
9. Vnesite besedilo Excel (ali pa vnesete svoje ime).
10. Pritisnite tipko Enter. S tem se bo kazalec pomaknil v celico A3.
11. Kliknite gumb Ustavi snemanje zavihek Razvijalec.

To bi zabeležilo makro v relativnem referenčnem načinu.

Zdaj naredi to.

1. Izberite katero koli celico (razen A1).
2. Kliknite zavihek Razvijalec.
3. V skupini Koda kliknite gumb Makri.
4. V pogovornem oknu Makro kliknite Ime makra - EnterTextRelRef.
5. Kliknite gumb Zaženi.

Kar se zgodi? Ali se je kazalec vrnil v celico A3.

Ne bi - ker ste makro posneli v relativnem referenčnem načinu. Tako bi se kazalec premaknil glede na aktivno celico. Če na primer to storite, ko je izbrana celica K3, bo vnesla besedilo Excel je celica K4 in na koncu izbrala celico K5.

Tu je koda, ki se zabeleži v ozadju (okno kode modula VB Editor):

Sub EnterTextRelRef () '' EnterTextRelRef Macro '' ActiveCell.Offset (1, 0) .Range ("A1"). Izberite ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset (1, 0) .Range ("A1"). Izberite End Sub

Upoštevajte, da se celotna koda nikjer ne nanaša na celice K3 ali K4. Namesto tega uporablja Activecell za sklicevanje na izbrano celico in Offset za premikanje glede na aktivno celico.

Ne skrbite za del obsega ("A1"), ki ga ima koda. To je ena od tistih nepotrebnih kod, ki jih doda snemalnik makrov, ki nima nobenega namena in jih je mogoče odstraniti. Koda bi brez nje delovala v redu.

Gumb »Uporabi relativno referenco« na zavihku Razvijalec je preklopni gumb. Lahko ga izklopite (in preklopite nazaj na absolutno referenco) s klikom nanj.

Kaj makro snemalnik ne zmore

Makro snemalnik vam odlično sledi v Excelu in beleži vaše natančne korake, vendar vam lahko spodleti, ko potrebujete več.

• Kode ne morete izvesti brez izbire predmeta. Če želite, da snemalnik makrov preide na naslednji delovni list in označi vse zapolnjene celice v stolpcu A, ne da bi zapustil trenutni delovni list, potem tega ne bo mogel narediti. Če vas prosim, da to storite, tudi vi tega ne boste mogli storiti (ne da bi zapustili trenutni list). In če tega ne zmorete sami, kako bo snemalnik makrov zajel vaša dejanja. V takih primerih morate ročno ustvariti/urediti kodo.
• S snemalnikom makrov ne morete ustvariti funkcije po meri. Z VBA lahko ustvarite funkcije po meri, ki jih lahko uporabite na delovnem listu kot običajne funkcije. To lahko ustvarite tako, da kodo napišete ročno.
• Kode ne morete izvajati na podlagi dogodkov: V VBA lahko za zagon kode, povezane s tem dogodkom, uporabite številne dogodke - na primer odpiranje delovnega zvezka, dodajanje delovnega lista, dvoklik na celico itd. Za to lahko uporabite snemalnik makrov.
• Z snemalnikom makrov ne morete ustvariti zank. Ko ročno vnesete kodo, lahko povečate moč zank v VBA (na primer Za naprej, Za vsako naslednjo, Naredi med, Naredi do). Ko posnamete makro, tega ne morete storiti.
• Ne morete analizirati pogojev: Pogoje v kodi lahko preverite s snemalnikom makrov. Če kodo VBA napišete ročno, lahko z izjavami IF Then Else analizirate stanje in zaženete kodo, če je res (ali drugo kodo, če je napačna).
• Argumentov v postopku makra ne morete posredovati: Ko posnamete makro, nikoli ne bo imel argumentov. Podprogram lahko sprejme vhodne argumente, ki jih je mogoče uporabiti v makru za izvajanje naloge. Med snemanjem makra tega ni mogoče storiti, saj so posneti makri neodvisni in niso povezani z nobenim obstoječim makrom.

Razširitve datotek, ki omogočajo makre

Ko snemate makro ali ročno pišete kodo VBA v Excelu, morate datoteko shraniti z razširitvijo datoteke, ki podpira makro (.xlsm).

Pred Excelom 2007 je zadostovala ena sama oblika datoteke - .xls.

Toda od leta 2007 je bil .xlsx uveden kot standardna razširitev datoteke. Datoteke, shranjene kot .xlsx, ne morejo vsebovati makra. Torej, če imate datoteko z razširitvijo .xlsx in posnamete/napišete makro in ga shranite, vas bo opozoril, da ga shranite v obliki, ki omogoča makro, in prikazal pogovorno okno (kot je prikazano spodaj):

Če izberete Ne, lahko Excel shrani v obliki, ki podpira makre. Če pa kliknete Da, Excel samodejno odstrani vso kodo iz vašega delovnega zvezka in jo shrani kot delovni zvezek .xlsx.

Če imate torej v delovnem zvezku makro, ga morate shraniti v obliki .xlsm, če želite obdržati ta makro.

Različni načini za zagon makra v Excelu

Doslej smo videli le en način za zagon makra v Excelu - to je uporaba pogovornega okna Makro.

Vendar obstaja več načinov za zagon makrov.

1. Zaženite makro s trakom (zavihek za razvijalce)
2. Uporaba bližnjice na tipkovnici (ki jo morate dodeliti)
3. Dodelite makro obliki
4. Dodelite makro gumbu
5. Zaženite makro iz urejevalnika VB

Zaključek - Ko se zatakne, posnemite makro

Omenil sem že, da je snemalnik makrov uporabno orodje za vse, ki delajo z VBA v Excelu.

Ko nekajkrat uporabite snemalnik makrov, boste opazili, da izpljune veliko nepotrebne kode. Vendar je še vedno uporaben in vam daje nekaj idej, kje začeti. Če vas na primer prosim, da filtrirate stolpec celic z uporabo VBA in nimate pojma, kakšna je skladnja, lahko hitro posnamete makro in preverite kodo.

Makro snemalnik je nepogrešljivo orodje in tudi po letih izkušenj s kodiranjem VBA pod mojim pasom se pogosto zatečem k makro snemalniku.