在VBA中使用求解器将计算模式设置为手动 [英] Using Solver in VBA sets calculation mode to manual

问题描述

在尝试帮助解决

这是我正在测试的代码...

  Sub mySolve（）
 Dim SetRng As Range，ChangeRng As Range 
 Dim i As Long 
 For i = 2 To 4 
 Set SetRng = Sheets（Sheet1）。Cells（i，5）
设置ChangeRng = Sheets（Sheet1）。单元格（i，4）
 
 SolverReset 
 SolverOk SetCell：= SetRng.Address，MaxMinVal：= 3，_ 
 ValueOf：= 1，ByChange：= ChangeRng.Address，_ 
引擎：= 1，EngineDesc：=GRG NONLINEAR
 SolverSolve UserFinish：= T rue 
 SolverFinish KeepFinal：= 1 
 
 Next i 
 End Sub 
  

我使用F8浏览此代码，并在执行每行后手动检查选项中的计算模式。

1. 在SolverSolve之后，计算模式是手动的，除非我这样做，否则不会退回。


2. 如果我删除了对SolverReset的调用，则在调用SolverSolve之后，计算模式不会更改为手动。

解决方案

我只是在Excel 2010中运行你的代码，得到了相同的结果。如果有的话，我会得到错误，无论 SolverReset 是否被调用，此外，运行代码后，文本设置问题...在Excel底部的状态栏中

解决方案需要将计算设置为手动，使用 GRG非线性因为它将通过更改工作表中的单元格来评估目标函数，并且不希望在正确的输入值到位之前进行评估。当从Excel界面调用时，Solver自身清理。谁组合VBA界面必须忘记这个细节。它显然是一个错误。也许您可以在 Solver.com （的网站前线系统，为Microsoft制定了解决方案）。解决方法当然很简单：只需将线路

  Application.Calculation = xlCalculationAutomatic 
  

在底部。

In trying to help address this question, I came across some very strange behavior from Solver in VBA.

I am wondering if anyone else can reproduce it or if there is something wrong with my system (Windows 10, Excel 2016), and if anyone can point me to what is going on.

The workbook setup is very simple.

Here is the code I am testing ...

Sub mySolve()
    Dim SetRng As Range, ChangeRng As Range
    Dim i As Long
    For i = 2 To 4
        Set SetRng = Sheets("Sheet1").Cells(i, 5)
        Set ChangeRng = Sheets("Sheet1").Cells(i, 4)

        SolverReset
        SolverOk SetCell:=SetRng.Address, MaxMinVal:=3, _
            ValueOf:=1, ByChange:=ChangeRng.Address, _
            Engine:=1, EngineDesc:="GRG NONLINEAR"
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1

    Next i
End Sub

I step through this code using F8 and manually check the calculation mode in Options after each line is executed.

1. After SolverSolve, the calculation mode is manual, and does not get set back unless I do it.
2. If I remove the call to SolverReset, after SolverSolve is called, the calculation mode does NOT change to manual.

解决方案

I just ran your code in Excel 2010 and got the same results. If anything, it is worse in that I get the bug whether or not SolverReset is called and, furthermore, after running the code the text "Setting Up Problem..." lingers in the status bar on the bottom of Excel

It makes perfect sense that the Solver needs to set calculation to manual with GRG Nonlinear since it is going to be evaluating the objective function by changing cells in the worksheet and wouldn't want to evaluate them until the correct input values are in place. When called from the Excel interface, the Solver cleans up after itself. Whoever put together the VBA interface must have forgotten this little detail. Its clearly a bug. Perhaps you could file a bug report at Solver.com (the website of Frontline Systems, who make the solver for Microsoft) .The workaround is of course easy enough: just put the line

Application.Calculation = xlCalculationAutomatic

at the bottom of the sub.

这篇关于在VBA中使用求解器将计算模式设置为手动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！