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 variableMyNumber.
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 aFor 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 clickView Code on theControls 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 calledStartNumber and one
calledEndNumber. We've stored a value of 5 in theEndNumber variable. Then comes the first line of the loop:
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 theStartNumber 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 wordTo. 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 optionalStep 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 theStartNumber. We'll come back to this.
The final line of a For loop is the wordNext followed by the variable name you typed after the wordFor 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 wordsNext StartNumber and adds 1 to the variable. The programming flow is then forced back up to theFor line, ready to repeat the whole process.
In between theFor 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 calledanswer. The message box has been moved to the end. Between theFor line and theNext 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 calledanswer and whatever is stored in the variable calledStartNumber. 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. TheStartNumber 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, theanswer 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 beforeNext 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 underanswer =.
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!