Using Solver With Another Iterative Routine

I've written an iterative numerical integration Sub which works fine. Several arguments are passed to it (cell references).

I now wish to incorporate Solver in the code process so that it reads the integration result, changes the values of two of the argument cells, after which the integration routine runs again, Solver changes the cells again, etc, until the integral is minimised.

Overall:

1. Initial values are set up as the arguments.

2. The integration routine then iteratively calculates the integral, based on the passed arguments.

3. Solver reads the integration result, then changes two of the argument cells.

4. Steps 2 and 3 repeat until Solver determines the integral result found at step 2 has been minimised.

(The final values of the changing cells (arguments) are then used for other things.)

I am unable to define the structure of the VBA code to achieve this.

I call the integration Sub, then call Solver, but Solver does a single pass only.

How do I get the integration sub and Solver to be repeatedly called, until the minimum integral value is found, after which the result is passed back to a cell, and the code terminates?

hi kubota1000,

Is there a way to know how many passes are required? here is a simple loop you could use, this loops 10 times

Please Login or Register  to view this content.

Will an error occur when minimizing is complete, and you try to run the code again?

Hi, thanks for your help and suggestion.

The number of iterations made by the integrator is not known in advance. It is highly dependent on the integrand, and on the specified precision, both of which are changeable, and are passed as arguments.

The integrator does not use any previously calculated values for its initial data. The sequence of X values is recalculated all over again, and the integrator simply repeats its process and exits with the same result.

When the integrator converges, it puts its final result into a cell. I'm trying to get Solver to (then) use that cell as the Target cell, and to then change two other cells used by the integrand expression.

It does seem that Solver has to be "put on hold" while it waits for the integrator to complete its iterative work, and to then exit its code. Only then should Solver read the target cell (the calculated integral value provided by the integrator), and make its changes to the changing cells as required.

Then, the integrator has to be called again, to calculate the integral using the new changing cells data, while Solver rests up for a while... etc, etc, until the Solver exits.

Any ideas appreciated.

Can ypu give more details about your issue ? (a example file)

Hello, the problem is the following:

I have an excel worksheet that solves system of PDEs using newton-raphson method. The solution obviously depends on some variables that I input. There is a VBA macro (lets call it VBA1) that is used to solve the equations, after solving the equations the macro inputs the solution as values. Basically I can input variables, run macro VBA1 and it gives results as values.
The VBA1 macro gives me modeled results, which depend on variables. I also have some real data from my experiments that I want to fit to the model. The problem can be solved by comparing these two data sets, defining SSE and minimizing it by varying input parameters. I guess it can be done by some optimization algorithm, but in excel we only have solver.
Now the problem is that solver won’t accept the data from the model as it is pasted as values (I guess solver needs explicit solution with formulas), because there is no analytical solution, and as I mentioned earlier, I used newton-raphson iterative method for solution.
I tried to write a macro that utilizes solver for optimization, it calls VBA1 and tries to minimize SSE, but nothing really happens. VBA1 inputs the solution as values, solver tries to change variables but nothing happens. I am not sure why it behaves like this.
Is it possible to write a VBA utilizing solver so it can optimize variables and minimize SSE in this problem? If not possible to utilize solver then why?

I do not have a spreadsheet with me right now, because it is an old problem that I couldn’t solve. Need to search for the file.
I have my original post in this forum, by not able to give you a link because I am a new user)

Originally Posted by nurlanj

Hello, the problem is the following:

I have an excel worksheet that solves system of PDEs using newton-raphson method.....
Is it possible to write a VBA utilizing solver so it can optimize variables and minimize SSE in this problem? If not possible to utilize solver then why?

I do not have a spreadsheet with me right now, because it is an old problem that I couldn’t solve. Need to search for the file.
I have my original post in this forum, by not able to give you a link because I am a new user)

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread