Utilizarea macrocomenzilor Excel poate accelera munca și vă poate economisi mult timp.
O modalitate de a obține codul VBA este de a înregistra macrocomanda și de a lua codul pe care îl generează. Cu toate acestea, codul de la macro recorder este adesea plin de cod care nu este cu adevărat necesar. De asemenea, macro recorderul are unele limitări.
Așadar, merită să aveți o colecție de coduri macro VBA utile pe care le puteți avea în buzunarul din spate și să le utilizați atunci când este necesar.
În timp ce scrierea unui cod macro VBA Excel poate dura ceva timp inițial, după ce ați terminat, îl puteți păstra disponibil ca referință și îl puteți folosi oricând veți avea nevoie de el în continuare.
În acest articol masiv, voi lista câteva exemple utile de macrocomenzi Excel de care am nevoie des și le păstrez ascunse în seiful meu privat.
Voi continua să actualizez acest tutorial cu mai multe exemple macro. Dacă credeți că ceva ar trebui să fie pe listă, lăsați doar un comentariu.
Puteți marca această pagină ca referință viitoare.
Acum, înainte să intru în Exemplul Macro și să vă dau codul VBA, permiteți-mi să vă arăt mai întâi cum să utilizați aceste exemple de coduri.
Utilizarea codului din exemple de macrocomenzi Excel
Iată pașii pe care trebuie să îi urmați pentru a utiliza codul din oricare dintre exemple:
- Deschideți registrul de lucru în care doriți să utilizați macro-ul.
- Țineți apăsată tasta ALT și apăsați F11. Aceasta deschide Editorul VB.
- Faceți clic dreapta pe oricare dintre obiectele din exploratorul de proiecte.
- Accesați Insert -> Module.
- Copiați și lipiți codul în fereastra Codului modulului.
În cazul în care exemplul spune că trebuie să lipiți codul în fereastra codului foii de lucru, faceți dublu clic pe obiectul foii de lucru și copiați lipiți codul în fereastra codului.
După ce ați introdus codul într-un registru de lucru, trebuie să îl salvați cu o extensie .XLSM sau .XLS.
Cum să rulați macro-ul
După ce ați copiat codul în Editorul VB, iată pașii pentru a rula macro-ul:
- Accesați fila Dezvoltator.
- Faceți clic pe Macrocomenzi.
- În caseta de dialog Macro, selectați macrocomanda pe care doriți să o rulați.
- Faceți clic pe butonul Executare.
În cazul în care nu găsiți fila dezvoltator în panglică, citiți acest tutorial pentru a afla cum să o obțineți.
Tutorial asemănător: diferite moduri de a rula o macro în Excel.
În cazul în care codul este lipit în fereastra codului foii de lucru, nu trebuie să vă faceți griji cu privire la rularea codului. Acesta va rula automat când are loc acțiunea specificată.
Acum, să trecem la exemplele macro utile care vă pot ajuta să automatizați munca și să economisiți timp.
Notă: Veți găsi multe cazuri de apostrof (') urmate de o linie sau două. Acestea sunt comentarii care sunt ignorate în timpul rulării codului și sunt plasate ca note pentru auto / cititor.
În cazul în care găsiți vreo eroare în articol sau cod, vă rugăm să fiți minunat și anunțați-mă.
Exemple de macrocomenzi Excel
Mai jos exemplele macro sunt prezentate în acest articol:
Afișează toate foile de lucru dintr-o dată
Dacă lucrați într-un registru de lucru care are mai multe foi ascunse, trebuie să afișați aceste foi una câte una. Acest lucru ar putea dura ceva timp în cazul în care există multe foi ascunse.
Iată codul care va afișa toate foile de lucru din registrul de lucru.
„Acest cod va afișa toate foile din registrul de lucru Sub UnhideAllWoksheets () Dim ws Ca foaie de lucru pentru fiecare ws din ActiveWorkbook.Wheksheets ws.Visible = xlSheetVisible Next ws End Sub
Codul de mai sus utilizează o buclă VBA (Pentru fiecare) pentru a parcurge fiecare fișă de lucru din registrul de lucru. Apoi schimbă proprietatea vizibilă a foii de lucru în vizibil.
Iată un tutorial detaliat despre cum să utilizați diferite metode pentru a afișa foile în Excel.
Ascundeți toate foile de lucru, cu excepția foii active
Dacă lucrați la un raport sau tablou de bord și doriți să ascundeți toată foaia de lucru, cu excepția celei care are raportul / tabloul de bord, puteți utiliza acest cod macro.
'Această macrocomandă va ascunde toată foaia de lucru, cu excepția foii active Sub HideAllExceptActiveSheet () Dim ws Ca foaie de lucru pentru fiecare ws din acest manual.
Sortați foile de lucru alfabetic folosind VBA
Dacă aveți un registru de lucru cu multe foi de lucru și doriți să le sortați alfabetic, acest cod macro poate fi foarte util. Acesta ar putea fi cazul dacă aveți nume de foi ca ani sau nume de angajați sau nume de produse.
„Acest cod va sorta foile de lucru alfabetic Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count Pentru i = 1 To ShCount - 1 Pentru j = i + 1 To ShCount If Sheets (j) .Name <Sheets (i) .Name Then Sheets (j) .Move before: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Protejați toate foile de lucru dintr-o dată
Dacă aveți o mulțime de foi de lucru într-un registru de lucru și doriți să protejați toate foile, puteți utiliza acest cod macro.
Vă permite să specificați parola în cadrul codului. Veți avea nevoie de această parolă pentru a proteja foaia de lucru.
'Acest cod va proteja toate foile dintr-o singură dată Sub ProtectAllSheets () Dim ws Ca foaie de lucru Dim parolă ca Parolă șir = "Test123"' înlocuiți Test123 cu parola pe care o doriți Sfârșitul Sub
Deprotejați toate foile de lucru dintr-o dată
Dacă aveți o parte sau toate foile de lucru protejate, puteți utiliza doar o ușoară modificare a codului utilizat pentru a proteja foile pentru a o proteja.
'Acest cod va proteja toate foile dintr-o singură dată Sub ProtectAllSheets () Dim ws Ca foaie de lucru Dim parolă ca Parolă șir = "Test123"' înlocuiește Test123 cu parola pe care o doriți Sfârșitul Sub
Rețineți că parola trebuie să fie aceeași cu cea utilizată pentru blocarea foilor de lucru. În caz contrar, veți vedea o eroare.
Afișați toate rândurile și coloanele
Acest cod macro va afișa toate rândurile și coloanele ascunse.
Acest lucru ar putea fi foarte util dacă primiți un fișier de la altcineva și doriți să vă asigurați că nu există rânduri / coloane ascunse.
'Acest cod va dezvălui toate rândurile și coloanele din Foaia de lucru Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Anulează toate celulele combinate
Este o practică obișnuită să îmbini celulele pentru a le face una. În timp ce funcționează, atunci când celulele sunt îmbinate, nu veți putea sorta datele.
În cazul în care lucrați cu o foaie de lucru cu celule îmbinate, utilizați codul de mai jos pentru a anula toate celulele îmbinate dintr-o dată.
„Acest cod va anula toate celulele îmbinate Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Rețineți că, în loc de Fuzionare și Centrare, vă recomand să utilizați opțiunea Centrare peste selecție.
Salvați registrul de lucru cu TimeStamp în numele său
Foarte mult timp, poate fi necesar să creați versiuni ale lucrării dvs. Acestea sunt destul de utile în proiectele lungi în care lucrați cu un fișier în timp.
O bună practică este salvarea fișierului cu marcaje de timp.
Utilizarea marcajelor de timp vă va permite să reveniți la un anumit fișier pentru a vedea ce modificări au fost făcute sau ce date au fost utilizate.
Iată codul care va salva automat registrul de lucru în folderul specificat și va adăuga un marcaj de timp ori de câte ori este salvat.
'Acest cod va salva fișierul cu un timestamp în numele său Sub SaveWorkbookWithTimeStamp () Dim timestamp as String timestamp = Format (Data, "dd-mm-aaaa") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp End Sub
Trebuie să specificați locația folderului și numele fișierului.
În codul de mai sus, „C: UsersUsernameDesktop este locația folderului pe care am folosit-o. Trebuie să specificați locația folderului în care doriți să salvați fișierul. De asemenea, am folosit un nume generic „WorkbookName” ca prefix al numelui de fișier. Puteți specifica ceva legat de proiectul sau compania dvs.
Salvați fiecare foaie de lucru ca PDF separat
Dacă lucrați cu date pentru ani sau diviziuni sau produse diferite, este posibil să aveți nevoie să salvați diferite foi de lucru ca fișiere PDF.
Deși ar putea fi un proces care necesită mult timp dacă se face manual, VBA îl poate accelera cu adevărat.
Iată un cod VBA care va salva fiecare foaie de lucru ca un PDF separat.
'Acest cod va salva fiecare foaie de lucru ca fișier PDF separat SaveWorkshetAsPDF () Dim ws Ca foaie de lucru pentru fiecare ws din foi de lucru ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
În codul de mai sus, am specificat adresa locației folderului în care doresc să salvez PDF-urile. De asemenea, fiecare PDF va primi același nume cu cel al foii de lucru. Va trebui să modificați locația acestui folder (cu excepția cazului în care numele dvs. este, de asemenea, Sumit și îl salvați într-un folder de test de pe desktop).
Rețineți că acest cod funcționează numai pentru foi de lucru (și nu pentru foi de diagrame).
Salvați fiecare foaie de lucru ca PDF separat
Iată codul care vă va salva întregul registru de lucru ca PDF în folderul specificat.
'Acest cod va salva întregul registru de lucru ca PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Va trebui să schimbați locația folderului pentru a utiliza acest cod.
Convertiți toate formulele în valori
Utilizați acest cod atunci când aveți o foaie de lucru care conține o mulțime de formule și doriți să convertiți aceste formule în valori.
„Acest cod va converti toate formulele în valori Sub ConvertToValues () Cu ActiveSheet.UsedRange .Value = .Value End With End Sub
Acest cod identifică automat celulele utilizate și îl convertesc în valori.
Protejați / blocați celulele cu formule
Poate doriți să blocați celulele cu formule atunci când aveți multe calcule și nu doriți să o ștergeți sau să o modificați din greșeală.
Iată codul care va bloca toate celulele care au formule, în timp ce toate celelalte celule nu sunt blocate.
„Acest cod macro va bloca toate celulele cu formule Sub LockCellsWithFormulas () With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub
Tutorial asociat: Cum se blochează celulele în Excel.
Protejați toate foile de lucru din registrul de lucru
Utilizați codul de mai jos pentru a proteja simultan toate foile de lucru dintr-un registru de lucru.
„Acest cod va proteja toate foile din registrul de lucru Sub ProtectAllSheets () Dim ws Ca foaie de lucru pentru fiecare ws din foi de lucru ws.
Acest cod va parcurge toate foile de lucru unul câte unul și îl va proteja.
În cazul în care doriți să protejați toate foile de lucru, utilizați ws.Unprotect în loc de ws.Protect în cod.
Introduceți un rând după fiecare alt rând în selecție
Utilizați acest cod atunci când doriți să inserați un rând gol după fiecare rând din intervalul selectat.
'Acest cod va insera un rând după fiecare rând în selecția Sub InsertAlternateRows () Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Introduceți ActiveCell.Offset (2, 0). Selectați Next i End Sub
În mod similar, puteți modifica acest cod pentru a insera o coloană goală după fiecare coloană din intervalul selectat.
Introduceți automat data și marcajul de timp în celula adiacentă
Un timestamp este ceva pe care îl utilizați atunci când doriți să urmăriți activitățile.
De exemplu, vă recomandăm să urmăriți activități, cum ar fi momentul în care a fost efectuată o anumită cheltuială, la ce oră a fost creată factura de vânzare, când a fost efectuată introducerea datelor într-o celulă, când a fost actualizat ultima dată raportul etc.
Utilizați acest cod pentru a insera o ștampilă de dată și oră în celula alăturată atunci când se face o intrare sau se editează conținutul existent.
'Acest cod va introduce un timestamp în celula alăturată Private Sub Worksheet_Change (ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset (0, 1) = Format (Acum (), „zz-ll-aaaa hh: mm: ss”) Application.EnableEvents = Adevărat sfârșit în cazul în care Handler: End Sub
Rețineți că trebuie să inserați acest cod în fereastra codului foii de lucru (și nu în fereastra codului modulului așa cum am făcut în alte exemple de macrocomenzi Excel până acum). Pentru a face acest lucru, în Editorul VB, faceți dublu clic pe numele foii pe care doriți această funcționalitate. Apoi copiați și lipiți acest cod în fereastra de cod a foii respective.
De asemenea, acest cod funcționează atunci când introducerea datelor se face în Coloana A (rețineți că codul are linia Target.Column = 1). Puteți schimba acest lucru în consecință.
Evidențiați rândurile alternative din selecție
Evidențierea rândurilor alternative poate crește foarte ușor lizibilitatea datelor. Acest lucru poate fi util atunci când trebuie să faceți o imprimare și să parcurgeți datele.
Iată un cod care va evidenția instantaneu rânduri alternative în selecție.
„Acest cod ar evidenția rânduri alternative în selecția Sub HighlightAlternateRows () Dim Myrange as Range Dim Myrow As Range Set Myrange = Selecție pentru fiecare Myrow din Myrange.Rows If Myrow.Row Mod 2 = 1 Apoi Myrow.Interior.Color = vbCyan End Dacă următorul Myrow End Sub
Rețineți că am specificat culoarea ca vbCyan în cod. Puteți specifica și alte culori (cum ar fi vbRed, vbGreen, vbBlue).
Evidențiați celulele cu cuvinte scrise greșit
Excel nu are o verificare ortografică așa cum se întâmplă în Word sau PowerPoint. Deși puteți rula verificarea ortografică apăsând tasta F7, nu există niciun indiciu vizual atunci când există o greșeală de ortografie.
Utilizați acest cod pentru a evidenția instantaneu toate celulele care au o greșeală de ortografie.
„Acest cod va evidenția celulele care au cuvinte ortografiate greșit Sub HighlightMisspelledCells () Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub
Rețineți că celulele care sunt evidențiate sunt cele care au text pe care Excel îl consideră o eroare de ortografie. În multe cazuri, ar evidenția și nume sau termeni de marcă pe care nu îi înțelege.
Reîmprospătați toate tabelele pivot din registrul de lucru
Dacă aveți mai multe tabele pivot în registrul de lucru, puteți utiliza acest cod pentru a reîmprospăta toate aceste tabele pivot simultan.
„Acest cod va reîmprospăta toate tabelele pivot din tabelul de lucru Sub RefreshAllPivotTables () Dim PT Ca tabel pivot pentru fiecare PT din ActiveSheet.PivotTables PT.RefreshTable Următorul PT End Sub
Puteți citi mai multe despre reîmprospătarea tabelelor pivot aici.
Schimbați majusculele cu celule selectate în majuscule
În timp ce Excel are formulele pentru a schimba literele majuscule ale textului, vă face să faceți acest lucru într-un alt set de celule.
Utilizați acest cod pentru a schimba instantaneu literele minuscule ale textului din textul selectat.
'Acest cod va schimba selecția cu majuscule majuscule ChangeCase () Dim Rng ca interval pentru fiecare Rng din selecție.
Rețineți că, în acest caz, am folosit UCase pentru a face ca textul să fie superior. Puteți utiliza LCase pentru minuscule.
Evidențiați toate celulele cu comentarii
Utilizați codul de mai jos pentru a evidenția toate celulele care au comentarii.
"Acest cod va evidenția celulele care au comentarii" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
În acest caz, am folosit vbBlue pentru a da o culoare albastră celulelor. Puteți schimba acest lucru în alte culori, dacă doriți.
Evidențiați celulele goale cu VBA
Deși puteți evidenția celula goală cu formatare condiționată sau utilizând caseta de dialog Accesați special, dacă trebuie să o faceți destul de des, este mai bine să utilizați o macro.
Odată creată, puteți avea această macrocomandă în Bara de instrumente cu acces rapid sau o puteți salva în registrul dvs. personal de macrocomenzi.
Iată codul macro VBA:
„Acest cod va evidenția toate celulele goale din setul de date Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
În acest cod, am specificat celulele goale care trebuie evidențiate în culoarea roșie. Puteți alege alte culori precum albastru, galben, cian etc.
Cum se sortează datele după o singură coloană
Puteți utiliza codul de mai jos pentru a sorta datele după coloana specificată.
Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes End Sub
Rețineți că am creat un interval numit cu numele „DataRange” și l-am folosit în locul referințelor de celulă.
De asemenea, există trei parametri cheie care sunt utilizați aici:
- Tasta 1 - Aceasta este pe care doriți să sortați setul de date. În exemplul de cod de mai sus, datele vor fi sortate pe baza valorilor din coloana A.
- Comandă - Aici trebuie să specificați dacă doriți să sortați datele în ordine crescătoare sau descendentă.
- Antet - Aici trebuie să specificați dacă datele dvs. au anteturi sau nu.
Citiți mai multe despre cum să sortați datele în Excel folosind VBA.
Cum se sortează datele după mai multe coloane
Să presupunem că aveți un set de date așa cum se arată mai jos:
Mai jos este codul care va sorta datele pe baza mai multor coloane:
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlDa. Aplicați End With End Sub
Rețineți că aici am specificat să sortați mai întâi pe baza coloanei A și apoi pe baza coloanei B.
Ieșirea ar fi ceva așa cum se arată mai jos:
Cum se obține doar partea numerică dintr-un șir din Excel
Dacă doriți să extrageți numai partea numerică sau doar partea de text dintr-un șir, puteți crea o funcție personalizată în VBA.
Puteți utiliza apoi această funcție VBA în foaia de lucru (la fel ca funcțiile Excel obișnuite) și va extrage doar partea numerică sau text din șir.
Ceva așa cum se arată mai jos:
Mai jos este codul VBA care va crea o funcție pentru a extrage partea numerică dintr-un șir:
„Acest cod VBA va crea o funcție pentru a obține partea numerică dintr-un șir. ) Apoi Rezultat = Rezultat & Mediu (CellRef, i, 1) Următorul i GetNumeric = Funcția de finalizare a rezultatului
Aveți nevoie de un loc în cod într-un modul și apoi puteți utiliza funcția = GetNumeric în foaia de lucru.
Această funcție va lua un singur argument, care este referința celulei celulei din care doriți să obțineți partea numerică.
În mod similar, mai jos este funcția care vă va aduce doar partea de text dintr-un șir în Excel:
„Acest cod VBA va crea o funcție pentru a obține partea de text dintr-un șir Funcție GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Apoi Rezultat = Rezultat & Mediu (CellRef, i, 1) Următorul i GetText = Funcția de finalizare a rezultatului
Așadar, acestea sunt câteva dintre codurile macro Excel utile pe care le puteți utiliza în munca dvs. de zi cu zi pentru a automatiza sarcinile și a fi mult mai productiv.
