Cum să înregistrați o macro în Excel - Un ghid pas cu pas

Chiar dacă sunteți un începător complet în lumea Excel VBA, puteți înregistra cu ușurință o macro și automatiza o parte din munca dvs.

În acest ghid detaliat, voi acoperi tot ce trebuie să știți pentru a începe să înregistrați și să utilizați macrocomenzi în Excel.

Dacă sunteți interesat să învățați VBA într-un mod ușor, consultați Instruire online VBA Excel.

Ce este un macro?

Dacă sunteți un începător al VBA, permiteți-mi să vă spun mai întâi ce este o macro - la urma urmei, voi continua să folosesc acest termen în întregul tutorial.

O macro este un cod scris în VBA (Visual Basic pentru aplicații) care vă permite să rulați o bucată de cod ori de câte ori este executat.

Adesea, veți găsi oameni (inclusiv eu) care se referă la un cod VBA ca macro - indiferent dacă este generat utilizând un înregistrator de macro sau a fost scris manual.

Când înregistrați o macrocomandă, Excel urmărește îndeaproape pașii pe care îi faceți și o notează într-o limbă pe care o înțelege - care este VBA.

Și din moment ce Excel este un utilizator foarte bun pentru a lua note, creează un cod foarte detaliat (așa cum vom vedea mai târziu în acest tutorial).

Acum, când opriți înregistrarea, salvați macrocomanda și o rulați, Excel pur și simplu revine la codul VBA pe care l-a generat și urmează exact aceiași pași.

Aceasta înseamnă că, chiar dacă nu știți nimic despre VBA, puteți automatiza unele sarcini doar lăsând Excel să înregistreze pașii dvs. o singură dată și apoi să le reutilizați mai târziu.

Acum să ne scufundăm și să vedem cum să înregistrăm o macrocomandă în Excel.

Obținerea filei pentru dezvoltatori în panglică

Primul pas pentru a înregistra o macro este să obțineți fila Dezvoltator în panglică.

Dacă puteți vedea deja fila dezvoltator în panglică, accesați secțiunea următoare, altfel urmați pașii de mai jos:

• Faceți clic dreapta pe oricare dintre filele existente în panglică și faceți clic pe opțiunea „Personalizați panglica”. Se va deschide caseta de dialog Opțiuni Excel.
• În caseta de dialog Opțiuni Excel, veți avea opțiunile Personalizați panglica. În dreapta, în panoul Filelor principale, bifați opțiunea Dezvoltator.
• Faceți clic pe OK.

Pașii de mai sus ar face fila Dezvoltator disponibilă în zona panglicii.

Înregistrarea unei macrocomenzi în Excel

Acum, că avem totul la dispoziție, să învățăm cum să înregistrăm o macro în Excel.

Să înregistrăm o macro foarte simplă - una care selectează o celulă și introduce textul „Excel” în ea. Folosesc textul „Excel” în timp ce înregistrez această macrocomandă, dar nu ezitați să vă introduceți numele sau orice alt text care vă place.

Iată pașii pentru înregistrarea acestei macrocomenzi:

1. Faceți clic pe fila Dezvoltator.
2. În grupul Cod, faceți clic pe butonul Macro. Aceasta va deschide caseta de dialog „Înregistrați macro”.
3. În caseta de dialog Înregistrare macro, introduceți un nume pentru macro. Folosesc numele EnterText. Există câteva condiții de denumire pe care trebuie să le urmați atunci când denumiți o macro. De exemplu, nu puteți utiliza spații între ele. De obicei prefer să-mi păstrez numele macro ca un singur cuvânt, cu diferite părți cu un prim alfabet cu majuscule. De asemenea, puteți utiliza subliniere pentru a separa două cuvinte - cum ar fi Enter_Text.
4. (Pas opțional) Dacă doriți, puteți atribui o comandă rapidă de la tastatură. În acest caz, vom utiliza comanda rapidă Control + Shift + N. Amintiți-vă că comanda rapidă pe care o atribuiți aici va înlocui orice scurtături existente în registrul dvs. de lucru. De exemplu, dacă atribuiți comanda rapidă Control + S, nu veți putea utiliza acest lucru pentru salvarea registrului de lucru (în schimb, de fiecare dată când îl utilizați, acesta va executa macro-ul).
5. În opțiunea „Magazin macro în”, asigurați-vă că este selectat „Acest registru de lucru”. Acest pas asigură faptul că macro-ul face parte din registrul de lucru. Va fi acolo când îl salvați și îl redeschideți din nou sau chiar dacă îl împărtășiți cu cineva.
6. (Pas opțional) Introduceți o descriere. De obicei nu fac asta, dar dacă sunteți extrem de organizat, poate doriți să adăugați despre ce este macro.
7. Faceți clic pe OK. De îndată ce faceți clic pe OK, începe să vă înregistreze acțiunile în Excel. Puteți vedea butonul „Opriți înregistrarea” în fila Dezvoltator, care indică faptul că înregistrarea macro este în desfășurare.
8. Selectați celula A2.
9. Introduceți textul excela (sau vă puteți folosi numele).
10. Apăsați tasta Enter. Aceasta va selecta celula A3.
11. Faceți clic pe butonul Oprire înregistrare fila Dezvoltator.

Felicitări!

Tocmai ați înregistrat prima dvs. macro în Excel. Nu mai ești macro virgin.

Deși macro-ul nu face nimic util, acesta își va servi scopul de a explica modul în care funcționează un macro recorder în Excel.

Acum să mergem mai departe și să testăm această macro.

Urmați pașii de mai jos pentru a testa macro-ul:

1. Ștergeți textul din celula A2. Aceasta este pentru a testa dacă macro-ul introduce textul în celula A2 sau nu.
2. Selectați orice celulă - alta decât A2. Aceasta este pentru a testa dacă macro-ul selectează celula A2 sau nu.
3. Faceți clic pe fila Dezvoltator.
4. În grupul Cod, faceți clic pe butonul Macro.
5. În caseta de dialog Macro, faceți clic pe numele macro - EnterText.
6. Faceți clic pe butonul Executare.

Veți observa că imediat ce faceți clic pe butonul Executare, textul „Excel” este introdus în celula A2 și celula A3 este selectată.

Acum, toate acestea se pot întâmpla într-o fracțiune de secundă, dar în realitate, macro - la fel ca un elf ascultător - a urmat pașii exacți pe care i-ai arătat-o ​​în timp ce înregistrați macro-ul.

Deci macro-ul selectează mai întâi celula A2, apoi introduce textul excela în ea și apoi selectează celula A3.

Notă: De asemenea, puteți rula macro utilizând comanda rapidă de la tastatură Control + Shift + N (țineți apăsate tastele Control și Shift și apoi apăsați tasta N). Aceasta este aceeași comandă rapidă de la tastatură pe care am atribuit-o macro-ului la înregistrare.

Ce face înregistrarea unei macro-uri în backend

Acum să mergem la backend-ul Excel - Editorul VB - și să vedem ce face cu adevărat înregistrarea unei macro.

Iată pașii pentru a deschide Editorul VB în Excel:

1. Faceți clic pe fila Dezvoltator.
2. În grupul Cod, faceți clic pe butonul Visual Basic.

Sau puteți utiliza comanda rapidă de la tastatură - ALT + F11 (țineți apăsată tasta ALT și apăsați F11), în locul celor doi pași de mai sus. Această comandă rapidă deschide, de asemenea, același editor VB.

Acum, dacă vedeți editorul VB pentru prima dată, nu vă lăsați copleșiți.

Permiteți-mi să vă familiarizez rapid cu anatomia editorului VB.

