Excel VBA-loops - voor elk, voor volgende, doen terwijl, genest en meer

Om effectief in VBA te werken, moet u Loops begrijpen.

Met lussen kunt u een codeblok een bepaald aantal keren herhalen of een codeblok herhalen op elk object in een set objecten.

Eerst laten we je een paar voorbeelden zien om je te laten zien waartoe loops in staat zijn. Dan leren we je alles over loops.

Snelle voorbeelden van VBA-lus

Voor elke lus

Voor elke lus loopt u door elk object in een verzameling, zoals elk werkblad in de werkmap of elke cel in een bereik.

Doorloop alle werkbladen in de werkmap

Deze code loopt door alle werkbladen in de werkmap en maakt elk blad zichtbaar:

12345678

Sub LoopThroughSheets()Dim ws als werkbladVoor elke ws In werkbladenws.Zichtbaar = TrueVolgendeEinde sub

Loop door alle cellen in bereik

Deze code doorloopt een reeks cellen en test of de celwaarde negatief, positief of nul is:

1234567891011121314

Sub If_Loop()Dim Cell als bereikVoor elke cel binnen bereik ("A2:A6")Als Cell.Value > 0 DanCell.Offset(0, 1).Waarde = "Positief"ElseIf Cell.Waarde < 0 DanCell.Offset(0, 1).Waarde = "Negatief"AndersCell.Offset(0, 1).Waarde = "Nul"Stop alsVolgende celEinde sub

Voor volgende lussen

Een ander type "For" Loop is de For Next Loop. Met de For Next Loop kunt u gehele getallen doorlopen.

Deze code loopt door de gehele getallen 1 tot en met 10 en geeft elk een berichtvenster weer:

123456

Sub ForLoop()Dim ik als geheel getalVoor i = 1 tot 10MsgBox ivolgende iEinde sub

Do While Loops

Do While Loops worden herhaald als aan een voorwaarde wordt voldaan. Deze code loopt ook door de gehele getallen 1 tot en met 10 en geeft elk een berichtvenster weer.

12345678

Sub DoWhileLoop()Dim n als geheel getaln = 1Doen terwijl n < 11MsgBox nn = n + 1LusEinde sub

Doen tot lussen

Omgekeerd zal Do Until Loops een lus vormen totdat aan een voorwaarde is voldaan. Deze code doet hetzelfde als de vorige twee voorbeelden.

12345678

Sub DoUntilLoop()Dim n als geheel getaln = 1Doen tot n >= 10MsgBox nn = n + 1LusEinde sub

We zullen dit hieronder bespreken, maar je moet uiterst voorzichtig zijn bij het maken van Do While- of Do Until-loops, zodat je geen oneindige loop maakt.

VBA Loop Builder

Dit is een screenshot van de "Loop Builder" van onze Premium VBA-invoegtoepassing: AutoMacro. Met de Loop Builder kunt u snel en eenvoudig lussen bouwen om door verschillende objecten of nummers te lopen. U kunt op elk object acties uitvoeren en/of alleen objecten selecteren die aan bepaalde criteria voldoen.

De invoegtoepassing bevat ook veel andere codebuilders, een uitgebreide VBA-codebibliotheek en een assortiment codeertools. Het is een must voor elke VBA-ontwikkelaar.

Nu gaan we dieper in op de verschillende soorten lussen.

VBA voor volgende lus

For Loop-syntaxis

Met de For Next Loop kun je een codeblok een bepaald aantal keren herhalen. De syntaxis is:

12345

[Dimteller als geheel getal]Voor teller = begin tot eind [stapwaarde][Doe iets]Volgende [Teller]

Waar de items tussen haakjes optioneel zijn.

• [Dimteller zo lang] - Declareert de tellervariabele. Vereist als Option Explicit bovenaan uw module is gedeclareerd.
• Balie - Een integer-variabele die wordt gebruikt om te tellen
• Begin - De startwaarde (vb. 1)
• Einde - De eindwaarde (vb. 10)
• [Stapwaarde] - Hiermee kunt u elke n gehele getallen tellen in plaats van elk 1 geheel getal. U kunt ook in omgekeerde richting gaan met een negatieve waarde (bijv. Stap -1)
• [Doe iets] - De code die wordt herhaald
• Volgende [Teller] - Slotverklaring voor de For Next Loop. U kunt de teller opnemen of niet. Ik raad echter ten zeerste aan om de teller op te nemen, omdat dit uw code gemakkelijker leesbaar maakt.

