Create VBA Macros from VB Code

J

Jérémie Gent

Hi!

I'm generating Excel Files from a VB Application. Those Files are quite
complex and content many Worksheets with Data, Charts, Pivottables, etc.
Fortunately, the "Microsoft Excel 9.0 Object Library" which I'm using is
really well done and quite complete for my needs.
However, I didn't find the way to add VBA code to my Excel Files. Some
events have to be handled in the final generated files, and therefore I need
to associate texts (code) to worksheets and workbooks.
I was looking for collections like 'macros', 'modules', code, ... but
couldn't find any...

I don't know if it is actually possible to create code from code ....

Anybody could help me?
Thank you!

Jérémie
 
H

Howard Kaikow

Jérémie Gent said:
Hi!

I'm generating Excel Files from a VB Application. Those Files are quite
complex and content many Worksheets with Data, Charts, Pivottables, etc.
Fortunately, the "Microsoft Excel 9.0 Object Library" which I'm using is
really well done and quite complete for my needs.
However, I didn't find the way to add VBA code to my Excel Files. Some
events have to be handled in the final generated files, and therefore I need
to associate texts (code) to worksheets and workbooks.
I was looking for collections like 'macros', 'modules', code, ... but
couldn't find any...

I don't know if it is actually possible to create code from code ....

Anybody could help me?
Thank you!

Jérémie
Click to expand...


The best thing to do may be to convert the VB to an ActiveX DLL and just use
VBA stubs in the Excel files to call the procedures in the DLL.

This way the code will run faster, be secure from prying eyes and eliminate
the chance that some of the VB code cannot be ported to VBA.
 
A

aaron.kempf

Visual Basic For Applications Extensibility 5.3 allows some pretty neat
functionality in this regard.. at least from what i read about it the
other day.



-Aaron
 
J

Jérémie Gent

Thanks to all of you for your help!
I finally used the VBProject object of the Workbook Object, and its
VBComponents collection.
I now have text - containing the codes - files that I load to each
VBComponent .CodeModule.AddFromFile
This works really fine.

I firstly didn't find those objects because they don't appear in the VB
editor in the list of available properties / methods that comes when you
type the dot after the name of your object.

So thank you again very much for your help!

Jérémie
 
J

Jérémie Gent

Thank you! This page is exactly what I was looking for!
Indeed , I use files for each Module now. (cf my other response below)

Jérémie
 
Top