Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells in Worksheets Functions
Does SpecialCells function properly when used in a custom worksheet function?
I cannot get it to work. Here is an example of a custom worksheet function that I hope to build. Public Function EvaluateRange(SearchRange As Range) As Integer Dim RangeWithContent As Range Dim Cell As Range Dim Total As Integer ' this line always returns the entire range Set RangeWithContent = SearchRange.SpecialCells(xlCellTypeConstants) For Each Cell In RangeWithContent ' example logic Select Case Cell.Value Case "A" Total = Total + 7 Case "B" Total = Total + 11 Case "C" Total = Total + 15 End Select Next Cell EvaluateRange = Total End Function In short, I am hoping to use SpecialCells to extract only the Cells with content from the SearchRange. I need to cycle through all of the Cells with content and perform logic on them to return a value. I would expect SpecialCells to return only cells with a constant, but it returns the entire range, regardless of the range argument used in the worksheet. I have tried large and small ranges, but SpecialCells always returns the entire SearchRange for RangeWithContent. If SpecialCells will not work in this case, is there another efficent way to eliminate empty cells from a range (that will work with a custom worksheet function?) While the above example uses a relatively simple Select Case structure to calculate a value, the actual function I am building requires considerably more processor intensive logic (it involves looking up values for each cell in a database), and the worksheet function is used muliple times, so calculation time becomes an issue. The SearchRange may be several thousand cells, so checking each one for a value (even using "If isEmpty(Cell)" to exclude individual cells) takes a good deal of processing time. If I could skip evaluating the empty cells, it would help execution tremendously. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells in Worksheets Functions
Thanks Sébastien!
Your post helped me realize the best solution to this problem was probably not VBA, but a standard worksheet function. Not as clean as I would like (it has to be a rather long cell function), but very functional. "sebastienm" wrote: Unfortunately, some properties and methods can't be used within a custom vba worksheet function. Eg: Find, SpecialCells, CurrentRegion, CurrentArray... In your sample function, you could just do: Public Function EvaluateRange(Rg As Range) As Double With Application.WorksheetFunction EvaluateRange = .CountIf(Rg, "A") * 7 _ + .CountIf(Rg, "B") * 11 _ + .CountIf(Rg, "C") * 15 End With End Function -- Regards, Sébastien <http://www.ondemandanalysis.com "Developer of the Caribbean" wrote: Does SpecialCells function properly when used in a custom worksheet function? I cannot get it to work. Here is an example of a custom worksheet function that I hope to build. Public Function EvaluateRange(SearchRange As Range) As Integer Dim RangeWithContent As Range Dim Cell As Range Dim Total As Integer ' this line always returns the entire range Set RangeWithContent = SearchRange.SpecialCells(xlCellTypeConstants) For Each Cell In RangeWithContent ' example logic Select Case Cell.Value Case "A" Total = Total + 7 Case "B" Total = Total + 11 Case "C" Total = Total + 15 End Select Next Cell EvaluateRange = Total End Function In short, I am hoping to use SpecialCells to extract only the Cells with content from the SearchRange. I need to cycle through all of the Cells with content and perform logic on them to return a value. I would expect SpecialCells to return only cells with a constant, but it returns the entire range, regardless of the range argument used in the worksheet. I have tried large and small ranges, but SpecialCells always returns the entire SearchRange for RangeWithContent. If SpecialCells will not work in this case, is there another efficent way to eliminate empty cells from a range (that will work with a custom worksheet function?) While the above example uses a relatively simple Select Case structure to calculate a value, the actual function I am building requires considerably more processor intensive logic (it involves looking up values for each cell in a database), and the worksheet function is used muliple times, so calculation time becomes an issue. The SearchRange may be several thousand cells, so checking each one for a value (even using "If isEmpty(Cell)" to exclude individual cells) takes a good deal of processing time. If I could skip evaluating the empty cells, it would help execution tremendously. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells in Worksheets Functions
..Find seems to work in UDF's called from a worksheet in xl2002+. .Specialcells
is still a problem--I didn't look at the rest. sebastienm wrote: Unfortunately, some properties and methods can't be used within a custom vba worksheet function. Eg: Find, SpecialCells, CurrentRegion, CurrentArray... In your sample function, you could just do: Public Function EvaluateRange(Rg As Range) As Double With Application.WorksheetFunction EvaluateRange = .CountIf(Rg, "A") * 7 _ + .CountIf(Rg, "B") * 11 _ + .CountIf(Rg, "C") * 15 End With End Function -- Regards, Sébastien <http://www.ondemandanalysis.com "Developer of the Caribbean" wrote: Does SpecialCells function properly when used in a custom worksheet function? I cannot get it to work. Here is an example of a custom worksheet function that I hope to build. Public Function EvaluateRange(SearchRange As Range) As Integer Dim RangeWithContent As Range Dim Cell As Range Dim Total As Integer ' this line always returns the entire range Set RangeWithContent = SearchRange.SpecialCells(xlCellTypeConstants) For Each Cell In RangeWithContent ' example logic Select Case Cell.Value Case "A" Total = Total + 7 Case "B" Total = Total + 11 Case "C" Total = Total + 15 End Select Next Cell EvaluateRange = Total End Function In short, I am hoping to use SpecialCells to extract only the Cells with content from the SearchRange. I need to cycle through all of the Cells with content and perform logic on them to return a value. I would expect SpecialCells to return only cells with a constant, but it returns the entire range, regardless of the range argument used in the worksheet. I have tried large and small ranges, but SpecialCells always returns the entire SearchRange for RangeWithContent. If SpecialCells will not work in this case, is there another efficent way to eliminate empty cells from a range (that will work with a custom worksheet function?) While the above example uses a relatively simple Select Case structure to calculate a value, the actual function I am building requires considerably more processor intensive logic (it involves looking up values for each cell in a database), and the worksheet function is used muliple times, so calculation time becomes an issue. The SearchRange may be several thousand cells, so checking each one for a value (even using "If isEmpty(Cell)" to exclude individual cells) takes a good deal of processing time. If I could skip evaluating the empty cells, it would help execution tremendously. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can these look ups be done using Worksheets functions | Excel Worksheet Functions | |||
Functions for all worksheets | Excel Discussion (Misc queries) | |||
Functions in worksheets | Excel Discussion (Misc queries) | |||
SpecialCells in Worksheets Functions | Excel Programming | |||
Functions across multiple worksheets | Excel Worksheet Functions |