Maak je geen zorgen als dat verwarrend is. We zullen enkele voorbeelden bekijken:

Tel tot 10

Deze code telt tot 10 met behulp van een For-Next Loop:

12345678

Sub ForEach_CountTo10()Dim n als geheel getalVoor n = 1 tot 10MsgBox nVolgende nrEinde sub

Voor lusstap

Tel tot 10 - Alleen even getallen

Deze code telt tot 10 en telt alleen even getallen:

12345678

Sub ForEach_CountTo10_Even()Dim n als geheel getalVoor n = 2 tot 10 Stap 2MsgBox nVolgende nrEinde sub

Merk op dat we "Stap 2" hebben toegevoegd. Dit vertelt de For Loop om door de teller te "stappen" met 2. We kunnen ook een negatieve stapwaarde gebruiken om achteruit te stappen:

Voor lusstap - omgekeerd

Aftellen vanaf 10

Deze code telt af vanaf 10:

123456789

Sub ForEach_Countdown_Inverse()Dim n als geheel getalVoor n = 10 Naar 1 Stap -1MsgBox nVolgende nrMsgBox "Lift uit"Einde sub

Rijen verwijderen als cel leeg is

Ik heb het vaakst een negatieve stap For-Loop gebruikt om door celbereiken te lopen en rijen te verwijderen die aan bepaalde criteria voldoen. Als u van de bovenste rijen naar de onderste rijen doorloopt, verprutst u uw teller terwijl u rijen verwijdert.

In dit voorbeeld worden rijen met lege cellen verwijderd (vanaf de onderste rij):

12345678910

Sub ForEach_DeleteRows_BlankCells()Dim n als geheel getalVoor n = 10 Naar 1 Stap -1If Range ("a" & n). Waarde = "" DanBereik ("a" & n). Gehele rij.VerwijderenStop alsVolgende nrEinde sub

Genest voor lus

U kunt een For Loop in een andere For Loop "nesten". We gebruiken Nested For Loops om een ​​tafel van vermenigvuldiging te maken:

1234567891011

Sub Nested_ForEach_MultiplicationTable()Dim rij als geheel getal, col als geheel getalVoor rij = 1 tot 9Voor col = 1 tot 9Cellen (rij + 1, col + 1). Waarde = rij * colvolgende kleurVolgende rijEinde sub

Afsluiten voor

Met het Exit For-statement kunt u een For Next-lus onmiddellijk verlaten.

Normaal gesproken gebruikt u Exit For samen met een If-statement, waarmee u de For Next Loop verlaat als aan een bepaalde voorwaarde is voldaan.

U kunt bijvoorbeeld een For-lus gebruiken om een ​​cel te zoeken. Zodra die cel is gevonden, kunt u de lus verlaten om uw code te versnellen.

Deze code doorloopt de rijen 1 tot 1000, op zoek naar "fout" in kolom A. Als deze wordt gevonden, selecteert de code de cel, waarschuwt u voor de gevonden fout en verlaat de lus:

12345678910111213

Sub ExitFor_Loop()Dim ik als geheel getalVoor i = 1 tot 1000If Range ("A" & i). Waarde = "fout" DanBereik ("A" & i).SelecteerMsgBox "Fout gevonden"Afsluiten voorStop alsvolgende iEinde sub

Belangrijk: In het geval van Nested For Loops, verlaat Exit For alleen de huidige For Loop, niet alle actieve Loops.

Doorgaan voor

VBA heeft niet de opdracht "Doorgaan" die wordt gevonden in Visual Basic. In plaats daarvan moet u "Afsluiten" gebruiken.

VBA voor elke lus

De VBA For Each Loop doorloopt alle objecten in een verzameling:

• Alle cellen in een bereik
• Alle werkbladen in een werkmap
• Alle vormen in een werkblad
• Alle geopende werkmappen

Je kunt Nested For Each Loops ook gebruiken om:

