Is it possible to refer to a worksheet's object name in a formula rather
than it's display name ?

For example, Sheet1 has been renamed to Data.

On Sheet2, there are formulas that count errors on the Data worksheet
using COUNTIF (i.e., =COUNTIF(Data!$A$1:$E$25,"Error")

When saving the Data worksheet to another name, the formulas on Sheet2
recalculate and add about a minute to the file save time.

When writing the formula above, is it possible to create a UDF that
references Sheet1 instead of Data ?



- Ronald K.

on 8/30/2011, kittronald supposed :
Is it possible to refer to a worksheet's object name in a formula rather
than it's display name ?

For example, Sheet1 has been renamed to Data.

On Sheet2, there are formulas that count errors on the Data worksheet
using COUNTIF (i.e., =COUNTIF(Data!$A$1:$E$25,"Error")

When saving the Data worksheet to another name, the formulas on Sheet2
recalculate and add about a minute to the file save time.

When writing the formula above, is it possible to create a UDF that
references Sheet1 instead of Data ?



- Ronald K.

Here's a reusable function I got from Rob Bovey, which I believe is
also available in his Excel books. It allows you to use the codename to
find a sheet's 'tabname'. Normally, I would assign unique codenames to
a project wkb (or template) sheets that reflects their 'as released'
name so if users rename the sheets then my code has no problem finding
the new name.

Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As
String
Dim Wks As Worksheet
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.Name: Exit Function
Next
End Function

Example usage:
(At design time...)
Sheet1.CodeName = "wksExpenses"
Sheet1.Name = "Expenses"

User renames to "Cash Out"

I need to ref that sheetname in code:
Sheets(Get_SheetTabName(ActiveWorkbook, "wksExpenses")).Activate

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Corrections made below:
on 8/30/2011, kittronald supposed :
Is it possible to refer to a worksheet's object name in a formula
rather than it's display name ?

For example, Sheet1 has been renamed to Data.

On Sheet2, there are formulas that count errors on the Data worksheet
using COUNTIF (i.e., =COUNTIF(Data!$A$1:$E$25,"Error")

When saving the Data worksheet to another name, the formulas on Sheet2
recalculate and add about a minute to the file save time.

When writing the formula above, is it possible to create a UDF that
references Sheet1 instead of Data ?



- Ronald K.

Here's a reusable function I got from Rob Bovey, which I believe is also
available in his Excel books. It allows you to use the codename to find a
sheet's 'tabname'. Normally, I would assign unique codenames to a project wkb
(or template) sheets that reflects their 'as released' name so if users
rename the sheets then my code has no problem finding the new name.

Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As String
Dim Wks As Worksheet
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.Name: Exit Function
Next
End Function

Example usage:
(At design time...)

Sheets(1).CodeName = "wksExpenses"
Sheets(1).Name = "Expenses"

User renames to "Cash Out"

I need to ref that sheetname in code:
Sheets(Get_SheetTabName(ActiveWorkbook, "wksExpenses")).Activate

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Ok, it's been a while since I've been able to get back to this UDF due
to other processes having to work first.

What is the syntax of the workbook argument when using the UDF in a cell
?

In VB, using Activeworkbook works, but in a cell, the #VALUE error keeps
resulting.



- Ronald K.

kittronald laid this down on his screen :
Garry,

Ok, it's been a while since I've been able to get back to this UDF due to
other processes having to work first.

What is the syntax of the workbook argument when using the UDF in a cell
?

In VB, using Activeworkbook works, but in a cell, the #VALUE error keeps
resulting.



- Ronald K.

Make the following change to the args declaration, and ignore the Wkb
arg when using in a cell.

Function Get_SheetTabName(CodeName As String, Optional Wkb As Workbook)
As String
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook '//add this line
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.Name: Exit Function
Next
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Using a worksheet named Test with a codename of Sheet1.

When entering the formula below, the function returns a blank result.

=Get_SheetTabName("Sheet1")

Am I entering this wrong ?


- Ronald K.

kittronald has brought this to us :
Garry,

Using a worksheet named Test with a codename of Sheet1.

When entering the formula below, the function returns a blank result.

=Get_SheetTabName("Sheet1")

Am I entering this wrong ?


- Ronald K.

It works for me! Where did you put the function? It should be in a
standard module.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Using Excel 2007 SP2, in a standard module (Module1), the following is
used:

Function Get_SheetTabName(CodeName As String, Optional Wkb As
Workbook) As String
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook '//add this line
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.Name: Exit Function
Next
End Function


In a macro, the following is used:

Sub TEST()
x = Sheets(1).CodeName
Debug.Print x
y = Get_SheetTabName("Sheet1")
Debug.Print y
End Sub


Debug.Print x = Sheet1

Debug.Print y = TEST

In a cell, the following returns a #VALUE error:

=Get_SheetTabName("Sheet1")



- Ronald K.

Garry,

For some reason, it's working now ... after I pressed Send.

Perhaps, when something isn't working I can just press Send.


- Ronald K.

kittronald presented the following explanation :
Garry,

For some reason, it's working now ... after I pressed Send.

Perhaps, when something isn't working I can just press Send.


- Ronald K.

Hey Ronald, I don't think 'Send' has anything to do with it. I did have
an instance, though, where Excel refused to let my UDF work. I shutdown
and restarted Excel and all was good again! You're guess is as good as
mine as to what's going on.<g -Poltergeist!!!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

That appears to have been the issue.

Thanks for the help ... again !



- Ronald K.