Loops: Looping Through Collections with For Each X in Y Loops

Quick reference

• 00:05 The last looping construct that we're gonna go through is what we call the For
• 00:09 Each loop.
• 00:10 This is different than the For x to y loop that we used previously.
• 00:14 The For x to y loop basically takes a range of numbers and steps through each
• 00:19 individual number within that group, iterating a certain number of times.
• 00:25 So we basically have to know how far we want to go and provide a number for
• 00:28 the beginning and the end that makes sense.
• 00:30 This can be challenging.
• 00:33 If you think of the case of worksheets, for example,
• 00:35 what you have today may not be what you have tomorrow.
• 00:37 Because you could send it to somebody else and
• 00:39 where you thought there were five worksheets,
• 00:41 they add four more to a file and now your code doesn't run on all of them.
• 00:45 So for this reason we have the For Each construct and what For Each does,
• 00:49 it allows us to iterate through each item within a collection of items.
• 00:53 And this is big because when you think of things like worksheets, we actually have
• 00:58 a collection of worksheets within the ActiveWorkbook.Worksheets container.
• 01:02 So we can actually cycle through each individual one.
• 01:05 And that way if somebody adds a new one, we've still got it.
• 01:08 So this is cool.
• 01:10 Now in order to make this loop work, we still need to dimension our variable.
• 01:14 So you'll notice that I've got my Dim ws As Worksheet.
• 01:17 And Worksheet is singular because I want to look at each Worksheet in
• 01:21 the Worksheets collection.
• 01:23 Then I'm gonna set up my For loop.
• 01:26 And it basically works this way, we say For Each, what's our variable,
• 01:31 In is a keyword, okay?
• 01:33 So For Each, whatever it is, In, what's the collection?
• 01:36 So this is ActiveWorkbook.Worksheets, this part is plural.
• 01:39 We're gonna look at each singular worksheet in the ActiveWorkbook.Worksheets
• 01:43 collection.
• 01:44 And then what we're gonna do is we're gonna send to
• 01:47 the immediate window that worksheets name.
• 01:50 The final part of this is that we say Next ws.
• 01:53 Now you don't have to have this piece here where it says ws.
• 01:56 I prefer it, it makes my code, when I'm looking back at it, at least I know what
• 01:59 I'm iterating through when I'm looking at the beginning or the end of the loop.
• 02:02 So it's a style thing, but for me, personally,
• 02:04 I always put the variable name at the end as well.
• 02:07 Let's go and step through it and see what it looks like.
• 02:09 We'll go in for each ws,
• 02:11 you'll notice right now that ws as my variable has nothing set to it.
• 02:16 When I go and actually click on it, it will open up and create the variable,
• 02:20 it assigns it there, and you can see that this is the CodeName "Sheet1".
• 02:24 It's an index of 1 and if I were to go down and
• 02:27 look further down we could find that the name is here.
• 02:30 Here is Name, "Sheet1" as well.
• 02:32 So notice it says "Sheet1" here, we'll say debug.print, let's go and execute that.
• 02:37 It says Sheet1.
• 02:39 Now we go into the next worksheet, what's that one?
• 02:41 It's Sheet2 and Sheet3 and
• 02:42 it'll work through them in the order that they're actually setup up here.
• 02:46 Okay, so all that looks good.
• 02:50 Where this gets bigger though,
• 02:52 is that we can actually start looping through individual cells as well.
• 02:56 So you'll notice that in this next one here that I've gone through and
• 02:59 I've said I'd like to set up a variable for Dim cl As Range.
• 03:02 That's because this area that I've selected is a range but
• 03:07 it's made up of ranges of cells.
• 03:10 So when we're actually looping through all cells, we always want to do this.
• 03:12 We want to set it up as a range.
• 03:13 Now, cl is just my abbreviation for cell.
• 03:16 I can't use cell because that is a reserved word in the object model but
• 03:19 cl will work just fine for me.
• 03:21 So I'm gonna say let me loop through each cl in the selection.
• 03:26 And we're gonna Debug.Print with the cl address &: the cl Value.
• 03:32 And then we'll say Next cl.
• 03:33 So what you'll see here is that I've selected these six cells.
• 03:37 Let's go and take a look at what actually ends up coming up.
• 03:39 So, For Each, we'll debug the first one.
• 03:41 It says B5 has a value of 23.
• 03:43 C5 has nothing.
• 03:46 B6, there's C6,
• 03:51 and B7, and C7.
• 03:55 Okay, so it's giving me all of the information that I'm looking for and
• 04:00 it actually ends up working across as it's reading these things out.
• 04:04 Which is handy to know.
• 04:05 And there we go, we'll see you Next cl and we'll finish.
• 04:10 Why does this stuff become useful?
• 04:12 Well, you know what? What if I wanted to go and
• 04:13 rename my worksheets and I didn't know how many worksheets were actually in there?
• 04:17 And I said, hey, you know what, I know they're all Dept10.
• 04:20 I'm gonna rename them out because I wanna be able to print this out on a worksheet
• 04:24 or onto paper and have page numbers, or department footer, or
• 04:27 whatever I'm looking for.
• 04:29 So in this case, I'm dimming my WS's worksheet.
• 04:32 I'm giving a count in this case, much like we did inside our do loop.
• 04:36 So for each worksheet in the ActiveWorkbook.Worksheets.
• 04:40 So look in each worksheet in the Worksheets container.
• 04:43 I'm going to increase my count, notice that I did not set a count before I
• 04:46 start to here so this is count is going to be at 0 to start with.
• 04:49 We're gonna increase it 1 and then we'll say, ws.Name = "Dept10-".
• 04:54 The first time will be 1, the second time will be 2.
• 04:56 So as we go through here.
• 04:58 We can go through and say, all right, the first time through, there's the rename.
• 05:05 You'll notice it's now Dept 10-1 up here and
• 05:08 you'd also notice if we actually expanded this guy here, it is Dept 10-1 as well.
• 05:14 So let's go back to the Visual Basic editor and
• 05:18 let's run the rest of these out.
• 05:19 And you'll notice that all the worksheet names have changed and
• 05:22 everything looks good as well.
• 05:23 So this is why the For Each loop is really, really useful is because if I had
• 05:27 3 worksheets or 30, it doesn't matter, it would have gone through each one of them.
• 05:31 So it allows us to loop through all the different elements that are there,
• 05:34 no matter how many there are without necessarily knowing the count.

Lesson notes are only available for subscribers.