Can class and standard modules be created via VBA? So, could you have code in Workbook XYZ that would create a new Workbook, name it PDQ and create modules in the newly created workbook?

Thanks!

Yes ... but I would suggest that you just create a blank Excel "template" with the code you want, then copy that template... or .. create the code in an "Add-In" then have your spreadsheets reference that add-in for the code.

Brent,

Thank you for the reply and suggestions. If I still wanted to do it as I outlined in my original post, could you still help me out with a code suggestion?

Thanks!

Hello,

I second Brent's opinion, mostly you can use templates or general addins.

But, if you want to try to give it a go, add Microsoft Visual Basic For Applications Extensibility 5.3 to your refs and try some code from this site:

http://www.cpearson.com/excel/vbe.aspx

Martin

Martin,

Thanks for the 2 cents worth. Interesting stuff at the link you included. Don't know if i'll even attempt this, but if i do i'll probably go the route you and Brent recommended. How difficult is it to create and add in? never done it...

Jmaes

Hi James, in fact, creating an addin is very easy. Just make a new workbook, add all what you need including code (make sure that the code refers to ThisWorkbook if it refers to sheets in the addin or ActiveWorkbook if you want to refer to workbook you are currently working with) and save as xla.

Tools=>Addins=>Browse=>OK and you're done. Now the add-in will always be there when you open Excel and you can call its code and functions you have defined and use them in your worksheets. However, if you want the other users to use the functions as well, you need to distribute the addin.

Let me know if you need more help

Martin

Creating Modules on the fly with VBA used to be fun, until Excel/Office XP - it's no longer a viable App for distribution as Excel by default will not allow code to access VBA Projects by default. You can change this on your system, but it's not supported in the OM, so your code will fail on 98% of your audience's machine.

So, yes, XLA Add-In.

Martin, thanks for the help. I got the xla file created, but here's my problem: the code in the workbook used for the xla file included a class module, a Workbook_Open event (that is a class module right?) that runs code when the workbook opens. What i'm attempting to do is have a new workbook created on the user's desktop via vba (no problem there) and then the new workbook should use the Workbook_Open code from the xla file to do some business. i can't seem to get the new workbook to use the Workbook_Open code from the xla file. How do i do that?

Thanks for your help!

Hi Nate. I was intrigued by the possibilities of creating "on demand" modules and figured it could be done but didn't know how complicated it would be. I checked out the link Martin provided a few posts above and it just seemed like too much work, especially since an easier solution was available- the Add-In. The fact that the code will almost always fail is the final nail in the coffin. That said, I was tempted to give it a go just for the sake of doing it!

James

Hello,

not sure about your workflow. If you need to create the procedure in the target workbook, you'll need some code from the link I have posted.

But if it's a one off job, try thie following:

The Workbook_Open() event in the xla (yes, it's in a Thisworkbook class module) is declared as private and thus it is invisible to other modules.

In xla, do something like this:

Private Workbook_Open()
OpenCode
End Sub

In a normal module of the xla put the OpenCode procedure that would contain all the previous workbook_open code. Don't declare as private, of course.

Now,you can call the code from the new workbook:

Application.Run "AddInName.xla!OpenCode"

Martin