Reliably get sheet 1 of the active workbook

[Michael D. Ober]

I have some VBA code that does the following:

Option Compare Text

for i = 1 to ActiveWorkBook.Worksheets(i)
if ActiveWorkBook.Worksheets(i).CodeName = "Sheet1" then
set ws = ActiveWorkBook.Worksheets(i)
exit for
end if
next i

The problem is that this code doesn't always return as expected. In the
debuger, it always works, when run from an Add-In menu item, it works about
10% of the time.

Any ideas,
Thanks,
Mike.

 

[Ken Macksey]

Hi

Not sure exactly what you are trying to do, but try this .
You may be able to modify to work as you want.

For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveWorkbook.Worksheets(i).CodeName = "Sheet1" Then
Set ws = ActiveWorkbook.Worksheets(i)
ws.Activate
Exit For
End If
Next i

HTH

Ken

 

[J.E. McGimpsey]

I would expect it to fail 100% of the time, unless you set i to
something > 0 before the for i = ... line (since Worksheets(i) will
return a subscript out of range error if i=0).

How about:

Dim ws As Worksheet
Dim found As Boolean
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.,
MsgBox ws.Name
Else
MsgBox "No worksheets found with codename Sheet1"
End If

 

[Michael D. Ober]

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.

Thanks,
Mike Ober.

 

[J.E. McGimpsey]

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.

 

[Michael D. Ober]

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

Mike.

 

[J.E. McGimpsey]

Works fine.

Instead of challenging, why not explain fully what problem you're
seeing. Beats playing "bring me a rock..."