如何在VBA中循环调用Solver并配置ByChange参数？

问题场景 #

编写VBA代码时需要实现：让变量ma从2到na（na为工作表“Dados Brutos - A”的总列数）循环调用Solver工具，核心难点是动态指定ByChange参数为对应列的第6、7、8行单元格范围。

解决代码 #

Sub AjustarCurvas_A()
'
' Ajusta equações de segundo grau às amostras selecionadas
'
    Dim na As Integer, ma As Integer
    
    Sheets("Dados Brutos - A").Select
    na = Cells.Find(What:="*", SearchDirection:=xlPrevious).Column
    
    Sheets("Det. Ruído - A").Select
    
    With ActiveSheet
        For ma = 2 To na
            SolverOk SetCell:=.Cells(2, ma), MaxMinVal:=2, ValueOf:=0, ByChange:=.Cells(6, ma).Resize(3), _
                Engine:=1, EngineDesc:="GRG Nonlinear"
            SolverSolve (True)
        Next ma
   End With
End Sub

代码逻辑解析 #

• 获取列数：先切换到“Dados Brutos - A”工作表，通过Cells.Find方法查找最后一个非空单元格，以此获取工作表的总列数na；
• 循环求解：切换到“Det. Ruído - A”工作表，利用With语句简化当前工作表的单元格引用；
• 动态配置Solver参数：
  • SetCell:=.Cells(2, ma)指定当前列第2行单元格为优化目标；
  • MaxMinVal:=2设置优化类型为“最小化”，ValueOf:=0将目标值设为0；
  • ByChange:=.Cells(6, ma).Resize(3)是关键：通过Cells(6, ma)定位当前列第6行单元格，再用Resize(3)扩展为3行的范围（即第6、7、8行），实现动态指定调整单元格区域；
  • 选用GRG Nonlinear引擎处理非线性优化问题；
  • SolverSolve (True)执行求解，参数True表示求解完成后不弹出结果对话框。

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