Autocompletion in Excel 2010 VBA Editor

Question

I'm new in VBA. In the editor in Excel 2010 there is the opportunity of an autocompletion by pressing CTRL + Space.

But sometimes, it doesn't work.. For example when I type in "ActiveSheet." I can't examine the possible methods and variables the object have..

But when I type in:

Set sheet = Workbooks.Open(file, True, True)
sheet.

and hit CTRL+Space I can see all possibilities..

Thanks for your help!

Answer 1

VBA only gives you the properties and methods when there is no ambiguity in the data type. The ActiveSheet can be a Worksheet object, Macrosheet, and probably a couple other things I don't remember.

If you go to the Object Browser (F2) and look up either ActiveSheet or the Item property of the Sheets class, you'll see that they returns an Object data type. Object is a generic data type that can hold any object (kind of like Variant). Because VBA doesn't know what object is behind the Object, it can't give you a list of properties and methods.

You don't get that list by using Set sheet = ..., you get it because previously in your code you declared sheet as Worksheet (probably). While Sheet.Item (and Activesheet) returns an Object, there is no ambiguity when you declare something as Worksheet.