Excel UDF giving value error if selected range is not from a single sheet

Question

The following code is made to concatenate cells with a delimiter. The Ref here can be a continuous range or a group of ranges put inside a bracket. The only limitation i'm getting is that if the group of cells contain range from two different sheets the UDF gives value error.

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

I think the solution is to identify .parent property of each cell when iterating through it but I cannot make it work.

Concatenation works when group of ranges are from single sheet like this

But doesn't work when range from another sheet is also included like this

Answer 1

You could do something like this:

Function myConcat(Sep As String, ParamArray Ref() As Variant) As String
    Dim s, c, m As String
    m = ""
    For Each s In Ref
        For Each c In s.Cells
            m = m & c.Value
            If Len(c.Value) > 0 Then m = m & Sep
        Next
    Next
    If Len(m) = 0 Then
        myConcat= "NO DATA TO SHOW"
    Else
        myConcat = Left(m, Len(m) - Len(Sep))
    End If
End Function

It can be called with something like:

=myConcat(", ",Sheet1!A1,Sheet2!A2,Sheet1!A3,Sheet2!A1,A1:A2)

Note - the ranges are not gathered in brackets.