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

-----------------------------------------------------------------------
zarasandreas's profile: http://www.exceljockeys.com/forums/p...iewprofile&u=1
View this thread: http://www.exceljockeys.com/forums/v...ic.php?t=32419

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

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!Auto_Open" to the first part.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"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