This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

2.6 Loops (part 3)

Beginning VBA

Beginning VBA

Previous Part: 2.5 Using Logic within VBA

 

Loops recap

In section 2.2 Loops (part 1) we covered the For loop, which loops through a piece of code a specified number of times.

In section 2.4 Loops (part 2) we covered the For Each loop, which loops through each item within a collection of objects (for example, each worksheet in a workbook, or each cell in a range).

Now we reach the third part, which covers the Do Loop.  This loop will repeat a piece of code while a condition remains true or false.  As an example, we might loop through each cell in a range, until a blank cell is found.  This differs from the For and For Each loop, as when using those loops there is an which naturally occurs.  With the Do loop we do not know how many times it will loop, it will just keep looping while or until the conditions are met.

This makes the Do loop riskier than other loops, as it is very easy (very very easy) to create an infinite loop.

Many call this the While loop.  However, the While loop itself is considered to be obsolete, and we should use the Do loop in its place.  If you find code on the net with the While loop, do not worry, it will still work and will be understandable (once you’ve finished this article).

 

The Do Loop

There are 4 varieties of Do loop, each with a slightly different syntax.  This may sound confusing, but as we work through a few examples, it will all become clear.

 

Do While Loop

This basic example loops through Cells in Column A, moving down the column while the value in each cell is less than 10.  As soon as the value in a cell is more than 10 the loop will cease.

'Create a counter
Dim i As Integer
i = 1

'Move keep looping whilst the cell value
'is less than 10
Do While Cells(i, 1) < 10

    MsgBox i

    'Add 1 to the counter
    i = i + 1

Loop

 

Do Until Loop

The Do Until Loop is the opposite of the Do While Loop.  Rather than looping while the value is less than 10, it will loop until the value is greater or equal to 10.  It will give the same result.

'Create a counter
Dim i As Integer
i = 1

'Move keep looping until the cell value
'is greater than or equal to 10
Do Until Cells(i, 1) >= 10

    MsgBox i

    'Add 1 to the counter
    i = i + 1

Loop

Advertisement:

 

Do Loop While

If we need a scenario where the code within the loop executes at least once, the Do While Loop and Do Until Loop are not suitable.  The first condition must be met before the loop starts.

The Do Loop While is the solution; it places the condition at the end of the statement; therefore it will be executed at least once.

'Create a counter
Dim i As Integer
i = 1

'Start the loop
Do

    MsgBox i

    'Add 1 to the counter
    i = i + 1

'Continue to loop while the next cell value
'is less than 10
Loop While Cells(i, 1) < 10

In this code, it does not matter whether the value in Cell A1 is greater than or less than 10; the code executes at least once.

 

Do Loop Until

You’ve probably guessed it, this last loop will execute the first time and continue to loop until a condition is met.

'Create a counter
Dim i As Integer
i = 1

'Start the loop
Do

    MsgBox i

    'Add 1 to the counter
    i = i + 1

'Continue to loop until the next cell value
'is greater than or equal to 10
Loop Until Cells(i, 1) >= 10

 

The While Loop

The While Loop (which is now replaced with the Do loops above) operates in the same way as the Do While Loop.  The only difference is the While loop starts with While (rather than Do While) and ends with Wend (rather than Loop).

 

Exiting a loop

If you are using loops to find a something, then it is crazy to keep looping once you’ve found it.  It would be like continuing to look for your keys after you’ve already found them.  We can use the Exit Do statement to leave any of the Do loops.

'Create a counter
Dim i As Integer
i = 1

'Loop while the counter is less than 100
Do While i < 100

    MsgBox i
    
    'If the cell value is > 10
    If Cells(i, 1) >= 10 Then
 
        'Exit the Do loop even though the original 
        'condition has not been met
        Exit Do

    End If

'Add 1 to the counter
 i = i + 1

Loop

 

Oh No!!!! The infinite loop

I’ve created infinite loops, and you will too.  Even if you’re an expert coder, it is going to happen at some point.

It occurs when a condition is never met.  The code below will loop until a counter is greater than 100.  As the counter starts at zero and reduces by one each time the code loops it will never be greater than 100; it is an infinite loop.

Dim i As Integer
i = 1

'Loop until the counter is greater than 100
Do Until i > 100

    'Minus 1 from the counter
    i = i - 1

Loop

In our example, the variable is an Integer (which has a permitted minimum value of -32,768) and the code executes quickly, so it will not be long until the variable is out of memory and will cause an error.  But if the code took 1 minute to execute or if the variable was a double, you would be waiting a very, very long time.

The easiest way to stop an infinite loop is to crash Excel.  Press Ctrl + Shift + Esc to open the Task Manager and end the Excel session.  Always save before running code with a Do loop (actually try to save your code regularly anyway, as who knows what might happen).


Advertisement:

 

Previous Part: 2.5 Using Logic within VBA

Next Part: (coming soon)



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are: