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.

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

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" wrote:

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

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 __
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) -?-

__ OP __
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 2 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.

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" wrote:

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 __
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) -?-

__ OP __
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 2 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.

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.