Loops: Looping under Conditions with Do While/Until Loops

Quick reference

• 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.