VBA在Evaluate()中调用自定义函数异常及@运算符位置疑问

问题解答 #

疑问1：为什么引用首个单元格赋值给整个区域会自动适配行号 #

你调用Address(0,0)生成的是行、列均为相对引用的地址，当你给整块rng2区域批量赋值公式时，Excel会遵循公式自动填充的默认规则：相对引用的地址会随着单元格位置的变化自动偏移，效果和你手动把B1的公式下拉填充到整个区域完全一致，因此第2行的公式会自动适配为A2、第3行适配为A3，不会全部固定引用A1。

疑问2：为什么内置函数和自定义函数的@位置不同 #

这是Excel对两类函数的数组兼容性判定逻辑不同导致的：

• 内置的EXP属于原生支持数组运算的函数，Excel识别到你传入了多单元格范围参数时，会默认把隐式交集运算符@加在范围前面，代表逐行取当前行对应的值传入函数计算。
• VBA写的自定义函数默认不会被Excel判定为数组兼容函数，Excel会默认在函数名前加@，代表仅返回函数的首个计算结果，因此直接传入范围时会出现计算错误，需要你手动调整@的位置或者改用相对引用的单单元格参数适配。

补充优化方案 #

如果你不想写公式到单元格，想要直接返回值（和你最初调用内置EXP的效果一致），可以给自定义函数加上数组返回的支持，修改代码如下：

Function TestExpo(alpha) As Variant
    Dim arr, i As Long
    ' 处理传入的范围/数组参数
    If TypeName(alpha) = "Range" Then
        arr = alpha.Value
    Else
        arr = alpha
    End If
    ' 逐元素计算
    If IsArray(arr) Then
        For i = 1 To UBound(arr, 1)
            arr(i, 1) = Exp(arr(i, 1))
        Next i
        TestExpo = arr
    Else
        TestExpo = Exp(arr)
    End If
End Function

修改后你最初的调用方式rng2 = Evaluate("TestExpo(" & rng1.Address & ")")就可以直接返回计算值，不需要写入公式。

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