Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2000 Solver: An unexpected internal error occurred 2

Status
Not open for further replies.
SteveGregory

SteveGregory

Structural
Jul 18, 2006
554
"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???
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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 ???
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Try this:
Code: 
Sub Model()
    SolverReset
    [b]SolverOk SetCell:="M", MaxMinVal:=1, ByChange:="P1X"[/b]
    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 for tips on how to make the best use of Eng-Tips.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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 for tips on how to make the best use of Eng-Tips.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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 for tips on how to make the best use of Eng-Tips.
 
Upvote 0 Downvote
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 for tips on how to make the best use of Eng-Tips.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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 for tips on how to make the best use of Eng-Tips.
 
Upvote 0 Downvote
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!!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BhagyeshVashi
  • Locked
  • Question
Need a rename macro for excel
Replies
11
Views
1K
sdz
sdz
n8cole
  • Locked
  • Question
Looking for an AI that will help me create a chemical functional group database
Replies
7
Views
3K
pierreick
pierreick
Kenja824
  • Locked
  • Question
excel Formula Find help
Replies
5
Views
1K
Kenja824
Kenja824
jhn1611
  • Locked
  • Question
Compass question excel 4
2 3 4
Replies
62
Views
2K
IDS
IDS
3DDave
  • Locked
  • Question
Python now integrated into Excel - apparently
Replies
8
Views
1K
IDS
IDS

Part and Inventory Search

Sponsor

Back
Top Bottom