3

I am trying to find a maximum return for a simple portfolio using Solver. Using Solver in the worksheet directly works sensibly, however it does not when the commands are set in VBA. Instead (as you can see from the screengrab)it ignores one of the constraints (that the sum of weights calculated in T10 should =1). Interestingly it works fine if I change the third line to say:

SolverAdd CellRef:="$T$10", Relation:=2, FormulaText:="100"

Or any other integer other than "1". (It may also be ignoring the other constraint but I cannot check this). The table looks like this: enter image description here

And my code is:

Sub FindRange()

                SolverReset
                SolverOk SetCell:="$T$7", MaxMinVal:=1, ValueOf:="0", ByChange:="$O$10:$R$10"
                SolverAdd CellRef:="$T$10", Relation:=2, FormulaText:="1"
                SolverAdd CellRef:="$O$10:$R$10", Relation:=3, FormulaText:="0"
                SolverSolve UserFinish:=True
                SolverFinish KeepFinal:=1
                Range("T9").Value = Range("T7").Value
           End Sub

Any suggestions gratefully welcomed!

Improve this question
7
  • The code looks good to me. Does it seem to iterate the same way non-VBA vs VBA? Maybe try adding the CellRef's before the SolverOK? Commented Mar 25, 2013 at 20:41
  • Maybe try using just FormulaText:=1? (without the double quotes) I mention it because the code on MSDN doesn't use quotes. Commented Mar 25, 2013 at 22:11
  • Thanks. Removing the double quotes does help (i.e. it stays below 10), however it now ignores "$O$10:$R$10" should equal 0. So both constraints are being ignored Commented Mar 26, 2013 at 12:11
  • It appears to work non-VBA Commented Mar 26, 2013 at 15:12
  • Rebooting Excel seems to have solved it.... Commented Mar 28, 2013 at 9:45

6 Answers 6

Reset to default
3

Found a work around for the bug. For the flag "FormulaText:=1". instead of using 1, use a reference to any cell with the value 1 instead.

I.e., Change "FormulaText:=1" to "FormulaText:=$H$5" where $H$5's value is 1

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Unfortunately this still fails sometimes.
1

I think there is a bug here whenever the value is exactly 1. Other postings state that the above solution (putting the value into a cell) is unreliable. I have found it does not work a my code always refers to a cell which holds the constraint limit. My (crude) solution is to shift the limit value by 1 part in 10^12 or lower in the appropriate direction which sort of makes the constraint into a < or > rather than a <= or >=. So rather than:

SolverAdd CellRef:=Range("SolverParam").Address, Relation:=3, _ FormulaText:=Range("SolverConstraint").value

Use:

SolverAdd CellRef:=Range("SolverParam").Address, Relation:=3, _ FormulaText:=Range("SolverConstraint").value + Abs(Range("SolverConstraint").value) * 1e-12

And use the opposite sign for Relation:=1

In this trivial example SolverParam is a single cell parameter to be adjusted and SolverConstraint is a single cell lower bound.

This is the only consistent approach I can foresee to have a uniform handling of all values

On further looking I found another solution from the web

FormulaText:="=" & Range("SolverConstraint").value

Seems to work reliably

Improve this answer

1 Comment

Changing Relation:=2, FormulaText:1 to Relation:=2, FormulaText:="=1" solved my problem.
0

I had the exact same issue. I solved it in the following way: Just type FormulaText:=1 without the quotes for 1.

Improve this answer

Comments

0

Adding an "=" as sevenkul suggested worked for me.

SolverAdd CellRef:="$F$13", Relation:=2, FormulaText:="=1"

Prior to that I was also getting the missing constraint in the solver window. Adding the constraint manually from the worksheet worked fine, but would not show up after running VBA code (due to a SolverReset call). I also think the issue is not limited to the value 1. I also tried with 2 and had a similar result.

Oddly enough after running the suggested fix once, I no longer need the extra "=" in the FormulaText. I closed and reopened the workbook and do not need the workaround. Running it once seems to do it. Not sure if this will need to be done on every new workbook using a solver in the code.

Improve this answer

Comments

0

I've been working with expressing constraints using SolverAdd in VBA and have encountered the "missing constraints" bug as others.

It would perhaps be helpful to know: When the problem is encountered AND when the problem appears to be solved AND PERHAPS when Excel and a workbook is opened: What is the state of the constraints in the Solver Parameter dialog? i.e.Open Data, Solver to get the Solver Parameters dialog and examine the Constraints list there. This appears to be the only way to really compare results.

Improve this answer

2 Comments

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review
OK - it's not an answer. So, I posted an answer next.
0

The workaround that I came up for missing constraints, does this:

  1. Don't use SolverAdd in VBA. That appears to be how this issue starts.
  2. Instead, use the Solver Parameters dialog that opens in a worksheet under Data/Solver.
  3. To make it easier to deal with, structure the variables and constraints in a list in the worksheet: Name | Type | Lower Bound | Value | Upper Bound For Binary types, only the value is necessary. For Integer and (non-integer constrained) types, the bounds may be necessary. (I use Evolutionary mode usually and make them required).
  4. With this done, you can select the Value cells to create ranges to paste into the Solver Parameters dialog in the appropriate places. You will have to specify Binary, Integer for those. For integer, you will also need to enter the <= constraints and the >= constraints, just the same as with non-integer constraints.
    This may seem counter to what you want to do if your intent is to use VBA. But, fortunately, this structured approach works well with VBA implementations because the Solver Parameters dialog information sticks once entered. So, now one is able to write optimization subs and to use SolverOk and SolverSolve in VBA. You can change the SetCell entry if appropriate, just make sure to enter the ByChange:= range the same as is entered in the Solver Parameters dialog. This is the only solution I've found after a lot of effort, study and research. I'm running large problems consistently without trouble as long as I don't use SolverAdd. I note that others have found positive results doing something similar.
    It's worthy of note that the right-hand-side RHS of the constraints can be entered as ranges as set out above. I had hoped this would help use SolverAdd but it didn't. The approach using ranges on both the LHS and RHS reduced the number of constraint entries from around 80 down to 6. This makes it much easier to debug!!
Improve this answer

1 Comment

I'm writing a tutorial about this and, in my simple example workbook, found that the ranges expressed in "Subject to the Constraints" might have to use separate range entries that involve decision variables on the left-hand-side (LHS) vs. formulas on the LHS. I have usually been doing this on larger problems so hadn't encountered it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.