vba - 对数组值应用动态定义的聚合函数

Question

我想对值数组应用用户定义的聚合函数(来自某个单元格的字符串值，例如 "SUM" 或 "STDEV")。这是一个简化的示例。但是我不知道如何进行聚合(最后一行):

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 & ")")  ' <-- This doesn't work, but hopefully it's clear what it should do

End Sub

编辑

我的原始代码还从使用 , 作为十进制符号的电子表格动态创建 values 数组。这会导致斯科特下面的回答出现问题。

Const datasht = "Daten"
Const aggregate_cell = "G1"

Sub run()
    Dim sht As Worksheet
    Dim n_rows As Integer
    Dim rw As Integer

    Application.DecimalSeparator = "."

    Set sht = ActiveWorkbook.Worksheets(datasht)
    n_rows = sht.Cells(1, 1).CurrentRegion.Rows.Count  ' Get range of data

    Dim values() As String
    ReDim values(1 To n_rows)

    For rw = 1 To n_rows
        values(rw) = sht.Cells(rw, 1).Value
    Next rw
    Debug.Print (aggregate(values))

End Sub

Function aggregate(values() As String)
    ' Get aggregated value
    Dim aggregate_fn As String
    aggregate_fn = ActiveWorkbook.Worksheets(datasht).Range(aggregate_cell).Value
    aggregate = Evaluate("=" & aggregate_fn & "(" & Join(values, ",") & ")") ' <-- doesn't work as intended

End Function

Answer 1

这应该适用于通过 Application.WorksheetFunction 提供的任何函数，并且如果传递太多值，则不太可能给出错误。

Sub Tester()

    Dim arr(0 To 1000), res, x As Long, f as String

    'get some values to work with...
    For x = 0 To 1000
        arr(x) = Rnd() * 10
    Next x

    f = "SUM"

    res = CallByName(Application.WorksheetFunction, f, VbMethod, arr)

    Debug.Print res

End Sub