Excel UDF引用跨工作表单元格范围时返回值错误的解决求助

问题概述 #

原自定义函数CONCATENATEMULTIPLE可按指定分隔符连接单元格内容，支持连续单元格范围或括号内的多范围组合，但当引用范围包含来自不同工作表的单元格时会返回值错误，仅在单工作表范围内能正常工作。

原错误代码 #

Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
Dim plc2remove As Long
plc2remove = Len(Separator)

For Each Cell In Ref
    If Not Cell.Value = "" Then
        Result = Result & Cell.Value & Separator
    End If
Next Cell
If Result = "" Then
    CONCATENATEMULTIPLE = "NO DATA TO SHOW"
Else
    CONCATENATEMULTIPLE = Left(Result, Len(Result) - plc2remove)
End If
End Function

问题原因 #

当Ref参数包含跨工作表的多区域范围时，直接遍历Ref内的单元格会触发错误——多区域Range对象需要先遍历每个独立的区域（Area），再遍历区域内的单元格，原代码未处理这种嵌套遍历逻辑。

修复后的代码 #

Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim area As Range
Dim Cell As Range
Dim Result As String
Dim plc2remove As Long
plc2remove = Len(Separator)

' 先遍历每个区域（支持跨工作表的多范围）
For Each area In Ref.Areas
    ' 再遍历当前区域内的每个单元格
    For Each Cell In area
        If Not Cell.Value = "" Then
            Result = Result & Cell.Value & Separator
        End If
    Next Cell
Next area

If Result = "" Then
    CONCATENATEMULTIPLE = "NO DATA TO SHOW"
Else
    CONCATENATEMULTIPLE = Left(Result, Len(Result) - plc2remove)
End If
End Function

功能说明 #

• 自动支持跨工作表的单元格范围引用，无论是单区域还是多区域组合
• 跳过空单元格，不会在结果中插入多余分隔符
• 最终结果自动移除末尾的分隔符
• 若所有引用单元格均为空，返回NO DATA TO SHOW

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