VBA – For-Each-Beispiel (Schnellreferenz)

Dieses Tutorial zeigt Ihnen Beispiele für die Verwendung der For-Each-Schleife in VBA. Klicken Sie hier, um mehr über Schleifen im Allgemeinen zu erfahren.

For-Each-Schleife

Mit der For-Each-Schleife können Sie jedes Objekt in einer Sammlung durchlaufen:

• Alle Zellen in einem Bereich
• Alle Arbeitsblätter in einer Arbeitsmappe
• Alle geöffneten Arbeitsmappen
• Alle Formen in einem Arbeitsblatt
• Alle Elemente in einem Array
• und mehr!

For Each: Grundlegende Beispiele

In diesen Beispielen wird gezeigt, wie For-Each-Schleifen eingerichtet werden, um verschiedene Arten von Objekten zu durchlaufen.

Zellen durchlaufen

Diese Prozedur durchläuft jede Zelle im Bereich A1:A10 und belegt die Zelle rechts daneben mit Ihrem Wert.

Sub ForEach_Zelle()
    Dim Zelle As Range
    
    For Each Zelle In Sheets("Tabelle1").Range("A1:A10")
        Zelle.Offset(0, 1).Value = Zelle.Value
    Next Zelle
    
End Sub

Blätter durchlaufen

Diese Prozedur durchläuft jedes Blatt in einer Arbeitsmappe in einer Schleife und blendet es wieder ein.

Sub ForEach_Arbeitsblatt()
    Dim ws As Worksheet

    For Each ws In Sheets
        ws.Visible = True
    Next ws

End Sub

Arbeitsmappen durchlaufen

Diese Prozedur durchläuft alle Arbeitsmappen in einer Schleife und schließt jede einzelne.

Sub ForEach_Arbeitsmappe()
    Dim wb As Workbook
    
    For Each wb In Workbooks
        wb.Close
    Next wb
    
End Sub

Formen durchlaufen

Diese Prozedur durchläuft alle Formen in Tabelle1 und löscht jede einzelne.

Sub ForEach_Form()
    Dim frm As Shape
    
    For Each frm In Sheets("Tabelle1").Shapes
        frm.Delete
    Next frm
    
End Sub

Diagramme durchlaufen

Diese Prozedur durchläuft alle Diagramme in Tabelle1 in einer Schleife und löscht jedes einzelne.

Sub ForEach_Diagramm()
    Dim diag As ChartObject
    
    For Each diag In Sheets("Tabelle1").ChartObjects
        diag.Delete
    Next diag
    
End Sub

PivotTables durchlaufen

Diese Prozedur durchläuft alle PivotTables in Tabelle1 in einer Schleife und löscht jede einzelne.

Sub ForEach_PivotTabelle()
    Dim pvt As PivotTable
    
    For Each pvt In Sheets("Tabelle1").PivotTables
        pvt.ClearTable
    Next pvt
    
End Sub

Tabellen durchlaufen

Diese Prozedur durchläuft alle Tabellen in Tabelle1 in einer Schleife und löscht jede einzelne.

Sub ForEach_Tabelle()
    Dim tbl As ListObject
    
    For Each tbl In Sheets("Tabelle1").ListObjects
        tbl.Delete
    Next tbl
    
End Sub

Elemente in Array durchlaufen

Diese Prozedur durchläuft alle Elemente in einem Array und zeigt jeden Wert in einer Meldungsbox an.

Sub ForEach_ElementInArray()
    Dim arrWert As Variant
    Dim Element As Variant
    arrWert = Array("Punkt 1", "Punkt 2", "Punkt 3")
    
    For Each Element In arrWert
        MsgBox Element
    Next Element
    
End Sub

Zahlen durchlaufen

Diese Prozedur durchläuft alle Zahlen in einem Array in einer Schleife und zeigt jeden Wert in einer Meldungsbox an.

