I'm not sure what you mean by "the "CodeName" property doesn't exist
until you enter the VBE environment."

When I put the code into a UDF, it works fine when called from the
worksheet, e.g.:

Public Function foo()
Dim ws As Worksheet
Dim found As Boolean
Application.Volatile
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
found = True
Exit For
End If
Next ws
If found Then
'do stuff, e.g.,
foo = Application.CountIf(ws.Cells, "5")
Else
foo = CVErr(xlErrRef)
End If
End Function

and call it like:

=foo()

I get a valid return (as long as there's no circular reference).
Worked fine when foo() was in an add-in, too.

In article ,
"Michael D. Ober" <[email protected] wrote:

I just tried this and it still didn't work. After putting in some msgbox
statements, I discovered that the "CodeName" property doesn't exist until
you enter the VBE environment. Your comment about using for each ... next
instead of for i ... next is valid in general. However, I found that a
workbook will always have at least one worksheet, thus the for i ... next
does work. I did switch to for each ... next because it makes the code
shorter and easier to read.