VBA Looping - For Next (1 of 3)

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)