The Do Until Loop in Excel VBA is utilized, when one has a set of statements or actions to be repeated and repetition occurs until the condition evaluates to true, in other words, while the condition is false the repetition occurs.
We can also evaluate more than one condition using the Or keyword in our Do Until Loop.
So, let’s see how to use the Do Until Loop in Excel VBA.
Table of Contents
Do Until Loop Example
To understand the Do Until Loop in Excel VBA first we will look at a simple example where we will be using a column in which the top 8 cells will give the number 50 with an interior color of cyan. To do this follow the below procedure.
- Press on the Visual Basic under the Developer option.
- Copy and paste the following code.
Sub loopexample() Dim i As Integer i = 1 Do Until i > 8 Cells(i, 1).Value = 50 Cells(i, 1).Interior.Color = vbCyan i = i + 1 Loop End Sub
- After running this code you will see the following result.
As you can see from the code is that we have to put value 50 with cyan color in column 1 where the number of rows is less than 8. It means it will check the condition, i > 8 for column 9 also and as the condition is not satisfied for row number 9 then nothing will happen in row number 9.
Using the Do Until Loop in Excel VBA
In our source example, a hypothetical research lab is evaluating different processes to reduce the levels of cyanide in the cassava root samples/cultivars obtained. The ultimate aim of the research lab is to make a scientific contribution to food security in the developing world, through cassava research and reduction of cyanide levels.
There are three teams in the lab, and each team has been given a different cyanide reduction process to optimize. Each of the processes contains ten steps, and after each purification/extraction/filtration step, the amount of cyanide remaining is measured. The maximum safe intake of HCN per kg of human body weight is 10 mg. We want to use the data from these trial experiments to see where in future to stop the experimental run.
The source data is shown below.
1) So, first things first go to the Visual Basic Editor (VBE) by going to Developer>Code>Visual Basic or by pressing Alt-F11 on your keyboard.
2) Once in the VBE, go to Insert>Module.
3) A new module is created.
4) Click on the module and type the following code.
Sub DoUntilLoopExample() Dim valueofInterest As Integer Range("B8").Select Do Until ActiveCell.Value <= 10 Or ActiveCell.Value = "" valueofInterest = ActiveCell.Value If valueofInterest > 10 Then ActiveCell.Offset(0, 1).Value = "Yes, Continue" End If ActiveCell.Offset(1, 0).Select Loop End Sub
5) We now want to add a button for our macro to our Quick Access Toolbar, in order for us to access it from there.
6) Click on the drop-down arrow on the Quick Access Toolbar and select More Command.
7) The Excel Options dialog box should appear, in the Choose Commands from: drop-down select Macros and you should see the macro you created, the name is drawn from the name used in the Sub Procedure.
8) In the Customize Quick Access Toolbar section, change the option from the default For all documents to the workbook or macro-enabled workbook at hand as shown below.
9) Then select the macro, we created called DoUntilLoopExample and press the Add button in order to add it to the Quick Access Toolbar for this specific macro-enabled workbook only.
10) With the macro selected, choose Modify…, now you can choose a button to represent your macro on the Quick Access Toolbar, in this case, we’ll choose a smiley face, and change the Display Name to Food Security Macro as shown below.
11) Click Ok.
12) Click Ok again. The button for our macro is now added to the Quick Access Toolbar.
13) Once the hypothetical data has been filled in, in terms of the mg of HCN left over after each step for each Team/process in Column B, go to the sheet called ProcessOne and click on the button for the macro on the Quick Access Toolbar in order to run the code. Since these are our trial experimental runs, we input all the data obtained, in column B.
By running the macro, we can see for Process One/Team 1 – the enzyme cocktail yielded what we wanted i.e 10 mg or less of HCN at Step Nine. Thus in the future, we could stop after Step 9, and do not need to continue beyond this point.
14) We can then go to the ProcessTwo sheet with the hypothetical data filled in, and then click on the button to run the macro from the Quick Access Toolbar.
By running the macro we can see, that the ProcessTwo sheet’s experimental runs did not yield 10 mg or below for the entire set of steps encompassed, in the experimental run. We would, therefore, have to optimize the alkali procedure and perhaps try different temperatures in conjunction with the same base, or different bases in order to optimize the experimental design in the future.
15) We now go to the ProcessThree sheet and click on the button to run the macro, once we have filled in all the data for the trial experiment.
We can see that after Step Five, we could stop running the experiment since the desired reduction was obtained. The above operation follows the Do While Loop in Excel VBA.
From our analysis, we can see that Process Three was the most effective. Using our macro, we were able to determine that we could stop the experimental run after Step Five in Process Three, whereas we only achieved the desired cyanide reduction after Step Nine with Process One, and Process Two did not yield the desired cyanide reduction for the entire experimental run with all the steps.
Download Working File
The Do Until Loop in Excel is utilized when one wants to repeat statements or actions, as long as a certain specified condition evaluates to false. The action is repeated until the condition evaluates to true.
Please feel free to tell us if you use the Do Until Loop in Excel. Leave a question below if you face any problems.
Review Section: Test your Understanding
1) When does one use a Do Until Loop in Excel?
2) Using a Do Until Loop, fill each cell in a range containing data, with a blue fill, up until one reaches a blank cell.