VBA Do Loop

Do loops to improve the efficiency of their financial models

Do Loop in VBA

A Do Loop is a sub section within a macro that will “loop” or repeat, until some specific criteria is 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 ends 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 itself, 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 criteria is inserted right after the “do until” statement. The loop ends with the “Loop” statement. A simple exam 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 is met. This 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. This 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 modelling. 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 uses 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!

 

Learn more!