Set rngA = Sheets(2).Range("A:A")

Does Sheets(2) mean the 2nd tab from the left?


In VBE, under Microsoft Excel Objects,
the 2nd tab appears as: Sheet2 (Sheet2)

What is the first instance of Sheet2 in the above ?

I gather that the 2nd instance -- within parens -- is the tabname, which can
be changed by user.

How could use be made of the 1st instance which apparently cannot be changed
and is more robust? An example line or 2 illustrating this usage would be
welcomed.

Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

The codename is that name to the left. The worksheet name (inside ()'s) is the
name you see on the worksheet tab.

The codename can be changed in code or via the properties window in the VBE.
But it is much more difficult for the average user to change that codename.

So when you have a worksheet named "Prices", but with code name of PricesWks,
you could use:

priceswks.range("a1").value = 1234.23

If you had done something like:

worksheets("Prices").range("a1").value = ...

And the user changes the name on the tab, then the code will break.

And yep. Sheets(2) is the second sheet counting from the left.

Max wrote:

Set rngA = Sheets(2).Range("A:A")

Does Sheets(2) mean the 2nd tab from the left?

In VBE, under Microsoft Excel Objects,
the 2nd tab appears as: Sheet2 (Sheet2)

What is the first instance of Sheet2 in the above ?

I gather that the 2nd instance -- within parens -- is the tabname, which can
be changed by user.

How could use be made of the 1st instance which apparently cannot be changed
and is more robust? An example line or 2 illustrating this usage would be
welcomed.

Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--

Dave Peterson

The codename can be changed in code ...

An example or 2 of how the above could be done, Dave?

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewCodeName"
where sheet1 is the code name

or
dim wks as worksheet
set wks = activesheet
ThisWorkbook.VBProject.VBComponents(wks.codename). Name = "NewCodeName"
'or
ThisWorkbook.VBProject.VBComponents(wks.CodeName) _
.Properties("_CodeName").Value = "NewCodeName2"


But you'll have to toggle a security setting
tools|macro|security|trusted publisher tab
check "trust access to Visual basic project"

(added in xl2002 IIRC--who knows where it's hiding in xl2007 <vbg.)


Max wrote:

The codename can be changed in code ...

An example or 2 of how the above could be done, Dave?

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--

Dave Peterson

Ps.

I saw a note from Chip Pearson about the first version.
Don't use it in xl97.
http://support.microsoft.com/kb/q172500/

Going through the .properties("_Codename") is ok, though.

Dave Peterson wrote:

ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewCodeName"
where sheet1 is the code name

or
dim wks as worksheet
set wks = activesheet
ThisWorkbook.VBProject.VBComponents(wks.codename). Name = "NewCodeName"
'or
ThisWorkbook.VBProject.VBComponents(wks.CodeName) _
.Properties("_CodeName").Value = "NewCodeName2"

But you'll have to toggle a security setting
tools|macro|security|trusted publisher tab
check "trust access to Visual basic project"

(added in xl2002 IIRC--who knows where it's hiding in xl2007 <vbg.)

Max wrote:

The codename can be changed in code ...

An example or 2 of how the above could be done, Dave?

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--

Dave Peterson

--

Dave Peterson

Thanks for all the clarifications, Dave.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Some quick testing reveals that yes the sheets are ordered in their workbook
position. So when I used either this loop
For Each s In ActiveWorkbook.Sheets
or
For c = 1 To ActiveWorkbook.Sheets.Count
The sheets processed in the exact same order, from first sheet showing on
the left to the last one showing on the right in tab order.




"Max" wrote:

Set rngA = Sheets(2).Range("A:A")

Does Sheets(2) mean the 2nd tab from the left?


In VBE, under Microsoft Excel Objects,
the 2nd tab appears as: Sheet2 (Sheet2)

What is the first instance of Sheet2 in the above ?

I gather that the 2nd instance -- within parens -- is the tabname, which can
be changed by user.

How could use be made of the 1st instance which apparently cannot be changed
and is more robust? An example line or 2 illustrating this usage would be
welcomed.

Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I skimped on that last reply.

To refer to a sheet directly, you would use:

Sheets("Sheetname")

and a range on that sheet would be for example

Sheets("Sheetname").Range("A1:B2")

You can/should of course precede Sheets with a workbook object. I would also
suggest using the Cells convention as my experience has been it is somewhat
more efficient and easier to program for processing once you get used to it.
The two lines below are equivalent:

Sheets("Sheetname").Range("A1")
Sheets("Sheetname").Cells(1, 1)

Also note: Sheets is a convention that refers to any sheet, either Worksheet
or a Chart sheet. Using Worksheets intead of Sheets (in For loops especially)
will refer to only the worksheet type of sheet.




"Max" wrote:

Set rngA = Sheets(2).Range("A:A")

Does Sheets(2) mean the 2nd tab from the left?


In VBE, under Microsoft Excel Objects,
the 2nd tab appears as: Sheet2 (Sheet2)

What is the first instance of Sheet2 in the above ?

I gather that the 2nd instance -- within parens -- is the tabname, which can
be changed by user.

How could use be made of the 1st instance which apparently cannot be changed
and is more robust? An example line or 2 illustrating this usage would be
welcomed.

Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks for the views. Appreciated.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sheets is the collection object, of course.

Inside the parentheses:
(1), (2), etc. are the index numbers for the collection array which is
automatically assigned by the system in the order that they appear at the
bottom of your screen, no matter what names or numbers appear on the tabs.
You can verify this by opening the VB editor and you will see them in the
same order, unless someone has changed the code name. Even if someone
changes the code name, it will still sort in the sequence of the code name to
apply the index number.

("Sheet1"), ("Sheet2"), etc. the default name on the tab can be changed to
whatever you want but the index number remains the same.

Don't get into the code name, if you can help it.

"Max" wrote:

Set rngA = Sheets(2).Range("A:A")

Does Sheets(2) mean the 2nd tab from the left?


In VBE, under Microsoft Excel Objects,
the 2nd tab appears as: Sheet2 (Sheet2)

What is the first instance of Sheet2 in the above ?

I gather that the 2nd instance -- within parens -- is the tabname, which can
be changed by user.

How could use be made of the 1st instance which apparently cannot be changed
and is more robust? An example line or 2 illustrating this usage would be
welcomed.

Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You may find that using the codename makes your procedure much more robust than
using the worksheet name. I don't think I'd warn people to stay away from using
it.



JLGWhiz wrote:
<<snipped

Don't get into the code name, if you can help it.

Once bitten, twice shy...
I had difficulties using codenames a few years back and since then never
use them. I prefer to set an object reference to a sheet and use that
through out the code.
(the above ought to help this discussion really get going, maybe it will work as
well as recommending Database functions instead of SumProduct formulas) <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Dave Peterson"
wrote in message
You may find that using the codename makes your procedure much more robust than
using the worksheet name. I don't think I'd warn people to stay away from using
it.



JLGWhiz wrote:
<<snipped

Don't get into the code name, if you can help it.

Do you protect the structure of every workbook that you develop so that users
can't rename the worksheets?

Or do you find the correct worksheet some other way -- in case that worksheet is
renamed?



Jim Cone wrote:


Once bitten, twice shy...
I had difficulties using codenames a few years back and since then never
use them. I prefer to set an object reference to a sheet and use that
through out the code.
(the above ought to help this discussion really get going, maybe it will work as
well as recommending Database functions instead of SumProduct formulas) <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Dave Peterson"
wrote in message
You may find that using the codename makes your procedure much more robust than
using the worksheet name. I don't think I'd warn people to stay away from using
it.

JLGWhiz wrote:
<<snipped

Don't get into the code name, if you can help it.


--

Dave Peterson

There ain't no easy way. Am I the only one who has found the use of code
names unreliable? It would make life much easier to be able to use them.
Using object references, identifying sheets by a unique feature,
and using a VerifySheetExists function..."Can't find the Total Tally Sheet"
can all work.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Dave Peterson"
wrote in message
Do you protect the structure of every workbook that you develop so that users
can't rename the worksheets?
Or do you find the correct worksheet some other way -- in case that worksheet is
renamed?



Jim Cone wrote:


Once bitten, twice shy...
I had difficulties using codenames a few years back and since then never
use them. I prefer to set an object reference to a sheet and use that
through out the code.
(the above ought to help this discussion really get going, maybe it will work as
well as recommending Database functions instead of SumProduct formulas) <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Dave Peterson"
wrote in message
You may find that using the codename makes your procedure much more robust than
using the worksheet name. I don't think I'd warn people to stay away from using
it.

JLGWhiz wrote:
<<snipped

Don't get into the code name, if you can help it.
--
Dave Peterson

Using is one thing, changing them is another. There is no visibility if they
are changed, without going to the VB editor project window. My preference is
to stick with stuff that I can see, unless I want to purposely hide something.

"Dave Peterson" wrote:

You may find that using the codename makes your procedure much more robust than
using the worksheet name. I don't think I'd warn people to stay away from using
it.



JLGWhiz wrote:
<<snipped

Don't get into the code name, if you can help it.

I don't think I've ever seen a "normal" user change the codename of a sheet. So
in my situation, the only way a codename is changed is by the developer (usually
me). And that provides all the visibility I need.



JLGWhiz wrote:

Using is one thing, changing them is another. There is no visibility if they
are changed, without going to the VB editor project window. My preference is
to stick with stuff that I can see, unless I want to purposely hide something.

"Dave Peterson" wrote:

You may find that using the codename makes your procedure much more robust than
using the worksheet name. I don't think I'd warn people to stay away from using
it.



JLGWhiz wrote:
<<snipped

Don't get into the code name, if you can help it.



--

Dave Peterson

In , Dave Peterson
spake thusly:

I don't think I've ever seen a "normal" user change the codename
of a sheet. So in my situation, the only way a codename is
changed is by the developer (usually me). And that provides all
the visibility I need.

Hmm. I change the codenames fairly often. I'm somewhat of
a power user, but still "normal." My VBA skills are very
intermediate, but improving at pace. (Thanks, partly, to these
great groups.)

--
dman

Thanks for the views. Appreciated.

I'm still tracking the flow-through discussions <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---