Solver Reference Problem

[Andres]

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

[sebastienm]

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

[Andres]

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