×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel 2000 Solver: An unexpected internal error occurred
2

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???

RE: Excel 2000 Solver: An unexpected internal error occurred

(OP)
I tried changing my second version of the macro to find out what instruction was causing Solver to choke. The error occurs when setting the ByChange cell P1X. New VBA macro:

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

(OP)
The error occurs using Solver from the TOOLS menu or VBA. I experimented with one of the VBA macros (see original email) and it appears that I isolated these commands as culprits:
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

2
Try this:

CODE

Sub Model()
    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

(OP)
Joerd,
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

The Solver add-in is pretty crappy in how it handles the names it uses to store the addresses of the variable cells. If you're interested, download Jan Karel Pieterse's NameManager.xla and see for yourself.
I hope the following modification works for you:

CODE

Sub Model()
    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
Good luck!

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

(OP)
Joerd,
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

I am guessing that you need to set solver_rhs1, solver_rhs2 and solver_rhs3 to the "right hand side", i.e. "0", "L" and "L" in your case.
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

Here is what I finally came up with. You have to set all names local to the sheet where the solver is running to make it work, so that is what MySolverAdd does.

CODE

Sub Model()
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

(OP)
Thanks Joerd,
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

Glad to help. That's the way I learned VBA, just try to understand somebody else's code, or whatever the macro recorder came up with.

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

(OP)
Joerd,
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!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources