excel - VBA 不向 Solver 模型添加元素

Question

早些时候我遇到了一个问题，即求解器没有通过 VBA 向模型添加二进制约束……我(大部分)已经解决了这个问题。但现在我有一个新问题。首先，让我发布违规代码的相关部分。我应该注意到这是在 Excel 2007 中运行的。

'build string of ByChange cells and set up cascading constraints
by_change_string = ""
For i = 1 To j - 1

    If Len(by_change_string) > 0 Then                   'there are already some elements in the string, so we might start with a comma
        If Not (Right(by_change_string, 1) = ",") Then  'make sure the last character isn't already a comma
            by_change_string = by_change_string & ","
        End If
    End If

    current_status = Sheets("Buyback Risk Area").Range("C1").Offset(i).Value
    Select Case current_status
        Case "Y"                'risk area is currently yellow, so green transition is available
            by_change_string = by_change_string & "$E$" & i + 1
            solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string

            'add binary constraints
            solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
        Case "O"                'risk area is currently orange, so green and yellow transitions are available
            by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1
            solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string

            'add cascading constraints
            solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow

            'add binary constraints
            solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"'                solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
        Case "R"                'risk area is currently red, so green, yellow, and orange transitions are available
            by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1 & ",$I$" & i + 1
            'solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string

            'add cascading constraints
            solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
            solveradd cellref:="$G$" & i + 1, relation:=1, formulatext:="$I$" & i + 1 'says G <= I, which means you can't select yellow unless you've already selected orange

            'add binary constraints
            solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
            solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
            solveradd cellref:="$I$" & i + 1, relation:=5, formulatext:="binary"
        Case "B"                'risk area is black, so green, yellow, orange and red transitions are avaailable
            by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1 & ",$I$" & i + 1 & ",$K$" & i + 1
            solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string

            'add cascading constraints
            solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
            solveradd cellref:="$G$" & i + 1, relation:=1, formulatext:="$I$" & i + 1 'says G <= I, which means you can't select yellow unless you've already selected orange
            solveradd cellref:="$I$" & i + 1, relation:=1, formulatext:="$K$" & i + 1 'says K <= M, which means you can't select orange unless you've already selected red

            'add binary constraints
            solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
            solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
            solveradd cellref:="$I$" & i + 1, relation:=5, formulatext:="binary"
            solveradd cellref:="$K$" & i + 1, relation:=5, formulatext:="binary"
    End Select
Next i

'buyback amount constraint
solveradd cellref:="$O$" & j + 1, relation:=1, formulatext:="$B$" & j + 2

'set target cell
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string

'set binary constraints
'binary_array = Split(by_change_string, ",")
'For i = 0 To UBound(binary_array)
'    solveradd cellref:=Range(binary_array(i)), relation:=5, formulatext:="binary"
'Next i


Application.ScreenUpdating = True

SolverSolve userFinish:=False

这里的基本思想是遍历工作表并查看数据(从执行到执行会发生变化)并构建适当的模型。

字符串 by_change_string 包含所有变化变量的单元格地址，它们都是二进制的。因此，当代码检查电子表格的每一行时，它会确定该行上的哪些单元格可供模型考虑，并将它们附加到字符串中。

使用不断增长的 by_change_string 多次调用 solverok 是为了解决未将二元约束添加到模型中的早期问题。所有 <= 约束都是，但不是二进制约束。在将变量添加到模型之前，您似乎无法将其约束为二进制。在构造 by_change_string 的循环完成之后，曾经只有一个solverok 语句，但是当我这样做时，我得到了所有的 <= 约束并且没有二进制约束。

当这段代码在我的测试表上运行时，生成的模型应该有 136 个决策变量(通过更改单元格)。我查过了，by_change_string 实际上有 136 个地址。但是当我查看求解器对话框时，只有前 41 个。我在 Debug模式下逐步执行了整个执行过程，并看到它在每次迭代后调用 solverok，但由于某种原因，只有前 41 个由更改单元格存在.并且出现问题的那一行数据并没有什么特别之处……这不像是某个选定案例的第一个实例或其他什么……

当求解器在这个简化模型上运行时，它会返回一个它认为是最优的垃圾解决方案。基本上它保持一切相同，因此最终的目标函数值与起始值相同。

所以然后我尝试了其他东西......你会注意到底部有一个注释掉的代码块:

'set binary constraints
'binary_array = Split(by_change_string, ",")
'For i = 0 To UBound(binary_array)
'    solveradd cellref:=Range(binary_array(i)), relation:=5, formulatext:="binary"
'Next i

这里的想法是通过将所有这些 solverok 和 solveradd 语句从选择案例中取出来使事情变得更加高效。通过等到最后的solverok语句之后，所有变量现在都在模型中，所以你应该只需要逐步执行by_change_string并将每个变量都设为二进制变量。这样，您只需要一个solverok 语句，您就可以摆脱选择案例中的所有solveradd 语句。所以我在选择的情况下注释掉了所有这些solverok和solveradd，然后再次运行宏。我没有得到求解器的输出。当我在运行后查看求解器对话框时，目标单元格和通过更改单元格字段都是空白的。就好像最后一个solverok语句从未运行过。

所以然后我尝试取消所有solverok的注释，但将solveradd的二进制变量注释掉。我回到只获取前 41 个决策变量。设置二进制约束的循环完成了它的工作，但是solveradd没有将变量放入模型中。

在这一点上，我非常难过。有任何想法吗？

Answer 1

正如我所假设的:
Solver 在通过更改单元格参数中最多只允许 255 个字符。 如果您不相信我，请尝试手动添加所需的单元格。你将无法做到这一点。
您可以通过求解器的最大非连续范围是 51(假设每个组只有 1 个单元格，格式类似于 $A$1,，带有 1 个字母 1 个数字 2 个美元符号和 1 个逗号)。如果您尝试手动添加更多内容，它将删除所有先前的选择。一旦你遇到了长度限制，大概用代码做它只会忽略一切。
老实说，我从未使用过求解器，也不知道如何通过代码访问它。无论如何，你试图让它做一些它做不到的事情。
现在，与其只是告诉你它不起作用，还有一个可能(但在我看来令人讨厌)的解决方法。如果我不得不做你正在做的事情，这就是我会尝试的。将重要的值复制到工作簿中的另一个位置，并将它们彼此相邻设置，以便您可以将它们作为连续范围传递。求解器运行后，将您的值放回它们应位于的位置。
编辑:This limited documentation for Solver可能有点过时，但它说明了以下内容:

Model decision variables are entered in the By Changing Cells editbox. Excel allows one to enter a so-called multiple selection, whichconsists of up to 16 ranges (rectangles, rows or columns, or singlecells) separated by commas.

这意味着解算器只能有 16 个不连续的范围，并希望它能够工作。