VBA Solver禁用在每次迭代后弹出的对话框 [英] VBA Solver disabling the dialog box that pops after each iteration

问题描述

我在VBA循环中使用Excel 2007中的内置求解器来解决许多不同的问题。偶尔，求解器会触发最大时间，这会弹出一个弹出对话框，询问用户是否要继续，停止或结束。在所有情况下，我希望它结束​​，并进入循环的下一行。这样可以防止用户坐在那里，每次都有回应。

我运行了一个使用求解器传递方法的宏（最大化时间/迭代对话框 ）但是它给了我另一个对话框说你键入的公式包含一个错误

我也尝试了 http://msdn.microsoft.com/en-us/library/office/ff197237（v = office.15）.aspx 。
这是一个不太复杂的版本的求解器传递通过方法，但每次迭代后，它给出了与您键入的公式包含错误相同的消息
这是我的代码

  Sub Optimize（）
'
'OptimizeShortfall Macro 
'
 
' 
设置MyFirstObj =范围（I124）
设置MyFirstRange =范围（H600：H698）
 Dim i As Integer 
对于i = 0至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，迭代：= 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 
 
函数SolverIteration（原因为整数）
 MsgBox原因
 SolverIteration = 1 
结束函数
  

解决方案

http://msdn.microsoft.com/en-我们/ library / office / ff197237（v = office.15）.aspx 将会清楚地向您解释当出现一个对话框时，它是什么意思，以及如何在循环中运行求解器时如何避免这种情况。 / p>

一个强制解决方案去达到最后时间/迭代的情况下的下一个循环是在整个宏的持续时间内按住Alt + T按钮。 p>

另一个更明智的方法是在SolverSolve函数中使用ShowRef参数。
什么ShowRef做的是，而不是弹出一个对话框，它将运行给它作为参数的宏，在这种情况下是SolverIteration。如果希望求解器继续给定的迭代，则将SolverIteration设置为0.如果要停止给定迭代的求解器并移至下一个迭代，请将SolverIteration设置为1。

请注意，ShowRef参数在消化具有很长名称和/或名称的工作簿中存在一些问题，因此设置工作簿的名称尽可能短。

如果我的解释不足够，对不起，以下是我需要解决我的问题的三个链接的详尽列表：
http://msdn.microsoft.com/en-us/library/office/ff197237（v = office.15）.aspx

在VBA中使用Excel求解程序时，可以捕获最大时间/迭代对话框

http://www.excelforum.com/excel-programming-vba-macros/555406-solved-solver-solversolve- showref.html

PS在上述问题中提到的代码中，只需从SolverIteration函数中删除MsgBox Reason行（它只是引起另一个对话框弹出一个值为Integer的值）：P）

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.

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"

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

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

解决方案

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.

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.

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.

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.

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

Catch max time/iteration dialog box when using Excel Solver in VBA

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)

这篇关于VBA Solver禁用在每次迭代后弹出的对话框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！