Schleifen in Excel VBA (For Each, For Next, Do While, verschachtelte Schleifen & mehr)

Um effektiv in VBA arbeiten zu können, müssen Sie Schleifen verstehen.

Mit Schleifen können Sie einen Codeblock eine bestimmte Anzahl von Malen wiederholen oder ihn für jedes Objekt in einer Gruppe von Objekten wiederholen.

Zunächst werden wir Ihnen anhand einiger Beispiele zeigen, wozu Schleifen in der Lage sind. Dann werden wir Ihnen alles über Schleifen beibringen.

Schleifen in VBA – Schnellbeispiele

For-Each-Schleifen

For-Each-Schleifen durchlaufen jedes Objekt in einer Sammlung, z. B. jedes Arbeitsblatt in einer Arbeitsmappe oder jede Zelle in einem Bereich.

Alle Arbeitsblätter in der Arbeitsmappe in einer Schleife durchlaufen

Dieser Code durchläuft alle Arbeitsblätter in der Arbeitsmappe in einer Schleife und blendet jedes Arbeitsblatt wieder ein:

Sub Arbeitsblaetter_Durchlaufen_Und_Einblenden()
Dim ws As Worksheet
 
    For Each ws In Worksheets
        ws.Visible = True
    Next
 
End Sub

Alle Zellen eines Bereichs in einer Schleife durchlaufen

Dieser Code durchläuft einen Zellenbereich in einer Schleife und prüft, ob der Zellenwert negativ, positiv oder Null ist:

Sub If_Schleife()
Dim Zelle As Range
 
  For Each Zelle In Range("A2:A6")
    If Zelle.Value > 0 Then
      Zelle.Offset(0, 1).Value = "Positiv"
    ElseIf Zelle.Value < 0 Then
      Zelle.Offset(0, 1).Value = "Negativ"
    Else
      Zelle.Offset(0, 1).Value = "Null"
     End If
  Next Zelle
 
End Sub

For-Next-Schleifen

Eine andere Art von For-Schleife ist die For-Next-Schleife. Mit der For-Next-Schleife können Sie ganze Zahlen durchlaufen.

Dieser Code durchläuft die Zahlen 1 bis 10 und zeigt jede in einer Meldungsbox an:

Sub For_Schleife()
    Dim i As Integer
    For i = 1 To 10
        MsgBox i
    Next i
End Sub

Do While-Schleifen

Do While-Schleifen wiederholen die Aktionen, solange eine Bedingung erfüllt ist. Dieser Code durchläuft auch die Zahlen 1 bis 10 in einer Schleife und zeigt jede in einer Meldungsbox an.

Sub Do_While_Schleife()
    Dim n As Integer
    n = 1
    Do While n < 11
        MsgBox n
        n = n + 1
    Loop
End Sub

Do Until-Schleifen

Umgekehrt werden Do-Until-Schleifen so lange wiederholt, bis eine Bedingung erfüllt ist. Dieser Code tut das Gleiche wie die beiden vorherigen Beispiele:

Sub Do_Until_Schleife()
    Dim n As Integer
    n = 1
    Do Until n >= 10
        MsgBox n
        n = n + 1
    Loop
End Sub

Wir werden dies weiter unten besprechen, aber Sie müssen beim Erstellen von Do-While- oder Do-Until-Schleifen äußerst vorsichtig sein, damit Sie keine Endlosschleife erzeugen.

VBA-Schleifen-Builder

Dies ist ein Screenshot des „Loop Builders“ 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 durchfü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-Code-Bibliothek und eine Reihe von Codierungswerkzeugen. Es ist ein Muss für jeden VBA-Entwickler.

Jetzt werden wir die verschiedenen Arten von Schleifen eingehend behandeln.

VBA – For-Next-Schleife

Syntax der For-Schleife

Mit der For Next-Schleife können Sie einen Codeblock eine bestimmte Anzahl von Malen wiederholen. Die Syntax lautet:

[Dim Zaehler as Integer]

For Zaehler = Start to Ende [Schritt]
    [Etwas tun]
Next [Zaehler]

Die Angaben in Klammern sind optional.

• [Dim Zaehler As Integer]: Deklariert die Zählervariable. Erforderlich, wenn Option Explicit am Anfang des Moduls deklariert ist.
• Zaehler: Eine Integer-Variable, die zum Zählen verwendet wird
• Start: Der Startwert (Bsp. 1)
• Ende: Der Endwert (Bsp. 10)
• [Schritt]: Ermöglicht es Ihnen, alle n Ganzzahlen zu zählen, anstatt alle 1 Ganzzahl. Sie können auch in umgekehrter Richtung mit einem negativen Wert arbeiten (z. B. Schritt -1)
• [Etwas tun]: Der Code, der wiederholt werden soll
• Next [Zaehler]: Abschlussanweisung für die For-Next-Schleife. Sie können den Zähler einschließen oder nicht. Ich empfehle jedoch dringend, den Zähler einzuschließen, da er Ihren Code leichter lesbar macht.

Wenn das verwirrend ist, machen Sie sich keine Sorgen. Wir werden einige Beispiele durchgehen:

Zählen bis 10

Dieser Code zählt mit einer For-Next-Schleife bis 10:

Sub For_Schleife_Bis_10_Zaehlen()

Dim n As Integer
For n = 1 To 10
    MsgBox n
Next n

End Sub

For-Schleifenschritt

Zählen bis 10 (nur gerade Zahlen)

Mit diesem Code wird bis 10 gezählt, wobei nur gerade Zahlen gezählt werden:

Sub For_Schleife_Bis_10_Zaehlen_Gerade()

Dim n As Integer
For n = 2 To 10 Step 2
    MsgBox n
Next n

End Sub

Beachten Sie, dass wir „Step 2“ hinzugefügt haben. Damit wird die For-Schleife angewiesen, den Zähler bei jeder Iteration um 2 zu erhöhen. Wir können auch einen negativen Schrittwert verwenden, um in umgekehrter Richtung zu gehen:

For-Schleifenschritt (umgekehrt)

Countdown von 10

Mit diesem Code wird von 10 heruntergezählt:

Sub For_Schleife_Countdown()

Dim n As Integer
For n = 10 To 1 Step -1
    MsgBox n
Next n
MsgBox "Abheben"

End Sub

Zeilen löschen, wenn eine bestimmte Zelle leer ist

Am häufigsten habe ich eine For-Schleife mit negativem Schritt verwendet, um in einer Schleife durch Zellenbereiche zu gehen und Zeilen zu löschen, die bestimmte Kriterien erfüllen. Wenn Sie in einer Schleife von den oberen zu den unteren Zeilen gehen und dabei Zeilen löschen, bringen Sie Ihren Zähler durcheinander.

In diesem Beispiel werden Zeilen mit leeren Zellen gelöscht (beginnend mit der untersten Zeile):

Sub For_Schleife_ZeilenLoeschen_LeereZellen()

Dim n As Integer
For n = 10 To 1 Step -1
    If Range("a" & n).Value = "" Then
        Range("a" & n).EntireRow.Delete
    End If
Next n

End Sub

Verschachtelte For-Schleife (Engl. Nested)

Sie können eine For-Schleife in einer anderen For-Schleife „verschachteln“. Wir werden verschachtelte For-Schleifen verwenden, um eine Multiplikationstabelle zu erstellen:

Sub Verschachtelte_For_Schleifen_Multiplikationstabelle()

Dim Zeile As Integer, Spalte As Integer

For Zeile = 1 To 9
    For Spalte = 1 To 9
        Cells(Zeile + 1, Spalte + 1).Value = Zeile * Spalte
    Next Spalte
Next Zeile

End Sub

Die Anweisung Exit For

Mit der Anweisung Exit For können Sie eine For-Next-Schleife sofort verlassen.

Normalerweise verwenden Sie Exit For zusammen mit einer If-Anweisung, um die For-Next-Schleife zu verlassen, wenn eine bestimmte Bedingung erfüllt ist.

Sie könnten zum Beispiel 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 geht in einer Schleife durch die Zeilen 1 bis 1000 und sucht nach dem Wort „Fehler“ in Spalte A. Wenn es gefunden wird, wählt der Code die Zelle aus, weist Sie auf den gefundenen Fehler hin und verlässt die Schleife:

Sub For_Schleife_Beenden()

Dim i As Integer
 
For i = 1 To 1000
    If Range("A" & i).Value = "Fehler" Then
        Range("A" & i).Select
        MsgBox "Fehler gefunden"
        Exit For
    End If
Next i

End Sub

Wichtig: Im Falle von verschachtelten For-Schleifen beendet Exit For nur die aktuelle For-Schleife, nicht alle aktiven Schleifen.

Die Anweisung Continue For

VBA verfügt nicht über den Befehl „Continue„, der in Visual Basic zu finden ist. Stattdessen müssen Sie „Exit“ verwenden.

Die VBA-Schleife For Each

Die VBA-Schleife For Each durchläuft alle Objekte einer Sammlung in einer Schleife:

• Alle Zellen in einem Bereich
• Alle Arbeitsblätter in einer Arbeitsmappe
• Alle Formen in einem Arbeitsblatt
• Alle geöffneten Arbeitsmappen

Sie können auch verschachtelte For-Each-Schleifen verwenden:

• Alle Zellen in einem Bereich auf allen Arbeitsblättern
• Alle Shapes auf allen Arbeitsblättern
• Alle Blätter in allen geöffneten Arbeitsmappen
• und so weiter…

Die Syntax lautet:

For Each Objekt in Sammlung
[Etwas tun]
Next [Objekt]

Wobei:

• Objekt: Variable, die einen Bereich, ein Arbeitsblatt, eine Arbeitsmappe, eine Form usw. darstellt. (z. B. rng)
• Sammlung: Sammlung von Objekten (z.B. Range(„a1:a10“))
• [Etwas tun]: Codeblock, der für jedes Objekt ausgeführt wird
• Next [Objekt]: Abschließende Anweisung. [Objekt] ist optional, wird aber dringend empfohlen.

Alle Zellen im Bereich durchlaufen (For Each Cell in Range)

Dieser Code durchläuft in einer Schleife jede Zelle in einem Bereich:

Sub Alle_Zellen_im_Bereich_Durchlaufen()

Dim Zelle As Range

For Each Zelle In Range("a1:a10")
    Zelle.Value = Zelle.Offset(0,1).Value
Next Zelle

End Sub

Alle Blätter in einer Arbeitsmappe durchlaufen (For Each Worksheet in Workbook)

Dieser Code durchläuft in einer Schleife alle Arbeitsblätter in einer Arbeitsmappe und hebt den Schutz für jedes Blatt auf:

Sub Alle_Blaetter_in_Arbeitsmappe_Durchlaufen()

Dim ws As Worksheet

For Each ws In Worksheets
    ws.Unprotect "Passwort"
Next ws

End Sub

Alle offenen Arbeitsmappen durchlaufen

Mit diesem Code werden alle geöffneten Arbeitsmappen gespeichert und geschlossen:

Sub Alle_Offenen_Arbeitsmappen_Durchlaufen()

Dim wb As Workbook

For Each wb In Workbooks
    wb.Close SaveChanges:=True
Next wb

End Sub

Alle Formen in einem Arbeitsblatt durchlaufen

Dieser Code löscht alle Formen auf dem aktuellen Arbeitsblatt.

Sub Alle_Formen_In_Arbeitsblatt_Loeschen()

Dim Form As Shape

For Each Form In ActiveSheet.Shapes
    Form.Delete
Next Form

End Sub

Alle Formen in jedem Arbeitsblatt in der Arbeitsmappe durchlaufen

Sie können For Each-Schleifen auch verschachteln. Hier werden alle Formen in allen Arbeitsblättern der aktiven Arbeitsmappe in einer Schleife durchlaufen:

Sub Alle_Formen_In_Allen_Arbeitsblaettern_Loeschen()

Dim Form As Shape, ws As Worksheet

For Each ws In Worksheets
    For Each Form In ws.Shapes
        Form.Delete
    Next Form
Next ws

End Sub

For-Each-If-Schleife

Wie bereits erwähnt, können Sie innerhalb einer Schleife eine If-Anweisung verwenden, die Aktionen nur dann ausführt, wenn bestimmte Kriterien erfüllt sind. Mit diesem Code werden alle leeren Zeilen in einem Bereich ausgeblendet:

Sub Alle_Zellen_Im_Bereich_Durchlaufen()

Dim Zelle As Range

For Each Zelle In Range("a1:a10")
    If Zelle.Value = "" Then _
       Zelle.EntireRow.Hidden = True
Next Zelle

End Sub

VBA – Do-While-Schleife

Die VBA-Schleifen Do While und Do Until (siehe nächsten Abschnitt) sind sehr ähnlich. Sie wiederholen eine Schleife, während (oder bis) eine Bedingung erfüllt ist.

Mit der Do-While-Schleife wird eine Schleife wiederholt , solange eine Bedingung noch erfüllt ist.

Hier ist die Do-While-Syntax:

Do While Bedingung
[Etwas tun]
Loop

Wo:

• Bedingung: Die zu testende Bedingung
• [Do Something]: Der zu wiederholende Code-Block

Sie können auch eine Do-While-Schleife einrichten, bei der die Bedingung am Ende der Schleife steht:

Do 
(Etwas tun)
Loop While Bedingung

Wir werden beide Schleifen demonstrieren und zeigen, wie sie sich unterscheiden:

Do While

Hier ist das Beispiel der Do-While-Schleife, das wir zuvor gezeigt haben:

Sub Do_While_Schleife()
    Dim n As Integer
    n = 1
    Do While n < 11
        MsgBox n
        n = n + 1
    Loop
End Sub

Loop While

Führen wir nun die gleiche Prozedur durch, nur dass wir die Bedingung an das Ende der Schleife verschieben:

Sub Do_Loop_While_Schleife()
    Dim n As Integer
    n = 1
    Do
        MsgBox n
        n = n + 1
    Loop While n < 11
End Sub

VBA – Do-Until-Schleife

Do-Until-Schleifen wiederholen eine Schleife, bis eine bestimmte Bedingung erfüllt ist. Die Syntax ist im Wesentlichen die gleiche wie bei Do-While-Schleifen:

Do Until Bedingung
[Etwas tun]
Loop

und ebenso kann die Bedingung am Anfang oder am Ende der Schleife stehen:

Do 
[Etwas tun]
Loop Until Bedingung

Do Until

Diese Do-Until-Schleife zählt bis 10, wie unsere vorherigen Beispiele

Sub Do_Until_Schleife()
    Dim n As Integer
    n = 1
    Do Until n > 10
        MsgBox n
        n = n + 1
    Loop
End Sub

Loop Until

Diese Loop-Until-Schleife zählt bis 10:

Sub Do_Loop_Until_Schleife()
    Dim n As Integer
    n = 1
    Do
        MsgBox n
        n = n + 1
    Loop Until n > 10
End Sub

Do-Schleife beenden

Ähnlich wie Exit For zum Verlassen einer For-Schleife verwenden Sie den Befehl Exit Do, um eine Do-Schleife sofort zu verlassen

Exit Do

Hier ist ein Beispiel für Exit Do:

Sub Do_Schleife_Beenden()

Dim i As Integer
i = 1 

Do Until i > 1000
    If Range("A" & i).Value = "Fehler" Then
        Range("A" & i).Select
        MsgBox "Fehler gefunden"
        Exit Do
    End If
    i = i + 1
Loop

End Sub

Schleife beenden oder abbrechen

Wie bereits erwähnt, können Sie Exit For oder Exit Do verwenden, um Schleifen zu beenden:

Exit For

Exit Do

Diese Befehle müssen jedoch in Ihren Code eingefügt werden, bevor Sie die Schleife ausführen.

Wenn Sie versuchen, eine 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, mit STRG + ALT + Entf Excel zwangsweise schließen.

Aus diesem Grund versuche ich, Do-Schleifen zu vermeiden. Es ist dabei einfacher, versehentlich eine Endlosschleife zu erzeugen, die Sie zwingt, Excel neu zu starten, wodurch Ihre Arbeit möglicherweise verloren geht.

Weitere Beispiele für Schleifen

Zeilen in einer Schleife durchlaufen

Diese Schleife geht durch alle Zeilen einer Spalte:

Public Sub Zeilen_Durchlaufen()
 
Dim Zelle As Range
 
For Each Zelle In Range("A:A")
    If Zelle.value <> "" then MsgBox Zelle.address &": " & Zelle.Value
Next Zelle
 
End Sub

Spalten in einer Schleife durchlaufen

Damit werden alle Spalten in einer Zeile durchlaufen:

Public Sub Spalten_Durchlaufen()

Dim Zelle As Range

For Each Zelle In Range("1:1")
    If Zelle.Value <> "" Then MsgBox Zelle.Address & ": " & Zelle.Value
Next Zelle

End Sub

Dateien in einem Ordner in einer Schleife durchlaufen

Dieser Code durchläuft in einer Schleife alle Dateien in einem Ordner und erstellt eine Liste:

Sub Dateien_Durchlaufen()

Dim oFSO As Object
Dim oOrdner As Object
Dim oDatei As Object
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oOrdner = oFSO.GetFolder("C:\Demo)

i = 2

For Each oDatei In oOrdner.Files
    Range("A" & i).Value = oDatei.Name
    i = i + 1
Next oDatei

End Sub

Array in einer Schleife durchlaufen

Dieser Code geht durch das Array ‚arrList‘ anhand einer Schleife:

For i = LBound(arrList) To UBound(arrList)
    MsgBox arrList(i)
Next i

Die Funktion LBound ermittelt die „untere Grenze“ des Arrays und UBound die „obere Grenze“.

Schleifen in Access-VBA

Die meisten der oben genannten Beispiele funktionieren auch in Access-VBA. In Access wird jedoch eine Schleife durch das Recordset-Objekt und nicht durch das Range-Objekt ausgeführt.

Sub Datensaetze_Durchlaufen()
   On Error Resume Next
   Dim dbs As Database
   Dim rst As Recordset
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblKunden", dbOpenDynaset)
   With rst
      .MoveLast
      .MoveFirst
      Do Until .EOF = True
         MsgBox (rst.Fields("Kundenname"))
        .MoveNext
     Loop
   End With
   rst.Close
   Set rst = Nothing
   Set dbs = Nothing
End Sub