I've come across this problem before, too. For the functions I was
writing, I chose to abandon Excel's Solver and write my own simple
Newton-Raphson algorithm to solve the scenario I had.

Another approach that I considered was to have the UDF return it's
values. Then, in a separate Sub procedure (maybe a workbook_calculate
procedure like you suggest), invoke Solver to optimize the function's
output. It would look something like:

Function UDF1(size)
code to return mass and temp
end function

sub worksheet_calculate()
invoke solver
end sub

advantage to this approach: You only need to know the relationship
between mass, temp, and size. You don't have to come up with the
optimization algorithm.

disadvantage: In order to work, you have to explicitly tell the
calculate procedure which cells contain the function and the "size"
value. Once the spreadsheet is setup (and you never have to move the
solver parameters), this isn't necessarily a problem. But if the
calculate procedure needs to be more flexible (UDF1 and size are in
different cells all the time), then you need to find ways to get the
calculate procedure to find the instance(s) of UDF1 and "size" before
it goes into the Solver routine.

For the UDF's I've written, I found it easier to write my own
optimization algorithm (because it wasn't a very complicated model)
than to teach the calculate procedure how to find the instances of
UDF1.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=390924