Do until Loops in VBA are for repeating a task until some criteria is met. How this is used can vary widely depending on what the developers goals are for implementing the loop in the first place. In this post, we will look at an example of the use of a general do loop. By general do loop we mean a loop that has no predetermine number of times it will run.

Our goal is going to be to create a subroutine that ask the user for a number. This number will then be squared until it is greater than 2000. Once the number is larger than 2000, VBA will exit the loop and share the results with the user.

Below is a picture of how the Do loop is suppose to work.

As you can see the loop takes a number, squares it and then checks if the number is larger than 2000. If the number is larger than 2000 VBA exits the loop. If not, the new x value is the square of the old one and the process repeats.

Below is the code for completing this task in VBA

Sub squareLoop()
Dim x As Double, y As Double
x = InputBox("Enter your number")
Do
y=x*x
If y > 2000 Then Exit Do
x=y
Loop
MsgBox ("Your number is " & y)
End Sub

Here is what each line of code does.

1. Line 1 declares the subroutine called “squareLoop”
2. Line 2 we dim the two variables we need
3. Line 3 is the code for creating the input box for the user and saves this information as our x variable
4. Line 4 is the beginning of or do loop
5. Line 5 is were we square our x variable and name it the y variable
6. Next we have our if-then statement. If y is greater than 2000 than we exit the do loop
7. In line 7 if our y value is not greater than 2000 than we save it as the new x value and return to the beginning of the loop in line 4-5
8. Once we have a value greater than 2000 we skip to the bottom of the code and output a message box

The output is shown below

First Excel ask you for a number. For our example, we put the number 10.

Next, VBA squares the input number

10 * 10 is 100 which is not greater than 2000 so VBA goes through the loop again

The second time through are value is greater than 2000 so we get the message box with our value.

This is a relative simple process that has greater implications.

Conclusion

Do loops are another powerful tool you can implement in Excel VBA. The ability to automate repetitive task is valuable for saving time to deal with other matters.