Pentru a lucra eficient în VBA, trebuie să înțelegeți buclele.
Buclele vă permit să repetați un bloc de cod de un număr stabilit de ori sau să repetați un bloc de cod pe fiecare obiect dintr-un set de obiecte.
Mai întâi vă vom arăta câteva exemple pentru a vă arăta de ce sunt capabile buclele. Apoi vă vom învăța totul despre bucle.
Exemple rapide de buclă VBA
Pentru fiecare buclă
Pentru fiecare buclă parcurge fiecare obiect dintr-o colecție, cum ar fi fiecare foaie de lucru din registrul de lucru sau fiecare celulă dintr-un interval.
Buclați prin toate foile de lucru din registrul de lucru
Acest cod va parcurge toate foile de lucru din registrul de lucru, dezvăluind fiecare foaie:
| 12345678 | Sub LoopThroughSheets ()Dim ws Ca foaie de lucruPentru fiecare ws din foi de lucruws.Visible = AdevăratUrmătorulSfârșitul Sub |
Buclați prin toate celulele din raza de acțiune
Acest cod va parcurge o serie de celule, testând dacă valoarea celulei este negativă, pozitivă sau zero:
| 1234567891011121314 | Sub If_Loop ()Dim Cell ca RangePentru fiecare celulă din interval („A2: A6”)Dacă Cell.Value> 0 AtunciCell.Offset (0, 1) .Value = "Pozitiv"ElseIf Cell.Value <0 ApoiCell.Offset (0, 1) .Value = "Negativ"AltfelCell.Offset (0, 1) .Value = "Zero"End IfUrmătoarea celulăSfârșitul Sub |
Pentru buclele următoare
Un alt tip de buclă „Pentru” este Bucla pentru următorul. Bucla pentru următorul vă permite să parcurgeți numerele întregi.
Acest cod va parcurge numerele întregi de la 1 la 10, afișând fiecare cu o casetă de mesaj:
| 123456 | Sub ForLoop ()Dim i IntegerPentru i = 1 până la 10MsgBox iApoi euSfârșitul Sub |
Faceți în timp ce bucle
Buclele Do While se vor bucla în timp ce se îndeplinește o condiție. Acest cod va parcurge, de asemenea, numerele întregi de la 1 la 10, afișând fiecare cu o casetă de mesaj.
| 12345678 | Sub DoWhileLoop ()Dim n Ca întregn = 1Faceți În timp ce n <11MsgBox nn = n + 1BuclăSfârșitul Sub |
Fă până la bucle
În schimb, Faceți până când buclele se vor bucla până când se îndeplinește o condiție. Acest cod face același lucru ca și cele două exemple anterioare.
| 12345678 | Sub DoUntilLoop ()Dim n Ca întregn = 1Faceți până la n> = 10MsgBox nn = n + 1BuclăSfârșitul Sub |
Vom discuta acest lucru mai jos, dar trebuie să fiți extrem de atenți atunci când creați bucle Do While sau Do Until, astfel încât să nu creați o buclă care nu se termină niciodată.
VBA Loop Builder
Aceasta este o captură de ecran a „Loop Builder” din programul nostru de completare Premium VBA: AutoMacro. Loop Builder vă permite să construiți rapid și ușor bucle pentru a parcurge diferite obiecte sau numere. Puteți efectua acțiuni pe fiecare obiect și / sau puteți selecta numai obiecte care îndeplinesc anumite criterii.
Suplimentul conține, de asemenea, mulți alți constructori de coduri, o bibliotecă extinsă de coduri VBA și un sortiment de instrumente de codare. Este obligatoriu pentru orice dezvoltator VBA.
Acum vom acoperi diferite tipuri de bucle în profunzime.
VBA pentru următoarea buclă
Pentru sintaxa buclei
Bucla pentru următorul vă permite să repetați un bloc de cod de un număr specificat de ori. Sintaxa este:
| 12345 | [Dim Counter ca întreg]Pentru Contor = Început până la sfârșit [Valoare pas][Fă ceva]Următorul [Contor] |
În cazul în care elementele dintre paranteze sunt opționale.
- [Dim Counter as Long] - Declară variabila contor. Necesar dacă Option Explicit este declarat în partea de sus a modulului.
- Tejghea - O variabilă întreagă utilizată pentru numărare
- start - Valoarea de pornire (Ex. 1)
- Sfârșit - Valoarea finală (Ex. 10)
- [Valoare pas] - Vă permite să numărați fiecare număr întreg în loc de fiecare număr întreg. De asemenea, puteți merge invers cu o valoare negativă (ex. Pasul -1)
- [Fă ceva] - Codul care se va repeta
- Următorul [Contor] - Declarație de închidere la For Next Loop. Puteți include Contorul sau nu. Cu toate acestea, recomand cu tărie includerea contorului, deoarece vă ușurează citirea codului.
Dacă este confuz, nu vă faceți griji. Vom trece în revistă câteva exemple:
Numărați până la 10
Acest cod va fi numărat până la 10 folosind un buclă pentru următoarea:
| 12345678 | Sub ForEach_CountTo10 ()Dim n Ca întregPentru n = 1 până la 10MsgBox nUrmătorul nSfârșitul Sub |
Pentru Loop Step
Numărați până la 10 - Numai Numere Pare
Acest cod va conta până la 10 numărând doar numerele pare:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n Ca întregPentru n = 2 - 10 Pasul 2MsgBox nUrmătorul nSfârșitul Sub |
Observați că am adăugat „Pasul 2”. Acest lucru îi spune For Loop să „pășească” prin contor până la 2. Putem folosi, de asemenea, o valoare de pas negativă pentru a face pasul invers:
Pentru Pasul buclei - invers
Numărătoarea inversă de la 10
Acest cod va face numărătoarea inversă de la 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n Ca întregPentru n = 10 la 1 Pasul -1MsgBox nUrmătorul nMsgBox „Ridicați-vă”Sfârșitul Sub |
Ștergeți rândurile dacă celula este necompletată
Cel mai frecvent am folosit un pas negativ For-Loop pentru a parcurge intervalele de celule, ștergând rânduri care îndeplinesc anumite criterii. Dacă faceți o buclă de la rândurile de sus la rândurile de jos, pe măsură ce ștergeți rândurile, vă veți încurca contorul.
Acest exemplu va șterge rândurile cu celule goale (începând cu rândul de jos):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n Ca întregPentru n = 10 la 1 Pasul -1Dacă Range ("a" & n) .Value = "" AtunciRange („a” & n) .EntireRow.EleteEnd IfUrmătorul nSfârșitul Sub |
Cuibărit pentru buclă
Puteți „cuibări” unul pentru buclă în interiorul altui pentru buclă. Vom folosi Nested For Loops pentru a crea un tabel de înmulțire:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable ()Reduceți rândul ca întreg, col ca întregPentru rând = 1 până la 9Pentru col = 1 la 9Celule (rând + 1, col + 1). Valoare = rând * colUrmătoarea colUrmătorul rândSfârșitul Sub |
Ieșiți pentru
Instrucțiunea Exit For vă permite să ieșiți imediat dintr-o buclă For Next.
De obicei, veți utiliza Exit For împreună cu o Instrucțiune If, ieșind din For Next Loop dacă este îndeplinită o anumită condiție.
De exemplu, puteți utiliza o buclă pentru a găsi o celulă. Odată ce acea celulă este găsită, puteți ieși din buclă pentru a accelera codul.
Acest cod va parcurge rândurile de la 1 la 1000, căutând „eroare” în coloana A. Dacă este găsit, codul va selecta celula, vă va avertiza cu privire la eroarea găsită și va ieși din buclă:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i IntegerPentru i = 1 până la 1000Dacă Range ("A" & i) .Value = "eroare" AtunciGama („A” & i). SelectațiMsgBox „Eroare găsită”Ieșiți pentruEnd IfApoi euSfârșitul Sub |
Important: În cazul cuibărit pentru bucle, Ieșire pentru iese doar din curent pentru buclă, nu toate buclele active.
Continuați pentru
VBA nu are comanda „Continue” care se găsește în Visual Basic. În schimb, va trebui să utilizați „Exit”.
VBA pentru fiecare buclă
VBA pentru fiecare buclă va parcurge toate obiectele dintr-o colecție:
- Toate celulele dintr-un interval
- Toate foile de lucru dintr-un registru de lucru
- Toate formele dintr-o foaie de lucru
- Toate registrele de lucru deschise
De asemenea, puteți utiliza Nested For Each Loops pentru:
- Toate celulele dintr-un interval pe toate foile de lucru
- Toate formele de pe toate foile de lucru
- Toate foile din toate registrele de lucru deschise
- si asa mai departe…
Sintaxa este:
| 123 | Pentru fiecare obiect din colecție[Fă ceva]Următorul [Obiect] |
Unde:
- Obiect - Variabilă care reprezintă o gamă, o foaie de lucru, un registru de lucru, o formă etc. (ex. Rng)
- Colectie - Colecție de obiecte (ex. Range („a1: a10”)
- [Fă ceva] - Bloc de cod pentru a rula pe fiecare obiect
- Următorul [Obiect] - Declarație de încheiere. [Obiectul] este opțional, deși este foarte recomandat.
Pentru fiecare celulă din interval
Acest cod va parcurge fiecare celulă dintr-un interval:
| 123456789 | Sub ForEachCell_inRange ()Dim celula ca intervalPentru fiecare celulă din interval ("a1: a10")cell.Value = cell.Offset (0,1) .ValueUrmătoarea celulăSfârșitul Sub |
Pentru fiecare foaie de lucru din registrul de lucru
Acest cod va parcurge toate foile de lucru dintr-un registru de lucru, neprotejând fiecare foaie:
| 123456789 | Sub ForEachSheet_inWorkbook ()Dim ws Ca foaie de lucruPentru fiecare ws din foi de lucruws.Defectați „parola”Următorul wsSfârșitul Sub |
Pentru fiecare registru de lucru deschis
Acest cod va salva și închide toate registrele de lucru deschise:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Ca registru de lucruPentru fiecare wb din registrele de lucruwb.Închideți SaveChanges: = TrueUrmătorul wbSfârșitul Sub |
Pentru fiecare formă din foaia de lucru
Acest cod va șterge toate formele din foaia activă.
| 123456789 | Sub ForEachShape ()Dim shp Ca formăPentru fiecare shp din ActiveSheet.ShapesștergețiUrmătorul shpSfârșitul Sub |
Pentru fiecare formă din fiecare foaie de lucru din registrul de lucru
De asemenea, puteți face cuiburi pentru fiecare buclă. Aici vom parcurge toate formele din toate foile de lucru din registrul de lucru activ:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp ca formă, ws ca foaie de lucruPentru fiecare ws din foi de lucruPentru fiecare shp In cu formeștergețiUrmătorul shpUrmătorul wsSfârșitul Sub |
Pentru fiecare - buclă IF
După cum am menționat anterior, puteți utiliza o instrucțiune If într-o buclă, efectuând acțiuni numai dacă sunt îndeplinite anumite criterii.
Acest cod va ascunde toate rândurile goale dintr-un interval:
| 12345678910 | Sub ForEachCell_inRange ()Dim celula ca intervalPentru fiecare celulă din interval ("a1: a10")If cell.Value = "" Atunci _cell.EntireRow.Hidden = AdevăratUrmătoarea celulăSfârșitul Sub |
VBA Do While Loop
VBA Do While și Do Until (vezi secțiunea următoare) sunt foarte asemănătoare. Vor repeta o buclă în timp ce (sau până) se îndeplinește o condiție.
Bucla de a face în timp ce va repeta o buclă în timp ce se îndeplinește o condiție.
Iată sintaxa Do While:
| 123 | Faceți în condiții[Fă ceva]Buclă |
Unde:
- Condiție - Condiția de testat
- [Fă ceva] - Blocul de cod de repetat
De asemenea, puteți configura o buclă Do While cu condiția la sfârșitul buclei:
| 123 | Do[Fă ceva]Buclați în condiții |
Vom demonstra fiecare și vom arăta în ce fel diferă:
Face în timp ce
Iată exemplul de buclă Do While pe care l-am demonstrat anterior:
| 12345678 | Sub DoWhileLoop ()Dim n Ca întregn = 1Faceți În timp ce n <11MsgBox nn = n + 1BuclăSfârșitul Sub |
Bucla În timp ce
Acum să rulăm aceeași procedură, cu excepția faptului că vom muta starea la sfârșitul buclei:
| 12345678 | Sub DoLoopWhile ()Dim n Ca întregn = 1DoMsgBox nn = n + 1Bucla În timp ce n <11Sfârșitul Sub |
VBA Fă până la buclă
Faceți până când buclele vor repeta o buclă până când se îndeplinește o anumită condiție. Sintaxa este în esență aceeași cu buclele Do While:
| 123 | Fă până la condiție[Fă ceva]Buclă |
și în mod similar, condiția poate merge la începutul sau la sfârșitul buclei:
| 123 | Do[Fă ceva]Buclați până la condiție |
Fă până
Acest lucru până când bucla va conta până la 10, la fel ca exemplele noastre anterioare
| 12345678 | Sub DoUntilLoop ()Dim n Ca întregn = 1Faceți până la n> 10MsgBox nn = n + 1BuclăSfârșitul Sub |
Bucla până
Această buclă până la buclă va conta până la 10:
| 12345678 | Sub DoLoopUntil ()Dim n Ca întregn = 1DoMsgBox nn = n + 1Bucla până la n> 10Sfârșitul Sub |
Ieșiți din buclă
Similar cu utilizarea Exit For pentru a ieși dintr-un For Loop, utilizați comanda Exit Do pentru a ieși imediat dintr-un Loop
| 1 | Ieșiți Do |
Iată un exemplu de Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i Integeri = 1Faceți până la i> 1000Dacă Range ("A" & i) .Value = "eroare" AtunciGama („A” & i). SelectațiMsgBox „Eroare găsită”Ieșiți DoEnd Ifi = i + 1BuclăSfârșitul Sub |
End sau Break Loop
După cum am menționat mai sus, puteți utiliza Exit For sau Exit Do pentru a ieși din bucle:
| 1 | Ieșiți pentru |
| 1 | Ieșiți Do |
Cu toate acestea, aceste comenzi trebuie adăugate la cod înainte de a rula bucla.
Dacă încercați să „rupeți” o buclă care rulează în prezent, puteți încerca să apăsați ESC sau CTRL + Pauză pauză pe tastatură. Cu toate acestea, este posibil să nu funcționeze. Dacă nu funcționează, va trebui să așteptați ca bucla să se termine sau, în cazul unei bucle nesfârșite, să o utilizați CTRL + ALT + Șterge pentru a forța închiderea Excel.
Acesta este motivul pentru care încerc să evit buclele Do, este mai ușor să creez din greșeală o buclă nesfârșită care te obligă să repornești Excel, pierzându-ți potențial munca.
Mai multe exemple de bucle
Buclați prin rânduri
Aceasta va parcurge toate rândurile dintr-o coloană:
| 123456789 | Public Sub LoopThroughRows ()Dim celula ca intervalPentru fiecare celulă din interval ("A: A")Ff cell.value "" apoi MsgBox cell.address & ":" & cell.valueUrmătoarea celulăSfârșitul Sub |
Buclați prin coloane
Aceasta va parcurge toate coloanele dintr-un rând:
| 123456789 | Public Sub LoopThroughColumns ()Dim celula ca intervalPentru fiecare celulă din interval („1: 1”)Dacă cell.Value "" Atunci MsgBox cell.Address & ":" & cell.ValueUrmătoarea celulăSfârșitul Sub |
Buclați prin fișiere într-un folder
Acest cod va parcurge toate fișierele dintr-un folder, creând o listă:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO Ca obiectReduceți folderul ca obiectDim oFile ca obiectDim i IntegerSetați oFSO = CreateObject ("Scripting.FileSystemObject")Setați oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Pentru fiecare oFile din oFolder.FilesRange ("A" & i) .value = oFile.Namei = i + 1Următorul oFisierSfârșitul Sub |
Buclă prin matrice
Acest cod va parcurge matricea „arrList”:
| 123 | Pentru i = LBound (arrList) To UBound (arrList)MsgBox arrList (i)Apoi eu |
Funcția LBound obține „limita inferioară” a matricei, iar UBound primește „limita superioară”.
Bucle în Access VBA
Majoritatea exemplelor de mai sus vor funcționa și în Access VBA. Cu toate acestea, în Access, parcurgem obiectul Recordset, mai degrabă decât obiectul Range.
| 123456789101112131415161718 | Sub LoopThroughRecords ()La eroare Reluați în continuareDim dbs ca bază de dateDim rst As RecordsetSetați dbs = CurrentDbSetați rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Cu prima.MoveLast.MoveFirstFă până .EOF = AdevăratMsgBox (primele câmpuri („ClientName”)).MoveNextBuclăSe termina cumai întâi ÎnchideSet rst = NimicSet dbs = NothingSfârșitul Sub |
