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.
Worksheet intelli-sense
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Worksheet intelli-sense
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet intelli-sense
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.
IntelliSense can't work with generic Objects.
IntelliSense can't work with generic Objects.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Worksheet intelli-sense
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?)
(I like your shaped arrow - is that from the drawing toolbar, or do you prefer a particular app for screenshots?)
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet intelli-sense
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)agibsonsw wrote:Shame they couldn't resolve this in a later version.
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.agibsonsw wrote:(I like your shaped arrow - is that from the drawing toolbar, or do you prefer a particular app for screenshots?)
Best wishes,
Hans
Hans
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Worksheet intelli-sense
My way around this problem is to declare an object variable:
Dim oSh as Worksheet
and then use that.
Dim oSh as Worksheet
and then use that.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Worksheet intelli-sense
That's what I do, and I guess Andy too:
agibsonsw wrote:Why do we then receive help if we use an object variable (Set wks = Worksheets(1))?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Worksheet intelli-sense
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).
Regards,
Rory
Rory