Spreadsheet 'call' from VBA function macro

I'm familiar with the usual application of VBA function macros - pass arguments from the spreadsheet to a function, the function then returning a value to the spreadsheet.

Is it possible to do this in reverse Can a function macro pass arguments to a spreadsheet, with the results of the spreadsheet calculation passed back to the function

The job I'm trying to avoid is reprogramming an existing spreadsheet as a VBA function, in an effort to make it addressible by another function. Instead I'd like to 'call' the spreadsheet directly from a function. Ideally, the spreadsheet could be accessed without having been opened previously, the calling function perhaps opening the spreadsheet automatically.

Thanks

Microsoft ISV Community Center Forums3

A spreadsheet isn't a programming language with a sequence of operations, so you don't "call" it. What you do is put the appropriate inputs into the proper cells, then read the calculations from other cells. Instead of doing this manually, have the code enter the inputs and extract the outputs. I use this approach if I want to do a goal seek or solver calculation within a VBA procedure.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

There is a function in application layer called evaluate.

sub foo()

dim x as double, y as double

x = 2

dim s as string

s = "sqrt(" & x & ")"

y= evaluate(s)

end sub

This sub uses the worksheet function sqrt rather than the VBA function sqr. This can be useful if you have functions defined in the xl4 interface, however it uses a context switch thus can be slow

HTH

Phineas