Excel VBA LoopsWhat are they and how to use them
With VBA, you can make performing repetitive tasks a tad less annoying.
For example, you want to delete the contents in all odd-numbered rows.
You can do this the tedious way by manually deleting the contents odd row by odd row.
Or you can save yourself from the pain of having to do it over and over again with the help of a VBA code.
And since we’re talking about making performing repetitive tasks a tad less tedious, it’s only suitable that we talk about loops.
VBA loops, to be exact.
VBA loops are among the most important and powerful programming tools in VBA.
As the name implies, they allow you to loop/repeat a code block multiple times until the specified condition is satisfied or a specific point is attained.
When the condition is met or the specific point is attained, only then will the program proceed to execute the next section of the VBA code.
If you plan on learning how to use VBA in Excel, you might as well learn about VBA loops – what they are and how to use them efficiently.
They can help make a lot of tasks less burdensome.
Here are some examples of how VBA loops can help:
- Loop through all worksheets to check for charts then transfer said charts to a specific worksheet
- Loop through all open workbooks and save each of them
- Looping through all the odd rows of the worksheet and applying specific formatting to them
- Loop through all even columns and delete the contents within them
- Loop through all worksheets and protect or unprotect each of them
And those are just some of the things you can do with VBA loops in Excel. Interested? Read on to find out more about VBA loops.
The Four VBA Loops
There are primarily four VBA loops you can use in Excel:
- Do While
- Do Until
- For Each
- For Next
We’ll be discussing what each loop does one by one in this article.
The “Do While” Loop
With the “Do While” loop, you can indefinitely loop a section of a code so long as the condition is met (or is TRUE).
It will only stop the loop once the condition is no longer met.
There are two ways you can write “Do While” into your code.
Do While [Condition Statement]
[Code block to execute]
Loop
In this version, the condition statement is checked first before running the loop. This means that if the condition statement isn’t met or is FALSE, the loop will not run.
The other version is this:
Do
[Code block to execute]
Loop While [Condition Statement]
In this version, the code block is executed first. Then, the code will check if the condition statement is met or is TRUE.
If it isn’t met or it’s FALSE, then the loop will end.
The main difference between this one and the other version is that it will always run the code block at least once even if the condition statement isn’t met or is FALSE.
Sample Application
Suppose you want to enter all the dates of the current month in one column of the worksheet.
You can do this with the following VBA Code:
Sub EnterCurrentMonthDates()
Dim CMDate As Date
Dim i As Integer
i = 0
CMDate = DateSerial(Year(Date), Month(Date), 1)
Do While Month(CMDate) = Month(Date)
Range("A1").Offset(i, 0) = CMDate
i = i + 1
CMDate = CMDate + 1
Loop
End Sub
This code will enter all the dates of the current month in a column of the active worksheet, particularly the A column starting on cell A1.
You can specify the starting cell on line 7 of the code (change A1 to the cell you want the 1st date to appear).
The loops will continue to run until the last date of the current month is entered.
The “Do Until” Loop
The “Do Until” loop pretty is the functional opposite of the “Do While” loop.
A condition statement is still included in a “Do Until” loop. I say it’s the opposite because it will indefinitely loop a section of a code so long as the condition isn’t met yet (or is FALSE). It will only stop the loop once the condition is met or evaluated to TRUE.
Just like the “Do While” loop, there are two ways you can write a “Do Until” loop into a code.
The first version goes like this:
Do Until [Condition Statement]
[Code block to execute]
Loop
In this version, the condition statement is checked first before running the loop. This means that if the condition statement is met or is TRUE, the loop will not run.
The other version is this:
Do
[Code block to execute]
Loop Until [Condition Statement]
In this version, the code block is executed first. Then, the code will check if the condition statement is met or not.
If it isn’t met or it’s FALSE, then the loop will continue to run. If it is TRUE, then the loop will end.
Unlike in the other version, the version of the loop will always run the code block at least once even if the condition statement is met or is TRUE.
Sample Application
Suppose you want to add up the first ten positive integers.
Rather than doing it manually, you can always use a VBA code that incorporates the “Do Until” Loop.
You will need to set the condition statement so that the loop stops if the integer being added is greater than 10.
Here is a VBA code that will do such a task:
Sub AddFirst10PositiveIntegers() Dim i As Integer i = 1 Do Until i > 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
This loop will continue until the value i becomes equal to 11, which is the first positive integer that is greater than 10.
When the condition is met (i > 10), the loop will stop. A message box will then appear which will show the result.
The “For Each” Loop
With the “For Each” loop, you can run through a set of objects (i.e. collection) while performing a specified action on each object.
Some of these sets of objects include:
- A collection of all the charts within the entire workbook
- All of the open workbooks
- All of the worksheets of the active workbook
- A collection of all the cells within a selected range of cells
- All the even rows or columns of the active worksheet
An example of what you can do with a “For Each” loop is to protect all the worksheets of the active workbook.
The “For Each” loop will go through the first worksheet, protect it, then go through the next worksheet, and then protect it.
This loop will continue until it goes through the last worksheet and protects it.
The “For Each” loop is also referred to as the “For Each-Next” loop. With this loop, you don’t have to know how many objects there are in a set or collection.
Here’s how to write a “For Each” loop in a code:
For Each element In collection
[Code Block to Execute]
Next [element]
Element refers to the object.
Sample Application
Suppose you want to protect all the worksheets in the active workbook.
Rather than going through each sheet one by one, you can use a VBA code that incorporates the “For Each” loop.
Here’s a code that can perform such a task:
Sub ProtectSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
In this code, we set ws (which stands for worksheet) as the element, while the collection is set to ActiveWorkbook.Worksheets (which refers to the worksheets of the active workbook).
This code will go through each worksheet and protect it. The loop will stop when the last worksheet in the active workbook has been gone through and protected.
The “For Next” Loop
With the “For Next” loop, you can specify the number of times a code block will be looped.
Once the code has looped through the specified code block a certain amount of times, the loop will stop.
For example, with the “For Next” loop, I can have a code go through a specific code block five times.
You can write the “For Next” loop in a VBA code this way:
For Counter = Start To End [Step Value]
[Code Block to Execute]
Next [counter]
The counter parameter is what allows you to specify the number of times the code will loop through the code block.
Sample Application
Suppose you want to add up the first five odd positive integers (1, 3, 5, 6, 9).
Instead of doing it manually, you can use a VBA code that incorporates the “For Next” loop.
Here’s the VBA code:
Sub AddOddNumbers() Dim Total As Integer Dim Count As Integer Total = 0 For Count = 1 To 9 Step 2 Total = Total + Count Next Count MsgBox Total End Sub
This code will add up the first five odd positive integers, then show the result in a message box.
The count start is set at 1(which is the first odd positive integer) and the end is set at 9 (the fifth odd positive integer).
Then, the step value is set to “Step 2” to ignore the even positive integers between 1 and 9.
Conclusion
And those are the different VBA loops you can use in Excel.
Each loop functions differently so it pays to learn how to use each of then.
Learning these loops will tremendously improve your Excel experience (especially VBA).