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.