Lucrul cu macrocomenzile Excel VBA
Macrocomenzile din Excel sunt stocate ca cod VBA și, uneori, veți dori să editați acest cod direct. Acest tutorial va acoperi cum să vizualizați și să editați macrocomenzi, să descrieți câteva tehnici de depanare macro și să dați câteva exemple comune de editare.
Vizualizați macrocomenzile
O listă de macrocomenzi poate fi afișată în dialogul Macrocomenzi. Pentru a vizualiza acest dialog, selectați fila Dezvoltator de pe panglică și faceți clic pe butonul Macros.
Dacă mai multe registre de lucru sunt deschise, macrocomenzile din toate registrele de lucru vor fi afișate în listă. Macro-urile din registrul de lucru activ vor apărea numai după nume, în timp ce macro-urile din alte registre de lucru vor fi prefixate de numele registrului de lucru și de un punct de exclamare (adică „Book2! OtherMacro”).
Deschideți o macro pentru editare
Puteți utiliza dialogul Macro pentru a deschide codul pentru o macro selectând numele macro-ului și făcând clic pe butonul Editați. Aceasta va deschide macro-ul în Editorul VB.
Alternativ, puteți deschide editorul VB direct făcând clic pe butonul Visual Basic din fila Dezvoltator sau apăsând comanda rapidă de la tastatură ALT + F11.
Folosind această metodă, va trebui să navigați la macrocomanda dorită (numită și „procedură”). Vom trece peste aspectul Editorului VBA:
Prezentare generală a Editorului VB
Editorul VB are mai multe ferestre; în acest tutorial vom acoperi fereastra de proiect, fereastra de proprietăți și fereastra de cod.
Fereastra proiectului
Fereastra Proiect arată fiecare fișier Excel ca proiect propriu, cu toate obiectele din acel proiect clasificate după tip. Macrocomenzile înregistrate vor apărea în categoria „Module”, obișnuit în obiectul „Module1”. (Dacă proiectul dvs. are mai multe module și nu sunteți sigur unde este stocată macrocomanda dvs., pur și simplu deschideți-o din fereastra de dialog Macros menționată mai sus.)
Fereastra Proprietăți
Fereastra Proprietăți afișează proprietățile și valorile asociate ale unui obiect - de exemplu, făcând clic pe un obiect foaie de lucru în fereastra Proiect va afișa o listă de proprietăți pentru foaia de lucru. Numele proprietăților sunt în stânga, iar valorile proprietăților sunt în dreapta.
Selectarea unui modul în fereastra Proiect va arăta că are o singură proprietate, „(Nume)”. Puteți schimba numele unui modul făcând dublu clic pe valoarea proprietății, tastând un nume nou și apăsând Enter. Schimbarea numelui unui modul îl va redenumi în Fereastra de proiect, ceea ce este util dacă aveți o mulțime de module.
Ferestre de cod
Ferestrele de coduri sunt editoare speciale de text în care puteți edita codul VBA al macro-ului. Dacă doriți să vedeți codul pentru o macrocomandă situată în modulul 1, faceți dublu clic pe „Modulul 1” în fereastra proiectului.
Rularea macro-urilor în Editorul VB
Macrocomenzile pot fi rulate direct din Editorul VB, care este util pentru testare și depanare.
Rularea unei macro
- În fereastra de proiect, faceți dublu clic pe modulul care conține macrocomanda pe care doriți să o testați (pentru a deschide fereastra de cod)
- În fereastra Cod, plasați cursorul oriunde pe codul macrocomenzii între „Sub” și „End Sub”
- Apasă pe Alerga de pe bara de instrumente sau apăsați comanda rapidă de la tastatură F5
„Step-Through” o macro
În loc să rulați macro-ul dintr-o dată, puteți rula macro-ul pe rând, folosind o comandă rapidă de la tastatură pentru a „trece” prin cod. Macrocomanda se va întrerupe pe fiecare linie, permițându-vă să vă asigurați că fiecare linie de cod face ceea ce vă așteptați în Excel. De asemenea, puteți opri continuarea unei macrocomenzi în orice moment folosind această metodă.
Pentru a „trece” printr-o macro:
- În fereastra de proiect, faceți dublu clic pe modulul care conține macrocomanda pe care doriți să o testați (pentru a deschide fereastra de cod)
- În fereastra Cod, plasați cursorul oriunde pe codul macrocomenzii
- Apăsați comanda rapidă de la tastatură F8 pentru a începe procesul „pas cu pas”
- Apăsați F8 în mod repetat pentru a avansa executarea codului, indicat de evidențierea galbenă în fereastra Cod
- Pentru a opri continuarea unei macro-uri, apăsați tasta Resetați buton
De ce editați macro-urile VBA?
Înregistratorul macro - deși este eficient - este, de asemenea, foarte limitat. În unele cazuri, produce macrocomenzi lente, înregistrează acțiuni pe care nu intenționați să le repetați sau înregistrează lucruri pe care nu credeați că le faceți. Învățarea editării macrocomenzilor le va ajuta să ruleze mai rapid, mai eficient și mai previzibil.
Pe lângă remedierea acestor probleme, veți obține și o creștere masivă a productivității atunci când valorificați întreaga putere a macro-urilor. Macrocomenzile nu trebuie să fie doar înregistrări ale sarcinilor - macrocomenzile pot include logică, astfel încât să efectueze sarcini numai în anumite condiții. În doar câteva minute puteți codifica bucle care repetă o sarcină de sute sau mii de ori dintr-o dată!
Mai jos, veți găsi câteva sfaturi utile pentru a vă ajuta să vă optimizați codul macro, precum și instrumente pentru a face macro-urile să funcționeze mai greu și mai inteligent.
Exemple comune de editare macro
Accelerați macro-urile
Dacă aveți o macro care durează mult timp pentru a rula, pot exista câteva motive pentru care rulează lent.
În primul rând: când rulează o macro, Excel va afișa totul așa cum se întâmplă în timp real - în timp ce se poate uite rapid pentru tine, de faptarătând lucrarea este un hit semnificativ de performanță. O modalitate de a face Excel să funcționeze mult mai repede este să-i spui asta opriți actualizarea ecranului:
'Dezactivați ecranul de actualizare a aplicației.ScreenUpdating = False' Activați ecranul de actualizare a aplicației.ScreenUpdating = Adevărat
Linia „Application.ScreenUpdating = False” înseamnă că nu veți vedea macro-ul funcționând, dar va rula mult mai repede. Rețineți că ar trebui să setați întotdeauna ScreenUpdating la True la sfârșitul macro-ului dvs., altfel Excel ar putea să nu acționeze așa cum vă așteptați mai târziu!
O altă modalitate de a accelera macro-urile:dezactivați calculul automat în macro. Dacă ați lucrat cu foi de calcul complexe, veți ști că modificările mici pot declanșa mii de calcule care necesită timp pentru finalizare, motiv pentru care mulți oameni dezactivează calculul automat în opțiunile Excel. De asemenea, puteți comuta acest lucru cu codul VBA, astfel încât macrocomanda dvs. să funcționeze rapid pe alte computere. Acest lucru vă ajută în cazurile în care copiați și lipiți o mulțime de celule de formulă sau când declanșați multe calcule pe măsură ce lipiți datele într-un interval:
„Dezactivați aplicația de calcul automat.Calculation = xlCalculationManual” Activați aplicația de calcul automat.Calculation = xlCalculationAutomatic
Adăugați bucle și logică (Declarații If)
Înregistratorul de macro vă salvează toate acțiunile ca cod într-o limbă numită VBA. VBA este mai mult decât un mod de a înregistra acțiuni în Excel - este un limbaj de programare, ceea ce înseamnă că poate conține cod pentru a lua decizii cu privire la acțiunile de efectuat sau repeta acțiuni până când se îndeplinește o condiție.
Buclă
Spuneți că ați dorit să creați un macro care a pregătit un raport și, ca parte a macro-ului respectiv, a trebuit să adăugați nouăsprezece foi în registrul de lucru, pentru un total de douăzeci. Te-ai putea înregistra făcând clic pe butonul (+) de mai multe ori sau ai putea scrie o buclă care repetă acțiunea pentru tine, astfel:
Raport secundar Prep () Dim i As Long For i = 1 to 19 Sheets.Add Next i End Sub
În acest exemplu, folosim un Pentru buclă, care este un fel de buclă care iterează printr-o serie de articole. Aici, gama noastră este numerele de la 1 la 19, folosind o variabilă numită „i”, astfel încât bucla să poată urmări. În interiorul buclei noastre, se repetă o singură acțiune între pentru șiUrmătorul linii (adăugând foaia), dar puteți adăuga cât de mult cod în buclă doriți să faceți lucruri precum formatarea foii sau copierea și lipirea datelor pe fiecare foaie - orice doriți să repetați.
Dacă Declarații
Un Dacă afirmație este folosit pentru a decide dacă un anumit cod rulează sau nu, folosind un test logic pentru a lua decizia. Iată un exemplu simplu:
Sub ClearIfSmall () If Selection.Value <100 Then Selection.Clear End If End Sub
Acest exemplu simplu arată cum funcționează instrucțiunea If - testați o condiție fie adevărată, fie falsă (valoarea celulei selectate este mai mică de 100?), iar dacă testul returnează True, codul din interior rulează.
Un neajuns al acestui cod este că testează doar o celulă la un moment dat (și ar eșua dacă ați selecta mai multe celule). Acest lucru ar fi mai util dacă ați putea … parcurge fiecare celulă selectată și testați fiecare …
Sub ClearIfSmall () Dim c Ca interval pentru fiecare c din selecție. Celule dacă c. Valoare <100 Apoi c. Ștergeți sfârșitul Dacă următorul
În acest exemplu, există o buclă For ușor diferită - aceasta nu face o buclă printr-o gamă de numere, ci în schimb parcurge toate celulele din selecție, folosind o variabilă numită „c” pentru a ține evidența. În interiorul buclei, valoarea „c” este utilizată pentru a determina dacă celula trebuie sau nu ștearsă.
Instrucțiunile Bucle și If pot fi combinate în orice fel doriți - puteți pune bucle în bucle, sau unul If în interiorul altora, sau puteți folosi un If pentru a decide dacă o buclă ar trebui să ruleze deloc.
<<>>
Eliminați efectele de derulare
Un motiv obișnuit pentru editarea codului macro este eliminarea derulării ecranului. Când înregistrați o macrocomandă, poate fi necesar să accesați alte zone ale unei foi de lucru derulând, dar macrocomenzile nu trebuie să deruleze pentru a accesa datele.
Derularea vă poate aglomera codul cu sute sau chiar mii de linii de cod inutile. Iată un exemplu de cod care se înregistrează atunci când faceți clic și trageți pe bara de derulare:
Acest tip de cod este complet inutil și ar putea fi șters fără a afecta nicio altă funcționalitate. Chiar dacă doriți să păstrați derularea, acest cod ar putea fi condensat într-o buclă.
Eliminați codul redundant
Macrocomenzile înregistrate tind să adauge o mulțime de cod redundant care nu reflectă neapărat ceea ce doriți să facă macro-ul. Luați, de exemplu, următorul cod înregistrat, care înregistrează schimbarea unui nume de font pe o celulă:
Chiar dacă doar numele fontului a fost modificat, au fost înregistrate unsprezece (11) modificări ale fontului, cum ar fi dimensiunea fontului, efectele textului etc. Dacă intenția macro-ului a fost de a schimba doar numele fontului (lăsând în pace toate celelalte proprietăți) macro-ul înregistrat nu ar funcționa!
Este posibil să modificați această macrocomandă astfel încât să schimbe numai numele fontului:
Nu numai că această macro va funcționa așa cum se intenționează acum, dar este, de asemenea, mult mai ușor de citit.
Eliminați mișcările cursorului
Un alt lucru care se înregistrează în macrocomenzi sunt selecțiile de foi de lucru și celule. Aceasta este o problemă, deoarece un utilizator poate pierde cu ușurință urmele la care tocmai lucrau dacă cursorul se deplasează într-o poziție diferită după ce rulează o macro.
Ca și în cazul defilării, tu poate fi necesar să mutați cursorul și să selectați diferite celule pentru a efectua o sarcină, dar macrocomenzile nu trebuie să utilizeze cursorul pentru a accesa datele. Luați în considerare următorul cod, care copiază un interval și apoi îl lipeste în alte trei foi:
Există câteva probleme cu acest cod:
- Utilizatorul își va pierde locul anterior în registrul de lucru
- Macrocomanda nu specifică ce foaie copiemdin - aceasta ar putea fi o problemă dacă macro-ul a fost rulat pe foaia greșită
În plus, codul este greu de citit și risipitor. Aceste probleme pot fi rezolvate suficient de ușor:
În acest cod, este clar să vedem că copiem din Sheet1 și nici foaia de lucru activă, nici intervalul selectat nu trebuie modificate pentru a lipi datele. (O schimbare semnificativă este utilizarea „PasteSpecial” în loc de „Paste” - obiectele Range, cum ar fi „Range („ C4 ″) ”, au acces doar la comanda PasteSpecial.)
Ori de câte ori codul este plin de referințe la „.Select” și „Selecție”, este un indiciu că există spațiu pentru a optimiza codul respectiv și a-l face mai eficient.
