This page has 0 threads | Add post

Wise Owl guides and tutorials

FOR ... NEXT loops in VBA

Written by Andy Brown

In this tutorial

The Basic Syntax
A Simple Example
Alternative Syntax
An Example to Colour an Excel Worksheet
When to Use FOR ... NEXT

The Basic Syntax

The basic syntax of this type of loop is as follows:

'declare a variable to hold an integer

Dim i As Long

'loop a certain number of times

For i = 1 To 100

'do something within the loop

Debug.Print i

Next i

Note that it is not essential to put the name of the variable at the end of the loop:

'you don't need to suffix the NEXT statement with the

'name of the integer variable

Next i

However, it does help you see where a loop ends, and is particularly useful when you have one loop within another (see the example at the bottom of this page).

A Simple Example

Use this type of loop when you know exactly how many times you want to go through a loop. For example, suppose that want to give somebody 5 chances to type in their name - here's some code which would do this:

Sub GetName()

'the number of goes to give someone to type their name

Const NumberChances As Integer = 5

'number of each turn

Dim ThisGo As Integer

'the name typed in

Dim ThisName As String

'give someone up to N chances to enter name

For ThisGo = 1 To NumberChances

ThisName = InputBox("Type your name please")

If Len(ThisName) > 0 Then

'if this person typed in a name, store it in worksheet

Range("A1").Value = ThisName

MsgBox "Thank you!"

Exit For

End If

'if we get here, they didn't type a name - try again

Next ThisGo

End Sub

Here the variable ThisGo will take the values 1, 2, 3, 4 and 5. If the (rather stupid) user still hasn't typed anything into the input box which keeps appearing by then, the loop will end.

Notice the command Exit For to exit the loop prematurely if the user types in a value successfully.

Alternative Syntax

The example above gives the most useful form of the FOR ... NEXT loop, but you can change the start number, stop number and step number. For example:

Sub MoreComplicatedLooping()

Dim Pointless As Integer

'this will execute loop twice

For Pointless = 13 To 4 Step -5

Debug.Print "Pointless = " & Pointless

Next Pointless

End Sub

The code above will produce the following output:

The loop only executes twice, because on the third pass the value of the variable will be 3, which is below the stop value 4.

An Example to Colour an Excel Worksheet

A common requirement is to colour Excel worksheets with a pretty pattern (!):

A worksheet with alternate rows/columns coloured

Well, it gives us a chance to loop a given number of times!

The macro to get the above worksheet to appear could look like this:

Sub ColourWorksheet()

Dim RowNum As Integer

Dim ColNum As Integer

'clear any old format

Cells.ClearFormats

'for each alternate row ...

For RowNum = 1 To 10

'... for each alternate column within this ...

For ColNum = (RowNum Mod 2) + 1 To 10 Step 2

'... set the relevant cell's fill colour to be increasingly red - going down -

'and green - going across

Cells(RowNum, ColNum).Interior.Color = _

RGB(RowNum * 25, ColNum * 25, 0)

Next ColNum

Next RowNum

End Sub

Here:

the outer loop takes the row number from 1 to 10; while
the inner loop takes the column number from either 1 or 2 up to 20, producing the diagonal effect (the Mod function gives the remainder when you divide a number by - in this case - 2).

Notice the indentation above - without this, the code would be hard to read.

When to Use FOR ... NEXT

Not often, is the answer (although looping over arrays is an exception - see the example at the foot of this page).

For example, suppose that you wanted to print out the names of the worksheets in the current workbook. You could do this by looping N times (where N is the number of worksheets in the workbook):

Sub ListSheetNames()

Dim i As Integer

For i = 1 To Worksheets.Count

'for each worksheet, print its name to immediate window

Debug.Print Worksheets(i).Name

Next i

End Sub

However, the code above would be better by far if you looped over the built-in collection of worksheets (shown towards the end of this blog series):

Sub ListSheetNames()

Dim ws As Worksheet

'loop over all worksheets, printing name of each

For Each ws In Worksheets

Debug.Print ws.Name

Next ws

End Sub

Good VBA programmers rarely use the FOR ... NEXT loop structure, except when iterating over arrays. As an example of this, the following code reads text into array cells and then writes it back out again:

Sub WriteDwarves()

Dim BiblicalCharacters(2) As String

Dim bc As Integer

'put text into array elements

BiblicalCharacters(0) = "Shadrach"

BiblicalCharacters(1) = "Meshach"

BiblicalCharacters(2) = "Abednego"

'write it back out again!

For bc = 0 To UBound(BiblicalCharacters)

Debug.Print bc, BiblicalCharacters(bc)

Next bc

End Sub

This code would produce a list of the 3 characters shown in the debug window.

Previous
tutorial

Testing conditions

Previous
part

An introduction to loops in VBA

Top of page

Next
part

DO ... UNTIL Loops in VBA

Next
tutorial

Events

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on VBA

Loops

FOR ... NEXT loops in VBA

The Basic Syntax

A Simple Example

Alternative Syntax

An Example to Colour an Excel Worksheet

When to Use FOR ... NEXT

Head office

London

Manchester