如何通过Excel Solver调用VBA宏并优化其计算结果？

核心问题在于你使用的**演化引擎（Engine:=3）**不支持StepThru:=True搭配ShowRef的回调机制——这类引擎通过批量生成候选解迭代，不会触发单步调试的回调逻辑，导致宏无法在变量变更后执行。以下是可行的解决方案：

方案：利用工作表变更事件触发宏 #

通过监听变量单元格区域的变更，自动调用BothMethod宏更新目标单元格，确保Solver每次调整变量后都能获取最新计算结果。

1. 添加工作表变更事件代码 #

打开Inputs工作表的代码模块（右键工作表标签→查看代码），粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 检查变更区域是否包含变量单元格AI120:AI131
    If Not Intersect(Target, Me.Range("$AI$120:$AI$131")) Is Nothing Then
        ' 禁用事件防止递归（宏运行可能修改单元格导致重复触发）
        Application.EnableEvents = False
        ' 调用计算宏
        Call BothMethod
        ' 恢复事件监听
        Application.EnableEvents = True
    End If
End Sub

2. 修改Solver调用代码 #

移除原代码中StepThru:=True和ShowRef相关配置，调整后的代码如下：

Option Explicit

Sub SolverTest1()
    Dim ws As Worksheet
    Set ws = Worksheets("Inputs")
    
    ' 确保事件监听处于启用状态
    Application.EnableEvents = True
    
    SolverReset
    SolverOk SetCell:=ws.Range("$AO$120").Address, _
             MaxMinVal:=2, _
             ValueOf:=0, _
             ByChange:=ws.Range("$AI$120:$AI$131").Address, _
             Engine:=3 ' 演化引擎
    
    ' 添加约束条件
    SolverAdd CellRef:=ws.Range("$AI$120").Address, Relation:=1, FormulaText:=ws.Range("$AJ$120").Address
    SolverAdd CellRef:=ws.Range("$AI$121").Address, Relation:=1, FormulaText:=ws.Range("$AJ$121").Address
    
    SolverAdd CellRef:=ws.Range("$AI$120").Address, Relation:=3, FormulaText:=ws.Range("$AG$120").Address
    SolverAdd CellRef:=ws.Range("$AI$121").Address, Relation:=3, FormulaText:=ws.Range("$AG$121").Address
    
    ' 配置Solver选项
    SolverOptions MaxTime:=0, _
                  Iterations:=0, _
                  Precision:=0.000001, _
                  Convergence:=0.0001, _
                  Scaling:=True, _
                  AssumeNonNeg:=True, _
                  Derivatives:=1
    
    SolverOptions PopulationSize:=100, _
                  RandomSeed:=3, _
                  MutationRate:=0.1, _
                  Multistart:=True, _
                  RequireBounds:=True, _
                  MaxSubproblems:=0, _
                  MaxIntegerSols:=0, _
                  IntTolerance:=1, _
                  SolveWithout:=False, _
                  MaxTimeNoImp:=30
    
    ' 启动Solver
    SolverSolve UserFinish:=True
End Sub

关键注意事项 #

• 性能优化：BothMethod运行需1分钟，而演化引擎会进行大量迭代，整体耗时会非常长。建议优先优化BothMethod的执行速度，或调整MaxTimeNoImp、PopulationSize等参数减少迭代次数。
• 递归防护：必须在宏执行前禁用事件，否则BothMethod修改单元格时会再次触发Worksheet_Change，造成死循环或性能崩溃。

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