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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells in UDF
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells in UDF
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 "Jim Rech" wrote in message ... 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 |
Reply |
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 |