1.8 Resize
Home and Learn - Free Excel VBA Course
5.1 Excel VBA Programming Loops
Consider the following code:
Dim MyNumber As Integer
MyNumber = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10
All the code does is to add up the numbers 1 to 10. The result is then stored in the variable MyNumber.
And that's nice and easy. But suppose you want to add up the numbers 1 to a 1000. You wouldn't have to type them all out, surely? Thankfully, you don't have to, as there's a handy programming tool at your disposal - the loop.
A loop is something that goes round and round. Programming loops go round and round until you tell then to stop. You set a starting number for your loop, an end condition, and a way to get from the starting number to the end condition. In VBA, there are four types of loop to choose from: For loops, For Each loop, Do Loops, and While loops. The first loop we'll look at is the For Loop.
For Loops
VBA is a sequential programming language. What this means is that each line of code gets executed from top to bottom until there are no more lines of code to read. If you want to go back up then you have to force the programme to do so. You do the forcing with a loop. The reason why you'd want to force the programme to go back up is to execute a line or lines of code repeatedly.
The most common type of loop is called a For Loop. Don't worry about the name of the loop. Just bear in mind that a For Loop goes round and round until it meets an end condition. Once the end condition is met then the programming flow will continue downward, in its natural direction.
All this may be very confusing, so let's clear things up with an example. Create a new, blank Excel Workbook for this. Save it with the name loops.xlsm. Now click the Developer ribbon at the top of Excel, and click View Code on the Controls panel. In the Sheets1 coding window, type the following:
Sub LoopExample()
Dim StartNumber As Integer
Dim EndNumber As Integer
EndNumber = 5
For StartNumber = 1 To EndNumber
MsgBox StartNumber
Next StartNumber
End Sub
The first two lines just set up two Integer variable, one called StartNumber and one called EndNumber. We've stored a value of 5 in the EndNumber variable. Then comes the first line of the loop:
For StartNumber = 1 To EndNumber
You start with the type a loop you want, which is a For loop in this case. Next, you need a start point for your loop. We want to start the loop at the number 1. Notice that we now store a value 1 in the StartNumber variable:
For StartNumber = 1
We can't do it like this below as we'd get an error:
StartNumber = 1
EndNumber = 5
For StartNumber To EndNumber
VBA needs you to assign a value to a variable as the starting point for you loop. This value can be any number you like. We've started at 1.
The next thing you need is the word To. This just mean, "Your starting number TO which end number?".
After the word To, you need an end number or condition. We've set up an end number of 5. So our loop goes from 1 to 5.
After the end condition, you can add an optional Step value. The default is for VBA to go from your start number to the end number in steps of 1 each time round the loop. If you wanted to go in steps of any other number, say 2, you'd add the following:
For StartNumber = 1 To EndNumber Step 2
You can add a negative value, here, if you want:
For StartNumber = 10 To EndNumber Step -1
Now the loop starts at 10, and end with 5. To get from 10 to 5 we need to go down. The line above is going from 10 to 5 in steps of -1 each time round the loop.
To recap, then, the first line of your loop is where you set a start number and end number. This will tell VBA how many times you want to go round and round.
The next line of our loop is this:
MsgBox StartNumber
This is just a simple message box that displays the value of the StartNumber. We'll come back to this.
The final line of a For loop is the word Next followed by the variable name you typed after the word For on the first line. For us, this was StartNumber. What this tells VBA to do, though, is to add 1 to whatever number is in your variable.
We began with a value of 1 in our StartNumber variable. The first time round the loop, VBA sees the words Next StartNumber and adds 1 to the variable. The programming flow is then forced back up to the For line, ready to repeat the whole process.
In between the For line and the Next line is where you type the code for your loop. Our code is just a message box that display whatever is in the StartNumber variable.
Run your code and you'll see the message box appear 5 times. Each time it appears, the number in the message box changes by 1.
OK, let's do something a bit more practical than displaying a message box over and over again. Change your code to this, (the new lines are in bold):
Dim StartNumber As Integer
Dim EndNumber As Integer
Dim answer As Integer
EndNumber = 5
For StartNumber = 1 To EndNumber
answer = answer + StartNumber
Next StartNumber
MsgBox answer
The whole of your code should look like this:
We've added a new Integer variable called answer. The message box has been moved to the end. Between the For line and the Next line we have some new code. This:
answer = answer + StartNumber
To understand this line, start after the equal sign:
answer + StartNumber
This says, "Add together whatever is stored in the variable called answer and whatever is stored in the variable called StartNumber. However, we haven't stored anything in the answer variable yet. So what value does it hold? If you don't store a value in an Integer variable then it gets set to 0. The StartNumber variable is 1 the first time round the loop. So the sum on the right of the equal sign is really this:
0 + 1
When VBA has finished calculating this it needs to store the result somewhere. That "somewhere" is whatever you have to the left of the equal sign. We have the variable called answer to the left of the equal sign. So this is where VBA stores the result of 0 + 1. In other words, the answer variable will be overwritten with the new value.
The next time round the loop the two variables to the right of the equal sign will hold the following values:
1 + 2
The third time round the loop the two variables to the right of the equal sign will be this:
3 + 3
The fourth time round it will be:
6 + 4
And the fifth time round, the two variables will be:
10 + 5
Here's a table of those values: (Notice the right-hand column in our table below, for StartNumber. This shows that the values in StartNumber increase by 1 each time round the loop.)
But by going round the loop 5 times, we've added up the numbers from 1 to 5. This gives a value of 15. Run your programme and test it out. The message box should display an answer of 15.
Now move the message box from the end of the code to inside the loop, just before Next StartNumber:
For StartNumber = 1 To EndNumber
answer = answer + StartNumber
MsgBox answer
Next StartNumber
Now run the code again. The message box displays 5 times, once for each time round the loop. This time, the values will be the same as from our table above, from the left-hand column under answer =.
If you've never done any programming before then know that loops are one of the harder concepts to get the hang of. So don't despair of you don't understand them straightaway. Just go over the material above and it will sink in. Eventually!
In the next lesson, you'll learn about another type of for loop - the For Each loop.
Next Lesson: 5.2 For Each >