Let’s kick things up a bit! We will be using VBA commands that are more specific to Excel in this lesson, while building on previous concepts, and introducing you to loops.
Loops
Loops are a way to repeat a set of commands in a software language. Imagine you want to do something 10 times. Instead of coding a set of commands 10 times, you can program one single loop to do it for you.
There are three essential loops in VBA:
- For Loop
- Do Until Loop
- Do While Loop
These loops are listed in the order that I most frequently use them.
They all do the same thing, essentially. Each one of them are set up differently and each one terminates differently. You can’t make a wrong choice, per se, when it comes to selecting which type of loop to use. There may be instances where a certain loop is easier than another, and this will be apparent as you’re coding. The choice of which type of loop to use is up to you.
Let’s turn to Excel and VBA as an example and illustrate the three types of loops.
In this example, we will programmatically write text in cells A1:A10. Look at the code samples below:
For Loop
Option Explicit
Sub LoopHelloWorld_For()
Dim i As Integer
Dim strMessage As String
strMessage = "Hello World"
For i = 1 To 10
ThisWorkbook.ActiveSheet.Cells(i, 1).Value = strMessage
Next i
End Sub
The For Loop is what I use most frequently because of its ease of use and set up, for me anyway.
First thing you do is set a counter variable. In this case, the counter variable is i. What I am calling a counter variable will serve as a counter for 1 through 10. The variable’s value is defined in the loop itself. The first time the loop is executed i = 1, the second time the loop is executed, i = 2, and so on, until i = 10, then the loop exits.
By the end of this example, cells A1:A10 should have “Hello World” written in each cell of the active worksheet of whatever workbook you’re executing the code in.
For this example, execute the code by pressing the button F8 instead of F5. F5 just runs through the program entirely. By pressing F8 you’ll able to step through the code and see what it does line by line. You’ll have to press F8 repeatedly. You’ll executing code this way is working correctly when you see a yellow highlighted line in your code screen. Keep pressing F8 until the code has finished executing.
Now let’s look at a different type of loop.
Do Until
Option Explicit
Sub LoopHelloWorld_DoUntil()
Dim i As Integer
Dim strMessage As String
strMessage = "Hey Earth!"
i = 1
Do Until i > 10
ThisWorkbook.ActiveSheet.Cells(i, 1).Value = strMessage
i = i + 1
Loop
End Sub
And for the final loop type we are covering in this lesson:
Do While
Option Explicit
Sub LoopHelloWorld_DoWhile()
Dim i As Integer
Dim strMessage As String
strMessage = "Hi World"
i = 1
Do While i < 11
ThisWorkbook.ActiveSheet.Cells(i, 1).Value = strMessage
i = i + 1
Loop
End Sub
Do While and Do Until loops are set up differently than For loops. Just like in a For loop, you have to set up a counter variable. We are still using “i” as integer for this. But you have manually set up the counter function.
The first thing you have to do is set your counter variable equal to the starting number you are concerned with. Since we are wanting to fill rows A1:A10 with some text, we are going to start with the number “1.”
i = 1
You set the initial value of your counter variable outside of the loop, usually just before the loop starts.
Once inside the loop, you must “help” the counter loop by adding 1 to it on each iteration of the loop. The For loop does this for us automatically. In Do loops, we must do this math manually, or we run the risk of entering into an infinite loop, and Excel crashing.
In both of the DO loops, you can see this as the following:
i = i + 1
That might seem confusing but think about it algebraically for a minute. If i = 1, then imagine it as follows:
i = 1 + 1
In the example above, the variable “i” will be reset to 2. Likewise, if i = 2, imagine the following:
i = 2 + 1
In the example above, “i” will be set to equal 3.
Whatever is left of the equal sign will be defined, or in this case, redefined using whatever is on the right. This will
Do While and Do Until are also slightly different and serve different purposes.
Do Until is the Do Loop type I use most frequently. It seems the most intuitive for me with most instances where I would need a loop. Do Until does something until a condition is met. In this case, the condition is to do something until i = 10.
Do While is different. It assumes a condition is already true and performs a loop while it remains that way. In this case, I am telling the Do While loop to fill rows with text while the variable i < 11. Slightly different, and generally slightly less intuitive.
Some Code Review
Loops aside, take a look at this line of VBA:
ThisWorkbook.ActiveSheet.Cells(i, 1).Value = strMessage
We are looking at new code that hasn’t been previously covered in earlier lessons. In previous lessons, we focused on using the message box to return commands back to us. What we have here is a snippet of actual Excel-specific VBA code. In plain English, this statement is saying “on this workbook, on whatever worksheet is active, in cell row i and cell column 1 (Column 1 = Column A), set the value to the variable strMessage.” The row number is represented by the variable i which is also used as the counter for the loop. As you step through (by pressing F8) any one of these loops, and watch each successive row get filled with the value of the variable strMessage, pay attention to the value of i. The row value is represented by a variable and the column is represented by a fixed number. This is how I effectively place a value in each row, in a single column.
Try This!
Pick your favorite loop and add the following code inside the loop. For either of the Do loops, add this code before the i = i + 1 line.
This code will help you track the value of the variable i:
ThisWorkbook.ActiveSheet.Cells(i, 2).Value = i
What do you think will happen? The value of i will be written into rows 1 - 10 of column B. In this line of code, column B is represented as column 2.
What you’ve learned
This covered a basics of loops, one of the fundamental building blocks of effectively coding in any software language. Loops allow the programmer to write repetitive commands without having to replicate code over and over again. In this lesson, we have covered the bare basics for frequently used loop-types that should serve almost all of your needs in VBA:
- For Loop
- Do Until Loop
- Do While Loop
What’s next
The next lesson covers Conditional Logic which is another core component to programming VBA, or any software language.
Download the workbook for this lesson
Navigation