3/9/2020 VBA Loops - For, Do-While and Do-Until Loops

ExcelFunctions.net
Search Site:

Custom Search

Home » Excel-VBA-Tutorial » VBA-Loops

Excel VBA Tutorial Part 6 - VBA

Loops
l Functions ▾ If, in your VBA program, you need to perform the
same task (i.e. repeat the same piece of code)
New Functions multiple times, this can be done using one of the VBA
Loops.
New Functions
The VBA Loop types are:
up Tutorial
The For Loop

Tutorial The Do While Loop

The Do Until Loop

las
Each of the above loop types is discussed separately
las below.

n Excel
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.
cel Errors

The For ... Next Loop

ates
The For ... Next loop uses a variable, which cycles
s through a series of values within a specified range. The
VBA code inside the loop is then executed for each
utorial value. This is best explained by way of a simple
example:
Functions
For i = 1 To 10
vs 2007 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

https://www.excelfunctions.net/vba-loops.html 1/6
3/9/2020 VBA Loops - For, Do-While and Do-Until Loops

values, runs through the VBA code inside the loop.

Therefore, in the above example, the loop adds each of
the members 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 the variable d is set to the values
0.0, 0.1, 0.2, 0.3, ..., 9.9, 10.0 for each execution of the
VBA code inside the loop.

You can also use negative step sizes in the VBA For
loop, 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.

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:

https://www.excelfunctions.net/vba-loops.html 2/6
3/9/2020 VBA Loops - For, Do-While and Do-Until Loops

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, when searching for a
particular value in an array, you could use a loop to
check each entry of the array. However, once you have
found the value you are looking for, there is no need to
continue searching, so you exit the loop early.

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 Sub procedure,
where a Do While loop is used to print out all values of
the Fibonacci Sequence until the current value is greater
than 1,000:

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

https://www.excelfunctions.net/vba-loops.html 3/6
3/9/2020 VBA Loops - For, Do-While and Do-Until Loops

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 tested 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

https://www.excelfunctions.net/vba-loops.html 4/6
3/9/2020 VBA Loops - For, Do-While and Do-Until Loops

at the beginning. This causes the loop to be executed at

least once, regardless of whether or not the condition
initially evaluates to True.

The following code shows the form of a Do While Loop

which has the condition at the end of the loop:

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 sub procedure, 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:

iRow = 1
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, as 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

sometimes 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:

https://www.excelfunctions.net/vba-loops.html 5/6
3/9/2020 VBA Loops - For, Do-While and Do-Until Loops

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

Go To Excel VBA Tutorial Part 7 - VBA Operators &

Built-In Functions
Return to the Excel VBA Tutorial Page

Disclaimer Privacy Policy

Copyright © 2008-2020 ExcelFunctions.net

https://www.excelfunctions.net/vba-loops.html 6/6