Excel中Range在公式中生效但VBA自定义函数调用报错如何解决

• 工作表中使用的LN()函数支持传入多单元格区域/数组，会自动返回每个元素的自然对数值数组，再传入MAX()即可完成数组运算，这是工作表公式原生支持的特性。但VBA中的WorksheetFunction.Ln()仅支持单个数值作为入参，直接传入多单元格Range对象会触发类型不匹配错误。
• VBA不会默认对Range对象做逐元素的隐式运算，你传入的xr是多单元格区域，无法被WorksheetFunction.Ln()直接识别处理，因此运行报错。

方案1：调用工作表数组运算逻辑（代码最简） #

直接用Evaluate方法执行和工作表公式完全一致的数组运算逻辑，不需要手动遍历：

Function testy(xr As Range)
    testy = xr.Worksheet.Evaluate("MAX(LN(" & xr.Address & "))")
End Function

说明：调用Range所属工作表的Evaluate方法，可避免跨表引用时的地址匹配错误，运算结果和你直接在单元格写=MAX(LN(区域))完全一致。

方案2：显式遍历区域计算（兼容性更高） #

如果需要自行控制运算过程、处理异常值，可以遍历区域逐单元格计算后取最大值：

Function testy(xr As Range)
    Dim cell As Range, lnVals() As Double, i As Long
    ReDim lnVals(1 To xr.Cells.Count)
    i = 1
    For Each cell In xr.Cells
        ' 跳过非数值、小于等于0的无效值，避免计算错误
        If IsNumeric(cell.Value) And cell.Value > 0 Then
            lnVals(i) = VBA.Log(cell.Value) ' VBA内置Log就是自然对数，和工作表LN功能一致
            i = i + 1
        End If
    Next
    If i > 1 Then
        ReDim Preserve lnVals(1 To i - 1)
        testy = WorksheetFunction.Max(lnVals)
    Else
        testy = "无有效计算值"
    End If
End Function

说明：该方案额外兼容了空单元格、非数值、负数等异常场景，运行稳定性更高，且直接调用VBA内置的Log函数，比调用工作表函数运算效率更高。

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