在VBA中使用Excel Solver时，可以捕获最大时间/迭代对话框 [英] Catch max time/iteration dialog box when using Excel Solver in VBA

问题描述

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

I am using the built-in solver in Excel 2003 within a VBA loop to solver a number of different problems. Occasionally, the solver hits the maximum time or iterations limit, 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.

似乎有人在这里捅了一下，但失败了：
http://www.excelforum.com/excel-programming/483175-catching-max-iterations-stop -of-solver-in-vba.html

It appears someone took a stab at it here, but failed: http://www.excelforum.com/excel-programming/483175-catching-max-iterations-stop-of-solver-in-vba.html

推荐答案

这里是一个示例解决方案：

here's a sample solution:

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

it uses the SolverSolve PassThru method to call a function to handle the solver result at each iteration.

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

这篇关于在VBA中使用Excel Solver时，可以捕获最大时间/迭代对话框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！