Um effektiv in VBA zu arbeiten, müssen Sie Loops verstehen.
Schleifen ermöglichen es Ihnen, einen Codeblock eine bestimmte Anzahl von Malen zu wiederholen oder einen Codeblock für jedes Objekt in einem Satz von Objekten zu wiederholen.
Zunächst zeigen wir Ihnen einige Beispiele, um Ihnen zu zeigen, wozu Loops fähig sind. Dann bringen wir dir alles über Loops bei.
Schnelle Beispiele für VBA-Schleife
Für jede Schleife
For Each Loops durchlaufen jedes Objekt in einer Sammlung, z. B. jedes Arbeitsblatt in einer Arbeitsmappe oder jede Zelle in einem Bereich.
Durchlaufen Sie alle Arbeitsblätter in der Arbeitsmappe
Dieser Code durchläuft alle Arbeitsblätter in der Arbeitsmappe und blendet jedes Blatt ein:
| 12345678 | Sub LoopThroughSheets()Dim ws als ArbeitsblattFür jedes ws in Arbeitsblätternws.Visible = TrueNächsteEnd Sub |
Durchlaufen aller Zellen im Bereich
Dieser Code durchläuft eine Reihe von Zellen und testet, ob der Zellenwert negativ, positiv oder null ist:
| 1234567891011121314 | Unter If_Loop()Zelle als Bereich dimmenFür jede Zelle im Bereich ("A2: A6")Wenn Cell.Value > 0 ThenCell.Offset(0, 1).Value = "Positive"ElseIf Cell.Value < 0 ThenCell.Offset(0, 1).Value = "Negativ"AndersCell.Offset(0, 1).Value = "Null"Ende WennNächste ZelleEnd Sub |
Für nächste 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 ganzen Zahlen 1 bis 10 und wird jeweils mit einem Meldungsfeld angezeigt:
| 123456 | Sub ForLoop()Dim i As IntegerFür i = 1 bis 10MsgBox iAls nächstesEnd Sub |
Machen Sie While-Schleifen
Do While Loops führt eine Schleife aus, während eine Bedingung erfüllt ist. Dieser Code durchläuft auch die ganzen Zahlen 1 bis 10 und zeigt jede mit einem Meldungsfeld an.
| 12345678 | Sub DoWhileLoop()Dim n As Integern = 1Tun, während n < 11MsgBox-Nrn = n + 1SchleifeEnd Sub |
Mache bis Schleifen
Umgekehrt wird Do Until Loops wiederholt, bis eine Bedingung erfüllt ist. Dieser Code macht dasselbe wie die beiden vorherigen Beispiele.
| 12345678 | Sub DoUntilLoop()Dim n As Integern = 1Do Bis n >= 10MsgBox-Nrn = n + 1SchleifeEnd 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 endlose 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 Loops 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
For-Schleifensyntax
Mit der For Next-Schleife können Sie einen Codeblock eine bestimmte Anzahl von Malen wiederholen. Die Syntax lautet:
| 12345 | [Zähler dimmen als Integer]Für Zähler = Anfang bis Ende [Schrittwert][Etwas tun]Weiter [Zähler] |
Wobei die Elemente in Klammern optional sind.
- [Zähler so lang dimmen] - Deklariert die Zählervariable. Erforderlich, wenn Option Explicit oben in Ihrem Modul deklariert ist.
- Schalter - Eine Integer-Variable zum Zählen
- Start - Der Startwert (Bsp. 1)
- Ende - Der Endwert (Bsp. 10)
- [Schrittwert] - Erlaubt Ihnen, alle n ganzen Zahlen statt jede 1 ganze Zahl zu zählen. Sie können auch mit einem negativen Wert rückwärts gehen (z. B. Schritt -1)
- [Etwas tun] - Der Code, der sich wiederholen wird
- Weiter [Zähler] - Schlussanweisung für die For Next-Schleife. Sie können den Zähler einschließen oder nicht. Ich empfehle jedoch dringend, den Zähler einzubeziehen, da dies das Lesen Ihres Codes erleichtert.
Wenn das verwirrend ist, machen Sie sich keine Sorgen. Wir werden einige Beispiele überprüfen:
Zähl bis 10
Dieser Code zählt mit einer For-Next-Schleife bis 10:
| 12345678 | Unter ForEach_CountTo10()Dim n As IntegerFür n = 1 bis 10MsgBox-NrNächste NrEnd Sub |
For-Schleifenschritt
Bis 10 zählen - Nur gerade Zahlen
Dieser Code zählt bis 10 und zählt nur gerade Zahlen:
| 12345678 | Sub ForEach_CountTo10_Even()Dim n As IntegerFür n = 2 bis 10 Schritt 2MsgBox-NrNächste NrEnd Sub |
Beachten Sie, dass wir "Schritt 2" hinzugefügt haben. 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 schreiten:
For-Schleifenschritt - Invers
Countdown ab 10
Dieser Code wird von 10 heruntergezählt:
| 123456789 | Sub ForEach_Countdown_Inverse()Dim n As IntegerFür n = 10 zu 1 Schritt -1MsgBox-NrNächste NrMsgBox "Abheben"End Sub |
Zeilen löschen, 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 Reihen zu den unteren Reihen schleifen, werden Sie beim Löschen von Reihen Ihren Zähler durcheinander bringen.
In diesem Beispiel werden Zeilen mit leeren Zellen gelöscht (beginnend mit der unteren Zeile):
| 12345678910 | Unter ForEach_DeleteRows_BlankCells()Dim n As IntegerFür n = 10 zu 1 Schritt -1If Range("a" & n).Value = "" ThenRange("a" & n).EntireRow.DeleteEnde WennNächste NrEnd Sub |
Verschachtelte For-Schleife
Sie können eine For-Schleife in eine andere For-Schleife „verschachteln“. Wir verwenden Nested For Loops, um eine Multiplikationstabelle zu erstellen:
| 1234567891011 | Unter Nested_ForEach_MultiplicationTable()Dim row As Integer, col As IntegerFür Reihe = 1 bis 9Für Spalte = 1 bis 9Zellen (Zeile + 1, Spalte + 1).Wert = Zeile * SpalteNächste FarbeNächste ReiheEnd Sub |
Beenden für
Mit der Exit For-Anweisung können Sie eine For Next-Schleife sofort beenden.
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ö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 nach "Fehler" in Spalte A. Wenn er gefunden wird, wählt der Code die Zelle aus, benachrichtigt Sie über den gefundenen Fehler und verlässt die Schleife:
| 12345678910111213 | Unter ExitFor_Loop()Dim i As IntegerFür i = 1 bis 1000If Range("A" & i).Value = "error" ThenBereich("A" & i).WählenMsgBox "Fehler gefunden"Beenden fürEnde WennAls nächstesEnd Sub |
Wichtig: Bei verschachtelten For-Schleifen beendet Exit For nur die aktuelle For-Schleife, nicht alle aktiven Schleifen.
Weiter für
VBA verfügt nicht über den Befehl "Weiter", der in Visual Basic zu finden ist. Stattdessen müssen Sie "Beenden" verwenden.
VBA für jede Schleife
Die VBA For Each Loop 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 Nested For Each Loops auch 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 lautet:
| 123 | Für jedes Objekt in der Sammlung[Etwas tun]Nächstes [Objekt] |
Woher:
- Objekt - Variable, die einen Bereich, ein Arbeitsblatt, eine Arbeitsmappe, eine Form usw. darstellt (z. B. rng)
- Sammlung - Sammlung von Objekten (zB Range("a1:a10")
- [Etwas tun] - Codeblock zum Ausführen auf jedem Objekt
- Nächstes [Objekt] - Abschlusserklärung. [Objekt] ist optional, wird jedoch dringend empfohlen.
Für jede Zelle im Bereich
Dieser Code durchläuft jede Zelle in einem Bereich:
| 123456789 | Unter ForEachCell_inRange()Zelle als Bereich dimmenFür jede Zelle im Bereich("a1:a10")cell.Value = cell.Offset(0,1).ValueNächste ZelleEnd Sub |
Für jedes Arbeitsblatt in der Arbeitsmappe
Dieser Code durchläuft alle Arbeitsblätter in einer Arbeitsmappe und hebt den Schutz jedes Blatts auf:
| 123456789 | Unter ForEachSheet_inWorkbook()Dim ws als ArbeitsblattFür jedes ws in Arbeitsblätternws.Schutz "Passwort" aufhebenNächstes wsEnd Sub |
Für jede geöffnete Arbeitsmappe
Dieser Code speichert und schließt alle geöffneten Arbeitsmappen:
| 123456789 | Sub ForEachWB_inWorkbooks()Dim wb als ArbeitsmappeFür jedes wb in Arbeitsmappenwb.Close SaveChanges:=TrueNächstes wbEnd Sub |
Für jede Form im Arbeitsblatt
Dieser Code löscht alle Formen im aktiven Blatt.
| 123456789 | Sub ForEachShape()shp als Form dimmenFür jede shp in ActiveSheet.Shapesshp.LöschenNächster shpEnd Sub |
Für jede Form in jedem Arbeitsblatt in der Arbeitsmappe
Sie können auch For Each-Schleifen verschachteln. Hier werden wir alle Formen in allen Arbeitsblättern in der aktiven Arbeitsmappe durchlaufen:
| 1234567891011 | Sub ForEachShape_inAllWorksheets()Dim shp als Form, ws als ArbeitsblattFür jedes ws in ArbeitsblätternFür jede shp in ws.Shapesshp.LöschenNächster shpNächstes wsEnd Sub |
Für jeden - 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:
| 12345678910 | Unter ForEachCell_inRange()Zelle als Bereich dimmenFür jede Zelle im Bereich("a1:a10")Wenn cell.Value = "" Then _cell.EntireRow.Hidden = TrueNächste ZelleEnd Sub |
VBA Do While-Schleife
Die VBA Do While und Do Until (siehe nächster 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:
| 123 | Während Bedingung tun[Etwas tun]Schleife |
Woher:
- Zustand - Die Bedingung zum Testen
- [Etwas tun] - Der Codeblock zum Wiederholen
Sie können auch eine Do While-Schleife mit der Bedingung am Ende der Schleife einrichten:
| 123 | Tun[Etwas tun]Schleife während Bedingung |
Wir werden jeden vorführen und zeigen, wie sie sich unterscheiden:
Tue während
Hier ist das Beispiel der Do While-Schleife, das wir zuvor demonstriert haben:
| 12345678 | Sub DoWhileLoop()Dim n As Integern = 1Tun, während n < 11MsgBox-Nrn = n + 1SchleifeEnd Sub |
Schleife während
Lassen Sie uns nun dieselbe Prozedur ausführen, außer dass wir die Bedingung an das Ende der Schleife verschieben:
| 12345678 | Sub DoLoopWhile()Dim n As Integern = 1TunMsgBox-Nrn = n + 1Schleife während n < 11End Sub |
VBA tun bis Schleife
Do Until Loops wiederholt eine Schleife, bis eine bestimmte Bedingung erfüllt ist. Die Syntax ist im Wesentlichen die gleiche wie bei den Do While-Schleifen:
| 123 | Machen Sie bis Bedingung[Etwas tun]Schleife |
und ähnlich kann die Bedingung am Anfang oder am Ende der Schleife stehen:
| 123 | Tun[Etwas tun]Schleife bis Bedingung |
Machen bis
Diese do Until-Schleife zählt bis 10, wie in unseren vorherigen Beispielen
| 12345678 | Sub DoUntilLoop()Dim n As Integern = 1Do Bis n > 10MsgBox-Nrn = n + 1SchleifeEnd Sub |
Wiederholen bis
Diese Loop-Bis-Schleife zählt bis 10:
| 12345678 | Unter DoLoopUntil()Dim n As Integern = 1TunMsgBox-Nrn = n + 1Schleife bis n > 10End Sub |
Beenden Sie die Schleife
Ähnlich wie Exit For zum Beenden einer For-Schleife verwenden Sie den Befehl Exit Do, um eine Do-Schleife sofort zu beenden
| 1 | Beenden |
Hier ist ein Beispiel für Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop()Dim i As Integerich = 1Mache bis ich > 1000If Range("A" & i).Value = "error" ThenBereich("A" & i).WählenMsgBox "Fehler gefunden"BeendenEnde Wennich = ich + 1SchleifeEnd Sub |
Schleife beenden oder unterbrechen
Wie oben erwähnt, können Sie mit Exit For oder Exit Do Schleifen beenden:
| 1 | Beenden für |
| 1 | Beenden |
Diese Befehle müssen jedoch Ihrem Code hinzugefügt werden, bevor Sie Ihre Schleife ausführen.
Wenn Sie versuchen, eine laufende Schleife zu „unterbrechen“, können Sie versuchen, zu drücken ESC oder STRG + Pause Pause auf der Tastatur. Dies kann jedoch nicht funktionieren. Wenn es nicht funktioniert, müssen Sie warten, bis Ihre Schleife beendet ist, oder im Falle einer Endlosschleife verwenden Sie STRG + ALT + Löschen um das Schließen von Excel zu erzwingen.
Aus diesem Grund versuche ich, Do-Schleifen zu vermeiden. Es ist einfacher, versehentlich eine Endlosschleife zu erstellen, die Sie zwingt, Excel neu zu starten und möglicherweise Ihre Arbeit zu verlieren.
Weitere Loop-Beispiele
Reihen durchschleifen
Dadurch werden alle Zeilen in einer Spalte durchlaufen:
| 123456789 | Öffentliche Sub LoopThroughRows()Zelle als Bereich dimmenFür jede Zelle im Bereich("A:A")Ff cell.value "" then MsgBox cell.address &": " & cell.valueNächste ZelleEnd Sub |
Durchlaufen von Spalten
Dadurch werden alle Spalten in einer Reihe durchlaufen:
| 123456789 | Öffentliche Sub LoopThroughColumns()Zelle als Bereich dimmenFür jede Zelle im Bereich ("1:1")Wenn cell.Value "" Then MsgBox cell.Address & ": " & cell.ValueNächste ZelleEnd Sub |
Durchlaufen von Dateien in einem Ordner
Dieser Code durchläuft alle Dateien in einem Ordner und erstellt eine Liste:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()oFSO als Objekt dimmenoFolder als Objekt dimmenoDatei als Objekt dimmenDim i As IntegerSet oFSO = CreateObject("Scripting.FileSystemObject")Set oFolder = oFSO.GetFolder("C:\Demo)ich = 2Für jede oFile in oFolder.FilesRange("A" & i).value = oFile.Nameich = ich + 1Nächste oDateiEnd Sub |
Loop-Through-Array
Dieser Code durchläuft das Array 'arrList':
| 123 | Für i = LBound(arrList) Zu UBound(arrList)MsgBox arrList(i)Als nächstes |
Die LBound-Funktion erhält die „untere Grenze“ des Arrays und UBound 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 anstelle des Range-Objekts.
| 123456789101112131415161718 | Sub LoopThroughRecords()Bei Fehler Fortsetzen als nächstesDim dbs als DatenbankZuerst als Recordset dimmenSetze dbs = CurrentDbSet rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)Mit rst.Zuletzt verschieben.Zuerst verschiebenTun bis .EOF = TrueMsgBox (rst.Fields("ClientName")).Nächstes verschiebenSchleifeEnde mitzuerst.SchließenSetze zuerst = NichtsSetze dbs = NichtsEnd Sub |
