自定义VBA函数返回的Range在OFFSET公式中无法正常工作

我正在处理可变长度的数据范围，编写了UntilBlank函数获取完整范围：

Function UntilBlank(StartCell As Range)
Dim lastCell As Range
Dim currentCell As Range

Set lastCell = StartCell.Cells(1, 1)
Set currentCell = StartCell.Offset(1, 0)

While Not (IsEmpty(currentCell.Cells(1, 1)))
    Set lastCell = lastCell.Offset(1, 0)
    Set currentCell = lastCell.Offset(1, 0)
Wend

UntilBlank = Range(StartCell.Cells(1, 1), lastCell)

End Function

该函数在大多数场景下表现良好，但作为OFFSET函数的第一个参数使用时，OFFSET仅返回单个#VALUE单元格。返回的地址显示正常，但无法理解为何OFFSET无法正常处理该范围引用。

问题原因 #

你的函数存在两个关键问题：

1. 未指定返回类型，默认是Variant，在工作表公式中调用时，VBA会自动将Range对象转换为它的默认属性（即Value），实际返回的是范围的值数组而非Range对象本身。
2. 赋值返回值时未用Set关键字，这会进一步触发默认属性的转换，导致返回的不是对象引用。

而OFFSET函数的第一个参数要求是Range对象引用，接收值数组时自然会返回#VALUE!错误。

修改后的函数代码 #

指定返回类型为Range，并用Set关键字赋值返回值：

Function UntilBlank(StartCell As Range) As Range
    Dim lastCell As Range
    Dim currentCell As Range

    Set lastCell = StartCell.Cells(1, 1)
    Set currentCell = StartCell.Offset(1, 0)

    While Not IsEmpty(currentCell.Cells(1, 1))
        Set lastCell = lastCell.Offset(1, 0)
        Set currentCell = lastCell.Offset(1, 0)
    Wend

    Set UntilBlank = Range(StartCell.Cells(1, 1), lastCell)
End Function

使用方法 #

修改后直接将UntilBlank作为OFFSET的第一个参数即可，例如：

=OFFSET(UntilBlank(A1), 0, 1)

此时OFFSET能正确识别返回的Range对象，正常执行偏移操作。

额外优化建议 #

可以用Excel内置的End(xlDown)方法简化找最后非空单元格的逻辑，代码更简洁高效：

Function UntilBlank(StartCell As Range) As Range
    Dim lastRow As Long
    ' 从起始单元格向下定位最后一个非空单元格
    lastRow = StartCell.End(xlDown).Row
    ' 处理起始单元格下方全为空的情况，返回起始单元格本身
    If lastRow < StartCell.Row Then
        Set UntilBlank = StartCell
    Else
        Set UntilBlank = Range(StartCell, Cells(lastRow, StartCell.Column))
    End If
End Function

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