Optimization of newton-raphson iterative solution with excel solver vba

Cross-posted at https://www.mrexcel.com/board/thread...ation.1246158/

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?

You need some sort of calculation (formula) for solver to solve. Be it newton-raphson iterative method or others.

Otherwise, there is nothing for Solver to solve

Have a look at MrShorty's post below.
https://www.excelforum.com/tips-and-...ind-roots.html

Ok that makes sense.
Are there any other optimization techniques that can solve my problem?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I have added the link to the OP because new are still new.

I'm not sure how much you expect from us. As one who frequently does curve fitting/regression on PDE type equations that require a numerical method to solve, I note that I use the strategies in my thread that CK76 linked to to solve the PDE, then Solver has little difficulty with the regression part of the problem.

You say that you already have a VBA procedure that solves the PDE and writes the result to the spreadsheet. Would you be able to restructure that procedure so it is a Function procedure and can be called as a UDF from the spreadsheet? That's one strategy I use frequently and with good success. My tutorial thread included an example of writing a simple NR algorithm as a UDF and calling it from the spreadsheet, if that will help.

Unfortunately I have no idea how to convert my VBA to UDF, since it is complex. The vba itself generates 2d data, obtained from solving Jacobian matrix, and each of the rows of this 2d dataset is the result of newton-Raphson iterations. And the objective function to minimize is the sum over this 2d dataset.

I read somewhere about solver option called StepThru, would it be able to solve my problem?
I mean mean objective cell already contains formula (for calculation of SSE), would it be enough for solver to assume there is some calculation going on?

I have not heard of a StepThru option as part of Excel's Solver. A quick internet search returns no meaningful hits, so I have no idea what capabilities this option adds.

Here's a complete list of the Excel programming strategies I could foresee using for this.

1) Convert VBA1 to UDF to solve the PDE. Solver to perform the regression. You've already said you have no idea how to make VBA1 into a UDF, so maybe this option is off the table.
2) Convert VBA1 to circular reference spreadsheet formulas (iteration enabled) to solve the PDE. Solver to perform the regression. I, personally, have had minimal difficulty using Jacobian matrices in circular reference type NR formulas. I'm not sure if you feel up to programming this approach, though. Do you think you could convert VBA1 to circular reference spreadsheet formulas?
3) On occasion I have tried setting up Solver to simultaneously solve the PDE and regress the parameters, with mixed success. This can work, but it seems a bit less numerically stable than the previous two strategies.
4) I have never really tried it, but I believe Solver executes Calculate event procedures as part of each calculate event that it executes, so it can include those results in its algorithms. It might be worth the effort to move the VBA1 code into a calculate event procedure (or call VBA1 from a calculate event procedure) and see if Solver can handle the regression while the Calculate event procedure solves the PDE.

Which of those strategies do you feel like trying?

You say that you have no idea how to write a VBA UDF for this, but you also say that you would have no trouble programming the Jacobian calculations in VBA. It seems to me that programming the Jacobian calculations is the hard part of writing a UDF. If you can write the Jacobian calculations in VBA, it seems to me that you should also be able to put it in a VBA UDF. Perhaps you are just unfamiliar with the basics of writing UDFs? I have a tutorial for that here: https://www.excelforum.com/tips-and-...uild-udfs.html

I don't want to push you in a direction you don't want to go, but it seems to me that writing a VBA UDF to solve the PDE would be well within your skillset. Maybe I'm misunderstanding what you are saying.

Edit to add: Here's my strategy when I need a UDF to return an array/matrix/multiple values: https://www.excelforum.com/excel-pro...ml#post4071488

Let us know how it goes, and if there are any specific areas we can help.