For Next Loop
By Richard Rost
3 years ago
For Next Loops in Microsoft Access VBA
In this Microsoft Access tutorial, I'm going to teach 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.
Prerequisites
Links
VBA Loops Series
Recommended Courses
Syntax
For Counter = Start to End [Step Increment]
' Your Code Here
[If Condition Then Exit For]
Next [Counter]
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, For-Next Loop, for next loop, for/next loop, for loop, VBA, Step Increment, Exit For, Loop Counter,Total Accumulation, Conditional Exit, Loop Termination
Subscribe to For Next Loop
Get notifications when this page is updated
Intro In this video, I will show you how to use the For-Next loop in Microsoft Access VBA, including how to set up a basic loop, use the step increment, and apply the Exit For command to exit the loop early. We will walk through examples such as outputting a sequence of numbers, changing the step size, calculating running totals, and exiting a loop based on a condition.Transcript 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.Quiz Q1. What is the primary purpose of the For Next loop in VBA?
A. To repeat a block of code a specific number of times
B. To automatically sort an array
C. To handle user input
D. To interact with web services
Q2. Which part of the For Next loop allows you to specify how much the counter increases on each iteration?
A. Dim Statement
B. The Step keyword
C. The Total variable
D. The End keyword
Q3. What happens if you set For x = 1 To 10 Step 2 in your loop?
A. The loop will run from 1 to 20, increasing by 2 each time
B. The loop will run from 1 to 10, increasing by 1 each time
C. The loop will run from 1 to 10, increasing by 2 each time
D. The loop will not run at all
Q4. Why might you use the Exit For command inside a For Next loop?
A. To move to the next iteration of the loop
B. To repeat the current iteration
C. To immediately exit the loop based on a condition
D. To reset the loop counter to zero
Q5. What is the output in the status box if you use For x = 2 To 10 Step 2?
A. 1, 3, 5, 7, 9
B. 2, 4, 6, 8, 10
C. 1, 2, 3, 4, 5
D. 10, 9, 8, 7, 6
Q6. Which statement is true regarding variable declaration in the loop example shown in the video?
A. Variables must be declared with the Set keyword
B. You do not need to declare variables in VBA
C. Variables are declared using Dim, such as Dim x As Long
D. Variable names must always be uppercase
Q7. Why is indentation recommended in writing VBA code, as mentioned in the video?
A. It changes how the code works
B. It makes the code execute faster
C. It makes the code more readable
D. It is required by the VBA compiler
Q8. How do you accumulate a running total of loop values in VBA within a For Next loop?
A. Use the statement Total = x * x in the loop
B. Add the loop counter to the total each iteration: Total = Total + x
C. Print the value of x only
D. Use the Print statement outside the loop
Q9. Given the code If Total >= 30 Then Exit For, what will happen inside the loop?
A. The loop will skip every time Total is 30 or more
B. The loop will immediately exit once Total is 30 or higher
C. The loop will reset Total to zero every time
D. The loop will pause for user input when Total is 30
Q10. If you need to process but not necessarily use every number in a fixed range, which VBA loop should you use according to the video?
A. Do While loop
B. While Wend loop
C. For Next loop
D. For Each loop
Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-B; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is all about working with the For-Next loop in VBA. I will explain how to create a basic loop, make use of the step increment option, and work with the Exit For command. This is the first in a series of tutorials I have planned on different kinds of loops in VBA, so if you're interested in deepening your understanding of VBA programming, definitely keep an eye out for future lessons.
Before getting started with this developer-level lesson, I recommend that you already be familiar with working in VBA. If VBA programming is completely new to you, visit my website or YouTube channel for my introductory lesson that covers all the basics in around 20 minutes. There is also a separate beginner video on using variables in VBA, which you should definitely review if you have not worked with variables before. Understanding variables is especially important for using loops effectively.
The For-Next loop is the most basic and straightforward type of loop you can construct in VBA. Use this loop when you know in advance exactly how many times you want your code to repeat. The structure is simple: you set a counter to start at a particular value, specify the ending value, write the statements you want to execute each time, and then finish with Next. For example, if you want the loop to run 10 times, you set up a counter variable, define your starting and ending values, and the loop will process from 1 through 10.
A nice feature of the For-Next loop is the optional step increment. If you include the Step value, the counter will increase by that amount each loop cycle rather than the default of 1. For instance, using Step 2 will result in your counter jumping from 1 to 3 to 5 and so on, until it surpasses the upper bound. This is very helpful when, for example, you only want every other value in a range. If you need to exit a loop before it completes naturally, make use of the Exit For command. This forces the loop to terminate immediately based on your logic.
Let me walk you through an example in Access. I'm starting with my free Tech Help template database, which you can download from my website. On the form, I have a button originally labeled "Hello World" that is used to display a message in the status box. I use this status box feature for output, and it's also very handy for messaging users. If you want a more in-depth explanation of the status box, I have a separate video covering it.
I'll switch into Design View, rename the button from "Hello World" to "Run Loop," and enlarge it a bit. Then, I go into the event builder. Inside the code, instead of outputting a static message, I want to display the numbers from 1 to 10 dynamically. To do this, I declare a variable for the counter, then set up the For-Next structure: the loop will start at 1 and end at 10. A tip: I always write the Next statement at the bottom right away so I do not forget to include it. While VBA does not enforce indentation, keeping your code properly aligned helps with readability and troubleshooting later.
Inside the loop, I use my Status function to output the current value of the counter, concatenated as "The number is" followed by the variable. After saving and running it, you will see the numbers 1 through 10 appear in the status box, each on their own line because the status box shows the most recent line at the top.
Next, let's see how the step increment works. If I want the counter to increase by two each time, I adjust the loop to include Step 2. When I run the code, the sequence becomes 1, 3, 5, 7, and 9, displaying only the odd numbers between 1 and 10. If I want the loop to output even numbers instead, I simply start the counter at 2 instead of 1.
Suppose I want to add all the values from 1 to 10 together during the loop. For this, I introduce a new variable, Total, which is initialized to zero before the loop starts. Inside the loop, I increment the Total variable by the current counter value during each iteration. Then I output both the current counter value and the running total in the status box. As the loop progresses, the total accumulates all the numbers, and by the end, it displays the final sum, which should be 55.
Sometimes, you may need to break out of the loop before it reaches the end. For that, you use the Exit For command. For example, if you want to stop as soon as the total reaches or exceeds 30, insert an If statement in the loop. The logic is straightforward: check if the total is greater than or equal to 30, and if so, use Exit For to end the loop right away. After running this version, you will see the numbers and totals stop as soon as the total surpasses 30, without reaching the full range up to 10.
That is the basic process of setting up and working with a For-Next loop in VBA, using step increments and the Exit For command as needed. If you want to master loops and other VBA concepts, I have lots of free tutorials and lessons available on my website.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Declaring a counter variable for a For Next loop
Writing a basic For Next loop in VBA
Using the Step increment in a For Next loop
Outputting loop results to a status box
Changing the loop start and end values
Summing values inside a loop
Using multiple variables for calculations in a loop
Exiting a For Next loop early using Exit For
Conditionally breaking out of a loop |
