VBA使用ParamArray向Excel内置函数传递可变数量区域参数的问题

问题原因 #

你原来的代码报错是因为 Application.WorksheetFunction.Sum(Ranges) 是把整个 Ranges 数组作为单个参数传给Sum函数，而Sum无法识别包裹在数组里的Range对象，仅当你传入的都是单个单元格时，数组元素都是数值，才会正常计算。

实现代码 #

方案1：通用函数（推荐） #

只需要写一个通用函数，调用时指定要使用的内置函数名，即可适配所有支持多区域参数的工作表函数，完全满足你一次传参、调用10个不同函数的需求：

Function CallExcelFunc(funcName As String, ParamArray args() As Variant) As Variant
    On Error Resume Next
    ' 用Application.Run将参数数组拆分后传递给目标工作表函数
    CallExcelFunc = Application.Run(funcName, args)
    ' 参数非法或函数不存在时返回标准#VALUE!错误
    If Err.Number <> 0 Then CallExcelFunc = CVErr(xlErrValue)
    On Error GoTo 0
End Function

调用示例：

• 求和：=CallExcelFunc("SUM", A1:A2, C1:C5, E3)
• 求平均值：=CallExcelFunc("AVERAGE", A1:A2, C1:C5, E3)
• 求总体标准差：=CallExcelFunc("STDEV.P", A1:A2, C1:C5, E3)
  同一组区域参数只需要输入一次，修改函数名即可得到不同计算结果。

方案2：单独封装每个函数 #

如果不想每次输入函数名，也可以对每个需要的内置函数做单独封装：

' 自定义求和函数
Function MySum(ParamArray Ranges() As Variant) As Variant
    On Error Resume Next
    MySum = Application.Run("Sum", Ranges)
    If Err.Number <> 0 Then MySum = CVErr(xlErrValue)
    On Error GoTo 0
End Function

' 自定义计数函数
Function MyCount(ParamArray Ranges() As Variant) As Variant
    On Error Resume Next
    MyCount = Application.Run("Count", Ranges)
    If Err.Number <> 0 Then MyCount = CVErr(xlErrValue)
    On Error GoTo 0
End Function

调用示例：

=MySum(A1:A2) 直接返回区域求和结果，支持连续/不连续的任意大小区域。

说明 #

该实现依赖 Application.Run 的参数自动展开特性，会把你传入的 ParamArray 数组拆成独立的参数传递给目标工作表函数，完美适配所有支持多区域输入的Excel内置公式。

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