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.
The loop is closed with the following code:
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