Using a Do Loop to Iterate Through Spreadsheet Data

Do Loops are an effective means of iterating through a set of data where the number of iterations is not known. Given the versatility and easy for adding and removing data on an Excel spreadsheet, a Do Loop is an extremely valuable tool when used effectively in VBA.

Overview

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

The previous posts on For Loops and For Each Loops discuss how to loop through sets of data or collections if the total iterations required is known prior to loop entry. You may encounter situations where this approach is limiting. A Do Loop allows you to get around those limitations. Some of the uses of Do Loops include:

  • Performing an action on an iteration where one or many conditional tests returns True.
  • Looping through data where the number of iterations is not known prior to loop entry.
  • Ensuring that at least one iteration is completed, regardless of condition test.

The foundation of a Do Loop relies on the first item in the list: performing an action on an iteration where one or many conditional tests return True. Whereas a similar effect can be achieved within For Loops by nesting conditional statements within the loop, a Do Loop will actually contain the condition within the Loop statement. The conditional test is similar in syntax to a traditional VBA If statement.

The Do Loop comes in two main flavors: Do While and Do Until.

Do While

A Do While loop will run a loop while a condition is true. The basic syntax is is follows:

In a Do While loop, the loop will continue to run while the condition is true. In the following example, a Do While loop runs through a list of the top ten most populous EU Countries.

Do loop example.
List of largest European countries and population.

The following Do Loop will return a message box showing the user the country name and population while (If) the country’s population is greater than 20,000,000

The loop starts on row 2 (below heading), and loops through each population value until it reaches a value while the value is greater than 20,000,000. Once it reaches a value of less than or equal to 20,000,000, the conditional test fails and the loop is exited.

To further exemplify this, if Greece–the smallest of the group at about 11,000,000–were moved to the 2nd position below Germany, the script would only return the population of Germany, as the test would fail during the 2nd iteration. The Do Loop will exit and not check any additional values in the list.

Do Until

The Do Until loop is similar to the Do While loop in syntax, however, it waits until it reaches a certain value to return False. Let’s say we changed the list in the first example from largest population to smallest, to smallest population to largest. This would place Greece on top while Germany is on the bottom. We then want to write a program that returns messages of the EU countries with populations less than 20,000,000. The following script will accomplish that.

Do Loop Pre-test vs. Post-test

The two examples in the previous section used a pre-test to handle loop testing. In a pre-test, a condition is tested prior to entry to the loop. If the test returns False on the first run, the loop is skipped entirely.

Alternatively, a post-test will test a condition after the nested statements are executed. This is an effective approach if you want to always run the first iteration, or if you are working with dynamic data within the workbook. In the following example, a Do While loop will always display the first/largest city, even though the post-test returns False.

After initially running the MsgBox script, the post-test returns false and forces (Germany’s population is greater than $20 Million) control flow to exit the loop without returning to the top of the loop. The following is a comparison of the control flow between a pre-test and a post-test.

Pre-test Do Loop.
Pre-test Do Loop.
Post-test Do Loop.
Post-test Do Loop.

Unknown Number of Iterations

Do Loops are particularly useful in Excel, given that there are often situations where the number of required iterations is not know. Two common scenarios where you may implement this include (1) returning a value from a dataset based on a specific unique name or (2) determining the last row of data.

The following script counts the number of countries in the European country/population list. Using the global IsEmpty function, the test will return True upon detecting the first empty cell.

Upon exiting the loop, a message box is displays the number of countries counted. This technique is extremely useful–specifically for Do Until Loops–as it prevents situations where a Do Loop iterates past the data. If the counter is casted as an integer, an overflow error will appear after the 32,768 count. If it’s casted as a Long, an error will appear once the loop attempts to go beyond the last row of the spreadsheet. In summary, it’s important to have a control in place that prevents Do Loops from going beyond the data.

Including Logical Operators

Similar to conditional statements, Do Loops permit the use of logical operators to combine various criteria. The following script will return false when either it reaches a country with a population below 20 million, OR it reaches the end of the list.

The IsEmpty function prevents the script from running to the integer limit of 32,768. This is why, in many cases, it’s important to include a IsEmpty as failsafe within a Do Loop.

Other Notes

  • Any combination of logical operators can be used in a Do Loop’s condition. For complex statements, it’s important to use brackets to group sets of conditions.
  • Unlike For Loops, Do Loops do not have any built in counters.
  • VBA does not permit the use of the unary incriment/decriment operators (i++ or i–). Instead, you must use i = i + 1 or i = i – 1.

 

Leave a Reply