VBA Integrated Development Environment basic question
 

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

VBA Integrated Development Environment basic question
 

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.

--
Regards,
Tom Ogilvy

"max" wrote in message
...
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

VBA Integrated Development Environment basic question
 

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





"max" wrote in message
...
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

VBA Integrated Development Environment basic question
 

On Mon, 26 Jul 2004 10:01:55 -0400, "Tom Ogilvy"
wrote:

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

VBA Integrated Development Environment basic question
 

On Mon, 26 Jul 2004 09:06:40 -0500, "Chip Pearson"
wrote:

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