ExcelBanter - Using solver with function with multiple outputs

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Using solver with function with multiple outputs (https://www.excelbanter.com/excel-worksheet-functions/37567-using-solver-function-multiple-outputs.html)

Using solver with function with multiple outputs

Hello all, I am looking for help with the Excel Solver:

I have a VBA function that returns several outputs. I wish to use one
of these
outputs as the Solver target to minimize or maximize, and I wish to use
the other outputs as part of the constraints on the Solver.

For example, my function takes Size as an input, and returns
Temperature and Mass from the same function call. I wish to optimize
for maximum temperature by changing the size, but have a limit on the
mass.

However, the Solver target cell must contain a function, and functions
are not allowed to modify worksheet cells, which is what I must do if I
want the solver to see all the other outputs.

I can think of two approaches to this: 1) Hook into the
worksheet_calculate event somehow, or 2) Hook into the worksheet_change
event somehow, but I can't get either ways to work, any suggestions?

Thanks

Dave

Or should I put cell modification into a showRef method that gets
referenced from SolverSolve? How would i set that up?

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

Thanks for your reply. I'd like to try your first way, with the values
hardcoded in known cells. Can you be more specific in how the functions
would be layed out?

In my example, I have a function in the module:

public sub GetResults()
Dim size as double
size = Range( "$B$1" )

Dim results as ResultsObject
set results = ComplexCalculation( size )

Range( "$B$2" ) = results.Mass
Range( "$B$3" ) = results.Temperature
end sub

How would I set up the worksheet_calculate as you propose? I've tried
it several different ways but the Solver doesn't like it :(

I was of the impression you were using a Function procedure, not a Sub
procedure.

I've never been good at invoking Solver from within VBA; I gave up on
that approach rather quickly in what I was doing. The key, I think, in
your case, is to get what's going to be the target cell to contain a
formula rather than just a value. Here's an idea:

Public FUNCTION GetResults(size as double) as variant
Dim results as ResultsObject
results=ComplexCalculation(size)
Dim temp(2,1) as double
temp(1,1)=results.Mass
temp(2,1)=results.Temperature
GetResults=temp
End Function

Put size in B1
Select B2:B3 and array enter =GetResults(B1)

Then write the calculate subroutine that will invoke Solver. Like I
said, I'm not real good with that. The easiest way would probably be
to record a macro while you run Solver manually (targetcell=B3 to
Maximum by changing B1 with the constraint that B2 <= constraint on
mass). Then put the recorded code into a worksheet_calculate procedure
and adapt it to do exactly what you need.

As with most programming, there are going to be other possible
solutions. If you don't feel able to write your own optimization code,
then this should be one way to get what you want.

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