Programming the Solver search can help and significantly speed up data processing when it is necessary to analyze how the optimal solution depends on a parameter. Let us demonstrate this with the simplest problem, which is similar to the nonlinear programming task discussed earlier.
Suppose we need to solve the system of nonlinear equations:
x2+y2−1=0, 2x+3y−d=0
where the parameter dd varies in the interval from 0.2 to 1 with a step of 0.1.
It is obvious that solving this system is equivalent to solving the equation:
(x2+y2−1)2+(2x+3y−d)2=0.
The original system of equations cannot, of course, be solved directly using Solver, but the equivalent equation with two unknowns is suitable to attempt solving.
Worksheet Setup
On the worksheet, allocate cells A1 and B1 for the unknowns, cell D1 for the parameter values, and in cell C1 enter the formula for the left-hand side of the equation:
=(A1^2+B1^2-1)^2+(2*A1+3*B1-D1)^2
Also, place a button on the worksheet that will open the form of our application.
Application Interface
Now proceed to constructing the application interface. Create a form with three labels, three input fields, and one button. Set the properties of these controls in the Properties window as shown in Table.
Table. Values of properties set in the Properties window
| Object | Property | Value |
| Form | Name | frmSystemSolver |
| Caption | Solving a System Dependent on a Parameter | |
| Label | Caption | Initial Value |
| TextBox | Name | txtBegin |
| Label | Caption | Final Value |
| TextBox | Name | txtEnd |
| Label | Caption | Step Size |
| TextBox | Name | txtStep |
| Button | Name | cmdOK |
| Caption | OK |
Code Requirements
Before entering the necessary code into the form module and worksheet module Sheet1 , make sure that a reference to Solver.xlam is enabled in the References window.
Thus, to solve the problem it is sufficient to enter the initial value, final value of the parameter, and the step size into the input fields. After pressing the OK button, the application will find the solutions and display the parameter values in column E, and the unknowns x and y in columns F and G.
NOTE
From a geometric point of view, the problem considered here also represents finding the intersection point of a line and a circle. Clearly, this problem either has no solution (the line does not intersect the circle), exactly one solution (the line is tangent to the circle), or two solutions (the line intersects the circle).
Our program found only half of the solutions. This is because all solutions were found using the same initial approximation—namely, zero.