Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.
share|improve this question
add comment

1 Answer

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)
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.

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
add comment

Your Answer

 
discard

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.