VBA für Loop, Verwenden von Loops in VBA in Microsoft Excel
In diesem Artikel haben wir verschiedene Arten von Schleifen behandelt, die in VBA verwendet werden, und wie sie verwendet werden, um dieselbe Aufgabe auf unterschiedliche Weise auszuführen.
Warum Loops?
Looping ist eine der leistungsstärksten Programmiertechniken, die in vielen Programmiersprachen verwendet werden. Das Schleifen wird verwendet, um einen Codeblock für die erforderliche Anzahl von Malen zu wiederholen oder bis eine bestimmte Bedingung als wahr ausgewertet wird oder ein bestimmter Wert erreicht ist, wonach der nächste Codeblock ausgeführt wird.
Der Zweck einer Excel-VBA-Schleife besteht darin, Excel dazu zu bringen, einen bestimmten Code ein bestimmtes Mal zu wiederholen. Man kann angeben, wie oft ein Code als feste Zahl (z. B. 10 Mal) oder als Variable (z. B. so oft wie Datenzeilen vorhanden sind) wiederholt werden muss.
Excel-Schleifen können auf unterschiedliche Weise erstellt werden, um den unterschiedlichen Umständen gerecht zu werden. Oft können dieselben Ergebnisse auf unterschiedliche Weise erzielt werden, um Ihren persönlichen Vorlieben zu entsprechen.
In Excel VBA stehen drei verschiedene Arten von Schleifen zur Verfügung:
-
DO BIS Loop 2. DO WHILE Loop 3. FOR Loop {leer} 1. DO UNTIL-Schleife Mit der DO UNTIL-Schleife wird ein Codeblock auf unbestimmte Zeit wiederholt, bis die angegebene Bedingung auf True gesetzt ist. Der Zustand kann entweder am Anfang oder am Ende der Schleife überprüft werden. Die Anweisung DO UNTIL… LOOP testet die Bedingung am Anfang, während die Anweisung DO… LOOP UNTIL die Bedingung am Ende der Schleife testet.
Syntax der Anweisung DO UNTIL… LOOP
Do Bis [Bedingung]
{leer} [Zu wiederholender Codeblock]
Schleife
Syntax der Anweisung DO… LOOP UNTIL
Do [Zu wiederholender Codeblock]
Schleife bis [Bedingung]
Wir haben die DO… UNTIL-Schleife anhand eines Beispiels erklärt. Die Makros Loop1 und Loop2 werden verwendet, um den Durchschnitt der Zahlen in Spalte A und Spalte B mithilfe der Schleife DO… UNTIL zu berechnen.
Probendaten liegen im Bereich A15: B27 vor. Spalte A enthält Punktzahlen von Runde 1 und Spalte B enthält Punktzahlen von Runde 2. Wir möchten Durchschnittswerte der Punktzahlen in Runde 1 und Runde 2 in Spalte C berechnen.
Im Loop1-Makro haben wir „FormulaR1C1“ verwendet, um eine Durchschnittsformel in die aktive Zelle einzufügen. Die Bedingungsanweisung in der DO UNTIL-Schleife wird am Ende der Schleife überprüft.
Im Loop2-Makro haben wir „WorksheetFunction.Average“ verwendet, um einen Durchschnittswert in die aktive Zelle einzufügen. Auch in diesem Makro wird die Bedingungsanweisung am Ende der Schleife überprüft.
Der einzige Unterschied zwischen Loop1- und Loop2-Makro besteht darin, dass Loop1 die Durchschnittsformel einfügt, während Loop2 den Durchschnitt berechnet und dann den Durchschnittswert in die aktive Zelle einfügt.
{leer} 2. DO WHILE-Schleife Die DO WHILE-Schleife wird verwendet, um einen Codeblock auf unbestimmte Zeit zu wiederholen, während die angegebene Bedingung weiterhin True ist und stoppt, wenn die Bedingung False zurückgibt. Der Zustand kann entweder am Anfang oder am Ende der Schleife überprüft werden. Die Anweisung DO WHILE… LOOP testet die Bedingung am Anfang, während die Anweisung DO… LOOP WHILE die Bedingung am Ende der Schleife testet. Die Anweisung DO… LOOP WHILE wird verwendet, wenn die Schleife den Codeblock mindestens einmal ausführen soll, bevor die Bedingung überprüft wird.
Syntax der Anweisung DO WHILE… LOOP
Do While [Bedingung]
{leer} [Zu wiederholender Codeblock]
Schleife
Syntax der Anweisung DO… LOOP WHILE
Do [Zu wiederholender Codeblock]
Schleife während [Bedingung]
In diesem Beispiel werden Loop3- und Loop4-Makros verwendet, um Durchschnittswerte für Werte in Zellen von Spalte A und Spalte B zu berechnen. Beide Makros arbeiten mit denselben Beispieldaten wie die Makros Loop1 und Loop2. Beide verwenden die Anweisung DO WHILE, um den Bereich zu durchlaufen, der die Daten enthält.
Der einzige Unterschied zwischen Loop3- und Loop4-Makros besteht darin, dass sie unterschiedliche Arten der Darstellung der Bedingungen der DO WHILE-Schleife darstellen.
Da Loop3- und Loop4-Makros dieselben Eingabedaten verwenden und sogar dieselben Funktionen wie das Loop1-Makro ausführen, entspricht die zurückgegebene Ausgabe auch der des Loop1-Makros.
{leer} 3. FOR-Schleife Die For-Schleife wird verwendet, um einen Codeblock für eine bestimmte Anzahl von Malen zu wiederholen.
Syntax der FOR-Schleife
Für count_variable = start_value To end_value {leer} [Codeblock]
Das nächste Makro count_variable Loop5 zeigt, wie die FOR-Schleife zur Berechnung des Durchschnitts verwendet wird. Es werden auch dieselben Beispieldaten verwendet, die auch von anderen Makros verwendet werden. Wir haben 15 als Startwert verwendet, da die Beispieldaten ab der 15. Zeile beginnen. Wir haben Range („A“ & Cells.Rows.Count) .End (xlUp) .Row verwendet, um die letzte Zeile mit Daten zu finden. Die FOR-Schleife wird (letzte Zelle – 15) so oft wiederholt.
Die nach dem Ausführen des Loop5-Makros zurückgegebene Ausgabe entspricht der des Loop1-Makros.
Das Loop6-Makro wird nur zur Berechnung des Durchschnitts erstellt, wenn die aktive Zelle mit der Durchschnittsfunktion leer ist, bevor das Makro ausgeführt wird.
Beispieldaten für dieses Makro liegen im Bereich E15 bis G27 vor.
Wir haben DO… LOOP WHILE verwendet, um den definierten Bereich zu durchlaufen. Mit der IF-Anweisung wird geprüft, ob die Zelle, in die die Funktion eingefügt wird, einen Wert enthält. Dieses Makro fügt nur dann eine Durchschnittsfunktion in die Zelle ein, wenn sie leer ist.
Das Loop7-Makro wird auch zur Berechnung des Durchschnitts verwendet. Es prüft auf Werte in der Hilfsspalte, bevor bewertet wird, ob eine erneute Schleife durchgeführt werden soll. Es wird auch geprüft, ob die in der Durchschnittsfunktion zu verwendende Zellreferenz leer ist.
Die für das Loop7-Makro verwendeten Beispieldaten liegen im Bereich J15: M27.
Spalte M wird als Hilfsspalte verwendet. Dieses Makro fügt nur dann eine Durchschnittsfunktion ein, wenn eine Zelle in Spalte M nicht leer ist. Dieses Makro überprüft, ob eine Zelle leer sein sollte, bevor eine Durchschnittsfunktion in sie eingefügt wird. Es wird keine Durchschnittsfunktion eingefügt, wenn die Zelle, auf die in der Durchschnittsfunktion verwiesen wird, leer ist.
Bitte folgen Sie unten für den Code
Option Explicit
Sub Loop1()
'Calculating average
'Do Until loop will loop until cell in the previous column of active cell is empty
Range("C15").Select
Do
'Assigning average function on value in cells of previous two consecutive columns
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
'Moving to cell in next row
ActiveCell.Offset(1, 0).Select
'Checking whether value in cell of previous column is empty
'Do Until loop will loop until condition statement returns True
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
Range("A15").Select
End Sub
Sub Loop2()
'Calculating average
'Do Until loop will loop until cell in the previous column of active cell is empty
'This macro is similar to macro Loop1, only way of calculating average is different
Range("C15").Select
Do
'Worsheet.Average function is used for calculating the average
ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, _
ActiveCell.Offset(0, -2).Value)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
Range("A15").Select
End Sub
Sub Loop3()
'Calculating average
'Do While loop will run until cell in the previous column of active cell is empty
Range("C15").Select
'Checking whether value in cell of previous column is empty
'Do While loop will loop until condition statement is True
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
'Assigning average function on value in cells of previous two consecutive column
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
'Moving to cell in next row
ActiveCell.Offset(1, 0).Select
Loop
Range("A15").Select
End Sub
Sub Loop4()
'Calculating average
'Do While loop will run until cell in the previous column of active cell is empty
'This macro is similar to macro Loop3, only way of applying condition is different
Range("C15").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
Range("A15").Select
End Sub
Sub Loop5()
'FOR loop repeats for a fixed number of times determined by the number of rows
Dim i, lastcell As Long
'Finding the last row containing data in column A
lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row
Range("C15").Select
'i variable is assigned value of 15 as our sample data begin from 15th row
'FOR Loop will loop x
For i = 15 To lastcell
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Next i
Range("A15").Select
End Sub
Sub Loop6()
'Calculating average
'Do Until loop will loop until cell in the previous column of active cell is empty
'It does not calculate an average if there is already something in the cell
Range("G15").Select
Do
If IsEmpty(ActiveCell) Then
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
Range("E15").Select
End Sub
Sub Loop7()
'Do Until loop runs as long as there is something in cell in next column
'It does not calculate an average if there is already something in the active cell
'Nor if there is no data in cells which are used within average function (to avoid #DIV/0 errors).
'Calculating average
Range("L15").Select
Do
If IsEmpty(ActiveCell) Then
If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
ActiveCell.Value = ""
Else
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Range("J15").Select
End Sub
Wenn dir dieser Blog gefallen hat, teile ihn mit deinen Freunden auf Facebook und Facebook.
Wir würden gerne von Ihnen hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern und für Sie verbessern können. Schreiben Sie uns unter [email protected]