Creating WB with user defined functions

Hi all,

Here is a problem I believe is tricky - wondering if there would be ideas in the pool of infinite VBA wisdom gathered in this forum :)

Here it comes. The purpose would be to, programatically through VBA in Excel, create a new workbook (that's easy enough) that contains a "model", that is, put some values in that new workbook, and in some cells programmatically put some formulas that use these values (that's still fairly easy). At that point, that would look something like this:

Sub CreateNewModel()

Dim WB As Workbook
Dim WS As Worksheet

Set WB = Workbooks.Add
Set WS = WB.Worksheets.Add

WS.Cells(1, 1).Value = 1
WS.Cells(2, 1).Value = 2
WS.Cells(3, 1).Value = "=A1+A2"

End Sub


Now this example is trivial, but the type of formulas I am interested in is more complex, and, as you imagine, generating the string for the formula can quickly become quite a pain. So ideally, I would like to use user defined functions, which result in a much more compact notation. In the silly example before, I would define a function "mySum" in a module, and I would like to say something like:

WS.Cells(3, 1).Value = "=mySum(A1,A2)"

The problem is that the function is defined in a module attached to the "source" workbook, so when I save the new workbook the function definition will be lost. So essentially, I am looking for a way to create a workbook, and attach programmatically some modules to it. Is this feasible? I had a quick look at some references at programming into the VBE, and this sounded pretty hardcore / ugly. Are there some easy or not-so-easy-but-still-human ways to do that?

Thanks in advance for any idea,

Have a look at
http://www.cpearson.com/excel/vbe.htm
http://www.erlandsendata.no/english/index.php?t=envbavbe

Chip Pearson's site (http://www.cpearson.com/excel/vbe.htm) explains it quite well, and makes it sound very doable. :)

Near the bottom of that page, there's a section titled "Copying Modules Between Projects" - sounds like that should do what you're looking for.Rick are you stalking me? This is the 2nd time... the question has been waiting here for half an hour, and we still manage to post almost-identical answers within 2 minutes of each other :)

Thanks to both of you! There is still a small problem, though. Using the method described there requires to

1. Set a reference to the library "Microsoft Visual Basic For Applications Extensibility 5.3"
2. Change the security settings / trusted source

I can do this easily on my own machine, but the same XL file that would work on my machine would not work on another person, unless that person would do the changes by hand. Not surprisingly, the macro recorder did not register any of these when I tried it - do you know if it is possible to automate these steps as well?

Yeah, this is not a great way to distribute.

#1 could be handled by having a Template workbook. Use Workbooks.Add(Template:="C:\..\..\MyTemplate.xlt") Actually the Template can be a .XLS as well, it doesn't matter. Just make sure that the Template Workbook has all the references that you need.

#2, changing your client's Security settings via VBA can't be done. The intent of those security settings is to prevent VBA Macro Viruses from running in Excel. If the program itself could change those settings so that it could run, well... that's not very secure!

But as long as you have your 'MyTemplate.xlt' with all your required references already included, why not just make sure that it also contains Modules with the UDF functions that you also wish to include? Would be a ton easier!

Just a thought...

-- Mike

Thanks Mike!

I am not that surprised about #2, for exactly the reasons you mention - that would just be looking for problems...
As for #1, I am intrigued and I will definitely look into it - Never in my life did I use templates, and I am not even sure what they are used for...

Cheers, and thanks to all (does not mean I don't want to hear other ideas!)