VBA 求解器禁用每次迭代后弹出的对话框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25620884/
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 Solver disabling the dialog box that pops after each iteration
提问by Eyan Noronha
I am using the built-in solver in Excel 2007 within a VBA loop to solve a number of different problems. Occasionally, the solver hits the maximum time, which causes a pop-up dialog box to appear asking whether the user wants to Continue, Stop, or End. In all cases I want it to end, and proceed to the next line of the loop. This will prevent a user from having to sit there and respond each time.
我在 VBA 循环中使用 Excel 2007 中的内置求解器来解决许多不同的问题。有时，求解器会达到最大时间，这会导致出现一个弹出对话框，询问用户是要继续、停止还是结束。在所有情况下，我都希望它结束，然后继续循环的下一行。这将防止用户每次都必须坐在那里做出响应。
I ran a macro with the solver pass thru method (Catch max time/iteration dialog box when using Excel Solver in VBA) but then it gives me another dialog box saying "The formula you typed contains an error"
我使用求解器传递方法运行了一个宏（在 VBA 中使用 Excel 求解器时捕获最大时间/迭代对话框），但它给了我另一个对话框，提示“您键入的公式包含错误”
I also tried http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx. Which is a less complicated version of 'The solver pass thru method' but after each iteration it gives me the same message as "The formula you typed contains an error" This is my code
我也试过http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx。这是“求解器传递方法”的不太复杂的版本，但在每次迭代后，它给我的消息与“您输入的公式包含错误”相同，这是我的代码
Sub Optimize()
'
' OptimizeShortfall Macro
'
'
Set MyFirstObj = Range("I124")
Set MyFirstRange = Range("H600:H698")
Dim i As Integer
For i = 0 To 1
MyObj = MyFirstObj.Offset(0, i).Address
MyTestRange = MyFirstRange.Offset(0, i).Address
SolverReset
SolverOk SetCell:=MyObj, MaxMinVal:=1, ValueOf:="0", ByChange:= _
MyTestRange
SolverAdd CellRef:=MyTestRange, Relation:=1, FormulaText:="100%"
SolverOptions MaxTime:=20, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
SolverSolve UserFinish:=True, ShowRef:="SolverIteration"
Next i
End Sub
Function SolverIteration(Reason As Integer)
MsgBox Reason
SolverIteration = 1
End Function
采纳答案by Eyan Noronha
http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx, will explain to you clearly when a dialog box appears, what it means and how to avoid it if you are running solver in a loop.
http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx，会在出现对话框时向您清楚地解释，这意味着什么以及如何避免它正在循环中运行求解器。
One force Solution to go to the next loop in case Max Time/Iterations were reached was to keep the Alt+T button pressed for the duration of the whole macro.
在达到最大时间/迭代次数的情况下进入下一个循环的一种强制解决方案是在整个宏的持续时间内按住 Alt+T 按钮。
Another wiser method is to use The ShowRef argument in the SolverSolve function. What ShowRef does is instead of poping a dialog box, it will run the macro given to it as an argument, in this case "SolverIteration". If you want the solver to continue for the given Iteration set SolverIteration to 0. If you want to stop the solver for the given Iteration and move to the next Iteration, Set SolverIteration to 1.
另一种更明智的方法是在 SolverSolve 函数中使用 ShowRef 参数。ShowRef 所做的不是弹出对话框，而是运行作为参数提供给它的宏，在本例中为“SolverIteration”。如果您希望求解器在给定的迭代中继续，请将 SolverIteration 设置为 0。如果您想停止给定迭代的求解器并移动到下一个迭代，请将 SolverIteration 设置为 1。
Do take a note that the ShowRef Argument has some issues in digesting workbooks with very long name and/or names with space in them, So set the name of the workbook as short as possible.
请注意 ShowRef Argument 在消化名称很长和/或名称中有空格的工作簿时存在一些问题，因此将工作簿的名称设置得尽可能短。
Sorry if my explanation wasn't sufficient enough for you. Here is the exhaustive list of three links that i needed to solve my problem: http://msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx
对不起，如果我的解释对你来说还不够。这是我解决问题所需的三个链接的详尽列表：http: //msdn.microsoft.com/en-us/library/office/ff197237(v=office.15).aspx
Catch max time/iteration dialog box when using Excel Solver in VBA
在 VBA 中使用 Excel Solver 时捕获最大时间/迭代对话框
http://www.excelforum.com/excel-programming-vba-macros/555406-solved-solver-solversolve-showref.html
http://www.excelforum.com/excel-programming-vba-macros/555406-solved-solver-solversolve-showref.html
P.S. In my code mentioned in the above question, just remove the line 'MsgBox Reason' from the function "SolverIteration".(It just causes another dialog box to pop up with an Integer of value Reason on it :P)
PS在上面问题中提到的我的代码中，只需从函数“SolverIteration”中删除“MsgBox Reason”行。（它只会导致另一个对话框弹出，上面有一个整数值原因：P）
