Loops: Basic Looping with Do Loops

Quick reference

• 00:05 We're now gonna begin the journey of learning how to use loops inside VBA.
• 00:10 And looping is a really important technique, because there's
• 00:13 often times when you're going to want to look at each item in a particular area.
• 00:18 You may wanna look at every row in a spreadsheet.
• 00:20 You may wanna look at every worksheet in a workbook, and things like that.
• 00:24 What I will tell you, though, is that once we've learned and mastered how to loop,
• 00:28 you're gonna spend all of your time trying to avoid using loops at all.
• 00:32 Loops are a really interesting phenomenon,
• 00:34 and sometimes they are really important and we have to use them.
• 00:38 There are other times though where we reach them far too easily,
• 00:41 if you're looking at trying to check every item or every row in a worksheet for
• 00:45 example to see whether a specific characteristic is met,
• 00:48 you're better to throw an auto filter on it and record the auto filter actions.
• 00:52 It's much more efficient than looking at every single row.
• 00:55 But regardless, we do need to understand them and
• 00:57 that's what we're gonna do right now.
• 00:59 The first thing I wanna show you is the structure of what we call a do loop.
• 01:03 And you'll notice that, that is right here.
• 01:05 It says do, it's got something that's happening and then it says loop.
• 01:08 And what will happen is that every time this particular piece of code is executed,
• 01:11 it will jump into the do, and then it will execute this line of code and
• 01:15 then it will loop and do this over and over and over again.
• 01:18 So, this is just an academic piece here.
• 01:20 You'll notice that we've setup an L count variable as long,
• 01:23 we've got L count equals 1 and then we generate
• 01:26 jump into our do loop where we actually keep adding 1 to the L count variable.
• 01:30 And we expect that it will eventually or
• 01:33 should give us a printout with the debug.print keyword.
• 01:36 Now, what I'm gonna show you here is as we step through,
• 01:39 you can see, we set the L count to 1, we can see it in the locals window.
• 01:43 And then we're going to go in and we're gonna click do and it goes to 2 and
• 01:47 3 and 4.
• 01:47 And I'm just gonna click Run and I'm gonna let this guy go for right now.
• 01:51 Now if you ever get into the state and
• 01:54 you want to stop something because it is running, it tells you at the top here.
• 01:58 There is a key set on your keyboard called control break.
• 02:00 And that will actually break into and it give you the macro or
• 02:03 it will stop the macro and
• 02:04 get you in =to the debug cycle so that you can actually start dealing with that.
• 02:09 One of the challenges though is that some keyboards don't actually have that key
• 02:12 anymore and that means that you can't break into it, so you have to wait for
• 02:15 something like this to happen.
• 02:16 And we end up with a runtime error and an overflow.
• 02:19 What the heck does that actually mean?
• 02:21 Well, the challenge here is if I go into debug you'll notice that the L count is
• 02:25 now up at this massive number.
• 02:27 And as it happens, this is the highest number that a long data type can go into.
• 02:31 So it ran into what's called a buffer overflow.
• 02:33 The reason it did it is because this particular loop has run unchecked, it had
• 02:37 no exit point, no test, it just runs forever until it runs out of memory, okay.
• 02:41 So unfortunately it doesn't crash Excel but you'll notice that if I go down to
• 02:45 debug.print, let me just give myself a little room here.
• 02:48 I go and take a look at it now, it tells me that it counted this high and
• 02:52 that's all wonderful, there we go.
• 02:54 Now much smarter to run your do Loop with a test.
• 03:00 So, what you'll notice here is that we have a test in this particular case.
• 03:04 So this is still a loop we, go through, we've got out L count,
• 03:07 we've started our count = 1.
• 03:09 And we said, hey, look, I wanna take that 1 and I wanna add 1.
• 03:12 And then, this one here actually does something different.
• 03:14 It says, I'm gonna take the actor sheet, we're gonna go to range A and the L count.
• 03:19 So this would give me A and 2.
• 03:22 What's the value and it will give me the L count value and
• 03:25 then it will count until L Count = 5 and these keyword Exit Do will
• 03:30 make it jump out of the loop and continue on to the next row or next line of code.
• 03:34 So at this point, what you'll see is that if we go in here and we say, okay,
• 03:37 let's try it.
• 03:38 We'll set the value to 1.
• 03:39 We'll go in, it's gonna increase the value to 2,
• 03:41 it's gonna write that to the worksheet, so there it is, it's got the 2 showing up.
• 03:45 And then it's going to say if the L count = 5, well it doesn't, so it will loop.
• 03:51 And then it will go back and do this again, does L count = 5, no and
• 03:54 it will keep on doing this until L count = 5 which it does now,
• 04:00 at which point it will exit the do.
• 04:04 And then it will go and tell us how high it counted.
• 04:07 Okay, so if you're working with the do loop,
• 04:09 it's really really important to always make sure that you
• 04:12 actually have an exit checking here if you're just working with a regular loop.
• 04:18 For reference, I just wanna show you on this if statement.
• 04:20 This if statement is exactly the same as doing this.
• 04:26 So you have the alternative of writing an if statement that says then, exit, do,
• 04:30 and having your and if.
• 04:32 Or we can go back and I could actually set that back if I press Ctrl+Z a few times.
• 04:37 If I don't wanna type the end if and there is nothing else,
• 04:39 there is no else statement, I can do this.
• 04:41 If L count = 5 then Exit Do all in one line, that will work quite nicely, okay?
• 04:45 So big key thing I want you to remember here is if you're gonna work with
• 04:49 a standard do loop without some of the keywords that I'll show you later.
• 04:53 Make sure that you have a task that exits the do, otherwise you'll end up in
• 04:57 a situation where it runs forever and nobody wants to see that happen.

Lesson notes are only available for subscribers.