Loops in VBA: Optimize Your Programming

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.

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:

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:

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:

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:

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...