Using Solver to Minimise the Result of Another Iterative Routine

[Graeme Dennes]

I've written an iterative Trapezoidal integrator in VBA for integrating an
analytic function f(x), where f(x) is defined in a cell, for example, B1:
=3*A1^3 + 2*A1^2 + A2^3 - A3, where the integrating variable x is in cell A1,
and cells A2 and A3 contain parameters. In this example, the cells B1, A1,
the cells with the lower and upper limits, and the cell to receive the
result, are passed as arguments.

All this works fine.

Now, this is the part I 'm wanting to do, but cannot achieve.

After the integrator produces its result, I'm wanting to call the Solver
tool to minimise the integral result (target cell), adjust the parameters in
A2 and A3 (changing cells), and then for the integrator to be run again,
producing its next result. This process is to be repeated, until such time as
the parameters have been so adjusted by the Solver, and the integral result
has reached its minimum value.

ie,

1. Set up initial values.
2. The integrator determines its result.
3. Solver reads the result, then adjusts the two parameters.
4. Repeat steps 2 and 3, until the Solver has minimised the integral via the
two changed parameters.

I am unable to determine how to combine the iterative integrator with the
Solver in the above manner, so they alternately operate until such time that
the integral has been minimised.

Any guidance greatly appreciated.

 

[Héctor Miguel]

hi, Graeme !

it's not clear (or viewable) "where" (in the code) could it be a integration with solver tool

however (and AFAIK) once solver "solves" an equation...
there is no way that solver seek for another (possible) solution
unless you reset the solver arguments and you will get (probably) the same solution as before

(perhaps) if you post the code (and a sample data) -?-

hth,
hector.

__ OP __

 

[Graeme Dennes]

Hi Hector,

On thinking this through further:

What I'm needing in principle is to have the call to the integrator and the
call to the Solver in a loop, to allow them to be repeated in sequence as
necessary. The loop exit criteria will take place after the Solver call.

This would be a brief outline of the process, and I seek your advice on
this: After the integrator call is made, and the integral result provided,
Solver is called. It looks at the integral result (target cell), does some
thinking, then makes changes to the changing cells. Then at THAT very moment,
I need Solver to quit, ie release itself back to the code loop. (I don't want
Solver to attempt to run its own iteration process to completion, or at least
attempt to do so, because it won't succeed, as it cannot invoke the
integration routine after making changes to the change cells).

At the time Solver releases itself back to the code loop, Solver would also
need to provide a status value, indicating whether the last presented target
cell value is the lowest it can find, or not, allowing the code to either
reloop or terminate.

This (in theory) should do the job, I think.

Q. Can the Solver be used in this manner, ie, allowing its internal
processes to be interacted with in this way for program control purposes?

Thanks for your help.

Graeme

PS Standing right back, it would seem that my requirement would not be very
different in principle to any other situation where an iterative routine in
VBA code is needed to calculate some mathematical function for whatever
purpose, and then to try to use the Solver in conjunction with it for finding
some minimum or maximum value of it. Still, I'm not feeling very confident
Hector. Perhaps Solver's specifications don't allow for such interaction.
Hoping you can identify a path through this.

--------------------------

 

[Héctor Miguel]

hi, Graeme !

i'm not so sure the solver tool is what you look for to interact with your routines (but perhaps)...
you might need some (named ?) TempCells for solver to put its results (i.e. SolvMin & SolvMax)
and do some check if solver finds something different versus your integrator procedure (and if so)...
for doing a (re)process changing your variables (target/changind cells) to the mim/max solver could found
previous error free/checking routines (just in case ?)

hth,
hector.

__ OP __

On thinking this through further:

What I'm needing in principle is to have the call to the integrator and the call to the Solver in a loop
to allow them to be repeated in sequence as necessary. The loop exit criteria will take place after the Solver call.

