19.6 Do While and Do Until Loops
Next, let’s take a look at loops where we don’t know the number of iterations we might need. These will be the Do While and the Do Until loops. So, what’s the difference? A Do While executes the loop while a condition is TRUE. The Do Until loop executes the loop until a condition is TRUE. So, a slight difference there. Let’s start with Do While loops.
Do While Loops
A Do While loop is a loop that executes while a specified condition is met. There are two different ways it can be written. You can check the condition before you execute the loop so that it is checked even before the loop is ever executed. If the condition or expression is TRUE, then you execute the code in the loop. Alternatively, you can run the loop first and then check the condition after. If the condition is TRUE, you will execute the code in the loop again. So, the main difference is when you check the condition. Here are the two different approaches.
'Approach 1
Do While [condition]
[instructions]
Loop
'Approach 2
Do
[instructions]
Loop While [condition]
You do need to be a little bit careful with both the Do While and Do Until loops to not get into an endless loop—that is, don’t forget to change the conditions such that you can actually get out of the loop at some point in time. If you don’t change the conditions, you’ll get stuck in an endless loop that will take processing power, and your machine will become unresponsive. Sometimes this is referred to as an infinite loop!
Our example function takes a column value and returns the row of the largest value in that column. Here is the code:
'Do While Loops
Function RowofLargest(Col)
Dim MaxVal, Row
MaxVal = WorksheetFunction.Max(Columns(Col))
Do
Row = Row + 1
Loop While Cells(Row, Col) <> MaxVal
RowofLargest = Row
End Function
Let’s walk through the code. We pass the column value we are interested in as the column letter (e.g., L). A couple of variables are declared as variants. MaxVal gets assigned the largest value found in a given column. Notice that we use WorksheetFunction.Max to find the MaxVal in conjunction with telling it which column to look at. In our example, MaxVal will be assigned a value of 105, which is found in L26.
Next, we enter our loop. Initially, Row will have a value of 0, so on the first iteration, it will be assigned 1. Then we check the condition to see if the value of Cells(1, L) equals the MaxVal, 105. In this case, there isn’t anything in L1, so those values are not equal to each other. Since the condition is TRUE, we enter the loop another time where the value of Row is incremented by one. We then compare the value in Cells(2, L) which is not equal to MaxVal. We continue this pattern until we get to row 26, where Cells(26, L) returns a value of 105, which matches the MaxValue. Since these values are not different from each other, our expression returns a FALSE, and the loop is complete. The code continues execution on the line after the Loop keyword, where the current value of Row, 26, gets returned from the function. This is a decent example of not knowing exactly how many times we might want to iterate through a loop.
Let’s highlight one deficiency of the codes as it stands. Try putting the value 500 in cell L23. Hit Return. Did it update the high value? Sometimes when we write code, if our function refers to cells that weren’t passed to it, VBA doesn’t know that it should rerun anytime a value on the sheet changes. To signal to VBA that it should rerun anytime anything changes, we need to add the following line of code to our function.
Application.Volatile
Now, you can’t just add it to the code and expect it to work; the function has to execute at least once for it to be tagged as a function that should recalculate with any change. So, you will need to edit or retype the function in a cell once. After that, try changing another value in column L to the highest value and see if it correctly identifies the row.
'Do While Loops
Function RowofLargest(Col)
Dim MaxVal, Row
'Application.Volatile 'when any cell's value on sheet changes it recalculates
MaxVal = WorksheetFunction.Max(Columns(Col))
Do
Row = Row + 1
Loop While Cells(Row, Col) <> MaxVal
RowofLargest = Row
End Function
As a side note, if you want to exit a loop prematurely, you can use the keywords Exit Do, and it will jump to the line of code after the Loop keyword.
Do Until Loops
A Do Until loop is very similar to a Do While loop. The main difference is that a Do While loop executes while the condition is TRUE and a Do Until loop executes until the condition is TRUE. Like a Do While loop, you can check the condition either before you enter the loop or after the loop has been executed.
'Approach 1
Do Until [condition]
[instructions]
Loop
'Approach 2
Do
[instructions]
Loop [Until Condition]
As you can see below, our example is very similar to the previous scenario. We will just change the condition the value in the cell matches. We’ll do this until we find the largest value rather than continue if the current value is not equal to the largest.
'Do Until Loops
Function RowofLargest1(c)
Dim MaxVal, r
Application.Volatile
MaxVal = WorksheetFunction.Max(Columns(c))
Do
r = r + 1
Loop Until Cells(r, c) = MaxVal
RowofLargest1 = r
End Function
We pass the letter of the column the data is in and store that in variable c. Most of the logic will remain the same. You can see that we still increment the row we are looking at inside the loop. It’s when we get to the end that we look for whether or not the current row is equal to MaxVal. If it’s not, then it will continue to increment the row until the value in the current cell is equal to MaxVal.
You can always use a Do While instead of a Do Until or a Do Until instead of a Do While—it just depends on how your condition is written. Take a little bit of time and make sure you’re solid on the differences. Do While executes the instructions while the condition is TRUE. Do Until executes the instructions until the condition is met.