Using solver with function with multiple outputs

[dave.rafkind]

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

 

[dave.rafkind]

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

 

[MrShorty]

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

Another approach that I considered was to have the UDF return it'
values. Then, in a separate Sub procedure (maybe a workbook_calculat
procedure like you suggest), invoke Solver to optimize the function'
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 relationshi
between mass, temp, and size. You don't have to come up with th
optimization algorithm.

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

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

 

[dave.rafkind]

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

 

[MrShorty]

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

I've never been good at invoking Solver from within VBA; I gave up o
that approach rather quickly in what I was doing. The key, I think, i
your case, is to get what's going to be the target cell to contain
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
said, I'm not real good with that. The easiest way would probably b
to record a macro while you run Solver manually (targetcell=B3 t
Maximum by changing B1 with the constraint that B2 <= constraint o
mass). Then put the recorded code into a worksheet_calculate procedur
and adapt it to do exactly what you need.

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

 

[dave.rafkind]

Thanks, using a returned array is exactly the solution I was looking
for.

I also talked to a Frontline customer help person about this, he
recommended hooking into the Worksheet_Change event like so:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False

' modify sheet values as necessary
Range("c1").Value = Range("b1") + Range("b2")
Application.EnableEvents = True
End Sub


But I have not tried this method

 

[Tushar Mehta]

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.

Or, you can write a function that returns multiple results when used as
an array formula. Very much how LINEST works. For an example of how
to create an array formula UDF see
Selecting a random subset without repeating -- using a user defined
function (UDF)
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/vba.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions