Kako snimiti makronaredbu u Excelu - Vodič korak po korak

Čak i ako ste tek početnik u svijetu programa Excel VBA, možete jednostavno snimiti makro i automatizirati neki svoj rad.

U ovom detaljnom vodiču pokriti ću sve što trebate znati za početak snimanja i korištenja makronaredbi u Excelu.

Ako ste zainteresirani za učenje VBA na jednostavan način, provjerite moj Mrežna Excel VBA obuka.

Što je makro?

Ako ste tek početnik u VBA -u, prvo ću vam reći što je makro - uostalom, nastavit ću koristiti ovaj izraz u cijelom vodiču.

Makro je kôd napisan u VBA (Visual Basic za aplikacije) koji vam omogućuje da pokrenete komad koda kad god se izvrši.

Često ćete vidjeti da ljudi (uključujući i mene) nazivaju VBA kôd kao makronaredbu - bilo da je generirana pomoću snimača makroa ili je napisana ručno.

Kada snimate makronaredbu, Excel pomno prati korake koje poduzimate i bilježi ih na jeziku koji razumije - a to je VBA.

A budući da je Excel zaista dobar bilježnik, stvara vrlo detaljan kod (kao što ćemo vidjeti kasnije u ovom vodiču).

Sada, kada zaustavite snimanje, spremite makronaredbu i pokrenete je, Excel se jednostavno vraća na VBA kôd koji je generirao i slijedi potpuno iste korake.

To znači da čak i ako ne znate ništa o VBA -i, možete automatizirati neke zadatke samo dopuštajući Excelu da jednom zabilježi vaše korake, a zatim ih kasnije ponovno koristiti.

Hajdemo sada zaroniti i vidjeti kako snimiti makronaredbu u Excelu.

Dobivanje kartice Developer na vrpci

Prvi korak za snimanje makronaredbe je unos kartice Developer u vrpcu.

Ako već vidite karticu razvojnog programera na vrpci, prijeđite na sljedeći odjeljak, u suprotnom slijedite korake u nastavku:

• Desnom tipkom miša kliknite bilo koju od postojećih kartica na vrpci i kliknite opciju "Prilagodi vrpcu". Otvorit će se dijaloški okvir Mogućnosti programa Excel.
• U dijaloškom okviru Opcije programa Excel imat ćete opcije Prilagodi vrpcu. S desne strane, u oknu Glavne kartice, provjerite opciju Developer.
• Pritisnite U redu.

Gore navedeni koraci učinili bi karticu Developer dostupnom u području vrpce.

Snimanje makronaredbi u Excelu

Sada kada imamo sve na mjestu, naučimo kako snimiti makronaredbu u Excelu.

Snimimo vrlo jednostavan makro - onaj koji odabire ćeliju i u nju unosi tekst 'Excel'. Prilikom snimanja ove makronaredbe koristim tekst ‘Excel’, ali slobodno unesite svoje ime ili bilo koji drugi tekst koji vam se sviđa.

Evo koraka za snimanje ove makronaredbe:

1. Kliknite karticu Developer.
2. U grupi Kôd kliknite gumb Makro. Ovo će otvoriti dijaloški okvir "Snimanje makronaredbi".
3. U dijaloški okvir Snimanje makronaredbe unesite naziv svoje makronaredbe. Koristim ime EnterText. Postoje neki uvjeti imenovanja koje morate slijediti pri imenovanju makronaredbe. Na primjer, ne možete koristiti razmake između njih. Obično radije držim svoje makro nazive kao jednu riječ, s različitim dijelovima s velikim početnim slovom. Također možete koristiti donju crtu za odvajanje dvije riječi - poput Enter_Text.
4. (Opcijski korak) Ako želite, možete dodijeliti tipkovnički prečac. U ovom slučaju koristit ćemo prečac Control + Shift + N. Imajte na umu da će prečac koji ovdje dodijelite nadjačati sve postojeće prečace u vašoj radnoj knjizi. Na primjer, ako dodijelite prečac Control + S, nećete ga moći koristiti za spremanje radne knjige (umjesto toga, svaki put kada je upotrijebite, ona će izvršiti makronaredbu).
5. U opciji "Spremi makro u" provjerite je li odabrana "Ova radna knjiga". Ovaj korak osigurava da je makronaredba dio radne knjige. Bit će tu kad ga spremite i ponovno otvorite, pa čak i ako ga podijelite s nekim.
6. (Opcijski korak) Unesite opis. Obično to ne radim, ali ako ste izuzetno organizirani, možda biste htjeli dodati o čemu se radi makronaredba.
7. Pritisnite U redu. Čim pritisnete U redu, počinje bilježiti vaše radnje u Excelu. Na kartici Razvojni programer možete vidjeti gumb ‘Zaustavi snimanje’ koji označava da je snimanje makronaredbi u tijeku.
8. Odaberite ćeliju A2.
9. Unesite tekst Excel (ili možete koristiti svoje ime).
10. Pritisnite tipku Enter. Time će se odabrati ćelija A3.
11. Pritisnite gumb Zaustavi snimanje na kartici Razvojni programer.

Čestitamo!

Upravo ste snimili svoj prvi makro u Excelu. Više nisi makro djevica.

Iako makronaredba ne čini ništa korisno, poslužit će svojoj svrsi u objašnjavanju kako makro snimač radi u Excelu.

Idemo sada isprobati ovu makronaredbu.

Slijedite donje korake za testiranje makronaredbe:

1. Izbrišite tekst u ćeliji A2. Ovo služi za provjeru umetne li makronaredba tekst u ćeliju A2 ili ne.
2. Odaberite bilo koju ćeliju - osim A2. Ovo služi za provjeru odabire li makronaredba ćeliju A2 ili ne.
3. Kliknite karticu Developer.
4. U grupi Kôd kliknite gumb Makronaredbe.
5. U dijaloškom okviru Makro kliknite makro Naziv - EnterText.
6. Pritisnite gumb Pokreni.

Primijetit ćete da čim pritisnete gumb Pokreni, tekst 'Excel' umeće se u ćeliju A2 i odabire ćeliju A3.

Ovo se može dogoditi u djeliću sekunde, ali u stvarnosti je makro - baš poput poslušnog vilenjaka - slijedio točno one korake koje ste pokazali tijekom snimanja makronaredbe.

Dakle, makronaredba prvo odabire ćeliju A2, a zatim unosi tekst Excel u njemu, a zatim odabire ćeliju A3.

Napomena: Makro možete pokrenuti i pomoću tipkovnog prečaca Control + Shift + N (držite tipke Control i Shift, a zatim pritisnite tipku N). Ovo je isti tipkovnički prečac koji smo dodijelili makronaredbi prilikom snimanja.

Što Snimanje makronaredbi radi u pozadini

Idemo sada na pozadinu programa Excel - VB Editor - i vidjeti što snimanje makronaredbe zapravo čini.

Evo koraka za otvaranje VB uređivača u Excelu:

1. Kliknite karticu Developer.
2. U grupi Kôd kliknite gumb Visual Basic.

Ili možete koristiti tipkovnički prečac - ALT + F11 (držite tipku ALT i pritisnite F11), umjesto gornja dva koraka. Ovaj prečac također otvara isti VB Editor.

Sada, ako prvi put vidite VB Editor, nemojte se opterećivati.

Dopustite mi da vas brzo upoznam s anatomijom VB Editor -a.

• Traka izbornika: Ovdje imate sve mogućnosti VB Editor -a. Smatrajte to vrpcom VBA -e. Sadrži naredbe koje možete koristiti tijekom rada s VB Editor -om.
• Alatna traka - Ovo je poput alatne trake za brzi pristup uređivača VB. Dolazi s nekim korisnim opcijama, a možete mu dodati još opcija. Njegova je prednost to što je opcija na alatnoj traci udaljena samo jedan klik.
• Prozor istraživača projekta - Ovdje Excel navodi sve radne knjige i sve objekte u svakoj radnoj knjizi. Na primjer, ako imamo radnu knjigu s 3 radna lista, ona će se pojaviti u Project Exploreru. Ovdje postoje neki dodatni objekti kao što su moduli, korisnički obrasci i moduli klase.
• Prozor koda - Ovdje se snima ili zapisuje VBA kôd. Za svaki objekt naveden u Istraživaču projekata postoji prozor koda - poput radnih listova, radnih knjiga, modula itd. Vidjet ćemo kasnije u ovom vodiču da snimljena makro ide u kodni prozor modula.
• Prozor svojstava - U ovom prozoru možete vidjeti svojstva svakog objekta. Često koristim ovaj prozor za imenovanje objekata ili promjenu skrivenih svojstava. Ovaj prozor možda nećete vidjeti kada otvorite VB editor. Da biste to prikazali, kliknite karticu Prikaz i odaberite Svojstva prozor.
• Neposredni prozor - Često koristim neposredni prozor dok pišem kôd. Korisno je kada želite testirati neke izraze ili tijekom ispravljanja pogrešaka. Možda prema zadanim postavkama nije vidljiv, a možete ga prikazati klikom na karticu Pogled i odabirom opcije Neposredni prozor.

Kad smo snimili makro - EnterText, sljedeće stvari su se dogodile u VB Editoru:

• Umetnut je novi modul.
• Snimljena je makronaredba s imenom koje smo naveli - EnterText
• Kôd je napisan u kodnom prozoru modula.

Dakle, ako dvaput kliknete na modul (u ovom slučaju Modul 1), pojavit će se prozor koda kao što je prikazano u nastavku.

Evo koda koji nam je dao makro snimač:

Sub EnterText () '' EnterText Macro '' Tipkovnički prečac: Ctrl+Shift+N 'Raspon ("A2"). Odaberite ActiveCell.FormulaR1C1 = Raspon "Excel" ("A3"). Odaberite End Sub

U VBA -u svaka linija koja slijedi ‘ (znak apostrofa) se ne izvršava. To je komentar koji je samo informativnog karaktera. Ako uklonite prvih pet redaka ovog koda, makro će i dalje raditi prema očekivanjima.

Dopustite mi da na brzinu obuhvatim ono što svaki red koda radi:

Kôd počinje s Sub, iza kojeg slijedi naziv makronaredbe i prazne zagrade. Sub je kratica za Subroutine. Svaka podrutina (koja se također naziva procedura) u VBA počinje s Sub i završava s End Sub.

• Raspon ("A2"). Odaberite - Ova linija odabire ćeliju A2.
• ActiveCell.FormulaR1C1 = “Excel” - ovaj redak unosi tekst Excel u aktivnu ćeliju. Budući da smo kao prvi korak odabrali A2, on postaje naša aktivna ćelija.
• Raspon (“A3”). Odaberite - Time se odabire ćelija A3. To se događa kada pritisnemo tipku Enter nakon unosa teksta, što je rezultiralo odabirom ćelije A3.

Nadam se da ste do sada već razumjeli kako snimiti makro u Excelu.

Imajte na umu da kôd koji je zapisao makro snimač nikako nije učinkovit kod.

Makro snimač ponekad dodaje mnogo nečistoća kodu što ponekad nije potrebno. Ali to ne znači da nije korisno. Za nekoga tko uči VBA, makro snimač može biti izvrstan način za analizu kako stvari funkcioniraju u VBA.

Apsolutno vs relativno makro snimanje

Već znate za apsolutne i relativne reference u Excelu … zar ne?

Ako ne - prvo pročitajte ovaj vodič o referencama.

Čitati? Idemo dalje.

Kasnije ćemo u ovom odjeljku vidjeti kako zapisati makroe u apsolutnim i relativnim referencama. No prije toga, dopustite mi da brzo sažmem razliku između apsolutne i relativne reference u VBA -i (u slučaju da ste se ulijenili i niste pročitali vezu koju sam dao prije nekoliko redaka):

Ako za snimanje makronaredbi koristite opciju apsolutne reference, VBA kôd uvijek bi se odnosio na iste ćelije koje ste koristili. Na primjer, ako odaberete ćeliju A2, unesite tekst Excel i svaki put pritisnite Enter - bez obzira na to gdje se nalazite na radnom listu i bez obzira na to koja je ćelija odabrana, vaš će kôd najprije odabrati ćeliju A2, unijeti tekst Excel i zatim prijeđite u ćeliju A3.

Ako za snimanje makronaredbi koristite opciju relativne reference, VBA neće teško kodirati reference ćelija. Umjesto toga, usredotočio bi se na kretanje u usporedbi s aktivnom stanicom. Na primjer, pretpostavimo da ste već odabrali ćeliju A1 i počeli snimati makro u relativnom referentnom načinu.

Sada odaberete ćeliju A2, unesite tekst Excel i pritisnite tipku enter. Sada, kada pokrenete ovu makronaredbu, ona se neće vratiti u ćeliju A2, već će se pomaknuti u odnosu na aktivnu ćeliju. Na primjer, ako je odabrana ćelija K3, premjestit će se u K4, unijeti tekst Excel, a zatim konačno odabrati ćeliju K5.

Sada ću vam reći kako snimiti makro u načinu relativnih referenci:

1. Odaberite ćeliju A1.
2. Kliknite karticu Developer.
3. U grupi Kôd kliknite gumb "Koristi relativne reference". Zasvijetlit će, što znači da je uključeno.
4. Pritisnite gumb Snimi makro.
5. U dijaloški okvir Snimanje makronaredbe unesite naziv svoje makronaredbe. Koristim ime EnterTextRelRef.
6. U opciji Spremi makronaredbu u provjerite je li odabrana "Ova radna knjiga".
7. Pritisnite U redu.
8. Odaberite ćeliju A2.
9. Unesite tekst Excel (ili možete unijeti svoje ime).
10. Pritisnite tipku Enter. Ovo će odvesti kursor u ćeliju A3.
11. Pritisnite gumb Zaustavi snimanje na kartici Razvojni programer.

Time bi se makro snimio u relativnom referentnom načinu.

Sada učini ovo.

1. Odaberite bilo koju ćeliju (osim A1).
2. Kliknite karticu Developer.
3. U grupi Kôd kliknite gumb Makronaredbe.
4. U dijaloškom okviru Makro kliknite naziv makronaredbe - EnterTextRelRef.
5. Pritisnite gumb Pokreni.

Što se događa? Je li se kursor vratio u ćeliju A3.

Ne bi - jer ste snimili makro u relativnom referentnom načinu. Tako bi se kursor pomaknuo u odnosu na aktivnu ćeliju. Na primjer, ako to učinite kada je odabrana ćelija K3, ona će unijeti tekst Excel je ćelija K4 i na kraju odabrati ćeliju K5.

Evo koda koji se bilježi u pozadini (prozor koda modula VB Editor):

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

Imajte na umu da se cijeli ovaj kod nigdje ne odnosi na ćelije K3 ili K4. Umjesto toga, koristi Activecell za upućivanje na odabranu ćeliju i Offset za pomicanje u odnosu na aktivnu ćeliju.

Ne brinite o dijelu raspona ("A1") koji kôd ima. To je jedan od onih nepotrebnih kodova koje makro snimač dodaje i koji nema svrhe i može se ukloniti. Kod bi bez njega dobro funkcionirao.

Gumb "Koristi relativnu referencu" na kartici Razvojni programer gumb je za prebacivanje. Možete ga isključiti (i vratiti se na apsolutnu referencu) klikom na njega.

Što makro snimač ne može učiniti

Makro snimač izvrsno vas prati u Excelu i bilježi vaše točne korake, ali može vas iznevjeriti kad vam zatreba da učinite više.

