Using external subroutine in Solver (in VBA)

I don't think you can accomplish exactly what you describe. There might be "pay" upgrades to Solver that can do this, but I am not certain.

You don't give any details of what you algorithm is doing or how it is coded. My first thought is to rewrite your VBA procedures so that they are "User-Defined Functions" (function procedures that can be called from a spreadsheet cell like a regular Excel function). These can work very well as part of Solver models.

Of course, if you understand the functions well enough, you can program your own optimization algorithms (Newton Raphson or bisection or whatever) into VBA.

The objective cell must contain a formula.

I am familiar with some of those equations (mostly VLE type equations, such as cubic EOS's, activity coefficient equations, and the like), and I use VBA user-defined functions for many of those equations. So, my answer is still basically the same. Revise your current algorithm so that it fits into one or more user-defined functions. Then you can enter those functions into a spreadsheet and use Solver's algorithms to optimize or solve the mathematical model.

Either that or learn to program the kinds of algorithms that Solver uses, and incorporate those algorithms into your VBA. I use this strategy in some of my code as well, frequently using newton raphson or bisection type algorithms to solve and optimize equations. A lot will depend on exactly what algorithms you are working with and how you are implementing them, but many of these root finding and optimization algorithms are not overly difficult to program, once you have the "main" algorithm in place.

Hello,
Just wanted to share some observations.

It looks like you can use Solver and a VBA procedure for the above problem I described. Heres how,
1. Use a "Worksheet_Change" subroutine to capture any changes occurring to certain cells of interest (Solver variables) in the excel worksheet.
2. Inside the worksheet_change, call the procedure you want to run to print out the results in excel. Calculate an objective function in excel (using excel formula). Set this cell as Objective in Solver.

Use Solver to setup the optimization problem. When constraint variables are modified by the Solver, it is captured by the "Worksheet_Change" subroutine, and this inturn runs the algorithm you have coded and minimizes the objective function. Below is an example with 6 parameters in cells C1:C6.

This has worked well for me, and I can print the Solver results after each iteration.
Comments are welcome.

Please Login or Register  to view this content.