Pentru a profita la maximum de Excel și VBA, trebuie să știți cum să utilizați buclele în mod eficient.
În VBA, buclele vă permit să parcurgeți un set de obiecte / valori și să le analizați unul câte unul. De asemenea, puteți efectua sarcini specifice pentru fiecare buclă.
Iată un exemplu simplu de utilizare a buclelor VBA în Excel.
Să presupunem că aveți un set de date și doriți să evidențiați toate celulele în rânduri uniforme. Puteți utiliza o buclă VBA pentru a parcurge intervalul și a analiza numărul fiecărui rând de celulă. Dacă se dovedește a fi uniform, îi dai o culoare, altfel o lași așa cum este.
Acum, desigur, este foarte simplu de a face looping în Excel VBA (și puteți face acest lucru și folosind formatarea condiționată).
În viața reală, puteți face mult mai mult cu buclele VBA în Excel, care vă pot ajuta să automatizați sarcinile.
Iată câteva exemple mai practice în care buclele VBA pot fi utile:
- Cercetând printr-o gamă de celule și analizând fiecare celulă (evidențiați celulele cu un text specific în ea).
- Parcurgeți toate foile de lucru și faceți ceva cu fiecare (cum ar fi protejarea / neprotejarea).
- Buclați toate registrele de lucru deschise (și salvați fiecare registru de lucru sau închideți toate, cu excepția registrului de lucru activ).
- Buclați toate caracterele dintr-o celulă (și extrageți partea numerică dintr-un șir).
- Buclați prin toate valorile o matrice.
- Parcurgeți toate diagramele / obiectele (și dați o margine sau modificați culoarea de fundal).
Acum, pentru a utiliza cel mai bine buclele în Excel VBA, trebuie să știți despre diferitele tipuri care există și sintaxa corectă a fiecăruia.
În acest tutorial, voi prezenta diferite tipuri de bucle Excel VBA și voi acoperi câteva exemple pentru fiecare buclă
Notă: Acesta va fi un tutorial imens, unde voi încerca să acoper fiecare buclă VBA în detaliu. Vă recomand să marcați această pagină pentru referințe viitoare.Dacă sunteți interesat să învățați VBA într-un mod ușor, consultați Instruire online VBA Excel.
Pentru Next Loop
Bucla „Pentru următorul” vă permite să parcurgeți un bloc de cod pentru numărul specificat de ori.
De exemplu, dacă vă cer să adăugați numerele întregi de la 1 la 10 manual, veți adăuga primele două numere, apoi adăugați al treilea număr la rezultat, apoi adăugați al patrulea număr la rezultat, așa mai departe …
Nu-i așa?
Aceeași logică este utilizată în bucla For Next din VBA.
Specificați de câte ori doriți să ruleze bucla și, de asemenea, specificați ce doriți să faceți codul de fiecare dată când se rulează bucla.
Mai jos este sintaxa buclei For Next:
For Counter = Start To End [Step Value] [Code Block to Execute] Next [counter]
În bucla Pentru Următorul, puteți utiliza un Contor (sau orice variabilă) care va fi utilizat pentru a rula bucla. Acest contor vă permite să rulați această buclă de un număr necesar de ori.
De exemplu, dacă vreau să adaug primele 10 numere întregi pozitive, atunci valoarea contorului meu ar fi de la 1 la 10.
Să aruncăm o privire la câteva exemple pentru a înțelege mai bine cum funcționează bucla For Next.
Exemplul 1 - Adăugarea primelor 10 numere întregi pozitive
Mai jos este codul care va adăuga primele 10 numere întregi pozitive folosind o buclă For Next.
Apoi va afișa o casetă de mesaj care arată suma acestor numere.
Numere adunări secundare () Număr total ca număr întreg Dim Număr ca număr întreg = 0 Pentru numărare = 1 până la 10 Total = Total + Numărare Număr următor MsgBox Total sfârșit Sub
În acest cod, valoarea Total este setată la 0 înainte de a intra în bucla Pentru următorul.
Odată ce intră în buclă, deține valoarea totală după fiecare buclă. Deci, după prima buclă, când Contorul este 1, valoarea „Total” devine 1, iar după a doua buclă devine 3 (1 + 2) și așa mai departe.
Și, în sfârșit, când bucla se termină, variabila „Total” are suma primelor 10 numere întregi pozitive.
O MsgBox afișează apoi rezultatul într-o casetă de mesaj.
Exemplul 2 - Adăugarea primelor 5 numere întregi chiar pozitive
Pentru a însuma primele cinci numere întregi pozitive (adică 2,4,6,8 și 10), aveți nevoie de un cod similar cu o condiție pentru a lua în considerare doar numerele pare și a ignora numerele impare.
Iată un cod care o va face:
Sub AddEvenNumbers () Dim Total ca întreg Numerat Numărat ca număr întreg = 0 Pentru numărare = 2 până la 10 Pasul 2 Total = Total + Numărare Număr următor MsgBox Total sfârșit Sub
Rețineți că am început valoarea Count din 2 și am folosit și „Pasul 2‘.
Când folosești 'Pasul 2', îi spune codului să incrementeze valoarea „Count” cu 2 de fiecare dată când se rulează bucla.
Deci, valoarea Count începe de la 2 și apoi devine 4, 6, 8 și 10 pe măsură ce are loc buclarea.
NOTĂ: O altă modalitate de a face acest lucru ar putea fi rularea buclei de la 1 la 10 și verificarea în cadrul buclei dacă numărul este par sau impar. Cu toate acestea, utilizarea Step, în acest caz, este un mod mai eficient, deoarece nu necesită ca bucla să ruleze de 10 ori, ci doar de 5 ori.
Valoarea Step poate fi, de asemenea, negativă. În astfel de cazuri, Contorul începe la o valoare mai mare și continuă să fie decrementat de valoarea specificată a Pasului.
Exemplul 3 - Introducerea numărului de serie în celulele selectate
De asemenea, puteți utiliza bucla Pentru următorul pentru a parcurge o colecție de obiecte (cum ar fi celule sau foi de lucru sau registre de lucru),
Iată un exemplu care introduce rapid numere de serie în toate celulele selectate.
Sub EnterSerialNumber () Dim Rng Ca Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset (Counter - 1, 0) .Value = Counter Next Counter End Sub
Codul de mai sus contorizează mai întâi numărul de rânduri selectate și apoi atribuie această valoare variabilei RowCount. Apoi rulăm bucla de la „1 la RowCount”.
De asemenea, rețineți că, deoarece selecția poate fi orice număr de rânduri, am setat variabila Rng la Selecție (cu linia „Set Rng = Selection”). Acum putem folosi variabila „Rng” pentru a ne referi la selecția din codul nostru.
Exemplul 4 - Protejați toate foile de lucru din registrul de lucru activ
Puteți utiliza bucla „Pentru următorul” pentru a parcurge toate foile de lucru din registrul de lucru activ și pentru a proteja (sau a proteja) fiecare dintre foile de lucru.
Mai jos este codul care va face acest lucru:
Sub ProtectWorksheets () Dim i Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets (i). Protejați Următorul i End Sub
Codul de mai sus contorizează numărul de foi folosind ActiveWorkbook.Worksheets.Count. Aceasta îi spune VBA de câte ori trebuie rulată bucla.
În fiecare caz, se referă la registrul de lucru Ith (folosind foi de lucru (i)) și îl protejează.
Puteți utiliza același cod și pentru a Proteja foile de lucru. Doar schimbă linia Fișe de lucru (i). Protejați la Fișe de lucru (i) .UnProtect.
Bucle imbricate „Pentru următorul”
Puteți utiliza bucle imbricate „Pentru următorul” pentru a realiza automatizări mai complexe în Excel. O buclă „Pentru următorul” imbricată ar însemna că există o buclă „Pentru următorul” în cadrul unei bucle „Pentru următorul”.
Permiteți-mi să vă arăt cum să utilizați acest lucru folosind un exemplu.
Să presupunem că am 5 registre de lucru deschise în sistemul meu și vreau să protejez toate foile de lucru din toate aceste registre de lucru.
Mai jos este codul care va face acest lucru:
Sub ProtectWorksheets () Dim i As Integer Dim j As Integer For i = 1 To Workbooks.Count for j = 1 To Workbooks (i) .Worksheets.Count Workbooks (i) .Worksheets (j). Protejați Următorul j Următorul i End Sub
Cele de mai sus sunt o buclă imbricată pentru Next, deoarece am folosit una pentru bucla Next în cadrul alteia.
Declarațiile „EXIT For” din For Next Loops
Instrucțiunea „Exit For” vă permite să ieșiți complet din bucla „For Next”.
Îl puteți folosi în cazurile în care doriți ca bucla Pentru următorul să se încheie atunci când este îndeplinită o anumită condiție.
Să luăm un exemplu în care aveți un set de numere în coloana A și doriți să evidențiați toate numerele negative cu font roșu. În acest caz, trebuie să analizăm fiecare celulă pentru valoarea sa și apoi să schimbăm culoarea fontului în consecință.
Dar pentru a face codul mai eficient, putem verifica mai întâi dacă există sau nu valori negative în listă. Dacă nu există valori negative, putem folosi declarația Exit For pentru a ieși pur și simplu din cod.
Mai jos este codul care face acest lucru:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Counter = Rng.Count For i = 1 To Counter If WorksheetFunction.Min (Rng)> = 0 Apoi Ieșiți pentru Dacă Rng (i). Valoare <0 Apoi Rng (i) .Font.Color = vbRed Next i End Sub
Când utilizați instrucțiunea „Exit For” într-o buclă imbricată „For Next”, aceasta va ieși din bucla în care este executată și va continua să execute următoarea linie din cod după bucla For Next.
De exemplu, în codul de mai jos, instrucțiunea „Exit For” vă va scoate din bucla interioară, dar bucla exterioară ar continua să funcționeze.
Sub SampleCode () Pentru i = 1 până la 10 Pentru j = 1 până la 10 Ieșiți pentru Următorul J Următorul i Sfârșit Sub
Faceți în timp ce buclați
O buclă „Do While” vă permite să verificați dacă există o condiție și să rulați bucla în timp ce respectiva condiție este îndeplinită (sau este ADEVĂRATĂ).
Există două tipuri de sintaxă în bucla Do While.
Faceți [Condiție În timp ce] [Blocare cod pentru Executare] Buclă
și
Faceți [Blocare cod pentru a executa] Buclă [Condiție în timp ce]
Diferența dintre aceste două constă în faptul că în primul, starea While este verificată mai întâi înainte de executarea oricărui bloc de cod, iar în al doilea caz, blocul de cod este executat mai întâi și apoi se verifică starea While.
Aceasta înseamnă că dacă condiția While este Falsă sunt ambele cazuri, codul va rula în continuare cel puțin o dată în al doilea caz (deoarece condiția „While” este verificată după ce codul a fost executat o dată).
Acum să vedem câteva exemple de utilizare a buclelor Do While în VBA.
Exemplul 1 - Adăugați primele 10 numere întregi pozitive folosind VBA
Să presupunem că doriți să adăugați primele zece numere întregi pozitive folosind bucla Do While din VBA.
Pentru a face acest lucru, puteți utiliza bucla Do While până când următorul număr este mai mic sau egal cu 10. De îndată ce numărul este mai mare de 1o, bucla dvs. se va opri.
Iată codul VBA care va rula această buclă Do While și va afișa rezultatul într-o casetă de mesaj.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Bucla de mai sus continuă să funcționeze până când valoarea „i” devine 11. De îndată ce devine 11, bucla se termină (deoarece condiția While devine False).
În cadrul buclei, am folosit o variabilă de rezultat care deține valoarea finală Odată ce bucla este finalizată, o casetă de mesaj arată valoarea variabilei „Rezultat”.
Exemplul 2 - Introduceți datele pentru luna curentă
Să presupunem că doriți să introduceți toate datele lunii curente într-o coloană de foaie de lucru.
Puteți face acest lucru utilizând următorul cod de buclă Do While:
Sub EnterCurrentMonthDates () Dim CMDate as Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Codul de mai sus va introduce toate datele în prima coloană a foii de lucru (începând de la A1). Buclele continuă până când valoarea lunară a variabilei „CMDate” se potrivește cu cea din luna curentă.
Declarație de ieșire
Puteți utiliza instrucțiunea Exit Do pentru a ieși din buclă. De îndată ce codul execută linia „Exit Do”, acesta iese din bucla Do While și trece controlul la următoarea linie imediat după buclă.
De exemplu, dacă doriți să introduceți numai primele 10 date, puteți ieși din buclă imediat ce sunt introduse primele 10 date.
Codul de mai jos va face acest lucru:
Sub EnterCurrentMonthDates () Dim CMDate as Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 Dacă i> = 10 Exit Do Do CMDate = CMDate + 1 Loop End Sub
În codul de mai sus, instrucțiunea IF este utilizată pentru a verifica dacă valoarea lui i este mai mare de 10 sau nu. De îndată ce valoarea „i” devine 10, instrucțiunea Exit Do este executată și bucla se termină.
Fă până la buclă
Buclele „Do Until” seamănă foarte mult cu buclele „Do While”.
În „Faceți în timp ce”, bucla rulează până când este îndeplinită condiția dată, în timp ce în „Faceți până”, se bucură până când este îndeplinită condiția specificată.
Există două tipuri de sintaxă în Buclă până la buclă.
Faceți [Până la condiție] [Blocare cod pentru Executare] Buclă
și
Faceți [Blocare cod pentru executare] Buclă [Până la condiție]
Diferența dintre aceste două constă în faptul că în primul, condiția Until este verificată mai întâi înainte de executarea oricărui bloc de cod și, în al doilea caz, blocul de cod este executat mai întâi și apoi se verifică condiția Until.
Aceasta înseamnă că dacă condiția Until este TRUE este ambele cazuri, codul va rula în continuare cel puțin o dată în al doilea caz (deoarece condiția „Until” este verificată după ce codul a fost executat o dată).
Acum să vedem câteva exemple de utilizare a buclelor Do Until în VBA.
Notă: Toate exemplele pentru Do Until sunt aceleași cu cele ale Do While. Acestea au fost modificate pentru a vă arăta cum funcționează bucla Do Until.
Exemplul 1 - Adăugați primele 10 numere întregi pozitive folosind VBA
Să presupunem că doriți să adăugați primele zece numere întregi pozitive folosind bucla Do Until în VBA.
Pentru a face acest lucru, trebuie să rulați bucla până când următorul număr este mai mic sau egal cu 10. De îndată ce numărul este mai mare de 1o, bucla dvs. se va opri.
Iată codul VBA care va rula această buclă și va afișa rezultatul într-o casetă de mesaj.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do Until i> 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Bucla de mai sus continuă să funcționeze până când valoarea „i” devine 11. Imediat ce devine 11, bucla se termină (pe măsură ce condiția „Până” devine True).
Exemplul 2 - Introduceți datele pentru luna curentă
Să presupunem că doriți să introduceți toate datele lunii curente într-o coloană de foaie de lucru.
Puteți face acest lucru utilizând următorul cod de buclă Do Until:
Sub EnterCurrentMonthDates () Dim CMDate as Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do Until Month (CMDate) Month (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Codul de mai sus va introduce toate datele în prima coloană a foii de lucru (începând de la A1). Bucla continuă până când Luna variabilei CMDate nu este egală cu cea a lunii curente.
Declarație de ieșire
Puteți utiliza instrucțiunea „Exit Do” pentru a ieși din buclă.
De îndată ce codul execută linia „Exit Do”, acesta iese din bucla Do Until și trece controlul la următoarea linie imediat după buclă.
De exemplu, dacă doriți să introduceți numai primele 10 date, puteți ieși din buclă imediat ce sunt introduse primele 10 date.
Codul de mai jos va face acest lucru:
Sub EnterCurrentMonthDates () Dim CMDate as Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do Until Month (CMDate) Month (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 Dacă i> = 10 Apoi ieșiți Do CMDate = CMDate + 1 Loop End Sub
În codul de mai sus, de îndată ce valoarea „i” devine 10, se execută declarația Exit Do și bucla se termină.
Pentru fiecare
În VBA, puteți parcurge un set de colecții folosind bucla „Pentru fiecare”.
Iată câteva exemple de colecții în Excel VBA:
- O colecție a tuturor registrelor de lucru deschise.
- O colecție a tuturor foilor de lucru dintr-un registru de lucru.
- O colecție de toate celulele dintr-o gamă de celule selectate.
- O colecție cu toate diagramele sau formele din registrul de lucru.
Folosind bucla „Pentru fiecare”, puteți parcurge fiecare dintre obiectele dintr-o colecție și puteți efectua unele acțiuni asupra acesteia.
De exemplu, puteți parcurge toate foile de lucru dintr-un registru de lucru și le puteți proteja sau puteți parcurge toate celulele din selecție și puteți modifica formatarea.
Cu bucla „Pentru fiecare” (denumită și bucla „Pentru fiecare-următoare”), nu trebuie să știți câte obiecte există într-o colecție.
Bucla „Pentru fiecare” va trece automat prin fiecare obiect și va efectua acțiunea specificată. De exemplu, dacă doriți să protejați toate foile de lucru dintr-un registru de lucru, codul ar fi același indiferent dacă aveți un registru de lucru cu 3 foi de lucru sau 30 de foi de lucru.
Iată sintaxa buclei For Each-Next din Excel VBA.
Pentru fiecare element din colecție [Blocare cod pentru executare] Următorul [element]
Acum să vedem câteva exemple de utilizare pentru fiecare buclă în Excel.
Exemplul 1 - Parcurgeți toate foile de lucru dintr-un registru de lucru (și protejați-l)
Să presupunem că aveți un registru de lucru în care doriți să protejați toate foile de lucru.
Mai jos pentru bucla următoare bucla puteți face acest lucru cu ușurință:
Sub ProtectSheets () Dim ws Ca foaie de lucru pentru fiecare ws din ActiveWorkbook. Foi de lucru ws.Protect Next ws End Sub
În codul de mai sus, am definit variabila „ws” ca obiect de foaie de lucru. Aceasta îi spune VBA că „ws” ar trebui interpretat ca un obiect foaie de lucru în cod.
Acum folosim declarația „Pentru fiecare” pentru a parcurge fiecare „ws” (care este un obiect foaie de lucru) în colecția tuturor foilor de lucru din registrul de lucru activ (oferit de ActiveWorkbook.Worksheets).
Rețineți că, spre deosebire de alte bucle în care am încercat să protejăm toate foile de lucru dintr-un registru de lucru, aici nu trebuie să ne facem griji cu privire la câte foi de lucru există în registrul de lucru.
Nu este nevoie să le numărăm pentru a rula bucla. Fiecare buclă asigură că toate obiectele sunt analizate unul câte unul.
Exemplul 2 - Parcurgeți toate registrele de lucru deschise (și Salvați toate)
Dacă lucrați cu mai multe cărți de lucru în același timp, poate fi util să puteți salva toate aceste cărți de lucru simultan.
Mai jos, codul VBA poate face acest lucru pentru noi:
Sub SaveAllWorkbooks () Dim wb Ca registru de lucru pentru fiecare wb din Workbooks wb.Save Next wb End Sub
Rețineți că în acest cod nu primiți nicio solicitare care vă solicită să salvați registrul de lucru într-o anumită locație (dacă îl salvați pentru prima dată).
Îl salvează în folderul implicit (a fost dosarul „Documente” în cazul meu). Acest cod funcționează cel mai bine atunci când aceste fișiere sunt deja salvate și faceți modificări și doriți să salvați rapid toate registrele de lucru.
Exemplul 3 - Parcurgeți toate celulele dintr-o selecție (Evidențiați valorile negative)
Folosind bucla „Pentru fiecare”, puteți parcurge toate celulele dintr-un anumit interval sau din intervalul selectat.
Acest lucru poate fi util atunci când doriți să analizați fiecare celulă și să efectuați o acțiune bazată pe aceasta.
De exemplu, mai jos este codul care va trece prin toate celulele din selecție și va schimba culoarea celulei celulelor cu valori negative în roșu.
Sub HighlightNegativeCells () Dim Cll ca interval pentru fiecare Cll din selecție Dacă Cll.Value <0 Apoi Cll.Interior.Color = vbRed End Dacă următorul Cll End Sub
(Rețineți că am folosit Cll ca nume scurt de variabilă pentru celulă. Este recomandabil să nu folosiți nume de obiecte precum Foi de calcul sau Interval ca nume de variabile)
În codul de mai sus, bucla For Each-Next trece prin colecția de celule din selecție. Instrucțiunea IF este utilizată pentru a identifica dacă valoarea celulei este negativă sau nu. În cazul în care este, celulei i se dă o culoare interioară roșie, altfel merge la celula următoare.
În cazul în care nu aveți o selecție și, în schimb, doriți ca VBA să selecteze toate celulele umplute dintr-o coloană, începând de la o celulă specifică (la fel cum folosim tasta Control + Shift + Săgeată în jos pentru a selecta toate celulele umplute), puteți folosiți codul de mai jos:
Sub HighlightNegativeCells () Dim Cll Ca Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Pentru fiecare Cll din Rng Dacă Cll.Value <0 Apoi Cll.Interior.Color = vbRed End If Next Cll End Sub
În exemplul de mai sus, nu contează câte celule umplute sunt acolo. Va începe de la celula A1 și va analiza toate celulele umplute adiacente din coloană.
De asemenea, nu este nevoie să selectați celula A1. Puteți selecta orice celulă îndepărtată și când rulează codul, va lua în considerare toate celulele din coloana A (începând de la A1) și va colora celulele negative.
Declarația „Ieșiți pentru”
Puteți utiliza instrucțiunea „Exit For” din bucla For Each-Next pentru a ieși din buclă. Acest lucru se face de obicei în cazul în care este îndeplinită o anumită condiție.
De exemplu, în Exemplul 3, pe măsură ce parcurgem un set de celule, poate fi mai eficient să verificăm dacă există sau nu valori negative. În cazul în care nu există valori negative, putem pur și simplu să ieșim din buclă și să economisim ceva timp de procesare VBA.
Mai jos este codul VBA care va face acest lucru:
Sub HighlightNegativeCells () Dim Cll ca interval pentru fiecare Cll din selecția If WorksheetFunction.Min (Selection)> = 0 Apoi Ieșiți pentru Dacă Cll.Value <0 Apoi Cll.Interior.Color = vbRed End Dacă următorul Cll End Sub
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:
- Accesați fila Dezvoltator.
- Faceți clic pe opțiunea Visual Basic. Aceasta va deschide editorul VB în backend.
- Î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.
- Accesați Insert și faceți clic pe Module. Aceasta va insera un obiect modul pentru registrul dvs. de lucru.
- Copiați și lipiți codul în fereastra modulului.
