Za učinkovit rad u VBA morate razumjeti petlje.
Petlje vam omogućuju ponavljanje bloka koda određeni broj puta ili ponavljanje bloka koda za svaki objekt u skupu objekata.
Prvo ćemo vam pokazati nekoliko primjera koji će vam pokazati za što su petlje sposobne. Tada ćemo vas naučiti sve o petljama.
Brzi primjeri petlje VBA
Za svaku petlju
Za svaku petlju petlja kroz svaki objekt u zbirci, kao što je svaki radni list u radnoj knjizi ili svaka ćelija u rasponu.
Prelistajte sve radne listove u radnoj svesci
Ovaj kôd će se provlačiti kroz sve radne listove u radnoj knjizi, otkrivajući svaki list:
| 12345678 | Sub LoopThroughSheets ()Zatamni kao radni listZa svaki ws u radnim listovimaws.Visible = IstinaSljedećiKraj podm |
Prođite kroz sve ćelije u dosegu
Ovaj kôd će se provlačiti kroz niz ćelija, provjeravajući je li vrijednost ćelije negativna, pozitivna ili nula:
| 1234567891011121314 | Sub If_Loop ()Prigušena ćelija kao rasponZa svaku ćeliju u rasponu ("A2: A6")Ako je Cell.Value> 0 TadaCell.Offset (0, 1) .Value = "Pozitivno"Ostala ćelija.Vrijednost <0 ZatimCell.Offset (0, 1) .Value = "Negativno"DrugoCell.Offset (0, 1) .Value = "Nula"Završi akoSljedeća ćelijaKraj podm |
Za sljedeće petlje
Druga vrsta petlje "Za" je petlja Za sljedeću. For Next Loop omogućuje vam da prolazite kroz cijele brojeve.
Ovaj kôd će se kretati kroz cijele brojeve od 1 do 10, prikazujući svaki s okvirom poruke:
| 123456 | Sub ForLoop ()Dim i Kao cijeli brojZa i = 1 do 10MsgBox iSljedeći iKraj podm |
Do While petlje
Do While petlje će se petljati dok je uvjet zadovoljen. Ovaj će kôd također prolaziti kroz cijele brojeve od 1 do 10, prikazujući svaki s okvirom za poruku.
| 12345678 | Sub DoWhileLoop ()Dim n kao cijeli brojn = 1Učinite Dok je n <11MsgBox nn = n + 1PetljaKraj podm |
Radite do petlji
Nasuprot tome, petlje Učinit će se sve dok se ne ispuni uvjet. Ovaj kôd radi isto što i prethodna dva primjera.
| 12345678 | Sub DoUntilLoop ()Dim n kao cijeli brojn = 1Učinite sve dok n> = 10MsgBox nn = n + 1PetljaKraj podm |
O tome ćemo raspravljati u nastavku, ali morate biti iznimno oprezni pri stvaranju petlji Do While ili Do Until kako ne biste stvorili neprekidnu petlju.
VBA graditelj petlji
Ovo je snimak zaslona „Loop Builder-a“ iz našeg Premium VBA dodatka: AutoMacro. Loop Builder omogućuje vam brzo i jednostavno stvaranje petlji za petlje kroz različite objekte ili brojeve. Možete izvesti radnje na svakom objektu i/ili odabrati samo objekte koji zadovoljavaju određene kriterije.
Dodatak također sadrži mnoge druge graditelje kodova, opsežnu biblioteku kodova VBA i niz alata za kodiranje. To mora imati svaki VBA programer.
Sada ćemo dubinski pokriti različite vrste petlji.
VBA za sljedeću petlju
Za sintaksu petlje
For Next Loop omogućuje ponavljanje bloka koda određeni broj puta. Sintaksa je:
| 12345 | [Zatamni brojač kao cijeli broj]Za brojač = Početak do kraja [Vrijednost koraka][Učini nešto]Sljedeći [brojač] |
Gdje su stavke u zagradama izborne.
- [Dim Counter as Long] - Deklarira varijablu brojača. Obavezno ako je opcija Explicit deklarirana na vrhu vašeg modula.
- Brojač - Cijela varijabla koja se koristi za brojanje
- Početak - Početna vrijednost (Primjer 1)
- Kraj - Krajnja vrijednost (Primjer 10)
- [Vrijednost koraka] - Omogućuje vam brojanje svakih n cijelih brojeva umjesto svakog 1 cijelog broja. Također možete krenuti unatrag s negativnom vrijednošću (npr. Korak -1)
- [Učini nešto] - Kôd koji će se ponoviti
- Sljedeći [brojač] - Završna izjava za narednu petlju. Možete uključiti brojač ili ne. Međutim, toplo preporučujem uključivanje brojača jer vam olakšava čitanje koda.
Ako je to zbunjujuće, ne brinite. Pregledat ćemo neke primjere:
Broji do 10
Ovaj će se kôd brojati do 10 pomoću petlje For-Next:
| 12345678 | Sub ForEach_CountTo10 ()Dim n kao cijeli brojZa n = 1 do 10MsgBox nDalje nKraj podm |
Za Loop Step
Broji do 10 - samo parni brojevi
Ovaj kôd broji do 10 računajući samo parne brojeve:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n kao cijeli brojZa n = 2 do 10 Korak 2MsgBox nDalje nKraj podm |
Primijetite da smo dodali "Korak 2". To govori da For Loop "korača" kroz brojač za 2. Također možemo koristiti negativnu vrijednost koraka za korak unatrag:
Za korak petlje - obrnuto
Odbrojavanje od 10
Ovaj kôd će odbrojavati od 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n kao cijeli brojZa n = 10 do 1 korak -1MsgBox nDalje nMsgBox "Lift Off"Kraj podm |
Izbriši retke ako je ćelija prazna
Najčešće sam koristio negativan korak For-Loop za petlje kroz raspone ćelija, brišući retke koji zadovoljavaju određene kriterije. Ako petljate iz gornjih redaka u donje redove, dok brišete retke, zabrljat ćete brojač.
Ovaj će primjer izbrisati retke s praznim ćelijama (počevši od donjeg retka):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n kao cijeli brojZa n = 10 do 1 korak -1Ako Range ("a" & n) .Value = "" ZatimRaspon ("a" & n) .EntireRow.DeleteZavrši akoDalje nKraj podm |
Ugniježđeno za petlju
Možete "ugnijezditi" jedan For Loop u drugi For Loop. Za izradu tablice množenja koristit ćemo Ugniježđene petlje:
| 1234567891011 | Pod ugniježđena_ForEach_MultiplicationTable ()Zatamni redak kao cijeli broj, stolnjak kao cijeli brojZa red = 1 do 9Za col = 1 do 9Ćelije (redak + 1, stupac + 1) .Vrijednost = redak * stupacSljedeći colSljedeći redKraj podm |
Izlaz za
Naredba Exit For omogućuje vam da odmah izađete iz petlje For Next.
Obično biste upotrijebili Izlaz za zajedno s Izjavom Ako izlazite iz Petlje za sljedeće ako je ispunjen određeni uvjet.
Na primjer, za pronalaženje ćelije možete upotrijebiti For Loop. Nakon što se ta ćelija pronađe, možete izaći iz petlje kako biste ubrzali kôd.
Ovaj kôd će se provlačiti kroz retke od 1 do 1000, tražeći "pogrešku" u stupcu A. Ako se pronađe, kôd će odabrati ćeliju, upozoriti vas na pronađenu pogrešku i izaći iz petlje:
| 12345678910111213 | PodizlazFor_Loop ()Dim i Kao cijeli brojZa i = 1 do 1000Ako Range ("A" & i) .Value = "error" TadaRaspon ("A" & i). OdaberiteMsgBox "Greška je pronađena"Izlaz zaZavrši akoSljedeći iKraj podm |
Važno: U slučaju ugniježđene for petlje, Exit For napušta samo trenutnu For Loop, a ne sve aktivne petlje.
Nastavite za
VBA nema naredbu "Nastavi" koja se nalazi u Visual Basicu. Umjesto toga morat ćete upotrijebiti “Exit”.
VBA za svaku petlju
VBA za svaku petlju petljat će kroz sve objekte u zbirci:
- Sve ćelije u rasponu
- Svi radni listovi u radnoj bilježnici
- Svi oblici na radnom listu
- Sve otvorene radne bilježnice
Također možete koristiti Ugniježđeno za svaku petlju za:
- Sve ćelije u rasponu na svim radnim listovima
- Svi oblici na svim radnim listovima
- Svi listovi u svim otvorenim radnim bilježnicama
- i tako dalje…
Sintaksa je:
| 123 | Za svaki objekt u zbirci[Učini nešto]Sljedeći [objekt] |
Gdje:
- Objekt - Varijabla koja predstavlja raspon, radni list, radnu knjigu, oblik itd. (Npr. Rng)
- Kolekcija - Zbirka predmeta (npr. Raspon ("a1: a10"))
- [Učini nešto] - Kodni blok za izvođenje na svakom objektu
- Sljedeći [objekt] - Završna riječ. [Objekt] nije obavezan, ali se toplo preporučuje.
Za svaku ćeliju u dometu
Ovaj kôd će se provlačiti kroz svaku ćeliju u rasponu:
| 123456789 | Sub ForEachCell_inRange ()Prigušena ćelija kao rasponZa svaku ćeliju u rasponu ("a1: a10")ćelija.Vrijednost = ćelija.Offset (0,1) .VrijednostSljedeća ćelijaKraj podm |
Za svaki radni list u radnoj bilježnici
Ovaj kôd će proći kroz sve radne listove u radnoj knjizi, uklanjajući zaštitu svakog lista:
| 123456789 | Pod ForEachSheet_inWorkbook ()Zatamni kao radni listZa svaki ws u radnim listovimaws.Unprotect "password"Sljedeći wsKraj podm |
Za svaku otvorenu radnu knjigu
Ovaj će kôd spremiti i zatvoriti sve otvorene radne knjige:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Kao radna bilježnicaZa svaku wb u radnim bilježnicamawb.Zatvori Spremi promjene: = IstinaSljedeća wbKraj podm |
Za svaki oblik na radnom listu
Ovaj će kôd izbrisati sve oblike na aktivnom listu.
| 123456789 | Sub ForEachShape ()Dim shp As ShapeZa svaki shp u ActiveSheet.oblicishp.BrisatiSljedeći shpKraj podm |
Za svaki oblik na svakom radnom listu u radnoj svesci
Također se možete ugnijezditi za svaku petlju. Ovdje ćemo proći kroz sve oblike na svim radnim listovima u aktivnoj radnoj knjizi:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp kao oblik, ws kao radni listZa svaki ws u radnim listovimaZa svaki shp U ws.oblicishp.BrisatiSljedeći shpSljedeći wsKraj podm |
Za svaki - IF petlja
Kao što smo već spomenuli, možete koristiti naredbu If unutar petlje, izvodeći radnje samo ako su ispunjeni određeni kriteriji.
Ovaj će kôd sakriti sve prazne retke u rasponu:
| 12345678910 | Sub ForEachCell_inRange ()Prigušena ćelija kao rasponZa svaku ćeliju u rasponu ("a1: a10")Ako je cell.Value = "" Tada _cell.EntireRow.Hidden = TrueSljedeća ćelijaKraj podm |
VBA Do While Loop
VBA Do Do i Do Do (vidi sljedeći odjeljak) vrlo su slični. Ponavljat će petlju dok (ili dok) uvjet nije ispunjen.
Petlja Do While ponavljat će petlju dok je uvjet ispunjen.
Ovdje je sintaksa Do While:
| 123 | Do While Stanje[Učini nešto]Petlja |
Gdje:
- Stanje - Uvjet za testiranje
- [Učini nešto] - Blok koda za ponavljanje
Također možete postaviti petlju Do While s uvjetom na kraju petlje:
| 123 | Čini[Učini nešto]Stanje petlje |
Pokazat ćemo svaki od njih i pokazati kako se razlikuju:
Učinite Dok
Evo primjera petlje Do While koji smo prethodno demonstrirali:
| 12345678 | Sub DoWhileLoop ()Dim n kao cijeli brojn = 1Učinite Dok je n <11MsgBox nn = n + 1PetljaKraj podm |
Petlja Dok
Pokrenimo sada isti postupak, osim što ćemo premjestiti uvjet na kraj petlje:
| 12345678 | Sub DoLoopWhile ()Dim n kao cijeli brojn = 1ČiniMsgBox nn = n + 1Petlja Dok je n <11Kraj podm |
VBA Do Until Loop
Do Do Loops će ponavljati petlju sve dok se ne ispuni određeni uvjet. Sintaksa je u biti ista kao i petlje Do While:
| 123 | Učinite do uvjeta[Učini nešto]Petlja |
i slično, uvjet može ići na početak ili kraj petlje:
| 123 | Čini[Učini nešto]Petlja do stanja |
Učinite do
Ova petlja do, računat će se do 10, kao i naši prethodni primjeri
| 12345678 | Sub DoUntilLoop ()Dim n kao cijeli brojn = 1Radite do n> 10MsgBox nn = n + 1PetljaKraj podm |
Petlja do
Ova petlja Petlja do brojat će se do 10:
| 12345678 | Sub DoLoopUntil ()Dim n kao cijeli brojn = 1ČiniMsgBox nn = n + 1Petlja do n> 10Kraj podm |
Izađite iz Do Loopa
Slično kao i za izlaz iz For za izlaz iz For Loopa, naredbu Exit Do za izlaz iz Do Loopa odmah koristite
| 1 | Izlaz Do |
Evo primjera programa Exit Do:
| 123456789101112131415 | PodizlazDo_Loop ()Dim i Kao cijeli broji = 1Učiniti do i> 1000Ako Range ("A" & i) .Value = "error" TadaRaspon ("A" & i). OdaberiteMsgBox "Greška je pronađena"Izlaz DoZavrši akoi = i + 1PetljaKraj podm |
Kraj ili prekid petlje
Kao što smo gore spomenuli, za izlaz iz petlji možete koristiti Exit For ili Exit Do:
| 1 | Izlaz za |
| 1 | Izlaz Do |
Međutim, ove se naredbe moraju dodati vašem kodu prije nego što pokrenete svoju petlju.
Ako pokušavate "prekinuti" petlju koja se trenutno izvodi, možete pokušati pritisnuti ESC ili CTRL + Pauziraj stanku na tipkovnici. Međutim, ovo možda neće uspjeti. Ako ne radi, morat ćete pričekati da se vaša petlja završi ili, u slučaju beskonačne petlje, upotrijebiti CTRL + ALT + Izbrisati da biste prisilno zatvorili Excel.
Zato pokušavam izbjeći Do petlje, lakše je slučajno stvoriti beskrajnu petlju koja vas tjera na ponovno pokretanje programa Excel, što potencijalno može izgubiti vaš rad.
Više primjera petlji
Petlja kroz redove
Ovo će proći kroz sve retke u stupcu:
| 123456789 | Javna podzvučna petljaThroughRows ()Prigušena ćelija kao rasponZa svaku ćeliju u rasponu ("A: A")Ff cell.value "" zatim MsgBox cell.address & ":" & cell.valueSljedeća ćelijaKraj podm |
Petlja kroz kolone
Ovo će proći kroz sve stupce zaredom:
| 123456789 | Javna podpružna petljaThroughColumns ()Prigušena ćelija kao rasponZa svaku ćeliju u rasponu ("1: 1")If cell.Value "" Then MsgBox cell.Address & ":" & cell.ValueSljedeća ćelijaKraj podm |
Petlje kroz datoteke u mapi
Ovaj kôd će proći kroz sve datoteke u mapi, stvarajući popis:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO As ObjectZatamni mapu kao objektDim oFile As ObjectDim i Kao cijeli brojPostavi oFSO = CreateObject ("Scripting.FileSystemObject")Postavi oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Za svaki oFile In oFolder.FilesRaspon ("A" & i) .value = oFile.Namei = i + 1Sljedeća oFileKraj podm |
Loop Through Array
Ovaj kôd će se provlačiti kroz niz 'arrList':
| 123 | Za i = LBound (arrList) Za UBound (arrList)MsgBox arrList (i)Sljedeći i |
LBound funkcija dobiva "donju granicu" niza, a UBound "gornju granicu".
Petlje u Access VBA
Većina gornjih primjera također će raditi u Access VBA. Međutim, u Accessu prolazimo kroz objekt skupa zapisa, a ne kroz objekt raspona.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Uključeno Slijedi nastavak greškeDim dbs kao baza podatakaDim rst As RecordsetPostavite dbs = CurrentDbPostavi rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S prvim.Pomičite se posljednje.Pomiči se prvoUčiniti do .EOF = IstinaMsgBox (rst.Fields ("ClientName")).Pomiči seSljedećiPetljaZavrši sprvo.ZatvoriPostavite rst = NištaPostavite dbs = NištaKraj podm |
