return a value from a vba function in another xla

[Emily]

How do I get the value a function returns from another xla
I am trying to use the code
returnedValue = Excel.Application.Run "xlaname.xla!functionname",
functionparameter1, functionparameter2

I keep getting "expected end statement"

If the statement is on its own it runs but I dont get the value it returns,
that is
Excel.Application.Run "xlaname.xla!functionname", functionparameter1,
functionparameter2


I need the value that the function returns. How do I do this with out
making changes to the function.
Regards,
Emily

 

[JLGWhiz]

i have not tried it but, this looks right.

Excel.Application.Run "xlaname.xla!functionname"( functionparameter1,
functionparameter2)

 

[Dave Peterson]

This was saved from a post for a similar question. You'll have to change the
workbook name from personal.xls to the real name.

Dim pWkbk as workbook
set pwkbk = workbooks("Personal.xls")
application.run "'" & pwkb.name & "'!macronamehere", "parm1", "parm2"

or if you're returning a value from a function:

dim res as string 'or variant or long or ...
res = application.run("'" & pwkb.name & "'!macronamehere", "parm1", "parm2")

You could also create a reference to this personal.xls workbook and call it just
like it was built into excel.

Tools|references
(but give the personal.xls's project a nice unique name (not VBAProject).

ps.

If you're using a function living in personal.xls inside a cell:
=personal.xls!functionnamehere(a1,b1,c1)

or save the file as an addin (*.xla) and use it in the cell like it's built into
excel:
=functionnamehere(a1,b1,c1)

 

[Emily]

thanks. that did help. I needed the put brakets after the run to return the
value. I had tried putting brackets in but after the function name, which
did not work.
thanks also for the other tips which will be useful.
Emily