What are For Loops and How to Use Them - VBA Tutorial

For loops are an essential programming method used across many different languages, and allow for more precise, readable, and flexible code.

Contents:

1. What is a For Loop?

1.1. Basic Loop Example

1.2. Real Word Loop Example

2. Why Should You Use For Loops?

3. What is a Double Loop?

3.1. Double Loop Example

VBA For Loop Tutorial

What is a VBA For Loop?

A for loop in VBA is a statement that allows you to repeat a specific block of code a predetermined number of times. It is often used to iterate over a collection of items, such as the cells in a range or the elements in an array.

Here is an example of a for loop in VBA that prints the square of the numbers 1 through 10 to the immediate window (like a virtual console in VBA):

Sub print_squares()
    For i = 1 To 10
        Debug.Print i * i
    Next i
End Sub

In this code, the loop is defined by the 'For' and 'Next' statements. The variable 'i' is the loop variable that is used to track the number of iterations. It is initially set to 1, and the code within the loop will be executed for each value of 'i' from 1 to 10.

The code inside the loop consists of a single statement that prints the square of 'i' to the immediate window using the Debug.Print method. This statement will be executed 10 times, once for each value of 'i', and the resulting output will look like this:

Here is another example of a for loop in VBA that sums the values in a range of cells:

Sub sum_range()

  Dim rng As Range
  Dim total As Double
  
  Set rng = Range("A1:A10")
  total = 0
  
  For Each cell In rng
      total = total + cell.Value
  Next cell
  
  Range("A11") = total

End Sub

In this code, the loop is defined by the For Each and Next statements, with 'cell' being the loop variable. The code within the loop will be executed for each cell in the range, and the resulting output will be the sum of all the values in the range.

So, for each cell in our range A1:A10, we are taking the value, and adding that value to all of our previous cell values, giving us the range total which we are printing in cell A11. This is the same calculation as the SUM function:

= SUM(A1:A10)   

Why Should You Use for Loops While Writing VBA?

There are several reasons why you might choose to use a for loop over other statements in VBA:

1. Precision: for loops allow you to specify exactly how many times the code within the loop should be executed, which is not always possible with other control flow statements. For example, if you want to iterate over a collection of items, you can use a for loop to make sure that the code is executed once for each item in the collection.

2. Readability: for loops are often easier to read and understand because they clearly indicate the start and end of the loop and the number of times that the loop will be executed. This can make your code more maintainable and easier to debug.

3. Flexibility: for loops can be used in a variety of situations. For example, you can use a for loop to iterate over a range of cells in a worksheet, to process the elements in an array, or to repeat a specific task a certain number of times.

In general, for loops are a valuable tool in your programming toolkit and can be used to solve a wide range of problems. Of course, there are other control flow statements that may be more appropriate in certain situations, so it's important to consider your specific needs and choose the right statement for the job.

What is a Double Loop in VBA?

A double loop in VBA is a loop that is nested inside another loop. In other words, it is a loop that is defined within the code block of another loop. Double loops are useful when you want to iterate over two or more collections of items simultaneously.

Here is an example of a double loop in VBA that prints the product of two numbers to the immediate window:

Sub print_products()
    For i = 1 To 10 
        For j = 1 To 10
            Debug.Print i * j
        Next j
    Next i
End Sub

In this code, the outer and inner loops are defined For and Next statements. The inner loop being inside the code block of the outer loop. The loop variables i and j are used to track the number of iterations for each loop.

The code inside the inner loop consists of a single statement that prints the product of 'i' and 'j' to the immediate window using the Debug.Print method. This statement will be executed 100 times, once for each combination of values for 'i' and 'j'. The resulting output would be seen to the left of this paragraph.

1 is multiplied by 1->10, and then 2 is multiplied by 1->10, and so on, until i = 10.