Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 6 - Collections and Loops

Lesson 6.2 - For Next Loops

Topic 6.2.1 - Basic For Next Loops

In an earlier module we saw how to use a Do Loop to continue looping over a set of instructions based on the result of a logical test. A For Next loop continues looping based on the value of a counter. When the counter reaches a specified limit, the loop with stop. This part of the lesson explains the basic syntax of the For Next loop.

Files Needed

You don't need to download any files for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

The For Next Loop

To demonstrate the basics of the For Next loop we'll create a subroutine which loops three times and writes information to the Immediate window. Start by creating a new workbook and open the VBE. Insert a new module and create a subroutine called BasicForNextLoop:

Create a basic subroutine as shown here.

A For Next loop requires a variable to keep track of how many times the loop has been executed. Declare a counter variable called i which uses the Integer data type:

Declare a variable as shown. You can provide a different name for the variable if you prefer.

To begin a For Next loop you must say which number you want to the loop to begin counting from and which number it should count to. Add a line of code which counts from 1 to 3 as shown below:

Use the counter variable to count from a lower value to a higher one. you can start and end at any whole number, providing it is within the range of values allowed by the data type you have used.

To tell the loop to move on to the next iteration use the Next keyword:

You can optionally write the name of the variable after the word Next.

Each time the code reaches the Next keyword it will increase the value of the counter variable by 1 and then check the value against the number you have told the loop to count up to. The loop will automatically stop once the value of the variable exceeds the upper limit of the loop that you specified.

All that remains is to add some code within the loop. Add an instruction which writes the value of the counter variable to the Immediate window:

Use the Debug.Print statement to write information to the Immediate window.

Execute the subroutine and check the results in the Immediate window. If you can't see this window, choose View | Immediate Window from the menu:

The not very impressive result of running the code!

Why Use For Next Loops?

For Next loops are useful when you have a set number (or you can determine the number) of iterations to perform. We can create a procedure which created a new worksheet for each month of the year by looping twelve times. Begin a new subroutine called CreateMonthSheets and add an instruction which creates a new workbook:

Begin the subroutine as shown here.

Create a variable which you can use as a loop counter:

We've used a more descriptive name for the variable this time.

Create a For Next loop which counts from 1 to 12:

Create the loop below the line which creates the new workbook.

Add an instruction within the loop which creates a new worksheet and positions it after any existing sheet in the workbook:

Add the instruction to add the worksheet between the For and Next lines.

Add an instruction within the loop to change the name of the sheet that has just been added:

You can use the MonthName function to convert the numbers 1 to 12 into the corresponding month names. Set the second parameter of the function to True to create short month names or False to create full month names.

Run the subroutine and check the result in the new workbook that has been created:

The new worksheet will have a separate sheet for each month of the year.

Exiting from a For Next Loop

You can exit from a For Next loop before the counter reaches its limit using the Exit For statement. In the example we have just created, perhaps we want to create worksheets only up to the current month of the year. Add an If statement which checks if the value of the MonthNum variable is equal to the month number of the current system date:

If the value of the counter variable is the same as the number of the current month then exit from the For Next loop.

Run the subroutine again and check that you see a different number of worksheets (unless it is currently December!):

Running the code in August means that only eight new worksheets are created in the new workbook.

To practise writing basic For Next loops we'll create a subroutine which shows the 56 different colours available to the ColorIndex property of a cell:

Create a new subroutine called ColourLoop and declare a variable to use as the loop counter:

Sub ColourLoop()

Dim ColourNum As Integer

End Sub

Add instructions to activate the workbook in which the code is stored and insert a new worksheet:

Sub ColourLoop()

Dim ColourNum As Integer

ThisWorkbook.Activate

Worksheets.Add

End Sub

Create a For Next loop which counts from 1 to 56:

Sub ColourLoop()

Dim ColourNum As Integer

ThisWorkbook.Activate

Worksheets.Add

For ColourNum = 1 To 56

Next ColourNum

End Sub

Declare a variable which can hold a reference to a Range object.

Sub ColourLoop()

Dim ColourNum As Integer

Dim ColourCell As Range

ThisWorkbook.Activate

Worksheets.Add

For ColourNum = 1 To 56

Next ColourNum

End Sub

Add an instruction within the loop which sets the Range variable to refer to a cell in column A in the same row as the number held in the ColourNum variable:

Sub ColourLoop()

Dim ColourNum As Integer

Dim ColourCell As Range

ThisWorkbook.Activate

Worksheets.Add

For ColourNum = 1 To 56

Set ColourCell = Cells(ColourNum, 1)

Next ColourNum

End Sub

Add an instruction within the loop which sets the Interior.ColorIndex property of the cell referenced by the Range variable to the value held by the ColourNum variable:

Sub ColourLoop()

Dim ColourNum As Integer

Dim ColourCell As Range

ThisWorkbook.Activate

Worksheets.Add

For ColourNum = 1 To 56

Set ColourCell = Cells(ColourNum, 1)

ColourCell.Interior.ColorIndex = ColourNum

Next ColourNum

End Sub

Run the subroutine and check the results in Excel:

The first few rows of the new worksheet should resemble this.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The For Next Loop

Why Use For Next Loops?

Exiting from a For Next Loop

Head office

London

Manchester