This would be a brief outline of the process, and I seek your advice on this: After the integrator call is made
and the integral result provided, Solver is called. It looks at the integral result (target cell), does some thinking
then makes changes to the changing cells. Then at THAT very moment, I need Solver to quit
ie release itself back to the code loop. (I don't want Solver to attempt to run its own iteration process to completion
or at least attempt to do so, because it won't succeed, as it cannot invoke the integration routine after making changes to the change cells).

At the time Solver releases itself back to the code loop, Solver would also need to provide a status value
indicating whether the last presented target cell value is the lowest it can find, or not, allowing the code to either reloop or terminate.

This (in theory) should do the job, I think.

Q. Can the Solver be used in this manner, ie, allowing its internal processes to be interacted with in this way for program control purposes?

Thanks for your help.

Graeme

PS Standing right back, it would seem that my requirement would not be very different in principle to any other situation
where an iterative routine in VBA code is needed to calculate some mathematical function for whatever purpose
and then to try to use the Solver in conjunction with it for finding some minimum or maximum value of it.
Still, I'm not feeling very confident... Perhaps Solver's specifications don't allow for such interaction.
Hoping you can identify a path through this.

__ previous posts __

 

[Graeme Dennes]

Hi Hector,

It appears that Solver may not be the tool of choice. (If only it had the
option to allow me to intercede in its process, as I described. Oh well...).

I note that the publication "Numerical Recipes" 3rd edn ( www.nr.com )
describes several minimisation (and maximisation) algorithms for one or more
independent variables in Chapter 10. One of these could act as the starting
point, making the necessary changes needed, being that it is not the analytic
function I need minimised, but the integral of the function.

This way, I am creating my own "Solver" for this task. I'm sure it will be
an interesting exercise.

Thank you for your good advice.

Best regards.

----------------------------

 

[Héctor Miguel]

hi, Graeme !

... I am creating my own "Solver" for this task.
I'm sure it will be an interesting exercise.

you can count on it (for sure) )

regards,
hector.

 

[Graeme Dennes]

Apologies to all for this long post, especially to Dana.

Hi Dana.

I'm going to need to spend some time going over your response, so I fully
understand your suggestions. Thank you for your advice. I have a background
in programming and mathematics, but little experience with Excel application
development.

Re posting a simple equation. In my opening message, I basically describe
the process I'm trying to achieve. My goal is to minimise the integral of a
given analytic function which has (say) two independent variables
(parameters). I gave an example in the post. The idea is that a numerical
integrator sub is called to calculate the integral. Following that, I then
wish to call Solver, so it does some thinking, and then makes changes to the
changing cells (parameters). Solver is then to exit, returning control back
to the loop. An exit test then occurs. This way, the call to the integrator
and Solver are to be repeated in a code loop, until the integral is
minimised. However, to achieve this, I need Solver to do one iteration only,
ie, after making changes to the changing cells, I want Solver to exit, and
the exit test done, after which the integrator is called again, with its
(new) result dependent on the changed parameter values. Then Solver does one
iteration again and exits, etc, etc.

When Solver runs, if it thinks the integral has been minimised, it exits
with a particular status value, while if it thinks the integral hasn't (yet)
been minimised, it exits with a different status value. Then the exit test
(placed after the Solver call) reads the Solver status value, and either
loops back to the integrator/Solver pair again, or else exits the loop, and
the code terminates with the minimised integral value and the final values of
the two parameters in specified cells.

Overall, my problem here is to get the Solver to do one iteration only, then
exit. ie, look at the target cell (integral result), think, then change the
changing cells (independent variables), and then exit - just one single pass
only.

So, the sequence is: integrate, adjust parameters, integrate, adjust
parameters, etc, etc, until the Solver indicates the integral has reached its
minimum value. It is the final values of the two parameters which I seek.

Another issue which has now come to mind is that for the Solver (or any
similar purpose routine) to "know" when a minimum has been reached, it will
(?) need to have in its "hands" the integral results and parameter values
associated with the last several passes, so it can do its own algorithmic
things and tests to enable it to decide if the minimum has been found. In the
manner I am proposing above, no such previous results are stored, nor is
Solver aware of them, as each time the call to Solver is made in the loop, it
is starting afresh.... It holds no prior history, as I'm giving it a single
pass only before it exits.

Perhaps the last para sums up the greatest problem I face - I want the
Solver to tell me when the minimum has been found, but I'm expecting it to do
so on a single pass.... Still, I could store the last several integral
results and parameter values, but then I need to tell Solver of their
existence, as well as what I want Solver to do with them. Whew!!!

The only (?) other alternative is that, ok, let Solver iterate in its usual
manner, but that demands that Solver has the ability to repeatedly call the
integrator sub after it makes changes to the changing cells. I am not aware
that Solver has this ability. I understand that it can only read the target
cell and change the changing cells - it cannot call a sub and process the
result, all internally within itself...

My initial thinking is that I cannot do this as I have described it.
Therefore, I need to now consider your previois post in much detail. Perhaps
the answer is in there.

Wouldn't it be nice if the Solver tool functionality was to be revised to
address the core issues discussed here? The Subject of this thread could
indeed be achieved, making the Solver tool applicable to a brave new range of
mathematical problems.

Dana, if you have the patience to read the above, I'd be most grateful for
your thoughts.

Best regards.

Graeme

-----------------------------------

 

[Graeme Dennes]

Hi Dana.

Thanks for your advice.

One such expression I have is as follows (actually a distortion equation):

f(x) = (( ASIN(( x^2 + 500*V1 - V1^2)/(500*x)) - V2) / x )^2

where V1 and V2 are the two independent variables. The function looks
something like the letter W sitting on the x-axis.

When V1 and/or V2 are varied, one, or two, of the three function peaks will
either increase or decrease wrt to the other peak(s), thus changing the
integral value. However, there is a unique pair of values for V1 and V2 which
will give the minimum value for the integral of the above expression. I'm
trying to find those values.

With the values of V1 = 16, V2 = 0.38 (radians), lower limit of x = 60, and
upper limit = 145, the numerical integrator gives a result of approx. 1.95
E-6.

The final values of V1 and V2 to minimise the integral will be within +- 5%
of the above values. (I calculate the integral to around 14 or 15 significant
digits, but I'm only showing three in the result above).

As I summarised in my last message, it appears there are some clear reasons
for why I cannot achieve what I'm seeking, in the manner I'm proposing. Short
of the Solver being modified to enable the exposure and control of its
internal operations, including its ability to call an external sub (for
evaluation of the integral), I think I'm out of luck with the Solver. It's
specifications simply do not provide for such (obvious?) functionality.

Graeme

---------------------------

 

[Graeme]

Hi Dana, you've done some great work here, and provide very useful insight.

It appears then that Solver cannot help me anyway in this situation,
regardless of the previously discussed issues. In that case, other tools will
be needed...

Your maths program seems to be very useful for such calculations, in so far
as its precision is concerned, as well as its abilities to perform numerical
integration (various methods) and function minimisation tasks. I'd be
interested to know if your program is based on a standard program such as
Mathematica?

1. In your experience, could you advise me which of the standard (or other)
available programs are more appropriate for this type of work?

2. Are there such programs, similar to the Solver optimiser and your maths
program, which can be called from within Excel via VBA, and which can make
use of the Excel worksheet interface and the Excel VBA environment?

In the meantime, I need to review the situation overall, based on your
advice to date.

Thank you for taking the time and effort to assist me here. I'm most
grateful to you.

Graeme

----------------------------------------

 

[Graeme]

Hi Dana,
Thank you for your excellent work, and your preparedness to work through
this issue with me.
Unfortunately, I don't understand how Solver has been able to minimise the
integral, as your results show.
1. I did not know that Solver can (automatically) make repeated calls to the
integration code, and wait for it to complete its loop before checking the
new value in the target cell. I thought this was not possible. I thought
Solver would only work with cell formulas.
2. Perhaps it can work this way where the code is written as a Function
instead of a Sub...?
3. Perhaps it can work this way where the Solver is called via its dialog
box, and not via the VBA code?

Being well and truly far from an Excel expert, I'm obviously missing
something very basic, in that I'm not properly understanding the thrust of
your message. There's a small number of combinations of the points above
which I don't have a strong grasp on, as you can see.

I'd be most grateful for your clarifying advice on how you managed to obtain
the Solver results.

Graeme

----------------------------------

 

[Graeme]

Hi Dana,
I managed to reproduce your example code and obtain results identical to
your own, so I now have a working test bed to review for understanding and
enhancement.

Your code examples have worked splendidly, and have given me much insight
into using the Solver. I am now able to perform numerical integration on the
function f(x), and to use Solver to minimise the integral by making changes
to its two parameter values. Wonderful work! This has enabled me to achieve
my initial goal in this thread. My next step will be to attempt to transplant
my iterative integrator code.

Now if only there was a way to define and use f(x) in a cell formula instead
of coding it in VBA... Oh well.

Thank you for the generousity of your time and knowledge, and patience to
assist a beginner. I salute you.

Graeme

PS I understand that Frontline's Premium Solver has the option to call VBA
code after making changes to the variable cells, then waiting on the
completion of the VBA call before it checks the target cell's value... I
think that would allow me to do this task via Sub calls, have f(x) defined in
a cell, and have values passed from VBA to cells for use by f(x), such as the
x value supplied by the integrator code. All outputs would be written to
specified cells. This would still need to have the cells hard coded in VBA,
as there's no way to specify them otherwise - I think. Now if a UDF could
write to cells, all input and output cells could be defined totally in the
UDF arguments. Life would be much easier.

PPS Further to the above. As you are a very experienced programmer in Excel
and VBA (and perhaps other areas), I'd appreciate your thoughts on this broad
issue: Is there some fundamental reason/rule for why UDFs are not allowed to,
say, write to cells? I don't mean because that is part of their
specification, but why such a specification exists in the first place? Why
does it HAVE to be this way? What/who would prevent that specification from
changing/evolving in the future, and why should it be prevented from doing
so? Taking it further, why shouldn't we have the option to use a third (yet
to be developed) structure, which has perhaps the cumulative options and
capabilities of Subs and UDFs? One such (new) structure could then do all the
things we now do with Subs and UDFs. It would simply be a matter of setting a
set of options to control or achieve the desired behaviour and outcomes.

