A Complete Guide to Loops in VBA
When you write good VBA code, you try not to repeat yourself too often – in other words, you try not to write the same code for repetitive tasks more than once. Looping structures are an invaluable part of VBA and you will use them often when you write good code. They allow you to repeat a section of your code while passing information to the code until a certain condition that you have set, is met.
There are six Visual Basic looping structures. Four of these use the Do…Loop structure.
First, we declare an integer (
intCounter) and give it a value of 1. We then ask the loop to increase the value of the counter on each loop – and to run the loop until the counter is equal to or less than 20.
As the counter is incremented on each loop, as soon as the counter reaches the value of 20, the code will stop. The loop is testing to see if the condition is TRUE (less than or equal to 20). It will only run the code if the condition is initially TRUE, and will continue to run the code while the condition is TRUE, stopping as soon as the condition becomes FALSE — ie when
intCounter > 20.
Sub TestLoop Dim intCounter As Integer intCounter =1 Do While intCounter &amp;amp;lt;=20 MsgBox “Counter is now” &amp;amp;amp; intCounter intCounter = intCounter + 1 Loop End Sub
This loop also tests to see if a condition is TRUE – but at the end of the loop instead of the beginning. In other words, it will always run the code once, and then start testing to see if further instances of the code are needed.
Sub TestLoop Dim intCounter As Integer intCounter =1 Do MsgBox “Counter is now” &amp;amp;amp; intCounter intCounter = intCounter + 1 Loop While intCounter &amp;amp;lt;=20 End Sub
This loop works in the opposite direction to the Do Until…While loop – it tests to see if a condition is FALSE rather than TRUE, and continues to loop through the code UNTIL the condition becomes TRUE.
Sub TestLoop Dim intCounter As Integer intCounter =1 Do Until intCounter &amp;amp;gt;=20 MsgBox “Counter is now” &amp;amp;amp; intCounter intCounter = intCounter + 1 Loop End Sub
Similar to the Do…Loop While in that it tests the condition at the end rather than at the beginning and therefore will always run the code once, but once again it runs while the condition is FALSE and tests UNTIL the condition is TRUE rather than WHILE the condition is TRUE.
Sub TestLoop Dim intCounter As Integer intCounter =1 Do MsgBox “Counter is now” &amp;amp;amp; intCounter intCounter = intCounter + 1 Loop Until intCounter &amp;amp;gt;=20 End Sub
The next 2 looping structures are FOR loops.
If you have to run a statement a specific number of times, you can use the For…Next loop to do so. This loops uses a counter that increases or decreases with each repetition of the code and stops when the counter reaches a pre-set value.
Sub TestLoop Dim intCounter As Integer For intCounter = 1 to 20 MsgBox “Counter is now” &amp;amp;amp; intCounter intCounter = intCounter + 1 Next intCounter End Sub
This loop is used with a collection of objects – for example, worksheets in Excel. Instead of repeating the code a specified number of times, the code will repeat for each object that is in the collection. You may, for example, wish to protect all your worksheets without having to go to each individual sheet and protecting it. The code below will loop through each sheet and protect each one with a password.
Sub ProtectSheets Dim wks As Worksheet For Each wks In ActiveWorkbook.Sheets wks.Protect Password=”fred” Next wks End Sub
Exiting (breaking) a loop before a condition is met
On occasion, you may want to break out of a loop when the condition that you have set has not been met. For example, you many need to EXIT a loop before your counter has reached 20, or you may need to exclude a particular sheet from being protected. If this is the case you can exit the loop. You would need an IF statement to do so.
To Exit the Do While/Until Loops
By adding the line of code
If intCounter = 5 then
Exit Do to your routine, when the counter is 5, the loop will be interrupted as the if statement will test to see the value of the counter, and then the EXIT DO method will occur.
Sub TestLoop Dim intCounter As Integer intCounter =1 Do While intCounter &amp;amp;lt;=20 If intCounter = 5 Then Exit Do MsgBox “Counter is now” &amp;amp;amp; intCounter intCounter = intCounter + 1 Loop End Sub
To Exit the For Each/Next Loops
Similarly, by adding the line of code,
If wks.name = “Sheet1” then Exit For to your routine below, the If statement will check to see what the name of the sheet is – and if the sheet is called Sheet 1, it will not protect the sheet and will actually exit the loop entirely.
Sub ProtectSheets Dim wks As Worksheet For Each wks In ActiveWorkbook.Sheets If wks.name = “Sheet1” Then Exit For wks.Protect Password=”fred” Next wks End Sub
Read here for more about exiting for loops.
A working example of a For…Next Loop with an Exit For
The examples and information above should give you a general idea of how looping in VBA works. It might however be useful to have a ‘real’ example of how the code can be useful. One way it can be useful in Excel, is perhaps to format a range of cells in different colors depending on the value of the cell.
The For Next Loop will enable you to loop through the range of cells, look at the value in the cell, and format it accordingly. First, you need to have a list of values in Excel, and highlight the values.
Then you run the code while the cells are selected – this is the selected range.
Sub CheckCellValue() Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount If IsNull(ActiveCell) Then Exit For If ActiveCell &amp;amp;lt; 300 Then ActiveCell.Font.Color = vbRed ActiveCell.Offset(1, 0).Select Next Counter End Sub
In conclusion, you can see how useful looping is in working with VBA code. It means that you do not have to repeat yourself and re-write code for repetitive tasks. Loops are one of the most powerful, yet basic tools in VBA and are also used across most popular programming languages. A loop enables you to write a few lines of simple code in order to achieve a significant outcome! I hope that this article is useful in demonstrating this.