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