In today's video, I'm going to show you how to use a For-Next loop in VBA. We'll also see how to use the step increment option and the Exit For command. This will be the first in a series of videos on VBA loops. So stay tuned to my channel if you want to learn all there is to know about different loops in VBA. Of course, this is a Developer lesson, so if you've never done any programming in VBA before, go watch this video first. It teaches you everything you need to know in about 20 minutes. And if you've never used a variable before, go watch this video. These are very important for loops. These are free videos; they're on my website and my YouTube channel. Go watch them and come on back. I'll wait for you.

The For Next loop is the most basic, simplest loop you can do in VBA. This is good if you know exactly how many times you want the loop to repeat. It basically looks like this: For counter = start to end, some code here, and then Next. So for example, if you want the loop to run ten times, it's For x = 1 to 10, and it will count up 1, 2, 3, 4, 5, 6, whatever. Do some stuff, and then Next. And it will repeat that many times. You can optionally specify a step increment, and that will have the counter add that much to its value. So for example, For x = 1 to 10, Step 2 will have x go up by 2 each time. So it's going to go 1, 3, 5, and so on until it hits 10 or is over 10. And if something happens inside the loop and you want to immediately get out of it, then you can use the Exit For command.

Let's take a look at an example in Access. Okay, this is a copy of my Tech Help Free template. This is a free database; you can download a copy off my website if you want to. You'll find the links down below. And I have this thing here called the "Hello World" button that just puts a message in the status box. So we're going to use this. I like to use this feature for outputting stuff. Or, if you're outputting messages for the user, you can use the status box for that. I've got a whole separate video on how the status box works if you're not familiar with it.

Let's go into Design View and we're going to change this from the "Hello World" button to "Run Loop." Let's make this a little bit bigger, just like this. All right, let's right-click, Build Event. Here I am in my code. And instead of "Hello World," let's say I just want to output the numbers from 1 to 10. Okay, we're going to start off by declaring our variable: Dim x As Long. Then our loop: For x = 1 to 10, press Enter twice, and then put Next at the bottom. I like to put my Next at the bottom first, before I write the stuff inside the loop because sometimes I forget to put it there. All right, now we're going to tab in here. Indentation is important; VBA doesn't care, but it's good to make your code readable. If you look at it later, you don't want to be going, "What did I do?" So make sure you indent properly.

And now we're just going to write that number out to the status box. So we'll use my Status function: "The number is " & x, a little string concatenation there. And that's it. That's your first simple loop. Let's save that, come back over here, and let's run it. Boom, there you go. And remember, the status box runs backwards, so it goes 1, 2, 3, 4, 5, all the way up to 10. That's a nice simple loop.

Let's take a look at that step indicator, right? If we want to come over here and say Step 2, that means every time it loops, it's going to add two to that value. Okay, save it, come back out here, run it again. And there you go; it started right here, right? 1, 3, 5, 7, 9, giving you all the odd numbers. If you want even numbers, you could start this at 2. Just say, For x = 2 to 10. And now, there you go: 2, 4, 6, 8, 10.

Let's say you want to total all of those up as you're looping. Let's come back over here, get rid of that Step, and let's say we want to add up the values from 1 to 10, right? 1 plus 2 plus 3 plus 4 and so on, all the way up to 10. So we'll need another variable to hold that; we'll call it Total As Long. We'll have to start the total off at 0, so Total = 0, and then inside the loop here, we'll say Total = Total + x, whatever our counter is. So it's going to start off at 0, then go to 1, plus 2 is 3, right? And we'll say, "The number is " & x & ", The total is " & Total. Let's save that. Okay, let's go back over here. Run it again. All right, there you go. The number is 1, the total is 1. The number is 2, the total is 3. See how it's adding up? 3, 6, 4, 10, and so on, all the way up to 10. When it gets to 10, the total is 55. All right, we're adding those up as we go.

If you have a situation where you need to exit that loop early, you can use the Exit For command. Let's say, for example, I'm going through here, I'm adding stuff up, and as soon as the total hits 30, I want to get out of here. I don't care what the number is; as soon as that total hits 30, get me out. All right, so right here we'll say If Total >= 30 Then Exit For. That's it. We're going to get out of the loop. So as soon as that total hits 30, boom, we're out. All right, save it. Let's go back out here. Run it. And there you go; the number is 7, the total is 28. The number is 8, the total is 36. Exit. We're out. It didn't get to 9 or 10.

And that's how you use the For-Next loop in VBA. Hope you learned something today. Don't forget to give me a thumbs-up and subscribe if you haven't yet. I release new lessons every week. Thanks for watching.