Excel Macro error in Add method of object _VBcomponents

[Cap Kirk]

I have a macro that adds a code module in EXCEL It works fine in EXCEL 97.
Now I am using it in EXCEL 2003 ant it fails (runtime 440) with the message
"error in Add method of object _VBcomponents"
The code is:

With Application.VBE.ActiveVBProject

' Create and use a new module
With .VBComponents.Add(vbext_ct_StdModule) <== This is the offending code
line

"Add" gives an error
' Change the module name
.Name = "ModuloDeMacrosDelIndice"
' Use the code module
With .CodeModule
' Add the contents of a file
.AddFromFile Archivo$

End With

Any help will be welcome

Cap Kirk

 

[Bernie Deitrick]

Cap'n,

Your code worked fine for me in Excel 2003.

HTH,
Bernie
MS Excel MVP

 

[Jurgen]

Did you check 'enable programmatic access to vba" somewhere in the macro
security settings (I'm on 2007, can't check exact location)?
Jurgen

 

[Cap Kirk]

Dear Jurgen:

In EXCEL 2003 I checked:

Tools => Options => Safety => Macro Safety => Safety Level (First Tab) :
medium
Tools => Options => Safety => Macro Safety => Trusted Sources (Second Tab)
=> Check "Trust Visual Basic Projects access" and also "Trust all
Complements and Templates installed"

Dear Bernie:

My code, which is exactly as published, in EXCEL 2003 with OS Win2K, does
not work

The runtime error 404 appears and the second line says the .Add method does
not work with object _VBcomponents

???? any clue on how to debug this? How con I track how the Add method is
working?

Thank You very much. Any help is welcome.

Cap Kirk

 

[Bernie Deitrick]

When I removed the reference to VBA extensibility, I got a different error
than what you reported - a constant (vbext_ct_StdModule) not defined. It
always pays to use Debug / Compile on your code to find errors like that.
Try setting your Tools / Options, General Tab "Compile on demand" and you
will get better error messages.

Bernie

 

[Cap Kirk]

I found the culprit.

The "MS VBA EXTENSIBILITY 5.3 " library was NOT referenced.
I went to the VBA editor (EXCEL=>Tools=>Macros=>VBA Editor) and there again:
Tools=>References and checked the library.

Now the macro is alive again. Thanks to everybody that tried to help me.

Cap Kirk