Lesson 4 Excel VBA Loops

Workbook Download
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
Dim iMyNumber2 ad 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
Dim iMyNumber2 ad 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")
rMyCell.Value = rMyCell.Address
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.

My Personal Experience

While Loops are without doubt a very handy feature of VBA they are the most misused piece of code in VBA. In all the above examples the Loop will complete in less than a second. But in most real world situations you will need to Loop a lot lot more than 100 times this may be as high as 1000000 times or more! When you try to Loop any more than 1000 times and are writing to cells or some other operation you will notice a time lag. All too often I see code that Loops through thousands of Cells just to look for ten or more cells that meet the condition they are after. This is either due to ignorance and/or laziness. There are many alternatives to Loops in these cases that will do the same job, but about 100 times (or more!) quicker and more efficiently. But people tend to stick with what they know or the way they have always done things.

Don't Fall Into The Loop Yourself

I often use this expression to try and drive home the point that Loops can (and do!) draw people in. A lot of people, when they first get the hang of Loops, find they can use them for a whole range of solutions that they previously THOUGHT was not possible. This then draws them into the Loop where they themselves become trapped. I too used to do the same, but when I started Looping through thousands of cells just to find the few I wanted and it took anything from 10 secs to more than 5 mins, I thought that this just cannot be right. You see when you use Excels Find function (Edit>Find) it searches through ALL 16777215 cells and finds what you want in the blink of the eye, no matter which cell it is in. You have a list of data entries and you only want to see all the entries that start with the letter "A", you use AutoFilter and again in the blink of an eye you have them. So initially I started to try and fathom out exactly how these built in Function it did it. Then the penny dropped! Instead of trying to mimic the functions like Find, AutoFilter and the Worksheet Functions to name but a few, I decided I would work them into my code and leave the Loops out altogether. At first this this meant I had to 'Step outside the box' as they say and actually look at a problem from a different angle altogether. But now I will go to no ends to try and find an alternative to some Loop code that needs to Loop anymore than 1000 times.

Short Term Pain For Long Term Gain

There is no mistaking that trying to find an alternative to a Loop will mean sitting back and thinking a lot harder. It will also mean trying a lot of alternate methods to a Loop that wont work at all and/or give Run Time Errors etc. But believe me, persistence WILL pay off. Once you have done this quite a few times you will realise that Loops only need play a very small part in your programming. Then, like me, you will no doubt look for any other method except a Loop. To date I have been able to use an alternative to a Loop about 80% of the time. I often run into a problem that requires dealing with thousands of Cells, Rows etc. Years ago I would have used a Loop, but now a Loop is something I only use as a last resort. Don't misunderstand me, if I'm only dealing with hundreds rather than thousands and I cannot think of an alternative, I wont ponder on it for too long before using the Loop. But the heading of this paragraph is very true.

Sometimes You Just Have To Loop

As I have stated above, 80% of the time I can avoid a Loop, but there is that 20% when I have to bite the bullet and use one. If this is the case I will immediately try to think of a way I could narrow down the field to Loop through. Let's say I need to search through a range of 10000 cells (A1:D2050) that contain a mix of numbers, text and formulas and Clear all the cells that contain formulas and have a value of greater than 100. Firstly I'm only interested in Numeric cells and of those I'm only interested in Formula cells. So ideally I would like to narrow the field down to Numbers and Formulas. We can easily do this by setting a Range Object to all cells in the range A1:D2050 that are Formulas and Numeric. We do this by using Excels SpecialCells Method:

Sub LoopThroughNumericFormulas()
Dim rNumFormulas as Range

Set rNumFormulas = _
Range("A1:D2050").SpecialCells(xlCellTypeFormulas, xlNumbers)
End Sub

Now that we have narrowed the field down we can Loop through the cells in our Range Variable "rNumFormulas" and Clear all cells that are greater than 100. We would do this like below:

Sub LoopThroughNumericFormulas()
Dim rNumFormulas As Range
Dim r100OrGreater As Range

    Set rNumFormulas = _
    Range("A1:D2050").SpecialCells(xlCellTypeFormulas, xlNumbers)

    For Each r100OrGreater In rNumFormulas
        If r100OrGreater.Value > 100 Then r100OrGreater.Clear
    Next r100OrGreater

Set rNumFormulas = Nothing
End Sub

This could potentially make our Loop run in less than 1 second as apposed to 5 to 10 seconds or even longer. There many other methods that I use which are similar to this. Below is a list of Excel built in features that can quite often be used either in place of, or to narrow down a Loop.

AutoFilter
AdvancedFormulas
Find
SpecialCells
Sort
SubTotals
AutoFill
WorksheetFunction
TextToColumns
Group

I have included with this lesson some examples of Loops and how they might be used. I will also include a Loop that CAN be narrowed down and done a far quicker way. Don't worry too much if you cannot achieve it, I would be very surprised if you can! The loop will take a while to run, but I want to show you a comparison.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.