Adding constraints with VBA to the solver tool

Question

I am trying to add some new constraints to my excel VBA solver solution and am getting some odd behavior when I run the code. I want to do the following for a minimization problem and am having some trouble:

The two levers that are being adjusted must be <= 35%, however when I add the constraint:

SolverAdd CellRef:="$C$39", Relation:=2, FormulaText:=".35"
SolverAdd CellRef:="$C$40", Relation:=2, FormulaText:=".35"

They automatically set themselves at 35% when the idea is for these to be as low as possible. Without this constraint the cells do set themselves below 35% for the problems I'm validating against, so I know the optimum solution is below 35%.

Also I was wondering if there is a way to make sure that a) the cells are optimized in increments of 5%, and that the ending value does not include decimals. i.e. 10.00% instead of 10.23%

Sorry for the long winded question, I appreciate any help I get.

Here is the full code:

    Private Sub CommandButton2_Click()

Dim i As Integer


i = 2

For i = 2 To 5

   Range("$C$14").Value = Application.Workbooks("test_model_2.xls").Worksheets("All Models").Cells(i, 2).Value
    SolverReset
    SolverAdd CellRef:="$F$70", Relation:=3, FormulaText:="5000"
    SolverAdd CellRef:="$C$39", Relation:=2, FormulaText:=".35"
    SolverAdd CellRef:="$C$40", Relation:=2, FormulaText:=".35"
    SolverOk SetCell:="$F$70", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$39:$C$40"
    SolverSolve True
   MsgBox "i is:" & i

Next i

End Sub

I've also tried removing the " " around the FormulaText to no effect. — Andy
– Andy, Commented Sep 6, 2011 at 19:04

Tim Williams · Accepted Answer · 2011-09-06 19:19:51Z

2

See: http://msdn.microsoft.com/en-us/library/aa272233(v=office.10).aspx

You're using relation:=2, which equates to "=". You should be using 1 (<=)

As for getting your solution to the nearest 0.05, just set up some other cells which feed into the "percent" cells, and have them constrained to integer values.

Eg:

A1 = 7           (set solver to change this, and constrain to integer)
B1 = A1 * 0.05   (your percent cell, constrain to <=0.35)

answered Sep 6, 2011 at 19:19

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

Andy Over a year ago

Sounds like this should work. I'm implementing it right now as we speak and I will let you know. Thanks again Tim.

Collectives™ on Stack Overflow

Adding constraints with VBA to the solver tool

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related