In VBA, why do things like ACTIVESHEET not display all the options tha are available when you type it like most other commands?

[Loun]

Hi All,

I'm currently trying to learn VBA, but have a question.

If, for instance, I type in Range("A1").

Then after I put the period it will bring up a range of options, so I would select 'Interior' and then another list drops down of all the possibiltiies.

However, if I want to do anything with activesheet, for example, I get no options appear?

An example is activesheet.tab.colorindex = 4

To change the tabs colour, however, when you type in activesheet. no option appear? Can anybody explain why this is or the best way to find out the options? It is quite difficult to know what command I need to use when I can't see any of the options!

Just a bit puzzled as to some appear and others don't?

Thanks,
Lou

 

[AlphaFrog]

ActiveSheet could be either a Worksheet or a ChartSheet. So it's declared as generic type object and doesn't have any methods or properties for Intellisense to display. You may not like it, but that's why it is the way it is.

 

[Peter_SSs]

I've often wondered about a similar circumstance?

This produces Intelli-sense
Range("A:A").Find(

This does not
Columns("A").Find(

 

[pgc01]

I've often wondered about a similar circumstance?

This produces Intelli-sense
Range("A:A").Find(

This does not
Columns("A").Find(

Hi Peter

Not exactly on topic, but a remark (that you probably know but can be used by others):
Columns("A"), and it's sister Cells(2, 3) don't give you the intelli-sense, that is especially annoying in the latter case that we use a lot.

What I usually is to write the method without the parameters, like:

Columns().
Cells().

this invokes intelli-sense and then I go back and add the parameters.

 

[Colin Legg]

I wrote an explanation for this here which you might be interested in:
Making Sense of Cells Intellisense – Part 1 | RAD Excel
The article is about intellisense with Cells but ActiveSheet is also discussed in the comments.

I followed it up with one where intellisense actually gives the wrong prompt:
Making Sense Of Cells Intellisense – Part 2 | RAD Excel

 

[Peter_SSs]

Yes, PGC, I usually do

Range("A1").Find(

to complete the 'Find' parameters with intelli-sense and then go back and change the Range("A1") to Columns(..) or Rows(..) as desired.
A bit annoying, but bearable.

 

[Peter_SSs]

I wrote an explanation for this here which you might be interested in:
Making Sense of Cells Intellisense – Part 1 | RAD Excel
The article is about intellisense with Cells but ActiveSheet is also discussed in the comments.

I followed it up with one where intellisense actually gives the wrong prompt:
Making Sense Of Cells Intellisense – Part 2 | RAD Excel

Colin

Thanks for that. Your links confirm that I still have a lot to learn!