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.

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.

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