Excel Solver宏自动化：如何将目标值设为单元格引用而非硬编码

具体修改步骤 #

1. 在宏代码开头添加变量定义语句，绑定你存储动态目标值的单元格，你可以自行调整代码里的单元格地址匹配你的实际表结构。
2. 把所有硬编码的0.154替换为ValueOf:=target1，所有硬编码的0.172替换为ValueOf:=target2即可完成动态绑定。
3. 可选优化：原代码是录制宏生成的，存在大量重复的SolverOk语句，不影响运行，你可以按需删除冗余语句减少执行耗时。

修改后的完整代码 #

Sub Macro6()
'
' Macro6 Macro
' Automate Solver
'
' Keyboard Shortcut: Ctrl+Shift+K
'
    ' =====新增的变量绑定语句开始=====
    Dim target1 As Double, target2 As Double
    ' 请将下面的工作表名、单元格地址替换为你实际存储目标值的位置
    target1 = ThisWorkbook.Sheets("你的工作表名").Range("A1").Value ' 对应原0.154的动态值
    target2 = ThisWorkbook.Sheets("你的工作表名").Range("A2").Value ' 对应原0.172的动态值
    ' =====新增的变量绑定语句结束=====

    ActiveWindow.SmallScroll Down:=-66
    Range("A1").Select
    SolverReset
    SolverOk SetCell:="$D$39", MaxMinVal:=2, ValueOf:=0, ByChange:="$Y$14:$Y$33", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$Y$34", Relation:=2, FormulaText:="1"
    SolverAdd CellRef:="$Y$14:$Y$33", Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$D$39", MaxMinVal:=2, ValueOf:=0, ByChange:="$Y$14:$Y$33", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$D$39", MaxMinVal:=2, ValueOf:=0, ByChange:="$Y$14:$Y$33", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
    SolverOk SetCell:="$D$39", MaxMinVal:=2, ValueOf:=0, ByChange:="$Y$14:$Y$33", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$D$38", MaxMinVal:=3, ValueOf:=target1, ByChange:= _
        "$Y$14:$Y$33", Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$D$38", MaxMinVal:=3, ValueOf:=target1, ByChange:= _
        "$Y$14:$Y$33", Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
    SolverOk SetCell:="$D$38", MaxMinVal:=3, ValueOf:=target1, ByChange:= _
        "$Y$14:$Y$33", Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$D$38", MaxMinVal:=3, ValueOf:=target2, ByChange:= _
        "$Y$14:$Y$33", Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$D$38", MaxMinVal:=3, ValueOf:=target2, ByChange:= _
        "$Y$14:$Y$33", Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
    SolverOk SetCell:="$D$38", MaxMinVal:=3, ValueOf:=target2, ByChange:= _
        "$Y$14:$Y$33", Engine:=1, EngineDesc:="GRG Nonlinear"
End Sub

注意事项 #

如果运行时提示Solver相关报错，需要先在VBA编辑器的「工具-引用」菜单里勾选「Solver」选项，启用Solver的VBA库引用即可正常运行。

内容的提问来源于stack exchange，提问作者sherif