For at arbejde effektivt i VBA skal du forstå Loops.
Loops giver dig mulighed for at gentage en kodeblok et bestemt antal gange eller gentage en kodeblok på hvert objekt i et sæt objekter.
Først vil vi vise dig et par eksempler for at vise dig, hvad sløjfer er i stand til. Så lærer vi dig alt om sløjfer.
VBA Loop Hurtige eksempler
For hver sløjfe
For hver sløjfe går hvert objekt i en samling igennem, f.eks. Hvert regneark i projektmappen eller hver celle i et område.
Sløjfe igennem alle regneark i projektmappe
Denne kode går gennem alle regneark i projektmappen, og hvert ark vises:
| 12345678 | Sub LoopThroughSheets ()Dim ws Som regnearkFor hver gang i regnearkws.Visible = TrueNæsteAfslut Sub |
Sløjfe gennem alle celler inden for rækkevidde
Denne kode går gennem en række celler og tester, om celleværdien er negativ, positiv eller nul:
| 1234567891011121314 | Sub If_Loop ()Dim celle som rækkeviddeFor hver celle i området ("A2: A6")Hvis Cell.Value> 0 SåCell.Offset (0, 1) .Værdi = "Positiv"ElseIf Cell.Value <0 HerefterCell.Offset (0, 1) .Value = "Negativ"AndetCell.Offset (0, 1) .Value = "Nul"Afslut HvisNæste celleAfslut Sub |
Til næste sløjfer
En anden type “For” Loop er For Next Loop. For næste løkke giver dig mulighed for at gå gennem heltal.
Denne kode går gennem heltal 1 til 10 og viser hver med en meddelelsesboks:
| 123456 | Sub ForLoop ()Dim i As IntegerFor i = 1 til 10MsgBox iNæste iAfslut Sub |
Gør mens loops
Do While Loops loopes, mens en betingelse er opfyldt. Denne kode vil også gå gennem heltal 1 til 10 og vise hver med en meddelelsesboks.
| 12345678 | Sub DoWhileLoop ()Dim n Som heltaln = 1Gør Mens n <11MsgBox nn = n + 1SløjfeAfslut Sub |
Gør indtil sløjfer
Omvendt, gør indtil sløjfer løkker, indtil en betingelse er opfyldt. Denne kode gør det samme som de to foregående eksempler.
| 12345678 | Sub DoUntilLoop ()Dim n Som heltaln = 1Gør indtil n> = 10MsgBox nn = n + 1SløjfeAfslut Sub |
Vi vil diskutere dette nedenfor, men du skal være yderst forsigtig, når du opretter Do While eller Do Through loops, så du ikke opretter en loop, der aldrig slutter.
VBA Loop Builder
Dette er et skærmbillede af "Loop Builder" fra vores Premium VBA-tilføjelse: AutoMacro. Loop Builder giver dig mulighed for hurtigt og nemt at bygge loops til at gå igennem forskellige objekter eller tal. Du kan udføre handlinger på hvert objekt og/eller kun vælge objekter, der opfylder visse kriterier.
Tilføjelsen indeholder også mange andre kodebyggere, et omfattende VBA-kodebibliotek og et sortiment af kodningsværktøjer. Det er et must for enhver VBA -udvikler.
Nu vil vi dække de forskellige typer sløjfer i dybden.
VBA til næste sløjfe
Til Loop Syntax
For næste løkke giver dig mulighed for at gentage en kodeblok et bestemt antal gange. Syntaksen er:
| 12345 | [Dim tæller som heltal]For tæller = Start til slut [trinværdi][Gør noget]Næste [Tæller] |
Hvor elementerne i parentes er valgfrie.
- [Dim tæller så lang] - Deklarerer tællervariablen. Påkrævet, hvis Option Explicit er angivet øverst på dit modul.
- Tæller - En heltalsvariabel, der bruges til at tælle
- Start - Startværdien (eks. 1)
- Ende - Slutværdien (eks. 10)
- [Trinværdi] - Giver dig mulighed for at tælle hvert n heltal i stedet for hvert 1 heltal. Du kan også gå omvendt med en negativ værdi (f.eks. Trin -1)
- [Gør noget] - Koden, der gentages
- Næste [Tæller] - Afslutningserklæring til For Next Loop. Du kan inkludere tælleren eller ej. Jeg anbefaler dog stærkt at inkludere tælleren, da det gør din kode lettere at læse.
Hvis det er forvirrende, skal du ikke bekymre dig. Vi vil gennemgå nogle eksempler:
Tæl til 10
Denne kode tæller til 10 ved hjælp af en For-Next Loop:
| 12345678 | Sub ForEach_CountTo10 ()Dim n Som heltalFor n = 1 til 10MsgBox nNæste nAfslut Sub |
Til Loop Step
Tæl til 10 - kun lige tal
Denne kode tæller til 10 og tæller kun lige tal:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n Som heltalFor n = 2 Til 10 Trin 2MsgBox nNæste nAfslut Sub |
Bemærk, at vi tilføjede “Trin 2”. Dette fortæller For Loop at "træde" gennem tælleren med 2. Vi kan også bruge en negativ trinværdi til at træde omvendt:
For sløjfe trin - omvendt
Nedtælling fra 10
Denne kode nedtælles fra 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n Som heltalFor n = 10 Til 1 Trin -1MsgBox nNæste nMsgBox "Lift off"Afslut Sub |
Slet rækker, hvis cellen er tom
Jeg har oftest brugt et negativt trin For-Loop til at gennemgå celleområder og slette rækker, der opfylder visse kriterier. Hvis du går fra de øverste rækker til de nederste rækker, vil du ødelægge din tæller, når du sletter rækker.
Dette eksempel sletter rækker med tomme celler (startende fra nederste række):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n Som heltalFor n = 10 Til 1 Trin -1If Range ("a" & n) .Value = "" HerefterOmråde ("a" og n) .EntireRow.DeleteAfslut HvisNæste nAfslut Sub |
Indlejret til sløjfe
Du kan “rede” en For Loop inde i en anden For Loop. Vi vil bruge Nested For Loops til at oprette en multiplikationstabel:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable ()Dim række som heltal, kol som heltalFor række = 1 til 9For kol = 1 til 9Celler (række + 1, kol + 1). Værdi = række * kolNæste kolNæste rækkeAfslut Sub |
Afslut For
Med Exit For -sætningen kan du straks forlade en For Next -loop.
Du vil normalt bruge Exit For sammen med en If -erklæring og forlade For Next Loop, hvis en bestemt betingelse er opfyldt.
For eksempel kan du bruge en For Loop til at finde en celle. Når denne celle er fundet, kan du forlade sløjfen for at fremskynde din kode.
Denne kode går gennem rækker 1 til 1000 og leder efter "fejl" i kolonne A. Hvis den findes, vælger koden cellen, advarer dig om den fundne fejl og afslutter løkken:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i As IntegerFor i = 1 til 1000Hvis område ("A" & i) .Value = "fejl" SåOmråde ("A" og i). VælgMsgBox "Fejl fundet"Afslut ForAfslut HvisNæste iAfslut Sub |
Vigtigt: I tilfælde af Nested For Loops forlader Exit For kun den aktuelle For Loop, ikke alle aktive Loops.
Fortsæt til
VBA har ikke kommandoen "Fortsæt", der findes i Visual Basic. I stedet skal du bruge “Exit”.
VBA for hver sløjfe
VBA For Every Loop går gennem alle objekter i en samling:
- Alle celler i et område
- Alle regneark i en projektmappe
- Alle former i et regneark
- Alle åbne projektmapper
Du kan også bruge indlejret for hver sløjfe til at:
- Alle celler i et område på alle regneark
- Alle former på alle regneark
- Alle ark i alle åbne projektmapper
- og så videre…
Syntaksen er:
| 123 | For hvert objekt i samlingen[Gør noget]Næste [Objekt] |
Hvor:
- Objekt - Variabel, der repræsenterer et område, regneark, arbejdsbog, form osv. (F.eks. Rng)
- Kollektion - Indsamling af objekter (f.eks. Rækkevidde (“a1: a10”)
- [Gør noget] - Kodeblok til at køre på hvert objekt
- Næste [Objekt] - Afslutningserklæring. [Objekt] er valgfri, men anbefales stærkt.
For hver celle inden for rækkevidde
Denne kode går gennem hver celle i et område:
| 123456789 | Sub ForEachCell_inRange ()Dim celle som områdeFor hver celle i området ("a1: a10")cell.Value = cell.Offset (0,1) .VærdiNæste celleAfslut Sub |
For hvert regneark i projektmappe
Denne kode går gennem alle regneark i en projektmappe, uden at beskytte hvert ark:
| 123456789 | Sub ForEachSheet_inWorkbook ()Dim ws Som regnearkFor hver gang i regnearkws.Beskyt "adgangskode"Næste wsAfslut Sub |
For hver åben projektmappe
Denne kode gemmer og lukker alle åbne projektmapper:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Som projektmappeFor hver wb i projektmapperwb.Close SaveChanges: = TrueNæste wbAfslut Sub |
For hver form i regnearket
Denne kode vil slette alle former i det aktive ark.
| 123456789 | Sub ForEachShape ()Dim shp som formFor hver shp i ActiveSheet.Shapesshp.SletNæste shpAfslut Sub |
For hver form i hvert regneark i projektmappen
Du kan også rede for hver sløjfe. Her går vi igennem alle former i alle regneark i den aktive projektmappe:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetFor hver gang i regnearkFor hver shp In ws.Shapesshp.SletNæste shpNæste wsAfslut Sub |
For hver - IF Loop
Som vi tidligere har nævnt, kan du bruge en If -sætning i en loop og kun udføre handlinger, hvis visse kriterier er opfyldt.
Denne kode skjuler alle tomme rækker i et område:
| 12345678910 | Sub ForEachCell_inRange ()Dim celle som områdeFor hver celle i området ("a1: a10")Hvis cell.Value = "" Så _cell.EntireRow.Hidden = SandtNæste celleAfslut Sub |
VBA Do While Loop
VBA Do While og Do Through (se næste afsnit) ligner meget. De vil gentage en loop, mens (eller indtil) en betingelse er opfyldt.
Do While Loop gentager en loop, mens en betingelse er opfyldt.
Her er Do While -syntaksen:
| 123 | Gør mens tilstand[Gør noget]Sløjfe |
Hvor:
- Tilstand - Betingelsen for at teste
- [Gør noget] - Kodeblokken skal gentages
Du kan også konfigurere en Do While -sløjfe med betingelsen i slutningen af løkken:
| 123 | Gør[Gør noget]Sløjfe mens tilstand |
Vi vil demonstrere hver enkelt og vise, hvordan de adskiller sig:
Gøre imens
Her er Do While loop -eksemplet, som vi tidligere demonstrerede:
| 12345678 | Sub DoWhileLoop ()Dim n Som heltaln = 1Gør Mens n <11MsgBox nn = n + 1SløjfeAfslut Sub |
Sløjfe mens
Lad os nu køre den samme procedure, bortset fra at vi flytter tilstanden til slutningen af sløjfen:
| 12345678 | Sub DoLoopWhile ()Dim n Som heltaln = 1GørMsgBox nn = n + 1Sløjfe Mens n <11Afslut Sub |
VBA Gør indtil sløjfe
Gør indtil sløjfer vil gentage en sløjfe, indtil en bestemt betingelse er opfyldt. Syntaksen er i det væsentlige den samme som Do While -loops:
| 123 | Gør indtil tilstand[Gør noget]Sløjfe |
og på samme måde kan tilstanden gå i starten eller slutningen af sløjfen:
| 123 | Gør[Gør noget]Sløjfe indtil tilstand |
Gør Indtil
Dette gør indtil loop vil tælle til 10, ligesom vores tidligere eksempler
| 12345678 | Sub DoUntilLoop ()Dim n Som heltaln = 1Gør indtil n> 10MsgBox nn = n + 1SløjfeAfslut Sub |
Sløjfe Indtil
Denne loop til loop vil tælle til 10:
| 12345678 | Sub DoLoopUntil ()Dim n Som heltaln = 1GørMsgBox nn = n + 1Sløjfe Indtil n> 10Afslut Sub |
Afslut Do Loop
Ligesom at bruge Exit For til at afslutte en For Loop, bruger du Exit Do -kommandoen til at afslutte en Do Loop med det samme
| 1 | Afslut Do |
Her er et eksempel på Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i As Integerjeg = 1Gør indtil jeg> 1000Hvis område ("A" & i) .Value = "fejl" SåOmråde ("A" og i). VælgMsgBox "Fejl fundet"Afslut DoAfslut Hvisi = i + 1SløjfeAfslut Sub |
Afslut eller bryd sløjfe
Som vi nævnte ovenfor, kan du bruge Exit For eller Exit Do til at afslutte loops:
| 1 | Afslut For |
| 1 | Afslut Do |
Disse kommandoer skal dog føjes til din kode, før du kører din loop.
Hvis du forsøger at "bryde" en loop, der kører i øjeblikket, kan du prøve at trykke på ESC eller CTRL + Pause på tastaturet. Dette fungerer dog muligvis ikke. Hvis det ikke virker, skal du vente på, at din loop slutter, eller i tilfælde af en endeløs loop, bruge CTRL + ALT + Slet for at tvinge Luk Excel.
Det er derfor, jeg forsøger at undgå Do loops, det er lettere ved et uheld at oprette en endeløs loop, der tvinger dig til at genstarte Excel og muligvis miste dit arbejde.
Flere sløjfeeksempler
Sløjfe gennem rækker
Dette vil gå gennem alle rækker i en kolonne:
| 123456789 | Offentlig Sub LoopThroughRows ()Dim celle som områdeFor hver celle i området ("A: A")Ff cell.value "" derefter MsgBox cell.address & ":" & cell.valueNæste celleAfslut Sub |
Sløjfe gennem kolonner
Dette vil gå gennem alle kolonner i en række:
| 123456789 | Offentlig Sub LoopThroughColumns ()Dim celle som områdeFor hver celle i området ("1: 1")Hvis cell.Value "" Så er MsgBox cell.Address & ":" & cell.ValueNæste celleAfslut Sub |
Loop gennem filer i en mappe
Denne kode går gennem alle filer i en mappe og opretter en liste:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO som objektDim oFolder som objektDim oFile som objektDim i As IntegerIndstil oFSO = CreateObject ("Scripting.FileSystemObject")Indstil oFolder = oFSO.GetFolder ("C: \ Demo)jeg = 2For hver oFile I oFolder.FilesOmråde ("A" og i) .value = oFile.Namei = i + 1Næste oFileAfslut Sub |
Loop Through Array
Denne kode går gennem array 'arrList':
| 123 | For i = LBound (arrList) Til UBound (arrList)MsgBox arrList (i)Næste i |
LBound -funktionen får arrayets "nedre grænse", og UBound får den "øvre grænse".
Sløjfer i Access VBA
De fleste af eksemplerne ovenfor fungerer også i Access VBA. I Access går vi imidlertid gennem Recordset -objektet frem for Range -objektet.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Ved fejl Genoptag næsteDim dbs som databaseDim første som rekordsætIndstil dbs = CurrentDbIndstil rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Med første.Flyt sidste.MoveFirstGør indtil .EOF = TrueMsgBox (rst.Fields ("ClientName")).MoveNextSløjfeAfslut medførst. lukIndstil rst = IntetIndstil dbs = IntetAfslut Sub |
