XL: 'For Each Item in List' Doesn't Work

ID: Q112330

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel, the Visual Basic 'For Each...Next' control structure may not function properly if the group argument is a list in a custom dialog box.

MORE INFORMATION

In Microsoft Excel, you can use the 'For Each...Next' control structure to repeat a group of statements for each element in an array or collection.

For example, the following code displays dialog boxes containing the contents of each cell in a selected range:


   For Each mCell In Selection  '"Selection" is the group argument
      MsgBox mCell.Value
   Next

If the group argument is a list in a custom dialog box, the For Each command may fail, and you will receive the following error message:

Run-time error '10':
Duplicate definition

WORKAROUND

To avoid this error, set an object equal to the list


   <Object> = <Listname>.List

and then use the object in the For Each statement. For example, instead of


     xList = DialogSheets("Dialog1").ListBoxes("List1")
   For Each mItem in xList.List
      <statements>
   Next

use

   xList = DialogSheets("Dialog1").ListBoxes("List1")
   mTemp = xList.List
   For Each mItem in mTemp
      <statements>
   Next

Visual Basic Code Example

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

This example shows one way that you can avoid the error associated with calling a list directly from a For Each statement.

The following example assumes you have a workbook that contains a Visual Basic module (Module1) and a dialog sheet (Dialog1). The dialog sheet contains a single list box (List1).

In Module1, enter the following subroutine:


'----------------------------------------------------------------------
Option Explicit

Sub ForEachListItem()

   'Dimension some variables.
   Dim Alpha As Variant, Foxtrot As Variant, Golf As Variant

   'Set an object name for easy referencing of the list box.
   Set Alpha = DialogSheets("Dialog1").ListBoxes("List1")

   'Add three items to the list.
   Alpha.AddItem "Bravo"
   Alpha.AddItem "Charlie"
   Alpha.AddItem "Delta"

   'Set an object name so that the For Each structure can function
   'properly.
   Golf = Alpha.List

   'Iterate through the loop once for each item in Golf (which is
   'the same as iterating once for each item in the list box).
   For Each Foxtrot In Golf

      'Show the current list item in a message box.
      MsgBox Foxtrot
   Next                                  'repeat until all done
End Sub
'----------------------------------------------------------------------

To run the subroutine, position the cursor in the line that reads "Sub ForEachListItem()," and either press F5 or choose Start from the Run menu.

If the For Each line is entered as shown, the subroutine will run properly and message boxes will display the names of the items in the list.

If the For Each line is altered to refer to Alpha.List directly, without using an intermediary function (in this case, Golf), you will receive the error message shown above and the message boxes will not be displayed.

Additional query words:

Keywords : kbprg
Version : WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : WINDOWS winnt
Issue type :