Nested Loops – Looping Within Loops

While stand-alone loops will accomplish many tasks that require basic iteration, loops can be nested within other loops. In Excel, nested loops provide robust functionality to complete a number of tasks, such as filling out spreadsheet data, creating multidimensional arrays, or formatting cells.

Note: If you are looking for information on how to use 2D arrays in a nested loops, see our post on multi-dimensional arrays.

Overview

This post within the Getting Started with VBA series continues the focus on Loops in VBA.

As you now understand the basics of the For, For Each, and Do Loops in VBA, it’s worth looking into a common application of loops. In many situations, there may be a need to perform iterations within existing iterations. This technique is known as nesting loops.

The following examples will utilize different loop types to output a multiplication table on a spreadsheet.

Nested For Loop

For Loops are generally the easiest iterative statement, outlining the start and end value in the For statement, then the iteration statement in the Next statement. To output a multiplication table on a spreadsheet, the following actions must take place:

  • A For Loop increments through each row of the first factor.
    • A For Loop nested within the first For Loop increments through each column of the second factor.
      • A statement within the nested For Loop multiplies the factors and prints them to the corresponding cell.

The script appears as follows:

The output:

Nested loops used to create a multiplication table.
Basic multiplication table created with nested For Loops.

One very important thing to keep in mind is that different variables should be used within nested loops. If identical counter variables are used in nested For Loops, VBA will return an error before any script processing.

Nested Do Loop

The approach to building a multiplication table for a Do Loop is similar to the For Loop, but contains the following differences:

  • The starting value is not specified in the Do Loop statement.
  • The increment statement must be specified within the loop.

The script is as follows:

Given the previously mentioned constraints with Do Loops, this script contains a few lines that were not required for the For Loop. These include:

  1. The integer start value must be specified prior to entry into the loop. The start value of the first factor i is specified before the parent loop, as it will only iterate through rows 1 through 10 once. For the nested loop, variable j is set to 1 prior to entry to the nested (child) loop, and will be reset to 1 for each iteration of i given that it must loop through column “A” through column “J” 10 times.
  2. Within the Do Loop, an increment statement exists (j = j + 1 and i = i + 1) to increase the value of the counter by 1 for each iteration. VBA does not have any increment operator — the counter must be an explicit addition statement.

The choice of a For Loop or Do Loop on a script like this is a matter of preference for readability or future needs. If you anticipate that this script will serve one purpose, such as printing a basic multiplication table, a For Loop may be the best implementation. If you anticipate scaling the script in the future, such as adding other conditions as it prints, then a Do Loop may be a a more effective approach.

Note that unlike its For Loop counterpart, a nested Do Loop will not return an error if a duplicate counter variable is used. There may be some advanced application for utilizing the same counter variable, but for most basic needs, it will lead to unintended results and overflow errors.

Using Conditionals within Nested Loops

While Do Loops offer flexibility in terms of determining what should or shouldn’t be printed, there may be situations where conditional statements nested within loops are needed. In the following example, the previous Do Loop script contains an added conditional statement to test if the the first factor i is equal to the second factor j. If the conditional returns True, a statement turns the color of the cell blue.

The result:

A conditional highlights numbers with equal factors.
Multiplication table created by nested loops. A conditional highlights numbers with equal factors.

Nested Loop Inside Nested Loops

VBA does provide the ability to nest any number of loops within loops. While it’s great that VBA–and most languages, for the matter–are flexible to this extent, you should approach multiple nested levels with caution. Complex loop structures can lead to spaghetti code; reducing code readability and increasing potential errors. In many cases, complex loop structures can be trimmed down in size with the proper use of conditional statements and functions.

Regardless, there may be situations where a complex loop structure cannot be avoided. In an effort to provide a quick overview of all loop types, the following script contains a For Each Loop, within a Do Loop, within a For Loop. This script is effectively very similar to the multiplication table script, with the difference being instead of printing the multiplication product, an array of common household pets (array commonPets) is printed in each cell.

The new For Each loop (highlighted) iterates through the array defined earlier in the script. As an added touch, a new conditional–using the modulus (Mod) operator–highlights all data on even numbered rows.

Output from a 3 level nested loop.
Output from a 3 level nested loop.

Avoid Spaghetti Code

To further prove the point of avoiding many levels of nesting, the nesting in the previous example can be easily reduced by one level. By moving the For Each loop from inside the Do Loop, to a place before For Loop, the petGroup concatenated string will be generated prior to the entry to the main printing script (also remember to remove the “petGroup = Empty” line).

The previous script used additional overhead during each iteration to reproduce the same string from the same array. While it was presented for demonstration purposes, it’s important to scrutinize your own code to seek out inefficiencies like the previous example. A good place to start is within loops, seeking code that can be moved from the inside of a loop, to outside of a loop.

Other Notes

  • There are many situations where VBA’s native functionality can prevent the need for loops. For example, when copying data from one sheet to another, ranges (as an object) can be moved with one command.
  • If a For Loop contains many conditionals, think about transitioning to a Do Loop. As a For Loop iterates through false statements, system overhead is being wasted. If a Do Loop conditional test returns False, that iteration is skipped entirely.

Leave a Reply