• Bară de meniu: Aici aveți toate opțiunile VB Editor. Luați în considerare acest lucru ca panglica VBA. Conține comenzi pe care le puteți utiliza în timp ce lucrați cu VB Editor.
• Bara de instrumente - Acesta este ca bara de instrumente de acces rapid a editorului VB. Vine cu câteva opțiuni utile și îi puteți adăuga mai multe opțiuni. Avantajul său este că o opțiune din bara de instrumente este la doar un clic distanță.
• Fereastra Project Explorer - Aici Excel listează toate registrele de lucru și toate obiectele din fiecare registru de lucru. De exemplu, dacă avem un registru de lucru cu 3 foi de lucru, acesta va apărea în Project Explorer. Există câteva obiecte suplimentare aici, cum ar fi module, formulare de utilizator și module de clasă.
• Fereastra Cod - Aici este înregistrat sau scris codul VBA. Există o fereastră de cod pentru fiecare obiect listat în Project Explorer - cum ar fi foi de lucru, registre de lucru, module etc. Vom vedea mai târziu în acest tutorial că macro-ul înregistrat intră în fereastra de cod a unui modul.
• Fereastra Proprietăți - Puteți vedea proprietățile fiecărui obiect în această fereastră. Folosesc adesea această fereastră pentru a denumi obiecte sau pentru a modifica proprietățile ascunse. Este posibil să nu vedeți această fereastră când deschideți editorul VB. Pentru a afișa acest lucru, faceți clic pe fila vizualizare și selectați Fereastra Proprietăți.
• Fereastra imediata - Folosesc adesea fereastra imediată în timp ce scriu cod. Este util atunci când doriți să testați câteva instrucțiuni sau în timp ce depanați. Este posibil să nu fie vizibil în mod implicit și îl puteți face să apară făcând clic pe fila Vizualizare și selectând opțiunea Fereastră imediată.

Când am înregistrat macro - EnterText, următoarele lucruri s-au întâmplat în Editorul VB:

• A fost introdus un nou modul.
• A fost înregistrată o macro cu numele specificat de noi - EnterText
• Codul a fost scris în fereastra de cod a modulului.

Deci, dacă faceți dublu clic pe modul (modulul 1 în acest caz), va apărea o fereastră de cod așa cum se arată mai jos.

Iată codul pe care ni l-a dat macro recorderul:

Sub EnterText () "EnterText Macro" Comandă rapidă de la tastatură: Ctrl + Shift + N 'Range ("A2"). Selectați ActiveCell.FormulaR1C1 = "Excel" Range ("A3"). Selectați End Sub

În VBA, orice linie care urmează ‘ (semnul apostrof) nu este executat. Este un comentariu plasat doar în scop informativ. Dacă eliminați primele cinci linii ale acestui cod, macro-ul va funcționa în continuare conform așteptărilor.

Acum permiteți-mi să acoper rapid ce face fiecare linie de cod:

Codul începe cu Sub urmat de numele macro și paranteze goale. Sub este scurt pentru Subroutine. Fiecare subrutină (numită și Procedură) din VBA începe cu Sub și se termină cu End Sub.

• Range („A2”). Selectați - Această linie selectează celula A2.
• ActiveCell.FormulaR1C1 = „Excel” - această linie introduce textul Excel în celula activă. Deoarece am selectat A2 ca prim pas, devine celula noastră activă.
• Range („A3”). Selectați - Aceasta selectează celula A3. Acest lucru se întâmplă când apăsăm tasta Enter după introducerea textului, rezultatul căruia a fost selectarea celulei A3.

Sper că acum aveți o înțelegere de bază despre cum să înregistrați o macro în Excel.

Rețineți că codul scris de un macro recorder nu este nicidecum un cod eficient.

Înregistratorul macro adaugă uneori o mulțime de puf la cod, ceea ce nu este necesar uneori. Dar asta nu înseamnă că nu este util. Pentru cineva care învață VBA, un macro recorder poate fi o modalitate excelentă de a analiza modul în care funcționează lucrurile în VBA.

Absolute Vs Înregistrare macro relativă

Știți deja referințe absolute și relative în Excel … nu?

Dacă nu, citiți mai întâi acest tutorial despre referințe.

Citește? Să mergem mai departe.

Vom vedea mai târziu în această secțiune despre cum să înregistrați macrocomenzi în referințe absolute și relative. Dar înainte de aceasta, permiteți-mi să rezum rapid diferența dintre referința absolută și cea relativă în VBA (în cazul în care ați devenit leneși și nu ați citit linkul pe care l-am dat acum câteva rânduri):

Dacă utilizați o opțiune de referință absolută pentru a înregistra o macro, codul VBA se va referi întotdeauna la aceleași celule pe care le-ați folosit. De exemplu, dacă selectați celula A2, introduceți textul Excel și apăsați Enter, de fiecare dată - indiferent unde vă aflați în foaia de lucru și indiferent ce celulă este selectată, codul dvs. va selecta mai întâi celula A2, introduceți textul Excel și apoi treceți la celula A3.

Dacă utilizați o opțiune de referință relativă pentru a înregistra o macrocomandă, VBA nu ar codifica în mod dur referințele celulei. Mai degrabă, s-ar concentra asupra mișcării în comparație cu celula activă. De exemplu, să presupunem că ați selectat deja celula A1 și începeți să înregistrați macrocomanda în modul de referință relativ.

Acum selectați celula A2, introduceți textul Excel și apăsați tasta Enter. Acum, când rulați această macrocomandă, aceasta nu va reveni la celula A2, ci se va deplasa în raport cu celula activă. De exemplu, dacă este selectată celula K3, aceasta se va deplasa la K4, va introduce textul Excel și apoi va selecta celula K5.

Acum permiteți-mi să vă spun cum să înregistrați o macro în modul referințe relative:

1. Selectați celula A1.
2. Faceți clic pe fila Dezvoltator.
3. În grupul Cod, faceți clic pe butonul „Utilizați referințe relative”. Se va face verde, indicând faptul că este pornit.
4. Faceți clic pe butonul Înregistrare macro.
5. În caseta de dialog Înregistrare macro, introduceți un nume pentru macro. Folosesc numele EnterTextRelRef.
6. În opțiunea Magazin macro în, asigurați-vă că este selectat „Acest registru de lucru”.
7. Faceți clic pe OK.
8. Selectați celula A2.
9. Introduceți textul excela (sau vă puteți introduce numele).
10. Apăsați tasta Enter. Aceasta va duce cursorul la celula A3.
11. Faceți clic pe butonul Opriți înregistrarea fila Dezvoltator.

Aceasta ar înregistra macro-ul în modul de referință relativ.

Acum fă asta.

1. Selectați orice celulă (alta decât A1).
2. Faceți clic pe fila Dezvoltator.
3. În grupul Cod, faceți clic pe butonul Macro.
4. În caseta de dialog Macro, faceți clic pe numele macro - EnterTextRelRef.
5. Faceți clic pe butonul Executare.

Ce se întâmplă? A revenit cursorul la celula A3.

Nu ar fi - deoarece ați înregistrat macro-ul în modul de referință relativ. Deci, cursorul s-ar deplasa în raport cu celula activă. De exemplu, dacă faceți acest lucru când este selectată celula K3, va introduce textul Excel este celula K4 și va selecta celula K5.

Iată codul care este înregistrat în backend (fereastra codului modulului VB Editor):

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

Rețineți că întregul cod nu se referă la celulele K3 sau K4 nicăieri. În schimb, folosește Activecell pentru a se referi la celula selectată și Offset pentru a se deplasa relativ la celula activă.

Nu vă faceți griji cu privire la partea Range („A1”) pe care o are codul. Este unul dintre acele coduri inutile pe care le adaugă macro recorder, care nu are niciun scop și poate fi eliminat. Codul ar funcționa foarte bine fără el.

Butonul „Utilizați referința relativă” din fila Dezvoltator este un buton de comutare. Puteți să o dezactivați (și să reveniți la referința absolută) făcând clic pe ea.

Ce nu poate face un înregistrator macro

Înregistrarea macro este excelentă când vă urmărește în Excel și vă înregistrează pașii exacți, dar este posibil să vă eșueze atunci când aveți nevoie de ea pentru a face mai mult.

