SpecialCells in UDF

[Harald Staff]

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

 

[Jim Rech]

Or am I missing something obvious ?

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

 

[Harald Staff]

Thanks Jim, I feared that it was the "can not change environment"
limitation. In this case it doesn't make any sense to me. But maybe the
specialcells has some huge powers that I'm not yet aware of <bg>.

Best wishes Harald