Lucrul cu foi de lucru folosind Excel VBA (explicat cu exemple)

În afară de celule și intervale, lucrul cu foi de lucru este un alt domeniu pe care ar trebui să-l cunoașteți pentru a utiliza VBA eficient în Excel.

La fel ca orice obiect din VBA, foile de lucru au diferite proprietăți și metode asociate cu acesta pe care le puteți utiliza în timp ce vă automatizați lucrul cu VBA în Excel.

În acest tutorial, voi acoperi în detaliu „Fișe de lucru” și vă voi arăta, de asemenea, câteva exemple practice.

Asadar, haideti sa începem.

Toate codurile pe care le menționez în acest tutorial trebuie plasate în Editorul VB. Accesați secțiunea „Unde se pune codul VBA” pentru a afla cum funcționează.

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

Diferența dintre foi de lucru și foi în VBA

În VBA, aveți două colecții care uneori pot fi puțin confuze.

Într-un registru de lucru, puteți avea foi de lucru și, de asemenea, foi de diagrame. Exemplul de mai jos are trei foi de lucru și o foaie de diagramă.

În Excel VBA:

• Colecția „Foi de lucru” se referă la colecția tuturor obiectelor foii de lucru dintr-un registru de lucru. În exemplul de mai sus, colecția foi de lucru ar consta din trei foi de lucru.
• Colecția „Foi” se va referi la toate foile de lucru, precum și la foile grafice din registrul de lucru. În exemplul de mai sus, ar avea patru elemente - 3 foi de lucru + 1 foaie de diagramă.

Dacă aveți un registru de lucru care are numai foi de lucru și fără foi de diagrame, atunci colecția „Foi de lucru” și „Foi” este aceeași.

Dar când aveți una sau mai multe foi de grafic, colecția „Foi” ar fi mai mare decât colecția „Fișe de lucru”

Foi = Foi de lucru + Foi de diagrame

Acum, cu această distincție, vă recomand să fiți cât mai specific posibil atunci când scrieți un cod VBA.

Deci, dacă trebuie să vă referiți numai la foi de lucru, utilizați colecția „Foi de lucru” și, dacă trebuie să vă referiți la toate foile (inclusiv foi de diagrame), utilizați colecția „Foi”.

În acest tutorial, voi folosi doar colecția „Foi de lucru”.

Referirea unei foi de lucru în VBA

Există multe moduri diferite pe care le puteți utiliza pentru a vă referi la o foaie de lucru în VBA.

Înțelegerea modului de referire la foile de lucru vă va ajuta să scrieți un cod mai bun, mai ales atunci când utilizați bucle în codul dvs. VBA.

Utilizarea numelui foii de lucru

Cel mai simplu mod de a face referire la o foaie de lucru este să-i folosiți numele.

De exemplu, să presupunem că aveți un registru de lucru cu trei foi de lucru - Foaia 1, Foaia 2, Foaia 3.

Și doriți să activați foaia 2.

Puteți face acest lucru folosind următorul cod: Sub ActivateSheet () Foi de lucru ("Sheet2"). Activați End Sub

Codul de mai sus cere VBA să se refere la Sheet2 din colecția de foi de lucru și să o activeze.

Deoarece folosim numele exact al foii, puteți utiliza și colecția Foi. Deci, codul de mai jos ar face același lucru.

Sub ActivateSheet () Foi ("Sheet2"). Activează End Sub

Utilizarea numărului index

În timp ce utilizarea numelui foii este o modalitate ușoară de a face referire la o foaie de lucru, uneori, este posibil să nu știți numele exact al foii de lucru.

De exemplu, dacă utilizați un cod VBA pentru a adăuga o foaie de lucru nouă în registrul de lucru și nu știți câte foi de lucru există deja, nu ați ști numele noii foi de lucru.

În acest caz, puteți utiliza numărul de index al foilor de lucru.

Să presupunem că aveți următoarele foi într-un registru de lucru:

Codul de mai jos ar activa Sheet2:

Sub ActivateSheet () Foi de lucru (2). Activați End Sub

Rețineți că am folosit indexul 2 în Fișe de lucru (2). Aceasta s-ar referi la al doilea obiect din colecția fișelor de lucru.

Acum, ce se întâmplă când folosești 3 ca număr index?

Se va selecta Sheet3.

Dacă vă întrebați de ce a selectat Sheet3, deoarece este în mod clar al patrulea obiect.

Acest lucru se întâmplă deoarece o foaie de diagramă nu face parte din colecția de foi de lucru.

Deci, atunci când folosim numerele index din colecția foi de lucru, se va referi doar la foile de lucru din registrul de lucru (și va ignora foile de diagrame).

Dimpotrivă, dacă utilizați Foi de calcul, Foi de calcul (1) se referă la Foi de calcul 1, Foi de calcul (2) se referă la Fișa 2, Foi de calcul (3) se referă la Diagrama 1 și Foi de calcul (4) se referă la Fișa 3.

Această tehnică de utilizare a numărului de index este utilă atunci când doriți să parcurgeți toate foile de lucru dintr-un registru de lucru. Puteți să numărați numărul de foi de lucru și apoi să le parcurgeți folosind acest număr (vom vedea cum să faceți acest lucru mai târziu în acest tutorial).

Notă: numărul indexului merge de la stânga la dreapta. Deci, dacă mutați Sheet2 în stânga Sheet1, atunci Foi de lucru (1) se va referi la Sheet2.

Utilizarea numelui codului foii de lucru

Unul dintre dezavantajele utilizării numelui foii (așa cum am văzut în secțiunea de mai sus) este că un utilizator îl poate modifica.

Și dacă numele foii a fost modificat, codul dvs. nu ar funcționa până când nu schimbați numele foii de lucru și în codul VBA.

Pentru a rezolva această problemă, puteți utiliza numele de cod al foii de lucru (în locul numelui obișnuit pe care l-am folosit până acum). Un nume de cod poate fi atribuit în Editorul VB și nu se modifică atunci când schimbați numele foii din zona foii de lucru.

Pentru a oferi foii de lucru un nume de cod, urmați pașii de mai jos:

1. Faceți clic pe fila Dezvoltator.
2. Faceți clic pe butonul Visual Basic. Aceasta va deschide Editorul VB.
3. Faceți clic pe opțiunea View din meniu și faceți clic pe Window Window. Acest lucru va face vizibil panoul Proprietăți. Dacă panoul Proprietăți este deja vizibil, săriți peste acest pas.
4. Faceți clic pe numele foii din exploratorul de proiecte pe care doriți să îl redenumiți.
5. În panoul Proprietăți, schimbați numele în câmpul din fața (Nume). Rețineți că nu puteți avea spații în nume.

Pașii de mai sus ar schimba numele foii de lucru în backend-ul VBA. În vizualizarea foii de lucru Excel, puteți denumi foaia de lucru oricum doriți, dar în backend va răspunde atât la nume - numele foii, cât și numele codului.

În imaginea de mai sus, numele foii este „SheetName”, iar numele de cod este „CodeName”. Chiar dacă modificați numele foii pe foaia de lucru, numele codului rămâne același.

Acum, puteți utiliza fie colecția de foi de lucru pentru a vă referi la foaia de lucru, fie utilizați numele de cod.

De exemplu, ambele linii vor activa foaia de lucru.

Fișe de lucru („Nume foaie”). Activați Numele Codului. Activați

Diferența dintre aceste două este că, dacă schimbați numele foii de lucru, prima nu ar funcționa. Dar a doua linie ar continua să funcționeze chiar și cu numele schimbat. A doua linie (folosind CodeName) este, de asemenea, mai scurtă și mai ușor de utilizat.

Referindu-vă la o foaie de lucru într-un alt registru de lucru

Dacă doriți să faceți referire la o foaie de lucru într-un alt registru de lucru, acel registru de lucru trebuie să fie deschis în timp ce rulează codul și trebuie să specificați numele registrului de lucru și foaia de lucru la care doriți să faceți referire.

De exemplu, dacă aveți un registru de lucru cu numele Exemple și doriți să activați Sheet1 în exemplul de registru de lucru, trebuie să utilizați codul de mai jos:

Sub SheetActivate () Cărți de lucru ("Exemple.xlsx"). Foi de lucru ("Sheet1"). Activați End Sub

Rețineți că, dacă registrul de lucru a fost salvat, trebuie să utilizați numele fișierului împreună cu extensia. Dacă nu sunteți sigur ce nume să folosiți, luați ajutor de la Project Explorer.

În cazul în care registrul de lucru nu a fost salvat, nu este necesar să utilizați extensia de fișier.

Adăugarea unei foi de lucru

Codul de mai jos ar adăuga o foaie de lucru (ca prima foaie de lucru - adică ca foaia din stânga din fila foaie).

SubAdSheet () Foi de lucru.Adăugare sfârșit Sub

Se ia numele implicit Sheet2 (sau orice alt număr bazat pe câte foi există deja).

Dacă doriți ca o foaie de lucru să fie adăugată înainte de o foaie de lucru specifică (să spunem Foaia 2), atunci puteți utiliza codul de mai jos.

Sub AddSheet () Worksheets.Add Before: = Worksheets ("Sheet2") End Sub

Codul de mai sus îi spune VBA să adauge o foaie și apoi folosește instrucțiunea „Înainte” pentru a specifica foaia de lucru înaintea căreia ar trebui inserată noua foaie de lucru.

În mod similar, puteți adăuga și o foaie după o foaie de lucru (să spunem Foaia 2), folosind codul de mai jos:

Sub AddSheet () Worksheets.Add After: = Worksheets ("Sheet2") End Sub

Dacă doriți ca noua foaie să fie adăugată la sfârșitul foilor, trebuie mai întâi să știți câte foi există. Următorul cod contează mai întâi numărul de foi, iar adăugați noua foaie după ultima foaie (la care ne referim folosind numărul de index).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After: = Worksheets (SheetCount) End Sub

Ștergerea unei foi de lucru

Codul de mai jos ar șterge foaia activă din registrul de lucru.

Sub DeleteSheet () ActiveSheet. Ștergeți End Sub

Codul de mai sus va afișa un mesaj de avertizare înainte de a șterge foaia de lucru.

Dacă nu doriți să vedeți solicitarea de avertizare, utilizați codul de mai jos:

Sub DeleteSheet () Application.DisplayAlerts = Fals ActiveSheet.Delete Application.DisplayAlerts = Sub End True

Când Application.DisplayAlerts este setat la False, nu vă va afișa promptul de avertizare. Dacă îl utilizați, nu uitați să îl setați la True la sfârșitul codului.

Amintiți-vă că nu puteți anula această ștergere, deci folosiți codul de mai sus când sunteți absolut sigur.

Dacă doriți să ștergeți o anumită foaie, puteți face acest lucru folosind următorul cod:

Sub DeleteSheet () Foi de lucru ("Sheet2"). Ștergeți Sub Sub

De asemenea, puteți utiliza numele de cod al foii pentru ao șterge.

Sub DeleteSheet () Sheet 5. Ștergeți End Sub

Redenumirea foilor de lucru

Puteți modifica proprietatea de nume a foii de lucru pentru a-i schimba numele.

Următorul cod va schimba numele Sheet1 în „Summary”.

Sub RenameSheet () Foi de lucru ("Sheet1"). Name = "Summary" End Sub

Puteți combina acest lucru cu metoda de adăugare a foilor pentru a avea un set de foi cu nume specifice.

De exemplu, dacă doriți să inserați patru foi cu numele 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 și 2021-2022 Q4, puteți utiliza codul de mai jos.

Sub RenameSheet () Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 To 4 Workshesets.Add after: = Worksheets (Countsheets + i - 1) Worksheets (Countsheets + i) .Name = "2018 Q" & i Next i Sfârșitul Sub

În codul de mai sus, numărăm mai întâi numărul de foi și apoi folosim o buclă Pentru următorul pentru a insera noi foi la final. Pe măsură ce foaia este adăugată, codul o redenumește și ea.

Atribuirea obiectului Foaie de lucru unei variabile

Când lucrați cu foi de lucru, puteți atribui o foaie de lucru unei variabile de obiect și apoi utilizați variabila în locul referințelor de foaie de lucru.

De exemplu, dacă doriți să adăugați un prefix de an la toate foile de lucru, în loc să numărați foile și bucla care rulează de multe ori, puteți utiliza variabila obiect.

Iată codul care va adăuga2021-2022 ca prefix la toate numele foii de lucru.

Sub RenameSheet () Dim Ws Ca foaie de lucru pentru fiecare Ws din foi de lucru Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Codul de mai sus declară o variabilă Ws ca tip de foaie de lucru (utilizând linia „Dim Ws As Worksheet”).

