Article ID: 112330
Article Last Modified on 10/10/2006
APPLIES TO
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 5.0c
- Microsoft Excel 5.0 Standard Edition
This article was previously published under Q112330
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:
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.
Keywords: kbprogramming KB112330
