Try your function on a CSV file that you just opened. Let me know if it
works then.

Mike.

"J.E. McGimpsey" wrote in message
...
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.