In dit artikel hebben we verschillende soorten lussen besproken die in VBA worden gebruikt en hoe we deze kunnen gebruiken om dezelfde taak op verschillende manieren uit te voeren.
Waarom Loops?
Looping is een van de krachtigste programmeertechnieken die in veel programmeertalen wordt gebruikt. Looping wordt gebruikt om een codeblok het vereiste aantal keren te herhalen of totdat een bepaalde voorwaarde waar is of een specifieke waarde is bereikt, waarna het volgende codeblok wordt uitgevoerd.
Het doel van een Excel VBA-lus is om Excel een bepaald aantal keren een stuk code te laten herhalen. Men kan specificeren hoe vaak een code herhaald moet worden als een vast getal (doe dit bijvoorbeeld 10 keer), of als een variabele (doe dit bv. zo vaak als er rijen gegevens zijn).
Excel Loops kunnen op verschillende manieren worden geconstrueerd voor verschillende omstandigheden. Vaak kunnen dezelfde resultaten op verschillende manieren worden verkregen om aan uw persoonlijke voorkeuren te voldoen.
Er zijn drie verschillende soorten lussen beschikbaar in Excel VBA, namelijk:
1. DOEN TOT lus
2. DO WHILE Loop
3. FOR-lus
1. DOEN TOT lus
De DO UNTIL Loop wordt gebruikt om een codeblok voor onbepaalde tijd te herhalen, totdat de opgegeven voorwaarde is ingesteld op True. De conditie kan aan het begin of aan het einde van de Loop worden gecontroleerd. De instructie DO UNTIL… LOOP test de voorwaarde aan het begin, terwijl de instructie DO… LOOP UNTIL de voorwaarde aan het einde van de loop test.
Syntaxis van DO UNTIL… LOOP-instructie
Doen tot [Voorwaarde]
[Blok met code moet worden herhaald]
Lus
Syntaxis van de instructie DO… LOOP UNTIL
Doen
[Blok met code moet worden herhaald]
Loop tot [Conditie]
We hebben de DO… UNTIL-lus uitgelegd met een voorbeeld. Loop1- en Loop2-macro's worden gebruikt om het gemiddelde van getallen in kolom A en kolom B te berekenen met behulp van de DO… UNTIL-lus.
Voorbeeldgegevens zijn aanwezig in het bereik A15:B27. Kolom A bevat scores van ronde 1 en kolom B bevat scores van ronde 2. We willen gemiddelden berekenen van scores in ronde 1 en ronde 2 in kolom C.
In Loop1-macro hebben we "FormulaR1C1" gebruikt om de gemiddelde formule in de actieve cel in te voegen. De voorwaarde-instructie in de DO UNTIL-lus wordt gecontroleerd aan het einde van de lus.
In Loop2-macro hebben we "WorksheetFunction.Average" gebruikt om de gemiddelde waarde in de actieve cel in te voegen. Zelfs in deze macro wordt de voorwaarde-instructie aan het einde van de lus gecontroleerd.
Het enige verschil tussen Loop1 en Loop2 macro is dat Loop1 de gemiddelde formule invoegt, terwijl Loop2 het gemiddelde berekent en vervolgens de gemiddelde waarde in de actieve cel invoegt.
2. DO WHILE Loop
De DO WHILE-lus wordt gebruikt om een codeblok voor onbepaalde tijd te herhalen, terwijl de opgegeven voorwaarde True blijft en stopt wanneer de voorwaarde False retourneert. De conditie kan aan het begin of aan het einde van de Loop worden gecontroleerd. De instructie DO WHILE… LOOP test de voorwaarde aan het begin, terwijl de instructie DO… LOOP WHILE de voorwaarde aan het einde van de lus test. De instructie DO… LOOP WHILE wordt gebruikt wanneer we willen dat de lus het codeblok minstens één keer uitvoert voordat wordt gecontroleerd op de voorwaarde.
Syntaxis van DO WHILE… LOOP-instructie
Doen terwijl [Voorwaarde]
[Blok met code moet worden herhaald]
Lus
Syntaxis van de instructie DO… LOOP WHILE
Doen
[Blok met code moet worden herhaald]
Loop terwijl [Conditie]
In dit voorbeeld worden Loop3- en Loop4-macro's gebruikt om gemiddelden te berekenen voor waarden in cellen van kolom A en kolom B. Beide macro's werken op dezelfde voorbeeldgegevens als gebruikt door macro's Loop1 en Loop2. Beide gebruiken de instructie DO WHILE om door het bereik te lopen dat de gegevens bevat.
Het enige verschil tussen Loop3- en Loop4-macro's is dat het verschillende manieren zijn om de voorwaarden van de DO WHILE-lus weer te geven.
Omdat Loop3- en Loop4-macro's dezelfde invoergegevens gebruiken en zelfs dezelfde functies uitvoeren als Loop1-macro, zodat de geretourneerde uitvoer ook hetzelfde is als die van Loop1-macro.
3. FOR-lus
De For Loop wordt gebruikt om een codeblok een bepaald aantal keren te herhalen.
Syntaxis van FOR-lus
Voor count_variable = start_value To end_value
[codeblok]
Volgende count_variable
De Loop5-macro laat zien hoe u de FOR-lus kunt gebruiken om het gemiddelde te berekenen. Het gebruikt ook dezelfde voorbeeldgegevens die door andere macro's worden gebruikt. We hebben 15 als startwaarde gebruikt omdat de voorbeeldgegevens beginnen bij de 15e rij. We hebben Range("A" & Cells.Rows.Count).End(xlUp).Row gebruikt om de laatste rij met gegevens te vinden. FOR-lus wordt herhaald (laatste cel-15) aantal keren.
De uitvoer die wordt geretourneerd na het uitvoeren van de Loop5-macro is hetzelfde als die van de Loop1-macro.
Loop6-macro wordt gemaakt om het gemiddelde te berekenen, alleen als de actieve cel die de gemiddelde functie heeft, leeg is voordat de macro wordt uitgevoerd.
Voorbeeldgegevens voor deze macro zijn aanwezig in het bereik E15 tot G27.
We hebben DO… LOOP WHILE gebruikt om het gedefinieerde bereik te doorlopen. IF-statement wordt gebruikt om te controleren of de cel waarin de functie wordt ingevoegd, een waarde bevat. Deze macro voegt alleen de gemiddelde functie toe aan de cel als deze leeg is.
Loop7-macro wordt ook gebruikt om het gemiddelde te berekenen. Het controleert op waarden in de helperkolom voordat wordt geëvalueerd of er opnieuw een lus moet worden gemaakt. Het controleert ook of de celverwijzing die in de gemiddelde functie moet worden gebruikt, leeg is.
Voorbeeldgegevens die voor Loop7-macro worden gebruikt, liggen in het bereik J15:M27.
Kolom M wordt gebruikt als hulpkolom. Deze macro voegt alleen een gemiddelde functie in als een cel in kolom M niet leeg is. Deze macro controleert of een cel leeg moet zijn voordat er een gemiddelde functie in wordt ingevoegd. Er wordt geen gemiddelde functie ingevoegd als de cel waarnaar wordt verwezen in de gemiddelde functie leeg is.
Volg hieronder voor de code:
Optie Explicit Sub Loop1() 'Gemiddelde berekenen' Do Tot lus loopt totdat cel in de vorige kolom van actieve cel leeg is Range ("C15"). Selecteer Do 'Gemiddelde functie toewijzen aan waarde in cellen van vorige twee opeenvolgende kolommen ActiveCell. FormulaR1C1 = "=Average(RC[-1],RC[-2])" 'Verplaatsen naar cel in volgende rij ActiveCell.Offset(1, 0).Selecteer 'Controleren of waarde in cel van vorige kolom leeg is' Doen tot lus loopt door totdat voorwaarde-instructie True Loop Until IsEmpty(ActiveCell.Offset(0, -1)) Range("A15") retourneert.Select End Sub Sub Loop2() 'Calculating average' Do Until lus loopt door totdat cel in de vorige kolom van actieve cel is leeg 'Deze macro is vergelijkbaar met macro Loop1, enige manier om het gemiddelde te berekenen is anders Range ("C15"). Selecteer Do 'Worsheet.Average-functie wordt gebruikt voor het berekenen van het gemiddelde ActiveCell.Value = WorksheetFunction.Average( ActiveCell.Offset(0, -1).Waarde, _ ActiveCell.Offset(0, -2.Value) ActiveCell.Offset(1, 0).Selecteer Lus tot leeg is(ActiveCel l.Offset(0, -1)) Range("A15").Select End Sub Sub Loop3() 'Berekenen van gemiddelde'Do While-lus loopt totdat cel in de vorige kolom van actieve cel leeg is Range("C15") .Selecteer 'Controleren of de waarde in de cel van de vorige kolom leeg is' 'Do While-lus wordt herhaald totdat de voorwaarde-instructie True is. Do While IsEmpty(ActiveCell.Offset(0, -1)) = False 'Gemiddelde functie toewijzen aan waarde in cellen van vorige twee opeenvolgende kolommen ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])" 'Verplaatsen naar cel in volgende rij ActiveCell.Offset(1, 0).Select Loop Range("A15").Select End Sub Sub Loop4() 'Berekenen van gemiddelde' Do While loop wordt uitgevoerd totdat cel in de vorige kolom van actieve cel leeg is 'Deze macro is vergelijkbaar met macro Loop3, de enige manier om de voorwaarde toe te passen is een andere Range ("C15").Selecteer Do While Not IsEmpty(ActiveCell.Offset(0, -1)) ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])" ActiveCell.Offset(1, 0).Select Loop Range(" A15"). Selecteer End Sub Sub Loop5() 'VOOR lusherhalingen voor een vast nummer' aantal keren bepaald door het aantal rijen Dim i, lastcell As Long 'De laatste rij met gegevens in kolom A vinden lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row Range("C15 ").Selecteer 'i variabele krijgt een waarde van 15 toegewezen omdat onze voorbeeldgegevens beginnen vanaf de 15e rij 'FOR Loop zal x For i = 15 To lastcell ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2 ])" ActiveCell.Offset(1, 0).Selecteer Next i Range("A15").Select End Sub Sub Loop6() 'Berekenen van gemiddelde 'Do Tot lus loopt door totdat cel in de vorige kolom van actieve cel leeg is' Het berekent geen gemiddelde als er al iets in de cel is. Bereik ("G15").Selecteer Do If IsEmpty(ActiveCell) Then ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])" End If ActiveCell.Offset(1, 0).Selecteer Loop Until IsEmpty(ActiveCell.Offset(0, -1)) Range("E15").Select End Sub Sub Loop7() 'Do Until loop loopt zolang er is iets in cel in volgende kolom 'Het berekent geen gemiddelde als er al iets in de actieve c . staat ell 'Noch als er geen gegevens zijn in cellen die worden gebruikt binnen de gemiddelde functie (om #DIV/0-fouten te voorkomen). 'Gemiddeld bereik berekenen ("L15"). Selecteer Doen als is leeg (actieve cel) dan als leeg is (actieve cel. Offset (0, -1)) en is leeg (actieve cel. Offset (0, -2)) dan ActiveCell. Waarde = " " Else ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])" End If End If ActiveCell.Offset(1, 0).Selecteer Loop Until IsEmpty(ActiveCell.Offset(0, 1)) Bereik ("J15"). Selecteer End Sub
Als je deze blog leuk vond, deel hem dan met je vrienden op Facebook. Ook kunt u ons volgen op Twitter en Facebook.
We horen graag van u, laat ons weten hoe we ons werk kunnen verbeteren en voor u kunnen verbeteren. Schrijf ons op de e-mailsite