optimisation tool for objective function macro

  • Thread starter Thread starter wjm
  • Start date Start date
W

wjm

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
 
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
 
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
 
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
 
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.
 
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-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
 
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
 
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 :
 
Back
Top