How to use Microsoft VBA Loops:

Loops are used in VBA (Visual Basic for Applications) to repeat the same set of programming instructions several times to iterate through variables incrementally (For Loops) through items in a collection, (For Each Loops) such as through all of the worksheets in a workbook or (Do Loops) through a subroutine until a condition is met.

The following are some of the common types of For Loops (if you need to terminate prematurely based on a certain condition use Exit For):

 

Increment a variable by 1 each loop pass (starting at 1)

——————–

Dim i As Long

For i = 1 To 10

Next i

——————–

Note:  This loop will increment the variable i by 1 each pass starting from 1 and ending at 10.  If you want to modify the cells in column A from 1 to 10, you could refer to the range within the VBA code as Range(“A” & i) and each pass you will be referencing a different row.

 

Increment a variable by 1 each loop pass (not starting at 1)

——————–

Dim i as Long

For i = -5 To 10

Next i

——————–

or

——————–

Dim i as Long

For i = 10 To 20

Next i

——————–

Increment a variable by x each loop pass (x = 2)

——————–

Dim i as Long

For i = 1 To 20 Step 2

Next i

——————–

Increment a variable by -x each loop pass (x = -1)

——————–

Dim i as Long

For i = 10 To 1 Step -1

Next i

——————–

Note:  This example will run in reverse and increment by -1 each pass.

 

The following is a common type of For Each Loop (if you need to terminate prematurely based on a certain condition use Exit For):

——————–

Dim WS As Worksheet

For Each WS In Worksheets

Next

——————–

Note:  In each pass through the loop you can use the WS variable within the VBA code to reference the specific worksheet in the worksheets collection, and each pass you will be referencing a different worksheet.

 

The following are some of the common types of Do Loops (if you need to terminate prematurely based on a certain condition use Exit Do):

 

Run until condition is met

——————–

Do Until

Loop

——————–

Run while condition is met

——————–

Do While

q Loop

——————–

Specify condition check on loop line

——————–

Do

Loop While

——————–

Note:  The difference in these methods is that the Do Until or Do While loops may skip all iterations if the initial condition is False; however, the Loop While example will always run at least one iteration.