0

The picture that explains the question:

enter image description here

Notes:

  • I have Tools: Options: Auto List Members option selected
  • Ctrl+Space fails
  • I know that I can use the Object Browser to see, what members I can use with the Worksheet object, but it would just be easier, if Excel showed me the list.
Improve this question

1 Answer 1

Reset to default
1

This is because the Worksheets object Item Property returns an object (or in VB speak a Variant data type). The code completion dropdown doesn't know which methods to show because the return is not strongly typed to a Worksheet object. Short of Excel either changing the code to use a strongly typed collection or heuristically figuring out the data type there is no way to change this behavior for the Item property.

You can however assign the returned object to a typed variable and the code completion will work. For example:

Worksheet Sheet1 = Worksheets(1)
Sheet1.

You will get the completion list when you type the period after Sheet1. This is because the returned object is now cast as a Worksheet type and Excel knows what methods are associated with that type.

Improve this answer
3
  • Thanks for the response. So, just to clarify (and so that I understand this correctly), in Excel VBA, there is no way to map or cast the object returned by the Worksheets.Item() property, so that the returned object is explicitly the Worksheet object? (~no way to explicitly change the type of the object that the Item property returns).
    – colemik
    Mar 18, 2013 at 1:20
  • Well there is no UI to do it. In Visual Studio there are third party programs that tie into the code completion feature to enhance it (we use Resharper for example). VBA uses similar tech so it might be possible for an add-on to do this. However, I don't know of any that do.
    – Brad Patton
    Mar 18, 2013 at 1:59
  • @trismarck falling asleep last night I thought of a way to get the completion list. I've edited the answer to include it. Hope that helps.
    – Brad Patton
    Mar 18, 2013 at 11:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .