#VALUE! error from custom function

[wgbrow43]

I am trying to create a custom function for use in Excel 2003 (Excel 2007 has the function I need but I have to use 2003 at work). I want the function to count based on 3 criteria but I can not get it to count using only 1 criteria. Here is my code:

Function sumviolation(strViol As String) As Integer
Dim intCount As Integer
Dim rCel, rViol As Range
rViol = Range("Data!I2: I1004")

intCount = 0

For Each rCel In rViol
If rCel.Value = strViol Then intCount = intCount + 1
Next rCel
sumviolation = intCount

End Function

The cell where I want the value contains:
=sumviolation("SPEEDING")
Excel shows #VALUE! in the cell. I have tried formatting the cell as General, Number, and Text -- formatted as Text is simply displays the formula. I have tried changing the function return value to variant with the same result.

I realize there is a built in formula that will work but I want to expand this function to check 2 additional values in different ranges, once it is working.

What am I missing?

 

[AnotherHiggins]

Have a look at the built in SumProduct function. It can do what you want.

Example (you didn't provide any details, so this is very generalized):
[tab]=SumProduct(--(Range1 = "SPEEDING") * --(Range2 = "SomethingElse") * --(Range3 = "OneMoreThing"))

If you need further help with this approach, please post a new thread in forum68.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wgbrow43]

Thank you for your reply. My function name is a misnomer - I don't want to SUM anything, I want to count based on 3 different criteria. I guess what I really want is a COUNTIFS() function for Excel 2003.

 

[SkipVought]

John's suggestion DOES count. Try it!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[electricpete]

There may be many ways to skin the cat and I'll take the word of the posters above that the excel funciton does what you want.

In case you want to continue using and customizing your own function, I cut/pasted your code and stepped through with the debugger. The problem is at the following lineL

rViol = Range("Data!I2: I1004")

For one thing, you need "set" to assign a range. Also there is a space buried in your address after the colon.

Also I am not sure if your syntax for specifying a range is right. I am used to seeing the sheet name as parent object to the range, not as part of the argument of range. Try this instead and it should work:

Set rViol = Sheets("Data").Range("I2:I1004")

 

[electricpete]

My bad, there was nothing wrong with
Range("Data!I2: I1004")
(even though I'm not used to seeing it that way).
You just needed the "set" in front of it.

 

[electricpete]

i.e.
Set rViol = Range("Data!I2: I1004")

 

[xlbo]

For the code you have, a simple COUNTIF will do. That ios availabl;e as far back as '95 at least so no version problems.....

See the FAQs for an explanation of how SUMPRODUCT works

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

Also, I think For Each rCel In rViol should be, For Each rCel In rViol[red].cells[/red]

(Although I agree that you don't need a macro to do this.)

_________________
Bob Rashkin

 

[electricpete]

Adding cells doesn't change the operation of that function. It works the same with cells as without cells.

Another problem with the original function as written is that the function does not update when you change a cell within the range of interest ("Data!I2:I1004"). You have to go over to the function call and do something to change it (like edit the string) in order to get the function to update. F9 doesn't work.

The reason is apparently that excel does not know the function depends on those particular cells, because those cells are not included among the function arguments. Good programming form would be to pass the range as an argument, and then the function will automatically recalculate when a cell within the target range changes:

Function sumviolation(strViol As String, rViol As Range) As Integer
Dim intCount As Integer
Dim rCel
' note - deleted duplicate declaration and assignment of rViol

intCount = 0
For Each rCel In rViol
If rCel.Value = strViol Then intCount = intCount + 1
Next rCel
sumviolation = intCount

End Function

And of course the function would be called using
=sumviolation("SPEEDING",Data!I2:I1004)

 

[AnotherHiggins]

This is all a fine discussion as far as academic curiosity goes, but I still wonder if wgbrow43 ever tried SumProduct.

It does what is desired (COUNT how many records meet multiple criteria, not just sum) and you don't need to fiddle with a custom function or worry about users' macro security settings.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[electricpete]

I don't recall the original poster saying he wanted "and" logic to combine his multiple conditions.

I don't recall the original poster saying that speed was paramount such that worksheet functions should be preferred above all else.

I do recall instead that the original poster specifically identified he was aware of worksheet functions, but was looking for a way to do it with vba instead. Also, since he was interested in using vba, I assumed (silly me), that macro security was not an insurmountable obstacle for his application.

Since he posted with vba code with syntax and logical errors, I assumed (silly me) that he might be interested to learn from those errors so he would not repeat them.

My deepest apologies anotherhiggins for daring to contribute to a discussion when you knew so precisely what the original poster needed that any discussion after yours was purely academic.

 

[electricpete]

Sorry - guess I got a little carried away on my sarcasm. Safe to say there is almost always more than one way to skin a cat.

I am curious what does the double dash (--) do in the sumproduct function as presented above?

 

[PHV]

You have to go over to the function call and do something to change it (like edit the string) in order to get the function to update. F9 doesn't work.
Isn't the Application.Volatile method useful for that ?

 

[xlbo]

electricpete - Actually, the initial post said that a custom function was only being used as the OP wanted to use a function that was available in 2007 but not 2003

I will reiterate - this does not even need sumproduct - a simple COUNTIF which is available about as far back as you can go would do the trick

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Good point, Geoff. As has been stated, there are many ways to skin a cat. Excel happens to have multiple skinning methods built right in for this particular cat.

electricpete: As for the "- -", that's called a double unary. It is a way to convert text to numbers.

What it does is multiply by -1, then multiply the result by -1, which is the same as multiplying by 1. Multiplying by 1 is a common way to force Excel to treat a numeric string as a number.

Not knowing what kind of data the OP was dealing with, I threw them in.

For another example of this principle, search Tek-Tips for "Times One Fix", or just enter '0234 into A1 (with the leading apostrophe which forces Excel to treat anything that follows as text) and in another cell type in =A1*1 or =--A1.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wgbrow43]

Thank you to all for your responses. I was not aware of the SUMPRODUCT function before and will use it. I debated about how much info to include in my original post and now see I made an error in that as well.

FWIW: I've got is a bunch data from traffic tickets and I need to count the number of records for each violation, by sex, by age group:
Example:
Violation: Speeding
Sex: Male
Age Brackets: < 18 18-29 30-39 40 or Above

Again, I really appreciate all the responses and I have learned something new. I ALWAYS find Tek-Tips to be a great place for answers!!

 

[AnotherHiggins]

wgbrow43,

No worries.

I'm not sure how your data is laid out, but have a look at Pivot Tables. That might give you the view you want in one step.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.