For at få mest muligt ud af Excel og VBA skal du vide, hvordan du bruger loops effektivt.
I VBA giver loops dig mulighed for at gå igennem et sæt objekter/værdier og analysere det en efter en. Du kan også udføre specifikke opgaver for hver sløjfe.
Her er et enkelt eksempel på brug af VBA -sløjfer i Excel.
Antag, at du har et datasæt, og du vil fremhæve alle cellerne i lige rækker. Du kan bruge en VBA -sløjfe til at gå igennem området og analysere hvert cellereserienummer. Hvis det viser sig at være jævnt, giver du det en farve, ellers lader du det være som det er.
Nu er dette naturligvis meget enkelt med looping i Excel VBA (og du kan også gøre dette ved hjælp af betinget formatering).
I virkeligheden kan du gøre meget mere med VBA -sløjfer i Excel, der kan hjælpe dig med at automatisere opgaver.
Her er nogle mere praktiske eksempler, hvor VBA -sløjfer kan være nyttige:
- Looping gennem en række celler og analysere hver celle (fremhæv celler med en bestemt tekst i den).
- Sløjfer igennem alle regnearkene og gør noget med hver (f.eks. Beskyt/afbeskyt det).
- Gennemgå alle de åbne projektmapper (og gem hver projektmappe eller luk alle undtagen den aktive projektmappe).
- Gennemgå alle tegnene i en celle (og udtræk den numeriske del fra en streng).
- Gennemgå alle værdierne i en matrix.
- Gennemgå alle diagrammer/objekter (og giv en ramme eller skift baggrundsfarven).
For nu bedst at bruge loops i Excel VBA, skal du vide om de forskellige slags, der findes og den korrekte syntaks for hver.
I denne vejledning viser jeg forskellige typer Excel VBA -loops og dækker et par eksempler for hver loop
Bemærk: Dette bliver en kæmpe vejledning, hvor jeg vil forsøge at dække hver VBA -sløjfe i detaljer. Jeg anbefaler, at du bogmærker denne side til fremtidig reference.Hvis du er interesseret i at lære VBA på den nemme måde, så tjek min Online Excel VBA -træning.
Til næste sløjfe
'For næste' -sløjfen giver dig mulighed for at gå gennem en kodeblok i det angivne antal gange.
For eksempel, hvis jeg beder dig om at tilføje heltalene fra 1 til 10 manuelt, vil du tilføje de to første tal, derefter tilføje det tredje tal til resultatet og derefter tilføje det fjerde tal til resultatet, som så videre …
Er det ikke?
Den samme logik bruges i For Next -løkken i VBA.
Du angiver, hvor mange gange du vil have løkken til at køre, og angiver også, hvad du vil have koden til at gøre hver gang løkken køres.
Nedenfor er syntaksen for For Next loop:
For tæller = Start til afslutning [trinværdi] [Kodeblok til udførelse] Næste [tæller]
I For Next -løkken kan du bruge en tæller (eller en hvilken som helst variabel), der bruges til at køre sløjfen. Denne tæller giver dig mulighed for at køre denne loop i et påkrævet antal gange.
For eksempel, hvis jeg vil tilføje de første 10 positive heltal, så vil min tællerværdi være fra 1 til 10.
Lad os se på et par eksempler for bedre at forstå, hvordan For Next loop fungerer.
Eksempel 1 - Tilføjelse af de første 10 positive heltal
Nedenfor er koden, der tilføjer de første 10 positive heltal ved hjælp af en For Next loop.
Det vil derefter vise en meddelelsesboks, der viser summen af disse tal.
Sub AddNumbers () Dim Total As Integer Dim Count As Integer Total = 0 For Count = 1 To 10 Total = Total + Count Next Count MsgBox Total End Sub
I denne kode er værdien af Total sat til 0, før du går ind i For Næste loop.
Når den kommer ind i sløjfen, holder den den samlede værdi efter hver sløjfe. Så efter den første sløjfe, når tælleren er 1, bliver 'Total' -værdien 1, og efter den anden sløjfe bliver den til 3 (1+2) osv.
Og endelig, når sløjfen slutter, har variablen 'Total' summen af de første 10 positive heltal.
En MsgBox viser derefter blot resultatet i en meddelelsesboks.
Eksempel 2 - Tilføjelse af de første 5 lige positive heltal
For at opsummere de første fem lige positive heltal (dvs. 2,4,6,8 og 10) har du brug for en lignende kode med en betingelse for kun at betragte lige numre og ignorere de ulige tal.
Her er en kode, der gør det:
Sub AddEvenNumbers () Dim Total As Integer Dim Count As Integer Total = 0 For Count = 2 To 10 Step 2 Total = Total + Count Next Count MsgBox Total End Sub
Bemærk, at vi startede Count -værdien fra 2 og også brugte ‘Trin 2‘.
Når du bruger 'Trin 2', fortæller den koden at øge værdien 'Count' med 2 hver gang loopet køres.
Så Count -værdien starter fra 2 og bliver derefter 4, 6, 8 og 10, når loopingen opstår.
BEMÆRK: En anden måde at gøre dette på kan være at køre løkken fra 1 til 10 og inden for sløjfen kontrollere, om tallet er lige eller ulige. Imidlertid er brug af Step i dette tilfælde en mere effektiv måde, da det ikke kræver, at loopet kører 10 gange, men kun 5 gange.
Trinværdien kan også være negativ. I sådanne tilfælde starter tælleren med en højere værdi og bliver ved med at blive reduceret med den angivne trinværdi.
Eksempel 3 - Indtastning af serienummer i de udvalgte celler
Du kan også bruge For Next -løkken til at gennemgå en samling af objekter (f.eks. Celler eller regneark eller projektmapper),
Her er et eksempel, der hurtigt indtaster serienumre i alle de valgte celler.
Sub EnterSerialNumber () Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset (Counter - 1, 0) .Value = Counter Next Counter End Sub
Ovenstående kode tæller først antallet af markerede rækker og tildeler derefter variablen RowCount denne værdi. Vi kører derefter løkken fra '1 til RowCount'.
Bemærk også, at da valg kan være et vilkårligt antal rækker, har vi Indstil variablen Rng til markering (med linjen 'Set Rng = Selection'). Nu kan vi bruge variablen 'Rng' til at henvise til markeringen i vores kode.
Eksempel 4 - Beskyt alle regneark i den aktive projektmappe
Du kan bruge 'Til næste' -løkken til at gå igennem alle regnearkene i den aktive projektmappe og beskytte (eller fjerne beskyttelsen) af hvert regneark.
Nedenfor er koden, der gør dette:
Sub ProtectWorksheets () Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets (i) .Protect Next i End Sub
Ovenstående kode tæller antallet af ark ved hjælp af ActiveWorkbook.Worksheets.Count. Dette fortæller VBA, hvor mange gange sløjfen skal køres.
I hvert tilfælde refererer den til den Ith -projektmappe (ved hjælp af regneark (i)) og beskytter den.
Du kan også bruge den samme kode til at afbeskytte regneark. Bare skift linjen Arbejdsark (i) .Beskyt til Regneark (i) .UnProtect.
Indlejrede 'til næste' sløjfer
Du kan bruge indlejrede 'For Next' -sløjfer til at få mere kompleks automatisering udført i Excel. En indlejret 'For Næste' sløjfe ville betyde, at der er en 'Til Næste' sløjfe inden for en 'Til Næste' sløjfe.
Lad mig vise dig, hvordan du bruger dette ved hjælp af et eksempel.
Antag, at jeg har 5 projektmapper åbne i mit system, og jeg vil beskytte alle regnearkene i alle disse projektmapper.
Nedenfor er koden, der gør dette:
Sub ProtectWorksheets () Dim i As Integer Dim j As Integer For i = 1 To Workbooks.Count For j = 1 To Workbooks (i) .Worksheets.Count Workbooks (i) .Worksheets (j) .Beskyt Næste j Næste i Afslut Sub
Ovenstående er en indlejret For Next loop, da vi har brugt en For Next loop inden for en anden.
'EXIT For' -erklæringer i For næste sløjfer
'Exit For' -erklæring giver dig mulighed for at afslutte 'For Next' -sløjfen helt.
Du kan bruge den i tilfælde, hvor du vil have, at For Næste loop skal slutte, når en bestemt betingelse er opfyldt.
Lad os tage et eksempel, hvor du har et sæt tal i kolonne A, og du vil fremhæve alle de negative tal med rød skrifttype. I dette tilfælde skal vi analysere hver celle for dens værdi og derefter ændre skrifttypefarven i overensstemmelse hermed.
Men for at gøre koden mere effektiv kan vi først kontrollere, om der er negative værdier på listen eller ej. Hvis der ikke er negative værdier, kan vi bruge erklæringen Afslut for simpelthen at komme ud af koden.
Nedenfor er koden, der gør dette:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Counter = Rng.Count For i = 1 To Counter If WorksheetFunction.Min (Rng)> = 0 Afslut derefter For Hvis Rng (i) .Værdi <0 Herefter Rng (i) .Font.Color = vbRed Næste i Afslut Sub
Når du bruger 'Exit For' -erklæringen i en indlejret 'For Next' -sløjfe, kommer den ud af sløjfen, hvor den udføres, og fortsætter med at udføre den næste linje i koden efter For Next -løkken.
For eksempel i nedenstående kode vil 'Exit For' -sætningen få dig ud af den indre sløjfe, men den ydre sløjfe vil fortsætte med at fungere.
Subprøvekode () For i = 1 til 10 For j = 1 til 10 Afslut til næste J Næste i Afslut Sub
Gør Mens Loop
En 'Do While' -sløjfe giver dig mulighed for at kontrollere for en tilstand og køre løkken, mens denne betingelse er opfyldt (eller er SAND).
Der er to typer syntaks i Do While Loop.
Gør [Mens tilstand] [Kodeblok til udførelse] Loop
og
Gør [Kodeblok til udførelse] Loop [Mens tilstand]
Forskellen mellem disse to er, at i det første kontrolleres tilstanden While først, før en kodeblok udføres, og i det andet tilfælde udføres kodeblokken først, og derefter kontrolleres tilstanden While.
Dette betyder, at hvis tilstanden While er falsk begge tilfælde er, vil koden stadig køre mindst en gang i det andet tilfælde (da tilstanden ‘Mens’ er markeret, efter at koden er blevet udført én gang).
Lad os nu se nogle eksempler på brug af Do While -loops i VBA.
Eksempel 1 - Tilføj de første 10 positive heltal ved hjælp af VBA
Antag, at du vil tilføje de første ti positive heltal ved hjælp af Do While -løkken i VBA.
For at gøre dette kan du bruge Do While -sløjfen, indtil det næste tal er mindre end eller lig med 10. Så snart tallet er større end 1o, stopper din loop.
Her er VBA -koden, der vil køre denne Do While -sløjfe, og vis resultatet i en meddelelsesboks.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Resultat = Resultat + i i = i + 1 Loop MsgBox Resultat End Sub
Ovenstående sløjfe fortsætter med at fungere, indtil værdien af ‘i’ bliver til 11. Så snart den bliver til 11, slutter sløjfen (som mens betingelsen bliver Falsk).
Inden for løkken har vi brugt en resultatvariabel, der holder den endelige værdi Når løkken er afsluttet, viser en meddelelsesboks værdien af variablen 'Resultat'.
Eksempel 2 - Indtast datoer for den aktuelle måned
Lad os sige, at du vil indtaste alle datoerne for den aktuelle måned i en regnearkskolonne.
Du kan gøre det ved at bruge følgende Do While -loop -kode:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Ovenstående kode angiver alle datoerne i den første kolonne i regnearket (startende fra A1). Sløjferne fortsætter, indtil månedsværdien af variablen 'CMDate' matcher værdien for den aktuelle måned.
Afslut Do -erklæring
Du kan bruge Exit Do -sætningen til at komme ud af løkken. Så snart koden udfører 'Exit Do' -linjen, kommer den ud af Do While -sløjfen og sender kontrollen til den næste linje lige efter sløjfen.
Hvis du f.eks. Kun vil indtaste de første 10 datoer, kan du forlade kredsløbet, så snart de første 10 datoer er indtastet.
Nedenstående kode vil gøre dette:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 Hvis i> = 10 Afslut derefter Do CMDate = CMDate + 1 Loop End Sub
I ovenstående kode bruges IF -sætningen til at kontrollere, om værdien af i er større end 10 eller ej. Så snart værdien af 'i' bliver 10, eksekveres Exit Do -sætningen, og sløjfen slutter.
Gør indtil sløjfe
‘Do Do’ -sløjfer ligner meget‘ Do While’ -sløjferne.
I 'Do While' kører loop'en, indtil den givne betingelse er opfyldt, mens den i 'Do indtil' sløjfer, indtil den angivne betingelse er opfyldt.
Der er to typer syntaks i Do indtil Loop.
Gør [Indtil tilstand] [Kodeblok til udførelse] Loop
og
Gør [Kodeblok til udførelse] Loop [Indtil tilstand]
Forskellen mellem disse to er, at i den første kontrolleres indtil tilstand først, før en kodeblok udføres, og i det andet tilfælde udføres kodeblokken først, og derefter kontrolleres indtil tilstanden.
Dette betyder, at hvis Till -betingelsen er SAND er begge tilfælde, vil koden stadig køre mindst én gang i det andet tilfælde (da betingelsen ‘Indtil’ er markeret, efter at koden er blevet udført én gang).
Lad os nu se nogle eksempler på brug af Do Through -sløjfer i VBA.
Bemærk: Alle eksemplerne for Do Do er de samme som Do While. Disse er blevet ændret for at vise dig, hvordan Do Through -løkken fungerer.
Eksempel 1 - Tilføj de første 10 positive heltal ved hjælp af VBA
Antag, at du vil tilføje de første ti positive heltal ved hjælp af Do Till -løkken i VBA.
For at gøre dette skal du køre løkken, indtil det næste tal er mindre end eller lig med 10. Så snart tallet er større end 1o, stopper din løkke.
Her er VBA -koden, der vil køre denne loop og vise resultatet i en meddelelsesboks.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do indtil i> 10 Resultat = Resultat + i i = i + 1 Loop MsgBox Resultat End Sub
Ovenstående sløjfe fortsætter med at fungere, indtil værdien af 'i' bliver til 11. Så snart den bliver til 11, slutter sløjfen (når tilstanden 'Indtil' bliver sand).
Eksempel 2 - Indtast datoer for den aktuelle måned
Lad os sige, at du vil indtaste alle datoerne for den aktuelle måned i en regnearkskolonne.
Du kan gøre det ved at bruge følgende Do Till -loop -kode:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do until Month (CMDate) Month (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Ovenstående kode angiver alle datoerne i den første kolonne i regnearket (startende fra A1). Sløjfen fortsætter, indtil måneden med variabel CMDate ikke er lig med den aktuelle måned.
Afslut Do -erklæring
Du kan bruge 'Exit Do' -sætningen til at komme ud af løkken.
Så snart koden udfører 'Exit Do' -linjen, kommer den ud af Do Till -sløjfen og sender kontrollen til den næste linje lige efter sløjfen.
Hvis du f.eks. Kun vil indtaste de første 10 datoer, kan du forlade kredsløbet, så snart de første 10 datoer er indtastet.
Nedenstående kode vil gøre dette:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do until Month (CMDate) Month (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 Hvis i> = 10 Afslut derefter Do CMDate = CMDate + 1 Loop End Sub
I ovenstående kode, så snart værdien af 'i' bliver til 10, udføres Exit Do -erklæring, og sløjfen slutter.
For hver
I VBA kan du gå igennem et sæt samlinger ved hjælp af 'For hver' -sløjfen.
Her er nogle eksempler på samlinger i Excel VBA:
- En samling af alle de åbne projektmapper.
- En samling af alle regneark i en projektmappe.
- En samling af alle cellerne i en række udvalgte celler.
- En samling af alle diagrammer eller former i projektmappen.
Ved hjælp af 'For hver' -løkken kan du gå gennem hvert af objekterne i en samling og udføre en handling på det.
For eksempel kan du gå igennem alle regnearkene i en projektmappe og beskytte disse, eller du kan gå igennem alle cellerne i markeringen og ændre formateringen.
Med 'For hver'-sløjfen (også kaldet' For hver-næste'-sløjfen) behøver du ikke at vide, hvor mange objekter der er i en samling.
'For hver' loop vil automatisk gå gennem hvert objekt og udføre den angivne handling. For eksempel, hvis du vil beskytte alle regnearkene i en projektmappe, vil koden være den samme, uanset om du har en projektmappe med 3 regneark eller 30 regneark.
Her er syntaksen for For Every-Next loop i Excel VBA.
For hvert element i samlingen [Kodeblok til udførelse] Næste [element]
Lad os nu se et par eksempler på brug af For Every Loop i Excel.
Eksempel 1 - Gå gennem alle regnearkene i en projektmappe (og beskytt den)
Antag, at du har en projektmappe, hvor du vil beskytte alle regnearkene.
Nedenunder for hver-næste loop kan nemt gøre dette:
Sub ProtectSheets () Dim ws Som regneark for hver ws i ActiveWorkbook.Worksheets ws.Protect Næste ws End Sub
I ovenstående kode har vi defineret 'ws' -variabel som et regnearksobjekt. Dette fortæller VBA, at 'ws' skal tolkes som et regnearksobjekt i koden.
Nu bruger vi sætningen 'For hver' til at gennemgå hver 'ws' (som er et regnearkobjekt) i samlingen af alle regnearkene i den aktive projektmappe (givet af ActiveWorkbook.Worksheets).
Bemærk, at i modsætning til andre sløjfer, hvor vi har forsøgt at beskytte alle regnearkene i en projektmappe, skal vi her ikke bekymre os om, hvor mange regneark der er i projektmappen.
Vi behøver ikke at tælle disse for at køre løkken. For Hver sløjfe sikrer, at alle objekterne analyseres en efter en.
Eksempel 2 - Gå gennem alle de åbne projektmapper (og gem alle)
Hvis du arbejder med flere projektmapper på samme tid, kan det være praktisk at kunne gemme alle disse projektmapper på én gang.
Nedenfor VBA -kode kan gøre dette for os:
Sub SaveAllWorkbooks () Dim wb som projektmappe for hver wb i projektmapper wb. Gem næste wb End Sub
Bemærk, at i denne kode får du ikke en prompt, der beder dig om at gemme projektmappen et bestemt sted (hvis du gemmer den for første gang).
Det gemmer det i standardmappen (det var mappen ‘Dokumenter’ i mit tilfælde). Denne kode fungerer bedst, når disse filer allerede er gemt, og du foretager ændringer, og du vil gemme alle projektmapper hurtigt.
Eksempel 3 - Gennemgå alle cellerne i et udvalg (fremhæv negative værdier)
Ved hjælp af 'For hver' -sløjfen kan du gå igennem alle cellerne i et specifikt område eller i det valgte område.
Dette kan være nyttigt, når du vil analysere hver celle og udføre en handling baseret på den.
For eksempel er nedenstående kode, der går igennem alle cellerne i markeringen og ændrer cellens farve med negative værdier til rød.
Sub HighlightNegativeCells () Dim Cll som område for hver Cll i markering Hvis Cll.Value <0 Herefter Cll.Interior.Color = vbRed End If Next Cll End Sub
(Bemærk, jeg har brugt Cll som et kort variabelnavn til Cell. Det tilrådes ikke at bruge objektnavne, f.eks. Sheets eller Range som variabelnavne)
I ovenstående kode går For Every-Next loop gennem samlingen af celler i markeringen. IF -sætning bruges til at identificere, om celleværdien er negativ eller ej. Hvis det er tilfældet, får cellen en rød indvendig farve, ellers går den til den næste celle.
Hvis du ikke har et valg, og i stedet vil have VBA til at vælge alle de fyldte celler i en kolonne, startende fra en bestemt celle (ligesom vi bruger Ctrl + Skift + Piletast ned til at vælge alle fyldte celler), kan du brug nedenstående kode:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) For hver Cll In Rng If Cll.Value <0 Herefter Cll.Interior.Color = vbRed End If Next Cll End Sub
I eksemplet ovenfor er det ligegyldigt, hvor mange fyldte celler der er. Det starter fra celle A1 og analyserer alle de sammenhængende fyldte celler i kolonnen.
Du behøver heller ikke have valgt celle A1. Du kan have valgt enhver fjerntliggende celle, og når koden kører, vil den stadig overveje alle cellerne i kolonne A (startende fra A1) og farve de negative celler.
'Exit For' erklæring
Du kan bruge udsendelsen 'Afslut til' i For hvert-næste-løkken til at komme ud af løkken. Dette gøres normalt, hvis en bestemt betingelse er opfyldt.
For eksempel i eksempel 3, da vi gennemgår et sæt celler, kan det være mere effektivt at kontrollere, om der er negative værdier eller ej. Hvis der ikke er negative værdier, kan vi simpelthen afslutte sløjfen og spare lidt VBA -behandlingstid.
Nedenfor er VBA -koden, der gør dette:
Sub HighlightNegativeCells () Dim Cll som område for hver Cll i markering Hvis WorksheetFunction.Min (Selection)> = 0 Afslut derefter For hvis Cll.Value <0 Herefter Cll.Interior.Color = vbRed End If Next Cll End Sub
Hvor skal man placere VBA -koden
Gad vide, hvor VBA -koden går i din Excel -projektmappe?
Excel har en VBA -backend kaldet VBA -editoren. Du skal kopiere og indsætte koden i vinduet VB Editor -modulkode.
Her er trinene til at gøre dette:
- Gå til fanen Udvikler.
- Klik på indstillingen Visual Basic. Dette åbner VB -editoren i backend.
- Højreklik på et objekt til projektmappen, hvor du vil indsætte koden, i ruden Project Explorer i VB Editor. Hvis du ikke kan se Project Explorer, skal du gå til fanen View og klikke på Project Explorer.
- Gå til Indsæt og klik på Modul. Dette vil indsætte et modulobjekt til din projektmappe.
- Kopier og indsæt koden i modulvinduet.
