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

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
---

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

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.

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

The only time I've seen the codename fail is when I'm adding a new sheet and the
VBE hasn't been opened. But in other cases, I don't remember seeing any
problem.

And if "can all work" means that your macro stops with a warning message that
things aren't right, then I see your point <vbg.

(I've used a hidden worksheet level name to make sure that I'm on a "correct"
type of worksheet, too.)

Jim Cone wrote:


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

--

Dave Peterson

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

After you change the codename from something like Sheet1, do you continue to
change it to other names?

Why would you do that? I've had second thoughts about names of things
(including worksheet names and worksheet codenames), but I don't change the
codenames very often.

And my definition of a "normal" user is one who is not a developer of the
workbook.

Dallman Ross wrote:

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

--

Dave Peterson

In , Dave Peterson
spake thusly:

After you change the codename from something like Sheet1, do you
continue to change it to other names?

Sometimes.

Why would you do that? I've had second thoughts about names of
things (including worksheet names and worksheet codenames), but I
don't change the codenames very often.

In a workbook I've been VBA-coding in for about three weeks now
(it's pretty much done), :-) as an example, I have a bunch of
sheets named things like "Order Status - P 1" through "- P8".
I also have two other sheets: "Order Status - Pending" and
"Order Status - New". These sheets happen to contain web
queries and pull down those pages from my open orders at
my broker's site. Well, I added and deleted sheets a few
times, so the code names for the sheets got jumbled in the
process. When I was looking at the project, it was confusing
to me to see "- P5" called "Sheet7" or something in the code.
Even if it didn't really confuse me, it upset my general
sense of order about the universe. So I changed the names.
I added "- P 7" and "- P 8" later and their codenames were
numbered after the ones for the "- New" and "- Pending",
too. I changed all those. I changed the "- New" one's
codepage to "SheetN", for example. There is a main
"MergeSheet" in the book, and I changed its codename to
"Sheet0".

You have to realize that I'm the kind of person who goes
through wordprocessing documents deleting invisible whitespace
from the ends of paragraphs, just because it ought not to
be there. I've always done that. Actually, I may be
relaxing a bit, because in recent years I've started
consciously trying not always to do that. :-)

I have also walked all the way down stairs in order
to recycle one business card rather than throw it in
the regular trash. :-)

And my definition of a "normal" user is one who is not a
developer of the workbook.

Hmm. Okay, I'm not "normal," then. (I guess I let that cat out
of the bag a couple of paragraphs ago, anyway.) But I work for
me. I am too cheap to hire someone to do my Excel stuff, and like
doing it myself anyway, and too much of a perfectionist to be
satisfied with what such a person I might hire would turn out. (I
couldn't afford somebody like you.) :-)

Notice that I deleted my .sig from the end of the quoted
message below, too. I usually do that. It's wasted text
that doesn't need to be there. I have a hard time in these
Microsoft groups with the top-posting, but I try to cope
there and make some compromises so that you guys will still
answer some of my questions. :-) I've been posting to Usenet
since 1990, and I am pretty rigorous about what I consider
rules of netiquette . . . . (Oh, and ellipses should have
spaces between the dots, as I just did, and whether there
are three or four dots is also a matter of a fine rule.)

dman
============================
Dallman Ross wrote:

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.)

My only comment is that I would choose more mnemonically significant codenames
than SheetN.

I'd take the time to rename that code name to NewSheet (or SheetNew)....

But then I'd never change it again.

<vbg

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

After you change the codename from something like Sheet1, do you
continue to change it to other names?

Sometimes.

Why would you do that? I've had second thoughts about names of
things (including worksheet names and worksheet codenames), but I
don't change the codenames very often.

In a workbook I've been VBA-coding in for about three weeks now
(it's pretty much done), :-) as an example, I have a bunch of
sheets named things like "Order Status - P 1" through "- P8".
I also have two other sheets: "Order Status - Pending" and
"Order Status - New". These sheets happen to contain web
queries and pull down those pages from my open orders at
my broker's site. Well, I added and deleted sheets a few
times, so the code names for the sheets got jumbled in the
process. When I was looking at the project, it was confusing
to me to see "- P5" called "Sheet7" or something in the code.
Even if it didn't really confuse me, it upset my general
sense of order about the universe. So I changed the names.
I added "- P 7" and "- P 8" later and their codenames were
numbered after the ones for the "- New" and "- Pending",
too. I changed all those. I changed the "- New" one's
codepage to "SheetN", for example. There is a main
"MergeSheet" in the book, and I changed its codename to
"Sheet0".

You have to realize that I'm the kind of person who goes
through wordprocessing documents deleting invisible whitespace
from the ends of paragraphs, just because it ought not to
be there. I've always done that. Actually, I may be
relaxing a bit, because in recent years I've started
consciously trying not always to do that. :-)

I have also walked all the way down stairs in order
to recycle one business card rather than throw it in
the regular trash. :-)

And my definition of a "normal" user is one who is not a
developer of the workbook.

Hmm. Okay, I'm not "normal," then. (I guess I let that cat out
of the bag a couple of paragraphs ago, anyway.) But I work for
me. I am too cheap to hire someone to do my Excel stuff, and like
doing it myself anyway, and too much of a perfectionist to be
satisfied with what such a person I might hire would turn out. (I
couldn't afford somebody like you.) :-)

Notice that I deleted my .sig from the end of the quoted
message below, too. I usually do that. It's wasted text
that doesn't need to be there. I have a hard time in these
Microsoft groups with the top-posting, but I try to cope
there and make some compromises so that you guys will still
answer some of my questions. :-) I've been posting to Usenet
since 1990, and I am pretty rigorous about what I consider
rules of netiquette . . . . (Oh, and ellipses should have
spaces between the dots, as I just did, and whether there
are three or four dots is also a matter of a fine rule.)

dman
============================
Dallman Ross wrote:

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.)

--

Dave Peterson

Thanks, Dave. Noted. <vbg back atcha

Dallman

======================
In , Dave Peterson
spake thusly:

My only comment is that I would choose more mnemonically
significant codenames than SheetN.

I'd take the time to rename that code name to NewSheet (or
SheetNew)....

But then I'd never change it again.

<vbg

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

After you change the codename from something like Sheet1, do
you continue to change it to other names?

Sometimes.

Why would you do that? I've had second thoughts about
names of things (including worksheet names and worksheet
codenames), but I don't change the codenames very often.

In a workbook I've been VBA-coding in for about three weeks
now (it's pretty much done), :-) as an example, I have a bunch
of sheets named things like "Order Status - P 1" through "-
P8". I also have two other sheets: "Order Status - Pending"
and "Order Status - New". These sheets happen to contain web
queries and pull down those pages from my open orders at my
broker's site. Well, I added and deleted sheets a few times,
so the code names for the sheets got jumbled in the process.
When I was looking at the project, it was confusing to me to
see "- P5" called "Sheet7" or something in the code. Even
if it didn't really confuse me, it upset my general sense of
order about the universe. So I changed the names. I added
"- P 7" and "- P 8" later and their codenames were numbered
after the ones for the "- New" and "- Pending", too. I changed
all those. I changed the "- New" one's codepage to "SheetN",
for example. There is a main "MergeSheet" in the book, and I
changed its codename to "Sheet0".

You have to realize that I'm the kind of person who goes
through wordprocessing documents deleting invisible whitespace
from the ends of paragraphs, just because it ought not to be
there. I've always done that. Actually, I may be relaxing a
bit, because in recent years I've started consciously trying
not always to do that. :-)
[rest deleted]

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
---

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

Thanks for the views. Appreciated.

I'm still tracking the flow-through discussions <g
--
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
---