Excel VBA Loops – voor elke, voor de volgende, doen terwijl, geneste & meer
om effectief te werken in VBA, moet u lussen begrijpen.met
kunt u een codeblok een aantal keren herhalen of een codeblok herhalen op elk object in een verzameling objecten.
eerst zullen we u een paar voorbeelden tonen om u te laten zien waartoe lussen in staat zijn. Dan leren we je alles over lussen.
VBA Loop snelle voorbeelden
voor elke lussen
voor elke lussen loop door elk object in een verzameling, zoals elk werkblad in een werkmap of elke cel in een bereik.
Loop Door alle Werkbladen in de Werkmap
Deze code wordt doorlopen van alle werkbladen in de werkmap, het zichtbaar maken van elk vel:
|
1
2
3
4
5
6
7
8
|
Sub LoopThroughSheets()
Dim ws Als Werkblad
Voor Elk ws In Werkbladen
ws.Visible = True
volgende
End Sub
|
Loop door alle cellen binnen bereik
deze code loopt door een celbereik, waarbij wordt getest of de celwaarde negatief, positief of nul is:
voor volgende lussen
een ander type “voor” – lus is de voor volgende lus. Met de For Next Loop kun je gehele getallen doorlopen.
deze code zal door gehele getallen 1 tot en met 10 lopen, elk met een berichtenvenster:
|
1
2
3
4
5
6
|
Sub ForLoop()
Dim i As Integer
For i = 1 To 10
MsgBox i
ik
End Sub
|
Do While-Lussen
Do While Lussen met een lus als aan de voorwaarde is voldaan. Deze code zal ook loop door gehele getallen 1 tot en met 10, elk weergeven met een berichtenvenster.
|
1
2
3
4
5
6
7
8
|
Sub DoWhileLoop()
Dim n As Integer
n = 1
Do While n < 11
MsgBox n
n = n + 1
Herhalen
End Sub
|
nog Tot Lussen
Omgekeerd, Doen totdat lussen zal lus totdat een voorwaarde is voldaan. Deze code doet hetzelfde als de vorige twee voorbeelden.
|
1
2
3
4
5
6
7
8
|
Sub DoUntilLoop()
Dim n As Integer
n = 1
Terug Tot n >= 10
MsgBox n
n = n + 1
Herhalen
End Sub
|
We bespreken dit hieronder, maar u moeten uiterst voorzichtig zijn bij het maken van Do While of do Until loops, zodat u geen nooit eindigende lus.
VBA Loop Builder
Dit is een screenshot van de “Loop Builder” uit onze Premium VBA Add-in: AutoMacro. Met de Loop Builder kunt u snel en eenvoudig lussen bouwen om door verschillende objecten of getallen te lussen. U kunt acties uitvoeren op elk object en / of alleen objecten selecteren die aan bepaalde criteria voldoen.
de invoegtoepassing bevat ook vele andere codebouwers, een uitgebreide VBA-codebibliotheek en een assortiment coderingshulpmiddelen. Het is een must voor elke VBA-Ontwikkelaar.
nu zullen we de verschillende soorten lussen in de diepte behandelen.
VBA voor volgende lus
voor lus syntaxis
met de voor volgende lus kunt u een blok code een bepaald aantal keren herhalen. De syntaxis is:
|
1
2
3
4
5
|
For Counter = Start to End
Next
|
Where the items in brackets are optional.
- – Declares the counter variable. Required if Option Explicit is declared at the top of your module.
- Counter-een integer variabele die wordt gebruikt om
- Start te tellen-de startwaarde (Ex. 1)
- End-de eindwaarde (Ex. 10)
- – Hiermee kunt u elke n gehele getallen tellen in plaats van elk 1 geheel getal. U kunt ook achteruit gaan met een negatieve waarde (bijv. Stap -1)
- – de code die
- next – Closing statement zal herhalen voor de volgende lus. U kunt de teller of niet. Echter, Ik beveel het opnemen van de teller als het maakt uw code gemakkelijker te lezen.
als dat verwarrend is, maak je geen zorgen. We zullen enkele voorbeelden bekijken:
tel tot 10
deze code telt tot 10 met behulp van een For-Next lus:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10()
Dim n As Integer
For n = 1 To 10
MsgBox n
n
End Sub
|
Voor Loop-Stap
tel tot 10 – Alleen even Nummers
Dit code telt tot 10 alleen tellen even nummers:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10_Even()
Dim n As Integer
Voor n = 2 Tot 10 Stap 2
MsgBox n
n
End Sub
|
Merk op dat we toegevoegd de “Stap 2″. Dit vertelt De For lus om” stap ” door de teller door 2. We kunnen ook een negatieve stapwaarde gebruiken om in omgekeerde stappen te stappen:
voor lus stap-Inverse
Countdown van 10
deze code zal aftellen van 10:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEach_Countdown_Inverse()
Dim n As Integer
Voor n = 10 To 1 Step -1
MsgBox n
n
MsgBox “Lift Off”
End Sub
|
Verwijderen Rijen als de cel leeg is
Ik heb het vaakst een negatieve stap voor-lus gebruikt om cellenbereiken te doorlopen, waardoor rijen worden verwijderd die aan bepaalde criteria voldoen. Als u lus van de bovenste rijen naar de onderste rijen, als u rijen te verwijderen zult u verknoeien uw teller.
dit voorbeeld zal rijen met lege cellen verwijderen (beginnend vanaf de onderste rij):
Genest voor lus
U kunt “nest” een voor lus binnen een andere voor lus. We zullen geneste voor lussen gebruiken om een vermenigvuldigingstabel te maken:
|
1
2
3
4
5
6
7
8
9
10
11
|
Sub Nested_ForEach_MultiplicationTable()
Dim rij Als geheel Getal, col Als geheel Getal
Voor rij = 1 Tot en met 9
Voor de col = 1 Tot en met 9
Cellen(rij + 1, col + 1).Value = rij * kol
volgende kol
volgende rij
einde Sub
|
exit voor
met het Exit For statement kunt u A voor de volgende lus onmiddellijk verlaten.
u gebruikt gewoonlijk Exit voor samen met een If Statement, waarbij u de For Next lus afsluit als aan een bepaalde voorwaarde is voldaan.
u kunt bijvoorbeeld een For-lus gebruiken om een cel te vinden. Zodra die cel is gevonden, kunt u de lus verlaten om uw code te versnellen.
deze code zal rij 1 tot 1000 doorlopen, op zoek naar” fout ” in kolom A. Als deze gevonden wordt, zal de code de cel selecteren, u waarschuwen voor de gevonden fout, en de lus verlaten:
belangrijk: in het geval van geneste voor lussen, Exit For alleen verlaat de huidige Voor lus, niet alle actieve lussen.
doorgaan voor
VBA heeft niet het” doorgaan ” commando dat gevonden wordt in Visual Basic. In plaats daarvan, je nodig hebt om te gebruiken “Exit”.
VBA voor elke lus
de VBA voor elke lus zal door alle objecten in een verzameling lopen:
- Alle cellen in een bereik
- Alle werkbladen in een werkmap
- Alle shapes in een werkblad
- Alle geopende werkmappen
U kunt ook gebruik maken van Geneste Voor Elke Lussen aan:
- Alle cellen in een bereik op alle werkbladen
- Alle shapes op alle werkbladen
- Alle bladen in alle geopende werkmappen
- enzovoort…
De syntaxis is:
|
1
2
3
|
For Each Object in Collection
Next
|
Where:
For Each Cell in Range
This code will loop through each cell in a range:
For Each Worksheet in Workbook
This code will loop through all worksheets in a workbook, unprotecting each sheet:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachSheet_inWorkbook()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Verwijder “password”
volgende ws
End Sub
|
voor elke Open werkmap
deze code zal alle open werkmappen opslaan en sluiten:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachWB_inWorkbooks()
Dim wb Als Werkmap
Voor Elke wb In Werkmappen
wb.SaveChanges Sluiten:= True
volgende wb
End Sub
|
voor elke vorm in het werkblad
deze code verwijdert alle vormen in het actieve werkblad.
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachShape()
Dim shp Als Vorm
Voor Elke shp In ActiveSheet.Vormen
shp.Delete
volgende shp
einde Sub
|
voor elke vorm in elk werkblad in werkmap
u kunt ook nestelen voor elke lussen. Hier zullen we alle vormen doorlopen in alle werkbladen in de actieve werkmap:
|
1
2
3
4
5
6
7
8
9
10
11
|
Sub ForEachShape_inAllWorksheets()
Dim shp Vorm, ws Als Werkblad
Voor Elk ws In Werkbladen
Voor Elke shp In ws.Vormen
shp.Delete
volgende shp
volgende ws
End Sub
|
voor elke – IF-lus
zoals we eerder hebben gezegd, kunt u een If-statement gebruiken binnen een lus, acties alleen uitvoeren als aan bepaalde criteria is voldaan.
deze code verbergt alle lege rijen in een bereik:
VBA Do While Loop
de VBA do While En Do totdat (zie volgende sectie) zeer vergelijkbaar zijn. Ze zullen een lus herhalen terwijl (of totdat) aan een voorwaarde is voldaan.
De do While lus zal een lus herhalen terwijl aan een voorwaarde wordt voldaan.
Hier is het te Doen, Terwijl de Syntaxis:
|
1
2
3
|
Do While Conditie
Herhalen
|
Waar:
- Voorwaarde – De conditie te testen
- – Het code-blok te herhalen
U kunt ook een Do While lus met de Toestand op het einde van de lus:
|
1
2
3
|
Do
Loop While Condition
|
We will demo each one and show how they differ:
Do While
Here is the Do While loop example we demonstrated previously:
|
1
2
3
4
5
6
7
8
|
Sub DoWhileLoop()
Dim n As Integer
n = 1
Do While n < 11
MsgBox n
n = n + 1
Herhalen
End Sub
|
Loop terwijl
laten we nu dezelfde procedure uitvoeren, behalve dat we de voorwaarde naar het einde van de loop verplaatsen:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopWhile()
Dim n As Integer
n = 1
Terug
MsgBox n
n = n + 1
Lus: While n < 11
End Sub
|
VBA Doen Totdat de Lus
Terug Tot Lussen zullen een lus herhalen totdat aan een bepaalde voorwaarde is voldaan. De syntaxis is in wezen hetzelfde als de Do While-lussen:
|
1
2
3
|
Doen Totdat Conditie
Herhalen
|
en ook de conditie kan gaan aan het begin of het einde van de lus:
|
1
2
3
|
Do
Loop Until Condition
|
Do Until
This do Until loop will count to 10, like our previous examples
|
1
2
3
4
5
6
7
8
|
Sub DoUntilLoop()
Dim n As Integer
n = 1
Terug Tot n > 10
MsgBox n
n = n + 1
Herhalen
End Sub
|
Herhalen Totdat
Deze Lus Totdat de lus zal tellen tot 10:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopUntil()
Dim n As Integer
n = 1
Terug
MsgBox n
n = n + 1
Loop Tot n > 10
End Sub
|
Exit Do-Lus
Vergelijkbaar met het gebruik van Afslag naar afslag van een For-Lus, u gebruik maken van de Exit Do opdracht tot afsluiten van een Lus onmiddellijk
|
1
|
Afsluiten
|
Hier is een voorbeeld van een Exit te Doen:
End of Breken Loop
Zoals we hierboven vermeld, kunt u gebruik maken van de Afslag of Afrit Hoeft te verlaten lussen:
|
1
|
Exit For
|
|
1
|
Exit Do
|
However, these commands must be added to your code before you run je lus.
als u probeert een lus die momenteel draait te “breken”, kunt u proberen op ESC of Ctrl + Pause Break op het toetsenbord te drukken. Dit kan echter niet werken. Als het niet werkt, moet u wachten tot uw lus is beëindigd of, in het geval van een eindeloze lus, gebruik Ctrl + ALT + Delete om Excel te sluiten.
daarom probeer ik Do loops te vermijden, het is makkelijker om per ongeluk een eindeloze lus te maken die je dwingt Excel opnieuw op te starten, waardoor je mogelijk je werk verliest.
meer Lusvoorbeelden
lus door rijen
Dit zal door alle rijen in een kolom lopen:
Loop door kolommen
Dit zal door alle kolommen in een Rij lopen:
Loop door bestanden in een map
deze code zal door alle bestanden in een map lopen en een lijst maken:
Loop door Array
deze code zal door de array lopen ‘arrlist”:
|
1
2
3
|
For i = LBound(arrList) To UBound(arrList)
MsgBox arrList(i)
ik
|
De functie LBound krijgt de “ondergrens” van de matrix en UBound krijgt de “bovengrens”.
Loops in Access VBA
De meeste voorbeelden hierboven zullen ook werken in Access VBA. In Access lus je echter door het object Recordset in plaats van het object Range.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
Sub LoopThroughRecords()
On Error Resume Next
Dim db As Database
Dim eerste Als Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblClients”, dbOpenDynaset)
With rst
.MoveLast
.MoveFirst
Do Until .EOF = True
MsgBox (rst.Fields(“ClientName”))
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
|