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