Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The picture that explains the question:

enter image description here


  • 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.
share|improve this question
up vote 1 down vote accepted

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)

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.

share|improve this answer
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 '13 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 '13 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 '13 at 11:33
Thanks very much. – colemik Mar 18 '13 at 12:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.