Repeated application of Solver to new equations

[John Cordes]

I'm trying to help a friend with this one. He has about 5,000
equations, which for the sake of discussion can be taken of the form
2*x^2 - 5*x + 1.
The multiplicity of equations comes about because each eqn. has a
different coefficient of the linear term in x. So the picture is
something like this: there is a column of (say) 5000 values of the
linear coefficient - in column A, say. Column B holds the quadratic,
using the linear coefficient from Col. A.

What he wants to do is to repeatedly apply Solver to all these
equations in turn. I am (very) weak in VBA, but this simple
macro/program (basically obtained by recording) does work ok,
interactively, for one equation:

Sub Solve()
SolverOk SetCell:="$E$2", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$2"
SolverSolve
End Sub

Would anyone have any ideas which would help to streamline the process
of obtaining the solution of 5000 such equations?

Thanks very much for any advice,
John Cordes

 

[Dana DeLouis]

The general solutions to a* x^2+b*x+c==0 is...

= (-b - Sqrt(b^2 - 4*a*c))/(2*a)
= (-b + Sqrt(b^2 - 4*a*c))/(2*a)

Would that help?
Solver would give only 1 solution. The two solutions to your example
(2*x^2 - 5*x + 1) would be:

0.2192235936
2.280776406

HTH

 

[John Cordes]

The general solutions to a* x^2+b*x+c==0 is...

= (-b - Sqrt(b^2 - 4*a*c))/(2*a)
= (-b + Sqrt(b^2 - 4*a*c))/(2*a)

Would that help?
Solver would give only 1 solution. The two solutions to your example
(2*x^2 - 5*x + 1) would be:

0.2192235936
2.280776406

HTH

Thank you for responding. Probably the quadratic expression was a poor
choice to illustrate the problem. I believe my friend's actual usage
will require solver's capabilities, if he is to use Excel at all. So
let's assume that the equations he really needs to solve are either
transcendental, or perhaps higher order polynomials. I am waiting for
him to get back to me about this, so I will know more precisely what
form of equations he wants to solve.

I suppose it is possible that he really does have quadratics, but was
thinking in terms of Solver (actually he was originally using Goal Seek,
till I mentioned Solver - would Goal Seek be any easier?) because he
wants to specify some range for the desired zero. Maybe there could be
another column to test the explicit quadratic formula results for a
desired interval. If we use the quadratic formula we'd have to be
careful to do some testing and use a numerically stable form.

Regards,
John

 

[Tushar Mehta]

If you still have to use Solver after reading Dana's post...

The easiest way to use Solver is to solve different problems is to set
each up anew and use SolverReset to reset the previous conditions. So,
in the simple example of the 'Set Cell' being column E and the 'By
changing cell' being column B, and the different problems constructed
in rows 2:10, one would use the untested:

for i=2 to 10
SolverReset
SolverOk SetCell:="$E$" & cstr(i), MaxMinVal:=3, _
ValueOf:="0", ByChange:="$B$" & cstr(i)
SolverSolve
SolverFinish 'I believe you will need this; check the help
Next i

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[John Cordes]

If you still have to use Solver after reading Dana's post...

The easiest way to use Solver is to solve different problems is to set
each up anew and use SolverReset to reset the previous conditions. So,
in the simple example of the 'Set Cell' being column E and the 'By
changing cell' being column B, and the different problems constructed
in rows 2:10, one would use the untested:

for i=2 to 10
SolverReset
SolverOk SetCell:="$E$" & cstr(i), MaxMinVal:=3, _
ValueOf:="0", ByChange:="$B$" & cstr(i)
SolverSolve
SolverFinish 'I believe you will need this; check the help
Next i

Tushar,

Thank you - your suggestion of using SolverReset seems to be working in
one test I've made so far (only 2, rather than 10, equations). One has
to click OK on Solver's result after each equation, so 5000 will be
rather painful by this technique, unless there's also a way to use VBA
to accept each result in turn (ignoring any error trapping for now at
least).

John

 

[Tom Ogilvy]

Maybe there is something useful in here:

http://support.microsoft.com/support/excel/content/solver/solver.asp
Creating Visual Basic Macros that Use Microsoft Excel Solver

 

[John Cordes]

Maybe there is something useful in here:

http://support.microsoft.com/support/excel/content/solver/solver.asp
Creating Visual Basic Macros that Use Microsoft Excel Solver

Thank you, Tom. That looks to be a very comprehensive page - a lot of
reading required! The crucial step suggested there seems to be to use
SolverSolve UserFinish:=True
That prevents the Solver dialog box from popping up.

Progress is being made, thanks to you knowledgeble and generous folks here.

John

 

[Dana DeLouis]

You may want to consider these additional ideas with Solver...

Sub Demo()
Dim R As Long 'Row
Dim Results As Long

For R = 2 To 10
SolverReset
SolverOk _
SetCell:=Cells(R, 5).Address, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=Cells(R, 2).Address
Results = SolverSolve(True)
Select Case Results
Case 0, 1, 2
'Results should be valid
SolverFinish ' Keep results (default)
Case Else
'Your code here
'SolverFinish 2' You may want to discard results here
End Select
Next R

End Sub

= = = = = = =

Case 0,1,& 2 correspond to these messages...

Sub Demo2()
'// Dana DeLouis
Dim Result As Long

With Workbooks("SOLVER.XLA").Sheets("LANGUAGE")
For Result = 0 To 2
Debug.Print Result; .Cells(Result + 51, 1)
Next Result
End With
End Sub

Returns the following...

0 Solver found a solution. All constraints and optimality conditions are
satisfied.
1 Solver found a solution within tolerance. All constraints and optimality
conditions are satisfied.
2 Solver has converged to the current solution. All constraints are
satisfied.