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.