如何在VBA中对数值数组应用动态指定的聚合函数

嘿，我来帮你搞定这个VBA的问题！你想通过字符串指定聚合函数（比如SUM、STDEV）来计算数值数组，但最后那行Evaluate代码跑不起来对吧？我先给你分析下原因，再给两个好用的解决办法：

原代码的问题所在 #

你原来的代码里，直接把VBA数组values拼到Evaluate的字符串里，VBA会把数组转换成默认的字符串表示（比如Variant()），这根本不是Excel公式能识别的数组格式，所以自然运行失败。

先把你的原代码贴出来方便对照：

Sub test()
    Dim values() As Double
    ReDim values(1 To 3)
    values(1) = 3.5
    values(2) = 5
    values(3) = 4.8
    Dim aggregate_fn As String
    aggregate_fn = "SUM"
    Dim result As Double
    result = Evaluate("=" & aggregate_fn & "(" & values & ")") ' <-- 无法正常工作的行
End Sub

解决办法一：把VBA数组转为Excel可识别的数组文本 #

我们可以把VBA数组的元素拼接成Excel公式里的数组格式（用大括号包裹，元素用逗号分隔），这样Evaluate就能正确解析了：

Sub test_Fix1()
    Dim values() As Double
    ReDim values(1 To 3)
    values(1) = 3.5
    values(2) = 5
    values(3) = 4.8
    Dim aggregate_fn As String
    aggregate_fn = "SUM"
    
    ' 将VBA数组转换为Excel公式能识别的数组文本
    Dim arrText As String
    arrText = "{" & Join(Application.Transpose(values), ",") & "}"
    Dim result As Double
    result = Evaluate("=" & aggregate_fn & "(" & arrText & ")")
    
    ' 可以弹出窗口查看结果
    MsgBox aggregate_fn & "的结果是：" & result
End Sub

说明：Application.Transpose是为了把VBA的一维纵向数组转成横向，这样Join才能正确把元素用逗号连接起来，最终生成类似{3.5,5,4.8}的数组文本。

解决办法二：用Application.Run直接调用工作表函数 #

这个方法更简洁，不需要转换数组格式，直接用Application.Run调用对应的Excel工作表函数，把VBA数组作为参数传递进去就行：

Sub test_Fix2()
    Dim values() As Double
    ReDim values(1 To 3)
    values(1) = 3.5
    values(2) = 5
    values(3) = 4.8
    Dim aggregate_fn As String
    aggregate_fn = "SUM"
    
    Dim result As Double
    ' 直接调用工作表函数，传递VBA数组作为参数
    result = Application.Run(aggregate_fn, values)
    
    MsgBox aggregate_fn & "的结果是：" & result
End Sub

说明：这个方法支持大部分Excel聚合函数（比如STDEV、AVERAGE等），只要你的aggregate_fn字符串和Excel工作表函数名称完全一致就行。

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