• Ne možete izvršiti kôd bez odabira objekta. Ako želite da makro snimač prijeđe na sljedeći radni list i istakne sve ispunjene ćelije u stupcu A, bez napuštanja trenutnog radnog lista, tada to neće moći učiniti. To je zato što ako vas zamolim da to učinite, čak ni vi to nećete moći učiniti (bez napuštanja trenutnog lista). A ako to ne možete učiniti sami, kako će makro snimač zabilježiti vaše radnje. U takvim slučajevima morate ručno otići i stvoriti/urediti kôd.
• Ne možete stvoriti prilagođenu funkciju pomoću snimača makronaredbi. S VBA -om možete stvoriti prilagođene funkcije koje možete koristiti na radnom listu kao redovite funkcije. To možete stvoriti ručnim pisanjem koda.
• Ne možete pokrenuti kodove na temelju događaja: U VBA možete koristiti mnoge događaje - poput otvaranja radne knjige, dodavanja radnog lista, dvostrukog klika na ćeliju itd. Za pokretanje koda povezanog s tim događajem. Za to možete koristiti makro snimač.
• Ne možete stvarati petlje pomoću snimača makronaredbi. Kada ručno unesete kôd, možete iskoristiti snagu petlji u VBA -i (poput For Next, For Every Next, Do While, Do till). Ali to ne možete učiniti kada snimate makronaredbu.
• Ne možete analizirati uvjete: Pomoću makro snimača možete provjeriti uvjete unutar koda. Ako ručno napišete VBA kôd, možete koristiti naredbe IF Then Else za analizu stanja i pokretanje koda ako je istinit (ili drugi kod ako je netočan).
• Ne možete proslijediti argumente u postupku makronaredbe: Kad snimite makro, on nikada neće imati argumente. Podprogram može uzeti ulazne argumente koji se mogu koristiti unutar makronaredbe za izvođenje zadatka. Tijekom snimanja makronaredbe to se ne može učiniti jer su snimljene makronaredbe neovisne i nisu povezane ni s jednom postojećom makronaredbom.

Proširenja datoteka s omogućenim makronaredbama

Kada snimate makronaredbu ili ručno pišete VBA kôd u Excelu, morate spremiti datoteku s nastavkom datoteke s omogućenim makroom (.xlsm).

Prije Excela 2007 postojao je jedan jedini format datoteke koji je bio dovoljan - .xls.

No, od 2007. nadalje, .xlsx je uveden kao standardno proširenje datoteke. Datoteke spremljene kao .xlsx ne mogu sadržavati makronaredbu. Dakle, ako imate datoteku s .xlsx ekstenzijom, a snimite/napišete makronaredbu i spremite je, upozorit će vas da je spremite u formatu s omogućenim makroima i prikazat će vam se dijaloški okvir (kao što je prikazano u nastavku):

Ako odaberete Ne, Excel vam omogućuje spremanje u formatu omogućenom za makroe. Ali ako kliknete Da, Excel će automatski ukloniti sav kôd iz vaše radne knjige i spremiti je kao .xlsx radnu knjigu.

Dakle, ako u radnoj knjizi imate makronaredbu, morate je spremiti u .xlsm formatu da biste zadržali tu makronaredbu.

Različiti načini pokretanja makronaredbi u Excelu

Do sada smo vidjeli samo jedan način pokretanja makronaredbi u Excelu - a to je korištenje dijaloškog okvira Makro.

No, postoji nekoliko načina na koje možete pokrenuti makronaredbe.

1. Pokretanje makronaredbe s vrpce (kartica za razvojne programere)
2. Korištenje prečaca na tipkovnici (koju morate dodijeliti)
3. Dodijelite makronaredbu obliku
4. Dodijelite makronaredbu gumbu
5. Pokrenite makro iz VB uređivača

Zaključak - Snimite makro kada zaglavite

Već sam spomenuo da je makro snimač koristan alat za svakoga tko radi s VBA -om u Excelu.

Dok nekoliko puta koristite makro snimač, primijetit ćete da izbacuje mnogo nepotrebnog koda. Međutim, i dalje je korisno i daje vam ideje o tome odakle započeti. Na primjer, ako vas zamolim da filtrirate stupac ćelija pomoću VBA, a nemate pojma koja je sintaksa, možete brzo snimiti makronaredbu i provjeriti kôd.

Makro rekorder nezamjenjiv je alat, pa čak i nakon godina iskustva u kodiranju VBA -a, često se obraćam za pomoć makro rekorderu.