Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 --- |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 --- |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 --- |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 --- |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() 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. |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#9
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#10
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() 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 |
#11
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#12
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#13
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#14
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#15
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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.) |
#16
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#17
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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] |
#18
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 --- |
#19
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Thanks for the views. Appreciated.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#20
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Thanks for the views. Appreciated.
I'm still tracking the flow-through discussions <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#21
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#22
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#23
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Thanks for all the clarifications, Dave.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
macro to print sheet2 without open sheet2 | Excel Discussion (Misc queries) | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
Go to sheet2 | Excel Programming | |||
first name and a second name - In Sheet2 I only need the first name | Excel Programming |