Do Until Loop in Excel VBA with Examples
The Do Until Loop Structure 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 Structure in our VBA code.
Table of Contents
Using the Do Until Loop
Cassava is a shrub-like vegetable. Its roots are a major source of carbohydrates and it is consumed in the developing world as a major staple food. While the cassava root contains important nutritional compounds, such as starch and trace amounts of Phosphorous and Calcium, raw bitter cassava can also be a source of cyanide. Raw bitter cassava contains cyanogenic glycosides, which are capable of releasing cyanide into the human body when consumed. This is a cause for concern when large amounts of cassava, is eaten or if it is not prepared properly.
Some of the preparation methods involve extensive soaking, dewatering, ensiling and drying in order to reduce the levels of potentially toxic cyanide.
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.
Dim valueofInterest As Integer
Do Until ActiveCell.Value <= 10 Or ActiveCell.Value = “”
valueofInterest = ActiveCell.Value
If valueofInterest > 10 Then
ActiveCell.Offset(0, 1).Value = “Yes, Continue”
The first thing we are doing with this code is declaring a variable of the integer data type. We then select cell B8 in the worksheet. Cell B8 is the first cell in the data entry column. This column records the amount of cyanide remaining in the cultivar after the Step at hand.
We then open the Loop Structure and state that until the Active cell value is 10 or less or the Active cell value is blank, the statements in the loop should be repeated. In other words, the statements in the code should be executed as long as the conditions are false, once they are true, the statements stop being executed.
In addition, we are also using two conditions in our Do Until Loop. The first condition is about the actual experiment at hand, we are saying that the statements in the loop should be repeated until the ActiveCell value is 10 or less. The other condition states that if the ActiveCell value is blank, we should stop repeating the statements. The second condition stops us from creating an infinite loop.
In terms of our source example, if the valueofInterest, i.e the variable that is storing the amount of cyanide content is greater than 10 mg, then the experiment must continue and all the steps involved in the experimental design must continue. If the valueofInterest i.e the HCN content is 10 mg or less, the experiment must stop, since then we have reached our ideal HCN content. The code then specifies that in the cell next to the ActiveCell, the value of Yes, Continue is input, in order to indicate that we should proceed with the other experimental steps in future if the content is greater than 10 mg.
In this way, we are setting up a loop to indicate how far along to proceed with the experiment (also when to stop), thus contributing to a more effective experimental design and overall cost efficiency. Since lab chemicals and processes can be very expensive and adding additional steps to experimental design, when not necessary, decreases the economic feasibility of the project. Also, the addition of unnecessary steps increases ultimately, the potential rejection of an application to turn a lab project into a pilot scale project.
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 Commands…
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 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 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.
Our Results in Context: When evaluating the trials, Process One (the enzymatic cocktail) method of reducing HCN content in the cassava cultivar, Process Two (the alkali) method of reducing HCN content in the cassava cultivar and Process Three (the acidic) method of reducing HCN content in the cassava cultivar, 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.
We could also add more sheets, detailing the results from other processes such as nanotechnological processes or processes using supercritical fluid cocktails. We could also add results from other labs using other or the exact same processes, in our workbook, for comparative analysis. However, we have to ensure that the data is laid out and entered exactly in the same way as the other worksheets. In other words a three column range, and cell B8 being the point of data entry, since cell B8 is specified in the macro as well.
And there you have it.
Download Working File
The Do Until Loop Structure 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 Structure in your VBA code and your thoughts about cassava as a food source.
Review Section: Test your Understanding
1) When does one use a Do Until Loop?
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.