VBA Loop: How to Create Loops in Visual Basic Access

The purpose of a loop is to get your program to repeat a certain piece of code a number of times. The reason behind the various types of loops you can choose from is because each type of loop meets a certain condition. For example, the Do While loop works in a sense that a program will repeat a certain type of code a number of times while the program is doing something else.

Many professionals use VBA with Excel in order to do amazing and incredibly efficient work. If you want to take Excel to its fullest extent and combine programming with your data work, check out the Udemy course Ultimate Excel VBA. Of course, if you’re new to it all, VBA – Introduction to VBA Macros is the perfect way to get started.

The Various Types of Loops 

Before you create a loop in VBA you should understand what each loop means. Every loop is meant for a specific condition. For example, before we mentioned a do while loop that repeats a specific amount of code while a condition is met, but there is also a For…Next loop that starts with a certain number, increases based on the number you set for it, and moves on to the next iteration until it reaches the end of the list of numbers.

These two types of loops are very different, and they cannot be interchanged with each other. In total, there are four different types of loops in VBA, and they have various subtypes that you can use to make things even more convenient.

The four types of loops that you will use in VBA are the while loops, do loops, for loops, and for each loops.

• The while loop, as mentioned before, runs a certain number of statements as long as the condition that you set remains to be true.
• The do loop gives you the ability to test certain conditions at the beginning or end of a loop, you can then choose to have the loop repeat until the condition that you set is true or until it becomes true.
• The for loop is just like the example we mentioned before, you perform this loop a set number of times, it’s not based on a condition like the other loops when it comes to repeating. You can set the number of times this loop repeats, specify the number at which the loop increases, and set the start and end values of the loop.
• The final type of loop is the for each loop. This loop applies a certain setting to every item within a loop. For example, you can have a set of numbers, and with the for each loop you can increase all of those numbers by one a specific number of times until a condition you set is true.

Creating Your First While Loop

Remember that a While Loop runs a certain number of statements as long as you set a given condition and that condition happens to be true. Below is the basic structure of a while loop.

While condition
[Statement]
[Continue While]
[Statement]
[Exit While]
[statement]
End While

Before you create the loop, understand what each term means. The term condition is a Boolean expression, or an expression that can only be valued as either true or false. If you don’t set a condition then Visual Basic will automatically assume that it is false.

The term statement is option, but it is what will happen every time the condition that you set is true.

The Continue While statement is also option, what it does is allow you to start a new iteration of the While block, consider it as a way to check on the loop as it operates.

The Exit While stops the while loop, but still runs the block of code. Again this piece of the code is optional, but it is great for various applications.

The End While is the final part of the loop, and unlike the previous two conditions it is required. This stops the loop. Remember that if you don’t stop a look it will continue forever and eventually it will break the program.

In order to fully see how it works, here is an example of a While Loop:

Dim index As Integer = 0
While index <= 10
Debug. Write(index.ToString & “ “)
Index += 1
End While
Debug. WriteLine(“”)

What comes out of the program are the following outputs: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Notice how it stops at 10, which is the condition that you set for the program.

Creating Your First Do Loop 

A Do Loop works similarly to a while loop in that it repeats a certain block of statements while a condition is set to true, but it can also work in the opposite manner where it will only work if a condition is not true. The two types of ways you can write a do loop are as follows.

Do {While |Until} condition
[Statements]
[Continue Do]
[Statements]
[Exit Do]
[Statements]
Loop
Do
[Statements]
[Continue Do]
[Statements]
[Exit Do]
[Statements]
Loop {While |Until} condition

The first loop continues to repeat if the condition is true, while the second loop only continues to repeat if the condition is false. The terms are similar to the while loop, and everything except Do, While, Until, and Loop are required for the program to work. The statements, Continue Do, and Exit Do are all not necessarily needed.

Below is an example of a Do Loop so you can understand how it works. Notice the similarities to the While Loop:

Dim index As Integer = 0
Do
Debug. Write(index.ToString & “ “)
Index += 1
Loop Until index > 10
Debug. WriteLine(“”)

Again, the output for this code is 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.

Creating Your First For and For Each Loop 

In the same way that a do and a while loop are similar, for and for each loops also share a fair bit of similarities. In a for loop the basic structure is below.

For counter [As datatype] = start To end [Step step]
[Statements]
Next [counter]

Unlike in previous lines of code there is nothing optional here. Except for the statements, datatypes, and steps, the start, end, next, and counter terms are all required to get the program to run.

For index As Integer = 1 To 5
Debug.Write(index.ToString & " ")
Next
Debug.WriteLine("")

This is a basic type of code you would write for a for loop. A for each loop works very similarly, except for the fact that instead of creating a condition and increasing by a certain number, you take every item in a collection and change it however you want. Since we have already viewed the various terms for all types of loops you can simply view an example.

Dim lst As New List(Of String) _
From {"abc", "def", "ghi"}
For Each item As String In lst
Debug.Write(item & " ")
Next
Debug.WriteLine("")
Your output should be abc, def, ghi

Using VBA Loops in Various Ways 

Visual Basic Access gives you lots versatility when it comes to loops. You can even nest loops within each other. Use your knowledge of loops to create your own VBA projects and see how it works. You can even learn a lot more about VBA if you want. The Udemy courses Excel VBA 24-Hour Trainer and Learn VBA for Microsoft Access are both great resources.