使用 VBA 向求解器工具添加约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7324693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding constraints with VBA to the solver tool
提问by Andy
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:
我正在尝试向我的 excel VBA 求解器解决方案添加一些新约束，并且在运行代码时出现了一些奇怪的行为。我想为最小化问题执行以下操作，但遇到了一些麻烦：
The two levers that are being adjusted must be <= 35%, however when I add the constraint:
正在调整的两个杠杆必须 <= 35%，但是当我添加约束时：
SolverAdd CellRef:="$C", Relation:=2, FormulaText:=".35"
SolverAdd CellRef:="$C", 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%.
当他们的想法是尽可能低时，他们会自动将自己设置为 35%。如果没有这个限制，对于我正在验证的问题，单元格确实将自己设置为低于 35%，所以我知道最佳解决方案低于 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%
另外我想知道是否有办法确保 a) 单元格以 5% 的增量进行优化，并且最终值不包括小数。即 10.00% 而不是 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").Value = Application.Workbooks("test_model_2.xls").Worksheets("All Models").Cells(i, 2).Value
SolverReset
SolverAdd CellRef:="$F", Relation:=3, FormulaText:="5000"
SolverAdd CellRef:="$C", Relation:=2, FormulaText:=".35"
SolverAdd CellRef:="$C", Relation:=2, FormulaText:=".35"
SolverOk SetCell:="$F", MaxMinVal:=2, ValueOf:="0", ByChange:="$C:$C"
SolverSolve True
MsgBox "i is:" & i
Next i
End Sub
回答by Tim Williams
See: http://msdn.microsoft.com/en-us/library/aa272233(v=office.10).aspx
请参阅：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 (<=)
您正在使用关系：= 2，相当于“=”。您应该使用 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.
至于让您的解决方案最接近 0.05，只需设置一些其他单元格，这些单元格输入“百分比”单元格，并将它们限制为整数值。
Eg:
例如：
A1 = 7 (set solver to change this, and constrain to integer)
B1 = A1 * 0.05 (your percent cell, constrain to <=0.35)
