Za učinkovito delo v VBA morate razumeti zanke.
Zanke vam omogočajo, da ponovite kodni blok določeno število krat ali ponovite kodni blok za vsak predmet v nizu predmetov.
Najprej vam bomo pokazali nekaj primerov, ki vam bodo pokazali, česa so zmožne zanke. Nato vas bomo naučili vse o zankah.
Hitri primeri zanke VBA
Za vsako zanko
Za vsako zanko zankajte skozi vsak predmet v zbirki, na primer vsak delovni list v delovnem zvezku ali vsako celico v obsegu.
Prelistajte vse delovne liste v delovnem zvezku
Ta koda se bo pomikala po vseh delovnih listih v delovnem zvezku in razkrila vsak list:
| 12345678 | Sub LoopThroughSheets ()Zatemni kot delovni listZa vsak ws na delovnih listihws.Visible = ResNaslednjiEnd Sub |
Prehod skozi vse celice v dosegu
Ta koda se bo vrtela skozi vrsto celic in preizkušala, ali je vrednost celice negativna, pozitivna ali nič:
| 1234567891011121314 | Sub If_Loop ()Zatemni celico kot obsegZa vsako celico v razponu ("A2: A6")Če je Cell.Value> 0, potemCell.Offset (0, 1) .Value = "Pozitivno"ElseIf Cell.Value <0 PotemCell.Offset (0, 1) .Value = "Negativno"Sicer paCell.Offset (0, 1) .Value = "Nič"Konec ČeNaslednja celicaEnd Sub |
Za naslednje zanke
Druga vrsta zanke »za« je zanka za naslednjo zanko. Zanka For Next vam omogoča, da se pomikate skozi cela števila.
Ta koda se bo vrtela skozi cela števila od 1 do 10, pri čemer bo vsako prikazalo s poljem s sporočilom:
| 123456 | Sub ForLoop ()Dim i kot celo številoZa i = 1 do 10MsgBox iNaprej iEnd Sub |
Naredi Do Loops
Dokler se zanke izvajajo, medtem ko je pogoj izpolnjen. Ta koda se bo vrtela tudi skozi cela števila od 1 do 10, pri čemer bo vsako prikazalo s poljem s sporočilom.
| 12345678 | Sub DoWhileLoop ()Dim n kot celo številon = 1Naredite Medtem ko je n <11MsgBox nn = n + 1ZankaEnd Sub |
Naredi do zank
Nasprotno pa se zanke naredijo Do, dokler se ne izpolni pogoj. Ta koda dela isto kot prejšnja dva primera.
| 12345678 | Sub DoUntilLoop ()Dim n kot celo številon = 1Naredi do n> = 10MsgBox nn = n + 1ZankaEnd Sub |
O tem bomo razpravljali spodaj, vendar morate biti zelo previdni pri ustvarjanju zank Do Do ali Do Do, da ne ustvarite neskončne zanke.
VBA Loop Builder
To je posnetek zaslona "Loop Builder" iz našega dodatka Premium VBA: AutoMacro. Loop Builder vam omogoča hitro in enostavno izdelavo zank za kroženje po različnih predmetih ali številkah. Na vsakem objektu lahko izvedete dejanja in/ali izberete samo predmete, ki izpolnjujejo določena merila.
Dodatek vsebuje tudi številne druge ustvarjalce kod, obsežno knjižnico kod VBA in izbor orodij za kodiranje. To mora imeti vsak razvijalec VBA.
Zdaj bomo podrobno obravnavali različne vrste zank.
VBA za naslednjo zanko
Za sintakso zanke
For Next Loop vam omogoča, da določeno število krat ponovite blok kode. Sintaksa je:
| 12345 | [Zatemni števec kot celo število]Za števec = Od začetka do konca [Vrednost koraka][Naredi kaj]Naprej [števec] |
Kjer so elementi v oklepajih neobvezni.
- [Dimni števec tako dolgo] - Razglasi spremenljivko števca. Zahtevano, če je na vrhu modula deklarirana možnost Explicit.
- Števec - Celotna spremenljivka, ki se uporablja za štetje
- Začni - Začetna vrednost (Primer 1)
- Konec - Končna vrednost (Primer 10)
- [Vrednost koraka] - Omogoča štetje vsakih n celih številk namesto vsakega 1 celega števila. Z negativno vrednostjo lahko greste tudi v obratni smeri (npr. Korak -1)
- [Naredi kaj] - Koda, ki se bo ponovila
- Naprej [števec] - Zaključna izjava za naslednjo zanko. Števec lahko vključite ali ne. Vendar močno priporočam, da vključite števec, saj olajša branje vaše kode.
Če je to zmedeno, ne skrbite. Pregledali bomo nekaj primerov:
Štej do 10
Ta koda bo štela do 10 z uporabo zanke For-Next:
| 12345678 | Sub ForEach_CountTo10 ()Dim n kot celo številoZa n = 1 do 10MsgBox nNaprej nEnd Sub |
Za Loop Step
Štejte do 10 - samo parne številke
Ta koda bo štela do 10 samo pri štetju:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n kot celo številoZa n = 2 do 10 Korak 2MsgBox nNaprej nEnd Sub |
Upoštevajte, da smo dodali "2. korak". To pove, da zanka For "stopi" skozi števec za 2. Za negativni korak lahko uporabimo tudi negativno vrednost koraka:
Za korak zank - obratno
Odštevanje od 10
Ta koda bo odštevala od 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n kot celo številoZa n = 10 do 1 korak -1MsgBox nNaprej nMsgBox "Lift Off"End Sub |
Izbriši vrstice, če je celica prazna
Najpogosteje sem uporabil negativen korak For-Loop za prehod po obsegih celic in brisanje vrstic, ki izpolnjujejo določena merila. Če se vrnete iz zgornjih vrstic v spodnje vrstice, boste ob brisanju vrstic pokvarili števec.
Ta primer bo izbrisal vrstice s praznimi celicami (od spodnje vrstice):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n kot celo številoZa n = 10 do 1 korak -1Če Range ("a" & n) .Value = "" PotemRange ("a" & n) .EntireRow.DeleteKonec ČeNaprej nEnd Sub |
Ugnezdeno za zanko
Enega For Loopa lahko "ugnezdite" v drugega For Loop. Za izdelavo tabele množenja bomo uporabili N ugasnjene for zanke:
| 1234567891011 | Podvložena_ForEach_MultiplicationTable ()Zatemni vrstico kot celo število, stolpec kot celo številoZa vrstico = 1 do 9Za col = 1 do 9Celice (vrstica + 1, stolpec + 1). Vrednost = vrstica * stolpecNaslednji stolpecNaslednja vrsticaEnd Sub |
Izhod za
Stavek Exit For vam omogoča takojšen izhod iz zanke For Next.
Običajno bi uporabili Izhod For skupaj z izjavo If, če želite izpolniti določen pogoj, zapustiti zaporedje For Next.
Za iskanje celice lahko na primer uporabite For Loop. Ko je celica najdena, lahko zapustite zanko, da pospešite kodo.
Ta koda se bo vrtela skozi vrstice 1 do 1000 in iskala »napako« v stolpcu A. Če bo najdena, bo koda izbrala celico, vas opozorila na ugotovljeno napako in zapustila zanko:
| 12345678910111213 | PodizhodFor_Loop ()Dim i kot celo številoZa i = 1 do 1000Če Range ("A" & i) .Value = "error" PotemRazpon ("A" & i). IzberiteMsgBox "Napaka je bila najdena"Izhod zaKonec ČeNaprej iEnd Sub |
Pomembno: V primeru ugnezdenih zank, Izhod za zapušča samo trenutno zanko For, ne pa vseh aktivnih zank.
Nadaljuj za
VBA nima ukaza »Nadaljuj«, ki ga najdete v Visual Basicu. Namesto tega boste morali uporabiti »Izhod«.
VBA za vsako zanko
VBA za vsako zanko se bo pomikal skozi vse predmete v zbirki:
- Vse celice v razponu
- Vsi delovni listi v delovnem zvezku
- Vse oblike na delovnem listu
- Vsi odprti delovni zvezki
Uporabite lahko tudi ugnezdene za vsako zanko za:
- Vse celice v razponu na vseh delovnih listih
- Vse oblike na vseh delovnih listih
- Vsi listi v vseh odprtih delovnih zvezkih
- in tako naprej…
Sintaksa je:
| 123 | Za vsak predmet v zbirki[Naredi kaj]Naprej [predmet] |
Kje:
- Objekt - spremenljivka, ki predstavlja obseg, delovni list, delovni zvezek, obliko itd. (Npr. Rng)
- Zbirka - Zbirka predmetov (npr. Obseg (»a1: a10«)
- [Naredi kaj] - Blok kode za izvajanje na vsakem objektu
- Naprej [predmet] - Zaključna izjava. [Objekt] ni obvezen, vendar ga močno priporočamo.
Za vsako celico v dosegu
Ta koda bo krožila skozi vsako celico v obsegu:
| 123456789 | Sub ForEachCell_inRange ()Zatemni celico kot obsegZa vsako celico v dosegu ("a1: a10")cell.Value = cell.Offset (0,1) .VrednostNaslednja celicaEnd Sub |
Za vsak delovni list v delovnem zvezku
Ta koda se bo pomikala po vseh delovnih listih v delovnem zvezku in odstranila zaščito vsakega lista:
| 123456789 | Sub ForEachSheet_inWorkbook ()Zatemni kot delovni listZa vsak ws na delovnih listihws.Odstrani "geslo"Naslednji wsEnd Sub |
Za vsak odprt delovni zvezek
Ta koda bo shranila in zaprla vse odprte delovne zvezke:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Kot delovni zvezekZa vsako wb v delovnih zvezkihwb.Close SaveChanges: = TrueNaslednja wbEnd Sub |
Za vsako obliko na delovnem listu
Ta koda bo izbrisala vse oblike na aktivnem listu.
| 123456789 | Sub ForEachShape ()Dim shp As ShapeZa vsak shp v ActiveSheet.Shapesshp.IzbrišiNaslednji shpEnd Sub |
Za vsako obliko na vsakem delovnem listu v delovnem zvezku
Za vsako zanko lahko tudi ugnezdite. Tukaj bomo prečkali vse oblike na vseh delovnih listih v aktivnem delovnem zvezku:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Zatemni shp kot obliko, ws kot delovni listZa vsak ws na delovnih listihZa vsak shp V ws. Oblikeshp.IzbrišiNaslednji shpNaslednji wsEnd Sub |
Za vsakega - IF zanka
Kot smo že omenili, lahko v zanki uporabite stavek If, ki izvaja dejanja le, če so izpolnjena določena merila.
Ta koda bo skrila vse prazne vrstice v obsegu:
| 12345678910 | Sub ForEachCell_inRange ()Zatemni celico kot obsegZa vsako celico v dosegu ("a1: a10")Če cell.Value = "" Potem _cell.EntireRow.Hidden = TrueNaslednja celicaEnd Sub |
VBA Do While Loop
VBA Do While in Do Until (glej naslednji razdelek) sta si zelo podobna. Ponavljali bodo zanko, dokler (ali dokler) pogoj ni izpolnjen.
Zanka Do while bo ponavljala zanko, medtem ko je pogoj izpolnjen.
Tu je sintaksa Do While:
| 123 | Naredite Med pogojem[Naredi kaj]Zanka |
Kje:
- Pogoj - Pogoj za testiranje
- [Naredi kaj] - Blok kode za ponovitev
Nastavite lahko tudi zanko Do While s pogojem na koncu zanke:
| 123 | Naredi[Naredi kaj]Stanje zanke |
Vsak bomo demonstrirali in pokazali, kako se razlikujejo:
Naredi medtem
Tu je primer zanke Do While, ki smo ga prej pokazali:
| 12345678 | Sub DoWhileLoop ()Dim n kot celo številon = 1Naredite Medtem ko je n <11MsgBox nn = n + 1ZankaEnd Sub |
Loop While
Zdaj zaženimo isti postopek, le da bomo pogoj premaknili na konec zanke:
| 12345678 | Sub DoLoopWhile ()Dim n kot celo številon = 1NarediMsgBox nn = n + 1Zank Medtem ko je n <11End Sub |
VBA Do Until Loop
Do Do Loops bo ponavljal zanko, dokler ni izpolnjen določen pogoj. Sintaksa je v bistvu enaka zank Do While:
| 123 | Naredi do stanja[Naredi kaj]Zanka |
in podobno je lahko pogoj na začetku ali koncu zanke:
| 123 | Naredi[Naredi kaj]Zanka do stanja |
Naredi do
Ta zanka do, se bo štela do 10, tako kot naši prejšnji primeri
| 12345678 | Sub DoUntilLoop ()Dim n kot celo številon = 1Naredite do n> 10MsgBox nn = n + 1ZankaEnd Sub |
Zanka do
Ta zanka Dokler se zanka šteje do 10:
| 12345678 | Sub DoLoopUntil ()Dim n kot celo številon = 1NarediMsgBox nn = n + 1Zank do n> 10End Sub |
Zaprite Do Loop
Podobno kot pri izhodu For za izhod iz zanke For uporabite ukaz Izhod Do za takojšen izhod iz zanke Do
| 1 | Izhod Do |
Tu je primer programa Exit Do:
| 123456789101112131415 | PodizhodDo_Loop ()Dim i kot celo številoi = 1Naredi do i> 1000Če Range ("A" & i) .Value = "error" PotemRazpon ("A" & i). IzberiteMsgBox "Napaka je bila najdena"Izhod DoKonec Čei = i + 1ZankaEnd Sub |
End or Break Loop
Kot smo že omenili, lahko za izhod iz zank uporabite Exit For ali Exit Do:
| 1 | Izhod za |
| 1 | Izhod Do |
Vendar je treba te ukaze dodati v kodo, preden zaženete zanko.
Če poskušate »prekiniti« zanko, ki se trenutno izvaja, lahko poskusite pritisniti ESC ali CTRL + Premor na tipkovnici. Vendar to morda ne bo delovalo. Če ne deluje, boste morali počakati, da se zanka konča, ali pa v primeru neskončne zanke uporabiti CTRL + ALT + Izbriši za prisilno zapiranje Excela.
Zato se poskušam izogniti zank Do, lažje je po nesreči ustvariti neskončno zanko, ki vas prisili, da znova zaženete Excel, s čimer lahko izgubite delo.
Več primerov zank
Loop Through Rows
S tem boste prebrali vse vrstice v stolpcu:
| 123456789 | Public Sub LoopThroughRows ()Zatemni celico kot obsegZa vsako celico v dosegu ("A: A")Ff cell.value "" nato MsgBox cell.address & ":" & cell.valueNaslednja celicaEnd Sub |
Zanka skozi stolpce
S tem boste prešli skozi vse stolpce zapored:
| 123456789 | Public Sub LoopThroughColumns ()Zatemni celico kot obsegZa vsako celico v razponu ("1: 1")Če cell.Value "" Potem celica MsgBox.Address & ":" & cell.ValueNaslednja celicaEnd Sub |
Preglejte datoteke v mapi
Ta koda bo krožila skozi vse datoteke v mapi in ustvarila seznam:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Zatemni oFSO kot objektZatemni o mapo kot predmetDim oFile As ObjectDim i kot celo številoNastavi oFSO = CreateObject ("Scripting.FileSystemObject")Nastavi oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Za vsako datoteko oFile In oFolder.FilesObseg ("A" & i) .value = oFile.Namei = i + 1Naslednji oFileEnd Sub |
Loop Through Array
Ta koda bo krožila skozi matriko "arrList":
| 123 | Za i = LBound (arrList) Za UBound (arrList)MsgBox arrList (i)Naprej i |
Funkcija LBound dobi "spodnjo mejo" matrike, UBound pa "zgornjo mejo".
Zanke v Access VBA
Večina zgornjih primerov bo delovala tudi v Access VBA. Vendar pa v Accessu krožimo skozi objekt nabora zapisov in ne v objektu obsega.
| 123456789101112131415161718 | Sub LoopThroughRecords ()On Napaka Nadaljuj NaprejZatemni dbs kot bazo podatkovZatemni najprej kot zapis zapisaNastavite dbs = CurrentDbNastavite rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S prvim.Premaknite se nazadnje.Premakni se najprejNaredi do .EOF = ResMsgBox (rst.Fields ("ClientName")).Premakni se naprejZankaKončaj snajprej ZapriNastavi rst = NičNastavi dbs = ničEnd Sub |
