Problems with solver and vba

[Marco]

Dear all,
i'm trying to create a Visual Basic Macro that uses Excel
Solver.
I simply put on cell A1 a numeric value (example 3) and
on cell B1 the formula =A1*A1
Then in VBA i write the following:

-----------------------------------------------------
Sub Find_Square_Root()

' Set up the parameters for the model.
' Set the target cell A2 to a Value of 50 by changing
cell A1.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3,
ValueOf:=50, _
ByChange:=Range("A1")

' Solve the model but do not display the Solver
Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub
----------------------------------------------------------

Unfortunately by executing the macro from EXCEL nothing
happens. It seems that SOLVER is not able to load the
variables.
I can solve the problem by making a pre-run of SOLVER
from EXCEL setting the target cells and the cells to be
modified by hand.
Once that this pre-run is done I can load the macro that
works perfectly.

Is there any way to avoid initialising SOLVER from EXCEL
by hand and let it work from the macro?

thanks
Marco

 

[David Adamson]

look at
http://support.microsoft.com/default.aspx?scid=/support/excel/content/solver/solver.asp

"

 

[Guest]

-----Original Message-----
look at
http://support.microsoft.com/default.aspx? scid=/support/excel/content/solver/solver.asp

"


.

To be honest i just tried to make the axample work before
posting my first message. Unfortunately it didn't work
without making the solver pre-run from excel.
Try to copy and paste these lines from
http://support.microsoft.com/default.aspx?
scid=/support/excel/content/solver/solver.asp


Sub Find_Square_Root2()

Dim val
Dim sqroot

' Request the value for which you want to get the
square root.
val = Application.InputBox( _
prompt:="Please enter the value for which you
want " & _
"to find the square root:", Type:=1)

' Set up the parameters for the model.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3,
ValueOf:=val, _
ByChange:=Range("A1")

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of cell A1 (the changing cell)
before you discard
' the results.
sqroot = Range("a1")

' Finish and discard the results.
SolverFinish KeepFinal:=2

' Show the result in a message box.
MsgBox "The square root of " & val & " is " & Format
(sqroot, "0.00")

End Sub

Bye
Marco