Solved: Referencing a Sheet w/ Sheet Index Question

[YellowLabPro]

I stumbled onto an interesting detail in referencing a worksheet.
This code resides in a module within the workbook VBALessons.xls which is open along with Book1.xls.
The ActiveWorkbook is Book1.xls.

This first two lines return the same results in Book1.xls

Sheets(1).Range("a:i").ColumnWidth = 12
Sheets("Sheet 1").Range("a:i").ColumnWidth = 12


But this one does not return the result on the worksheet in Book1.xls, but rather in VBALessons.xls

Sheet1.Range("a:i").ColumnWidth = 12


It took me a minute to figure out that its reference is back in the workbook where the module resides, VBALessons.xls.

I thought to try the following- but it does not work.
Workbooks("Book1.xls").Sheet1.Range("a:i").ColumnWidth = 12
Is the index reference of Sheet1 somehow tied to where the code lives?This question is mere curiousity/interest.

[Norie]

Doug

This isn't using the sheet's index, it's using the sheet's codename.:bug:

Sheet1.Range("a:i").ColumnWidth = 12

:shark::shark::shark::shark::shark:

[YellowLabPro]

Ahhh- thanks Norie, you are right, got my terms crossed up. And this throws some light on the matter.
The question still is out there, just slightly different....
Would that mean that since it is the sheet's code name that it can only be referenced w/in its own book, like it owns it, and my method of referencing it is saying No Way Dude!?
The out of subscript error appears to support this theory.

[Norie]

Doug

I'd actually, personally, not bother about things like this.

Sure it's interesting but the answer whatever that is, if there is one, isn't really going to help as far as I can see.

Perhaps you should concentrate on ensuring you qualify all your references properly and/or try not to mix up your syntax.

[YellowLabPro]

you should concentrate on ensuring you qualify all your references properly
That is exactly what I am doing.... by learning what is going on, I am learning what is proper referencing, proper syntax, concepts, methods etc...
Because you know it and understand it so well it is second nature to you. But to me, things are still fuzzy, and by drilling down on this, it helps me learn what is correct, and proper-- .... that is just the way it is for me. I mean no disrespect here, To say not to worry about it, is not an option for me. It is the way I store and process information.

[Norie]

Doug

No problem.

I know you are trying to learn, and that's a good thing.

I've lost count of the number of people who expect an answer on a plate when posting here and to similar forums.:bug:

By the way this sort of stuff isn't really second nature to me, I still use the macro recorder, help and the object browser.

Sure, somethings I don't have problems with but others I've no idea about.

For example complex formulas. :wot :help

[austenr]

For complex formula help XLD is my go to person!!

[Tommy]

Hi YLP,



Would that mean that since it is the sheet's code name that it can only be referenced w/in its own book, like it owns it, and my method of referencing it is saying No Way Dude!?



Should be more like What are you trying to do Dude?? :devil2:

The term Sheet1 should point to the particular sheet.
ex
Workbooks("Book1.xls").WorkSheets("Sheet-BR549")

would be the same as Sheet1. Sheet1 is actually a variable, it could be called "VBALessonsSheet1" just as easy. I may have called it LssnSht (LessonSheet)

I sincerly hope this has helped and not confused you even more.: pray2:
I can code, I can show you, but for the life of me I can't seem to explain worth a flip. :rofl:

[YellowLabPro]

Hello Tommy,
I think I understand what you are saying how it could/should be referenced.... & I am the same way- I cannot explain things for anything....
What I was driving at in my last example was, it failed, why did if fail? (I am experimenting w/ the different options and not in need of a specific solution on this one). From what I THINK I have figured w/ Norie's pointing out that this is the sheet's code name and not the index position, was that the reference is trying to point to a sheet's name where the module that the code lives in is not in the same book, so it is out of range.

Originally I had two things going on-
This line performed the action, just not where I thought it was going to:
Sheet1.Range("a:i").ColumnWidth = 12
It performed it back on VBALessons, where I thought it was going to do it on the activeworkbook, Book1.xls.
So by changing a working line to target the activeworkbook,
Workbooks("Book1.xls").Sheet1.Range("a:i").ColumnWidth = 12
and it errored- it through me an unexpected result. But again, it was because I was thinking Sheet1 was the index value of the sheet, not the sheets's code name.
Why it through the Subscript 9 error was again like-- wait a minute both books are open, why am I getting this-- but now it seems pretty straight forward that the module is in VBALessons.xls and the reference sheet is being told to go to Book1.xls and the sheet's code name does not exist in Book1.xls.

[Bob Phillips]

Doug,

The codename of a worksheet is internal to VBA, specifically and exclusively. I don't know why, but the designers of Excel decided to add another property of a sheet that was not changeable from within Excel, but is within VBA. I say I don't know why, but I understand how it would help, but the implementation is very poor IMO.

I believe that you can only address the codename of a sheet within the workbook that the code resides. Also, in addins, the codename of a sheet in the addin often returns blank, and the resolution invariably requires Trust Access to VBA Projects. So in reality it is severely crippled IMO.

[YellowLabPro]

Thanks Bob....
I get what you are saying.... and by learning the parameters it goes to the long haul in developing my knowledge of the OBM.

[rory]

My interpretation is that Sheet1 is basically a restricted type of class and as such is only available to the project that contains it. I may well be completely wrong though! :)

[Bob Phillips]

Type of class? What sort of class is codename? It is a property pure and simple. Select a sheet and look at the properties window, kind of gives the game away. It is a property of the VBComponent of the VBProject.

The worksheet is class clearly, but it is not available only to the project, and that is not the thrust of this thread.

[rory]

I didn't say codename was a class; it just so happens that the codename is the same as the class name. Hence the fact that you can declare a variable 'As Sheet1' which would not be possible if Sheet1 were merely a property.
It seems to me that when you add a new worksheet you get a new class (and Interface) created which has the same name as the codename, and a new instance of the class at the same time. It's somewhat similar to what happens with userforms, except that you cannot use the New keyword to create instances of the Sheet1 class.
As I said, I may be wrong, but that makes more sense to me than the idea that I could simply use a property as an object (and an interface).

[YellowLabPro]

Bob,
I just went to the Properties Window to see how you and Rory were referring to this and this sparks a thought of something that you mentioned a while back. In the Properties Window, there are two Name fields, the code's Sheet name represented as (Name) and Excel's editable Sheet name represented as Name, non-bracketed.
Changing the Sheet Name in Excel is for obvious reasons. But reasons for changing the code's Sheet Name is not so obvious to me. I can't recall if you said it would have to do w/ protecting the sheet from other's mucking w/ the code.... or maybe something to this effect, or maybe something completely different.
Can you remind me of the reason(s) we might change the code's sheet name?

Persuing this a little further, by me changing the code's sheet name, I now have Test(TGFF), which originally was Sheet1(TGFF) and, remember, is in the non-activeworkbook, makes perfect sense why trying to reference back to it, whilst performing some task on the activeworkbook from the other book's Project Module is completely illegal. I got it yesterday, but by changing the code's Sheet Name just helped me see it that much more clearly.

[Bob Phillips]

I change the codename because I don't like the defaults. Just as Sheet1, 2, etc in Excel is just a meaninglerss series, so is Sheet1, 2, etc. in VBA.

[YellowLabPro]

Man, and I thought you would have a really profound answer here....:*)

[Bob Phillips]

It's my rebellious streak, don't like conform to what THEY dictate. Perhaps that is why I detest the ribbon.