OFF97: Auto List Members Missing for Some VBA Commands

ID: Q188389

The information in this article applies to:

Microsoft Office 97 for Windows

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" (without the quotation marks) 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.

REFERENCES

"Mastering Microsoft Office 97 Development," 97, Chapter 5, "Using Object Variables"

For additional information about early binding, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q138138
   TITLE     : INFO: Late, ID, Early Binding Types Possible in VB for Apps

Additional query words: XL97

Keywords          : kbdta xlvbainfo 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: May 19, 1999