För att få ut det mesta av Excel och VBA måste du veta hur du använder loopar effektivt.
I VBA låter loopar dig gå igenom en uppsättning objekt/värden och analysera det en efter en. Du kan också utföra specifika uppgifter för varje slinga.
Här är ett enkelt exempel på att använda VBA -slingor i Excel.
Anta att du har en dataset och du vill markera alla celler i jämna rader. Du kan använda en VBA -slinga för att gå igenom intervallet och analysera varje cellradnummer. Om det visar sig vara jämnt ger du det en färg, annars lämnar du det som det är.
Nu är detta naturligtvis väldigt enkelt att loopa i Excel VBA (och du kan också göra detta med villkorlig formatering).
I verkligheten kan du göra mycket mer med VBA -loopar i Excel som kan hjälpa dig att automatisera uppgifter.
Här är några mer praktiska exempel där VBA -slingor kan vara användbara:
- Looping genom ett antal celler och analysera varje cell (markera celler med en specifik text i den).
- Bläddra igenom alla kalkylblad och gör något med varje (t.ex. skydda/avskydda det).
- Gå igenom alla öppna arbetsböcker (och spara varje arbetsbok eller stäng alla utom den aktiva arbetsboken).
- Gå igenom alla tecken i en cell (och extrahera den numeriska delen från en sträng).
- Gå igenom alla värden i en array.
- Bläddra igenom alla diagram/objekt (och ge en ram eller ändra bakgrundsfärgen).
För att bäst använda loopar i Excel VBA måste du veta om de olika typerna som finns och rätt syntax för varje.
I den här självstudien visar jag olika typer av Excel VBA -slingor och täcker några exempel för varje slinga
Obs: Detta kommer att bli en enorm handledning, där jag kommer att försöka täcka varje VBA -slinga i detalj. Jag rekommenderar att du bokmärker den här sidan för framtida referens.Om du är intresserad av att lära dig VBA på det enkla sättet, kolla in min Online Excel VBA -utbildning.
För nästa loop
Med "For Next" -slingan kan du gå igenom ett kodblock för det angivna antalet gånger.
Till exempel, om jag ber dig att lägga till heltal från 1 till 10 manuellt, skulle du lägga till de två första talen, sedan lägga till det tredje numret i resultatet, sedan lägga till det fjärde talet i resultatet, som så …
Är det inte?
Samma logik används i For Next -slingan i VBA.
Du anger hur många gånger du vill att slingan ska köras och anger också vad du vill att koden ska göra varje gång slingan körs.
Nedan finns syntaxen för For Next -slingan:
For Counter = Start To End [Step Value] [Code Block to Execute] Nästa [räknare]
I For Next -slingan kan du använda en räknare (eller vilken variabel som helst) som ska användas för att köra slingan. Denna räknare låter dig köra den här slingan ett erforderligt antal gånger.
Till exempel, om jag vill lägga till de första 10 positiva heltalen, skulle mitt räknevärde vara från 1 till 10.
Låt oss titta på några exempel för att bättre förstå hur For Next loop fungerar.
Exempel 1 - Lägga till de första 10 positiva heltalen
Nedan finns koden som kommer att lägga till de första 10 positiva heltalen med en For Next -slinga.
Det kommer då att visa en meddelanderuta som visar summan av dessa nummer.
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 den här koden är värdet på Total satt till 0 innan du går in i For Next -slingan.
När den väl kommer in i slingan håller den det totala värdet efter varje slinga. Så efter den första slingan, när räknaren är 1, blir 'Totalt' värde 1, och efter den andra slingan blir det 3 (1+2), och så vidare.
Och slutligen, när slingan slutar, har variabeln 'Total' summan av de första 10 positiva heltalen.
En MsgBox visar sedan helt enkelt resultatet i en meddelanderuta.
Exempel 2 - Lägga till de första 5 jämna positiva heltalen
För att summera de första fem jämna positiva heltalen (dvs 2,4,6,8 och 10) behöver du en liknande kod med ett villkor för att bara betrakta jämna tal och ignorera udda tal.
Här är en kod som 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
Observera att vi startade Count -värdet från 2 och också använde ‘Steg 2‘.
När du använder 'Steg 2', den berättar för koden att öka "Count" -värdet med 2 varje gång slingan körs.
Så räknevärdet börjar från 2 och blir sedan 4, 6, 8 och 10 när slingan inträffar.
OBS: Ett annat sätt att göra detta kan vara att köra slingan från 1 till 10 och i slingan kontrollera om talet är jämnt eller udda. Att använda Step, i det här fallet, är dock ett mer effektivt sätt eftersom det inte kräver att slingan ska köras 10 gånger, utan bara 5 gånger.
Stegvärdet kan också vara negativt. I sådana fall börjar räknaren med ett högre värde och fortsätter att minska med det angivna stegvärdet.
Exempel 3 - Ange serienummer i de valda cellerna
Du kan också använda For Next -slingan för att gå igenom en samling objekt (t.ex. celler eller kalkylblad eller arbetsböcker),
Här är ett exempel som snabbt anger serienummer i alla markerade 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
Ovanstående kod räknar först antalet valda rader och tilldelar sedan variabeln RowCount detta värde. Vi kör sedan slingan från "1 till RowCount".
Observera också att eftersom val kan vara valfritt antal rader, har vi Ställ in variabeln Rng till markering (med raden 'Set Rng = Selection'). Nu kan vi använda variabeln ‘Rng’ för att referera till urvalet i vår kod.
Exempel 4 - Skydda alla kalkylblad i den aktiva arbetsboken
Du kan använda slingan "För nästa" för att gå igenom alla kalkylblad i den aktiva arbetsboken och skydda (eller avskydda) varje arbetsblad.
Nedan är koden som gör detta:
Sub ProtectWorksheets () Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets (i) .Protect Next i End Sub
Koden ovan räknar antalet ark med ActiveWorkbook.Worksheets.Count. Detta berättar för VBA hur många gånger slingan behöver köras.
I varje fall hänvisar den till Ith -arbetsboken (med hjälp av kalkylblad (i)) och skyddar den.
Du kan också använda samma kod för att avskydda kalkylblad. Ändra bara raden Arbetsblad (i) .Protect till Arbetsblad (i) .UnProtect.
Kapslade "för nästa" slingor
Du kan använda kapslade 'För nästa' -slingor för att få mer komplex automatisering i Excel. En kapslad 'För Nästa' slinga skulle innebära att det finns en 'För Nästa' slinga i en 'För Nästa' slinga.
Låt mig visa dig hur du använder detta med hjälp av ett exempel.
Antag att jag har fem arbetsböcker öppna i mitt system och jag vill skydda alla kalkylblad i alla dessa arbetsböcker.
Nedan är koden som gör detta:
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
Ovanstående är en kapslad för nästa slinga eftersom vi har använt en för nästa slinga inom en annan.
"EXIT For" -uttalanden i For Next Loops
Med "Avsluta för" kan du lämna slingan "För nästa" helt.
Du kan använda den i fall där du vill att For Next -slingan ska sluta när ett visst villkor är uppfyllt.
Låt oss ta ett exempel där du har en uppsättning siffror i kolumn A och du vill markera alla negativa siffror med rött teckensnitt. I det här fallet måste vi analysera varje cell för dess värde och sedan ändra teckensnittsfärgen i enlighet därmed.
Men för att göra koden mer effektiv kan vi först kontrollera om det finns några negativa värden i listan eller inte. Om det inte finns några negativa värden kan vi använda uttalet Avsluta för att helt enkelt komma ur koden.
Nedan är koden som gör detta:
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 Avsluta sedan för om Rng (i) .Värde <0 Sedan Rng (i) .Font.Color = vbRed Nästa i Avsluta sub
När du använder "Exit For" -uttalandet inom en kapslad "For Next" -slinga kommer den ut från loopen där den körs och fortsätter med att köra nästa rad i koden efter For Next -slingan.
Till exempel, i nedanstående kod kommer 'Exit For' -uttalandet att ta dig ur den inre slingan, men den yttre slingan skulle fortsätta att fungera.
Sub SampleCode () För i = 1 till 10 För j = 1 till 10 Avsluta för nästa J Nästa i Avsluta Sub
Gör medan loop
Med en "Do While" -slinga kan du söka efter ett villkor och köra slingan medan villkoret är uppfyllt (eller är SANT).
Det finns två typer av syntax i Do While Loop.
Gör [Medan skick] [Kodblock för att köra] Loop
och
Gör [Code block to Execute] Loop [While condition]
Skillnaden mellan dessa två är att i det första, medan villkoret kontrolleras först innan något kodblock körs, och i det andra fallet, körs kodblocket först och sedan kontrolleras medan -villkoret.
Detta betyder att om medan villkoret While är falskt är båda fallen, kommer koden fortfarande att köras minst en gång i det andra fallet (eftersom "While" -villkoret kontrolleras efter att koden har körts en gång).
Låt oss nu se några exempel på hur du använder Do While -loopar i VBA.
Exempel 1 - Lägg till de första 10 positiva heltalen med VBA
Antag att du vill lägga till de första tio positiva heltalen med Do While -slingan i VBA.
För att göra detta kan du använda Do While -slingan tills nästa siffra är mindre än eller lika med 10. Så snart talet är större än 1o skulle din loop sluta.
Här är VBA -koden som kör denna Do While -slinga och visar resultatet i en meddelanderuta.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Resultat = Resultat + i i = i + 1 Loop MsgBox Result End Sub
Ovanstående slinga fortsätter att fungera tills värdet på ‘i’ blir 11. Så snart det blir 11, slutar slingan (när medan villkoret blir falskt).
Inom slingan har vi använt en resultatvariabel som håller det slutliga värdet När slingan är klar visar en meddelanderuta värdet på variabeln ”Resultat”.
Exempel 2 - Ange datum för den aktuella månaden
Låt oss säga att du vill ange alla datum för den aktuella månaden i en kolumn med kalkylblad.
Du kan göra det med följande Do While -loop -kod:
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 ovan anger alla datum i den första kolumnen i kalkylbladet (från A1). Slingorna fortsätter tills månadsvärdet för variabeln 'CMDate' matchar värdet för den aktuella månaden.
Exit Do -uttalande
Du kan använda Exit Do -satsen för att komma ur slingan. Så snart koden kör "Exit Do" -raden, kommer den ut från Do While -slingan och skickar kontrollen till nästa rad direkt efter slingan.
Till exempel, om du bara vill ange de första tio datumen, kan du lämna slingan så snart de första tio datumen har angetts.
Koden nedan gör detta:
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 Om i> = 10 Avsluta Gör CMDate = CMDate + 1 Loop End Sub
I ovanstående kod används IF -satsen för att kontrollera om värdet på i är större än 10 eller inte. Så snart värdet på 'i' blir 10, körs Exit Do -satsen och slingan slutar.
Gör tills slingan
"Gör tills" -öglor är mycket som "gör medan" -öglorna.
I "Do While" körs slingan tills det givna villkoret är uppfyllt, medan det i "Do Through" slingas tills det angivna villkoret är uppfyllt.
Det finns två typer av syntax i Gör tills slingan.
Gör [Till tillstånd] [Kodblock för att köra] Loop
och
Gör [Code block to Execute] Loop [Till skick]
Skillnaden mellan dessa två är att i det första kontrolleras Till -villkoret först innan något kodblock körs, och i det andra fallet körs kodblocket först och sedan kontrolleras Till -villkoret.
Det betyder att om Till -villkoret är SANT är båda fallen, kommer koden fortfarande att köras minst en gång i det andra fallet (eftersom villkoret ”Till” kontrolleras efter att koden har körts en gång).
Låt oss nu se några exempel på hur du använder Do Through -slingor i VBA.
Obs! Alla exemplen för Gör tills är desamma som när du gör. Dessa har modifierats för att visa hur Do Till -slingan fungerar.
Exempel 1 - Lägg till de första 10 positiva heltalen med VBA
Antag att du vill lägga till de första tio positiva heltalen med Do Till -slingan i VBA.
För att göra detta måste du köra slingan tills nästa siffra är mindre än eller lika med 10. Så snart talet är större än 1o, skulle din slinga stanna.
Här är VBA -koden som kommer att köra den här slingan och visa resultatet i en meddelanderuta.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Gör tills i> 10 Resultat = Resultat + i i = i + 1 Loop MsgBox Resultat End Sub
Ovanstående slinga fortsätter att fungera tills värdet på 'i' blir 11. Så snart det blir 11, slutar slingan (när villkoret 'Till' blir sant).
Exempel 2 - Ange datum för den aktuella månaden
Låt oss säga att du vill ange alla datum för den aktuella månaden i en kolumn med kalkylblad.
Du kan göra det genom att använda följande Do Till -loop -kod:
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 ovan anger alla datum i den första kolumnen i kalkylbladet (från A1). Slingan fortsätter tills månaden för variabel CMDate inte är lika med den för den aktuella månaden.
Exit Do -uttalande
Du kan använda "Exit Do" -uttalandet för att komma ur slingan.
Så snart koden kör "Exit Do" -raden kommer den ut från Do Till -slingan och skickar kontrollen till nästa rad direkt efter slingan.
Till exempel, om du bara vill ange de första tio datumen, kan du avsluta öglan så snart de första tio datumen har angetts.
Koden nedan gör detta:
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 Om i> = 10 Avsluta Gör CMDate = CMDate + 1 Loop End Sub
I koden ovan, så snart värdet på 'i' blir 10, utförs Exit Do statment och slingan slutar.
För varje
I VBA kan du bläddra igenom en uppsättning samlingar med hjälp av "För varje" -slingan.
Här är några exempel på samlingar i Excel VBA:
- En samling av alla öppna arbetsböcker.
- En samling av alla kalkylblad i en arbetsbok.
- En samling av alla celler i ett antal utvalda celler.
- En samling av alla diagram eller former i arbetsboken.
Med hjälp av "För varje" -slingan kan du gå igenom vart och ett av objekten i en samling och utföra några åtgärder på den.
Till exempel kan du gå igenom alla kalkylblad i en arbetsbok och skydda dessa, eller så kan du gå igenom alla celler i markeringen och ändra formateringen.
Med slingan "För varje" (även kallad "För varje nästa" -slinga) behöver du inte veta hur många objekt som finns i en samling.
"För varje" slinga skulle automatiskt gå igenom varje objekt och utföra den angivna åtgärden. Till exempel, om du vill skydda alla kalkylblad i en arbetsbok, skulle koden vara densamma om du har en arbetsbok med tre kalkylblad eller 30 kalkylblad.
Här är syntaxen för For Each-Next loop i Excel VBA.
För varje element i samlingen [Kodblock att köra] Nästa [element]
Låt oss nu se ett par exempel på hur du använder For each loop i Excel.
Exempel 1 - Gå igenom alla kalkylblad i en arbetsbok (och skydda den)
Anta att du har en arbetsbok där du vill skydda alla kalkylblad.
Nedan för varje-nästa slinga kan göra detta enkelt:
Sub ProtectSheets () Dim ws As Worksheet For each ws In ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
I koden ovan har vi definierat "ws" -variabel som ett kalkylbladobjekt. Detta berättar för VBA att 'ws' ska tolkas som ett kalkylbladsobjekt i koden.
Nu använder vi satsen "För varje" för att gå igenom varje "ws" (vilket är ett kalkylbladsobjekt) i samlingen av alla kalkylblad i den aktiva arbetsboken (ges av ActiveWorkbook.Worksheets).
Observera att till skillnad från andra loopar där vi har försökt att skydda alla kalkylblad i en arbetsbok, behöver vi här inte oroa oss för hur många kalkylblad som finns i arbetsboken.
Vi behöver inte räkna dessa för att köra slingan. För Varje slinga säkerställer att alla objekt analyseras en efter en.
Exempel 2 - Gå igenom alla öppna arbetsböcker (och spara alla)
Om du arbetar med flera arbetsböcker samtidigt kan det vara praktiskt att kunna spara alla dessa arbetsböcker samtidigt.
Nedan VBA -kod kan göra detta för oss:
Sub SaveAllWorkbooks () Dim wb som arbetsbok för varje wb i arbetsböcker wb.Save nästa wb End Sub
Observera att i den här koden får du ingen uppmaning som ber dig spara arbetsboken på en specifik plats (om du sparar den för första gången).
Det sparar det i standardmappen (det var "Dokument" -mappen i mitt fall). Den här koden fungerar bäst när dessa filer redan är sparade och du gör ändringar och du vill spara alla arbetsböcker snabbt.
Exempel 3 - Gå igenom alla celler i ett urval (markera negativa värden)
Med hjälp av "För varje" -slingan kan du gå igenom alla celler i ett specifikt intervall eller i det valda intervallet.
Detta kan vara till hjälp när du vill analysera varje cell och utföra en åtgärd baserad på den.
Till exempel nedan är koden som går igenom alla celler i urvalet och ändrar cellfärgen på cellerna med negativa värden till röd.
Sub HighlightNegativeCells () Dim Cll som intervall för varje Cll i urval Om Cll.Value <0 Sedan Cll.Interior.Color = vbRed End If Next Cll End Sub
(Observera att jag har använt Cll som ett kort variabelnamn för Cell. Det är lämpligt att inte använda objektnamn som Sheets eller Range som variabelnamn)
I ovanstående kod går slingan För varje nästa genom samlingen av celler i urvalet. IF -sats används för att identifiera om cellvärdet är negativt eller inte. Om det är så får cellen en röd inre färg, annars går den till nästa cell.
Om du inte har ett urval och istället vill att VBA ska markera alla fyllda celler i en kolumn, från en specifik cell (precis som vi använder Ctrl + Skift + nedåtpilen för att markera alla fyllda celler) kan du använd koden nedan:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) För varje Cll In Rng If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
I exemplet ovan spelar det ingen roll hur många fyllda celler det finns. Den startar från cell A1 och analyserar alla sammanhängande fyllda celler i kolumnen.
Du behöver inte heller markera cell A1. Du kan välja vilken avlägsen cell som helst och när koden körs kommer den fortfarande att beakta alla celler i kolumn A (från A1) och färga de negativa cellerna.
"Exit For" -uttalande
Du kan använda uttalandet "Avsluta för" i slingan För varje nästa för att komma ut ur slingan. Detta görs vanligtvis om ett visst villkor är uppfyllt.
Till exempel, i exempel 3, när vi går igenom en uppsättning celler, kan det vara mer effektivt att kontrollera om det finns några negativa värden eller inte. Om det inte finns några negativa värden kan vi helt enkelt lämna slingan och spara lite VBA -behandlingstid.
Nedan är VBA -koden som gör detta:
Sub HighlightNegativeCells () Dim Cll som intervall för varje Cll i urval Om WorksheetFunction.Min (Selection)> = 0 Avsluta sedan för If Cll.Value <0 Sedan Cll.Interior.Color = vbRed End If Next Cll End Sub
Var ska man lägga VBA -koden
Undrar du vart VBA -koden går i din Excel -arbetsbok?
Excel har en VBA -backend som kallas VBA -editor. Du måste kopiera och klistra in koden i fönstret VB Editor -modulkod.
Här är stegen för att göra detta:
- Gå till fliken Utvecklare.
- Klicka på alternativet Visual Basic. Detta öppnar VB -redigeraren i backend.
- I fönstret Projektutforskare i VB-redigeraren högerklickar du på valfritt objekt för arbetsboken där du vill infoga koden. Om du inte ser Project Explorer går du till fliken View och klickar på Project Explorer.
- Gå till Infoga och klicka på Modul. Detta kommer att infoga ett modulobjekt för din arbetsbok.
- Kopiera och klistra in koden i modulfönstret.
