4

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!

mrbela
  • 4,477
  • 9
  • 44
  • 79
  • Try looking [here.](http://stackoverflow.com/questions/25796920/vba-excel-what-methods-attributes-can-i-use-with-activesheet) – TheEngineer Dec 17 '14 at 15:07
  • http://www.pcreview.co.uk/forums/why-no-intellisense-activesheet-t4031852.html – chancea Dec 17 '14 at 15:09
  • allright, am I right, that there are no autocomplete possibilites because the computer doesn't know what kind of type ActiveSheet really is? – mrbela Dec 17 '14 at 15:36
  • Does this answer your question? [Autocomplete / drop down box for arguments past the first full stop](https://stackoverflow.com/questions/60387950/autocomplete-drop-down-box-for-arguments-past-the-first-full-stop) – Zev Spitz Feb 26 '20 at 12:44

1 Answers1

6

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.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • RE _VBA only gives you the properties and methods when there is no ambiguity in the data type._ -- The `ActiveSheet` property has no ambiguity about its' data type; its' data type is unambiguously `Object`. – Zev Spitz Feb 26 '20 at 12:42