在 VBA 中使用 Excel Solver 时捕获最大时间/迭代对话框

Question

我在 VBA 循环中使用 Excel 2003 中的内置求解器来求解许多不同的问题。有时，求解器会达到最大时间或迭代限制，这会导致出现弹出对话框，询问用户是否要继续、停止或结束。在所有情况下，我都希望它结束​​，并继续循环的下一行。这将防止用户每次都必须坐在那里进行响应。

似乎有人在这里尝试过，但失败了： http://www .excelforum.com/excel-programming/483175-捕捉-max-iterations-stop-of-solver-in-vba.html

Answer 1

这是一个示例解决方案：

它使用 SolverSolve PassThru 方法调用一个函数来处理每次迭代的求解器结果。

Option Explicit

Sub SolverExample()
    Dim results

    ' Set up your solver here...


    ' Execute solve
    SolverOptions StepThru:=True

    results = SolverSolve(True, "SolverIteration")

    Select Case results
    Case 0, 1, 2
        ' solution found, keep final values
        SolverFinish KeepFinal:=1
    Case 4
        'Target does not converge
        'Your code here
    Case 5
        'Solver could not find a feasible solution
        'Your code here
    Case Else
        'Your code
    End Select
End Sub

Function SolverIteration(Reason As Integer)
    ' Called on each solver iteration

    Const SolverContinue As Boolean = False
    Const SolverStop As Boolean = True
    '
    Select Case Reason
    Case 1
        SolverIteration = False ' Continue

    Case 2
        ' Max Time reached
        SolverIteration = True ' Stop

    Case 3
        ' Max Iterations reached
        SolverIteration = True ' Stop

    End Select
End Function