Solver Reference Problem
 

Hi,

I have recieved an excel file with a macro, this macro uses the Excel
Solver, please find below part of the code . I have checked Tools-Add
In-Solver Add In in Excel and Tools-References-Solver.xla and save the
file.

When I try to run the macro a message error appears saying that
"SolverSolve" is an unknow function or Sub. I have tried in several ways to
solve this problem, but I am running out of ideas, Can someone give me a
little help?

For references purposes, I have Windows 2000 Professional, Excel 2002 SP-2
and the Solver when I use it manually in Excel works fine.

Thanks and Regards

Andres Navarrete

Sub Solve()

Dim OldActiveCell, OldWorksheets, outsheet As String
Dim Genauigkeit, Konvergenz As Double

outsheet = "BM"

Application.ScreenUpdating = False
OldActiveCell = ActiveCell.Address
OldWorksheets = ActiveSheet.Name

Worksheets(outsheet).Activate

SolverSolve UserFinish:=True

'Cells.Select
'ActiveSheet.Protect Scenarios:=False
' set up solver
SolverReset

SolverOptions MaxTime:=200, Iterations:=10000, Precision:=0.1, _
Estimates:=2, Derivatives:=2, Convergence:=1E-99
SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0", ByChange:="$I$10:$I$16"
SolverAdd CellRef:="$I$10:$I$16", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$I$10:$I$16", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$I$17", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$L$17", Relation:=2, FormulaText:="$B$4"
SolverAdd CellRef:="$M$18", Relation:=2, FormulaText:="$B$5"

'refine precision step by step
Genauigkeit = 0.0001
Konvergenz = 0.001
SolverOptions MaxTime:=200, Iterations:=1000, Precision:=Genauigkeit, _
Estimates:=2, Derivatives:=2, Convergence:=Konvergenz
SolverSolve UserFinish:=True
Genauigkeit = Genauigkeit ^ 1.48
Konvergenz = 1E-31
SolverSolve UserFinish:=True

Worksheets(outsheet).Select
' Cells.Select
' ActiveSheet.Protect Scenarios:=True

Worksheets(OldWorksheets).Activate
Range(OldActiveCell).Activate
Application.ScreenUpdating = True

End Sub

Solver Reference Problem
 

Hi,
No real idea here, but what about:
-When in Tools-References try removing the reference then going back to
Tools-References and instead of just checking the listed solver.xla, try to
browse to the specific location to make sure it is linking properly.
-If you use any function from the Analysis Toolpack, make a reference the
"Analysis Toolpack - VBA"

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Andres" wrote:

Hi,

I have recieved an excel file with a macro, this macro uses the Excel
Solver, please find below part of the code . I have checked Tools-Add
In-Solver Add In in Excel and Tools-References-Solver.xla and save the
file.

When I try to run the macro a message error appears saying that
"SolverSolve" is an unknow function or Sub. I have tried in several ways to
solve this problem, but I am running out of ideas, Can someone give me a
little help?

For references purposes, I have Windows 2000 Professional, Excel 2002 SP-2
and the Solver when I use it manually in Excel works fine.

Thanks and Regards

Andres Navarrete

Sub Solve()

Dim OldActiveCell, OldWorksheets, outsheet As String
Dim Genauigkeit, Konvergenz As Double

outsheet = "BM"

Application.ScreenUpdating = False
OldActiveCell = ActiveCell.Address
OldWorksheets = ActiveSheet.Name

Worksheets(outsheet).Activate

SolverSolve UserFinish:=True

'Cells.Select
'ActiveSheet.Protect Scenarios:=False
' set up solver
SolverReset

SolverOptions MaxTime:=200, Iterations:=10000, Precision:=0.1, _
Estimates:=2, Derivatives:=2, Convergence:=1E-99
SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0", ByChange:="$I$10:$I$16"
SolverAdd CellRef:="$I$10:$I$16", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$I$10:$I$16", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$I$17", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$L$17", Relation:=2, FormulaText:="$B$4"
SolverAdd CellRef:="$M$18", Relation:=2, FormulaText:="$B$5"

'refine precision step by step
Genauigkeit = 0.0001
Konvergenz = 0.001
SolverOptions MaxTime:=200, Iterations:=1000, Precision:=Genauigkeit, _
Estimates:=2, Derivatives:=2, Convergence:=Konvergenz
SolverSolve UserFinish:=True
Genauigkeit = Genauigkeit ^ 1.48
Konvergenz = 1E-31
SolverSolve UserFinish:=True

Worksheets(outsheet).Select
' Cells.Select
' ActiveSheet.Protect Scenarios:=True

Worksheets(OldWorksheets).Activate
Range(OldActiveCell).Activate
Application.ScreenUpdating = True

End Sub

Solver Reference Problem
 

Hi,

I have just solved my problem.

The Solver.... function (SolverSolve, SolverReset, etc) surprisingly doesn't
match with the functions stated in solver.xla. Even in Help files appears as
a Solver.... function, the actual function is Solv....., so I jsut changed
"Solver.." for "Solv.." and now the macro runs fine.

I hope this message could help anyone with this type of problem.

Rgards

andres

"sebastienm" wrote:

Hi,
No real idea here, but what about:
-When in Tools-References try removing the reference then going back to
Tools-References and instead of just checking the listed solver.xla, try to
browse to the specific location to make sure it is linking properly.
-If you use any function from the Analysis Toolpack, make a reference the
"Analysis Toolpack - VBA"

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Andres" wrote:

Hi,

I have recieved an excel file with a macro, this macro uses the Excel
Solver, please find below part of the code . I have checked Tools-Add
In-Solver Add In in Excel and Tools-References-Solver.xla and save the
file.

When I try to run the macro a message error appears saying that
"SolverSolve" is an unknow function or Sub. I have tried in several ways to
solve this problem, but I am running out of ideas, Can someone give me a
little help?

For references purposes, I have Windows 2000 Professional, Excel 2002 SP-2
and the Solver when I use it manually in Excel works fine.

Thanks and Regards

Andres Navarrete

Sub Solve()

Dim OldActiveCell, OldWorksheets, outsheet As String
Dim Genauigkeit, Konvergenz As Double

outsheet = "BM"

Application.ScreenUpdating = False
OldActiveCell = ActiveCell.Address
OldWorksheets = ActiveSheet.Name

Worksheets(outsheet).Activate

SolverSolve UserFinish:=True

'Cells.Select
'ActiveSheet.Protect Scenarios:=False
' set up solver
SolverReset

SolverOptions MaxTime:=200, Iterations:=10000, Precision:=0.1, _
Estimates:=2, Derivatives:=2, Convergence:=1E-99
SolverOk SetCell:="$B$6", MaxMinVal:=1, ValueOf:="0", ByChange:="$I$10:$I$16"
SolverAdd CellRef:="$I$10:$I$16", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$I$10:$I$16", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$I$17", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$L$17", Relation:=2, FormulaText:="$B$4"
SolverAdd CellRef:="$M$18", Relation:=2, FormulaText:="$B$5"

'refine precision step by step
Genauigkeit = 0.0001
Konvergenz = 0.001
SolverOptions MaxTime:=200, Iterations:=1000, Precision:=Genauigkeit, _
Estimates:=2, Derivatives:=2, Convergence:=Konvergenz
SolverSolve UserFinish:=True
Genauigkeit = Genauigkeit ^ 1.48
Konvergenz = 1E-31
SolverSolve UserFinish:=True

Worksheets(outsheet).Select
' Cells.Select
' ActiveSheet.Protect Scenarios:=True

Worksheets(OldWorksheets).Activate
Range(OldActiveCell).Activate
Application.ScreenUpdating = True

End Sub