Excel VBA Tutorial Part 6: VBA Loops

Search this site:

Custom Search

If you need to perform the same task several times (perhaps for several elements of an array), this can be done using by repeating the same block of code several times, using one of the VBA Loops.

These Loops are:

The For Loop
The Do While Loop
The Do Until Loop

Each of the above loop types are discussed separately in the following sections.

The Visual Basic For Loop

The Visual Basic For loop takes on two separate forms. These are the For ... Next loop and the For Each loop.

The For ... Next Loop

The For ... Next loop sets a variable to a specified set of values, and for each value, runs the VBA code inside the loop. This is best explained by way of a simple example:

For i = 1 To 10
Total = Total + iArray(i)
Next i

The above simple For ... Next loop sets the variable i to have the values 1, 2, 3, ..., 10, and for each of these values, runs through the VBA code inside the loop. Therefore, in the above example, the loop adds each of the entries of the array 'iArray' to the variable, 'Total'.

In the above example, no step size is specified, so the loop uses the default step size of 1, when looping from 1 to 10. However, you may sometimes want to step through a loop using different sized steps. This can be done using the Step keyword, as shown in the following simple example.

For d = 0 To 10 Step 0.1
dTotal = dTotal + d
Next d

In the above For loop, because the step size is specified as 0.1, the value of d is set to the values 0.0, 0.1, 0.2, 0.3, ..., 9.9, 10.0 for each run through the VBA code inside the loop.

Although the previous examples all show increasing steps, you can also use negative step sizes, as is illustrated below:

For i = 10 To 1 Step -1
iArray(i) = i
Next i

In this example, the step size is specified as -1, and so the loop sets the variable, i, to have the values, 10, 9, 8, ..., 1 in turn.

The For Each Loop

The For Each loop is similar to the For ... Next loop but, instead of running through a set of values for a variable, the For Each loop runs through every object within a set of objects. For example, the following code shows the For Each loop used to list every Worksheet in the current Excel Workbook:

Dim wSheet As Worksheet

For Each wSheet in Worksheets
MsgBox "Found Worksheet: " & wSheet.Name
Next wSheet

The Exit For Statement

If, you want to exit a 'For' Loop early, you can use the Exit For statement. This statement causes VBA to jump out of the loop and continue with the next line of code outside of the loop. For example, you may be searching for a particular value in an array. You might loop through each entry of the array, but when you find the value you are looking for, you no longer wish to continue searching, so you might exit the loop before you get to the end of it.

The Exit For statement is illustrated in the following example, which loops through 100 array entries, comparing each to the value 'dVal'. The loop is exited early if dVal is found in the array :

For i = 1 To 100
If dValues(i) = dVal Then
IndexVal = i
Exit For
End If
Next i

The Visual Basic Do While Loop

The Do While loop repeatedly executes a section of code while a specified condition continues to evaluate to True. This is shown in the following subroutine, where a Do While loop is used to print out all values of the Fibonacci Sequence until the values exceed 1,000 :

' Subroutine to list the Fibonacci series for all values below 1,000
Sub Fibonacci()

Dim i As Integer      ' counter for the position in the series
Dim iFib As Integer       ' stores the current value in the series
Dim iFib_Next As Integer ' stores the next value in the series
Dim iStep As Integer ' stores the next step size

' Initialise the variables i and iFib_Next
i = 1
iFib_Next = 0

' Do While loop to be executed as long as the value
' of the current Fibonacci number exceeds 1000

Do While iFib_Next < 1000

    If i = 1 Then
        ' Special case for the first entry of the series
        iStep = 1
        iFib = 0
    Else
        ' Store the next step size, before overwriting the
        ' current entry of the series
        iStep = iFib
        iFib = iFib_Next
    End If

    ' Print the current Fibonacci value to column A of the
    ' current Worksheet
    Cells(i, 1).Value = iFib

    ' Calculate the next value in the series and increment
    ' the position marker by 1
    iFib_Next = iFib + iStep
    i = i + 1
Loop

End Sub

It can be seen that, in the above example, the condition iFib_Next < 1000 is executed at the start of the loop. Therefore, if the first value of iFib_Next were greater than 1,000, the loop would not be executed at all.

Another way that you can implement the Do While loop is to place the condition at the end of the loop instead of at the beginning. This causes the loop to be executed at least once, regardless of whether or not the condition initially evaluates to True. This makes no difference to the above Fibonacci Sequence loop, as the variable iFib_Next has been initialised to 0 and so the 'While' condition is always satisfied the first time it is tested.

However, if the variable iFib_Next had previously been used for other calculations and was set to a value greater than zero, the initial 'While' condition would be False, and the loop would not be entered. One way to solve this would be to place the condition at the end of the loop, as follows:

Do
  .
  .
  .
Loop While iFib_Next < 1000

The Visual Basic Do Until Loop

The Do Until loop is very similar to the Do While loop. The loop repeatedly executes a section of code until a specified condition evaluates to True. This is shown in the following subroutine, where a Do Until loop is used to extract the values from all cells in Column A of a Worksheet, until it encounters an empty cell :

Do Until IsEmpty(Cells(iRow, 1))

    ' Store the current cell value in the dCellValues array
    dCellValues(iRow) = Cells(iRow, 1).Value
    iRow = iRow + 1

Loop

In the above example, since the condition IsEmpty(Cells(iRow, 1)) is at the start of the Do Until loop, the loop will only be entered if the first cell encountered is non-blank.

However, as illustrated in the Do While loop, you may on some occasions want to enter the loop at least once, regardless of the initial condition. In this case, the condition can be placed at the end of the loop, as follows:

Do
  .
  .
  .
Loop Until IsEmpty(Cells(iRow, 1))