Microsoft KB Archive/188389

= OFF97: Auto List Members Missing for Some VBA Commands =

Article ID: 188389

Article Last Modified on 11/22/2000

-

APPLIES TO


 * Microsoft Office 97 Standard Edition

-



This article was previously published under Q188389



SYMPTOMS
After you press the PERIOD key when you type some object keywords in a Microsoft Visual Basic for Applications Code window, the list that contains the properties and methods available for the object (Auto List Members) may not appear.

NOTE: Even if the keyword resolves to a valid object name, this behavior may occur.



CAUSE
The behavior may occur if the following conditions are true:
 * When the code is run, the object name refers to more than one object (for example, "ActiveSheet" could be a chart or a worksheet in Microsoft Excel). -or-


 * The object name resolves to the Object type. -or-


 * The object name is the Variant variable type.



RESOLUTION
To resolve this problem, you must declare a variable with the specific object type. When you type the variable name and press the PERIOD key, the correct list of methods and properties appears.

The following line of code declares a variable called "wsSheet" as a Worksheet object. This allows the Auto List Member feature to function when you type wsSheet and press the PERIOD key. Dim wsSheet As Worksheet This procedure is referred to as early binding.



MORE INFORMATION
When you press the PERIOD key after you type a Visual Basic keyword, you receive a list of items (Auto List Members) that help you complete the Visual Basic code. Although they are Visual Basic keywords, some items do not show in the list. Examples are listed later in this article.

Example 1: Items That Can Refer to Multiple Objects
Because they can refer to a worksheet or a chart sheet object, the use of Sheets(1) or ActiveSheet in Microsoft Excel will not display Auto List Members. The exact object type is ambiguous, and no list is displayed.

NOTE: If you press the PERIOD key after typing ActiveWorkbook or ActiveChart, this problem does not occur, because both keywords have associated properties and methods.

Example 2: Items That Resolve to the Object Type
In Microsoft Excel, Worksheets(1) will not display the Auto List Members, because you can use the following syntax.

Worksheets(Array("Sheet1", "Sheet2"))

and return another Worksheets collection. The context in which Worksheets(1) is used in Visual Basic for Applications is ambiguous.

Example 3: Variant Objects
If you use the Set statement to assign an object to a variable without using the Dim statement first to declare the variable to be an object, the Auto List Members feature will not work for that variable.

Auto List Members feature will not work with the following line of code: Set rngTest = Application. Workbooks(1).Worksheets(1).Range("A1") However if the following line of code is inserted before the earlier code, the Auto List Members feature will work: Dim rngTest As Excel.Range To turn the Auto List Members feature on or off, follow these steps:
 * 1) Start the Visual Basic Editor by pressing ALT+F11.
 * 2) On the Tools menu, click Options.
 * 3) Click the Editor tab, and click to clear the Auto List Members check box.

