在 Excel 中使用求解器进行 VBA 宏优化未返回最佳变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14988387/
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
VBA Macro optimization using Solver in Excel not returning optimal variables
提问by user2092724
I am trying to optimize three parameters in Excel in order to minimize the error between a experimental value and a theoretical value. I use Solver for each parameter, one at a time, in a for loop. However, I want to iterate this solver for loop (loop inside a loop) until the error in the experimental value and the theoretical value is less than some target value.
我正在尝试优化 Excel 中的三个参数，以尽量减少实验值和理论值之间的误差。我在 for 循环中为每个参数使用 Solver，一次一个。但是，我想为循环（循环内循环）迭代这个求解器，直到实验值和理论值的误差小于某个目标值。
My experimental value is $K25.
My theoretical value (calculated based on my model equations) is $J$25.
My parameters that need to be optimized are $C$4, $C$5, $C$6
我的实验值是$K25.
我的理论值（根据我的模型方程计算）是$J$25.
我的参数需要优化的$C$4，$C$5，$C$6
When I run the following VBA code my parameters in $C$4, $C$5, $C$6do not change from their initial values. However, the macro compiles fine with no errors. Can anyone help me out here?
当我运行下面的VBA代码我的参数 $C$4，$C$5，$C$6不从初始值改变。但是，宏编译得很好，没有错误。有人可以帮我从这里出去吗？
Here is the code:
这是代码：
Sub Macro3()
Application.ScreenUpdating = False
SolverReset
Dim j As Integer
For j = 1 To 100 Step 1
If "$J" > "$K" Then
Dim i As Integer, s As String
For i = 4 To 6 Step 1
s = Format(i, "0")
SolverOk SetCell:="$J", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$" & s, Engine:= _
1, EngineDesc:="GRG Nonlinear"
SolverOptions MaxTime:=0, Iterations:=1000000, Precision:=0.000001, Convergence _
:=0.00001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
:=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30
SolverOk SetCell:="$J", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$" & s, Engine:= _
1, EngineDesc:="GRG Nonlinear"
SolverSolve (True)
SolverReset
Next i
End If
Next j
Application.ScreenUpdating = True
End Sub
采纳答案by Peter Albert
I'm not really sure you need to do this in VBA, as what you're looking for is exactly what the Solver ought to do - modify a set of parameters so that something else is maximized/minimized!
我不确定您是否需要在 VBA 中执行此操作，因为您正在寻找的正是求解器应该做的 - 修改一组参数，以便最大化/最小化其他参数！
Therefore, all you need to do is to insert the formula =ABS(J25-K25)in another cell. This cell will display the delta between your experimental value and the theoretical value. Now set up your Solver so that it minimizes this cell by changing your three parameters - and you're done! (Note that you can provide more than one cell in the "By Changing Variable Cells" field!)
因此，您需要做的就是=ABS(J25-K25)在另一个单元格中插入公式。此单元格将显示您的实验值和理论值之间的差值。现在设置您的求解器，以便它通过更改您的三个参数来最小化此单元格 - 大功告成！（请注意，您可以在“通过更改变量单元格”字段中提供多个单元格！）
In case you want to stick to your approach, here is the syntactical correct code. Note that I have not tested it - but only corrected the mistakes I could spot by looking through the code. It will hopefully be a good starting point. In fact, looking at this approach, I'm sure you'll end up with the wrong result, because each run optimizes only one variable - and you'll therefore never look into any effects that result from the combination of two or three parameters!
如果你想坚持你的方法，这里是语法正确的代码。请注意，我尚未对其进行测试 - 只是更正了通过查看代码可以发现的错误。希望这将是一个很好的起点。事实上，看看这种方法，我相信你最终会得到错误的结果，因为每次运行只优化一个变量——因此你永远不会研究由两个或三个参数组合产生的任何影响！
Anyway, here's your code:
无论如何，这是您的代码：
Sub RunSolver()
Dim j As Integer, i As Integer
Application.ScreenUpdating = False
SolverReset
For j = 1 To 100
Application.Statusbar = j & "/100"
If Range("$J") > Range("$K") Then
For i = 4 To 6
SolverOk SetCell:=Range("$J"), MaxMinVal:=2, ValueOf:=0, ByChange:=Range("$C$" & i), Engine:= _
1, EngineDesc:="GRG Nonlinear"
SolverOptions MaxTime:=0, Iterations:=1000000, Precision:=0.000001, Convergence _
:=0.00001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
:=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30
SolverSolve (True)
SolverReset
Next i
End If
Next j
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
回答by Ome
You may double check that line of your code that says:
您可以仔细检查您的代码行，该行表示：
Engine:= 1, EngineDesc:="GRG Nonlinear"
According to MS documentation:
根据MS 文档：
- 1 for the Simplex LP method,
- 2 for the GRG Nonlinear method, or
- 3 for the Evolutionary method.
- 1 对于单纯形 LP 方法，
- 2 对于 GRG 非线性方法，或
- 3 为进化方法。
Probably, your objective function is nonlinearand you thought you are using the GRG Nonlinear solver since you mention it under the EngineDescparameter. Which is incorrect. This is just a description parameter.
可能您的目标函数是非线性的，并且您认为您正在使用 GRG 非线性求解器，因为您在EngineDesc参数下提到了它。这是不正确的。这只是一个描述参数。
The solver you are actually using is Simplex LPwhich has the value of 1.
您实际使用的求解器是Simplex LP，其值为1。
Change to 2to use GRG Nonlinear solver.
更改为2以使用GRG Nonlinear solver。
