Using dcount function within user-defined worksheet function

[Guest]

Hi All,

I'm writing a function that counts the number of rows that match a composite
criteria. A set of criteria is passed in the function's arguments. See
example:

Function CountCustomers(group as string, status as string) as Integer
dim cr as Range ' Criteria Range
dim Result as Integer

Result = 0

set r = ThisWorkbook.Sheets("Criteria").Range("A1:B2")
r.Range("A1") = group ' Function exits at this line when used in
worksheet
r.Range("B1") = status

For Each s In ThisWorkbook.Sheets
If InStr(s.Name, "CUST")=1 Then
result = result +
WorkSheetFunction.DCountA(s.Range("M8:M11"), "Name", criteriaRange)
End If
Next
CountCustomers = result
End Function

I created a hidden sheet "Criteria" to be used as a selection criteria for
the DCountA function (last argument).

But when I use "=CountCustomer" in the Excel sheet cell, it appeared as
'#Value'. I know that this happens when we try to modify cells in a function.

Is there any way to make the calculation go through without premature
exiting at the remarked point?

Thanks a lot in advance
pongthai

 

[Guest]

Hi K. Pong

Try:

with ThisWorkbook.Sheets("Criteria")
..Range("A1") = group ' Function exits at this line worksheet
..Range("B1") = status

end with

You could also try putting on error resume next at thestart of the sub.

 

[Charles Williams]

User defined functions are not allowed to modify other cells.

Charles Williams
Decision Models

 

[Bob Phillips]

You really should use Option Explicit in your code, you are declaring one
variable and then trying to use two others in its place, won't work.

This works

Function CountCustomers(group As String, status As String) As Integer
Dim cr As Range ' Criteria Range
Dim Result As Integer

Result = 0

Set cr = ThisWorkbook.Sheets("Criteria").Range("A1:A2")
cr.Range("A1") = group ' Function exits at this line when used in
Worksheet
cr.Range("A2") = status

For Each s In ThisWorkbook.Sheets
If InStr(s.Name, "CUST") = 1 Then
Result = Result + _
Application.WorksheetFunction.DCountA(s.Range("M8:M11"), "Name", cr)
End If
Next
CountCustomers = Result
End Function



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)