• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Portfolio Optimization-efficient Frontier : Prompt Solver through VBA


this xlsm is a typical efficient frontier build up where I use Solver to determine dots on the frontier. These dots are generated through solver optimization called through VBA. However, my code does not execute properly :
- it should run 11 routines corresponding to 11 dots on the curve. For that I used a Do While loop with iter as a counter. i named all cells in xls which I took back in VBA.
-then it should report the results in a table in Sheet 2 and for that I used a typical anchor cell and targetRow in combination with Offset as a function.
I have the feeling I am close to make it work but I would definitively need a helping hand to get it done.
Your help (as always) much appreciated.
Best Regards



Well-Known Member
Sub SolverMacro()
Dim TargetRow As Integer
OrigCalculation = Application.Calculation    'store current calculation mode.
iter = 1
Do While iter <= 11
  Sheets("Sheet1").Range("targret").Value = Sheets("Sheet1").Range("Anchor").Offset(iter, 0).Value
  Call SolverSolve(True)
  TargetRow = Sheets("Sheet2").Range("h2").Value + 1
  Sheets("Sheet2").Range("datastart").Offset(TargetRow, 0).Value = Sheets("Sheet1").Range("Output").Offset(1, 0).Value
  Sheets("Sheet2").Range("datastart").Offset(TargetRow, 1).Value = Sheets("Sheet1").Range("Output").Offset(1, 1).Value
  Sheets("Sheet2").Range("datastart").Offset(TargetRow, 2).Value = Sheets("Sheet1").Range("Output").Offset(1, 2).Value
  Sheets("Sheet2").Range("datastart").Offset(TargetRow, 3).Value = Sheets("Sheet1").Range("Output").Offset(1, 3).Value
  Sheets("Sheet2").Range("datastart").Offset(TargetRow, 4).Value = Sheets("Sheet1").Range("Output").Offset(1, 4).Value
  iter = iter + 1
Application.Calculation = OrigCalculation    'reset calculation mode to same as before macro was executed.
End Sub
Note that because Solver set Calculation mode to Manual and doesn't seem to restore Calculation mode to automatic, I've included 2 new code lines to (a) store the current mode and (b) use that to restore the calculation mode at the end. (Sheet2!H2 didn't always recalculate).

I'd have done it differently:
Sub SolverMacro2()
Dim TargetRow As Range, cll As Range, OrigCalculation

OrigCalculation = Application.Calculation    'store current calculation mode.
Set TargetRow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1) 'first location of results.
For Each cll In Sheets("Sheet1").Range("Anchor").Offset(1).Resize(11).Cells
  Sheets("Sheet1").Range("targret").Value = cll.Value
  SolverSolve True
  TargetRow.Resize(, 5).Value = Sheets("Sheet1").Range("Output").Offset(1, 0).Resize(, 5).Value
  Set TargetRow = TargetRow.Offset(1) 'move the target row down one cell.
Next cll
Application.Calculation = xlCalculationAutomatic
End Sub
Note that calling Solver this way uses what manual setttings were applied in the Solver dialogue box before running - not sure that's a good idea.


Hi p45cal
I just tested both and they work perfectly.
I now understand why it did not work
Many many thanks!!!!