SpecialCells in UDF

H

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
 
J

Jim Rech

Or am I missing something obvious ?

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top