Acum, nu este nevoie să numărăm numărul de foi pentru a le parcurge. În schimb, putem folosi bucla „Pentru fiecare W din foile de lucru”. Acest lucru ne va permite să parcurgem toate foile din colecția de foi de lucru. Nu contează dacă există 2 coli sau 20 de coli.

În timp ce codul de mai sus ne permite să parcurgem toate foile, puteți atribui o anumită foaie unei variabile.

În codul de mai jos, atribuim variabila Ws la Sheet2 și o utilizăm pentru a accesa toate proprietățile Sheet2.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

Odată ce ați setat o referință de foaie de lucru la o variabilă de obiect (utilizând instrucțiunea SET), acel obiect poate fi utilizat în locul referinței de foaie de lucru. Acest lucru poate fi util atunci când aveți un cod complicat de mult timp și doriți să modificați referința. În loc să faceți modificarea peste tot, puteți face pur și simplu schimbarea în instrucțiunea SET.

Rețineți că codul declară obiectul Ws ca variabilă de tip Foaie de lucru (folosind linia Dim Ws ca Foaie de lucru).

Ascundeți foile de lucru folosind VBA (Ascuns + Foarte ascuns)

Ascunderea și dezvăluirea foilor de lucru în Excel este o sarcină simplă.

Puteți ascunde o foaie de lucru și utilizatorul nu o va vedea când deschide registrul de lucru. Cu toate acestea, pot dezvălui cu ușurință foaia de lucru făcând clic dreapta pe orice filă de foaie.

Dar dacă nu doriți ca acestea să poată dezvălui foaia de lucru.

Puteți face acest lucru folosind VBA.

Codul de mai jos ar ascunde toate foile de lucru din registrul de lucru (cu excepția foii active), astfel încât să nu îl puteți dezvălui făcând clic dreapta pe numele foii.

Sub HideAllExcetActiveSheet () Dim Ws Ca foaie de lucru pentru fiecare W din acest manual de lucru. Foi de lucru dacă Ws.Name ActiveSheet.Name Apoi Ws.Visible = xlSheetVeryHidden Următorul Ws End Sub

În codul de mai sus, proprietatea Ws.Visible este schimbată în xlSheetVeryHidden.

• Când proprietatea Vizibil este setată la xlSheetVisible, foaia este vizibilă în zona foii de lucru (ca file de foaie de lucru).
• Când proprietatea Vizibilă este setată la xlSheetHidden, foaia este ascunsă, dar utilizatorul o poate afișa făcând clic dreapta pe orice filă de foaie.
• Când proprietatea Vizibilă este setată la xlSheetVeryHidden, foaia este ascunsă și nu poate fi dezvăluită din zona foii de lucru. Trebuie să utilizați un cod VBA sau fereastra de proprietăți pentru a-l ascunde.

Dacă doriți să ascundeți pur și simplu foile, care pot fi ascunse cu ușurință, utilizați codul de mai jos:

Sub HideAllExceptActiveSheet () Dim Ws Ca foaie de lucru pentru fiecare W din acest manual de lucru. Foi de lucru dacă Ws.Name ActiveSheet.Name Apoi Ws.Visible = xlSheetHidden Next Ws End Sub

Codul de mai jos ar afișa toate foile de lucru (atât ascunse, cât și foarte ascunse).

Sub UnhideAllWoksheets () Dim Ws Ca foaie de lucru pentru fiecare W din acest manual de lucru. Foi de lucru Ws.Visible = xlSheetVisible Next Ws End Sub

Articol conex: Afișează toate foile în Excel (dintr-o dată)

Ascundeți Foi de calcul pe baza textului din acesta

Să presupunem că aveți mai multe foi cu numele diferitelor departamente sau ani și doriți să ascundeți toate foile, cu excepția celor care au anul 2021-2022 în ea.

Puteți face acest lucru folosind o funcție VBA INSTR.

Codul de mai jos ar ascunde toate foile, cu excepția celor cu textul 2021-2022.

Sub HideWithMatchingText () Dim Ws ca foaie de lucru pentru fiecare Ws din foi de lucru If InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Atunci Ws.Visible = xlSheetHidden End Dacă Next Ws End Sub

