SpecialCells in Worksheets Functions

[Developer of the Caribbean]

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.

[Developer of the Caribbean]

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.

[Dave Peterson]

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