Hello. We have been reading about
VBA useful features in series of posts. Understanding these will help you to a
great extent when you start serious coding. Do you remember our post on why not
record the macro all the time instead of coding? We saw that recording macros
does not support looping. That means if you have to perform a task n number of times, you have to either record
performing the task n number of
times or run the macro n number of
times. This is major drawback in recording macros and we saw that VBA coding
would help in this aspect. In this post, we are going to see how to use looping
in VBA.
For starters, we can use looping
to perform a task n number of times
where n can be defined by you or get
from user by an InputBox or UserForm. n need
not be defined as exact number, it can also be decided based on the situation.
To make it clear, you can loop a procedure 10 times by hard coding the number
10 or decide it based on the situation. For example: you can loop a macro those
many times as the number of cells selected before executing the macro or you
can loop those many times as the number of sheets in that workbook etc.
There are two kinds of loops in
VBA viz. For Next Loop, Do Loop. We will see about For Next Loop in this post
and continue with other kind in a future post. Let’s rock.
For Next Loop
You can use this loop in two ways.
First one is by using a variable as
a counter to tell VBA how many times the procedure to run.
Syntax is like below:
For VariableName = Start To end
Statements…..
Next VariableName
Let’s see what the syntax means.
For: key word to call the loop.
VariableName: Is any name you give to your variable.
It will be used as counter.
Start: Is the value you want to start your
variable at. It can be any number.
End: Is the value where you want to end the loop. It can be any
number more than Start. Loop will be
executed those many number of times as the difference between end and start.
Next: key word to loop. VariableName is not mandatory to be placed next
to Next but it looks neat and when
you have chain of loops with in loops, it will be clear.
Example:
I want to show a message box 2
times, with a prompt showing how many times the loop is run already. I use a
variable named MyCount in this
statement. See the sentence construction.
Sub Test()
Dim MyCount As Integer 'Declaring my variable
For MyCount = 1 To 2 'Declaring how many times it should loop
MsgBox MyCount 'Show message box
Next MyCount 'Looping
End Sub
Copy above code and paste it in your
code module. Make Locals window
visible on the screen and press F8 repeatedly and observe what happens to the
variable value on each press. I will take you through step by step on what’s
happening.
When the execution point (yellow
highlight) reaches For statement, MyCount value is empty. On press of F8,
focus will be moved to MsgBox line
and MyCount Value is set at 1 as in
the below screenshot.
This 1 is the value I set in For statement at the start. If you set
10 there, you will get 10 as the value in the Locals window. In that case, your code line will be For MyCount =
10 To …..
Pressing F8 again will display a
message box with 1.
Once you click OK in the message
box, execution point moves to Next statement.
Press F8 again and notice the code and locals window. Focus will move to MsgBox
line again (hey, that means we successfully looped) and Locals window now changes the value of MyCount to 2.
It means, when VBA executes Next statement, it increases the count
of variable automatically by adding 1 to it. Go ahead, press F8 again and VBA
will show the message box again.
Click on OK in the message box,
execution point moves to Next
statement again. So we wanted to loop 2 times and we got message 2 times. So,
ideally looping should stop now. Let’s see what happens if you press F8 now.
Press F8 and you will notice that
execution point moves to End Sub statement
and MyCount now shows a value 3 in Locals window. This is because focus
moved to Next statement 3 times so
counter increased to 3. That’s normal and you need not worry about it. Pressing
F8 again would end the procedure.
Step
We saw that counter will be
increased by 1 every time you reach the Next
statement. You can change this number to any other number. VBA adds that number on each execution of Next. This is done by using Step word as show below.
For MyCount = 1 To 10 Step 2
Here, I am trying to loop
starting 1 till 10 but at the end of every Next,
VBA adds 2 to the variable value instead of 1. You can enter any number
next to Step. If I run above line
placed in our example code at the start, I will get message box 5 times.
Step In Reverse
If you want to loop in reverse
order, you can use the code as below.
For MyCount = 10 To 1 Step -2
This will take MyCount value as 10 at the start and
keeps on reducing by 2 when Next
statement is executed.
We will use this looping method
in various codes going forward. Just know the basics and when we use it in a
code later, we will be clearer. Happy Learning!!
There is one more example of For Each available in the post: Unhide All Hidden Sheets using For Next & For Each. You may also continue reading about For Each Looping - For Next (2 of 3)