-------------------------------

Hi.

1. I did not know that Solver can (automatically) make repeated calls to the
integration code
I thought Solver would only work with cell formulas.

Yes, Solver can work this way by using custom functions.
(This is why Solver has to figure out the derivative via small samples
around the area in question)

2. Perhaps it can work this way where the code is written as a Function
instead of a Sub...?

A function returns a value, so it must be a Function.

3. Perhaps it can work this way where the Solver is called via its dialog
box, and not via the VBA code?

It can work either way.

Put 16 in A1, and 0.38 in A2.
In C1, put the function =Fx(A1,A2).
You should get the result of 1.95 E-6

With Solver, Minimize this Target value by changing the cells in A1:A2.
For this example, I didn't add any constraints, but it's always a good
idea.
Change some of the options as mentioned, and after a while, I got a
solution.

In VBA Code, see if this works for you.
You need to set a vba library reference to Solver.

Sub Demo()
[A1] = 16 'Changing Cells
[A2] = 0.38
[C1].Formula = "=Fx(A1,A2)" '<-Target Cell

'Minimize C1...
SolverOk SetCell:="C1", MaxMinVal:=2, ByChange:="A1:A2"

SolverOptions _
MaxTime:=500, _
Iterations:=100, _
Precision:=0.000000000001, _
AssumeLinear:=False, _
StepThru:=False, _
Estimates:=2, _
Derivatives:=1, _
SearchOption:=1, _
IntTolerance:=1, _
Scaling:=True, _
Convergence:=0.000000000001, _
AssumeNonNeg:=False

SolverSolve True
End Sub


'This is a very simple integration of the function from 60 to 145.

Function Fx(v1, v2)
Const k As Double = 0.01
Dim x, t
With WorksheetFunction
For x = 60 To 145 Step k
t = t + ((.Asin((x ^ 2 + 500 * v1 - v1 ^ 2) / (500 * x)) - v2) /
x) ^ 2
Next x
End With
Fx = t * k
End Function


Again, not the greatest, but something that could work for now.
--
HTH :>)
Dana DeLouis

Hi Dana,
Thank you for your excellent work, and your preparedness to work through
this issue with me.
Unfortunately, I don't understand how Solver has been able to minimise the
integral, as your results show.
1. I did not know that Solver can (automatically) make repeated calls to the
integration code, and wait for it to complete its loop before checking the
new value in the target cell. I thought this was not possible. I thought
Solver would only work with cell formulas.
2. Perhaps it can work this way where the code is written as a Function
instead of a Sub...?
3. Perhaps it can work this way where the Solver is called via its dialog
box, and not via the VBA code?

Being well and truly far from an Excel expert, I'm obviously missing
something very basic, in that I'm not properly understanding the thrust of
your message. There's a small number of combinations of the points above
which I don't have a strong grasp on, as you can see.

I'd be most grateful for your clarifying advice on how you managed to obtain
the Solver results.

Graeme

<snip>

 

[Graeme]

Thank you Dana. I now need to look at your example in detail.

Graeme

-------------------------

Hi. Glad it helped. :>)

Now if only there was a way to define and use f(x) in a cell formula instead
of coding it in VBA... Oh well.

If I understand the question, I don't think it can be done like that.
However, here's a poor-man's version of that idea.
This is not the greatest idea, but can sometimes help in certain
situations.
The problem you have is that it's an integration. :>~
Step1 just loads the formula for you onto a worksheet.
Step2 grabs that string and tries to evaluate the function.
Note however, that this method is rather slow.
Again, not the best way, but something to add to your library of routines.

Sub Step1()
[C1].Formula = "=((Asin((x ^ 2 + 500 * y - y ^ 2) / (500 * x)) - z) / x) ^
2"
End Sub


Sub Step2()
Dim f, x, t
Const k As Double = 0.01

'Load Start Values
ActiveWorkbook.Names.Add "y", 16
ActiveWorkbook.Names.Add "z", 0.38

'Get Function as a string
f = [C1].Formula

For x = 60 To 145 Step k
ActiveWorkbook.Names.Add "x", x
t = t + Evaluate(f)
Next x
Debug.Print t * k
End Sub

 

[Diederik Aerts]

I just read your exchange of posts, and in fact I am struggling with a very similar problem. I have a macro to calculate an integral numerically, and would like to use solver to make the value of the integral equal to a given value by varying the domain of integration. Hence, I have the impression that the solution of Greame's problem might entail a solution of mine. I found your posts by just looking on the web with search on 'integral', 'solver', 'Excel'. There is however something weird, namely I can read Greame's posts, but not Dana's. Dana's texts seems to be encrypted in some way, and only appear as a set of symbols on my browser. First I thought that perhaps I needed to become a member of the forum to be able to read Dana's text, but now that I have subscribed, it still is unreadable for me. Can you help me out in some way?

Diederik

 

[Dana DeLouis]

Hi. I'm now trying out a different News Reader than Windows Mail.
Feel free to send me your data/equation.
I'd be happy to look at it.

- -
Dana DeLouis

 

[Diederik Aerts]

Hi Dana,

I meanwhile have found that the exchanges you made with Greame appear in
different fora. The one I could not read was the egghead, but now I answer
you on the microsoft forum (where I can read the posts you made to Greame).
Hence, the problem I am trying to solve is very similar. I need to 'fix the
value of an integral over a fixed function by by making vary the domain of
integration' and was attempting to do this with Solver. The problem is that
the function is a Gaussian, more specifically a Gaussian in two dimensions,
namely cEXP(-(ax^2+by^2)), where a, b and c are given constans, and x and y
the variables of the function. Since it is a Gaussian, there does not exists
an indefinite integral of it, and hence I need to make a numerical
integration. The domain I need to integrate over is a square with fixed
surface of 1/100 cm^2. Hence, what I had in mind is to parametrize such a
square by its center, hence writing it as [X-0.05,X+0.05] times
[Y-0.05,Y+0.05], and then taking X and Y as the parameters to vary by Solver
to fix the integral to a given value. I found a code for VBA that does an
integration of a function of two variables at
http://digilander.libero.it/foxes/integr/double_integrals.htm

My question is now. Would it be posisble to use the code in
http://digilander.libero.it/foxes/integr/double_integrals.htm and work out a
code for a "user defined function' for Excel, hence in my case a function of
the variables X, Y, the coordinates of the center of the square of
integration for the fixed function cEXP(-(ax^2+by^2)).

I must admit that although I used Solver a lot, I am not capable of writing
VBA codes. I am theoretical physicist, hence the math aspects are not
problematic for me, but I know only little of programming.

Anyhow thanks beforehand if you would be willing to ponder my problem Dana,

Diederik