function from another workbook

[Dan]

I have a workbook (a) that create another workbook(b).
I will need the new workbook (b) to use a function (in the excel sheet - not
vba) that is user-defined in the code of the original workbook (a).

I can do it if I add a reference in (b) to (a) - but
1) is there another way, simpler (with no need to add reference something
like =a!Functionfroma (x,y) ?
2) how can I do it programaticaly (adding a reference) when creating
workbook b?

Any idea.
Many thanks,
Dan

 

[Howard31]

Hi Dan,

Make another Workbook lets call it 'C' import the user defined function into
this workbook, then when creating workbooks from workbook A in code, use the
workbook 'C' as a template this way the new workbook will have the function
in it's own code module*

*Dim NewWb As Workbook

1. Set NewWb = Workbooks.Add(template:=ThisWorkbook.Path & "\C.xls")

2. NewWb.SaveAs ThisWorkbook.Path & "\B.xls"

If you're using Excel 2007 you need to change line 2. with line 3.

3. NewWb.SaveAs ThisWorkbook.Path & "\B.xlsm",xlOpenXMLWorkbookMacroEnabled

Hope this helps,