Microsoft Visual Basic for Applications Extensibility 5.3 Issue

[Barb Reinhardt]

I am using the following bit of code

On Error Resume Next
Set FindWorksheet =
myXLBook.Worksheets(CStr(myXLBook.VBProject.VBComponents(myWSCodename).Properties("Name")))
On Error GoTo 0

to find a worksheet. FindWorksheet is defined as a Worksheet. This code
works on my computer when the Microsoft Visual Basic for Application
Extensibility 5.3 reference is unchecked. FWIW, I'm using Excel 2003 on
Windows XP.

A colleague is running the same code in her machine with the reference
unchecked and it's not finding anything. She's also using Office 2003 and I
suspect she's using Windows XP, but I'm not certain. When we select the
reference to the Extensibility library on her machine, everything works fine.


Gary's Student has helped some in this thread

http://www.microsoft.com/office/com...&p=1&tid=644e30de-64be-4805-9a49-5eec3868474a

, but I still don't understand why it works on some machines and not on
others. I really don't want to have to redistribute this application again.

Can someone assist?

Thanks,
Barb Reinhardt

 

[Peter T]

If you are using Late binding, which it seems you are, the reference and the
Extensibility version is not relevant.

If Trust Access to VBProjects is not allowed the code will fail.
There could be all sorts of other reasons it'll fail with names etc not as
expected


If all you are trying to do is set a reference to a sheet with known
codename, at least it looks like that's what you are after

For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = myWSCodename Then
Exit For
End If
Next

If Not ws Is Nothing Then
MsgBox ws.Name, , myWSCodename
Else
MsgBox "not found"
End If

I know this is a loop but unless you have hundreds of sheets it's unlikely
you'll notice any difference, if anything with a typical qty of sheets the
loop will probably be quicker. The main advantage though is the Trust Access
option is not required.

Regards,
Peter T

 

[Peter T]

When we select the

reference to the Extensibility library on her machine, everything works
fine.

I didn't fully absorb this bit. As I mentioned, the reference is not
directly relevant. Only thing I can think of is this scenario -

When a new sheet is added with the VBE closed (throughout session), the
codename does not exist, it's an empty string. If you had tried to trap that
when a new sheet was added you'd have got an empty string. However as soon
as you open the VBE the codename updates. Maybe it was opening the VBE to
add the ref that miraculously made things work. If this is not the reason I
have no idea, doesn't make sense.

Note the loop my other thread would similarly fail if you had been unable to
get the codename. With new sheets test codename is not an empty string. If
it is (an empty string) there are various ways to try and get the codename
updated.

Regards,
Peter T

 

[Barb Reinhardt]

I do have Trust Access to Visual Basic projects selected on my system. I
thought that setting was a project by project setting. Was I incorrect?

I'll have to check to see if the other users have that. Argh! Another
change!

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

Now wait a minute.

1) I have trust access on my system and I don't think the other user does
2) I don't have the reference selected and it works fine. When we selected
the reference on the other computer, it worked fine. WIthout the reference,
it didn't work.

Thanks for ideas of things to check.

Barb Reinhardt