VBA -s tõhusaks töötamiseks peate mõistma silmuseid.
Silmused võimaldavad teil korduvalt koodiplokki korrata või kordada koodiplokki objektide komplekti iga objekti puhul.
Kõigepealt näitame teile mõningaid näiteid, et näidata teile, milleks ahelad on võimelised. Siis õpetame teile kõike silmuste kohta.
VBA silmuse kiirnäited
Iga silmuse jaoks
Iga silmuse jaoks aas läbi kogu kogu iga objekti, näiteks iga töövihiku töölehe või vahemiku iga lahtri.
Sirvige kõiki töövihiku töölehti
See kood vaatab läbi kõik töövihiku töölehed, vabastades iga lehe peitmise:
| 12345678 | Sub LoopThroughSheets ()Dim ws töölehenaIga ws töölehtedelws.Visible = TõsiEdasiEnd Sub |
Loop kõik vahemikus olevad lahtrid
See kood liigub läbi lahtrite vahemiku, kontrollides, kas lahtri väärtus on negatiivne, positiivne või null:
| 1234567891011121314 | Alam If_Loop ()Dim Cell kui vahemikIga vahemiku lahtri kohta ("A2: A6")Kui Cell.Value> 0 SiisLahter.Nihke (0, 1) .Väärtus = "Positiivne"Muul juhul, kui Cell.Value <0 SiisLahter.Nihke (0, 1) .Väärtus = "Negatiivne"MuiduLahter.Nihke (0, 1) .Väärtus = "Null"Lõpp KuiJärgmine lahterEnd Sub |
Järgmiste silmuste jaoks
Teine “For” silmuse tüüp on For For Next Loop. For For Next Loop võimaldab teil täisarvudest läbi sõita.
See kood liigub täisarvudest 1 kuni 10, näidates igaüks koos sõnumikastiga:
| 123456 | Sub ForLoop ()Dim i As täisarvI = 1 kuni 10MsgBox iEdasi iEnd Sub |
Tehke silmuste ajal
Tehke ajal, kui silmused loopivad, kui tingimus on täidetud. See kood läbib ka täisarvu 1 kuni 10, näidates igaüks koos sõnumikastiga.
| 12345678 | Sub DoWhileLoop ()Dim n Nagu täisarvn = 1Tee ajal, kui n <11MsgBox nn = n + 1LoopEnd Sub |
Tehke kuni silmusteni
Ja vastupidi, Do Do Loops kordab, kuni tingimus on täidetud. See kood teeb sama, mis kaks eelmist näidet.
| 12345678 | Sub DoUntilLoop ()Dim n Nagu täisarvn = 1Tehke kuni n> = 10MsgBox nn = n + 1LoopEnd Sub |
Me arutame seda allpool, kuid te peate olema äärmiselt ettevaatlik, kui loote silmuseid Do while või Do Before, et mitte luua lõputut tsüklit.
VBA silmuste koostaja
See on ekraanipilt “Loop Builderist” meie Premium VBA lisandmoodulist: AutoMacro. Loop Builder võimaldab teil kiiresti ja hõlpsalt luua silmuseid erinevate objektide või numbrite läbimiseks. Saate teha toiminguid iga objektiga ja/või valida ainult teatud kriteeriumidele vastavaid objekte.
Lisandmoodul sisaldab ka paljusid teisi koodiehitajaid, ulatuslikku VBA kooditeeki ja valikut kodeerimisvahendeid. See peab olema iga VBA arendaja jaoks.
Nüüd käsitleme põhjalikult erinevat tüüpi silmuseid.
VBA järgmise silmuse jaoks
Loop süntaksi jaoks
Järgmise silmuse jaoks saate koodiplokki korrata määratud arvu kordi. Süntaks on järgmine:
| 12345 | [Dim Counter täisarvuna]Loenduri jaoks = algusest lõpuni [sammu väärtus][Tee midagi]Järgmine [loendur] |
Kui sulgudes olevad üksused on valikulised.
- [Pimendusloendur kui pikk] - Kuulutab loenduri muutuja. Nõutav, kui suvand Explicit on mooduli ülaosas deklareeritud.
- Loendur - Loendamiseks kasutatav täisarvuline muutuja
- Alusta - Algväärtus (näit. 1)
- Lõpp - lõppväärtus (näit. 10)
- [Etapi väärtus] - Võimaldab loendada iga 1 täisarvu asemel iga n täisarvu. Võite pöörduda ka negatiivse väärtusega tagurpidi (nt 1. samm)
- [Tee midagi] - Korduv kood
- Järgmine [loendur] - lõppsõna järgmise loo jaoks. Saate loenduri lisada või mitte. Siiski soovitan tungivalt loendurit lisada, kuna see muudab teie koodi hõlpsamini loetavaks.
Kui see on segane, ärge muretsege. Vaatame läbi mõned näited:
Loendage 10 -ni
See kood loetakse järgmise silmusena 10-ni:
| 12345678 | Sub ForEach_CountTo10 ()Dim n Nagu täisarvN = 1 kuni 10MsgBox nJärgmine nEnd Sub |
Loop Step jaoks
Loendage kümneni - ainult paarisarvud
See kood loetakse kümneni, lugedes ainult paarisarvu:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n Nagu täisarvN = 2 kuni 10 2. sammMsgBox nJärgmine nEnd Sub |
Pange tähele, et lisasime "2. sammu". See käsib For Loopil "loendurist" läbi astuda 2 võrra. Vastupidiseks astumiseks võime kasutada ka negatiivset sammu väärtust:
Loop Step jaoks - vastupidine
Loendus alates 10
See kood hakkab loendama kümnest:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n Nagu täisarvKui n = 10 kuni 1 Samm -1MsgBox nJärgmine nMsgBox "Tõstke välja"End Sub |
Kustutage read, kui lahter on tühi
Olen kõige sagedamini kasutanud negatiivset sammu For-Loop, et liikuda lahtrite vahemikku, kustutades teatud kriteeriumidele vastavad read. Kui liigute ülemistelt ridadelt alumistele ridadele, ridu kustutades ajate loenduri sassi.
See näide kustutab tühjade lahtritega read (alustades alumisest reast):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n Nagu täisarvN = 10 kuni 1 Samm -1Kui vahemik ("a" & n) .Väärtus = "" SiisVahemik ("a" & n) .EntireRow.DeleteLõpp KuiJärgmine nEnd Sub |
Pesatud silmuse jaoks
Saate silmuse jaoks ühe pesa teise pesa jaoks pesa panna. Kasutame korrutustabeli loomiseks Nested For Loops:
| 1234567891011 | Alampesastatud_ForEach_MultiplicationTable ()Hämardatud rida täisarvuna, veerg täisarvunaRida = 1 kuni 9Kolon = 1 kuni 9Lahtrid (rida + 1, veerg + 1). Väärtus = rida * veergJärgmine veergJärgmine ridaEnd Sub |
Välju
Lause Exit For võimaldab teil kohe järgmiseks tsüklist väljuda.
Tavaliselt kasutaksite väljapääsu ja koos avaldusega If, väljudes järgmise silmuse jaoks, kui teatud tingimus on täidetud.
Näiteks võite lahtri leidmiseks kasutada silmuse jaoks. Kui see lahter on leitud, saate koodi kiirendamiseks tsüklist väljuda.
See kood liigub läbi ridade 1 kuni 1000, otsides veerust A „viga”. Kui see leitakse, valib kood lahtri, teavitab teid leitud veast ja väljub tsüklist:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i As täisarvI = 1 kuni 1000Kui vahemik ("A" & i) .Väärtus = "viga" SiisVahemik ("A" ja i). ValigeMsgBox "Viga leitud"VäljuLõpp KuiEdasi iEnd Sub |
Oluline: Nested For Loops puhul väljub Exit For ainult praegusest ringist, mitte kõik aktiivsed ahelad.
Jätka
VBA -l pole Visual Basicus leiduvat käsku „Jätka”. Selle asemel peate kasutama nuppu "Välju".
VBA iga silmuse jaoks
Iga silmuse VBA vaatab läbi kõik kollektsiooni objektid:
- Kõik lahtrid vahemikus
- Kõik töölehed töövihikus
- Kõik kujundid töölehel
- Kõik avatud töövihikud
Pesastatud iga silmuse jaoks saate kasutada ka järgmist.
- Kõik lahtrid vahemikus kõigil töölehtedel
- Kõik kujundid kõikidel töölehtedel
- Kõik lehed kõikides avatud töövihikutes
- ja nii edasi…
Süntaks on järgmine:
| 123 | Iga kollektsiooni objekti kohta[Tee midagi]Järgmine [objekt] |
Kus:
- Objekt - Muutuja, mis esindab vahemikku, töölehte, töövihikut, kuju jne (nt rng)
- Kogu - objektide kogumine (nt vahemik ("a1: a10")
- [Tee midagi] - Koodplokk iga objekti jaoks
- Järgmine [objekt] - Lõppsõna. [Objekt] on valikuline, kuid tungivalt soovitatav.
Iga vahemiku lahtri kohta
See kood liigub läbi iga lahtri vahemikus:
| 123456789 | Sub ForEachCell_inRange ()Hämardage rakk vahemikunaIga vahemiku lahtri kohta ("a1: a10")cell.Value = cell. Offset (0,1). ValueJärgmine lahterEnd Sub |
Töövihiku iga töölehe jaoks
See kood vaatab läbi kõik töövihiku töölehed, eemaldades iga lehe kaitse:
| 123456789 | Alamlehe_lehe tööraamat ()Dim ws töölehenaIga ws töölehtedelws. Kaitske parooliJärgmine wsEnd Sub |
Iga avatud töövihiku kohta
See kood salvestab ja sulgeb kõik avatud töövihikud:
| 123456789 | Sub ForEWW_inWorkbooks ()Dim wb TöövihikunaIga wb kohta töövihikuteswb. Sulge SaveChanges: = TõsiJärgmine wbEnd Sub |
Iga kujundi jaoks töölehel
See kood kustutab kõik aktiivsel lehel olevad kujundid.
| 123456789 | Sub ForEachShape ()Dim shp As ShapeIga shp jaoks ActiveSheet.Shapesshp. KustutaJärgmine shpEnd Sub |
Iga kuju kohta töövihiku igal töölehel
Saate pesitseda ka iga silmuse jaoks. Siin vaatame läbi kõik kujundid aktiivse töövihiku kõikidel töölehtedel:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws AsheetIga ws töölehtedelIga shp jaoks ws. Kujudshp. KustutaJärgmine shpJärgmine wsEnd Sub |
Igaühe jaoks - IF Loop
Nagu me varem mainisime, saate tsüklis kasutada If -lauset, tehes toiminguid ainult siis, kui teatud kriteeriumid on täidetud.
See kood peidab kõik tühjad read vahemikus:
| 12345678910 | Sub ForEachCell_inRange ()Hämardage rakk vahemikunaIga vahemiku lahtri kohta ("a1: a10")Kui cell.Value = "" Siis _cell.EntireRow.Hidden = TõsiJärgmine lahterEnd Sub |
VBA tehke ringi
VBA Do While ja Do Do (vt järgmist jaotist) on väga sarnased. Nad kordavad tsüklit, kuni (või kuni) tingimus on täidetud.
Ling Do Do Loop kordab tsüklit, kui tingimus on täidetud.
Siin on tehke süntaks:
| 123 | Seisundi tegemine[Tee midagi]Loop |
Kus:
- Seisukord - Testimise tingimus
- [Tee midagi] - Korduv koodiplokk
Saate seadistada ka Do Do ahela, mille tingimus on tsükli lõpus:
| 123 | Tehke[Tee midagi]Loop samas seisukorras |
Demonstreerime igaüks ja näitame, kuidas need erinevad:
Tehke samas
Siin on Do Do loopi näide, mida me varem demonstreerisime:
| 12345678 | Sub DoWhileLoop ()Dim n Nagu täisarvn = 1Tee ajal, kui n <11MsgBox nn = n + 1LoopEnd Sub |
Loop Kuigi
Nüüd käivitame sama protseduuri, välja arvatud see, et teisaldame tingimuse silmuse lõppu:
| 12345678 | Sub DoLoopWhile ()Dim n Nagu täisarvn = 1TehkeMsgBox nn = n + 1Loop Kuigi n <11End Sub |
VBA Do Loop
Tehke kuni silmused kordab tsüklit, kuni teatud tingimus on täidetud. Süntaks on sisuliselt sama, mis Do while -ahelad:
| 123 | Tehke kuni tingimuseni[Tee midagi]Loop |
ja sarnaselt võib tingimus minna tsükli alguses või lõpus:
| 123 | Tehke[Tee midagi]Loop kuni tingimuseni |
Tehke kuni
Nii tehke kuni tsükkel, nagu meie eelmised näited, loetakse 10 -ni
| 12345678 | Sub DoUntilLoop ()Dim n Nagu täisarvn = 1Tehke kuni n> 10MsgBox nn = n + 1LoopEnd Sub |
Loop Kuni
See tsükkel kuni tsükkel loetakse 10 -ni:
| 12345678 | Sub DoLoopUntil ()Dim n Nagu täisarvn = 1TehkeMsgBox nn = n + 1Loop kuni n> 10End Sub |
Väljuge Do Loopist
Sarnaselt Exit For -i kasutamisega For Loopist väljumiseks kasutate käsku Exit Do, et Do Loop -st kohe väljuda
| 1 | Välju Do |
Siin on Exit Do näide:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i As täisarvi = 1Tehke kuni i> 1000Kui vahemik ("A" & i) .Väärtus = "viga" SiisVahemik ("A" ja i). ValigeMsgBox "Viga leitud"Välju DoLõpp Kuii = i + 1LoopEnd Sub |
Lõpeta või katkesta silmus
Nagu eespool mainitud, saate silmustest väljumiseks kasutada Exit For või Exit Do:
| 1 | Välju |
| 1 | Välju Do |
Need käsud tuleb aga enne tsükli käivitamist oma koodile lisada.
Kui proovite praegu töötavat silmust "murda", võite proovida vajutada ESC või CTRL + Paus Paus klaviatuuril. See aga ei pruugi toimida. Kui see ei tööta, peate ootama, kuni tsükkel lõpeb, või lõputu tsükli korral kasutama CTRL + ALT + Kustuta sundida Excel sulgema.
Sellepärast püüan vältida Do -silmuseid, lihtsam on kogemata luua lõputu silmus, mis sunnib teid Exceli taaskäivitama, kaotades tõenäoliselt oma töö.
Veel silmuste näiteid
Silmused läbi ridade
See liigub läbi kõik veeru read:
| 123456789 | Avalik alamring LoopThroughRows ()Hämardage rakk vahemikunaIga vahemiku lahtri kohta ("A: A")Ff cell.value "" seejärel MsgBox cell.address & ":" & cell.valueJärgmine lahterEnd Sub |
Silmus läbi veergude
See läbib kõiki veerge järjest:
| 123456789 | Avalik alamringi veerud ()Hämardage rakk vahemikunaIga vahemiku lahtri kohta ("1: 1")If cell.Value "" Siis MsgBox cell.Address & ":" & cell.ValueJärgmine lahterEnd Sub |
Kaustas olevate failide kordamine
See kood vaatab läbi kõik kaustas olevad failid, luues loendi:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO kui objektKausta himustamine objektinaDim oFile objektinaDim i As täisarvMäära oFSO = CreateObject ("Scripting.FileSystemObject")Määra oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Iga oFile kausta. FailidVahemik ("A" & i) .väärtus = oFile.Namei = i + 1Järgmine failEnd Sub |
Silmus läbi massiivi
See kood liigub läbi massiivi „arrList”:
| 123 | I = LBound (arrList) kuni UBound (arrList)MsgBoxi arveloend (i)Edasi i |
Funktsioon LBound saab massiivi „alumise piiri” ja UBound „ülemise piiri”.
Silmused Access VBA -s
Enamik ülaltoodud näiteid töötab ka Access VBA -s. Accessis aga loopime pigem kirjehulga objekti kui vahemiku objekti.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Vea korral Jätka järgmistDim dbs andmebaasinaDim rst Esmalt salvestusenaMäära dbs = CurrentDbMäära rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Koos esimesega.MoveLast.MoveFirstTehke kuni .EOF = TõsiMsgBox (rst.Fields ("Kliendinimi")).MoveNextLoopLõpetaesimene. SuleMäära esimene = mitte midagiMäära dbs = mitte midagiEnd Sub |
