In VBA, loops allow you to go through a set of objects/values and analyze it one by one. You can also perform specific tasks for each loop. Here is a simple example of using VBA loops in Excel. Suppose you have a dataset and you want to highlight all the cells in even rows. You can use a VBA loop to go through the range and analyze each cell row number. If it turns out to be even, you give it a color, else you leave it as is. Now this, of course, is very simple of looping in Excel VBA (and you can also do this using conditional formatting). In real life, you can do a lot more with VBA loops in Excel that can help you automate tasks. Here are some more practical examples where VBA loops can be useful:
Looping through a range of cells and analyzing each cell (highlight cells with a specific text in it). Looping through all the worksheets and do something with each (such as protect/unprotect it). Loop through all the open workbooks (and save each workbook or close all except the active workbook). Loop through all the characters in a cell (and extract the numeric part from a string). Loop through all the values an array. Loop through all the charts/objects (and give a border or change the background color).
Now to best use loops in Excel VBA, you need to know about the different kinds that exist and the correct syntax of each.
In this tutorial, I’ll showcase different types of Excel VBA loops and cover a few examples for each loop If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.
For Next Loop
The ‘For Next’ loop allows you to go through a block of code for the specified number of times. For example, if I ask you to add the integers from 1 to 10 manually, you would add the first two numbers, then add the third number to the result, then add the fourth number to the result, as so on.. Isn’t it? The same logic is used in the For Next loop in VBA. You specify how many times you want the loop to run and also specify what you want the code to do each time the loop is run. Below is the syntax of the For Next loop: In the For Next loop, you can use a Counter (or any variable) that will be used to run the loop. This counter allows you to run this loop for a required number of times. For example, if I want to add the first 10 positive integers, then my Counter value would be from 1 to 10. Let’s have a look at a few examples to better understand how For Next loop works.
Example 1 – Adding the first 10 positive integers
Below is the code that will add the first 10 positive integers using a For Next loop. It will then display a message box showing the sum of these numbers. In this code, the value of Total is set to 0 before getting into the For Next loop. Once it gets into the loop, it holds the total value after every loop. So after the first loop, when Counter is 1, ‘Total’ value becomes 1, and after the second loop it becomes 3 (1+2), and so on. And finally, when the loop ends, ‘Total’ variable has the sum of the first 10 positive integers. A MsgBox then simply displays the result in a message box.
Example 2 – Adding the first 5 Even Positive Integers
To sum the first five even positive integers (i.e, 2,4,6,8, and 10), you need a similar code with a condition to only consider the even numbers and ignore the odd numbers. Here is a code that will do it: Note that we started the Count value from 2 and also used ‘Step 2‘. When you use ‘Step 2’, it tells the code to increment the ‘Count’ value by 2 every time the loop is run. So the Count value starts from 2 and then becomes 4, 6, 8 and 10 as the looping occurs. NOTE: Another way of doing this could be to run the loop from 1 to 10 and within the loop check whether the number is even or odd. However, using Step, in this case, is a more efficient way as it does not require the loop to run 10 times, but only 5 times. The Step value can also be negative. In such as case, the Counter starts at a higher value and keeps getting decremented by the specified Step value.
Example 3 – Entering Serial Number in the Selected Cells
You can also use the For Next loop to go through a collection of objects (such as cells or worksheets or workbooks), Here is an example that quickly enters serial numbers in all the selected cells. The above code first counts the number of selected rows and then assigns this value to the variable RowCount. We then run the loop from ‘1 to RowCount’. Also note that since selection can be any number of rows, we have Set the variable Rng to Selection (with the line ‘Set Rng = Selection’). Now we can use the ‘Rng’ variable to refer to the selection in our code.
Example 4 – Protect All Worksheets in the Active Workbook
You can use the ‘For Next’ loop to go through all the worksheets in the active workbook, and protect (or unprotect) each of the worksheets. Below is the code that will do this: The above code counts the number of sheets by using ActiveWorkbook.Worksheets.Count. This tells VBA how many times the loop needs to be run. In each instance, it refers to the Ith workbook (using Worksheets(i)) and protects it. You can use this same code to Unprotect worksheets too. Just change the line Worksheets(i).Protect to Worksheets(i).UnProtect.
Nested ‘For Next’ Loops
You can use nested ‘For Next’ loops to get more complex automation done in Excel. A nested ‘For Next’ loop would mean that there is a ‘For Next’ loop within a ‘For Next’ loop. Let me show you how to use this using an example. Suppose I have 5 workbooks open in my system and I want to protect all the worksheets in all these workbooks. Below is the code that will do this: The above is a nested For Next loop as we have used one For Next loop within another.
‘EXIT For’ Statements in For Next Loops
‘Exit For’ statement allows you to exit the ‘For Next’ loop completely. You can use it in cases where you want the For Next loop to end when a certain condition is met. Let’s take an example where you have a set of numbers in Column A and you want to highlight all the negative numbers in red font. In this case, we need to analyze each cell for its value and then change the font color accordingly. But to make the code more efficient, we can first check if there are any negative values in the list or not. If there are no negative values, we can use the Exit For the statement to simply come out of the code. Below is the code that does this: When you use the ‘Exit For’ statement within a nested ‘For Next’ loop, it will come out of the loop in which it is executed and go on to execute the next line in the code after the For Next loop. For example, in the below code, the ‘Exit For’ statement will get you out of the inner loop, but the outer loop would continue to work.
Do While Loop
A ‘Do While’ loop allows you to check for a condition and run the loop while that condition is met (or is TRUE). There are two types of syntax in the Do While Loop. and The difference between these two is that in the first, the While condition is checked first before any code block is executed, and in the second case, the code block is executed first and then the While condition is checked. This means that if the While condition is False is both the cases, the code will still run at least once in the second case (as the ‘While’ condition is checked after the code has been executed once). Now let’s see some examples of using Do While loops in VBA.
Example 1 – Add First 10 Positive Integers using VBA
Suppose you want to add the first ten positive integers using the Do While loop in VBA. To do this, you can use the Do While loop until the next number is less than or equal to 10. As soon as the number is greater than 1o, your loop would stop. Here is the VBA code that will run this Do While loop and the show the result in a message box. The above loop continues to work until the value of ‘i’ becomes 11. As soon as it becomes 11, the loop ends (as the While condition becomes False). Within the loop, we have used a Result variable that holds the final value Once the loop is completed, a message box shows the value of the ‘Result’ variable.
Example 2 – Enter Dates For the Current Month
Let’s say you want to enter all the dates of the current month into a worksheet column. You can do that by using the following Do While loop code: The above code would enter all the dates in the first column of the worksheet (starting from A1). The loops continue till the Month value of the variable ‘CMDate’ matches that of the current month.
Exit Do Statement
You can use the Exit Do statement to come out of the loop. As soon as the code executes the ‘Exit Do’ line, it comes out of the Do While loop and passes the control to the next line right after the loop. For example, if you want to enter the first 10 dates only, then you can exit the loop as soon as the first 10 dates are entered. The below code will do this: In the above code, the IF statement is used to check if the value of i is greater than 10 or not. As soon as the value of ‘i’ becomes 10, Exit Do statement is executed and the loop ends.
Do Until Loop
‘Do Until’ loops are very much like the ‘Do While’ loops. In ‘Do While’, the loop runs till the given condition is met, while in ‘Do Until’, it loops until the specified condition is met. There are two types of syntax in the Do Until Loop. and The difference between these two is that in the first, the Until condition is checked first before any code block is executed, and in the second case, the code block is executed first and then the Until condition is checked. This means that if the Until condition is TRUE is both cases, the code will still run at least once in the second case (as the ‘Until’ condition is checked after the code has been executed once). Now let’s see some examples of using Do Until loops in VBA. Note: All the examples for Do Until are the same as that of Do While. These have been modified to show you how the Do Until loop works.
Example 1 – Add First 10 Positive Integers using VBA
Suppose you want to add the first ten positive integers using the Do Until loop in VBA. To do this, you need to run the loop until the next number is less than or equal to 10. As soon as the number is greater than 1o, your loop would stop. Here is the VBA code that will run this loop and show the result in a message box. The above loop continues to work until the value of ‘i’ becomes 11. As soon as it becomes 11, the loop ends (as the ‘Until’ condition becomes True).
Example 2 – Enter Dates For the Current Month
Let’s say you want to enter all the dates of the current month into a worksheet column. You can do that by using the following Do Until loop code: The above code would enter all the dates in the first column of the worksheet (starting from A1). The loop continues until the Month of variable CMDate is not equal to that of the current month.
Exit Do Statement
You can use the ‘Exit Do’ statement to come out of the loop. As soon as the code executes the ‘Exit Do’ line, it comes out of the Do Until loop and passes the control to the next line right after the loop. For example, if you want to enter the first 10 dates only, then you can exit the loop as soon as the first 10 dates are entered. The below code will do this: In the above code, as soon as the value of ‘i’ becomes 10, Exit Do statment is executed and the loop ends.
For Each
In VBA, you can loop through a set of collections using the ‘For Each’ loop. Here are some examples of collections in Excel VBA:
A collection of all the open Workbooks. A collection of all worksheets in a workbook. A collection of all the cells in a range of selected cells. A collection of all the charts or shapes in the workbook.
Using the ‘For Each’ loop, you can go through each of the objects in a collection and perform some action on it. For example, you can go through all the worksheets in a workbook and protect these, or you can go through all the cells in the selection and change the formatting. With the ‘For Each’ loop (also referred to as the ‘For Each-Next’ loop), you don’t need to know how many objects are there in a collection. ‘For Each’ loop would automatically go through each object and perform the specified action. For example, if you want to protect all the worksheets in a workbook, the code would be the same whether you have a workbook with 3 worksheets or 30 worksheets. Here is the syntax of For Each-Next loop in Excel VBA. Now let’s see a couple of examples of using the For Each Loop in Excel.
Example 1 – Go through All the Worksheets in a Workbook (and Protect it)
Suppose you have a workbook where you want to protect all the worksheets. Below For Each-Next loop can do this easily: In the above code, we have defined ‘ws’ variable as a Worksheet object. This tells VBA that ‘ws’ should be interpreted as a worksheet object in the code. Now we use the ‘For Each’ statement to go through each ‘ws’ (which is a worksheet object) in the collection of all the worksheets in the active workbook (given by ActiveWorkbook.Worksheets). Note that unlike other loops where we have tried to protect all the worksheets in a workbook, here we don’t need to worry about how many worksheets are there in the workbook. We don’t need to count these to run the loop. For Each loop ensures that all the objects are analyzed one by one.
Example 2 – Go through All the Open Workbooks (and Save All)
If you work with multiple workbooks at the same time, it can come in handy to be able to save all these workbooks at once. Below VBA code can do this for us: Note that in this code, you don’t get a prompt that asks you to save the workbook in a specific location (if saving it for the first time). It saves it in the default folder (it was the ‘Documents’ folder in my case). This code works best when these files are already saved and you’re making changes and you want to save all the workbooks quickly.
Example 3 – Go through All the Cells in a Selection (Highlight negative values)
Using the ‘For Each’ loop, you can loop through all the cells in a specific range or in the selected range. This can be helpful when you want to analyze each cell and perform an action based on it. For example, below is the code that will go through all the cells in the selection and change the cell color of the cells with negative values to red. (Note I’ve used Cll as a short variable name for Cell. It’s advisable not to use object names such as Sheets or Range as variable names) In the above code, the For Each-Next loop goes through the collection of cells in the selection. IF statement is used to identify if the cell value is negative or not. In case it is, the cell is given a red interior color, else it goes to the next cell. In case you don’t have a selection, and instead want VBA to select all the filled cells in a column, starting from a specific cell (just like we use Control + Shift + Down arrow key to select all filled cells), you can use the below code: In the above example, it doesn’t matter how many filled cells are there. It will start from cell A1 and analyze all the contiguous filled cells in the column. You also don’t need to have cell A1 selected. You can have any far-off cell selected and when the code runs, it will still consider all the cells in column A (starting from A1) and color the negative cells.
‘Exit For’ Statment
You can use the ‘Exit For’ statement in the For Each-Next loop to come out of the loop. This is usually done in case a specific condition is met. For example, in Example 3, as we are going through a set of cells, it can be more efficient to check if there are any negative values or not. In case there are no negative values, we can simply exit the loop and save some VBA processing time. Below is the VBA code that will do this:
Where to Put the VBA Code
Wondering where the VBA code goes in your Excel workbook? Excel has a VBA backend called the VBA editor. You need to copy and paste the code in the VB Editor module code window. Here are the steps to do this:
How to record a macro in Excel. Creating User-defined functions in Excel. Excel VBA Msgbox How to Run a Macro in Excel. How to Create and Use Add-ins in Excel. Excel VBA Events – An Easy (and Complete) Guide. How to Sort Data in Excel using VBA (A Step-by-Step Guide). 24 Useful Excel Macro Examples for VBA Beginners (Ready-to-use). How to Use Excel VBA InStr Function (with practical EXAMPLES). Excel Personal Macro Workbook | Save & Use Macros in All Workbooks. Using Select Case in Excel VBA.
I have tried lots of thin but still it is not working as I needed. Anybody can help me for this task….. how to Show the same months of combobox whose dates is written in the column number one of the worksheet ?
