För att fungera effektivt i VBA måste du förstå Loops.
Loops låter dig upprepa ett kodblock ett visst antal gånger eller upprepa ett kodblock på varje objekt i en uppsättning objekt.
Först kommer vi att visa dig några exempel för att visa vad loopar kan. Då lär vi dig allt om slingor.
VBA Loop Quick Exempel
För varje slinga
För varje loop går det genom varje objekt i en samling, till exempel varje kalkylblad i arbetsboken eller varje cell i ett område.
Gå igenom alla kalkylblad i arbetsboken
Den här koden går igenom alla kalkylblad i arbetsboken, vilket döljer varje ark:
| 12345678 | Sub LoopThroughSheets ()Dim ws Som arbetsbladFör varje ws i kalkylbladws.Visible = TrueNästaAvsluta Sub |
Slinga genom alla celler i intervallet
Den här koden går igenom ett cellintervall och testar om cellvärdet är negativt, positivt eller noll:
| 1234567891011121314 | Sub If_Loop ()Dim Cell som intervallFör varje cell i intervallet ("A2: A6")Om Cell.Value> 0 DåCell.Offset (0, 1) .Value = "Positive"ElseIf Cell.Value <0 SedanCell.Offset (0, 1) .Value = "Negativt"AnnanCell.Offset (0, 1) .Value = "Noll"Avsluta omNästa cellAvsluta Sub |
För nästa slingor
En annan typ av "For" Loop är For Next Loop. Med For Next Loop kan du gå igenom heltal.
Denna kod går igenom heltal 1 till 10 och visar var och en med en meddelanderuta:
| 123456 | Sub ForLoop ()Dim i As IntegerFör i = 1 till 10MsgBox iNästa iAvsluta Sub |
Gör medan slingor
Do While Loops loopas medan ett villkor är uppfyllt. Denna kod kommer också att gå igenom heltal 1 till 10 och visa var och en med en meddelanderuta.
| 12345678 | Sub DoWhileLoop ()Dim n Som heltaln = 1Gör medan n <11MsgBox nn = n + 1SlingaAvsluta Sub |
Gör tills slingor
Omvänt, gör tills slingor slingas tills ett villkor är uppfyllt. Denna kod gör samma sak som de två föregående exemplen.
| 12345678 | Sub DoUntilLoop ()Dim n Som heltaln = 1Gör tills n> = 10MsgBox nn = n + 1SlingaAvsluta Sub |
Vi kommer att diskutera detta nedan, men du måste vara extremt försiktig när du skapar Do While eller Do Through loopar så att du inte skapar en oändlig loop.
VBA Loop Builder
Detta är en skärmdump av "Loop Builder" från vårt Premium VBA-tillägg: AutoMacro. Med Loop Builder kan du snabbt och enkelt bygga slingor för att gå igenom olika objekt eller nummer. Du kan utföra åtgärder på varje objekt och/eller bara välja objekt som uppfyller vissa kriterier.
Tillägget innehåller också många andra kodbyggare, ett omfattande VBA-kodbibliotek och ett sortiment av kodningsverktyg. Det är ett måste för alla VBA -utvecklare.
Nu kommer vi att täcka de olika typerna av slingor på djupet.
VBA för nästa loop
För Loop Syntax
Med For Next Loop kan du upprepa ett kodblock ett visst antal gånger. Syntaxen är:
| 12345 | [Dimräknare som heltal]För räknare = Börja till slut [Stegvärde][Göra någonting]Nästa [räknare] |
Där objekten inom parentes är valfria.
- [Dim Counter as Long] - Deklarerar räknarvariabeln. Krävs om Option Explicit deklareras högst upp i din modul.
- Disken - En heltalsvariabel som används för att räkna
- Start - Startvärdet (Ex. 1)
- Slutet - Slutvärdet (Ex.10)
- [Stegvärde] - Gör att du kan räkna varje n heltal istället för varje 1 heltal. Du kan också gå bakåt med ett negativt värde (t.ex. steg -1)
- [Göra någonting] - Koden som kommer att upprepas
- Nästa [räknare] - Avslutande uttalande till For Next Loop. Du kan inkludera räknaren eller inte. Jag rekommenderar dock starkt att inkludera räknaren eftersom det gör din kod lättare att läsa.
Om det är förvirrande, oroa dig inte. Vi kommer att granska några exempel:
Räkna till 10
Denna kod kommer att räkna till 10 med en For-Next Loop:
| 12345678 | Sub ForEach_CountTo10 ()Dim n Som heltalFör n = 1 till 10MsgBox nNästa nAvsluta Sub |
För loopsteg
Räkna till 10 - endast jämna tal
Den här koden räknas till 10 och räknar bara jämna nummer:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n Som heltalFör n = 2 till 10 Steg 2MsgBox nNästa nAvsluta Sub |
Lägg märke till att vi har lagt till “Steg 2”. Detta säger att For Loop ska “kliva” genom räknaren med 2. Vi kan också använda ett negativt stegvärde för att gå i omvänd riktning:
För Loop Step - Invers
Nedräkning från 10
Denna kod kommer att räkna ned från 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n Som heltalFör n = 10 Till 1 Steg -1MsgBox nNästa nMsgBox "Lyft av"Avsluta Sub |
Ta bort rader om cellen är tom
Jag har oftast använt ett negativt steg For-Loop för att gå igenom cellområden och radera rader som uppfyller vissa kriterier. Om du slingrar från de översta raderna till de nedre raderna, när du raderar raderna kommer du att förstöra din räknare.
Detta exempel tar bort rader med tomma celler (från den nedre raden):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n Som heltalFör n = 10 Till 1 Steg -1If Range ("a" & n) .Value = "" DåOmråde ("a" och n) .EntireRow.DeleteAvsluta omNästa nAvsluta Sub |
Kapslad för slinga
Du kan “häcka” en For Loop inuti en annan för Loop. Vi kommer att använda Nested For Loops för att skapa en multiplikationstabell:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable ()Dim rad Som heltal, kol Som heltalFör rad = 1 till 9För kol = 1 till 9Celler (rad + 1, kol + 1). Värde = rad * kolNästa kolNästa radAvsluta Sub |
Avsluta för
Exit For -satsen låter dig omedelbart avsluta en For Next -slinga.
Du brukar använda Exit For tillsammans med ett If -uttalande, avsluta For Next Loop om ett visst villkor är uppfyllt.
Du kan till exempel använda en For Loop för att hitta en cell. När den cellen hittats kan du lämna slingan för att påskynda din kod.
Den här koden går igenom raderna 1 till 1000 och letar efter "fel" i kolumn A. Om den hittas väljer koden cellen, varnar dig för det hittade felet och lämnar slingan:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i As IntegerFör i = 1 till 1000If Range ("A" & i) .Value = "error" DåOmråde ("A" och i). VäljMsgBox "Fel hittades"Avsluta förAvsluta omNästa iAvsluta Sub |
Viktigt: När det gäller Nested For Loops lämnar Exit For endast den aktuella For Loop, inte alla aktiva loopar.
Fortsätt för
VBA har inte kommandot "Fortsätt" som finns i Visual Basic. Istället måste du använda "Exit".
VBA för varje loop
VBA för varje loop kommer att gå igenom alla objekt i en samling:
- Alla celler i ett område
- Alla kalkylblad i en arbetsbok
- Alla former i ett kalkylblad
- Alla öppna arbetsböcker
Du kan också använda Nested For each Loops för att:
- Alla celler i ett intervall på alla kalkylblad
- Alla former på alla kalkylblad
- Alla blad i alla öppna arbetsböcker
- och så vidare…
Syntaxen är:
| 123 | För varje objekt i samlingen[Göra någonting]Nästa [Objekt] |
Var:
- Objekt - Variabel som representerar ett intervall, kalkylblad, arbetsbok, form, etc. (ex. Rng)
- Samling - Samling av föremål (t.ex. intervall ("a1: a10")
- [Göra någonting] - Kodblock för att köra på varje objekt
- Nästa [Objekt] - Avslutande uttalande. [Objekt] är valfritt, men rekommenderas starkt.
För varje cell i intervallet
Denna kod kommer att gå igenom varje cell i ett intervall:
| 123456789 | Sub ForEachCell_inRange ()Dim cell As RangeFör varje cell i intervallet ("a1: a10")cell.Värde = cell.Offset (0,1) .VärdeNästa cellAvsluta Sub |
För varje kalkylblad i arbetsboken
Den här koden går igenom alla kalkylblad i en arbetsbok och avskyddar varje ark:
| 123456789 | Sub ForEachSheet_inWorkbook ()Dim ws Som arbetsbladFör varje ws i kalkylbladws.Unprotect "lösenord"Nästa wsAvsluta Sub |
För varje öppen arbetsbok
Denna kod sparar och stänger alla öppna arbetsböcker:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Som arbetsbokFör varje wb i arbetsböckerwb.Close SaveChanges: = TrueNästa wbAvsluta Sub |
För varje form i kalkylbladet
Denna kod raderar alla former i det aktiva arket.
| 123456789 | Sub ForEachShape ()Dim shp som formFör varje shp i ActiveSheet.Shapesshp.DeleteNästa shpAvsluta Sub |
För varje form i varje kalkylblad i arbetsboken
Du kan också bo för varje slinga. Här går vi igenom alla former i alla kalkylblad i den aktiva arbetsboken:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetFör varje ws i kalkylbladFör varje shp In ws.Shapesshp.DeleteNästa shpNästa wsAvsluta Sub |
För varje - IF Loop
Som vi har nämnt tidigare kan du använda en If -sats i en loop och utföra åtgärder endast om vissa kriterier är uppfyllda.
Denna kod döljer alla tomma rader i ett intervall:
| 12345678910 | Sub ForEachCell_inRange ()Dim cell As RangeFör varje cell i intervallet ("a1: a10")Om cell.Value = "" Då _cell.EntireRow.Hidden = SantNästa cellAvsluta Sub |
VBA Do While Loop
VBA gör medan och gör tills (se nästa avsnitt) är väldigt lika. De kommer att upprepa en loop medan (eller tills) ett villkor är uppfyllt.
Do While Loop kommer att upprepa en loop medan ett villkor är uppfyllt.
Här är Do While Syntax:
| 123 | Gör medan skick[Göra någonting]Slinga |
Var:
- Skick - Villkoret att testa
- [Göra någonting] - Kodblocket som ska upprepas
Du kan också konfigurera en Do While -slinga med villkoret i slutet av slingan:
| 123 | Do[Göra någonting]Loop While Condition |
Vi kommer att demonstrera var och en och visa hur de skiljer sig åt:
Göra medan
Här är Do While -loop -exemplet som vi visade tidigare:
| 12345678 | Sub DoWhileLoop ()Dim n Som heltaln = 1Gör medan n <11MsgBox nn = n + 1SlingaAvsluta Sub |
Loop While
Låt oss nu köra samma procedur, förutom att vi flyttar villkoret till slutet av slingan:
| 12345678 | Sub DoLoopWhile ()Dim n Som heltaln = 1DoMsgBox nn = n + 1Slinga medan n <11Avsluta Sub |
VBA Gör Till Loop
Gör tills slingor kommer att upprepa en slinga tills ett visst villkor är uppfyllt. Syntaxen är i huvudsak densamma som Do While -slingorna:
| 123 | Gör tills skick[Göra någonting]Slinga |
och på samma sätt kan tillståndet gå i början eller slutet av slingan:
| 123 | Do[Göra någonting]Loop till skick |
Gör tills
Detta gör Till -loop kommer att räkna till 10, som våra tidigare exempel
| 12345678 | Sub DoUntilLoop ()Dim n Som heltaln = 1Gör tills n> 10MsgBox nn = n + 1SlingaAvsluta Sub |
Loop till
Denna loop till -loop kommer att räknas till 10:
| 12345678 | Sub DoLoopUntil ()Dim n Som heltaln = 1DoMsgBox nn = n + 1Loop Till n> 10Avsluta Sub |
Avsluta Do Loop
På samma sätt som att använda Exit For för att avsluta en For Loop använder du kommandot Exit Do för att avsluta en Do Loop omedelbart
| 1 | Avsluta Do |
Här är ett exempel på Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i As Integerjag = 1Gör tills jag> 1000If Range ("A" & i) .Value = "error" DåOmråde ("A" och i). VäljMsgBox "Fel hittades"Avsluta DoAvsluta omi = i + 1SlingaAvsluta Sub |
Avsluta eller bryt slingan
Som vi nämnde ovan kan du använda Exit For eller Exit Do för att avsluta loopar:
| 1 | Avsluta för |
| 1 | Avsluta Do |
Dessa kommandon måste dock läggas till i din kod innan du kör din loop.
Om du försöker "bryta" en loop som för närvarande körs kan du försöka trycka på ESC eller CTRL + Paus paus på tangentbordet. Detta kanske dock inte fungerar. Om det inte fungerar måste du vänta på att din slinga ska sluta eller, i händelse av en ändlös slinga, använda CTRL + ALT + Radera för att tvinga stänga Excel.
Det är därför jag försöker undvika Gör slingor, det är lättare att av misstag skapa en oändlig slinga som tvingar dig att starta om Excel och kan förlora ditt arbete.
Fler loop -exempel
Loop Through Rows
Detta går igenom alla rader i en kolumn:
| 123456789 | Public Sub LoopThroughRows ()Dim cell As RangeFör varje cell i intervallet ("A: A")Ff cell.value "" sedan MsgBox cell.address & ":" & cell.valueNästa cellAvsluta Sub |
Loop Through Columns
Detta går igenom alla kolumner i rad:
| 123456789 | Public Sub LoopThroughColumns ()Dim cell As RangeFör varje cell i intervallet ("1: 1")If cell.Value "" Då MsgBox cell.Address & ":" & cell.ValueNästa cellAvsluta Sub |
Gå igenom filer i en mapp
Denna kod går igenom alla filer i en mapp och skapar en lista:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO som objektDim oFolder som objektDim oFile Som objektDim i As IntegerAnge oFSO = CreateObject ("Scripting.FileSystemObject")Ange oFolder = oFSO.GetFolder ("C: \ Demo)jag = 2För varje oFIL I oFolder.FilesOmråde ("A" och i) .value = oFile.Namei = i + 1Nästa filAvsluta Sub |
Loop Through Array
Denna kod går igenom matrisen 'arrList':
| 123 | För i = LBound (arrList) Till UBound (arrList)MsgBox arrList (i)Nästa i |
LBound -funktionen får matrisens "nedre gräns" och UBound får den "övre gränsen".
Slingor i Access VBA
De flesta exemplen ovan fungerar också i Access VBA. Men i Access går vi igenom Recordset Object snarare än Range Object.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Vid fel Återuppta nästaDim dbs som databasDim först som rekorduppsättningStäll in dbs = CurrentDbAnge rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Med första.MoveLast.MoveFirstGör tills .EOF = SantMsgBox (rst.Fields ("ClientName")).MoveNextSlingaSluta medförsta stängaAnge först = ingentingStäll in dbs = ingentingAvsluta Sub |
