VB & VBA in a Nutshell: The Language

For Each...Next Statement

Named Arguments

No

Syntax

For Each element In group
[statements]
[Exit For]
[statements]
Next [element]

element

Use: Required

Data Type: Variant or Object

A variant or object variable to which the current element from the group is assigned.

group

Use: Required

A collection, object collection, or an array.

statements

Use: Optional

A line or lines of program code to execute within the loop.

Description

Loops through the items of a collection or the elements of an array.

Rules at a Glance

• The For...Each code block is executed only if group contains at least one element. If group is a dynamic array that has not yet been dimensioned or an empty collection, a syntax error (runtime error 92, "For loop not initialized," and 424, "Object required," respectively) results.

• All statements are executed for each element in group in turn until either there are no more elements in group, or the loop is exited prematurely using the Exit For statement. Program execution then continues with the line of code following Next.

• For Each...Next can't be used with an array of user-defined types, because you can't assign a user-defined type to a variant.

• For Each...Next can't be used with an array of fixed-length strings.

• For Each...Next loops can be nested, but each element must be unique. For example:

  For Each myObj In AnObject
      For Each subObject In myObj
          SName = subObject.NameProperty
      Next
  Next

  uses a nested For Each...Next loop, but two different variables, myObj and subObject ...