This tutorial is essential for understanding how loops in VBA can simplify your tasks in Excel. Loops are fundamental in programming, and understanding their functionality will help you create more efficient programs.
What is a Loop in Programming?
A
loop is the cyclical repetition of an activity, limited to a finite number of repetitions. A loop can be interrupted either by reaching the predetermined number of repetitions or by fulfilling a specific condition. For more information on conditionals, check out our tutorial on
Conditionals in VBA.
Figure 1. Definition of a Loop in Programming.
Types of Loops in VBA
In VBA, there are two main structures for implementing loops:
For / Next and
Do / Loop. Each has unique characteristics that make it suitable for different situations. Below, we will explore both types with practical examples.
For Next Structure
The
For Next structure allows you to perform a repetitive action from a starting number (NI) to an ending number (NF). Below is an example of how to structure a
For loop:
| NI |
NF |
Total Repetitions |
| 1 |
10 |
10 |
| 10 |
19 |
10 |
| 81 |
91 |
10 |
The number of repetitions must be an integer. The
For Next structure is declared as follows:
| For Variable = NI To NF Step increment |
Loop declaration |
| Repetitive Activity |
Activity |
| Next |
Loop declaration |
Example of the For Next Loop in VBA
Next, we will see how to find the last occupied cell in a range using a
For Next loop:
Figure 2. Example of For Next Structure.
Sub Tutorial5a_Loops()
For i = 1 To 10
If Cells(i + 1, 1) = "" Then
LastRow = i
Exit For
End If
Next
MsgBox ("Last Row: " & LastRow)
End Sub
In this code, the program checks if the cell is empty, and if so, it stops and displays the last occupied row.
Do Loop Structure
The
Do Loop structure does not automatically increment the repetition variable. It offers three modes that allow for more flexible control:
Do Mode
This form allows you to declare the loop and specify the exit condition. It should be used carefully to avoid infinite loops.
| Do |
Loop declaration |
| Repetitive Activity |
Activity |
| Variable=Variable+1 |
Control of repetition count |
| Loop |
Loop declaration |
Example of the Do Loop in VBA
The following example shows how to find the last occupied row using a
Do Loop:
Figure 3. Example of Do Structure.
Sub Tutorial5b_Loops()
i = 2
Do
If Cells(i, 1) = "" Then
LastRow = i - 1
Exit Do
End If
i = i + 1
Loop
MsgBox ("Last Row: " & LastRow)
End Sub
Do While Mode
This mode executes as long as a specified condition is met. Let’s see how to apply it:
Figure 4. Do While Structure.
Sub Tutorial5c_Loops()
i = 2
Do While Cells(i, 1) <> ""
'Repetitive Activity
i = i + 1
Loop
LastRow = i - 1
MsgBox ("Last Row: " & LastRow)
End Sub
Do Until Mode
This mode executes until a specified condition is met. Here’s an example:
Figure 5. Example of Do Until Structure.
Sub Tutorial5d_Loops()
i = 2
Do Until Cells(i, 1) = ""
'Repetitive Activity
i = i + 1
Loop
LastRow = i - 1
MsgBox ("Last Row: " & LastRow)
End Sub
As you can see, the loop executes
until it finds the first empty cell.
Conclusion
With what you've learned in this tutorial, you'll be able to use loop structures in VBA with ease. It is advisable to avoid the
Do mode due to the risk of infinite loops. Instead, opt for
For Next,
Do While, or
Do Until for safer and more effective control.
Want to know more?
Contact us...
