function from another workbook
 

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

function from another workbook
 

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,

--
A. Ch. Eirinberg


"Dan" wrote:

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