# EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS # Excel Training VBA Lesson 10

## Excel VBA Loops

### << PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

This is a zipped Excel Workbook to go with this lesson.

Loops

This lesson we will focus on Loops. There are many varieties of these, but they are all basically the same in that they will repeat a line, or multiple lines, of code a set number times, or until a condition becomes True or False. Excel VBA has 5 loops from which we can choose from. Depending on the situation they are to be used for would dictate the type of loop to choose. Some loops are best suited for looping while incrementing a number, while others are ideal for looping through anyone of Excels Objects. Arguably 2 of the most useful loops would be the For loop and the For Each loop.

Shown below are the 5 loops available to us in Excel VBA.

Do
<Code to repeat>
Loop While <Whatever>

Do While <Whatever>
<Code to repeat>
Loop

Do Until <Whatever>
<Code to repeat>
Loop

For <Variable>= <Any Number> To <Any Other Number>
<Code to repeat>
Next   <Variable>

For Each <Object Variable> in <Object Collection>
<Code to repeat>
Next  <Object Variable>

I realise that loops can seem very confusing the first time you encounter them, but they really are not that complicated. Just remember, all they are doing is what you have instructed them to do (via code) either a set number of times or until a condition is met (True or False).

Let's look at each one in turn and use them in a simple way.

Do Loop

Sub OurDo()
Do
<Code to repeat>
Loop While <Whatever>
End Sub

The very first word here is "Do" so we now have to tell our Loop exactly what to Do. For this example we will add the number one to a Integer Variable we called "iMyNumber", so we can put:

Sub OurDo1()
Dim iMyNumber as Integer
Do
iMyNumber=1+iMyNumber
Loop While <Whatever>
End Sub

Ok, so we have now told our "Do" what it is we want to do. Now we have to tell it when to stop. To achieve this we must set a condition for our "Loop While". In this case we will: Do iMyNumber=1+iMyNumber until iMyNumber is > 100. So all we need to do is:

Sub OurDo2()
Dim iMyNumber as Integer

Do
iMyNumber=1+iMyNumber
Loop While iMyNumber < 100

End Sub

So the bottom line here is the Do Loop will Loop 101 times.

Do While

This is very similar to our Do Loop we just used above. The only difference is, instead of the condition we set (iMyNumber < 100) being checked AFTER the Do has run at least once, the Do While will check the condition BEFORE it runs. Let's say our Variable iMyNumber has already had the Value 101 parsed to it. In the Do Loop we used above, it will NOT know the Value of iMyNumber until it has run ONCE. This would mean our Variable would come out of the Do Loop with a Value of 102. But in the Do While below, it would never even enter the Loop:

Sub OurDoWhile()
Dim iMyNumber as Integer

Do While iMyNumber < 100
iMyNumber=1+iMyNumber
Loop
End Sub

Do Until

Again this is very similar to the Do While Loop we just used above in that it will check the condition BEFORE it enters the Loop.

If the Value of iMyNumber is 0 (zero) when it reaches the Loop, the difference is the Do While would keep adding the number one to iMyNumber until it reached 100.  In the Do Until it would never even enter the Loop because the condition MyNumber < 100 has been met already ie; iMyNumber is 0 (zero).

Sub OurDoUntil()
Dim iMyNumber as Integer

Do until iMyNumber < 100
iMyNumber=1+iMyNumber
Loop
End Sub

For

The For Loop is perhaps the most useful of all the Loops. It runs a line or lines of code a set amount of times in any increment we set. The default increment is one. As you can see from below you must use a Variable of the Numeric type to set the amount of Loops it will perform.

Sub OurFor()
Dim iMyNumber as Integer

For iMyNumber= 1 To 100
iMyNumber=1+iMyNumber
Next iMyNumber

End Sub

The above For Loop will simply Loop  through the code:

iMyNumber=1+iMyNumber

101 times. As we have set the condition we want met ie; increment iMyNumber by one, 100 times, we do NOT need to keep adding one to our Variable iMyNumber. This will happen automatically as the default increment is one. Now I know you are thinking why does it Loop 101 times and not 100? Simply because the default value for a Variable of the Numeric Data type is 0 (zero). This means that it must run once before "iMyNumber" will have the value of one.

We also do NOT need to increment our Variable by one each Loop with:

iMyNumber=1+iMyNumber

This is because we have already told our Loop this by using the For Loop type. So we could use:

Sub OurFor()
Dim iMyNumber as Integer

For  iMyNumber= 1 To 100
iMyNumber
Next iMyNumber
End Sub

...and iMyNumber will still end up with the Value of 101.  In fact we could even use:

Sub OurFor()
Dim iMyNumber as Integer

For iMyNumber= 1 To 100
iMyNumber2 =1+iMyNumber2
Next iMyNumber

End Sub

....and we would get the same result. You can see this for yourself by the use of a Message Box.

Sub OurFor()
Dim iMyNumber as Integer

For iMyNumber= 1 To 100
iMyNumber2 =1+iMyNumber2
Next iMyNumber

