 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
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.