Looping in Visual Basic for Applications macros
Part two of a four-part series of blogs

There are 4 types of loop in Visual Basic for Applications - this blog shows how and when to use each type of VBA loop structure.

  1. Looping in VBA Macros - an introduction to the 4 types of loop
  2. Looping a set number of times or iterations (FOR … NEXT) (this blog)
  3. Looping with conditions (DO UNTIL / WHILE ... LOOP)
  4. Looping over collections (FOR EACH ... NEXT)

This series of blogs is part of our Excel VBA online tutorial.  We also run training courses in Excel and VBA.

Posted by Andy Brown on 15 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Looping N times (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:

Debug output from macro

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.

 

This blog has 0 threads Add post