Syntax

For Each member in group

[statements]

[Exit For]

[statements]

Next [member]

Description

Repeats a block of statements for each element in a collection or array.

Comments

The For Each...Nextstatement takes the following parameters:

Parameter

Description

member

Name of the variable used for each iteration of the loop. If group is an array, then member must be a Variant variable. If group is a collection, then member must be an Object variable, an explicit OLE automation object, or a Variant.

group

Name of a collection or array.

statements

Any number of BasicScript statements.

BasicScript supports iteration through the elements of OLE collections or arrays, unless the arrays contain user-defined types or fixed-length strings. The iteration variable is a copy of the collection or array element in the sense that change to the value of member within the loop has no effect on the collection or array.

The For Each...Next statement traverses array elements in the same order the elements are stored in memory. For example, the array elements contained in the array defined by the statement

Dim a(1 To 2,3 To 4)

are traversed in the following order: (1,3), (1,4), (2,3), (2,4). The order in which the elements are traversed should not be relevant to the correct operation of the script.

The For Each...Next statement continues executing until there are no more elements in group or until an Exit For statement is encountered.

For Each...Next statements can be nested. In such a case, the Next [member] statement applies to the innermost For Each...Next or For...Next statement. Each member variable of nested For Each...Next statements must be unique.

A Next statement appearing by itself (with no member variable) matches the innermost For Each...Next or For...Next loop.

Example

’The following subroutine iterates through the elements

’of an array using For Each...Next.

Sub Main()

  Dim a(3 To 10) As Single

  Dim i As Variant

  Dim s As String

  For i = 3 To 10

    a(i) = Rnd()

    Next i

  For Each i In a

    i = i + 1

  Next i

    s = ""

  For Each i In a

    If s <> "" Then s = s & ","

    s = s & i

  Next i

  MsgBox s

End Sub

’The following subroutine displays the names of each worksheet

’in an Excel workbook.

Sub Main()

  Dim Excel As Object

  Dim Sheets As Object

  Set Excel = CreateObject("Excel.Application")

  Excel.Visible = 1

  Excel.Workbooks.Add

  Set Sheets = Excel.Worksheets

  For Each a In Sheets

  MsgBox a.Name

  Next a

End Sub

See Also

Do...Loop (statement), While...Wend (statement), For...Next (statement)

Note

Due to errors in program logic, you can inadvertently create infinite loops in your code. Under Windows and Win32, you can break out of infinite loops using Ctrl+Break.

F