The Ultimate Guide to Loops in Excel VBA
“Nearly all men can stand adversity, but if you want to test a man’s character, give him power” – Abraham Lincoln.
© Heysues23 | Dreamstime.com – Fair Rides Photo
Introduction
Loops are by far the most powerful component of VBA. They are the rocket fuel of your Macros. They can perform tasks in milliseconds that would take humans hours. They dramatically reduce the lines of code you need.
If you have never used loops before then this post is a great place to start. The aim here is to provide you with an in-depth guide to using loops, written in plain English.
The following questions will be addressed
What are Loops?
Why you need them?
When should you use them?
How do they work?
Which one should you in a given situation?
The first section of the post provides a quick guide to loops. If you are here looking for some quick info on loops then this is for you.
Otherwise you can skip to the second section where we start with a very important question – what are loops and why do we need them?
Quick Guide To Loops
There are 4 types of loops in VBA. The Do Loop can be used in 4 ways. The following table provides a quick guide to loops
A quick guide to loops in Excel VBA
What are Loops and Why Do You Need Them?
A loop is simply a way of running the same lines of code a number of times. Obviously running the same code over and over would give the same result. What is important to understand is that the lines of code normally contain a variable that changes slightly each time.
For example a loop could write to A1, then A2, A3 and so on. The slight change each time is the row.
Let’s look at a simple example.
Example 1: Printing 1 to 5
The following code prints the values 1 to 5 in the Immediate Window.
Debug.Print 1
Debug.Print 2
Debug.Print 3
Debug.Print 4
Debug.Print 5
The Immediate Window
If you have not used the Immediate Window before then this section will get you up to speed quickly.
Note: The function Debug.Print writes values to the Immediate Window. To view this window select View->Immediate Window from the menu( Shortcut is Ctrl + G)
Example 2: Printing 1 to 20
Now imagine we want to print out the numbers 1 to 20. We would need to add 15 more lines using the same code. However using a loop we only need to write Debug.Print once.
For i = 1 To 20
Debug.Print i
Next i
The output is
Output
If we needed print the numbers 1 to 1000 then we only need to change the 20 to 1000. Normally in code you would use a variable instead of the values 20 or 1000. This gives you greater flexibility. It allows you to decide the number of times you wish to run the loop when the Macro runs. The following example explains this.
Example 3: Counting Fruit Sold
A common task in Excel is read to the last row of a sheet with data. So the way you would do this is by first finding the last row with data and then using the number of this row in the loop. Then if more lines of data are added the code will still work fine.
Let’s have a look how you would do this with code. Imagine you receive a sheet with a list of fruit types that have been sold each day. You want to count the number of Oranges sold and this list will vary in size depending on sales. The following screenshot shows an example of this list
Sample Data of Fruit Sales
We can use the code to count the oranges
Sub CountFruit()
' Get the last row with text
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim i As Long, Total As Long
' Use LastRow in loop
For i = 2 To LastRow
' Check if cell has text "Orange"
If Cells(i, 1).Value = "Oranges" Then
' Add value in column B to total
Total = Total + Cells(i, 2).Value
End If
Next i
' Print total
Debug.Print "Total oranges sold was:"; Total
End Sub
You can try this code for yourself. Change the number of fruit items and you will see that the code still works fine.
If you were to increase the number fruit items to a large value like 10,000 then you hardly notice the difference in the time it takes to run – almost instantly. Loops are super fast. This is what makes them so powerful. Imagine performing a manual task on 10,000 cells. It would take a considerable amount of time.
You may be thinking that excel functions like Sum and Count are powerful as they can also perform a task with a large number of cells quickly. This is true but if you looked at the original code of the function you would find it contains a loop.
Advantages
To conclude this section we will list the major advantages of using loops
They reduce the lines code you need
They are flexible
They are fast
In the next sections we will look at the different types of loops and how to use them.
The For Loop
In VBA the For loop is the most common loop you will use. The For Loop is used when you can determine the number of times it will be run. For example if you want to repeat something twenty times.
Format of the Loop
A for loop is constructed as follows
For = to
Next
The start and end values can be variables. Also the variable after Next is optional but it is useful and it makes it clear which for loop it belongs to.
How a Loop Works
Let’s look at a simple for loop that prints the numbers 1 to 3
Dim i As Long
For i = 1 To 3
Debug.Print i
Next i
How this code works is as follows
i is set to 1
The value of i(now 1) is printed
i is set to 2
The value of i(now 2) is printed
i is set to 3
The value of i(now 3) is printed
If we did not use a loop then the equivalent code would be
Dim i As Long
i = i + 1
Debug.Print i
i = i + 1
Debug.Print i
i = i + 1
Debug.Print i
The “i = i + 1″ line is used to add 1 to i and is a common way in programming to update a counter.
Using Step
You can see that i is increased by one each time. This is the default. You can specify this interval using Step. The next example shows you how to do this
' Prints the even numbers i.e. 2,4,6,8 ... 20
For i = 2 To 20 Step 2
Debug.Print i
Next i
You can use a negative number with Step which will count in reverse
' Prints the even numbers in reverse i.e. 20,18,16,14 ... 2
For i = 20 To 2 Step -2
Debug.Print i
Next i
Note if Step is positive then your starting number must be lower than you ending number. The following loop will not run because the starting number 20 tells VBA it has already reached the target value 10.
' Will not run as starting number already greater than 10
For i = 20 To 10 Step 1
Debug.Print i
Next i
If Step is negative then the start number must be greater than the end number.
Exit For
Sometimes you may want to leave the loop earlier if a certain condition occurs. For example if you read bad data. You can use Exit For to automatically leave the loop as shown in the following code
For i = 1 To 1000
' If cell is blank then exit for
If Cells(i, 1) = "" Then
MsgBox "Blank Cell found - Data error"
Exit For
Else
Debug.Print Cells(i, 1)
End If
Next i
Using For with a Collection
The for loop can also be used to read items in a collection. In the following example we display the name of all the open workbooks
Dim i As Long
For i = 1 To Workbooks.Count
Debug.Print Workbooks(i).FullName
Next i
Using a Loop with a Loop
Sometimes you may want to use a loop within a loop. An example of this would be where you you want to print the names of the worksheets of each open workbook.
The first loop would go through each workbook. Each time this loop runs it would use a second loop to go through all the worksheets of that workbook. It is actually much easier to do than it sounds. The following code shows how
Sub ListWorksheets()
Dim i As Long, j As Long
' First Loop goes through all workbooks
For i = 1 To Workbooks.Count
' Second loop goes through all the worksheets of workbook(i)
For j = 1 To Workbooks(i).Worksheets.Count
Debug.Print Workbooks(i).Name + ":" + Worksheets(i).Name
Next j
Next i
End Sub
This works as follows
The first loop set i to 1
The second loop then uses the workbook at 1 to go through the worksheets.
The first loop sets i to 2
The second loop then uses the workbook at 2 to go through the worksheets.
and so on
It the next section we will use a For Each loop to perform the same task. You will find the For Each version much easier to read.
The For Each Loop
The For Each loop is used to read items from a collection or an array. We can can use the For Each loop to access all the open workbooks
Dim wk As Workbook
For Each wk In Workbooks
Debug.Print wk.FullName
Next wk
Format of the For Each Loop
For Each in
Next
To create a For Each loop we need a variable of the same type that the collection holds. In the example here we created a variable of type Workbook. If the collection has different types of items we can declare the variable as a variant.
VBA contains a collection called Sheets. This is a collection of sheets of type Worksheet(normal) and Chart(when you move a chart to be a full sheet). To go through this you would declare the variable as a variant. The following code used For Each to print out the name of all the sheets in the current workbook
Dim sh As Variant
For Each sh In ThisWorkbook.Sheets
Debug.Print sh.Name
Next sh
Order of Items
For Each goes through items in one way only. For example if you go through all the worksheets in a workbook it will always go through from left to right. If you go through a range it will start at the lowest cell e.g. Range(A1:A10) will return A1,A2,A3 etc. This means if you want any other order then you need to use the For loop.
Both loops in the following will read the worksheets from left to right.
' Both loops read the worksheets from left to right
Dim wk As Worksheet
For Each wk In ThisWorkbook.Worksheets
Debug.Print wk.Name
Next
Dim i As Long
For i = 1 To ThisWorkbook.Worksheets.Count
Debug.Print ThisWorkbook.Worksheets(i).Name
Next
As you can see the For Each loop is neater to write. However if you want to read the sheets in any other order e.g. right to left then you have to use the for loop.
' Reading the worksheets from right to left
Dim i As Long
For i = ThisWorkbook.Worksheets.Count To 1 Step -1
Debug.Print ThisWorkbook.Worksheets(i).Name
Next
For Each is Read-Only
Another attribute of the For Each loop is that it is Read-Only. You cannot change the value. The following example demonstrates this
Sub UseForEach()
' Create array and add three values
Dim arr() As Variant
arr = Array("A", "B", "C")
Dim s As Variant
' Assigning s does not change the array item
For Each s In arr
' Changes what s is referring to - not value of array item
s = "Z"
Next
' Print items to show the array has remained unchanged
For Each s In arr
Debug.Print s
Next
End Sub
In the first loop we try to assign s to “Z”. When happens is that s is now referring the string “Z” and no longer to the item in the array.
In the second loop we print out the array and you can see that none of the values have changes.
When we use the For Loop we can change the array item. If we change the previous code to use the For Loop you it will change all the array values to “Z”
Sub UsingForWithArray()
' Create array and add three values
Dim arr() As Variant
arr = Array("A", "B", "C")
Dim i As Long
For i = LBound(arr) To UBound(arr)
' Changes value at position to Z
arr(i) = "Z"
Next
' Print items to show the array values have change
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next
End Sub
Which is Faster? For vs For Each
The For Each loop is widely considered faster than the For loop as it is. However I have been unable to find any concrete sources to back this up 100%. What is important to remember when considering speed is that
The processing power of modern computers means both loops work extremely fast.
You need to have a huge collection for speed to be an issue
Therefore most of the time the loop speed difference will not be an issue for you. If it is then the best thing to do is to try both loops and see which is faster.
Loop Within a Loop
We saw already that you can have a loop inside other loops. In our previous example we used For to read all the worksheets in all the open workbooks. This time we will use For Each to perform the same task
Sub ReadAllWorksheets()
Dim wk As Workbook, sh As Worksheet
' Read each workbook
For Each wk In Workbooks
' Read each worksheet in the wk workbook
For Each sh In wk.Worksheets
' Print workbook name and worksheet name
Debug.Print wk.Name + ": " + sh.Name
Next sh
Next wk
End Sub
As you can see this is a neater way of performing this task than using the For Loop.
This code run as follows:
Get the first Workbook from the Workbooks collection
Go through all the worksheets in this workbook
Print the workbook/worksheet details
Get the next workbooks in the collection
Repeat steps 2 to 3
Continue until no more workbooks are left in the collection
The next loop we will look at is the Do loop. This uses a condition to determine when it completes so first we will take a quick look at conditions.
Conditions
A condition is a statement that evaluates to true or false. They are mostly used with Loops and If statements. When you create a condition you use signs like >,<>,>=,=.
The following are examples of conditions
VBA Conditions
You may have noticed x=5 as a condition. This should not be confused with x=5 when used as an assignment. When equals is used in a condition it means “is the left equal to the right”. The following table demonstrates how equals is used in conditions and assignments
Use equals in a condition vs statement
The Do Loop
The Do loop can be used in four ways and so often causes confusion. There is only a slight difference in each of these four ways.
The difference between the For loop and this loop is that For loop specifies the number of times it will run. The other loops run until a condition is met.
A Do Loop Example
Lets look at a simple example to explain this. Imagine you want to get a series of items from the user. Each time the user enters an item you print it to the immediate window. When the user enters a blank string the macro terminates.
In this case the For loop would not be suitable as you do not know how many items the user will enter. The user could enter the blank string first or hundredth time. For this type of situation you would use a Do loop. The following code shows an example of this
Dim sCommand As String
Do
' Get user input
sCommand = InputBox("Please enter item")
' Print to Immediate Window(Ctrl G to view)
Debug.Print sCommand
Loop Until sCommand = ""
The code enters the loop and runs the code. When it reaches the “Loop Until” line it checks the condition
sCommand = ""
If the condition is true then it exits the loop. If the condition is false it returns to the start of the loop.
Do Loop Until Format
The format of this loop is
Do
Loop Until
You can also place the condition at the start of the loop
Do Until
Loop
The different between these two loops is that the first will run 1 or more times and the second will run 0 or more times. In other words the first will run once before checking the condition so it will always run at least once.
In our example the first loop was used because we need to get a value from the user before we check it. In the following example we use both versions of the loop. The loop will run until the user enters the letter ‘n’
Sub GetInput()
Dim sCommand As String
' Condition at start
Do Until sCommand = "n"
sCommand = InputBox("Please enter item for Loop 1")
Loop
' Condition at end
Do
sCommand = InputBox("Please enter item for Loop 2")
Loop Until sCommand = "n"
End Sub
In this case both loops will behave the same. However if sCommand is equal to ‘n’ before the Do Until loop it will never run. The Do loop will always run at least once.
Sub GetInput2()
Dim sCommand As String
sCommand = "n"
' Loop will not run as command is "n"
Do Until sCommand = "n"
sCommand = InputBox("Please enter item for Loop 1")
Loop
' Loop will still run at least once
Do
sCommand = InputBox("Please enter item for Loop 2")
Loop Until sCommand = "n"
End Sub
Using While instead of Until
You can use While instead of Until in the do loop. This is the same as using Until except for one slight difference – While uses the opposite condition. Until and While are used the same as they are used in English. For example
Leave the clothes on the line Until it rains
Leave the clothes on the line While it does not rain
another example
Stay in bed Until it is light
Stay in bed While it is dark
yet another example
repeat Until the count equals ten
repeat While the count count is less than ten
As you can see – using Until and While is just the opposite way of writing the same condition.
The Do While Loop Format
The only difference in the format of the loop is that you replace Until with While.
Do
Loop While
Do While
Loop
The following code shows the ‘While’ and ‘Until’ loops side by side. As you can see the only difference is the condition is reversed. Note that ‘<>’ means ‘does not equal’.
Sub GetInput()
Dim sCommand As String
' Condition at start
Do Until sCommand = "n"
sCommand = InputBox("Please enter item for Loop 1")
Loop
Do While sCommand <> "n"
sCommand = InputBox("Please enter item for Loop 1")
Loop
' Condition at end
Do
sCommand = InputBox("Please enter item for Loop 2")
Loop Until sCommand = "n"
Do
sCommand = InputBox("Please enter item for Loop 2")
Loop While sCommand <> "n"
End Sub
The first loop says Loop until sCommand equals ‘n’. The second loop says loop while sCommand does not equal ‘n’.
Example: Checking Objects
An example of where Until and While are useful is for checking objects. When an object has not been assigned it has the value Nothing. So when we declare workbook in the following example it has a value of nothing until we assign it to a workbook
Dim wrk As Workbook
The opposite of “Nothing” is “Not Nothing” which can be confusing. Take the following example. Imagine we have two functions called GetFirstWorkbook and GetNextWorkbook which return some workbook objects. The code will print the name of the workbook until the functions do not return a workbook. You can see the sample code here
Dim wrk As Workbook
Set wrk = GetFirstWorkbook()
Do Until wrk Is Nothing
Debug.Print wrk.Name
Set wrk = GetNextWorkbook()
Loop
To write this code using Do While would be more confusing as the condition is Not Is Nothing
Dim wrk As Workbook
Set wrk = GetFirstWorkbook()
Do While Not wrk Is Nothing
Debug.Print wrk.Name
Set wrk = GetNextWorkbook()
Loop
This makes the code clearer and having clear conditions is always a good thing. To be honest this is a very small difference and choosing between While and Until really comes down to a personal choice.
While Wend
This loop is in VBA to make it compatible with older code. Microsoft recommends that you use the Do loops as they are more structured.
From MSDN: “The Do…Loop statement provides a more structured and flexible way to perform looping.”
Format of the While Wend Loop
The While loop has the following format
While
Wend
While Wend vs Do
The different between While and the Do Loop is :
1. While can only have a condition at the start of the loop
2. There is not statement to exit a While loop like Exit For or Exit Do
The condition for the While loop is the same as for the Do While loop. The two loops in the code below perform exactly the same way
Sub GetInput()
Dim sCommand As String
Do While sCommand <> "n"
sCommand = InputBox("Please enter item for Loop 1")
Loop
While sCommand <> "n"
sCommand = InputBox("Please enter item for Loop 2")
Wend
End Sub
Infinite Loop
Even if you have never written code in your life I’m sure you’ve heard the phrase Infinite Loop. This is a loop where the condition will never be met. It normally happens when you forget to update the count.
The following code shows an infinite loop
Dim cnt As Long
cnt = 1
' Do not run - this is an infinite loop
Do While cnt < 5
Loop
In this example cnt is set to 1 but it is never updated. Therefore the condition will never be met – cnt will always be less than 5.
In the following code the cnt is being updated each time so the condition will be met.
Dim cnt As Long
cnt = 1
Do While cnt < 5
cnt = cnt + 1
Loop
As you can see using a For Loop is safer for counting as it automatically updates the count in a loop. The following is the same loop using For.
Dim i As Long
For i = 1 To 4
Next i
This is clearly a better way of doing it. The For Loop sets the initial value, condition and count in one line.
Of course it is possible to have an infinite loop using For – It just takes a bit more effort 
Dim i As Long
' DO NOT RUN - Infinite Loop
For i = 1 To 4
' i will never each 4
i = 1
Next i
Dealing With an Infinite Loop
When you have an infinite loop – VBA will not give an error. You code will keep running and the Visual Basic editor will not respond.
In the old days you could break out of a loop by simply pressing Ctrl and Break. Nowadays different Laptops use different key combinations for the same result. It is a good idea to know what this is for your laptop so that if an infinite loop occurs you can stop the code easily.
You can also break out of a loop by killing the process. Press Ctrl+Shift+Esc. Under the Processes tab look for Excel/Microsoft Excel. Right click on this and select “End Process”. This will close Excel and you may lose some work – so it’s much better to use Ctrl+Break or it’s equivalent.
Using Worksheet Functions Instead of Loops
Sometimes you can use a worksheet function instead of using a loop. For example, imagine you wanted to add the values in a list of cells. You could do this using a loop but it would be more efficient to use the worksheet function Sum. This is quicker and saves you a lot of code.
It is very easy to use the Worksheet functions. The following is an example of using Sum and Count. Remember that in VBA the argument they take is a range object and not a string. In other words you must use Range(“A1:A10″) rather than “A1:A10″.
Sub WorksheetFunctions()
Debug.Print WorksheetFunction.Sum(Range("A1:A10"))
Debug.Print WorksheetFunction.Count(Range("A1:A10"))
End Sub
The following examples uses a loop to perform the same action. As you can see it is a much longer way of achieving the same goal
Sub SumWithLoop()
Dim total As Long, count As Long
Dim rg As Range
For Each rg In Range("A1:A10")
' Total
total = total + rg
' Count
If rg <> "" Then
count = count + 1
End If
Next rg
Debug.Print total
Debug.Print count
End Sub
Summary
There are four types of loops in VBA:
For Next
For Each Next
Do Loop
While Wend
The For Loop is the most commonly used in VBA
The For loop is flexible as you can determine the size and order to read through.
The For Each is used to read through a collection. You cannot change the order. It is neater to write than a For Loop.
For Each is considered faster than the For Loop but with modern processing power this not an issue unless you have extremely large collections.
For Each is read only – you cannot change the value of the item
For can read and write – you can change the value of the item
The While Wend loop is obsolete and you can use the Do Loop instead.
The Do loop can be used 4 ways. With a While/Until condition at the start or end of the loop.
While and Until use opposite conditions in a Do loop.
Infinite Loops occur if your loop condition will never be met
Sometimes using a worksheet function is more efficient than using a loop
I hope you enjoyed this post and found it beneficial. You may also find the posts about Arrays or Cells very useful.
