Locked lesson.
About this lesson
More advanced looping by looping while or until a certain condition is met.
Exercise files
Download this lesson’s exercise file.
Loops: Looping under conditions with Do While/Until loops.xlsm23.5 KB
Quick reference
Loops: Looping under conditions with Do While/Until loops
Looping until or while certain conditions are met
When to use
Running a loop until or while certain conditions are met. Safer than a basic Do Loop, as it forces you to think about the test, and leaves less chance of a runaway loop.
Instructions
Syntax: Do Loop Until
Do
<Action>
Loop Until <Condition>
Syntax: Do Until Loop
Do Until <Condition>
<Action>
Loop
Syntax: Do Loop While
Do
<Action>
Loop While <Condition>
Syntax: Do While Loop
Do While <Condition>
<Action>
Loop
Hints & tips
- The Do Loop Until and Do Loop While structures will always execute the loop at least once because they test the condition at the end of the loop.
- The Do Until and Do While loops may not execute the loop at all, depending if the condition is met upon execution.
- 00:04 In our previous module, we were looking at Do Loops.
- 00:08 Loops that could repeat indefinitely if we didn't put some kind of test in the middle
- 00:12 to make sure that they weren't gonna keep going.
- 00:14 There's a key, couple of keywords actually that you can use with your do loops in
- 00:18 order to ensure that they actually have some boundaries and won't run forever.
- 00:22 Those two keywords are Until and While.
- 00:26 And what these do is that they will loop until a specific condition has been met.
- 00:30 So, I'm gonna take a look at this particular loop.
- 00:33 And these are just for
- 00:34 academic purposes to show you how these loops actually will run.
- 00:37 The first part that I wanna look at is the Do Loop Until, okay?
- 00:41 So they're kind of a tongue twister.
- 00:42 We got Do Loop Until, and we've got Do Until Loop, and Do Loop While, and
- 00:46 Do While Loop, okay?
- 00:47 So it just depends on where the keyword actually is.
- 00:51 So the first one we have,
- 00:53 you can see we dimensioned our variable as long, we set the count to 1.
- 00:56 And then we step into the Do statement.
- 00:59 The different between this Do statement and
- 01:01 the last one is we still increment the counter, we add 1 to it.
- 01:05 But this time, instead of just saying Loop,
- 01:07 we actually loop until the count is greater than or equal to 1.
- 01:11 So, the big key here is that this is our full control.
- 01:15 Once this actually happens, it will automatically step out of the loop and
- 01:18 not execute it again.
- 01:19 So, we don't end up with a runaway loop like we had with just the raw Do Loop,
- 01:24 where we had to actually check the status and each iteration.
- 01:29 So let's go and step through this, we'll see how this works.
- 01:31 The big key part that I want you to recognize here is that we go through,
- 01:34 we set the variable, we hit the Do.
- 01:36 We step in and we execute the first line of the loop.
- 01:40 At this point our value is 1, we added the 1, so our value is now 2.
- 01:44 And therefore, when it hits this loop Until 1Count >= 1, it says, well,
- 01:47 yeah it's a 2.
- 01:48 It's obviously greater or equal than, so I'm gonna step out and
- 01:51 I'm gonna feed back the bottom to show you how far it counted.
- 01:55 The big key here, a Do Loop Until
- 01:59 will always execute the contents of the loop at least once, why?
- 02:04 Because it hits the Do, it goes straight into the next row.
- 02:08 It actually starts working through all the macro code that you've got in here and
- 02:12 then it tests the condition at the end, okay?
- 02:16 Now, I'm going to show you an alternate version of this and
- 02:18 this is the Do Until Loop.
- 02:21 So basically there's not much difference to this except that the Until lCount being
- 02:27 greater than or equal to 1 is no longer on the loop line like it was before,
- 02:31 it's been moved up to the Do line.
- 02:33 What that means is when we actually run through this you'll notice that lCount is
- 02:37 set to 1, it now says, Do Until the lCount is greater than or equal to 1.
- 02:42 Well, lCount's already 1.
- 02:44 So what it does, it says, well, it's already equal to 1.
- 02:47 I don't need to execute the loop, I can step over and
- 02:50 tell you exactly how far it counted.
- 02:51 So big difference, the Do Loop Until will always execute at least once.
- 02:57 The Do Until Loop may or may not, it depends on what
- 03:01 the conditions are that you're actually testing at the beginning here.
- 03:04 Okay, so that's the big difference between those two.
- 03:07 Now we have an alternate keyword that goes with this, much like the Until this is
- 03:12 actually While, so we can do a Do and then loop While.
- 03:16 So in this case here we're gonna loop While the count is less than 3.
- 03:20 So what we'll do is we'll come in, we set the counter to one,
- 03:22 we say Do, increase it, it will now be 2 as we can see in the locals window.
- 03:27 Loop While, the lCount is less than 3, it is less than 3, so we will loop.
- 03:32 That will move it to 3, is it less than 3?
- 03:34 Not anymore, so
- 03:35 now it will step out of the loop and feedback that it counted to 3.
- 03:40 We have another version of the same thing, much like the Do Until,
- 03:45 we can actually have a Do While as well.
- 03:48 So again, this will just change how often or how many times the individual
- 03:53 components are executed depending on what actually happens here.
- 03:56 So In this case, we've got for 1, we've added 1, that gives us 2.
- 03:59 We'll check, it's less than 3 still, we've added 1 more, we'll loop again.
- 04:03 It goes back to the top and it says Loop while it's less than 3, so therefore it's
- 04:08 not anymore, we are at 3, so I'm going to step over the rest of the loop here.
- 04:12 Go right to the debug and print out that particular piece, okay?
- 04:16 So this is how these four variance work of this, what can you use them for?
- 04:20 That's an interesting question, what about renaming a worksheet.
- 04:23 Let's say that right now, every one of our worksheets is listed as sheet 1, sheet 2,
- 04:28 sheet 3.
- 04:28 As a matter of fact, we can see that right here, those names are in brackets.
- 04:31 These are the sheet code names.
- 04:33 Here's the actual worksheet names that we see in our workbook.
- 04:36 We could actually do this, where we set up a preface for a report.
- 04:39 Instead of saying sheet, we're gonna call it report space.
- 04:42 I can do a loop here.
- 04:44 Where I count, I will increment my counter right now.
- 04:47 It has not been set to 1 here it's at 0, so 0 we're gonna increase by 1 and
- 04:51 then we're gonna say worksheets.
- 04:54 We're gonna use the index lCount,
- 04:55 we're gonna change the name to s preface and lCount, then we're going to loop
- 04:59 until the lCount equals the count of the worksheets in the workbook.
- 05:03 Active worksbook.worksheets.count.
- 05:06 So, you'll be able to see here,
- 05:08 as we go through the preface is going to be reports.
- 05:10 We're gonna do it,
- 05:11 we're gonna increase the counter by 1 because we need to have that first.
- 05:15 We're gonna change the name and
- 05:16 you can see right away, it actually changes it in project explorer if
- 05:19 I went back to the Excel workbook you'd see that that's already been changed.
- 05:23 And now I can loop it Until, and
- 05:24 I'm just going to run it at this point Until it does all of the worksheets
- 05:28 because it's counting up to the three worksheets that it actually has.
- 05:30 So that can be a practical use for one of the Do looping structures.
Lesson notes are only available for subscribers.