Solver and VBA

[zarasandreas]

Hello and happy new year. I face some problems while trying to cal
Excel solver through VBA in a Windows XP environment. I have teste
the above mentioned problem in both Office XP and Offixe 2003, bu
the message "Solver: An unexpected internal error occurred, o
available memory was exhausted" appear
One part of the code that i have created is as follows

Sub Minimum_Variance_Portfolio(

Application.ScreenUpdating = Fals
SolverRese
Range("b50006").Selec
Set rng = Range(Selection, Selection.End(xlToRight)
SolverOk SetCell:="$B$50012", MaxMinVal:=2
ValueOf:="0", ByChange:=
rng.Addres
SolverAdd CellRef:=rng.Address, Relation:=3
FormulaText:="0
SolverAdd CellRef:="$B$50016", Relation:=2
FormulaText:="1
SolverOk SetCell:="$B$50012", MaxMinVal:=2
ValueOf:="0", ByChange:=
rng.Addres
SolverSolve UserFinish:=Tru
FrmMinVarianceDescriptive.Sho
Application.ScreenUpdating = Tru

End Su

Following some instructions found on the web i added at the beginnin
of the code the following: Application.Ru
"Solver.xla!Autpen

The problem disappeared and the problem was solved

In another part of my code, the following sub was included

Sub Frontier(
'Calculates the solutions that correspond to the portfolio
falling on the frontie
Range("b50007").Selec
Set rng = Range(Selection, Selection.End(xlToRight)
Step = (WorksheetFunction.Max(rng) - WorksheetFunction.Min(rng))
FrontierPoint
Range("b50300").Selec
Selection.End(xlToRight).Offset(0, 2).End(xlDown).Offset(4
0).Selec
Selection.Value = "E(Rp)
Selection.Offset(0, 1).Value = "StDev(p)
Selection.Offset(1, 0).Value = WorksheetFunction.Min(rng
Selection.Offset(1, 0).Selec
For i = 1 To FrontierPoints +
Set rng = Range(Range("b50006")
Range("b50006").End(xlToRight)
Application.ScreenUpdating = Fals
SolverRese
SolverAdd CellRef:=rng.Address, Relation:=3
FormulaText:="0
SolverAdd CellRef:="$B$50016", Relation:=2
FormulaText:="1
RequiredReturn = Selection.Valu
SolverAdd CellRef:="$B$50011", Relation:=3
FormulaText:=RequiredRetur
SolverOk SetCell:="$B$50012", MaxMinVal:=2
ValueOf:="0", ByChange:=
rng.Addres
SolverSolv
rng.Cop
Destination:=Range("b50300").End(xlDown).Offset(4 + i
Selection.Offset(0, 1).Value
Range("b50013").Valu
Selection.Offset(1, 0).Value = Selection.Value + Ste
Selection.Offset(1, 0).Selec
Next
Selection.ClearContent
End Su

I added the following Application.Ru
"Solver.xla!MenuUpdate" at the beggining of the code bu
the problem was not solved. Any suggestions? Since it worked on th
first part of the code, why it still exists in the second part of th
code

Thanks in advanc

ANdrea

 

[Dana DeLouis]

Hi. If you don't get a better response, here are a few observations.
I found the code examples hard to follow due to many "Selection" statements.
Statements like End(xlToRight)), and xlDown are possible "flags." to pay
attention to.
For example, I believe your changing cells are set with...
Set rng = Range(Range("b50006"),.....End(xlToRight))
If it goes to the last column of Excel, you will have more than 200 Changing
cells, which exceeds Solver's limit. (Same caution with use of xlDown)
One technique is to stop the Solver code just before "SolverSolve" and look
at the Solver box from the worksheet. Does everything look good at this
point?

Here are just some thoughts.
Make sure your workbook, and any worksheets, do not have any spaces in their
name.
This causes problems for Solver in some cases.
Try not placing any controls on the worksheet where Solver will run. It's
been reported to cause problems.

"Solver.xla!Autpen"

If you wish, you can just use
SOLVER.Auto_open

Application.ScreenUpdating = False

In theory yes, but Solver needs it on, and turns it back on anyway.
Best just to remove this.

In your first code, you have two SolverOk statements.
You may want to remove one of them.

SolverOk SetCell:="$B$50012", ...
SolverOk SetCell:="$B$50012", ...

In a Min problem, Value of 0 is ignored, and can be removed for clarity.

SolverOk SetCell:="$B$50012", MaxMinVal:=2,
ValueOf:="0", ByChange:= rng.Address

You can "Select", but here's an alternative...

Range("b50007").Select
Set rng = Range(Selection, Selection.End(xlToRight))

With Range("B50007")
Set Rng = Range(.Cells(1), .Cells(1).End(xlToRight))
End With
'Now check Size of Rng

Consider using descriptive names, something like

With Range("B50006")
Set ChgCells = Range(.Cells(1), .Cells(1).End(xlToRight))
End With
'Check ChgCells size is ok....

Again, just some thoughts. Good luck.

 

[Jon Peltier]

I added the following Application.Run

"Solver.xla!MenuUpdate" at the beggining of the code but
the problem was not solved. Any suggestions? Since it worked on the
first part of the code, why it still exists in the second part of the
code?

No, you added Application.Run "Solver.xla!Autpen" to the first part.

- Jon