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.

Do While…Loop

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 <=20
	MsgBox “Counter is now” & intCounter
	intCounter = intCounter + 1
Loop
End Sub

Do…Loop While

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” & intCounter
	intCounter = intCounter + 1
Loop While intCounter <=20
End Sub

Do Until…Loop

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 >=20
	MsgBox “Counter is now” & intCounter
	intCounter = intCounter + 1
Loop 
End Sub

Do…Loop Until

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” & intCounter
	intCounter = intCounter + 1
Loop Until intCounter >=20
End Sub

The next 2 looping structures are FOR loops.

For…Next

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” & intCounter
	intCounter = intCounter + 1
Next intCounter
End Sub

For Each…Next

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 <=20
	If intCounter = 5 Then Exit Do
	MsgBox “Counter is now” & 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 < 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.