Sub ForEach_ZahlInZahlen()
    Dim arrZahl(1 To 3) As Integer
    Dim Zahl As Variant
    
    arrZahl(1) = 10
    arrZahl(2) = 20
    arrZahl(3) = 30
    
    For Each Zahl In arrZahl
        Msgbox Zahl
    Next Zahl
    
End Sub

For-Each-Schleifengenerator

Die Beispiele in diesem Artikel wurden mit dem Schleifengenerator in unserem VBA-Add-in AutoMacro erstellt.

Der Schleifengenerator macht es sehr einfach, Code zum Durchlaufen von Objekten zu erzeugen. AutoMacro enthält auch viele andere Codegeneratoren, eine umfangreiche Codebibliothek und leistungsstarke Programmierwerkzeuge.

For-Each-If

Sie können auch If-Anweisungen innerhalb von Schleifen verwenden, um zu prüfen, ob Objekte bestimmte Kriterien erfüllen und sicherzustellen, dass Aktionen nur an Objekten, die die Kriterien erfüllen, ausgeführt werden. Hier ist ein Beispiel für eine Schleife durch alle Zellen in einem Bereich:

For Each Zelle in Bereich – mit If-Anweisung

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 Each – Gängige Beispiele

Alle Arbeitsmappen schließen

Mit dieser Prozedur werden alle geöffneten Arbeitsmappen geschlossen und die Änderungen gespeichert.

Sub AlleArbeitsmappenSchliessen()
    
    Dim wb As Workbook
    
    For Each wb In Workbooks
        wb.Close SaveChanges:=True
    Next wb
    
End Sub

Alle Blätter ausblenden

Mit dieser Prozedur werden alle Arbeitsblätter ausgeblendet.

Sub AlleBlaetterAusblenden()
    Dim ws As Worksheet

    For Each ws In Sheets
        ws.Visible = xlSheetHidden
    Next ws
    
End Sub

Alle Blätter wieder einblenden

Diese Prozedur blendet alle Arbeitsblätter wieder ein.

Sub AlleBlaetterEinblenden()
    Dim ws As Worksheet

    For Each ws In Sheets
        ws.Visible = xlSheetVisible
    Next ws
    
End Sub

Alle Blätter schützen

Mit dieser Prozedur werden alle Arbeitsblätter geschützt.

Sub AlleBlaetterSchuetzen()
    Dim ws As Worksheet

    For Each ws In Sheets
        ws.Protect Password:="..."
    Next ws
    
End Sub

Schutz aller Blätter aufheben

Diese Prozedur hebt den Schutz für alle Arbeitsblätter auf.

Sub AlleBlaetterSchutzAufheben()
    Dim ws As Worksheet

    For Each ws In Sheets
        ws.Unprotect Password:="..."
    Next ws
    
End Sub

Alle Formen auf allen Arbeitsblättern löschen

Mit dieser Prozedur werden alle Formen in einer Arbeitsmappe gelöscht.

Sub AlleFormenInAllenArbeitsblaetternLoeschen()
    Dim Blatt As Worksheet
    Dim frm As Shape
    

    For Each Blatt In Sheets
        For Each frm In Blatt.Shapes
            frm.Delete
        Next frm
    Next Blatt
    
End Sub

Alle PivotTables aktualisieren

Diese Prozedur aktualisiert alle PivotTables auf einem Blatt.

Sub AllePivotTabellenAktualisieren()
    Dim pvt As PivotTable
    
    For Each pvt In Sheets("Tabelle1").PivotTables
        pvt.RefreshTable
    Next pvt
    
End Sub

For Each in Access-VBA verwenden

Die For Each-Schleife funktioniert in Access VBA auf die gleiche Weise wie in Excel VBA. Im folgenden Beispiel werden alle Tabellen in der aktuellen Datenbank gelöscht.

Sub AlleTabellenEntfernen()
  Dim tdf As TableDef
  Dim dbs As Database
  Set dbs = CurrentDb
  For Each tdf In dbs.TableDefs
      DoCmd.DeleteObject tdf.Name
  Loop
  Set dbs = Nothing
End Sub