如何修改VBA自定义函数MySum，使=MySum(B:B,C:C)返回正确结果？

这个问题的核心在于：Excel原生公式（比如=B:B + C:C）在单个单元格调用时，会自动触发隐式行迭代——只计算当前公式所在行的对应单元格值；但VBA自定义函数默认会把整列Range当成一个完整的区域对象处理，不会自动做这个迭代，所以直接传B:B会报错。

基础版：支持单个单元格调用 #

下面的修改让MySum能识别当前调用单元格的行号，自动取对应行的B、C列值相加，和原生公式行为一致：

Public Function MySum(x As Range, y As Range) As Variant
    ' 获取调用函数的单元格所在行号
    Dim currentRow As Long
    currentRow = Application.Caller.Row
    
    ' 检查当前行是否在传入的两个范围内，避免越界错误
    If Not Intersect(x, Rows(currentRow)) Is Nothing And Not Intersect(y, Rows(currentRow)) Is Nothing Then
        ' 定位到当前行在传入Range中的位置并计算和
        MySum = x.Cells(currentRow - x.Row + 1).Value + y.Cells(currentRow - y.Row + 1).Value
    Else
        ' 若当前行不在传入范围内，返回#VALUE!，和Excel原生行为对齐
        MySum = CVErr(xlErrValue)
    End If
End Function

关键逻辑解释 #

• Application.Caller：这是VBA里的核心对象，它指向调用这个自定义函数的单元格/单元格区域，通过.Row就能拿到当前公式所在的行号。
• x.Cells(currentRow - x.Row + 1)：因为Range的Cells索引是从1开始的，比如B:B的起始行是1，当前行是5的话，5-1+1=5，就会取B5的值。
• 越界检查：如果用户传入的是B2:B10但在B1调用函数，这时候返回#VALUE!，和Excel原生公式的报错逻辑一致。

进阶版：支持数组公式批量填充 #

如果需要支持数组公式（比如选中A1:A10，输入=MySum(B:B,C:C)后按Ctrl+Shift+Enter），可以修改成下面的版本，返回整列的计算结果：

Public Function MySum(x As Range, y As Range) As Variant
    Dim resultArr() As Variant
    Dim targetRng As Range
    Dim i As Long
    
    ' 获取要输出结果的目标区域（单个单元格或数组区域）
    Set targetRng = Application.Caller
    
    ' 初始化结果数组，和目标区域尺寸一致
    ReDim resultArr(1 To targetRng.Rows.Count, 1 To targetRng.Columns.Count)
    
    ' 遍历目标区域的每一行，计算对应行的和
    For i = 1 To targetRng.Rows.Count
        Dim currentRow As Long
        currentRow = targetRng.Row + i - 1
        
        If Not Intersect(x, Rows(currentRow)) Is Nothing And Not Intersect(y, Rows(currentRow)) Is Nothing Then
            resultArr(i, 1) = x.Cells(currentRow - x.Row + 1).Value + y.Cells(currentRow - y.Row + 1).Value
        Else
            resultArr(i, 1) = CVErr(xlErrValue)
        End If
    Next i
    
    ' 单个单元格调用返回单个值，数组调用返回数组
    If targetRng.Count = 1 Then
        MySum = resultArr(1, 1)
    Else
        MySum = resultArr
    End If
End Function

补充：为什么MySum(B:B+0,C:C+0)能正常工作？ #

当你写B:B+0时，Excel会触发隐式类型转换：它会把整列范围转换成当前行的单元格数值（相当于只取B1的值），传给MySum的已经是单个数字，而不是Range对象，所以直接相加不会报错。

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