Autocomplete in VBA Excel 2010 editor
I am new to VBA. Excel 2010 editor has an autocomplete option by pressing CTRL + Space.
But sometimes it doesn't work. For example, when I enter "ActiveSheet". I am unable to examine the possible methods and variables the object has.
But when I enter:
Set sheet = Workbooks.Open(file, True, True)
sheet.
and press CTRL + Space. I see all the possibilities.
Thank you for your help!
source to share
VBA only provides properties and methods if there is no ambiguity in the data type. The ActiveSheet could be a Worksheet object, a macrolith, and maybe a couple of other things that I don't remember.
If you go to Object Explorer (F2) and look for either the ActiveSheet or the Item property of the Sheets class, you can see that they return the Object data type. An object is a generic data type that can contain any object (sort of like a Variant). Since VBA doesn't know which object is behind the object, it cannot give you a list of properties and methods.
You won't get this list by using Set sheet = ...
, you will get it because earlier in the code you declared it sheet
as a Worksheet (maybe). Although Sheet.Item (and Activesheet) returns an object, there is no ambiguity when you declare something as a Worksheet.
source to share