you can't pass in a 3D range to rng variable. A rng variable can only
refer to one sheet. You will need to change your argument to a string and
then write the code to interpret it.

Function MyFunction(sel As String) As String


--
Regards,
Tom Ogilvy


"Marc" wrote in message
...
Hi everyone,

I have created a UDF receiving a range very similar to the SUM function
but
it works only on ranges that do not contain the worksheet names.

For example, I can do :
= MyFunction(A1:B3) like I could for =SUM(A1:B3)

I receive the range correctly and I can go thru the cells of the range.

But when I move to a more complex range like :
=MyFunction(Sheet1!A1:Sheet8!A1) like I could do for
=SUM(Sheet1!A1:Sheet8!A1)

My macro does not even start and the cell is filled with "#VALUE!"

Simple macro really. Here is the code :

Function MyFunction(sel As Range) As String
MyFunction = Empty
For i = 1 To sel.Count
If (sel.Cells(i).Value < Empty) Then
If (MyFunction < Empty) Then
MyFunction = MyFunction + ", " +
sel.Cells(i).Worksheet.Name
Else
MyFunction = sel.Cells(i).Worksheet.Name
End If
End If
Next i
End Function