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!Auto_Open"
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.
--
HTH :)
Dana DeLouis
Windows XP & Office 2003
"zarasandreas" wrote in
message ...
Hello and happy new year. I face some problems while trying to call
Excel solver through VBA in a Windows XP environment. I have tested
the above mentioned problem in both Office XP and Offixe 2003, but
the message "Solver: An unexpected internal error occurred, or
available memory was exhausted" appears
One part of the code that i have created is as follows:
Sub Minimum_Variance_Portfolio()
Application.ScreenUpdating = False
SolverReset
Range("b50006").Select
Set rng = Range(Selection, Selection.End(xlToRight))
SolverOk SetCell:="$B$50012", MaxMinVal:=2,
ValueOf:="0", ByChange:= _
rng.Address
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.Address
SolverSolve UserFinish:=True
FrmMinVarianceDescriptive.Show
Application.ScreenUpdating = True
End Sub
Following some instructions found on the web i added at the beginning
of the code the following: Application.Run
"Solver.xla!Auto_Open"
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 portfolios
falling on the frontier
Range("b50007").Select
Set rng = Range(Selection, Selection.End(xlToRight))
Step = (WorksheetFunction.Max(rng) - WorksheetFunction.Min(rng)) /
FrontierPoints
Range("b50300").Select
Selection.End(xlToRight).Offset(0, 2).End(xlDown).Offset(4,
0).Select
Selection.Value = "E(Rp)"
Selection.Offset(0, 1).Value = "StDev(p)"
Selection.Offset(1, 0).Value = WorksheetFunction.Min(rng)
Selection.Offset(1, 0).Select
For i = 1 To FrontierPoints + 1
Set rng = Range(Range("b50006"),
Range("b50006").End(xlToRight))
Application.ScreenUpdating = False
SolverReset
SolverAdd CellRef:=rng.Address, Relation:=3,
FormulaText:="0"
SolverAdd CellRef:="$B$50016", Relation:=2,
FormulaText:="1"
RequiredReturn = Selection.Value
SolverAdd CellRef:="$B$50011", Relation:=3,
FormulaText:=RequiredReturn
SolverOk SetCell:="$B$50012", MaxMinVal:=2,
ValueOf:="0", ByChange:= _
rng.Address
SolverSolve
rng.Copy
Destination:=Range("b50300").End(xlDown).Offset(4 + i)
Selection.Offset(0, 1).Value =
Range("b50013").Value
Selection.Offset(1, 0).Value = Selection.Value + Step
Selection.Offset(1, 0).Select
Next i
Selection.ClearContents
End Sub
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?
Thanks in advance
ANdreas
------------------------------------------------------------------------
zarasandreas's profile:
http://www.exceljockeys.com/forums/p...ewprofile&u=10
View this thread:
http://www.exceljockeys.com/forums/v...c.php?t=324193