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

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" wrote in message
...

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 solve
functions within a macro the solver still requires that the objectiv
function be formulated within a worksheet. My objective function i
calculated by a macro, which the solver can't deal with.

Wil

--
wj
-----------------------------------------------------------------------
wjm's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54896

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 mus
incrementally update the unknown variables (which are read into th
macro) and then repeatedly evaluate the objective function. If th
objective function is calculated in a macro, how does the solver kno
to call the macro to update the objective function for each iteratio
in the solution?

Regards,

Wil

--
wj
-----------------------------------------------------------------------
wjm's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54896

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


--
wjm
------------------------------------------------------------------------
wjm's Profile: http://www.excelforum.com/member.php...o&userid=35136
View this thread: http://www.excelforum.com/showthread...hreadid=548962

Could you reorganize your macro so that it can be used as a user-define
function in Excell? I've used this approach for several differen
problems (mostly curve fitting type problems) and it has worked wel
for me. This way you can still use VBA to calculate the value of th
function. Because the value of the function is then returned to
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 utilit
(www.solver.com) sell extended versions that, if I remember correctly
work with several different programming languages and with extende
functionality. I've never used them myself, but it might be wort
looking into

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=54896

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 tha
macros could be made into user defined functions. I'm sure I will b
using them again for other tasks.


SteveM,

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

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


Jeff,

Thanks for the first response - keeping the thread alive.




wjm :

--
wj
-----------------------------------------------------------------------
wjm's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54896