• Nu puteți executa un cod fără a selecta obiectul. Dacă doriți ca înregistratorul macro să meargă la următoarea foaie de lucru și să evidențieze toate celulele umplute în coloana A, fără a părăsi foaia de lucru curentă, atunci nu va putea face acest lucru. Pentru că, dacă îți cer să faci asta, nici tu nu vei putea face asta (fără a părăsi foaia curentă). Și dacă nu o puteți face singur, cum vă va înregistra macro recorderul acțiunile dvs. În astfel de cazuri, trebuie să mergeți manual și să creați / editați codul.
• Nu puteți crea o funcție personalizată cu un macro recorder. Cu VBA, puteți crea funcții personalizate pe care le puteți utiliza în foaia de lucru ca funcții obișnuite. Puteți crea acest lucru scriind manual codul.
• Nu puteți rula coduri bazate pe evenimente: În VBA puteți utiliza multe evenimente - cum ar fi deschiderea unui registru de lucru, adăugarea unei foi de lucru, dublu clic pe o celulă, etc, pentru a rula un cod asociat cu acel eveniment. Puteți utiliza un înregistrator macro pentru a face acest lucru.
• Nu puteți crea bucle cu un macro recorder. Când introduceți manual codul, puteți utiliza puterea buclelor în VBA (cum ar fi Pentru Următorul, Pentru Fiecare Următor, Faceți În timp, Faceți până). Dar nu puteți face acest lucru atunci când înregistrați o macro.
• Nu puteți analiza condițiile: Puteți verifica condițiile din cod folosind macro recorder. Dacă scrieți manual un cod VBA, puteți utiliza instrucțiunile IF Then Else pentru a analiza o condiție și a rula un cod dacă este adevărat (sau un alt cod dacă este fals).
• Nu puteți transmite argumente într-o procedură macro: Când înregistrați o macro, aceasta nu va avea niciodată argumente. Un subrutin poate lua argumente de intrare care pot fi utilizate în cadrul macro pentru a efectua o sarcină. În timp ce înregistrați o macrocomandă, acest lucru nu se poate face, deoarece macrocomenzile înregistrate sunt independente și nu sunt conectate la alte macrocomenzi existente.

Extensii de fișiere activate pentru macro

Când înregistrați o macro sau scrieți manual codul VBA în Excel, trebuie să salvați fișierul cu o extensie de fișier activată pentru macro (.xlsm).

Înainte de Excel 2007, exista un singur format de fișier care era suficient - .xls.

Dar, începând cu 2007, .xlsx a fost introdus ca extensie de fișier standard. Fișierele salvate ca .xlsx nu pot conține o macrocomandă. Deci, dacă aveți un fișier cu extensia .xlsx și înregistrați / scrieți o macrocomandă și o salvați, aceasta vă va avertiza să o salvați în format activat pentru macrocomenzi și vă va afișa un dialog (așa cum se arată mai jos):

Dacă selectați Nu, Excel vă permite să îl salvați într-un format activat pentru macro. Dar dacă faceți clic pe Da, Excel va elimina automat tot codul din registrul de lucru și îl va salva ca registru de lucru .xlsx.

Deci, dacă aveți o macrocomandă în registrul de lucru, trebuie să o salvați în formatul .xlsm pentru a păstra acea macrocomandă.

Moduri diferite de a rula o macro în Excel

Până acum, am văzut o singură modalitate de a rula o macro în Excel - care utilizează caseta de dialog Macro.

Dar există o serie de moduri în care puteți rula macrocomenzi.

1. Rulați o macro din panglică (fila Dezvoltator)
2. Utilizarea unei comenzi rapide de la tastatură (pe care trebuie să o atribuiți)
3. Atribuiți macro-ul unei forme
4. Atribuiți macro-ul unui buton
5. Rulați o macro din Editorul VB

Concluzie - Înregistrați o macro când ați blocat

Am menționat deja că un macro recorder este un instrument util pentru oricine lucrează cu VBA în Excel.

Pe măsură ce utilizați recorderul macro de câteva ori, veți observa că scuipă o mulțime de coduri inutile. Cu toate acestea, este încă util și vă oferă câteva idei despre de unde să începeți. De exemplu, dacă vă cer să filtrați o coloană de celule folosind VBA și nu aveți nicio idee despre sintaxă, puteți înregistra rapid o macro și puteți verifica codul.

Un macro recorder este un instrument indispensabil și, chiar și după ani de experiență de codare VBA, am apelat adesea la macro recorder pentru ajutor.