Excel规划求解的vba调用

Excel规划求解的vba调用

要用Excel自带的规划求解功能求解一个规划问题：

首先在Excel中建立线性规划模型：

如果没有开发工具选项卡，在文件–选项–自定义功能区–开发工具勾选上

随后打开规划求解功能：开发工具–Excel加载项–勾选 规划求解加载项

然后vba中要引用solver：alt+f11打开vbe编辑器，找 工具–引用–勾选 solver

然后进行宏录制，就可以得到可以复用的代码了：

之后进行操作：记得操作之前点击一下全部重置按钮，这样得到的vba代码就可以复用了，不然每次使用录制的代码就会重复添加约束条件。

最终得到的vba代码：

Sub 宏1()

'    AddIns("规划求解加载项").Installed = False
'    AddIns("规划求解加载项").Installed = True
    Dim SheetName As String

    SheetName = "Sheet3"
    
    SolverReset '全部重置
    SolverOk SetCell:="$B$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$4:$B$6", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$A$9", Relation:=1, FormulaText:="$C$9"
    SolverAdd CellRef:="$A$10", Relation:=1, FormulaText:="$C$10"
    SolverAdd CellRef:="$A$11", Relation:=1, FormulaText:="$C$11"
    SolverOk SetCell:="$B$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$4:$B$6", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$B$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$4:$B$6", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
End Sub

---

有时一个excel的一个sheet中可能需要多个问题的优化，或多个sheet中各需要一个优化，这是单元格就需要带着表名了，下面是改造过后的代码：

Sub 宏2()

'    AddIns("规划求解加载项").Installed = False
'    AddIns("规划求解加载项").Installed = True
    Dim SheetName As String

    SheetName = "Sheet3" '表名
    
    SolverReset '全部重置
    SolverOk SetCell:=SheetName + "!" + "$B$1", MaxMinVal:=1, ValueOf:=0, ByChange:=SheetName + "!" + "$B$4:$B$6", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=SheetName + "!" + "$A$9", Relation:=1, FormulaText:=SheetName + "!" + "$C$9"
    SolverAdd CellRef:=SheetName + "!" + "$A$10", Relation:=1, FormulaText:=SheetName + "!" + "$C$10"
    SolverAdd CellRef:=SheetName + "!" + "$A$11", Relation:=1, FormulaText:=SheetName + "!" + "$C$11"
    SolverOk SetCell:=SheetName + "!" + "$B$1", MaxMinVal:=1, ValueOf:=0, ByChange:=SheetName + "!" + "$B$4:$B$6", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:=SheetName + "!" + "$B$1", MaxMinVal:=1, ValueOf:=0, ByChange:=SheetName + "!" + "$B$4:$B$6", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
End Sub

最后，给宏价格按钮也是很容易的，这样方便别人使用：

可以给按钮改文字：

---

有一个问题，我很好奇solver是如何用代码实现优化算法给单元格参数，目标单元格根据参数单元格计算结果，之后优化代码读入目标单元格中目标函数值，然后优化代码再根据目标函数值生成新的参数输出到参数单元格，依次循环，这个循环过程中的时机是如何把握的？ 也就是说solver模块等待目标单元格计算完成，怎么知道目标单元格已经计算完成，然后实时的取回目标单元格的值？

查看solver的vba代码（密码为：Wildebeest!!，Solver模块中SolverCalls类–Solve函数–Solve = Solv(Me, Application, ThisWorkbook, x) ），而Solv的定义：

Private Declare PtrSafe Function Solv Lib "Solver32.dll" (ByVal object, ByVal app, ByVal wkb, ByVal x As Long) As Long

然后Solv调用dll执行优化，所以并不能看到dll中如何实现的这种操作。

有大神解答一下吗？

先解答一部分，excel的vba 如何实现单元格中公式计算完成再继续执行某vba函数？

Private Sub Worksheet_Change(ByVal Target As Range)
    'https://stackoverflow.com/questions/4388279/how-can-i-run-vba-code-each-time-a-cell-gets-its-value-changed-by-a-formula
    Dim updatedCell As Range
    Set updatedCell = Range(Target.Dependents.Address)
    'Intersect: 返回一 个 Range 对象，该对象代表两个或多个区域之间的矩形交集。
    '           如果指定了不同工作表中的一个或多个区域，则返回错误。
    If Not Intersect(updatedCell, Range("C2")) Is Nothing Then 'C2是目标单元格,里面有公式,比如 =A2*B2,
        Call MySub1 '当C2依赖的A2或B2改变时,会自动调用MySub1
    End If
End Sub
Sub MySub1()
Debug.Print (Time)
End Sub
里面有公式,比如 =A2*B2,
        Call MySub1 '当C2依赖的A2或B2改变时,会自动调用MySub1
    End If
End Sub
Sub MySub1()
Debug.Print (Time)
End Sub