VBA不向Solver模型添加元素

我之前遇到过一个问题，解决方法没有通过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个 . 我已经在调试模式中完成了整个执行，看到它在每次迭代后调用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没有将变量放入模型中 .

我在这一点上很难过 . 有任何想法吗？