如何自动化Excel Solver？解决VBA宏下标越界(Error9)问题

核心错误原因 #

• 工作表名称不匹配：代码里指定的Sheets("Sheet6")要么不存在，要么实际工作表名称和这个不一致（比如被重命名、带空格或大小写有差异），这是触发该错误的最常见原因。
• 重复调用SolverSolve：原代码连续执行两次SolverSolve，会打乱Solver的运行状态，容易引发异常。
• Solver加载项未启用：如果Excel未加载规划求解插件，调用相关函数也会导致运行错误（通常表现为编译错误，但也可能触发运行时错误）。

修复步骤 #

1. 核对工作表名称
   打开目标工作簿，右键点击工作表标签查看准确名称，确保和代码里的Sheet6完全一致。如果名称不符，直接修改代码中的Sheets("Sheet6")为实际工作表名称。

2. 删除冗余的SolverSolve调用
   原代码中SolverSolve (True)和SolverSolve userFinish:=True是重复操作，仅保留后者即可实现自动求解且不弹出提示框。

3. 启用Solver加载项
   打开Excel → 文件 → 选项 → 加载项 → 管理栏选择「Excel加载项」→ 转到 → 勾选「规划求解加载项」→ 确定。

修正后的完整代码 #

Sub SolverMacro()
    Dim i As Integer
    Dim targetSheet As Worksheet
    
    ' 提前绑定目标工作表，减少重复引用出错概率
    Set targetSheet = ThisWorkbook.Sheets("Sheet6") ' 替换为你的实际工作表名称
    
    For i = 0 To 37
        SolverReset ' 重置Solver状态
        
        ' 设置Solver参数：直接传递Range对象比传递单元格地址更稳定
        SolverOk SetCell:=targetSheet.Range("$R$1").Offset(i, 0), _
                 MaxMinVal:=1, _
                 ValueOf:=0, _
                 ByChange:=targetSheet.Range("$O$1").Offset(i, 0), _
                 Engine:=1, _
                 EngineDesc:="GRG Nonlinear"
        
        ' 添加约束条件
        SolverAdd CellRef:=targetSheet.Range("$R$1").Offset(i, 0), _
                  Relation:=2, _
                  FormulaText:=targetSheet.Range("$S$1").Offset(i, 0).Value
        
        SolverSolve userFinish:=True ' 自动求解，关闭弹窗
        SolverFinish KeepFinal:=1 ' 保留最终求解结果
    Next i
    
    Set targetSheet = Nothing ' 释放对象资源
End Sub

额外优化说明 #

• 使用targetSheet对象绑定工作表，避免重复调用Sheets("Sheet6")的开销，后续修改工作表名称时只需改动一处即可。
• 直接传递Range对象给SolverOk参数，比传递单元格地址更可靠，不会因切换工作表导致引用失效。
• SolverAdd的FormulaText使用.Value获取单元格值，确保约束条件准确传递。

内容的提问来源于stack exchange，提问作者Kristóf Polányi