在VBA中使用循环中的求解器 [英] Use solver in VBA with loop in rows
问题描述
代码是：
Sub Solver（）
Dim setcellrange As Range，bychangerange As Range
Dim i As Long
For i = 3 To 5
Set setcellrange = Sheets（AshfordPierce）。Cells（i，15）
Set bychangerange = Sheets（AshfordPierce）。单元格（i，16）
SolverReset
SolverOk SetCell：= setcellrange.Address，ValueOf：= 1，ByChange：= bychangerange.Address，Engine：= 1，EngineDesc：=GRG NONLINEAR
SolverSolve
Next i
End Sub
当我使用VBA中的求解器函数工作，但是VBA结果不会收敛。任何帮助不胜感激。
注意：我将使用这篇文章来突出几个挑战并希望在VBA中使用Solver的最佳做法。如果需要更新，请随时通知我。
告诉Solver要使用哪些单元格。
似乎（几乎）Solver的所有参数都是Variant类型。这可能会导致您相信，如何提供信息，您具有很大的灵活性。事实证明，您需要提供精心制作的文本字符串。
使用SolverSave作为示例，根据
我得到这些结果...
< a href =https://i.stack.imgur.com/izH7B.png =nofollow noreferrer>
使用此代码...
Sub mySolve（）
Dim LoadRng As Range
Dim i As Long
Set LoadRng = Sheets（Sheet1）。Range（Cells（1， 7），Cells（4，7））
For i = 2 To 4
LoadRng.Cells（1，1）.Value == $ E $&我& = 1
LoadRng.Cells（2，1）.Value == COUNT（$ D $& i&）
LoadRng.Cells（3，1）.Value = = {32767,32767,0.000001,0.01，FALSE，FALSE，FALSE，1,2,1,0.0001，TRUE}
LoadRng.Cells（4，1）.Value == {0,0， 1,100,0，FALSE，FALSE，0.075,0,0，FALSE，30}
SolverLoad LoadArea：= LoadRng.Address
SolverSolve UserFinish：= True
SolverFinish KeepFinal： = 1
Next i
End Sub
诊断事项：
- SolverReset引起了一些非常奇怪的行为 - 当使用它时，
后续调用SolverSolve导致Excel翻转到手动
计算。 - 首次调用SolverOK会使问题出现，但
后续调用不会修改问题。 - SolverAdd，SolverChange等影响约束，但不显示基本的
问题设置。
I am trying to use solver to find the root for 182 cells, each cell is depend only on one another variable, the output cell is from O2 to O183, the variable cell is from P2 to P183. I am trying to change the variable cells to have the output cell equal to 1. The code works but the results does not converge. Because I just want a fixed value of 1, I didn't set MaxMin value. The code is:
Sub Solver()
Dim setcellrange As Range, bychangerange As Range
Dim i As Long
For i = 3 To 5
Set setcellrange = Sheets("AshfordPierce").Cells(i, 15)
Set bychangerange = Sheets("AshfordPierce").Cells(i, 16)
SolverReset
SolverOk SetCell:=setcellrange.Address, ValueOf:=1, ByChange:=bychangerange.Address, Engine:=1, EngineDesc:="GRG NONLINEAR"
SolverSolve
Next i
End Sub
When I use solver function out of VBA it works, but VBA results doesn't converge. Any help is appreciated.
Note: I am going to use this post to highlight several challenges and hopefully best practices with using Solver in VBA. Please feel free to let me know if something needs updating.
Telling Solver what cells to use.
It seems (nearly) all of the parameters for Solver are of type Variant. This might lead you to believe that you have a lot of flexibility with how you provide it information. It turns out, you need to provide it carefully crafted text strings.
Using SolverSave as an example, as per the the MS documentation you must specify the SaveArea, and if the SaveArea is on a different sheet than the active sheet, the SaveArea must include the worksheet name.
This works:
SolverSave SaveArea:="Sheet2!A1"
As a matter of fact, all of the MS documentation uses string literals in their examples.
And this works:
SolverSave SaveArea:="Sheet2!A1:A4"
As long as Save only needs four rows to store its data, which is typically, but not always, the case.
This does NOT work:
Set SaveRng = Sheets("Sheet2").Range("A1")
SolverSave SaveArea:=SaveRng
It does not throw an error. It puts some information in cell A1 in Sheet2, but everything else in the active worksheet.
This does NOT work:
Set SaveRng = Sheets("Sheet2").Range("A1:A4")
SolverSave SaveArea:=SaveRng
It throws a type mismatch error at SolverSave.
This DOES work:
Set SaveRng = Sheets("Sheet2").Range("A1")
SaveAddress = Split(SaveRng.Address(external:=True), "[")(0) & Split(SaveRng.Address(external:=True), "]")(1)
SolverSave SaveArea:=SaveAddress
Above is the most concise way I could find to build a full address for a range that includes the sheet name, and the single quote "'" when required. (For you to investigate - why won't SaveRng.Address work?)
I recommend using the last method (above) for every Solver routine with a parameter requiring an address. By default, Solver is expecting things to be on the ActiveSheet and this can cause unexpected behavior.
SolverReset - Danger.
See this post about the combination of SolverReset and SolverSolve setting your calculation mode to Manual (and leaving it there).
SolverReset sets all of the Solver Options back to their defaults. This can be accomplished with SolverLoad (assuming you have a saved set of defaults) or by managing them with SolverGet / SolverOptions.
DO NOT use SolverReset.
SolverOK - Solver Mostly OK.
When recording a macro in order to get an example of Solver VBA code, if you choose the defaults, you will get "Engine:=1", and "EngineDesc:="GRG Nonlinear"". According to the MS documentation, setting a value for Engine or EngineDesc is like choosing a value from the drop down list in the Solver Parameters dialog box. It also says that Engine:=1 corresponds to the Simplex LP method, not GRG Nonlinear. There appears to be opportunity for conflict in setting both of these parameters.
When testing this code ...
Sub mySolve()
Dim SetRng As Range, ChgRng As Range
Dim SetAddr As String, ChgAddr As String
Dim i As Long
For i = 2 To 4
Set SetRng = Sheets("Sheet1").Cells(i, 5)
Set ChgRng = Sheets("Sheet1").Cells(i, 4)
SetAddr = Split(SetRng.Address(external:=True), "[")(0) & Split(SetRng.Address(external:=True), "]")(1)
ChgAddr = Split(ChgRng.Address(external:=True), "[")(0) & Split(ChgRng.Address(external:=True), "]")(1)
SolverOk SetCell:=SetAddr, MaxMinVal:=3, _
ValueOf:=i, ByChange:=ChgAddr, _
Engine:=1, EngineDesc:="GRG NONLINEAR"
SolverSolve UserFinish:=True
Next i
End Sub
Instead of solving the problems on lines 2, 3, and 4 one at a time, it solved the problem on line 4 three times. That was the last problem solved, previous to using this code. It behaves as if SolverOK never updates the SetCell, ValueOf, or ByChange values. No error is thrown.
However, testing this code (removing EngineDesc), all behaves as expected ...
Sub mySolve()
Dim SetRng As Range, ChgRng As Range
Dim SetAddr As String, ChgAddr As String
Dim i As Long
For i = 2 To 4
Set SetRng = Sheets("Sheet1").Cells(i, 5)
Set ChgRng = Sheets("Sheet1").Cells(i, 4)
SetAddr = Split(SetRng.Address(external:=True), "[")(0) & Split(SetRng.Address(external:=True), "]")(1)
ChgAddr = Split(ChgRng.Address(external:=True), "[")(0) & Split(ChgRng.Address(external:=True), "]")(1)
SolverOk SetCell:=SetAddr, MaxMinVal:=3, _
ValueOf:=i, ByChange:=ChgAddr, _
Engine:=1
SolverSolve UserFinish:=True
Next i
End Sub
I recommend only setting one of Engine or EngineDesc, not both.
SolverSave - Interpreting your results
As per the MS Documentation, SolverSave will save the Solver configuration in a column of information. In my experience, that column is typically 4 rows long.
Let me explain the results from running this code ...
Sub mySolve3()
Dim SetRng As Range, ChgRng As Range, SavRng As Range
Dim SetAddr As String, ChgAddr As String, SavAddr As String
Dim iLoop As Long
For iLoop = 2 To 4
Set SetRng = Worksheets("Sheet1").Cells(iLoop, 5)
Set ChgRng = Worksheets("Sheet1").Cells(iLoop, 4)
Set SavRng = Worksheets("Sheet2").Cells(1, iLoop - 1)
SetAddr = Split(SetRng.Address(external:=True), "[")(0) & Split(SetRng.Address(external:=True), "]")(1)
ChgAddr = Split(ChgRng.Address(external:=True), "[")(0) & Split(ChgRng.Address(external:=True), "]")(1)
SavAddr = Split(SavRng.Address(external:=True), "[")(0) & Split(SavRng.Address(external:=True), "]")(1)
SolverOk SetCell:=SetAddr, MaxMinVal:=3, ValueOf:=(iLoop - 1), ByChange:=ChgAddr, Engine:=1
SolverSolve UserFinish:=True
SolverSave SaveArea:=SavAddr
Next iLoop
End Sub
This code will save the setup of three different solver runs in Columns A, B, and C in Sheet 2.
After it is run, on Sheet2: Cell A1 contains =$E$2=1, Cell B1 contains =$E$3=2, Cell C1 contains =$E$4=3. The first row in the SolverSave's output is the SetCell address (that you might specify in SolverOK) and, in my case, setting it equal to the ValueOf value (that you would specify in SolverOK).
Cell A2 contains =COUNT($D$2), cell B2 contains =COUNT($D$3), and cell C2 contains =COUNT($D$4). The second row in SolverSave's output is ByChange address (that you would specify in SolverOK). More investigation is needed to understand why the COUNT function is used.
Cell A3, B3, and C3 contain
={32767,32767,0.000001,0.01,FALSE,FALSE,FALSE,1,2,1,0.0001,TRUE}
By inspection, it appears this is an array of the first 12 TypeNum values in SolverGet. Using SolverGet to retrieve these values, instead of 32767 you get 2147483647 - I expect they have some internal issues with data types.
Cells A4, B4, and C4 all contain
={0,0,1,100,0,FALSE,FALSE,0.075,0,0,FALSE,30}
Again, by inspection, it appears this is an array of the last 12 TypeNum values in SolverGet. Since SolverGet has 29 TypeNum's, it would seem there are 5 that are not available. SolverOptions, however, has only 21 parameters.
After using SolverSave to save a configuration, the contents of the cells can be modified and SolverLoad used to change the Solver configuration (instead of SolverOK).
My original answer to the posted question continues below ...
I need to investigate Solver more. There are several flaky things going on. I was able to make the following work using a loop in VBA.
Starting from this ...
I got these results ...
using this code ...
Sub mySolve()
Dim LoadRng As Range
Dim i As Long
Set LoadRng = Sheets("Sheet1").Range(Cells(1, 7), Cells(4, 7))
For i = 2 To 4
LoadRng.Cells(1, 1).Value = "=$E$" & i & "=1"
LoadRng.Cells(2, 1).Value = "=COUNT($D$" & i & ")"
LoadRng.Cells(3, 1).Value = "={32767,32767,0.000001,0.01,FALSE,FALSE,FALSE,1,2,1,0.0001,TRUE}"
LoadRng.Cells(4, 1).Value = "={0,0,1,100,0,FALSE,FALSE,0.075,0,0,FALSE,30}"
SolverLoad LoadArea:=LoadRng.Address
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Next i
End Sub
Things to diagnose:
- SolverReset caused some very strange behavior - when using it, subsequent calls to SolverSolve caused Excel to flip to manual calculation.
- The first call to SolverOK would set the problem up, but subsequent calls would not modify the problem.
- SolverAdd, SolverChange, etc. affect constraints but not (apparently) the base problem setup.
这篇关于在VBA中使用循环中的求解器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
