VBA Integrated Development Environment basic question

M

max

Hi

I'm reading "Excel 2000 power programming with vba" by Walkenbach, in
the 27th chapter he mentions that by adding a reference to Microsoft
Visual Basic for Applications Extensibility in a VBA Project a
programmer can have access to all the VB Editor's objects, properties
and methods.
Now I added a reference (tools,References in VBE) to the Microsoft
Visual basic for Applications Extensibility 5.3 for a new workbook and
then wrote this code :

Sub ListReferences()
Dim ref As Reference, msg As String
msg = ""
For Each ref In ActiveWorkbook.VBProject.References
msg = msg & ref.Name & vbCrLf
Next ref
MsgBox msg
End Sub

but when I try it I get an error (run time error 1004) as it can't
resolve ActiveWorkbook.VBProject.
Is the Microsoft Visual basic for Applications Extensibility 5.3 the
right reference to add?
I'm on Excel 2002.

Regards.

Massimo.
Bye
 
T

Tom Ogilvy

Max,
That is the correct library, but each workbook has its own reference list.
There is a possibility that when you added it, you added it to a different
workbook than the one where you have this code. The reference is added to
the active project, so select the workbook with this code in the Project
explorer, then do tools=>References and see if you have the reference to the
extensibility library. If not, then add it there.
 
C

Chip Pearson

Your code should work fine. However, if access to the VBProject
is not trusted, the code will fail. In Excel, go to the Tools
menu, choose Macros, then Security, then the Trusted Publishers
tab. There, ensure that "Trust Access To Visual Basic Project" is
checked.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
M

max

Max,
That is the correct library, but each workbook has its own reference list.
There is a possibility that when you added it, you added it to a different
workbook than the one where you have this code. The reference is added to
the active project, so select the workbook with this code in the Project
explorer, then do tools=>References and see if you have the reference to the
extensibility library. If not, then add it there.

Tom

first thanks for answering, I'm sure I added it to the right project
because it's the only open project and can also see it in its
references window (it was moved up within the loaded references) so
that should not be the problem.
But It doesn't work.

Massimo.
Bye
 
M

max

Your code should work fine. However, if access to the VBProject
is not trusted, the code will fail. In Excel, go to the Tools
menu, choose Macros, then Security, then the Trusted Publishers
tab. There, ensure that "Trust Access To Visual Basic Project" is
checked.

Chip

It worked nicely.

Thanks a lot and have a good day.

Massimo.

Bye
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top