For Loops

Like Do While and Do Until loops, For loops are used to repeat a block of code until (or while) a condition is met.  The syntax is:

For variable = Start To End
    Code to be repeated
Next

A For loop can also be used with the Step statement to alter the increment used.  For example, the code below increments the variable x from 0 to 10 in steps of 2, i.e. x = 0, 2, 4, 6, 8, 10.  Without the Step statement, the code would set x to 0, 1, 2, 3, ... 10.

For x = 0 To 10 Step 2
    Code to be repeated
Next

Note that the Step statement can also be used to run through a For loop "backwards":

For x = 10 To 0 Step -2

    Code to be repeated
Next

The Exit For statement can be used to exit a For loop before the end condition is met.  For example:

For x = 0 To 10 Step 2

    If intNum = 25 Then Exit For

Next

Do Until Loops

Just like a Do While loop, a Do Until loop repeats a sequence of code until a certain condition is met. The syntax for this type of loop is the following:

Do Until condition
    'Code to be executed
Loop

Again like a Do While loop, Excel can check the condition either before entering the loop (as above) or after Excel has run through the code once, as shown below:

Do
    'Code to be executed
Loop Until condition

You can use the Exit Do statement to force Excel to exit the loop when desired (instead of waiting for the specified loop condition to be met).

We can use the previous tutorial (the spreadsheet with the list of students and grades) as an example. The previous loop we used (Do While Range("C" & i).Value <> "") can be replaced with a Do Until loop instead:

Do Until Range("C" & i).Value = ""

Your code should now look like this:


Note that the not equal sign (<>) must now be changed to an equals (=) sign. Clicking on the "Generate grade" button has the same effect as before, assigning the appropriate letter grade for each student based on their percentage score.

Do While Loops

A loop is used to repeatedly run through lines of code until or while a certain condition is met, usually for as long as a variable is equal to a specific value. Loops in VBA can take a number of different forms; this post will deal with Do While loops. The syntax for a Do While loop is the following:

Do While variable = value
    'Code to loop through
Loop

An example of this could be:

Do While strStudentName = "Dan"
    'Code to be looped through
Loop

Note that the = sign above can also be replaced with another condition, such as greater than (>), less than (<) or not equal to (<>). In the above cases, Excel will check the condition (variable = value) before entering the loop; it is also possible to have Excel check the condition after entering the loop, by typing the condition at the end:

Do
    'Code to be looped through
Loop While variable = value

Another useful tip can be to exit a Do While loop in the middle of the loop. This is done using Exit Do. Used with an If statement, you can have VBA exit the loop when a different variable (not used after the Do or While statement) becomes equal (or not equal) to a value.


Do While Tutorial

To practice using a Do While loop, open up the workbook we created in the last tutorial. Delete the letter grades in the "Grade" column and add a few more names and grades (percent values). Your worksheet should look something like this:

Now edit the "Generate grade" button code (note: a quick shortcut for accessing VBA code in a workbook is to press Alt+F11).

We're going to add a loop to go through the students one at a time, adding the letter grade corresponding to their percentage grade. In order to do so, we need to create a variable that increments by 1 each time we go through the loop and that corresponds to the row the student is entered in. We'll call this variable i, and it is an integer type. Declare it by adding it to the first line existing in the code; it should now read Dim intGrade, i As Integer.

We can then delete the last two "blocks" of code (where we referred to cells C4 and C5). Then, in the remaining block of code (which should refer to row 3, i.e. cells C3 and D3), replace any instances of 3 with i. For example, the line that used to read intGrade = Range("C3").Value should now read intGrade = Range("C" & i).Value. The If statement should also now use this format (i.e. wherever "D3" was entered before, replace it with "D" & i). Now create the initial value for i - Dan is the first student, in row 3, so at the very beginning (right after declaring the variables), we need to set i equal to 3, using the line i = 3. Finally, add in the Do While loop and a line to increment the value of i with each loop. This should look like this:

Do While Range("C" & i).Value <> ""
    intGrade = Range("C" & i).Value
    'If statement
    i = i + 1
Loop

This means that the code will loop through the If statement for as long as there is a student grade entered in column C, row i. You're finished code should look like this:


When you run your code, you should now get a letter grade for all of the students in the list - regardless of the number of students you have in the list! Previously our code would only work for 3 students, but now you can add 10, 20, 30 or 473 students and the macro will determine the letter grade for all students.