Excel 2000 Solver: An unexpected internal error occurred 2

[SteveGregory]

"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!Autpen"'. 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???

 

[SteveGregory]

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

 

[SteveGregory]

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.

 

[joerd]

Try this:

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.

 

[SteveGregory]

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!

 

[joerd]

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:

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.

 

[SteveGregory]

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.

 

[joerd]

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.

 

[joerd]

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.

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.

 

[SteveGregory]

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!

 

[joerd]

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.

 

[SteveGregory]

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