Worksheet Set - question on Set?

[itr674]

When I use this code:

Set MenuSheet = ThisWorkbook.Sheet2

I get - "Compile Error: Method or Data Member Not Found."

I have declared MenuSheet with - Dim MenuSheet As Worksheet

When I use:

Set MenuSheet = ThisWorkbook.Sheets(2)

Everything works fine???
This message was edited by em on 2002-10-18 09:36

 

[stevebausch]

Sheet2 is a codename for a sheet. Does it exist?

Sheets(2) is an index for the second sheet, counting from the left sheet tab.
Rearrange the sheets, and you will index into another sheet.

_________________
This message was edited by stevebausch on 2002-10-13 01:04

 

[itr674]

yes-Sheet2 does exist. The tab name is MenuSheet.

The line of code works when using Sheets(2) or Sheets("MenuSheets"), but when I use Sheet2 I get the compile error??

 

[XL-Dennis]

em,

A worksheet is part of the Worksheets-collection.

You can refer to it either by it index-number och by it name:

Indexnumber
Set MenuSheet = ThisWorkbook.Worksheets(2)

By name:
Set MenuSheet = ThisWorkbook.Worksheets("MenuSheet")

Kind regards,
Dennis

 

[itr674]

I have started using the sheet "code" name instead of the "index" or "sheet name", so my question is, why does the sheet index and sheet name work. but when I use the "code" name it does not???

does not work - Set MenuSheet = ThisWorkbook.Sheet2

works - Set MenuSheet = ThisWorkbook.Sheets(2) 0r Sheets("MenuSheet")

WHY?????

 

[XL-Dennis]

em,

A worksheet has an indexnumber and a name and is a part of the worksheet-collection.

A codename refer to a VBA-module, which is part of the workbook VBA-project.

A worksheet has a VBA-module and the codename for it may differ from it´s worksheetname.

The module (i e the codename) is not available in the context You trying to access it.

OK?
Kind regards,
Dennis

 

[stevebausch]

I am showing my ignorance here, be gentle with me.

Why are you using the Set command with Worksheets? As I understand, the Set command, without New, just creates an alias, for all intents and purposes.

If it's just another name, referring to the same worksheet, aren't you making the code a bit harder to comprehend?

Here is a line from VBA Help, Excel 2002:
Generally, when you use Set to assign an object reference to a variable, no copy of the object is created for that variable. Instead, a reference to the object is created

If you are trying to clean-up code and make it readable, wouldn't a With ...End With construct be a better choice?

 

[Legacy 98055]

When referring to a collection, as earlier stated, you refer to the index.
If the name of your first worksheet is "MenuSheet", you can use the index in two ways. Both ways refer to the index.

Sheets(1)
or
Sheets("MenuSheet")

The code name refers to a class name.
The Sheet2 class is a member of VBAProject.
To use the correct syntax to set a reference to this class, use the following.
Set MenuSheet = VBAProject.Sheet2
Plain English.
Give me the location in memory which holds the reference to the class Sheet2 which is a member of the VBAProject class.
I hope I understand this myself?
Tom

 

[stevebausch]

TsTom,
Thanks for the "VBAProject" explanation. I knew that Sheet2 wasn't being offered by Intellisense when ThisWorkbook was the 'prefix'.

However, use "VBAProject", and Sheet2 shows up in the Intellisense menu.

Thanks so much,
SteveBausch

 

[Legacy 98055]

Yeah Steve.
If you go into your object browser, type in "Sheet2", and search. It will show you "who" Sheet2 belongs to(Member of). In this case the VBAProject class library or VBAProject collection of classes. You will also note that "ThisWorkbook" is a member of the same library.
This message was edited by TsTom on 2002-10-13 09:29