În codul de mai sus, funcția INSTR returnează poziția caracterului în care găsește șirul care se potrivește. Dacă nu găsește șirul corespunzător, returnează 0.

Codul de mai sus verifică dacă numele are textul 2021-2022. Dacă se întâmplă, nu se întâmplă nimic, altfel foaia de lucru este ascunsă.

Puteți face acest lucru cu un pas mai departe având textul într-o celulă și folosind acea celulă în cod. Acest lucru vă va permite să aveți o valoare în celulă și atunci când rulați macro-ul, toate foile, cu excepția celei cu textul potrivit, vor rămâne vizibile (împreună cu foile în care introduceți valoarea în celulă).

Sortarea foilor de lucru într-o ordine alfabetică

Folosind VBA, puteți sorta rapid foile de lucru pe baza numelor lor.

De exemplu, dacă aveți un registru de lucru care are foi pentru departamente sau ani diferiți, puteți utiliza codul de mai jos pentru a sorta rapid aceste foi într-o ordine crescătoare.

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For 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

Rețineți că acest cod funcționează bine cu numele textului și, în majoritatea cazurilor, cu ani și numere. Dar vă poate oferi rezultate greșite în cazul în care aveți numele foilor 1,2,11. Va sorta și vă va oferi secvența 1, 11, 2. Acest lucru se datorează faptului că face comparația ca text și consideră 2 mai mare decât 11.

Protejați / Protejați toate foile dintr-o singură acțiune

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 codul VBA de mai jos.

Vă permite să specificați parola în cadrul codului. Veți avea nevoie de această parolă pentru a proteja foaia de lucru.

Sub ProtectAllSheets () Dim ws Ca foaie de lucru Dim password As String password = "Test123" 'înlocuiți Test123 cu parola pe care o doriți

Următorul cod ar proteja toate foile dintr-o singură dată.

Sub ProtectAllSheets () Dim ws Ca foaie de lucru Dim password As String password = "Test123" 'înlocuiți Test123 cu parola pe care ați folosit-o în timp ce protejați Pentru fiecare ws din foile de lucru ws.Protejați parola: = parola Următoarea ws Sfârșitul sub

Crearea unui cuprins al tuturor foilor de lucru (cu hyperlinkuri)

Dacă aveți un set de foi de lucru în registrul de lucru și doriți să inserați rapid o foaie de rezumat care să conțină linkurile către toate foile, puteți utiliza codul de mai jos.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" Pentru i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i) .Name & "! A1", _ TextToDisplay: = Foi de lucru (i) .Nume Următor i Sfârșit Sub

Codul de mai sus introduce o nouă foaie de lucru și o numește Index.

Apoi parcurge toate foile de lucru și creează un hyperlink pentru toate foile de lucru din foaia Index.

Unde să puneți codul VBA

Vă întrebați unde merge codul VBA în registrul dvs. de lucru Excel?

Excel are un backend VBA numit editor VBA. Trebuie să copiați și să lipiți codul în fereastra de cod a modulului VB Editor.

Iată pașii pentru a face acest lucru:

1. Accesați fila Dezvoltator.
2. Faceți clic pe opțiunea Visual Basic. Aceasta va deschide editorul VB în backend.
3. În panoul Explorator de proiecte din Editorul VB, faceți clic dreapta pe orice obiect pentru registrul de lucru în care doriți să inserați codul. Dacă nu vedeți Project Explorer, accesați fila View și faceți clic pe Project Explorer.
4. Accesați Insert și faceți clic pe Module. Aceasta va insera un obiect modul pentru registrul dvs. de lucru.
5. Copiați și lipiți codul în fereastra modulului.

Vă pot plăcea, de asemenea, următoarele tutoriale Excel VBA:

• Lucrul cu registrele de lucru folosind VBA.
• Utilizarea declarațiilor IF Then Else în VBA.
• Pentru Next Loop în VBA.
• Crearea unei funcții definite de utilizator în Excel.
• Cum să înregistrați o macro în Excel.
• Cum se execută o macro în Excel.
• Evenimente Excel VBA - Un ghid ușor (și complet).
• Cum se creează un program de completare în Excel.
• Cum să salvați și să refolosiți macrocomenzile utilizând manualul de lucru Excel personal Macro.