Loops in VBA

Loops are an essential part of any programming language because they help with the repeated running of any code snippet. Just like any other language, VBA offers the following loops to enable efficient programming:

1. Do until
2. Do while
3. For
4. Foreach

In this article we will dive dep into the “for” loop which iterates only for “x” number of times where “x” is a known value before the loop’s runtime.

“X” can also be a predictable number at runtime, for example when you determine the Lbound and Ubound values of arrays.

The For Loop in VBA

In VBA, “for loop” is easy to use. It follows a simple and understandable syntax.

Syntax:

For <counter> = <start> to <end> [ <step value> ]

[ <code block to execute> ]

Next [ <counter> ]

where

<counter> is the iterator variable which keeps incrementing by the <step value> after every iteration. 

<step value> is optional. If this value is not provided, the default incremental value is “1.”

<Code block to execute> is also optional. It is the snippet of code to be executed during every iteration.

The “Next” statement marks the end of the “for” loop. It indicates to move to the next iteration of this loop with the incremented counter value.

Simple Examples of a For Loop

Print a Mathematical Table for a Number

Sub forloop_demo()
    ' declare all the required variables
    Dim counter, input_number As Integer
    
    ' Receive input from user
    input_number = InputBox(" Which tables do you want to print ? ")
    
    ' Print the mathematical table of the input number upto 12 count
    For counter = 1 To 12
        Debug.Print (counter & " X " & input_number & " = " & counter * input_number)
    Next
            
End Sub

This program receives a number as an input from the user. Then, using a “for” loop, it is multiplied with each number from 1 to 12 to print a “times table” table of that input number.

A sample output of this program if “3” is input by the user is seen in the image below.

Program to Print Contents of an Array

This program defines an array with its size. All elements of the array are assigned values one by one. Next they are printed as we iterate through a loop. It is noteworthy to say that the iterator variable (i) of the “for” loop is also used as the array index inside the loop (this is not mandatory but makes it easy to read/understand/maintain).

Sub array_cars()

'declaring variable while defining its size
Dim arr_my_cars1(5) As Variant

'initialize array elements for one array
 arr_my_cars1(0) = "Benz"
 arr_my_cars1(1) = "Maruthi"
 arr_my_cars1(2) = "Hyundai"
 arr_my_cars1(3) = "Ford"
 arr_my_cars1(4) = "Nissan"

'print all elements of the array
For i = LBound(arr_my_cars1) To UBound(arr_my_cars1)
    Debug.Print arr_my_cars1(i)
Next

End Sub

Output of the Program:

Benz

Maruthi

Hyundai

Ford

Nissan

Program to Write and Read Array Contents

This program slightly varies from the one above. Here you will create an array and iterate through it using its indices. 

Values are given to each array item using a “for” loop initially. (This was not the case in the previous program.)

Then, all the data stored in each element of the same array are read/printed back using another “for” loop.

Two loops are used here one below the other:

1. One to write data to array
2. Another one to read data from the same array.

Sub array_check_demo1()

' Declaration of array variable
Dim arr1(11)

' Assign values to array elements
For i = LBound(arr1) To UBound(arr1)
    'Allocate data for each array item through a loop
    arr1(i) = Cells(i + 2, 1).Value
Next

' Print values of the array
For i = LBound(arr1) To UBound(arr1)
    'Read data of each array item through a loop
    Debug.Print arr1(i)
Next
End Sub

Input is taken from the table below:

Principal amount	No of yrs	Age of customer
10000	5	67
340600	6	45
457800	8	34
23400	3	54
12000	4	23
23545	4	56
345243	2	55
34543	3	24
23223	2	19
3656	1	65

Output of the Program

Program That Uses the [step] Value

Check out this program and try to find out why the numbers are not continuous in the output:

Sub step_demo()
' declare a counter
Dim p As Integer

'loop through 10 numbers and print them
For p = 1 To 10 step 3
	Debug.Print p
Next 
End Sub

Output of the Program

1

4

7

10

Yes, you might have found that the iterator variable increments by “3” instead of “1” in every iteration. Hence, the numbers printed are skip counted by “3.”

Try the same with different values in the place of [step] to understand it better if you are still confused.

The Nested “For” Loop

A loop within a loop is called a nested loop. 

Structure

Let me explain this structure with colors:

1. The for loop structure in yellow color is the outermost loop. 
2. The for loop structure/block in green color is the inner loop for the outermost (yellow color) loop. But it also acts as the outer loop for the “for loop” structure in turquoise color.
3. The for loop structure in turquoise color is the innermost loop. 

In this way , we can have any number of nested loops. 

But there are ample chances for you to get confused when you see your own code after several days. You will wonder inside which loop is a specific line and what role it plays in the whole code.

To avoid this, follow the tips below while you code your logic:

1. Maintain the alignment in your code with proper tab spaces as shown in the structure above. This can help you find the block of code/loop in which your statement is. 

If possible, use the iterator variable next to the “next” keyword. It can help you indicate which “next” statement  marks the closure of which loop. This is also marked in the colored structure for reference.

A Few Examples of Nested For Loops

Program to Format Cells with a Specific Word

