Worksheet intelli-sense

[agibsonsw]

Hello. Excel 2003.
Quick question - why is it when using a Worksheet reference (Worksheets(1)) we don't obtain intelli-sense help?
I can appreciate this for Sheets, as it could be a Chart or a Worksheet object etc..
Why do we then receive help if we use an object variable (Set wks = Worksheets(1))? Andy.

[HansV]

For some reason probably known only to the Excel development team, the members of the Worksheets collection are defined as the generic type Object instead of as Worksheet.

x363.png

IntelliSense can't work with generic Objects.

[agibsonsw]

Thank you. Shame they couldn't resolve this in a later version. Andy.

(I like your shaped arrow - is that from the drawing toolbar, or do you prefer a particular app for screenshots?)

[HansV]

Shame they couldn't resolve this in a later version.

It might be on purpose, to make the Sheets, Worksheets and Charts collections work the same way. (There is no "Sheet" object - the members of Sheets are either worksheets or chart sheets)

(I like your shaped arrow - is that from the drawing toolbar, or do you prefer a particular app for screenshots?)

I use FastStone Screen Capture to take screenshots; it has great annotation tools. The current version is shareware, and it's well worth the modest price. You can still find the last free version if you search.

[Jan Karel Pieterse]

My way around this problem is to declare an object variable:
Dim oSh as Worksheet
and then use that.

[HansV]

That's what I do, and I guess Andy too:

Why do we then receive help if we use an object variable (Set wks = Worksheets(1))?

[rory]

I suspect the issue may be, among other things, that the code would not compile if you then tried to refer to an ActiveX object that you had added to that sheet (since it would not be a member of the Worksheet class).