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

Cap'n,

Your code worked fine for me in Excel 2003.

HTH,
Bernie
MS Excel MVP


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

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

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

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

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