Excel vba was ist der unterschied zwischen value und cells

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

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 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

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 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

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:

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

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 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

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 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

Alle offenen Arbeitsmappen durchlaufen

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

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

Alle Formen in einem Arbeitsblatt durchlaufen

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

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

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 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-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 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

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:

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

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:

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

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 For_Schleife()
    Dim i As Integer
    For i = 1 To 10
        MsgBox i
    Next i
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:

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

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

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

Do Until

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

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

Loop Until

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

Sub For_Schleife()
    Dim i As Integer
    For i = 1 To 10
        MsgBox i
    Next i
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

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

Hier ist ein Beispiel für Exit Do:

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

Schleife beenden oder abbrechen

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

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

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

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:

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

Spalten in einer Schleife durchlaufen

Damit werden alle Spalten in einer Zeile durchlaufen:

Sub Do_While_Schleife()
    Dim n As Integer
    n = 1
    Do While n < 11
        MsgBox n
        n = n + 1
    Loop
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 Do_While_Schleife()
    Dim n As Integer
    n = 1
    Do While n < 11
        MsgBox n
        n = n + 1
    Loop
End Sub

Array in einer Schleife durchlaufen

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

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

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.

Was bedeutet Value in VBA?

Was ist Cells in VBA?

Welche Variablen gibt es in VBA?