This post may contain affiliate links. Please read my disclosure for more info.

2.2 Loops (part 1)

Beginning VBA

Beginning VBA

Previous Part: 2.1 Using Variables


What are loops?

Loops are one of the most powerful features in any programming language.  A loop will perform a certain action over and over again.  Each time the action occurs something in the loop changes, so that the action varies slightly each time.

If we wanted to bold the text in Cell A1 on the first 10 worksheets we could:

  • Write the code 10 times, each time referencing a different worksheet.
  • Write the code once and loop it 10 times.  Each time it loops we change the worksheet referenced

Writing it once sounds much easier doesn’t it?  That is the power of loops.



The basic For loop

There are different types of loops, all of which will be covered in future posts.  As an introduction we will consider a loop called the For loop.

Sub firstForLoop()

'Create a variable which will count each time the loop runs
Dim counter As Integer

'The counter will change each time the loop runs
For counter = 1 to 10
    'Display a message box with the counter value
    MsgBox counter
Next counter

End Sub

If you try the example code above, it will display message boxes counting from 1 up to 10.

This code starts by creating a variable called counter which will hold whole numbers.

Dim counter As Integer

The For loop will run 10 times. The first time, counter will have a value of 1, the next time counter will have a value of 2, then a value of 3, etc, all the way up to 10.

For counter = 1 to 10

The action being performed in this example will just output the value of counter to a message box.

MsgBox counter


The loop is closed with the following code:

Next counter

Once counter equals 10, the loop will stop running.



When using a For loop there are some aspects to be aware of:

The For loop only works with numbers which have a consistent interval, for example where the number increases in 2’s

'Increment the For loop in 2's
For counter = 2 to 10 Step 2


The For loop can work backward, for example working from 10 down to 1

'Move through the For loop backwards
For counter = 10 to 1 Step -1

It is possible to have loops which exist within another loop.  Each For loop must have it’s own variable to step through.

'Create 2 variables, one for each loop
Dim counter As Integer
Dim subCounter As Integer

'Start the first For loop
For counter = 1 to 10

    'Start the second For loop
    For subCounter = 1 to 30 
        MsgBox counter + subCounter
    Next subCounter

Next counter

For loops are useful where the action being performed can be referenced by a number.  For example Sheets(1) is the first worksheet in the workbook, Sheets(2) is the second worksheet, therefore the For loop is a good option in this scenario.


Previous Part: 2.1 Using variables

Next Part: 2.3 Object variables