Excel VBA Loops – For Each, For Next, Do While, Nested & More
Um in VBA effektiv arbeiten zu können, müssen Sie Schleifen verstehen.
Mit Schleifen können Sie einen Codeblock eine festgelegte Anzahl von Malen wiederholen oder einen Codeblock für jedes Objekt in einer Reihe von Objekten wiederholen.
Zuerst zeigen wir Ihnen einige Beispiele, um Ihnen zu zeigen, wozu Schleifen in der Lage sind. Dann werden wir Ihnen alles über Schleifen beibringen.
VBA Loop Quick Examples
For Each Loops
For Each Loops Durchlaufen Sie jedes Objekt in einer Sammlung, z. B. jedes Arbeitsblatt in einer Arbeitsmappe oder jede Zelle in einem Bereich.
Durchlaufen aller Arbeitsblätter in der Arbeitsmappe
Dieser Code durchläuft alle Arbeitsblätter in der Arbeitsmappe und blendet jedes Blatt aus:
|
1
2
3
4
5
6
7
8
|
div>
Dim ws als Arbeitsblatt
Für jedes ws in Arbeitsblättern
ws.Visible = True
Next
End Sub
|
Durchlaufen aller Zellen im Bereich
Dieser Code durchläuft einen Bereich von Zellen und prüft, ob der Zellenwert negativ, positiv oder Null ist:
Für nächste Schleifen
Eine andere Art von „For“ -Schleife ist die For Next-Schleife. Mit der For Next-Schleife können Sie Ganzzahlen durchlaufen.
Dieser Code durchläuft die Ganzzahlen 1 bis 10 und zeigt jeweils ein Meldungsfeld an:
|
1
2
3
4
5
6
|
Sub ForLoop()
Dim i Als Integer
Für i = 1 Bis 10
MsgBox i
Weiter i
Ende Sub
|
Do While-Schleifen
Do While-Schleifen werden wiederholt, solange die Bedingung erfüllt ist. Dieser Code durchläuft auch die Ganzzahlen 1 bis 10 und zeigt jeweils ein Meldungsfeld an.
|
1
2
3
4
5
6
7
8
|
Sub DoWhileLoop()
Dim n Als Ganzzahl
n = 1
Do While n < 11
MsgBox n
n = n + 1
Schleife
Ende Sub
|
Mache bis Schleifen
Umgekehrt, Do Until Schleifen werden wiederholt, bis eine Bedingung erfüllt ist. Dieser Code macht dasselbe wie die beiden vorherigen Beispiele.
|
1
2
3
4
5
6
7
8
|
Sub DoUntilLoop()
Dimme n als Ganzzahl
n = 1
Zurück Bis n >= 10
MsgBox n
n = n + 1
Schleife
Ende Sub
|
Wir werden dies unten diskutieren, aber Sie sie müssen beim Erstellen von Do While- oder Do Until-Schleifen äußerst vorsichtig sein, damit Sie keine unendliche Schleife erstellen.
VBA Loop Builder
Dies ist ein Screenshot des „Loop Builder“ aus unserem Premium-VBA-Add-In: AutoMacro. Mit dem Loop Builder können Sie schnell und einfach Schleifen erstellen, um verschiedene Objekte oder Zahlen zu durchlaufen. Sie können Aktionen für jedes Objekt ausführen und/oder nur Objekte auswählen, die bestimmte Kriterien erfüllen.
Das Add-In enthält auch viele andere Code-Builder, eine umfangreiche VBA-Codebibliothek und eine Auswahl an Codierungstools. Es ist ein Muss für jeden VBA-Entwickler.
Jetzt werden wir die verschiedenen Arten von Schleifen ausführlich behandeln.
VBA für die nächste Schleife
Für Schleifensyntax
Mit der For Next-Schleife können Sie einen Codeblock eine bestimmte Anzahl von Malen wiederholen. Die Syntax ist:
|
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 – Eine ganzzahlige Variable, die zum Zählen verwendet wird
- Start – Der Startwert (Bsp. 1)
- End – Der Endwert (Bsp. 10)
- – Ermöglicht das Zählen aller n Ganzzahlen anstelle jeder 1 Ganzzahl. Sie können auch umgekehrt mit einem negativen Wert gehen (ex. Schritt -1)
- – Der Code, der die
- Next – Closing-Anweisung für die For Next-Schleife wiederholt. Sie können den Zähler einschließen oder nicht. Ich empfehle jedoch dringend, den Zähler einzuschließen, da dies das Lesen Ihres Codes erleichtert.
Wenn das verwirrend ist, keine Sorge. Wir werden einige Beispiele überprüfen:
Zähle bis 10
Dieser Code zählt mit einer For-Next-Schleife bis 10:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10()
Dim n Als Ganzzahl
Für n = 1 Bis 10
MsgBox n
Weiter n
Ende Sub
|
Für Schleifenschritt
Zähle bis 10 – Nur gerade Zahlen
Dieser Wert der Code zählt bis 10 und zählt nur gerade Zahlen:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10_Even()
Dim n Als Ganzzahl
Für n = 2 Bis 10 Schritt 2
MsgBox n
Next n
End Sub
|
Hinweis Wir haben den „Schritt 2“ hinzugefügt. Dies weist die For-Schleife an, den Zähler um 2 zu „durchlaufen“. Wir können auch einen negativen Schrittwert verwenden, um rückwärts zu gehen:
Für Loop Step – Inverse
Countdown von 10
Dieser Code zählt von 10:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEach_Countdown_Inverse()
im n Als Integer Für n = 10 bis 1 Schritt -1
MsgBox n
Weiter n
MsgBox „Abheben“
Sub beenden
|
Löschen Zeilen wenn Zelle leer ist
Ich habe am häufigsten einen negativen Schritt For-Loop verwendet, um Zellbereiche zu durchlaufen und Zeilen zu löschen, die bestimmte Kriterien erfüllen. Wenn Sie von den oberen Zeilen zu den unteren Zeilen schleifen, werden Sie beim Löschen von Zeilen Ihren Zähler durcheinander bringen.
In diesem Beispiel werden Zeilen mit leeren Zellen gelöscht (beginnend mit der unteren Zeile):
Nested For Loop
Sie können eine For-Schleife in einer anderen For-Schleife „verschachteln“. Wir werden verschachtelte For-Schleifen verwenden, um eine Multiplikationstabelle zu erstellen:
|
1
2
3
4
5
6
7
8
9
10
11
|
div> Zeile als Ganzzahl dimmen, Spalte als Ganzzahl
Für Zeile = 1 bis 9
Für Spalte = 1 bis 9
Zellen(Zeile + 1, Spalte + 1).Wert = Zeile * Spalte
Nächste Spalte
Nächste Zeile
Ende Sub
|
Exit For
Mit der Anweisung Exit For können Sie eine For Next-Schleife sofort beenden.
Normalerweise würden Sie Exit For zusammen mit einer If-Anweisung verwenden und die For Next-Schleife verlassen, wenn eine bestimmte Bedingung erfüllt ist.
Sie können beispielsweise eine For-Schleife verwenden, um eine Zelle zu finden. Sobald diese Zelle gefunden wurde, können Sie die Schleife verlassen, um Ihren Code zu beschleunigen.
Dieser Code durchläuft die Zeilen 1 bis 1000 und sucht in Spalte A nach „Fehler“. Wenn er gefunden wird, wählt der Code die Zelle aus, benachrichtigt Sie über den gefundenen Fehler und beendet die Schleife:
Wichtig: Bei verschachtelten For-Schleifen beendet Exit For nur die aktuelle For-Schleife, nicht alle aktiven Schleifen.
Weiter Für
VBA hat nicht den Befehl „Weiter“, der in Visual Basic gefunden wird. Stattdessen müssen Sie „Exit“ verwenden.
VBA für jede Schleife
Die VBA für jede Schleife durchläuft alle Objekte in einer Sammlung:
- Alle Zellen in einem Bereich
- Alle Arbeitsblätter in einer Arbeitsmappe
- Alle Formen in einem Arbeitsblatt
- Alle geöffneten Arbeitsmappen
Sie können auch Nested For Each verwenden, um:
- Alle Zellen in einem Bereich auf allen Arbeitsblättern
- Alle Formen auf allen Arbeitsblättern
- Alle Blätter in allen geöffneten Arbeitsmappen
- und so weiter…
Die Syntax ist:
|
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.Unprotect „password“
Next ws
End Sub
|
Für jede geöffnete Arbeitsmappe
Dieser Code speichert und schließt alle geöffneten Arbeitsmappen:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachWB_inWorkbooks()
im wb Als Arbeitsmappe wb In Arbeitsmappen
wb.Schließen SaveChanges:=True
Next wb
End Sub
|
Für jede Form im Arbeitsblatt
Dieser Code löscht alle Formen im aktiven Blatt.
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachShape()
im shp Als Form Aktivitätsblatt.Formen
shp.Delete
Next shp
End Sub
|
Für jede Form in jedem Arbeitsblatt in der Arbeitsmappe
kann auch für jede Schleife verschachtelt werden. Hier durchlaufen wir alle Formen in allen Arbeitsblättern in der aktiven Arbeitsmappe:
|
1
2
3
4
5
6
7
8
9
10
11
|
Unter ForEachShape_inAllWorksheets()
im shp als Form, ws als Arbeitsblatt Für jedes ws in Arbeitsblättern
Für jedes shp in ws.Formen
shp.Delete
Next shp
Next ws
End Sub
|
Für jede – IF-Schleife
Wie bereits erwähnt, können Sie eine If-Anweisung innerhalb einer Schleife verwenden und Aktionen nur ausführen, wenn bestimmte Kriterien erfüllt sind.
Dieser Code blendet alle leeren Zeilen in einem Bereich aus:
VBA Do While Schleife
Die VBA Do While und Do Until (siehe nächsten Abschnitt) sind sehr ähnlich. Sie wiederholen eine Schleife, solange (oder bis) eine Bedingung erfüllt ist.
Die Do While-Schleife wiederholt eine Schleife, solange eine Bedingung erfüllt ist.
Hier ist die Do While Syntax:
|
1
2
3
|
Do While Bedingung
Schleife
|
Wobei:
- Bedingung – Die Bedingung um
- – Den zu wiederholenden Codeblock zu testen
Sie können auch eine Do While-Schleife mit der Bedingung am Ende der Schleife einrichten:
|
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 Als Ganzzahl
n = 1
Do While n < 11
MsgBox n
n = n + 1
Schleife
Ende Sub
|
Loop While
Lassen Sie uns nun die gleiche Prozedur ausführen, außer dass wir die Bedingung an das Ende der Schleife verschieben:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopWhile()
Dim n Als Ganzzahl
n = 1
Zurück
= n + 1
Schleife Während n < 11
Ende Sub
|
VBA Do Until Schleife
Zurück Bis Schleifen wiederholen eine Schleife, bis eine bestimmte Bedingung erfüllt ist. Die Syntax ist im Wesentlichen die gleiche wie die Do While Schleifen:
|
1
2
3
|
Mache Bis Bedingung
Schleife
|
und ähnlich kann die Bedingung am Anfang oder am Ende der Schleife gehen:
|
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()
Dimme n als Ganzzahl
n = 1
Zurück Bis n > 10
MsgBox n
n = n + 1
Schleife
End Sub
|
Schleife bis
Diese Schleife bis Schleife zählt bis 10:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopUntil()
Dim n Als Ganzzahl
n = 1
Zurück
MsgBox n
n = n + 1
Schleife bis n > 10
Ende Sub
|
Do-Schleife beenden
Ähnlich wie beim Beenden einer For-Schleife mit Exit For verwenden Sie den Befehl Exit Do, um eine Do-Schleife sofort zu beenden
|
1
|
Beenden Sie
|
Hier ist ein Beispiel für Exit Do:
End or Break Loop
Wie oben erwähnt, können Sie den Exit For oder Exit Do verwenden, um Schleifen zu beenden:
|
1
|
Exit For
|
|
1
|
Exit Do
|
However, these commands must be added to your code before you run ihre Schleife.
Wenn Sie versuchen, eine gerade laufende Schleife zu „unterbrechen“, können Sie versuchen, ESC oder STRG + Pause auf der Tastatur zu drücken. Dies funktioniert jedoch möglicherweise nicht. Wenn dies nicht funktioniert, müssen Sie warten, bis Ihre Schleife beendet ist, oder im Falle einer Endlosschleife STRG + ALT + Entf verwenden, um das Schließen von Excel zu erzwingen.Es ist einfacher, versehentlich eine Endlosschleife zu erstellen, die Sie zum Neustart von Excel zwingt und möglicherweise Ihre Arbeit verliert.
Weitere Schleifenbeispiele
Schleife durch Zeilen
Dadurch werden alle Zeilen in einer Spalte durchlaufen:
Schleife durch Spalten
Dadurch werden alle Spalten in einer Reihe durchlaufen:
Schleife durch Dateien in einem Ordner
Dieser Code durchläuft alle Dateien in einem Ordner und erstellt eine Liste:
Schleife durch Array
Dieser Code durchläuft das Array ‚ arrList‘:
|
1
2
3
|
Für i = LBound(arrList) Zu UBound(arrList)
MsgBox arrList(i)
Nächstes i
|
Die Funktion LBound erhält die „untere bound“ des Arrays und UBound erhält die „obere Grenze“.
Schleifen in Access VBA
Die meisten der obigen Beispiele funktionieren auch in Access VBA. In Access durchlaufen wir jedoch das Recordset-Objekt und nicht das Range-Objekt.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
Sub LoopThroughRecords()
Bei Fehler Weiter fortsetzen
Dim dbs Als Datenbank
Dim rst 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
|