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:

Basic sub

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 variable

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:

Count

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:

Next

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:

Write to immediate

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:

Results

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:

New sub

Begin the subroutine as shown here.

 

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

Variable

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

 

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

For Next loop

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 sheet

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:

MonthName

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:

Result

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 statement

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!):

Result

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:

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

Sub ColourLoop()

 

Dim ColourNum As Integer

 

End Sub

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. Run the subroutine and check the results in Excel:
Result

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

 
  1. Save and close the workbook.
This page has 0 threads Add post