vba Excel求解器忽略VBA中的约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15620177/
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
Excel Solver Ignoring Constraint in VBA
提问by Mary
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:
我正在尝试使用 Solver 为简单的投资组合找到最大回报。在工作表中直接使用求解器很有效，但是在 VBA 中设置命令时则不然。相反（正如您从屏幕抓取中看到的那样）它忽略了一个约束（在 T10 中计算的权重总和应该 =1）。有趣的是，如果我将第三行更改为：
SolverAdd CellRef:="$T", 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:
或除“1”以外的任何其他整数。（它也可能忽略其他约束，但我无法检查）。该表如下所示：
And my code is:
我的代码是：
Sub FindRange()
SolverReset
SolverOk SetCell:="$T", MaxMinVal:=1, ValueOf:="0", ByChange:="$O:$R"
SolverAdd CellRef:="$T", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$O:$R", Relation:=3, FormulaText:="0"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Range("T9").Value = Range("T7").Value
End Sub
Any suggestions gratefully welcomed!
欢迎任何建议！
回答by Bo Lu
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.
找到了解决该错误的方法。对于标志“FormulaText:=1”。而不是使用 1，而是使用对值为 1 的任何单元格的引用。
I.e., Change "FormulaText:=1" to "FormulaText:=$H$5" where $H$5's value is 1
即，将“FormulaText:=1”更改为“FormulaText:=$H$5”，其中 $H$5 的值为 1
回答by user2092957
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:
我认为只要值恰好为 1，就会出现错误。其他帖子指出上述解决方案（将值放入单元格）是不可靠的。我发现它不起作用，因为我的代码总是引用一个包含约束限制的单元格。我的（粗略）解决方案是在适当的方向上将限制值移动 10^12 或更低的 1 部分，从而使约束变为 < 或 > 而不是 <= 或 >=。所以而不是：
SolverAdd CellRef:=Range("SolverParam").Address, Relation:=3, _ FormulaText:=Range("SolverConstraint").value
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
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
并对 Relation 使用相反的符号：=1
In this trivial example SolverParam is a single cell parameter to be adjusted and SolverConstraint is a single cell lower bound.
在这个简单的示例中，SolverParam 是要调整的单个单元格参数，而 SolverConstraint 是单个单元格的下限。
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
FormulaText:="=" & Range("SolverConstraint").value
Seems to work reliably
似乎工作可靠
回答by Priya
I had the exact same issue. I solved it in the following way: Just type FormulaText:=1 without the quotes for 1.
我有完全相同的问题。我通过以下方式解决了它：只需键入 FormulaText:=1 而不带 1 的引号。
