For Each Loops

VBA provides a looping structure specifically designed for iterating through members in a collection or an array. The For Each loop executes one or more statements inside the block so long as there is at least one member in the collection.

Dim myColors As New Collection Dim vColor As Variant myColors.Add "red", "r" myColors.Add "white", "w" myColors.Add "blue", "b"

For Each vColor In myColors MsgBox vColor


Notice the syntax of the For Each statement. The statement basically says, "For every object in the collection, display the member name in a message box."

The variable used in the For Each statement (called vColor in the preceding example) is always of variant type regardless of the collection's content. This is an important note because For Each statements cannot be used with an array of user-defined types; variants can't contain a user-defined type.

The next program code loops through all control names on a form using the built-in VBA Controls collection.

Dim myControls As New Collection Dim vControl As Variant

For Each vControl In Form_Form1.Controls

MsgBox vControl.Name


T«*»c,t If you need to exit the For Each loop early, VBA provides the Exi t For fS^O*. statement.

0 0

Post a comment