Learn VBA : Loops

by

Many times in our scripts we will need to repeat something a certain amount of times. To help us do this we have the a few statements that we can use. In this article you will learn how to use the “For … Next” , “For Each” and the “Do While”.

As mentioned there are a few ways that we can repeat or loop things. The first we are going to look at is the “For … Next” statement and it can look something like this,

Dim iCount
For iCount = 1 to 10
	debug.log(iCount)
Next

In the above example we have to declare a variable iCount which will hold a number. This number is going to be incremented by one , then printed to the output window then incremented by one again and then printed to the output window. This will continue until the value of iCount is greater than 10. So the final output will be “1,2,3,4,5,6,7,8,9,10” .In the above example the increment is 1 , but it is possible to change that for example ,

Dim iCount
For iCount = 1 to 10 Step 2
	debug.log(iCount)
Next

Would add two to iCount each time so you would end up with “1,3,5,7,9” in the output window. As well as adding a number , we can take away a number , so count backwards, this is an example of counting backwards.

Dim iCount
For iCount = 10 to 1 Step -2
	debug.log(iCount)
Next

In this example we will see “10,8,6,4,2” appear in the output window. Next we will look at the “For Each” loop. It is similar to the “For Next” but we are not required to specify numbers to loop. What this loop does it to loop collections of things, for example , the categories in a question. Let’s take a look at this example.

Dim oCat
For Each oCat in Gender.Categories
	debug.log(oCat.Name)
Next

In this example we can see that we are looping the categories in the Gender question one at a time and as we do this the variable oCat is taking on the properties of the current category. So first off oCat would take on the values of the Male category ( if that was first in the list ) and then it would take on the values of the Female category. So our output would look something like this “Male,Female”.

In the examples so far we have see loops that use a known end. For example in the “For Next” example we know that the loop will end when it gets to a certain number, but what loop should we use if we don’t know the end ? Well we can use the Do While loop. Here is a common example that we use when connecting to databases to loop records in a recordset.

Do while oRecordset.EOF = false and oRecordset.BOF = false
	debug.log(respondent.serial)
	oRecordset.MoveNext()
Loop

In the above example we do not know the number of records to be returned so we just loop around the until we get to the end of the set. You can imagine the recordset if you think about a ladder. You start at the bottom of the ladder ( the beging of the recordset ) and then you climb up the ladder a rung at a time ( the MoveNext ) and then finally you get to the top of the ladder ( the end of the recordset ). When you are at the end of the recordset the condition will force the loop to end.

1 thought on “Learn VBA : Loops”

  1. It’s important to note that in the case of Categories and other collections, the For Each loop will return items based on the .Order. For example, if you have a randomized list a,b,c, then the For Each might execute c,a,b and NOT necessarily a,b,c.

    Also, there are a number of other loops not mentioned – While .. End While, Do Until … Loop, Do … While Loop and Do … Until Loop. Admitedly, you really only need the Do While, but others are at times more convenient/readable.

Leave a Comment