Impossible to access VBE and VBComponents???

[kuhni]

Hi All,

I've a very strange problem, but I can't sort it out so I hope that
somebody of you has the clue!

In a given workbook, in let's say "Module1", I want to get the name of
the "Module2" (just to make a very simple case). So I use the following
code:

Sub test()
Debug.Print Application.VBE.ActiveVBProject. _
VBComponents("Module2").Name
End Sub

When running this one-liner, I get an error 1004:
"1004: Method 'VBE' of object '_Application' failed"

I'm using Excel 2003 and I have the macro security set to low (not
recommended) as well as the reference "Microsoft Visual Basic for
Applications Extensibility 5.3" included.

Any ideas where the problem could be?

MANY THANKS FOR YOUR HELP!

Cheers
Kuhni

 

[Cindy M -WordMVP-]

Hi Kuhni,

You unfortunately don't mention the version of Offic involved, but in
2003 there's an additional checkbox in Tools/macro/Security, on the
"Trusted Publishers" tab that you need to activate. The one about
allowing access to the VB project.

I've a very strange problem, but I can't sort it out so I hope that
somebody of you has the clue!

In a given workbook, in let's say "Module1", I want to get the name of
the "Module2" (just to make a very simple case). So I use the following
code:

Sub test()
Debug.Print Application.VBE.ActiveVBProject. _
VBComponents("Module2").Name
End Sub

When running this one-liner, I get an error 1004:
"1004: Method 'VBE' of object '_Application' failed"

I'm using Excel 2003 and I have the macro security set to low (not
recommended) as well as the reference "Microsoft Visual Basic for
Applications Extensibility 5.3" included.

Any ideas where the problem could be?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[kuhni]

Hi Cindy,

Many thanks for your answer!

Funny how long I was trying to fix that bug although I had already open
the right menu ;-)

But by the way: is this the standard setting in Excel that you don't
allow to access VB code? And if so, is it possible to change this
setting using a macro ;-) Probably not, I would guess, but the thing is
that I would like to avoid the users to manually change this setting...

Many thanks again,
Kuhni

 

[Tushar Mehta]

But by the way: is this the standard setting in Excel that you don't
allow to access VB code? And if so, is it possible to change this
setting using a macro ;-) Probably not, I would guess, but the thing is
that I would like to avoid the users to manually change this setting...

Many thanks again,
Kuhni

It's a security measure. If you could change it through code, so could
anyone else. It would be like having a lock that any key opens!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions