在VBA中使用Solver时遭遇运行时错误438的问题求助

错误根源 #

你在DepreciationProfilingMethod函数中，错误地将传入的Var和Adj这两个Range参数当作单元格名称字符串使用，通过WS.Range("Var")去引用区域。Excel会试图查找名为"Var"的命名区域，而非使用你传入的Range对象，这直接触发了「对象Worksheet的Range方法失败」的438错误。

修正后的代码 #

Function DepreciationProfilingMethod(WS As Worksheet, Var As Range, Adj As Range)
    SolverReset
    SolverOptions Assumenonneg:=False
    ' 直接使用传入的Range参数，而非通过字符串重新引用
    SolverOK SetCell:=Var, MaxMinVal:=3, ValueOf:=0, ByChange:=Adj, EngineDesc:="GRG Nonlinear"
    SolverSolve True ' 去掉多余括号，避免参数传递问题
End Function

Sub RunDepreciationMacro()
    Dim C_TRS As Excel.Worksheet
    Set C_TRS = ThisWorkbook.Sheets("C_TRS")
    
    Call addsolver ' 确保此过程能正确加载Solver加载项
    Call DepreciationProfilingMethod(C_TRS, C_TRS.Range("Var_2023"), C_TRS.Range("Adj_2023"))
End Sub

额外注意事项 #

• 确认addsolver过程能正确启用Solver加载项，若加载失败，会出现Solver对象未定义的错误。
• 建议给函数添加返回值类型，比如As Boolean，用来判断Solver运行是否成功：

  Function DepreciationProfilingMethod(WS As Worksheet, Var As Range, Adj As Range) As Boolean
      SolverReset
      SolverOptions Assumenonneg:=False
      SolverOK SetCell:=Var, MaxMinVal:=3, ValueOf:=0, ByChange:=Adj, EngineDesc:="GRG Nonlinear"
      SolverSolve True
      DepreciationProfilingMethod = (SolverSolve(True) = 0) ' 0表示Solver运行成功
  End Function
  

• 添加错误处理逻辑，防止Solver运行异常导致程序崩溃：

  Function DepreciationProfilingMethod(WS As Worksheet, Var As Range, Adj As Range) As Boolean
      On Error GoTo ErrorHandler
      
      SolverReset
      SolverOptions Assumenonneg:=False
      SolverOK SetCell:=Var, MaxMinVal:=3, ValueOf:=0, ByChange:=Adj, EngineDesc:="GRG Nonlinear"
      SolverSolve True
      DepreciationProfilingMethod = True
      
      Exit Function
  ErrorHandler:
      DepreciationProfilingMethod = False
      MsgBox "Solver运行出错：" & Err.Description
  End Function
  

内容的提问来源于stack exchange，提问作者Vish B