MsgBox iMyNumber

End Sub

The other great part about the For Loop is we can increment by any Value we like. We do this by using the Step Key word and telling it the Step (or increment) to use. so we could use:

Sub OurFor()
Dim iMyNumber as Integer
Dim iMyNumber2 as Integer

For iMyNumber= 1 To 100 Step 2
iMyNumber2 =1+iMyNumber2
Next iMyNumber

MsgBox iMyNumber

End Sub

By doing this we will Loop through our code 51 times instead of 101 times, but the Variable iMyNumber will end up with a Value of 101.

We could also use the Step Key word to work backwards like below:

Sub OurFor()
Dim iMyNumber as Integer
Dim iMyNumber2 as Integer

For iMyNumber= 100 To 1 Step -1
iMyNumber2 =1+iMyNumber2
Next iMyNumber

MsgBox iMyNumber

End Sub

This would mean that our Variable iMyNumber would end up with a Value of 0 (Zero).

For Each

This Loop is slightly different from the others, but only in the fact that it requires an Object as the Variable. What it does is simply Loop though each single Object in a Collection of Objects.

For Each <Object Variable> in <Object Collection>
<Code to repeat>
Next  <Object Variable>

To put this into something meaningful, we could use:

Sub OurForEach()
Dim rMyCell As Range
Dim iMyNumber2 As Integer

For Each rMyCell In Range("A1:A100")
iMyNumber2 = 1 + iMyNumber2
Next rMyCell

MsgBox iMyNumber2

End Sub

What this is saying in Layman's terms is:

For Each Cell in the Range A1 to Range A100 add 1 to the Variable iMyNumber2 Where "Cell" is represented by the Range Variable "rMyCell" So it will do this 100 times as there are 100 Range Objects in the Object Collection Range("A1:A100")

We do not need to tell the For Each Loop how many times to Loop as it already knows how many Objects (Cells in this case) there are in the Object Collection (Range(A1:A100)).

Our Object Collection does not have to be a Range Collection, it could be a Charts, Worksheets, Workbooks etc Collection. In fact it can be any Collection of Objects. So if we wanted to Loop through all Worksheets in a Workbook we could use:

Sub OurForEach()
Dim wWsht As Worksheet

For Each wWsht In ThisWorkbook.Worksheets
wWsht.Range("A1") = wWsht.Name
Next wWsht

End Sub

This would Loop through each Worksheet in the Workbook and place the name of the Worksheet in cell A1 of each.

So as you can see one of the big advantages with a For Each loop is that we do not need to know how many Objects are within the Object collection that we wish to loop through.

Inner and Outer Loops

Any type of Loop can have more than one level. This is very similar to Nesting Worksheet formulas on a Worksheet. There is no limit (except memory) of the level to which you can Nest loops. To keep things simple though we will only look at a two level Loop. Let's say we want to Loop through all cells in the Range A1:A10 on each Worksheet and place the address of the cell in each cell. To do this we would use:

Sub OurForEach()
Dim wWsht As Worksheet
Dim rMyCell As Range

For Each wWsht In ThisWorkbook.Worksheets
For Each rMyCell In wWsht.Range("A1:A10")
Next rMyCell
Next wWsht

End Sub

When you have two Loops Nested like this, you would refer to them as the Outer Loop and Inner Loop. With "For Each wWsht In ThisWorkbook.Worksheets" being the Outer Loop and "For Each rMyCell In wWsht.Range("A1:A10")" being the Inner Loop. What will happen is the code would first encounter the outer loop and know that is has to loop through whatever code is within it the same amount of times as there are Worksheets in the Workbook. As soon as it enters the outer loop it encounters the inner loop, it then knows it must loop through any code within it 10 times (there are 10 range Objects in range A1:A10). Once it has done the 10 loops it leaves the inner loop, it then continues on with the outer loop which in turn immediately makes it move onto the next Worksheet Object (wWsht) and the cycle starts again.

Exiting a Loop

In all the above Loop examples we have allowed the loop to continue on until the loop condition is met. But there are times when we may wish to force our loop to leave a loop early. This is done by using the Exit Statement. Let's assume we wish to loop through a range of cells and select a cell if it's value is 100.

Sub ExitALoop()
Dim rMyCell As Range

For Each rMyCell In Range("A1:A10")
If rMyCell.Value = 100 Then
rMyCell.Select
Exit For
End If
Next rMyCell
End Sub

This loop will only loop 10 times (A1:A10) if no cell within range A1:A10 is equal to 100. If there is a cell within range A1:A10 that is equal to 100 it will select it, then Exit the For Each loop.

We can also use the Exit Statement on any of the Do loops by simply using:

Exit Do

So that is basically all there is to Loops. Used in the context as shown above would not really be of much use, but it is far more important to understand the concept of them than to use them without knowing how they work. The only other part of Loops that you will most likely encounter is what is known as the endless Loop. This occurs when you start a loop that will never meet the condition you have set and so it just keeps going around endlessly. When this happens you need to push Ctrl+Break or Esc.