• Alle cellen in een bereik op alle werkbladen
• Alle vormen op alle werkbladen
• Alle werkbladen in alle geopende werkmappen
• enzovoort…

De syntaxis is:

123	Voor elk object in verzameling[Doe iets]Volgende [Object]

Waar:

• Object - Variabele die een bereik, werkblad, werkmap, vorm enz. vertegenwoordigt (bijv. rng)
• Verzameling - Verzameling van objecten (bijv. Bereik ("a1:a10")
• [Doe iets] - Codeblok om op elk object te draaien
• Volgende [Object] - Conclusie. [Object] is optioneel, maar wordt sterk aanbevolen.

Voor elke cel binnen bereik

Deze code loopt door elke cel in een bereik:

123456789

Sub ForEachCell_inRange()Dim cel als bereikVoor elke cel binnen bereik ("a1:a10")cel.Waarde = cel.Offset(0,1).WaardeVolgende celEinde sub

Voor elk werkblad in de werkmap

Deze code doorloopt alle werkbladen in een werkmap, waarbij de beveiliging van elk blad wordt opgeheven:

123456789

Sub ForEachSheet_inWorkbook()Dim ws als werkbladVoor elke ws In werkbladenws.Unprotect "wachtwoord"Volgende wsEinde sub

Voor elke open werkmap

Met deze code worden alle geopende werkmappen opgeslagen en gesloten:

123456789

Sub ForEachWB_inWorkbooks()Dim wb als werkboekVoor elke wb In Werkmappenwb.Close SaveChanges:=TrueVolgende wbEinde sub

Voor elke vorm in werkblad

Met deze code worden alle vormen in het actieve blad verwijderd.

123456789

Sub voor elke vorm()Dim shp als vormVoor elke shp in ActiveSheet.Shapesshp.Verwijderenvolgende shpEinde sub

Voor elke vorm in elk werkblad in de werkmap

Je kunt ook For Each Loops nesten. Hier lopen we door alle vormen in alle werkbladen in de actieve werkmap:

1234567891011

Sub ForEachShape_inAllWorksheets()Dim shp als vorm, ws als werkbladVoor elke ws In werkbladenVoor elke shp In ws.Shapesshp.Verwijderenvolgende shpVolgende wsEinde sub

Voor elke - IF-lus

Zoals we eerder hebben vermeld, kunt u een If-instructie binnen een lus gebruiken en alleen acties uitvoeren als aan bepaalde criteria wordt voldaan.

Deze code verbergt alle lege rijen in een bereik:

12345678910

Sub ForEachCell_inRange()Dim cel als bereikVoor elke cel binnen bereik ("a1:a10")If cell.Value = "" Dan _cell.EntireRow.Hidden = TrueVolgende celEinde sub

VBA Do While Loop

De VBA Do While en Do Until (zie volgende sectie) lijken erg op elkaar. Ze zullen een lus herhalen zolang (of totdat) aan een voorwaarde is voldaan.

De Do While Loop herhaalt een lus zolang aan een voorwaarde wordt voldaan.

Hier is de Do While-syntaxis:

123	Doen terwijl voorwaarde[Doe iets]Lus

Waar:

• Voorwaarde - De voorwaarde om te testen
• [Doe iets] - Het codeblok om te herhalen

Je kunt ook een Do While-lus instellen met de voorwaarde aan het einde van de lus:

123	Doen[Doe iets]Loop terwijl voorwaarde

We zullen ze allemaal demonstreren en laten zien hoe ze verschillen:

Doen terwijl

Hier is het voorbeeld van de Do While-lus die we eerder hebben gedemonstreerd:

12345678

Sub DoWhileLoop()Dim n als geheel getaln = 1Doen terwijl n < 11MsgBox nn = n + 1LusEinde sub

Loop While

Laten we nu dezelfde procedure uitvoeren, behalve dat we de voorwaarde naar het einde van de lus verplaatsen:

12345678

Sub DoLoopWhile()Dim n als geheel getaln = 1DoenMsgBox nn = n + 1Lus Terwijl n < 11Einde sub

VBA Doen tot lus

Do Until Loops herhaalt een lus totdat aan een bepaalde voorwaarde is voldaan. De syntaxis is in wezen hetzelfde als de Do While-lussen:

123	Doen tot voorwaarde[Doe iets]Lus

en op dezelfde manier kan de voorwaarde aan het begin of het einde van de lus staan:

123	Doen[Doe iets]Loop tot voorwaarde

Doen tot

Deze do Tot-lus telt tot 10, zoals onze vorige voorbeelden

12345678

Sub DoUntilLoop()Dim n als geheel getaln = 1Doen tot n > 10MsgBox nn = n + 1LusEinde sub

Herhaal tot

Deze Loop Tot-lus telt tot 10:

12345678

Sub DoLoopUntil()Dim n als geheel getaln = 1DoenMsgBox nn = n + 1Loop tot n > 10Einde sub

Afsluiten Do Loop

Vergelijkbaar met het gebruik van Exit For om een ​​For Loop te verlaten, gebruik je het Exit Do commando om een ​​Do Loop onmiddellijk te verlaten

1	Afsluiten Do

Hier is een voorbeeld van Exit Do:

123456789101112131415

Sub ExitDo_Loop()Dim ik als geheel getalik = 1Doen tot ik > 1000If Range ("A" & i). Waarde = "fout" DanBereik ("A" & i).SelecteerMsgBox "Fout gevonden"Afsluiten DoStop alsik = ik + 1LusEinde sub

Einde of verbreek lus

Zoals we hierboven vermeldden, kunt u de Exit For of Exit Do gebruiken om lussen te verlaten:

1	Afsluiten voor

1	Afsluiten Do

Deze opdrachten moeten echter aan uw code worden toegevoegd voordat u uw lus uitvoert.

Als je een lus probeert te "breken" die momenteel loopt, kun je proberen op te drukken ESC of CTRL + Pauze op het toetsenbord. Dit kan echter niet werken. Als het niet werkt, moet u wachten tot uw lus eindigt of, in het geval van een eindeloze lus, CTRL + ALT + Verwijderen om Excel te forceren.

Dit is de reden waarom ik Do-loops probeer te vermijden, het is gemakkelijker om per ongeluk een eindeloze lus te maken waardoor je Excel opnieuw moet opstarten, waardoor je mogelijk je werk kwijtraakt.

Meer lusvoorbeelden

Loop door rijen

Dit doorloopt alle rijen in een kolom:

123456789

Openbare Sub LoopThroughRows()Dim cel als bereikVoor elke cel binnen bereik ("A:A")Ff cell.value "" dan MsgBox cell.address &": " & cell.valueVolgende celEinde sub

Loop door kolommen

Dit doorloopt alle kolommen op een rij:

123456789

Openbare Sub LoopThroughColumns()Dim cel als bereikVoor elke cel binnen bereik ("1:1")Als cel. Waarde "" Dan MsgBox cel. Adres & ": " & cel. WaardeVolgende celEinde sub

Loop door bestanden in een map

Deze code doorloopt alle bestanden in een map en maakt een lijst:

12345678910111213141516171819

Sub LoopThroughFiles ()Dim oFSO als objectDim oFolder als objectDim ofFile As ObjectDim ik als geheel getalStel oFSO = CreateObject ("Scripting.FileSystemObject") inStel oFolder = oFSO.GetFolder("C:\Demo) inik = 2Voor elk oFile in oFolder.FilesBereik ("A" & i).waarde = oBestand.Naamik = ik + 1Volgende bestandEinde sub

Loop Through Array

Deze code loopt door de array 'arrList':

123	Voor i = LBound(arrList) Naar UBound(arrList)MsgBox arrList(i)volgende i

De LBound-functie krijgt de "ondergrens" van de array en UBound krijgt de "bovengrens".

Lussen in Access VBA

De meeste van de bovenstaande voorbeelden werken ook in Access VBA. In Access doorlopen we echter het Recordset-object in plaats van het Range-object.

123456789101112131415161718

Sub LoopThroughRecords()Bij fout Hervatten volgendeDim dbs als databaseDim eerst als recordsetStel dbs = CurrentDb inStel rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)Met eerst.MoveLast.Beweeg eerstDoen tot .EOF = TrueMsgBox (rst.Fields ("ClientName")).VerplaatsVolgendeLusEindigt meteerst.SluitenStel eerst = Niets inStel dbs = Niets inEinde sub