VBA Do Loop

Do loops to improve the efficiency of their financial models

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

What is Do Loop in VBA?

A VBA Do Loop is a subsection within a macro that will “loop” or repeat until some specific criteria are met. The coder can set the loop to repeat a specified number of times until a certain variable exceeds a threshold value or until a specific cell is activated. In fact, loops are quite robust in that the user can get creative with the criteria that end the loop, as long as he or she understands how VBA loops work. This can become very useful in making a financial model more efficient.

A Do Loop statement will have a beginning statement and an ending statement, with the code to perform contained within these two statements. This is like the structure of a macro, where the entirety of a macro code is held inside the Sub statement that starts a macro and the End Sub statement that ends it. Macros can contain several separate loops, and loops can contain loops within themselves, indefinitely.

To learn more, launch our Excel VBA financial modeling course online!

Different Loop Types

There are several different types of loops, and each type performs differently.

Do Until Loop

The Do Until Loop will continue repeating until the criteria is true. The criteriion is inserted right after the “do until” statement. The loop ends with the “Loop” statement. A simple example of this loop is to increase a counter until it reaches a specified number, as in the example below.

Dim n as Integer

N = 0

Do Until n = 10

    n = n + 1

Loop

In this example, we’ve set an integer variable known as “n”. The macro initially stores a value of 0 in n. When it runs into the Do Until loop, the criteria n = 10 is not true, so the process within the loop is run. The first iteration adds 1 to n, turning the value into 1. Since n is still not 10, the process will repeat 10 times, until n is 10. Once n = 10, the macro will pass the loop and continue with the rest of the macro.

This type of loop using an integer is useful for running a process a specified number of times. For example, you may wish to fill the first ten rows in column A with the text “Company n.” This is done as follows:

Dim n as Integer

N = 0

Do Until n = 10

    n = n + 1

    Range(“A” & n).Value = “Company “ & n

Loop

Running this macro will fill Cell A1 with Company 1, Cell A2 with Company 2, and so on until Cell A10 is filled with Company 10. At Cell A10, the do until criteria n = 10 will have been met, and so the macro will end the loop and move on.

VBA Do Loop

To learn more, launch our Excel VBA financial modeling course online!

Do While Loop

As opposed to the do until loop, the Do While loop will perform the loop until the criteria become false. In other words, the Do Loop will perform while the criteria are met. It looks to be the exact opposite of the do until loop.

If we were to use the exact same macro example above, but replace do until with do while, the macro will simply skip the loop. It is because n is 0 at the start of the process, and the loop will only perform while n = 10. Since n can only reach 10 through the loop process, it can never reach 10, and so the loop will be skipped over.

Instead, to perform the same process within the do while loop, we should use an inequality.

Dim n as Integer

N = 0

Do while n < 11

    n = n + 1

    Range(“A” & n).Value = “Company “ & n

Loop

Notice that the inequality uses 11 instead of 10. This is because the macro needs to execute until it reaches Company 10. If the loop criteria was set to n < 10, the loop would end at Company 9 since it will only perform while n is less than 10. Since 10 is not less than 10, it will end right before it reaches Company 10.

Do Loop example

Multiple Criteria using AND and OR with Do Loop

With these loops, you can also include several criteria by including two expressions separated by the AND or OR operator. These operators are quite self-explanatory.

A do until loop with two criteria separated by AND will perform until both criteria are met. A do until loop with two criteria separated by OR will perform until either of the criteria are met.

A do while loop with two AND criteria will perform while both criteria are met.

A do while loop with two OR criteria will perform while at least one of the criteria is met.

Using multiple criteria may become very important in financial modeling. For example, a user may require that two leverage ratios are above certain values before a certain macro runs.

Non-Number Criteria in Do Loop

In a For Loop, the above process can actually be simplified. The for loop has the advantage over do loops because it automatically creates a variable in the place of n in one simple step. However, do loops have certain upsides compared to for loops.

While for loops can only be generally used with number variables, do loops can have criteria that use other data types, like strings and dates. A do loop can be set to run only until a certain data arrives. For example, perhaps the user would like the macro to only run for the year 2017, and to not run when the year passes. This can be accomplished with a date variable and setting the criteria as Do Until [DateVariable] = 1/1/2018.

Perhaps the user would, instead, like the macro only to run while a certain cell is empty, and not run when that cell is filled. This can be accomplished with the criteria as Do Until Range(“A5”).Value = “”. The “” refers to a blank cell. Note: a cell containing a space may seem blank, but is not deemed as a blank cell by VBA.

To learn more, launch our Excel VBA financial modeling course online!

Additional Resources

Thank you for reading this CFI guide on how to create a Do Loop in VBA. To continue advancing your skills, these additional CFI resources will be helpful:

0 search results for ‘