Using sheet codename problems

[Dustin Carter]

I'm having problems with the way I access sheets of the
ActiveWorkbook from an addin. Basically, I need to
rewrite the following line of code (simplified for this
example) to use the sheet's codename (SamplePrep) instead
of the sheet's position in the Sheets collection (sheets
(1)):

vResult = ActiveWorkbook.Sheets(1).Range("A:A").Find
(iSearchValue).Offset(0,2).Value

to something like this:

vResult = ActiveWorkbook.SamplePrep.Range("A:A").Find
(iSearchValue).Offset(0,2).Value

Any ideas or suggestions would be *greatly* appreciated.

Thanks in advance,
Dustin Carter

 

[John Green]

Dustin,

The codename should not be qualified:

vResult = SamplePrep.Range("A:A").Find

 

[Guest]

Thanks John
Sorry about the massive delay in a response, I figured out the problem somewhat on my own after more tinkering and kinda forgot about posting. I've read many places that there isn't really a way to access a sheet's codename from another workbook (addin), and was wondering if this was true

Thanks again
- Dustin Carter

 

[Bernie Deitrick]

Dustin,

You can access the codename using code of the syntax:

Workbooks("Name.xls").VBProject.VBComponents("SheetName").Properties("_CodeN
ame").Value

All on one line, of course, though this will probably line wrap.

HTH,
Bernie
MS Excel MVP

Thanks John,
Sorry about the massive delay in a response, I figured out the problem

somewhat on my own after more tinkering and kinda forgot about posting.
I've read many places that there isn't really a way to access a sheet's
codename from another workbook (addin), and was wondering if this was true?

 

[Guest]

Heh
Thanks for the suggestion Bernie, I haven't read that anywhere. But..
I know the codename, not necessarily the sheet name. I'm creating a template system that copies sheets to new workbooks from an addin. The calculations for each cell on the sheet are performed by the addin so that I can distribute a formula free sheet. I don't want to work off of the Sheet Name because the user needs to be able to change it. Herein lies the problem =
So really I'm just looking for a way to do Workbooks("WorkbookName").Codename. Or something like that

Thanks again
- Dustin Carte

----- Bernie Deitrick wrote: ----

Dustin

You can access the codename using code of the syntax

Workbooks("Name.xls").VBProject.VBComponents("SheetName").Properties("_Code
ame").Valu

All on one line, of course, though this will probably line wrap

HTH
Berni
MS Excel MV

Thanks John
Sorry about the massive delay in a response, I figured out the proble

somewhat on my own after more tinkering and kinda forgot about posting
I've read many places that there isn't really a way to access a sheet'
codename from another workbook (addin), and was wondering if this was true

 

[Bernie Deitrick]

Dustin,

If you know the codename, you can simply replace

Worksheets("Changeable Name").

with

SheetCodeName.

wherever it occurs in your code.

Most users aren't savvy enough to know how to change the codename manually,
much less in code.

HTH,
Bernie
MS Excel MVP

Heh,
Thanks for the suggestion Bernie, I haven't read that anywhere. But...
I know the codename, not necessarily the sheet name. I'm creating a

template system that copies sheets to new workbooks from an addin. The
calculations for each cell on the sheet are performed by the addin so that I
can distribute a formula free sheet. I don't want to work off of the Sheet
Name because the user needs to be able to change it. Herein lies the
problem =)

So really I'm just looking for a way to do

Workbooks("WorkbookName").Codename. Or something like that.

 

[Jake Marx]

Hi Dustin,

You're correct - you can't access a sheet by codename in another workbook
directly. Jim Rech showed how you can do it here:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=#HixsZufCHA.2160@tkmsftngp10

Another way to do this is to set a reference to the other workbook's VBA
Project. You could start by modifying the Name property of each VBA Project
so they are distinct. Maybe something like prjData for a data workbook.
Then, in workbook 2, you can set a reference via Tools | References to
"prjData". Now, you can access that project's worksheets by codename:

MsgBox prjData.wsData.Range("A1").Value

Typically, when I create an add-in and work with templates or other
workbooks, I use a series of hidden defined names to make sure I'm looking
at one of my templates (and the correct worksheet within the template).
That way, I can just look through the worksheets to see which one I need to
reference based on a hidden name and not the name of the worksheet.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

Hey Jake
Jim might be right in saying that his method isn't worth the trouble. I like the sound of your second idea, with the hidden defined names, but I'm not sure exactly how to start implementing it. Any suggestions? I really haven't used Names at all, up to this point, and none of the information I've been able to find talks about using them in this way

Thanks again, and sorry for all the struggling
- Dustin Carter

 

[Hamilton R. Romano]

Dustin,

Try this:

Function wks(wb As String, codName As String) As Worksheet
Set wks = Workbooks(wb).Worksheets(Application.VBE.VBProjects(Workbooks
_(wb).VBProject.Name).VBComponents(codName).Properties("Name").Value)
End Function

Sub Test()
Set wk = wks(ActiveWorkbook.Name, "SamplePrep")
vResult = wk.Range("A:A").Find(iSearchValue).Offset(0, 2).Value
End Sub

Regards,
Hamilton R. Romano

Hi Dustin,

You're correct - you can't access a sheet by codename in another workbook
directly. Jim Rech showed how you can do it here:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=#HixsZufCHA.2160@tkmsftngp10

Another way to do this is to set a reference to the other workbook's VBA
Project. You could start by modifying the Name property of each VBA Project
so they are distinct. Maybe something like prjData for a data workbook.
Then, in workbook 2, you can set a reference via Tools | References to
"prjData". Now, you can access that project's worksheets by codename:

MsgBox prjData.wsData.Range("A1").Value

Typically, when I create an add-in and work with templates or other
workbooks, I use a series of hidden defined names to make sure I'm looking
at one of my templates (and the correct worksheet within the template).
That way, I can just look through the worksheets to see which one I need to
reference based on a hidden name and not the name of the worksheet.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hello again Bernie,
As far as I can tell, your method doesn't work when accessing other
workbooks.

Sub foo()
Msgbox Workbooks("test.xls").codename.Name
End Sub

This returns an error code 438: Object doesn't support this
property or method. Any ideas? And, I guess while we're at it, is
there a definitive list of error codes somewhere? Their descriptions
really suck.

Thanks again for all the help,
- Dustin Carter

 

[Jake Marx]

Hi Dustin,

You create a hidden defined name in much the same way you would a "normal"
name, except that you set the Visible argument to False so it's not visible
in the UI:

Public Const gsNAME_KEY As String = "hbMRTemplate"

ThisWorkbook.Names.Add Name:=gsNAME_KEY, RefersTo:="=TRUE", _
Visible:=False

The RefersTo argument is the interesting one. Instead of using a Range
address or function, we simply put in a value. So, in this case, the
defined name isn't really used to name a Range but to store a name/value
pair in the Workbook. To check if the Workbook is the correct file, you can
then just check the value of that Name. Typically, I'll set up a set of
functions that manage this for me:

Public Function gbIsWBMRTemplate(rwb As Workbook) As Boolean
On Error Resume Next
gbIsWBMRTemplate = Len(rwb.Names(gsNAME_KEY))
On Error GoTo 0
End Function

[In this example, I only check for the existence of the defined name - if
you need to check the value, you could use
CBool(Mid$(rwb.Names(gsNAME_KEY).RefersTo, 2)) or similar. Notice I check
the value by ignoring the first character in the RefersTo property value - I
do this because the first character is the equal sign.]

You can do similar things to keep track of Worksheets by adding a hidden
defined name to each Worksheet object and checking those values when looping
through the Worksheets collection until you find the correct Worksheet(s)
that you want to work with.

Here is some more information on using Names:

http://www.cpearson.com/excel/named.htm

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Dave Peterson]

