For å jobbe effektivt i VBA må du forstå Loops.
Loops lar deg gjenta en kodeblokk et bestemt antall ganger eller gjenta en kodeblokk på hvert objekt i et sett med objekter.
Først vil vi vise deg noen eksempler for å vise deg hva sløyfer er i stand til. Da vil vi lære deg alt om sløyfer.
VBA Loop Quick -eksempler
For hver sløyfe
For Every Loops går du gjennom hvert objekt i en samling, for eksempel hvert regneark i arbeidsboken eller hver celle i et område.
Gå gjennom alle regneark i arbeidsbok
Denne koden går gjennom alle regnearkene i arbeidsboken, og avslører hvert ark:
| 12345678 | Sub LoopThroughSheets ()Dim ws Som regnearkFor hver uke i regnearkws.Visible = TrueNesteSlutt Sub |
Sløyfe gjennom alle celler i rekkevidde
Denne koden går gjennom en rekke celler og tester om celleverdien er negativ, positiv eller null:
| 1234567891011121314 | Sub If_Loop ()Dim Cell som RangeFor hver celle i området ("A2: A6")Hvis Cell.Value> 0 DaCell.Offset (0, 1) .Value = "Positive"ElseIf Cell.Value <0 DaCell.Offset (0, 1) .Value = "Negativ"EllersCell.Offset (0, 1) .Value = "Null"Slutt omNeste celleSlutt Sub |
For neste sløyfer
En annen type "For" Loop er For Next Loop. For neste løkke lar deg gå gjennom heltall.
Denne koden går gjennom heltall 1 til 10, og hver viser en meldingsboks:
| 123456 | Sub ForLoop ()Dim i As IntegerFor i = 1 til 10MsgBox iNeste jegSlutt Sub |
Gjør mens sløyfer
Do While Loops vil løkke mens en betingelse er oppfylt. Denne koden vil også gå gjennom heltall 1 til 10, og hver med en meldingsboks.
| 12345678 | Sub DoWhileLoop ()Dim n Som heltalln = 1Gjør mens n <11MsgBox nn = n + 1LøkkeSlutt Sub |
Gjør inntil sløyfer
Omvendt, gjør til sløyfer vil løkke til en betingelse er oppfylt. Denne koden gjør det samme som de to foregående eksemplene.
| 12345678 | Sub DoUntilLoop ()Dim n Som heltalln = 1Gjør inntil n> = 10MsgBox nn = n + 1LøkkeSlutt Sub |
Vi vil diskutere dette nedenfor, men du må være ekstremt forsiktig når du lager Do While- eller Do Through -sløyfer, slik at du ikke oppretter en uendelig sløyfe.
VBA Loop Builder
Dette er et skjermbilde av "Loop Builder" fra vårt Premium VBA-tillegg: AutoMacro. Loop Builder lar deg raskt og enkelt bygge sløyfer for å gå gjennom forskjellige objekter eller tall. Du kan utføre handlinger på hvert objekt og/eller bare velge objekter som oppfyller visse kriterier.
Tillegget inneholder også mange andre kodebyggere, et omfattende VBA-kodebibliotek og et utvalg kodingsverktøy. Det er et must for enhver VBA -utvikler.
Nå skal vi dekke de forskjellige typer løkker i dybden.
VBA For Next Loop
For Loop Syntax
For neste sløyfe lar deg gjenta en kodeblokk et bestemt antall ganger. Syntaksen er:
| 12345 | [Dim teller som heltall]For teller = Start til slutt [trinnverdi][Gjør noe]Neste [Teller] |
Hvor elementene i parentes er valgfrie.
- [Dim Counter as Long] - Erklarer tellervariabelen. Påkrevd hvis Option Explicit er deklarert øverst i modulen.
- Disk - En heltallsvariabel som brukes til å telle
- Start - Startverdien (eks. 1)
- Slutt - Sluttverdien (eks.10)
- [Trinnverdi] - Lar deg telle hvert n heltall i stedet for hvert 1 heltall. Du kan også gå omvendt med en negativ verdi (f.eks. Trinn -1)
- [Gjør noe] - Koden som vil gjenta seg
- Neste [Teller] - Avsluttende uttalelse til For Next Loop. Du kan inkludere telleren eller ikke. Jeg anbefaler imidlertid på det sterkeste å inkludere telleren, da det gjør koden lettere å lese.
Hvis det er forvirrende, ikke bekymre deg. Vi vil gå gjennom noen eksempler:
Teller til 10
Denne koden vil telle til 10 ved hjelp av en For-Next Loop:
| 12345678 | Sub ForEach_CountTo10 ()Dim n Som heltallFor n = 1 til 10MsgBox nNeste nSlutt Sub |
For Loop Step
Tell til 10 - Bare partall
Denne koden teller til 10, og teller bare partall:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n Som heltallFor n = 2 til 10 Trinn 2MsgBox nNeste nSlutt Sub |
Legg merke til at vi la til "trinn 2". Dette forteller For Loop å "gå" gjennom telleren med 2. Vi kan også bruke en negativ trinnverdi til å gå omvendt:
For sløyfetrinn - omvendt
Nedtelling fra 10
Denne koden teller ned fra 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n Som heltallFor n = 10 Til 1 Trinn -1MsgBox nNeste nMsgBox "Lift Off"Slutt Sub |
Slett rader hvis cellen er tom
Jeg har oftest brukt et negativt trinn For-Loop for å gå gjennom celleområder, slette rader som oppfyller visse kriterier. Hvis du går fra de øverste radene til de nederste radene, vil du ødelegge telleren når du sletter rader.
Dette eksemplet vil slette rader med tomme celler (fra den nederste raden):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n Som heltallFor n = 10 Til 1 Trinn -1If Range ("a" & n) .Value = "" DaOmråde ("a" og n) .EntireRow.DeleteSlutt omNeste nSlutt Sub |
Nestet for sløyfe
Du kan “hekke” en For Loop inne i en annen For Loop. Vi bruker Nested For Loops til å lage en multiplikasjonstabell:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable ()Dim rad As Integer, col As IntegerFor rad = 1 til 9For kol = 1 til 9Celler (rad + 1, kol + 1). Verdi = rad * kolNeste kolNeste radSlutt Sub |
Avslutt for
Med Exit For -setningen kan du avslutte en For Next loop umiddelbart.
Du vil vanligvis bruke Exit For sammen med en If -setning, og avslutte For Next Loop hvis en bestemt betingelse er oppfylt.
For eksempel kan du bruke en For Loop for å finne en celle. Når cellen er funnet, kan du gå ut av sløyfen for å øke hastigheten på koden din.
Denne koden går gjennom radene 1 til 1000, og ser etter "feil" i kolonne A. Hvis den blir funnet, vil koden velge cellen, varsle deg om den funnet feilen og gå ut av løkken:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i As IntegerFor i = 1 til 1000If Range ("A" & i) .Value = "error" DaOmråde ("A" og i). VelgMsgBox "Feil funnet"Avslutt forSlutt omNeste jegSlutt Sub |
Viktig: Når det gjelder Nested For Loops, går Exit For bare ut av gjeldende For Loop, ikke alle aktive Loops.
Fortsett for
VBA har ikke kommandoen "Fortsett" som finnes i Visual Basic. I stedet må du bruke "Exit".
VBA for hver sløyfe
VBA For Every Loop vil gå gjennom alle objektene i en samling:
- Alle celler i et område
- Alle regneark i en arbeidsbok
- Alle former i et regneark
- Alle åpne arbeidsbøker
Du kan også bruke nestet for hver sløyfe til å:
- Alle celler i et område på alle regneark
- Alle former på alle regneark
- Alle ark i alle åpne arbeidsbøker
- og så videre…
Syntaksen er:
| 123 | For hvert objekt i samlingen[Gjør noe]Neste [Objekt] |
Hvor:
- Gjenstand - Variabel som representerer et område, regneark, arbeidsbok, form osv. (F.eks. Rng)
- Samling - Samling av objekter (f.eks. Rekkevidde ("a1: a10")
- [Gjør noe] - Kodeblokk som skal kjøres på hvert objekt
- Neste [Objekt] - Avsluttende uttalelse. [Objekt] er valgfritt, men anbefales på det sterkeste.
For hver celle i rekkevidde
Denne koden vil gå gjennom hver celle i et område:
| 123456789 | Sub ForEachCell_inRange ()Dim cell As RangeFor hver celle i området ("a1: a10")cell.Value = cell.Offset (0,1) .ValueNeste celleSlutt Sub |
For hvert regneark i arbeidsbok
Denne koden går gjennom alle regneark i en arbeidsbok, og avbeskytter hvert ark:
| 123456789 | Sub ForEachSheet_inWorkbook ()Dim ws Som regnearkFor hver uke i regnearkws.Unbeskytt "passord"Neste wsSlutt Sub |
For hver åpen arbeidsbok
Denne koden vil lagre og lukke alle åpne arbeidsbøker:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Som arbeidsbokFor hver wb i arbeidsbøkerwb.Close SaveChanges: = TrueNeste wbSlutt Sub |
For hver form i regnearket
Denne koden sletter alle former i det aktive arket.
| 123456789 | Sub ForEachShape ()Dim shp som formFor hver shp i ActiveSheet.Shapesshp. SlettNeste shpSlutt Sub |
For hver form i hvert regneark i arbeidsboken
Du kan også hekke for hver sløyfe. Her går vi gjennom alle former i alle regneark i den aktive arbeidsboken:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetFor hver uke i regnearkFor hver shp i ws.Shapesshp. SlettNeste shpNeste wsSlutt Sub |
For hver - IF Loop
Som vi har nevnt tidligere, kan du bruke en If -setning i en loop, og utføre handlinger bare hvis visse kriterier er oppfylt.
Denne koden vil skjule alle tomme rader i et område:
| 12345678910 | Sub ForEachCell_inRange ()Dim cell As RangeFor hver celle i området ("a1: a10")Hvis cell.Value = "" Da _cell.EntireRow.Hidden = TrueNeste celleSlutt Sub |
VBA Do While Loop
VBA Do While og Do Through (se neste avsnitt) er veldig like. De vil gjenta en sløyfe mens (eller til) en betingelse er oppfylt.
Do While Loop vil gjenta en loop mens en betingelse er oppfylt.
Her er Do While Syntax:
| 123 | Gjør mens tilstand[Gjør noe]Løkke |
Hvor:
- Tilstand - Betingelsen for å teste
- [Gjør noe] - Kodeblokken som skal gjentas
Du kan også sette opp en Do While -løkke med betingelsen på slutten av løkken:
| 123 | Gjøre[Gjør noe]Sløyfe mens tilstand |
Vi vil demonstrere hver enkelt og vise hvordan de er forskjellige:
Gjør mens
Her er Do While -løkkeeksemplet vi demonstrerte tidligere:
| 12345678 | Sub DoWhileLoop ()Dim n Som heltalln = 1Gjør mens n <11MsgBox nn = n + 1LøkkeSlutt Sub |
Sløyfe mens
La oss kjøre den samme prosedyren, bortsett fra at vi flytter tilstanden til slutten av løkken:
| 12345678 | Sub DoLoopWhile ()Dim n Som heltalln = 1GjøreMsgBox nn = n + 1Sløyfe Mens n <11Slutt Sub |
VBA Do Through Loop
Gjør til sløyfer vil gjenta en sløyfe til en bestemt betingelse er oppfylt. Syntaksen er i hovedsak den samme som Do While -løkkene:
| 123 | Gjør inntil tilstand[Gjør noe]Løkke |
og på samme måte kan tilstanden gå i begynnelsen eller slutten av løkken:
| 123 | Gjøre[Gjør noe]Sløyfe til tilstand |
Gjør inntil
Dette gjør til sløyfen vil telle til 10, som våre tidligere eksempler
| 12345678 | Sub DoUntilLoop ()Dim n Som heltalln = 1Gjør inntil n> 10MsgBox nn = n + 1LøkkeSlutt Sub |
Sløyfe til
Denne sløyfen til -sløyfen teller til 10:
| 12345678 | Sub DoLoopUntil ()Dim n Som heltalln = 1GjøreMsgBox nn = n + 1Sløyfe Til n> 10Slutt Sub |
Avslutt Do Loop
På samme måte som å bruke Exit For for å avslutte en For Loop, bruker du Exit Do -kommandoen for å avslutte en Do Loop umiddelbart
| 1 | Avslutt Do |
Her er et eksempel på Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i As Integerjeg = 1Gjør til jeg> 1000If Range ("A" & i) .Value = "error" DaOmråde ("A" og i). VelgMsgBox "Feil funnet"Avslutt DoSlutt omi = i + 1LøkkeSlutt Sub |
Slutt eller bryt sløyfe
Som vi nevnte ovenfor, kan du bruke Exit For eller Exit Do for å avslutte looper:
| 1 | Avslutt for |
| 1 | Avslutt Do |
Disse kommandoene må imidlertid legges til koden din før du kjører løkken.
Hvis du prøver å "bryte" en løkke som kjører for øyeblikket, kan du prøve å trykke ESC eller CTRL + Pause på tastaturet. Dette kan imidlertid ikke fungere. Hvis det ikke fungerer, må du vente på at sløyfen din skal slutte eller, i tilfelle en endeløs sløyfe, bruke CTRL + ALT + Slett for å tvinge til å lukke Excel.
Dette er grunnen til at jeg prøver å unngå Do loops, det er lettere å tilfeldigvis lage en endeløs loop som tvinger deg til å starte Excel på nytt og potensielt miste arbeidet ditt.
Flere løkkeeksempler
Sløyfe gjennom rader
Dette vil gå gjennom alle radene i en kolonne:
| 123456789 | Offentlig Sub LoopThroughRows ()Dim cell As RangeFor hver celle i området ("A: A")Ff cell.value "" deretter MsgBox cell.address & ":" & cell.valueNeste celleSlutt Sub |
Sløyfe gjennom kolonner
Dette vil gå gjennom alle kolonnene på rad:
| 123456789 | Offentlig Sub LoopThroughColumns ()Dim cell As RangeFor hver celle i området ("1: 1")If cell.Value "" Da MsgBox cell.Address & ":" & cell.ValueNeste celleSlutt Sub |
Sløyfe gjennom filer i en mappe
Denne koden vil gå gjennom alle filene i en mappe og lage en liste:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO som objektDim oFolder som objektDim oFile som objektDim i As IntegerSett oFSO = CreateObject ("Scripting.FileSystemObject")Sett oFolder = oFSO.GetFolder ("C: \ Demo)jeg = 2For hver oFile I oFolder.FilesOmråde ("A" og i) .value = oFile.Namei = i + 1Neste filSlutt Sub |
Loop Through Array
Denne koden går gjennom matrisen 'arrList':
| 123 | For i = LBound (arrList) Til UBound (arrList)MsgBox arrList (i)Neste jeg |
LBound -funksjonen får "nedre grense" for matrisen og UBound får den "øvre grensen".
Sløyfer i Access VBA
De fleste eksemplene ovenfor vil også fungere i Access VBA. I Access går vi imidlertid gjennom Recordset Object i stedet for Range Object.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Ved feil Fortsett nesteDim dbs som databaseDim rst As RecordsetSett dbs = CurrentDbSett rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Med første.MoveLast.MoveFirstGjør inntil .EOF = TrueMsgBox (rst.Fields ("klientnavn")).MoveNextLøkkeSlutt medførste lukkSett først = ingentingSett dbs = ingentingSlutt Sub |
