SolverOptions:如何找到参数位置(AssumeNonNeg)? [英] SolverOptions: How to find the parameter position (AssumeNonNeg)?

问题描述

我一直试图运行带有求解器问题的VBA代码，而没有手动在VB编辑器上的Tools类别的References部分中选中该框.为此，我必须在具有规划求解功能的行中添加Application.Run.以下是我手动运行宏时的代码:

I have been trying to run a VBA code with Solver problem without manually ticking the box in the References section of the Tools category on VB Editor. In order to do so, I will have to add Application.Run to the lines where I have a Solver function. The following is my code when I run the macro manually:

SolverOK SetCell:=Cells(LR + 1, 10), MaxMinVal:=3, ValueOf:="0", ByChange:="$N$4:$Q$4"
SolverSolve True

这已转换为:

 Application.Run "SolverOK", "Cells(LR + 1, 10)", 3, "0", "$N$4:$Q$4"
 Application.Run "SolverOptions", 0, 0, 0.000001, 0.0001, False, False, False, 1
 Application.Run "SolverOptions", 100, 0, 0.075, False, True, 0, 0, 1, False, 30
 Application.Run "SolverSolve", True

但是由于某些原因，代码仍然缺少AssumeNonNeg参数.我希望解决方案包括底片并使它等于False.没有骰子...

But the code is still missing the AssumeNonNeg parameter for some reason. I want the solution to include negatives and making it equal to False. No dice ...

这是我手动录制的宏:

SolverOk SetCell:="$J$59", MaxMinVal:=3, ValueOf:=0, ByChange:="$N$4:$Q$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.000001, Convergence:= _
        0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
    SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
        :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
        IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30
    SolverSolve

无法弄清楚我到底在做什么错.谢谢您看我的问题.

Cannot figure out what exactly am I doing wrong. Thank you for taking a look at my question.

我有Windows 10，MS Office 2013，文件是MS Excel 97-2003.

I have Windows 10, MS Office 2013 and the files are MS Excel 97-2003.

更新:

Application.Run "SolverOK", "Cells(LR + 1, 10)", 3, "0", "$N$4:$Q$4", 1, "GRG Nonlinear"
Application.Run "SolverOptions", 0, 0, 0.000001, False, False, 1, 1, 1, 1, True, 0.0001, False, 100, 0, False, True, 0.075, 0, 0, False, 30

Application.Run "SolverSolve", True

Update2:

尝试完全复制@Peh的代码.它确实为单个文件工作.但是，当我通过外壳程序(使用VBS)为其他类似文件运行它时，却显示Run-time error 1004: cannot run the macro 'solverOK'.在网络上搜索提供了以下链接- VBA:使用规划求解的运行时错误1004

Tried copying @Peh's code exactly. It did work for a single file. However, when I run it for other similar files via shell (using VBS), I am getting Run-time error 1004: cannot run the macro 'solverOK'. Searching the web gave the following link -- VBA: Runtime error 1004 using Solver

开头包含Application.Run "SolverReset", True，并出现相同的错误Run-time error 1004: cannot run the macro 'solverReset. The macro may not be available in this workbook...'

Included Application.Run "SolverReset", True at the beginning and got the same error Run-time error 1004: cannot run the macro 'solverReset. The macro may not be available in this workbook...'

推荐答案

您的问题可能是

SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.000001, Convergence:= _
    0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
    :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
    IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30

与

Application.Run "SolverOptions", 0, 0, 0.000001, 0.0001, False, False, False, 1
Application.Run "SolverOptions", 100, 0, 0.075, False, True, 0, 0, 1, False, 30

为什么会这样?

在第一种情况下，请通过名称来指定每个参数(例如MaxTime:=0).因此，这些参数的顺序无关紧要.

Why is this so?

In the first case you specify each parameter by it's name (eg MaxTime:=0). Therefore it doesn't matter in which order these parameters are.

但是在第二种情况下，您不能按名称指定参数，因此必须按其顺序指定参数.因此(根据 SolverOptions函数)参数的顺序始终是相同的MaxTime, Iterations, Precision, ….如果现在两次调用SolverOptions函数，则使用相同的参数但使用不同的值来调用它们.

But in the second case you cannot specify the parameters by name, so you have to specify them by their order. Therefore (according to the documentation of the the SolverOptions Function) the order of the parameters is always the same MaxTime, Iterations, Precision, …. If you now call the SolverOptions function twice you call them with the same parameters but different values.

在这里，您需要通过一个函数调用提交所有参数(以正确的顺序):

Here you need to submit all parameters (in their correct order) in one single function call:

SolverOptions(MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivatives, SearchOption, IntTolerance, Scaling, Convergence, AssumeNonNeg, PopulationSize, RandomSeed, MultiStart, RequireBounds, MutationRate, MaxSubproblems, MaxIntegerSols, SolveWithout, MaxTimeNoImp)

以您的情况

Application.Run "SolverOptions", MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivatives, SearchOption, IntTolerance, Scaling, Convergence, AssumeNonNeg, PopulationSize, RandomSeed, MultiStart, RequireBounds, MutationRate, MaxSubproblems, MaxIntegerSols, SolveWithout, MaxTimeNoImp
'instead of the parameter names put in your values:
Application.Run "SolverOptions", 0, 0, 0.000001, False, False, 1, 1, 1, 1, True, 0.0001, True, 100, 0, False, True, 0.075, 0, 0, False, 30
'I replaced the missing parameter values with their default due to the documentation.
'Note that the values are re-ordered due to the correct parameter order

如果需要，您需要为此列表中的每个参数设置一个值，不能省略一个，否则顺序会混乱.

You need to set a value for every parameter in this list if you need them or not, you cannot omit one, otherwise the order gets disordered.

SolverOK

SolverOk SetCell:="$J$59", MaxMinVal:=3, ValueOf:=0, ByChange:="$N$4:$Q$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"

与

Application.Run "SolverOK", "Cells(LR + 1, 10)", 3, "0", "$N$4:$Q$4"

尤其是当您将"Cells(LR + 1, 10)"用""包围时，它是一个字符串，并且未如预期那样被评估为VBA代码.

especially when you surround that "Cells(LR + 1, 10)" by "" it is a string and does not get evaluated as VBA code as expected.

根据 SolverOk函数参数的顺序为

SolverOk(SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)

这将导致类似以下内容:

and this should result in something like:

Application.Run "SolverOK", Cells(LR + 1, 10), 3, 0, "$N$4:$Q$4", 1, "GRG Nonlinear"

这篇关于SolverOptions:如何找到参数位置(AssumeNonNeg)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！