使用 VBA 将约束添加到求解器工具
我正在尝试向我的 Excel VBA 求解器解决方案添加一些新的约束，但在运行代码时出现一些奇怪的行为。我想对最小化问题执行以下操作，但遇到了一些麻烦：
正在调整的两个杠杆必须 <= 35%，但是当我添加约束时：
SolverAdd CellRef:="$C$39", Relation:=2, FormulaText:=".35"
SolverAdd CellRef:="$C$40", Relation:=2, FormulaText:=".35"
当想法适用时，它们会自动将自己设置为 35%这些尽可能低。如果没有这个限制，单元格确实会将自己针对我正在验证的问题设置为低于 35%，因此我知道最佳解决方案低于 35%。
我还想知道是否有办法确保 a) 单元格以 5% 的增量进行优化，并且最终值不包含小数。即 10.00% 而不是 10.23%
抱歉这个冗长的问题，我很感谢我得到的任何帮助。
这是完整的代码：
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 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
请参阅：http://msdn.microsoft.com/ en-us/library/aa272233(v=office.10).aspx
您正在使用关系：=2，相当于“=”。您应该使用 1 (<=)
至于使您的解决方案接近 0.05，只需设置一些其他单元格来输入“百分比”单元格，并将它们限制为整数值。
例如：
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: