Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells in UDF
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How are these uses of SpecialCells different? | Excel Programming | |||
SpecialCells Value=0?? | Excel Programming | |||
SpecialCells | Excel Programming | |||
SpecialCells | Excel Programming | |||
Specialcells | Charts and Charting in Excel |