Excel 2000 Solver: An unexpected internal error occurred
Excel 2000 Solver: An unexpected internal error occurred
(OP)
"An unexpected internal error occurred, or available memory was exhausted."
I've tried a hotfix download from Microsoft. I've tried the 'Application.Run "Solver.xla!MenuUpdate"', the 'Application.Run "Solver.xla!Auto_Open"'. Then, I tried only loading the parameters that I really needed and the VBA code worked.
Sub Maximum_Moment()
SolverLoad loadArea:=Range("OPT")
SolverOptions Iterations:=200
SolverSolve UserFinish:=True
End Sub
The "OPT" range
=MAX($SOLUTION)
=COUNT($VARIABLE)
=$VARIABLE<'=L
=$VARIABLE>'=0
Then, I made a new VBA macro in the same spreadsheet and kept getting the same error with "manual" Solver runs from the Tools menu and also using the VBA macro code. Here is the code:
Sub Wheel_Loading()
SolverLoad loadArea:=Range("Wheel")
SolverOptions Iterations:=200
SolverSolve UserFinish:=True
End Sub
Then I tried another version with the same results (error)...?
Sub Wheel_Loading()
SolverReset
SolverOk SetCell:="M", MaxMinVal:=1, ByChange:="P1X"
SolverAdd CellRef:="P2X", Relation:=1, FormulaText:="c3"
SolverAdd CellRef:="P1X", Relation:=1, FormulaText:="L"
SolverAdd CellRef:="P1X", Relation:=3, FormulaText:="0"
SolverOptions Iterations:=200, Convergence:=0.00001
SolverSolve UserFinish:=True
End Sub
The "Wheel" range
=MAX($M)
=COUNT($P1X)
=$P1X>'=0
=$P2X<'=L
Any ideas???
I've tried a hotfix download from Microsoft. I've tried the 'Application.Run "Solver.xla!MenuUpdate"', the 'Application.Run "Solver.xla!Auto_Open"'. Then, I tried only loading the parameters that I really needed and the VBA code worked.
Sub Maximum_Moment()
SolverLoad loadArea:=Range("OPT")
SolverOptions Iterations:=200
SolverSolve UserFinish:=True
End Sub
The "OPT" range
=MAX($SOLUTION)
=COUNT($VARIABLE)
=$VARIABLE<'=L
=$VARIABLE>'=0
Then, I made a new VBA macro in the same spreadsheet and kept getting the same error with "manual" Solver runs from the Tools menu and also using the VBA macro code. Here is the code:
Sub Wheel_Loading()
SolverLoad loadArea:=Range("Wheel")
SolverOptions Iterations:=200
SolverSolve UserFinish:=True
End Sub
Then I tried another version with the same results (error)...?
Sub Wheel_Loading()
SolverReset
SolverOk SetCell:="M", MaxMinVal:=1, ByChange:="P1X"
SolverAdd CellRef:="P2X", Relation:=1, FormulaText:="c3"
SolverAdd CellRef:="P1X", Relation:=1, FormulaText:="L"
SolverAdd CellRef:="P1X", Relation:=3, FormulaText:="0"
SolverOptions Iterations:=200, Convergence:=0.00001
SolverSolve UserFinish:=True
End Sub
The "Wheel" range
=MAX($M)
=COUNT($P1X)
=$P1X>'=0
=$P2X<'=L
Any ideas???





RE: Excel 2000 Solver: An unexpected internal error occurred
Sub Model()
SolverReset
SolverOk SetCell:="M"
SolverOk MaxMinVal:=1
SolverOk ByChange:="P1X"
SolverAdd CellRef:="P2X", Relation:=1, FormulaText:="c3"
SolverAdd CellRef:="P1X", Relation:=1, FormulaText:="L"
SolverAdd CellRef:="P1X", Relation:=3, FormulaText:="0"
SolverOptions Iterations:=200, Convergence:=0.00001
SolverSolve UserFinish:=True
End Sub
I am still clueless ???
RE: Excel 2000 Solver: An unexpected internal error occurred
SolverOk ByChange:="P1X"
SolverAdd CellRef:="P1X", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="P1X", Relation:=1, FormulaText:="L"
SolverAdd CellRef:="P2X", Relation:=1, FormulaText:="L"
P1X, P2X and L are named cells.
RE: Excel 2000 Solver: An unexpected internal error occurred
CODE
SolverReset
SolverOk SetCell:="M", MaxMinVal:=1, ByChange:="P1X"
SolverAdd CellRef:="P2X", Relation:=1, FormulaText:="c3"
SolverAdd CellRef:="P1X", Relation:=1, FormulaText:="L"
SolverAdd CellRef:="P1X", Relation:=3, FormulaText:="0"
SolverOptions Iterations:=200, Convergence:=0.00001
SolverSolve UserFinish:=True
End Sub
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel 2000 Solver: An unexpected internal error occurred
I tried your suggestion and got the same error. In my previous version, I tried separating the SolverOK parameters and single-stepped through the macro with the F8 (Debug)key and it seems that I get an error with the ByChange:="P1X" parameter.
By the way, I get the same error with these values using Solver without using VBA at all. Plus, I have another similar VBA macro that uses Solver without any problems.
I am almost ready to check into the hospital psyche ward!
RE: Excel 2000 Solver: An unexpected internal error occurred
I hope the following modification works for you:
CODE
SolverReset
SolverOk MaxMinVal:=1
ActiveSheet.Names.Add Name:="solver_opt", RefersTo:=Range("M"), Visible:=False
ActiveSheet.Names.Add Name:="solver_adj", RefersTo:=Range("P1X"), Visible:=False
SolverAdd CellRef:="P2X", Relation:=1, FormulaText:="c3"
SolverAdd CellRef:="P1X", Relation:=1, FormulaText:="L"
SolverAdd CellRef:="P1X", Relation:=3, FormulaText:="0"
SolverOptions Iterations:=200, Convergence:=0.00001
SolverSolve UserFinish:=True
End Sub
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel 2000 Solver: An unexpected internal error occurred
Thanks, this fixed the ByChange:="P1X" parameter. Single-stepping with F8 nows up on the 3 constaint lines, "SolverAdd". I think you are on the right track.
Any ideas? I downloaded the name manager add-in, but I don't have a clue to constraint names.
RE: Excel 2000 Solver: An unexpected internal error occurred
The relation type is stored in solver_rel1 etc.
The left-hand side is in solver_lhs1 etc., set to Range("P2X") etc.
Let me know if this is enough info for you.
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel 2000 Solver: An unexpected internal error occurred
CODE
Dim myName As Name, ConstraintNr As Integer
For Each myName In ActiveSheet.Names
If Left$(myName.Name, 7) = "solver_" Then myName.Delete
Next myName
SolverReset
SolverOk MaxMinVal:=1
'SolverOk SetCell:="M", MaxMinVal:=1, ByChange:="P1X"
ActiveSheet.Names.Add Name:="solver_opt", RefersTo:=Range("M"), Visible:=False 'Set the target cell
ActiveSheet.Names.Add Name:="solver_adj", RefersTo:=Range("P1X"), Visible:=False 'Set the changing cell(s)
' SolverAdd CellRef:="P2X", Relation:=1, FormulaText:="c3"
' SolverAdd CellRef:="P1X", Relation:=1, FormulaText:="L"
' SolverAdd CellRef:="P1X", Relation:=3, FormulaText:="0"
ConstraintNr = 1
MySolverAdd Constraint:=ConstraintNr, CellRef:="P2X", Relation:=1, FormulaText:="c3" 'Set the first constraint, ConstraintNr is augmented in MySolverAdd
MySolverAdd Constraint:=ConstraintNr, CellRef:="P1X", Relation:=1, FormulaText:="L" 'Set the next constraint
MySolverAdd Constraint:=ConstraintNr, CellRef:="P1X", Relation:=3, FormulaText:="0" 'Set the next constraint
ActiveSheet.Names.Add Name:="solver_num", RefersTo:=(ConstraintNr - 1), Visible:=False 'Set the number of constraints (=3)
SolverOptions Iterations:=200, Convergence:=0.00001
SolverSolve UserFinish:=True
End Sub
Sub MySolverAdd(ByRef Constraint As Integer, ByVal CellRef As String, ByVal Relation As Integer, ByVal FormulaText As String)
With ActiveSheet.Names
.Add Name:="solver_lhs" & CStr(Constraint), RefersTo:=Range(CellRef), Visible:=False
.Add Name:="solver_rel" & CStr(Constraint), RefersTo:=Relation, Visible:=False
.Add Name:="solver_rhs" & CStr(Constraint), RefersTo:="=" & FormulaText, Visible:=False
End With
Constraint = Constraint + 1 'setup next constraint index
End Sub
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel 2000 Solver: An unexpected internal error occurred
It worked great. Since I'm new at VBA, I'm going to study this a little bit and learn something. I may be able to simplify it a little bit since I only have one worksheet and one chart in my excel workbook.
Maybe you can teach an old dog new tricks. This worksheet was one I originally did in Quattro Pro(DOS) and included macros for the "optimizer".
Thanks again!
RE: Excel 2000 Solver: An unexpected internal error occurred
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel 2000 Solver: An unexpected internal error occurred
The Constraint and ContraintNr variables are not getting incremented in the Model() subroutine. I haven't been able to figure it out.
It causes a formula in cell P10 (V9R) to get modified by replacing one of the variables W1L to P1X. Bizarre!!!