Using solver with function with multiple outputs

[[email protected]]

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

[MrShorty]

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.

[[email protected]]

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

[[email protected]]

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 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.

[Tushar Mehta]

In article <[email protected]>,
[email protected] says...
>
> 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/ne...ction/vba.html

--
Regards,

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

In article <[email protected]>,
[email protected] says...
> 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
>
>

[[email protected]]

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