如何在Excel VBA UDF中调用带参数的命名Lambda函数ReadRaw

核心问题说明 #

你无法直接在VBA中调用Excel定义的Lambda命名函数ReadRaw，因为VBA运行环境与Excel公式环境相互独立，需要通过Application.Evaluate或Application.Run间接调用。

可行解决方案 #

方案1：使用Application.Run直接调用命名函数 #

Application.Run可以直接调用工作簿内定义的命名函数，只需传递匹配的参数即可：

Function AverageReadRaw(runPoint As Long, header As Range) As Variant
    Dim cell As Range
    Dim total As Double
    Dim count As Integer
    
    total = 0
    count = 0
    
    For Each cell In header
        ' 调用Excel命名函数ReadRaw，传入指定参数
        total = total + Application.Run("ReadRaw", runPoint, cell.Value)
        count = count + 1
    Next cell
    
    ' 处理空输入范围的情况，避免除以0错误
    AverageReadRaw = IIf(count > 0, total / count, "")
End Function

方案2：使用Application.Evaluate构造公式执行 #

将ReadRaw的调用格式构造成单元格中可执行的公式字符串，通过Evaluate执行计算：

Function AverageReadRaw(runPoint As Long, header As Range) As Variant
    Dim cell As Range
    Dim total As Double
    Dim count As Integer
    
    total = 0
    count = 0
    
    For Each cell In header
        ' 构造完整的公式字符串，模拟单元格内调用ReadRaw的格式
        Dim formulaStr As String
        formulaStr = "=ReadRaw(" & runPoint & ",""" & cell.Value & """)"
        ' 执行公式并累加结果
        total = total + Application.Evaluate(formulaStr)
        count = count + 1
    Next cell
    
    AverageReadRaw = IIf(count > 0, total / count, "")
End Function

额外补充：纯Excel Lambda实现方案（无需VBA） #

其实无需编写VBA，只需修改AverageReadRaw的Lambda函数，用MAP遍历header范围的每个值并调用ReadRaw，再取平均值即可：

=LAMBDA(runPoint,header,AVERAGE(MAP(header,LAMBDA(h,ReadRaw(runPoint,h)))))

在单元格中直接调用=AverageReadRaw(C4,C2:D2)就能得到结果，比VBA实现更简洁。

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