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
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