xl2002 added a user option that may make this fail:

Tools|Macro|security...|Trusted Sources Tab

If I turn off the "trust access to Visual basic project", it goes belly up.

(Just something to worry about for later!)

Dustin,

Try this:

Function wks(wb As String, codName As String) As Worksheet
Set wks = Workbooks(wb).Worksheets(Application.VBE.VBProjects(Workbooks
_(wb).VBProject.Name).VBComponents(codName).Properties("Name").Value)
End Function

Sub Test()
Set wk = wks(ActiveWorkbook.Name, "SamplePrep")
vResult = wk.Range("A:A").Find(iSearchValue).Offset(0, 2).Value
End Sub

Regards,
Hamilton R. Romano

Hi Dustin,

You're correct - you can't access a sheet by codename in another workbook
directly. Jim Rech showed how you can do it here:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=#HixsZufCHA.2160@tkmsftngp10

Another way to do this is to set a reference to the other workbook's VBA
Project. You could start by modifying the Name property of each VBA Project
so they are distinct. Maybe something like prjData for a data workbook.
Then, in workbook 2, you can set a reference via Tools | References to
"prjData". Now, you can access that project's worksheets by codename:

MsgBox prjData.wsData.Range("A1").Value

Typically, when I create an add-in and work with templates or other
workbooks, I use a series of hidden defined names to make sure I'm looking
at one of my templates (and the correct worksheet within the template).
That way, I can just look through the worksheets to see which one I need to
reference based on a hidden name and not the name of the worksheet.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hello again Bernie,
As far as I can tell, your method doesn't work when accessing other
workbooks.

Sub foo()
Msgbox Workbooks("test.xls").codename.Name
End Sub

This returns an error code 438: Object doesn't support this
property or method. Any ideas? And, I guess while we're at it, is
there a definitive list of error codes somewhere? Their descriptions
really suck.

Thanks again for all the help,
- Dustin Carter

 

[Jake Marx]

Hi Dustin,

Wow, thanks for all the replies, guys.

No problem - glad to help out!

Seriously, though, you guys are life savers. Well, that may be a bit
extreme. We'll settle for sanity savers. Is there a way I can
reciprocate the help? I've been working on this for a while now, and
have accumulated alot of knowledge through trial and error that
wasn't readily available on the net, especially in templating and
completely user transparent calculations (efficiently on vast amounts
of data). Should I just set aside a half hour and browse the
newsgroups looking for things I can help with?

We all contribute to these newsgroups as volunteers, so you shouldn't feel
as if you need to "pay us back". For me, an occasional thank you is more
than sufficient. Not to mention that I continue to learn new things every
day by participating. But you can definitely "pay it forward" by helping
others with some issues if you'd like. That's the whole spirit of these
newsgroups: ask questions when you need help, and reply when you can help
someone else.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]