Or am I missing something obvious ?

Yes<g. A worksheet-called UDF cannot "take actions" and SpecialCells is
unfortunately an action.

--
Jim
"Harald Staff" wrote in message
...
Hi Friends

I have an UDF accepting "anything" as input, it is using the numerical
stuff. A simplified demo:

Public Function myFunction(ParamArray vals() As Variant) As String
Dim L As Long
Dim Cel As Range
Dim FilledCells As Range

For L = LBound(vals) To UBound(vals)
If TypeName(vals(L)) = "Range" Then
On Error Resume Next
Set FilledCells = _
Union(vals(L).SpecialCells(xlCellTypeConstants, 1), _
vals(L).SpecialCells(xlCellTypeFormulas, 1))
On Error GoTo 0
If Not FilledCells Is Nothing Then _
myFunction = myFunction & FilledCells.Address & " "
Set FilledCells = Nothing
'Else stuff here
End If
Next
End Function

The Union statement resizes the range to use only number constants and
numeric formula results, improving performance a lot when ranges are big.
And here is the part I don't get:

Called from code the filled cells assign beautifully:

Sub test()
MsgBox myFunction(Sheets(1).Range("A1:B100"))
End Sub

but called from a cell it does nothing.
=myFunction(A1:B100)
returns A1:B100. Meaning that the Set statement works, but not the
specialcells extraction.

Is this a known issue ? Or am I missing something ovious ?

Best wishes Harald