optimisation tool for objective function macro

Does anyone know of a linear optimisation code that can optimise an objective function written in the Excel(97) VB Macro environment? I will consider any commercial add-in solutions or source code options that you may know of.

The optimisation problem consists of 3 variables and 1 response (relatively basic). The Excel (Frontline Systems) solver doesn't work, as far as I am aware, because it can't call a Macro to calculate the response and unfortunately the optimisation problem can not be reformulated within the worksheet environment as it needs to read data from an independent program.

Any help would be much appreciated.

Many thanks,
WJM

If you add a reference to Solver you can use its functions - I don't know
how they work but I'm sure you can call them in your code.

Jeff

"wjm" <[email protected]> wrote in message
news:[email protected]...
>
> Does anyone know of a linear optimisation code that can optimise an
> objective function written in the Excel(97) VB Macro environment? I
> will consider any commercial add-in solutions or source code options
> that you may know of.
>
> The optimisation problem consists of 3 variables and 1 response
> (relatively basic). The Excel (Frontline Systems) solver doesn't work,
> as far as I am aware, because it can't call a Macro to calculate the
> response and unfortunately the optimisation problem can not be
> reformulated within the worksheet environment as it needs to read data
> from an independent program.
>
> Any help would be much appreciated.
>
> Many thanks,
> WJM
>
>
> --
> wjm
> ------------------------------------------------------------------------
> wjm's Profile:
> http://www.excelforum.com/member.php...o&userid=35136
> View this thread: http://www.excelforum.com/showthread...hreadid=548962
>

Thanks for the suggestion. Unfortunately, although you can use solver functions within a macro the solver still requires that the objective function be formulated within a worksheet. My objective function is calculated by a macro, which the solver can't deal with.

Will

You are right, Solver forces you to use cell references to declare
variables. But your macro probably generates the objective function
coefficients for the variables right?

If your problem is linear, modify your macro to write out each
coefficient to a range. Name the adjacent range (your variable cells)
as SolnVars or something and set a target cell as the SUMPRODUCT of the
two. If the problem is non-linear, you just have to include the
intermediate step of calculating the functional values rather than
SUMPRODUCT.

SteveM

wjm wrote:
> Thanks for the suggestion. Unfortunately, although you can use solver
> functions within a macro the solver still requires that the objective
> function be formulated within a worksheet. My objective function is
> calculated by a macro, which the solver can't deal with.
>
> Will
>
>
> --
> wjm
> ------------------------------------------------------------------------
> wjm's Profile: http://www.excelforum.com/member.php...o&userid=35136
> View this thread: http://www.excelforum.com/showthread...hreadid=548962

SteveM,

I'm not sure I fully understand your suggestion. The solver must incrementally update the unknown variables (which are read into the macro) and then repeatedly evaluate the objective function. If the objective function is calculated in a macro, how does the solver know to call the macro to update the objective function for each iteration in the solution?

Regards,

Will

I'm confused too. Variables are not read in. They are merely cell
references whose values are unknown until the model is solved. Do you
mean the model coefficients that are read in?

If so then you write a macro to read in the coefficients and if your
Solver formulation is static you augment that with the Run Solver
command. like this:

Sub OptimizeModel()
Dim retVal as Integer
Call GetData 'Read in your data to the worksheet
retVal = SolverSolve(True) 'Run the Solver model that has been
preloaded.
Call ProcessOutputs 'Process the optimization results
End Sub

If you want to excute the model many times with the same formulation
but diffirent coefficient values, you merely update the coefficient
cells in your VB code and loop through the Solver call multiple times.
You need the ProcessOutputs routine to capture and save results after
each iteration so that they are not overwritten.

If your formulation changes along with your data, you have code up the
entire Solver formulation in VB and call that each time through the
loop. Easiest way to do that is to reset Solver, turn Macro Record on
and record your formulation as you define it. Then go to that macro
and modify the code as needed.

SteveM.

wjm wrote:
> SteveM,
>
> I'm not sure I fully understand your suggestion. The solver must
> incrementally update the unknown variables (which are read into the
> macro) and then repeatedly evaluate the objective function. If the
> objective function is calculated in a macro, how does the solver know
> to call the macro to update the objective function for each iteration
> in the solution?
>
> Regards,
>
> Will
>
>
> --
> wjm
> ------------------------------------------------------------------------
> wjm's Profile: http://www.excelforum.com/member.php...o&userid=35136
> View this thread: http://www.excelforum.com/showthread...hreadid=548962

SteveM,

I don't think I have explained my problem sufficiently well.

Solver requires a target cell (which has the value of the objective function - normally calculated by a formula within the cell) and changing cells (which are the variables to be optimised). The only way I can define my objective function is through a macro. Therefore the value in the target cell must be provided by my Macro and not a formula in the cell. To provide the value in the target cell the macro reads the values of the changing cells (optimisation variables) and runs the macro.

Solver requires the target cell to contain a formula, which is probably the first indication that I can't use a macro instead of a formula in the target cell. Can Solver or an alternative code or add-in work with an objective function calculated by a macro and not by a fromula in the target cell?

Hope this make sense?

Regards,

Will

Could you reorganize your macro so that it can be used as a user-defined function in Excell? I've used this approach for several different problems (mostly curve fitting type problems) and it has worked well for me. This way you can still use VBA to calculate the value of the function. Because the value of the function is then returned to a worksheet cell, and the input parameters are also in worksheet cells, then you can use solver to optimize the input parameters.

I seem to recall that the developers of the solver utility (www.solver.com) sell extended versions that, if I remember correctly, work with several different programming languages and with extended functionality. I've never used them myself, but it might be worth looking into.

wjm,

The macro won't work because the target cell is a function of the
decision variables which are cell references. E.g., if your objective
function is to min cost then then the target cell is the sumproduct of
the decision variables and the cost coefficients. (But you know this.)

Can you tell what about your objective function prevents you from
formulating it on a worksheet?

SteveM



wjm wrote:
> SteveM,
>
> I don't think I have explained my problem sufficiently well.
>
> Solver requires a target cell (which has the value of the objective
> function - normally calculated by a formula within the cell) and
> changing cells (which are the variables to be optimised). The only way
> I can define my objective function is through a macro. Therefore the
> value in the target cell must be provided by my Macro and not a formula
> in the cell. To provide the value in the target cell the macro reads the
> values of the changing cells (optimisation variables) and runs the
> macro.
>
> Solver requires the target cell to contain a formula, which is probably
> the first indication that I can't use a macro instead of a formula in
> the target cell. Can Solver or an alternative code or add-in work with
> an objective function calculated by a macro and not by a fromula in the
> target cell?
>
> Hope this make sense?
>
> Regards,
>
> Will
>
>
> --
> wjm
> ------------------------------------------------------------------------
> wjm's Profile: http://www.excelforum.com/member.php...o&userid=35136
> View this thread: http://www.excelforum.com/showthread...hreadid=548962

MrShorty,

Thank you very much. A great solution to my problem! I didn't know that macros could be made into user defined functions. I'm sure I will be using them again for other tasks.


SteveM,

Thank you for your interest in my question. I appreciate the time you have spent understanding the problem.

To answer your last question, "[what] prevents you from formulating it on a worksheet", I need the use of 3-D look-up tables and to be able to interpolate within the tables to obtain the coefficients I need. I am also analysing around 5000 points (each requiring 2 look-up values, with three variables to be optimised and 30 or so equations to be solved to obtain the target cell value) so a macro seems the most effective means to achieve this.


Jeff,

Thanks for the first response - keeping the thread alive.




wjm