Menu

Looping (For...Loop)

User defined functions

Event handling

Other links

Example code snippets

Userform input example

Looping
(For...Counter/Each...Loop)

This is one of the 'control flows' VBA provides which is required to make your VBA code procedures more flexible, reduce the amount of code required and make the system think for itself!

Without any of these 'control flows', your code is very linear and rigid which never is really suitable when trying to mimic 'real world' processes.

The four 'control flows' are:

1. If...Then...Else...End If

2. Select Case...End Select

3. Do...Until/While...Loop

4. For...Counter/Each...Next

There two variations of this type of loop both which are controlled counting loops which is neither driven by the user or the system.

There are:

1. For...Counter...Loop

2. For...Each...Loop

Before any of the above loops are called, we know how many times the system will repeat the code inside the block as opposed to a conditional type loop (Do...Until/While...Loop).

For...Counter...Loop

This type of loop is defined by the user telling the system how many times to repeat or the system using an object method or function to identify the number of iterations.

Structure (syntax):

For Counter = Start To End [Step N]

Code executed here...

....

[Exit For]

Next [Counter]

The Counter is a variable keeping count of the current number using the Start and End as its range. As soon as the Counter = End then the loop is finished and the code jumps out of the block.

Example:

The following example will display an input box requesting the sales figure for Month 1, Month 2 etc and input the results into the relevant cell on the spreadsheet. The procedure will therefore loop 12 times.

Sub MonthsForLoop()
    Dim MonthlySales As String
    Dim num As Integer

    For num = 1 To 12
        MonthlySales = InputBox("Enter sales for Month " & num)
        ActiveCell.Value = MonthlySales
        ActiveCell.Offset(1, 0).Select
    Next num

End Sub

The InputBox Function is not a practical solution for the above example but it displays how the For...Loop works.

The Counter variable at the end of the loop (after the Next keyword) is optional and can be left out but personally it makes it very clear to what is incremented (in the example by 1).

The Step argument is optional too and by default means the variable (Counter) will increment by 1. If your want to change the increment or use the decrement action (downward count) then you need to add the Step keyword with the value you wish to increment or decrement.

For example:

'Positive increment of 10
Sub ForLoopStepPositive()
    Dim counter As Integer
    For counter = 10 To 100 Step 10
        MsgBox counter
    Next counter
End Sub

'Negative decrement of 10
Sub ForLoopStepPositive()
    Dim counter As Integer
    For counter = 100 To 10 Step -10
        MsgBox counter
    Next counter
End Sub

You will also need to make sure the range Start To End is synchronised with the direction of the Step value otherwise it will cause an error.

For...Each...Loop

This type of loop is a self-counting loop based on a array variable (which use an index), Collections (which is Excel array to their objects) or by an object member method (like the Count method).

This is commonly used with Collections and therefore the number of times a loop occurs is driven by the current collection array.

Structure (syntax):

For Each Element In Group

Code executed here...

....

[Exit For]

Next [Element]

A typical example could be to loop through the current number of Worksheets in the ActiveWorkbook:

'Loops through each worksheet in the ActiveWorkbook
Sub HowManySheets()
    Dim item As Worksheet
    For Each item In ActiveWorkbook.Worksheets
        MsgBox item.Name
    Next item
End Sub

If there are 5 worksheets in the ActiveWorkbook, the procedure would loop 5 times.

It uses the ActiveWorkbook.Worksheets Collection to determine the number if elements (note the word is plural) in the group. The Item variable is its element which needs to be the singular matching object which is in this case Worksheet (singular keyword).

Another example could be to close all workbooks in Excel:

'Loops through each workbook in open in Excel and closes it except the 'current workbook (which contains this piece of code!
Sub closebooks()
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            wb.Close
        End If
    Next wb
End Sub

The If statement is used to test if the workbook collections current element is the current workbook (containing the above code) as we do not want to close it.

Exit For

Exit For keywords are included in a block should you wish to terminate a loop block early without having wait to the end of iteration period.

It can speed up your procedure if there are various tests in a loop that may unexpectedly change state and act as a catch (error handler of some kind).

It is commonly found with If blocks nested in a loop of this kind.

Next Topic: With...End With blocks

Want to teach yourself Access? Free online guide at About Access Databases

Tip: Use an Exit For to terminate a block early and speed up your procedures.

To break a loop during running your code use CTRL + PAUSE/BREAK.

Save your work before running a looping piece of code!

VBA Keywords: ActiveCell, Offset, MsgBox, ActiveWorkbook, Worksheets, WorkBooks, InputBox, Close and Exit For.

Looping (For...Counter/Each...Loop)

For...Counter...Loop

For...Each...Loop

Exit For

Looping
(For...Counter/Each...Loop)