For å få mest mulig ut av Excel og VBA, må du vite hvordan du bruker løkker effektivt.
I VBA lar loops deg gå gjennom et sett med objekter/verdier og analysere det en etter en. Du kan også utføre spesifikke oppgaver for hver sløyfe.
Her er et enkelt eksempel på bruk av VBA -løkker i Excel.
Anta at du har et datasett, og du vil markere alle cellene i jevne rader. Du kan bruke en VBA -sløyfe til å gå gjennom området og analysere hvert celle -radnummer. Hvis det viser seg å være jevnt, gir du den en farge, ellers lar du den være som den er.
Nå er dette selvfølgelig veldig enkelt å sløyfe i Excel VBA (og du kan også gjøre dette ved å bruke betinget formatering).
I virkeligheten kan du gjøre mye mer med VBA -løkker i Excel som kan hjelpe deg med å automatisere oppgaver.
Her er noen flere praktiske eksempler der VBA -løkker kan være nyttige:
- Sløyfe gjennom en rekke celler og analysere hver celle (marker celler med en bestemt tekst i den).
- Sløyfe gjennom alle regnearkene og gjør noe med hver (for eksempel beskytte/fjerne beskyttelsen).
- Gå gjennom alle de åpne arbeidsbøkene (og lagre hver arbeidsbok eller lukk alle unntatt den aktive arbeidsboken).
- Gå gjennom alle tegnene i en celle (og trekk ut den numeriske delen fra en streng).
- Gå gjennom alle verdiene i en matrise.
- Gå gjennom alle diagrammer/objekter (og gi en kant eller endre bakgrunnsfargen).
For å bruke looper i Excel VBA best, må du vite om de forskjellige typene som finnes og den riktige syntaksen for hver.
I denne opplæringen vil jeg vise frem forskjellige typer Excel VBA -løkker og dekke noen eksempler for hver løkke
Merk: Dette kommer til å bli en enorm opplæring, hvor jeg skal prøve å dekke hver VBA -løkke i detalj. Jeg anbefaler at du bokmerker denne siden for fremtidig referanse.Hvis du er interessert i å lære VBA på den enkle måten, sjekk min Online Excel VBA -opplæring.
For neste sløyfe
Med "For Next" -sløyfen kan du gå gjennom en blokk med kode for det angitte antallet ganger.
For eksempel, hvis jeg ber deg legge til heltallene fra 1 til 10 manuelt, vil du legge til de to første tallene, deretter legge det tredje tallet til resultatet, og deretter legge det fjerde tallet til resultatet, som så videre …
Er det ikke?
Den samme logikken brukes i For Next -løkken i VBA.
Du angir hvor mange ganger du vil at løkken skal kjøres, og du angir også hva du vil at koden skal gjøre hver gang løkken kjøres.
Nedenfor er syntaksen til For Next -løkken:
For Counter = Start To End [Step Value] [Code Block to Execute] Neste [teller]
I For Next -løkken kan du bruke en teller (eller en hvilken som helst variabel) som skal brukes til å kjøre løkken. Denne telleren lar deg kjøre denne sløyfen et nødvendig antall ganger.
For eksempel, hvis jeg vil legge til de første 10 positive heltallene, vil tellerverdien min være fra 1 til 10.
La oss se på noen få eksempler for bedre å forstå hvordan For Next loop fungerer.
Eksempel 1 - Legge til de første 10 positive heltallene
Nedenfor er koden som vil legge til de første 10 positive heltallene ved hjelp av en For Next -løkke.
Det vil da vise en meldingsboks som viser summen av disse tallene.
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 koden er verdien av Total satt til 0 før du går inn i For neste sløyfe.
Når den kommer inn i løkken, holder den den totale verdien etter hver sløyfe. Så etter den første sløyfen, når telleren er 1, blir ‘Total’ -verdien 1, og etter den andre sløyfen blir den 3 (1+2), og så videre.
Og til slutt, når sløyfen slutter, har "Total" -variabelen summen av de første 10 positive heltallene.
En MsgBox viser deretter bare resultatet i en meldingsboks.
Eksempel 2 - Legge til de første 5 jevne positive heltalene
For å oppsummere de fem til og med positive heltallene (dvs. 2,4,6,8 og 10), trenger du en lignende kode med en betingelse for å bare vurdere partallene og ignorere oddetallene.
Her er en kode som gjø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
Vær oppmerksom på at vi startet Count -verdien fra 2 og også brukte ‘Steg 2‘.
Når du bruker 'Steg 2', forteller den koden om å øke 'Count' -verdien med 2 hver gang løkken kjøres.
Så Count -verdien starter fra 2 og blir deretter 4, 6, 8 og 10 når loopingen oppstår.
MERK: En annen måte å gjøre dette på kan være å kjøre løkken fra 1 til 10 og i sløyfen sjekke om tallet er partall eller oddetall. Imidlertid er bruk av Step i dette tilfellet en mer effektiv måte, ettersom det ikke krever at løkken skal kjøres 10 ganger, men bare 5 ganger.
Step -verdien kan også være negativ. I slike tilfeller starter telleren med en høyere verdi og fortsetter å bli redusert med den angitte trinnverdien.
Eksempel 3 - Angi serienummer i de valgte cellene
Du kan også bruke For Next -løkken til å gå gjennom en samling objekter (for eksempel celler eller regneark eller arbeidsbøker),
Her er et eksempel som raskt legger inn serienumre i alle de valgte cellene.
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 Under
Koden ovenfor teller først antallet valgte rader og tildeler deretter variabelen RowCount denne verdien. Vi kjører deretter løkken fra ‘1 til RowCount’.
Vær også oppmerksom på at siden valg kan være et hvilket som helst antall rader, har vi Angi variabelen Rng til utvalg (med linjen ‘Sett Rng = utvalg’). Nå kan vi bruke variabelen ‘Rng’ til å referere til utvalget i koden vår.
Eksempel 4 - Beskytt alle regneark i den aktive arbeidsboken
Du kan bruke "For Next" -sløyfen til å gå gjennom alle regnearkene i den aktive arbeidsboken, og beskytte (eller fjerne beskyttelsen) av hvert regneark.
Nedenfor er koden som gjør dette:
Sub ProtectWorksheets () Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets (i) .Beskytt Neste i End Sub
Koden ovenfor teller antall ark ved å bruke ActiveWorkbook.Worksheets.Count. Dette forteller VBA hvor mange ganger sløyfen må kjøres.
I hvert tilfelle refererer den til den andre arbeidsboken (ved hjelp av regneark (i)) og beskytter den.
Du kan også bruke den samme koden til å fjerne beskyttelsen av regneark. Bare endre linjen Regneark (i) .Beskytt til Regneark (i) .UnProtect.
Nestede ‘For Next’ sløyfer
Du kan bruke nestede 'For Next' -løkker for å få mer kompleks automatisering utført i Excel. En nestet 'For Next' -sløyfe vil bety at det er en' For Next' -loop i en 'For Next' -loop.
La meg vise deg hvordan du bruker dette ved å bruke et eksempel.
Anta at jeg har fem arbeidsbøker åpne i systemet mitt, og jeg vil beskytte alle regnearkene i alle disse arbeidsbøkene.
Nedenfor er koden som gjø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) .Protect Next j Next i End Sub
Ovenstående er en nestet For Next -sløyfe ettersom vi har brukt en For Next -loop i en annen.
"EXIT For" -erklæringer i For Next Loops
Med "Exit For" -utsagn kan du gå helt ut av "For Next" -sløyfen.
Du kan bruke den i tilfeller der du vil at For Next -løkken skal slutte når en bestemt betingelse er oppfylt.
La oss ta et eksempel der du har et sett med tall i kolonne A, og du vil markere alle de negative tallene i rød skrift. I dette tilfellet må vi analysere hver celle for verdien og deretter endre skriftfargen deretter.
Men for å gjøre koden mer effektiv, kan vi først sjekke om det er noen negative verdier på listen eller ikke. Hvis det ikke er noen negative verdier, kan vi bruke Avslutt For -setningen for å komme ut av koden.
Nedenfor er koden som gjø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 Avslutt deretter for hvis Rng (i) .Værdi <0 Deretter Rng (i) .Font.Color = vbRed Neste i End Sub
Når du bruker 'Exit For' -setningen i en nestet 'For Next' -sløyfe, kommer den ut av sløyfen der den kjøres og fortsetter med å utføre den neste linjen i koden etter For Next -sløyfen.
For eksempel, i koden nedenfor, vil "Exit For" -setningen få deg ut av den indre sløyfen, men den ytre sløyfen vil fortsette å fungere.
Sub SampleCode () For i = 1 til 10 For j = 1 til 10 Avslutt for neste J Neste i Avslutt Sub
Do While Loop
En "Do While" -sløyfe lar deg se etter en tilstand og kjøre løkken mens denne betingelsen er oppfylt (eller er SANN).
Det er to typer syntaks i Do While Loop.
Gjør [Mens tilstand] [Kodeblokk for å utføre] Loop
og
Gjør [Code block to Execute] Loop [While condition]
Forskjellen mellom disse to er at i den første blir betingelsen While kontrollert først før en kodeblokk kjøres, og i det andre tilfellet utføres kodeblokken først og deretter mens betingelsen kontrolleres.
Dette betyr at hvis tilstanden While er feil er begge tilfellene, vil koden fremdeles kjøre minst en gang i det andre tilfellet (ettersom tilstanden ‘Mens’ er merket etter at koden er utført én gang).
La oss nå se noen eksempler på bruk av Do While -sløyfer i VBA.
Eksempel 1 - Legg til de første 10 positive heltalene som bruker VBA
Anta at du vil legge til de ti første heltallene med Do While -løkken i VBA.
For å gjøre dette, kan du bruke Do While -sløyfen til det neste tallet er mindre enn eller lik 10. Så snart tallet er større enn 1o, stopper sløyfen.
Her er VBA -koden som vil kjøre denne Do While -løkken og vise resultatet i en meldingsboks.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Resultat = Resultat + i i = i + 1 Loop MsgBox Resultat End Sub
Sløyfen ovenfor fortsetter å fungere til verdien av ‘i’ blir 11. Så snart den blir 11, ender sløyfen (ettersom mens tilstanden blir Falsk).
Innenfor løkken har vi brukt en resultatvariabel som holder den endelige verdien Når løkken er fullført, viser en meldingsboks verdien av variabelen ‘Resultat’.
Eksempel 2 - Skriv inn datoer for den nåværende måneden
La oss si at du vil skrive inn alle datoene for inneværende måned i en regnearkkolonne.
Du kan gjøre det ved å bruke 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
Koden ovenfor angir alle datoene i den første kolonnen i regnearket (starter fra A1). Sløyfene fortsetter til månedsverdien for variabelen 'CMDate' samsvarer med verdien for den nåværende måneden.
Avslutt Do -erklæring
Du kan bruke Exit Do -setningen for å komme ut av løkken. Så snart koden utfører "Exit Do" -linjen, kommer den ut av Do While -løkken og sender kontrollen til neste linje rett etter sløyfen.
For eksempel, hvis du bare vil angi de første 10 datoene, kan du gå ut av sløyfen så snart de første 10 datoene er angitt.
Koden nedenfor vil gjø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 Avslutt deretter CMDate = CMDate + 1 Loop End Sub
I koden ovenfor brukes IF -setningen til å kontrollere om verdien av i er større enn 10 eller ikke. Så snart verdien av 'i' blir 10, blir Exit Do -setningen utført og sløyfen avsluttes.
Gjør til sløyfe
"Gjør inntil" -løkker ligner veldig på "Do While" -løkkene.
I ‘Gjør mens’ løper sløyfen til den gitte betingelsen er oppfylt, mens den i ‘Gjør til’ løkker til den angitte betingelsen er oppfylt.
Det er to typer syntaks i Do Through Loop.
Gjør [Inntil tilstand] [Kodeblokk for å utføre] Loop
og
Gjør [Code block to Execute] Loop [Inntil tilstand]
Forskjellen mellom disse to er at i den første kontrolleres Til -tilstanden først før en kodeblokk utføres, og i det andre tilfellet utføres kodeblokken først og deretter blir Til -tilstanden kontrollert.
Dette betyr at hvis Til -betingelsen er SANN er begge tilfellene, vil koden fremdeles kjøre minst én gang i det andre tilfellet (ettersom ‘Til’ -tilstanden er merket etter at koden er blitt utført én gang).
La oss nå se noen eksempler på bruk av Do Through -sløyfer i VBA.
Merk: Alle eksemplene for Gjør inntil er de samme som for Gjør mens. Disse har blitt modifisert for å vise deg hvordan Do Through -sløyfen fungerer.
Eksempel 1 - Legg til de første 10 positive heltalene som bruker VBA
Anta at du vil legge til de første ti positive heltallene ved hjelp av Do Through -løkken i VBA.
For å gjøre dette må du kjøre sløyfen til det neste tallet er mindre enn eller lik 10. Så snart tallet er større enn 1o, ville sløyfen stoppe.
Her er VBA -koden som vil kjøre denne løkken og vise resultatet i en meldingsboks.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do Fram til i> 10 Resultat = Resultat + i i = i + 1 Loop MsgBox Resultat End Sub
Den ovennevnte løkken fortsetter å fungere til verdien av ‘i’ blir 11. Så snart den blir 11, ender sløyfen (ettersom ‘Inntil’ tilstanden blir sann).
Eksempel 2 - Skriv inn datoer for den nåværende måneden
La oss si at du vil skrive inn alle datoene for inneværende måned i en regnearkkolonne.
Du kan gjøre det ved å bruke følgende Do Through -løkke -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
Koden ovenfor angir alle datoene i den første kolonnen i regnearket (starter fra A1). Sløyfen fortsetter til måneden med variabel CMDate ikke er lik den for inneværende måned.
Avslutt Do -erklæring
Du kan bruke 'Exit Do' -setningen for å komme ut av løkken.
Så snart koden utfører "Exit Do" -linjen, kommer den ut av Do Through -sløyfen og sender kontrollen til neste linje rett etter sløyfen.
For eksempel, hvis du bare vil angi de første 10 datoene, kan du gå ut av sløyfen så snart de første 10 datoene er angitt.
Koden nedenfor vil gjø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 Avslutt deretter Do CMDate = CMDate + 1 Loop End Sub
I koden ovenfor, så snart verdien av ‘i’ blir 10, blir Exit Do -uttalelsen utført og sløyfen avsluttes.
For hver
I VBA kan du gå gjennom et sett med samlinger ved hjelp av "For Every" -sløyfen.
Her er noen eksempler på samlinger i Excel VBA:
- En samling av alle de åpne arbeidsbøkene.
- En samling av alle regneark i en arbeidsbok.
- En samling av alle cellene i et utvalg utvalgte celler.
- En samling av alle diagrammer eller former i arbeidsboken.
Ved å bruke "For Every" -løkken kan du gå gjennom hvert av objektene i en samling og utføre noen handlinger på den.
For eksempel kan du gå gjennom alle regnearkene i en arbeidsbok og beskytte disse, eller du kan gå gjennom alle cellene i utvalget og endre formateringen.
Med sløyfen "For hver" (også referert til som "For hver neste"), trenger du ikke å vite hvor mange objekter det er i en samling.
'For hver' sløyfe ville automatisk gå gjennom hvert objekt og utføre den spesifiserte handlingen. For eksempel, hvis du vil beskytte alle regnearkene i en arbeidsbok, vil koden være den samme om du har en arbeidsbok med 3 regneark eller 30 regneark.
Her er syntaksen til For Every-Next loop i Excel VBA.
For hvert element i samlingen [Kodeblokk å utføre] Neste [element]
La oss nå se et par eksempler på bruk av For Every Loop i Excel.
Eksempel 1 - Gå gjennom alle regnearkene i en arbeidsbok (og beskytt den)
Anta at du har en arbeidsbok der du vil beskytte alle regnearkene.
Under For Hver-Neste loop kan du gjøre dette enkelt:
Sub ProtectSheets () Dim ws As Worksheet For Every ws In ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
I koden ovenfor har vi definert "ws" -variabelen som et regnearkobjekt. Dette forteller VBA at 'ws' skal tolkes som et regnearkobjekt i koden.
Nå bruker vi setningen "For hver" til å gå gjennom hver "ws" (som er et regnearkobjekt) i samlingen av alle regnearkene i den aktive arbeidsboken (gitt av ActiveWorkbook.Worksheets).
Vær oppmerksom på at i motsetning til andre løkker der vi har prøvd å beskytte alle regnearkene i en arbeidsbok, trenger vi ikke bekymre deg for hvor mange regneark det er i arbeidsboken.
Vi trenger ikke å telle disse for å kjøre løkken. For Hver sløyfe sikrer at alle objektene blir analysert en etter en.
Eksempel 2 - Gå gjennom alle de åpne arbeidsbøkene (og lagre alle)
Hvis du jobber med flere arbeidsbøker samtidig, kan det være nyttig å kunne lagre alle disse arbeidsbøkene samtidig.
Nedenfor kan VBA -koden gjøre dette for oss:
Sub SaveAllWorkbooks () Dim wb As Workbook For Every wb In Workbooks wb.Save Next wb End Sub
Vær oppmerksom på at du i denne koden ikke får en melding som ber deg om å lagre arbeidsboken på et bestemt sted (hvis du lagrer den for første gang).
Den lagrer den i standardmappen (det var "Dokumenter" -mappen i mitt tilfelle). Denne koden fungerer best når disse filene allerede er lagret og du gjør endringer og du vil lagre alle arbeidsbøkene raskt.
Eksempel 3 - Gå gjennom alle cellene i et utvalg (fremheve negative verdier)
Ved å bruke "For Every" -løkken kan du gå gjennom alle cellene i et bestemt område eller i det valgte området.
Dette kan være nyttig når du vil analysere hver celle og utføre en handling basert på den.
For eksempel er koden nedenfor som går gjennom alle cellene i utvalget og endrer cellefargen til cellene med negative verdier til rød.
Sub HighlightNegativeCells () Dim Cll som område for hver Cll i utvalg Hvis Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
(Merk at jeg har brukt Cll som et kort variabelnavn for Cell. Det anbefales å ikke bruke objektnavn som Sheets eller Range som variabelnavn)
I koden ovenfor går sløyfen For hver-neste gjennom samlingen av celler i utvalget. IF -setning brukes til å identifisere om celleverdien er negativ eller ikke. I tilfelle det er, får cellen en rød indre farge, ellers går den til neste celle.
Hvis du ikke har et utvalg, og i stedet vil at VBA skal velge alle de fylte cellene i en kolonne, fra en bestemt celle (akkurat som vi bruker Ctrl + Skift + Piltast ned for å velge alle fylte celler), kan du bruk koden nedenfor:
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 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
I eksemplet ovenfor spiller det ingen rolle hvor mange fylte celler det er. Den starter fra celle A1 og analyserer alle sammenhengende fylte celler i kolonnen.
Du trenger heller ikke å ha celle A1 valgt. Du kan velge hvilken som helst fjerntliggende celle, og når koden kjører, vil den fortsatt vurdere alle cellene i kolonne A (fra A1) og farge de negative cellene.
"Avslutt for" -erklæring
Du kan bruke setningen "Avslutt for" i For hver-neste-løkken for å komme ut av løkken. Dette gjøres vanligvis hvis en bestemt betingelse er oppfylt.
For eksempel, i eksempel 3, mens vi går gjennom et sett med celler, kan det være mer effektivt å kontrollere om det er negative verdier eller ikke. Hvis det ikke er noen negative verdier, kan vi bare gå ut av sløyfen og spare litt VBA -behandlingstid.
Nedenfor er VBA -koden som vil gjøre dette:
Sub HighlightNegativeCells () Dim Cll som område for hver Cll i utvalg Hvis WorksheetFunction.Min (Selection)> = 0 Avslutt deretter for If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
Hvor å plassere VBA -koden
Lurer du på hvor VBA -koden går i Excel -arbeidsboken din?
Excel har en VBA -backend kalt VBA -redaktøren. Du må kopiere og lime inn koden i VB Editor -modulvinduet.
Her er trinnene for å gjøre dette:
- Gå til kategorien Utvikler.
- Klikk på Visual Basic -alternativet. Dette vil åpne VB -redaktøren i backend.
- I Project Explorer-ruten i VB Editor, høyreklikker du på et hvilket som helst objekt for arbeidsboken der du vil sette inn koden. Hvis du ikke ser Project Explorer, går du til kategorien View og klikker på Project Explorer.
- Gå til Sett inn og klikk på Modul. Dette vil sette inn et modulobjekt for arbeidsboken din.
- Kopier og lim inn koden i modulvinduet.
