Acestea sunt notele spectacolului meu din apariția mea la Episodul 33 din Call for Help de pe TechTV Canada.
VBA Macro Recorder
Fiecare copie de Excel vândută din 1995 include Visual Basic pentru aplicații (VBA) incredibil de puternic care se ascunde în spatele celulelor. Dacă aveți Excel, aveți VBA.
VBA vă permite să automatizați orice puteți face în Excel, plus să faceți mai multe lucruri care în general nu sunt posibile în Excel.
Din fericire pentru noi, Microsoft a inclus un macro recorder cu Excel. Acest lucru vă permite să scrieți cod VBA fără a fi programator. Din păcate, Macro Recorder este defect. Nu va produce cod care va funcționa de fiecare dată. Înregistratorul macro vă va apropia, dar în multe cazuri trebuie să remediați puțin codul pentru a putea funcționa în mod fiabil. În alte cazuri, trebuie să știți când să utilizați înregistrarea relativă pentru a face macro-ul să funcționeze.
Tracy Syrstad și cu mine am scris VBA și Macros Microsoft Excel 2016 pentru a vă ajuta să înțelegeți limitele codului înregistrat și pentru a vă învăța cum să remediați codul înregistrat în ceva care va funcționa de fiecare dată.
În cadrul emisiunii, am demonstrat procesul de înregistrare a unei macro care va funcționa perfect dacă știi cum să folosești butonul Referință relativă. Cineva îmi oferise o foaie de lucru Excel cu sute de nume și adrese. Voiau etichete poștale. Dacă ați folosit vreodată funcția de îmbinare a corespondenței Microsoft Word, știți că aveți nevoie de fiecare câmp dintr-o nouă coloană din foaia de lucru. În schimb, această foaie de lucru specială avea datele în jos în coloana A.
Procesul de remediere a unei singure etichete este destul de obositor.
- Începeți cu indicatorul de celulă pe numele din coloana A.
- Apăsați săgeata în jos pentru a vă deplasa la adresă
- Ctrl + x pentru a tăia
- Săgeată sus, săgeată dreapta pentru a trece la coloana B de lângă nume
- Ctrl + v pentru a lipi
- Săgeată în jos de două ori, săgeată în stânga o dată pentru a vă deplasa în oraș
- Ctrl + x pentru a tăia
- Săgeată sus de două ori, săgeată dreapta de trei ori
- Ctrl + v pentru a lipi
- Săgeată stânga de două ori, săgeată jos o dată pentru a trece la rândul acum gol.
- Țineți apăsată tasta Shift în timp ce apăsați săgeata în jos de două ori
- Alt + edr pentru a șterge rândurile goale
- Săgeată sus și săgeată jos pentru a selecta din nou doar numele.
Iată animația care arată acești pași:
Configurarea Excel pentru a permite macrocomenzile
Deși fiecare copie a Excel conține VBA, în mod implicit, Microsoft dezactivează posibilitatea de a rula macrocomenzi. Trebuie să faceți o ajustare unică pentru a permite rularea macro-urilor. Deschideți Excel. Din meniu, selectați Instrumente> Macro> Securitate. Dacă nivelul de securitate macro este în prezent setat la ridicat, schimbați-l la Mediu.
Pregătirea pentru înregistrarea macro-ului
Gândiți-vă la pașii necesari pentru realizarea sarcinii. Doriți să vă asigurați că începeți cu indicatorul de celulă pe un nume și îl terminați cu numele următor. Acest lucru vă va permite să rulați macro-ul în mod repetat de multe ori. Când aveți pașii gata de pornire, porniți înregistratorul de macrocomenzi. Din meniu, selectați Tools> Macro> Record New Macro.
Dacă veți rula această macrocomandă timp de mai multe zile, ar trebui să dați macrocomenzii un nume util. Dacă este o macrocomandă unică pentru a automatiza o sarcină unică, atunci lăsarea numelui Macro ca Macro1 este probabil bine. Câmpul important aici este câmpul pentru comanda rapidă. Dacă creați o macrocomandă pentru o singură utilizare, atribuiți macrocomanda unei taste de comandă rapidă, cum ar fi Ctrl + a - este destul de ușor să apăsați Ctrl + a în mod repetat, deoarece tastele sunt apropiate una de cealaltă. Dacă doriți să creați o macrocomandă pe care o veți utiliza în fiecare zi, atunci doriți să atribuiți macrocomanda unei taste care nu este deja o combinație importantă de taste de comenzi rapide. Ctrl + j sau Ctrl + k sunt alegeri bune.
Macrocomenzile de utilizare unică ar trebui stocate în ThisWorkbook. Dacă trebuie să utilizați macrocomanda în mod repetat pe mai multe registre de lucru diferite, atunci puteți stoca macrocomanda în registrul de lucru pentru macrocomenzi personale.
Acum vi se prezintă cea mai mică bară de instrumente din tot Excel; bara de instrumente Stop Recording. Are doar două pictograme și arată astfel:
Dacă această bară de instrumente îți intră în cale, nu apasă X pentru a o închide. În schimb, apucați bara albastră și trageți bara de instrumente într-o locație nouă. Acțiunea de a muta bara de instrumente nu este înregistrată în macro. Dacă închideți accidental bara de instrumente, recuperați-o cu Vizualizare> Bare de instrumente> Opriți înregistrarea. Această acțiune, însă, va fi înregistrată.
Primul buton de pe bara de instrumente este butonul „Opriți înregistrarea”. Acest lucru se explică de la sine. Când ați terminat de înregistrat macrocomanda, apăsați bara de instrumente pentru oprire înregistrare.
Butonul mai important (și rareori înțeles) este butonul „Referință relativă”.
În exemplul nostru actual, trebuie să apăsați butonul Referință relativă înainte de a începe. Dacă înregistrați o macro cu Referințe relative activate, Excel va înregistra pași ca acesta:
- Mutați o celulă în jos
- Tăiați celula curentă
- Mutați o celulă în sus
- Mutați o celulă spre dreapta
- Pastă
- etc.
Dacă în schimb înregistrați macrocomanda fără referințe relative, macrocomanda ar înregistra acești pași:
- Treceți la celula A4
- Tăiați celula A4
- Treceți la celula A3
- Treceți la celula B3
- Lipiți la celula B3
- etc.
Acest lucru ar fi îngrozitor de greșit - avem nevoie de macro pentru a lucra pe celule care sunt 1 și 2 celule de la punctul de pornire al macro-ului. Pe măsură ce rulați macro-ul de mai multe ori, punctul de plecare va fi rândul 4, rândul 5, rândul 6 etc. Înregistrarea macro-ului fără referințe relative activate ar avea ca macro să ruleze întotdeauna pe rândul 3 ca punct de plecare.
Urmați acești pași:
- Alegeți butonul de referință relativă din bara de instrumente Opriți înregistrarea
- Pointerul de celulă ar trebui să fie deja pe un nume din coloana A.
- Apăsați săgeata în jos pentru a vă deplasa la adresă
- Ctrl + x pentru a tăia
- Săgeată sus, săgeată dreapta pentru a trece la coloana B de lângă nume
- Ctrl + v pentru a lipi
- Săgeată în jos de două ori, săgeată în stânga o dată pentru a vă deplasa în oraș
- Ctrl + x pentru a tăia
- Săgeată sus de două ori, săgeată dreapta de trei ori
- Ctrl + v pentru a lipi
- Săgeată stânga de două ori, săgeată jos o dată pentru a trece la rândul acum gol.
- Țineți apăsată tasta Shift în timp ce apăsați săgeata în jos de două ori
- Alt + edr pentru a șterge rândurile goale
- Săgeată sus și săgeată jos pentru a selecta următorul nume din listă.
- Apăsați pe bara de instrumente Stop Recording
- Salvați registrul de lucru
- Testați macrocomanda apăsând tasta de comandă rapidă atribuită mai sus. Ar trebui să remedieze perfect următorul nume din listă
După ce vedeți că macro-ul funcționează după cum doriți, puteți ține apăsat Ctrl + a pentru a repara rapid câteva nume pe secundă. Întreaga listă de 500 de nume poate fi fixată într-un minut sau două.
Sfat bonus - Nu este prezentat
Puteți înfășura cu ușurință macro-ul într-o buclă simplă, pentru a-l fixa pe toate cele 500 de nume fără a fi nevoie să apăsați Ctrl + de câteva sute de ori.
Apăsați tasta Alt + F11 pentru a deschide editorul de macrocomenzi.
Dacă nu vedeți panoul Proiect - VBAProject, apăsați Ctrl + r.
Faceți clic dreapta pe Module1 și alegeți Vizualizare cod. Veți vedea codul care arată astfel:
Acum, nu trebuie să înțelegeți ce face acest cod, dar știți că vrem să rulăm totul în acel macro o dată pentru fiecare nume pe care îl avem. Dacă știți că există 462 de nume, atunci puteți adăuga 2 linii pentru a rula codul de 462 de ori. În partea de sus a macro-ului, introduceți o nouă linie cu cuvintele „ For i = 1 to 462”. În partea de jos a macro-ului, introduceți o linie pe care scrie „ Next i”. Acest lucru îi spune VBA să ruleze codul în 462 de ori.
Concluzie
După această macro simplă, am arătat un exemplu în spectacolul unei macro foarte complexe. Această macrocomandă a creat tabele pivot și diagrame pentru 46 de departamente ale unei companii. Acest raport dura 40 de ore în fiecare lună. Macro-ul VBA rulează acum și creează toate cele 46 de rapoarte în mai puțin de 2 minute.
Cartea VBA și Macros Microsoft Excel 2016 vă va ajuta să parcurgeți curba de învățare, astfel încât să puteți trece de la înregistrarea unor macrocomenzi simple ca acesta la rularea unor rapoarte foarte complexe care vă pot economisi sute de ore pe an. Desigur, dacă nu doriți să învățați VBA, angajați un consultant Excel pentru a proiecta un raport pentru dvs.