This program iterates through all the cells (every column of every row in this case). If the cell content has a word “India,” then the specified formatting is applied to it.

Sub format_cell_with()
For i = 1 To 15
    For j = 1 To 5
    cellcontent = Cells(i, j).Value
    If InStr(cellcontent, "India") > 0 Then
        With Cells(i, j).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
        End With
    End If
    Next
Next

Input sheet:

ICC Men’s Cricket World Cup Winners – 50 Overs
Year	World Cup Winner	Runners Up	Host	Finals Venue
1975	West Indies	Australia	England	Lord’s Cricket Ground, London
1979	West Indies	England	England	Lord’s Cricket Ground, London
1983	India	West Indies	England	Lord’s Cricket Ground, London
1987	Australia	England	India & Pakistan	Eden Gardens, Kolkata
1992	Pakistan	England	Australia & New Zealand	Melbourne Cricket Ground, Melbourne
1996	Sri Lanka	Australia	India, Pakistan & Sri Lanka	Gaddafi Stadium, Lahore
1999	Australia	Pakistan	England	Lord’s Cricket Ground, London
2003	Australia	India	Australia	Wanderers, Johannesburg
2007	Australia	Sri Lanka	West Indies	Kensington Oval, Bridgetown
2011	India	Sri Lanka	India	Wankhede Stadium, Mumbai
2015	Australia	New Zealand	Australia	Melbourne Cricket Ground
2019	England	New Zealand	England	Lord’s, London
2023	—	—	India	—

Output sheet – after the program is run:

Program with 2D Array and “NESTED FOR” Loop

This program declares a two dimensional array and initializes data in all its elements.

The first dimension holds the students’ names, and the second dimension holds the “exam results” of the students in the first dimension. These are written back to an Excel sheet. 

Warning: As the code does not select any sheet in specific, please ensure that you keep a blank Excel sheet selected. This can prevent any damage to your existing data.

In case you wish to learn more about multidimensional arrays, please check out this article.

Sub Nested_for_demo2()

'declaring and defining size of an array
'3 means 4 rows starting from 0 to 3 and 1 means 2 columns starting from 0 to 1
Dim arr_stu(1 To 5, 1 To 2) As Variant

'initialize array elements

 arr_stu(1, 1) = "Dave"
 arr_stu(1, 2) = "Fail"
 arr_stu(2, 1) = "Trumpo"
 arr_stu(2, 2) = "Pass"
 arr_stu(3, 1) = "Vincent"
 arr_stu(3, 2) = "Pass"
 arr_stu(4, 1) = "Rose Mary"
 arr_stu(4, 2) = "Pass"
 arr_stu(5, 1) = "Eliza"
 arr_stu(5, 2) = "Fail"
 
' print all elements to the open excel sheet.
For i = 1 To 5
    For j = 1 To 2
        Cells(i, j) = arr_stu(i, j)
    Next j
Next

End Sub

Output of the Program on the Active Excel Sheet: Here 

1. The rows indicate the “i” value of the array (first dimension) and the iterator variable of the outer “for” loop.
2. The columns indicate the  “j”  value of the array (Col A – first dimension , Col B – second dimension)  and the iterator variable of the inner “for” loop.

The “Continue” Statement in Visual Basic: Skip a Part of an Iteration

When you encounter a situation where you wish to skip running the rest of the code inside the loop for a specific iteration based on a condition, the “continue” statement will come to your rescue in Visual Basic Programming.

Continue

Syntax:

Continue { Do | For | While }

Where  { Do | For | While } are the respective loops in which it has to be used.

A Simple Example

Sub continue_demo()
' declare a counter
Dim i As Integer

'loop through 10 numbers and print them
For i = 1 To 10
    ' we will skip printing "4"
    If i = 4 Then
        Continue For
    End
    Debug.Print i
Next
End Sub

Your output will not have “4” in this case. As the condition is met in the 4^th iteration, the continue statement is hit and the rest of the code is skipped for that iteration. The control then moves to the “next” statement of the loop (inner loop in case of nested loops).  

Note: The Next statement is the closure of a loop, as you may be aware.

Output of the above program

1

2

3

5

6

7

8

9

10

Continue Statement is Not Available in VBA

VBA does not offer the “continue statement” because well-built logic in a loop can help us avoid using a “continue” statement. For example, rearranging statement blocks like nested loops and if conditions can help avoid the use of “continue” statements. 

An Alternative to the Continue Statement

In unavoidable situations, the Goto statement can be used. 

This article can provide you with more insights on how to properly use the “Goto” statement in VBA.

Conclusion

In my experience, I would say that the “for” loop is my favorite compared to the “do while “/“Do until”/“ For each” loops. The reason is the comfort of defining it completely in one line. Even during run-time, you can find how many iterations are completed and how many are yet to come.

I have also been able to use this loop in situations where I do not know the number of iterations. I manage it using conditions inside the loop. It also comes handy when I want to wait for a page load during automation. Once you start using this loop, you will even start playing around nested “for loops” with much more confidence and fewer of mistakes. 

So, what are you waiting for ? Why not give it a try? 😊