working of function sum() or sumif()

[Vasu]

Is there any way that i see the working of the functions sum( ) or sumif( ). I want to
make a function just like sum( ) or sum( ) and make changes to them.

thanks.

[Jerry W. Lewis]

Are you asking how to process an arbitrary number of arguments of
arbitrary type? If not, I can't figure out what you want to know that
isn't manifestly obvious.

The easiest way to define an arbitrary number of arguments (like SUM,
AVERAGE, COUNT, etc.) is with ParamArray; you could define each argument
separately, but then you would have to replicate code to process each
one separately (inefficient and difficult to maintain). Even with
ParamArray, you will still have to distinguish between scalar and array
values, so you will end up with something like the following (if someone
has a better way, I would appreciate knowing it)

Function MySum(ParamArray args() As Variant) As Variant
Dim arg As Variant, element As Variant
MySum = 0
For Each arg In args
If IsArray(arg) Then
For Each element In arg
If IsNumeric(element) Then
MySum = MySum + element
ElseIf IsError(element) Then
' ignore missing values, otherwise return the error
If Not IsMissing(element) Then
MySum element
Exit Function
End If
End If
Next element
Else
If IsNumeric(arg) Then
MySum = MySum + arg
ElseIf IsError(arg) Then
' ignore missing values, otherwise return the error
If Not IsMissing(arg) Then
MySum arg
Exit Function
End If
End If
End If
Next arg
End Function

This VBA function behaves almost the same as SUM, except for the
following differences:

- Only 29 arguments are accepted compared to 30 for SUM. This is a bug
in Excel--the argument wizard shows 30 blanks for MySum, but if you try
to enter the 30th argument, then you will get an Excel error dialog
saying that "You've entered too many arguments for this function."

- Explicit non-array text arguments are ignored instead of returning an
error (MySum is more consistent than SUM, so I would consider this a bug
in SUM, AVERAGE, etc., though MS would probably deny it), e.g.
MySum(x,"oops",y) = MySum({x,"oops",y}) = SUM({x,"oops",y}) compared to
SUM(x,"oops",y) = [#VALUE!]

- Skipped arguments are ignored by MySum, while SUM treats them as
zero. This is immaterial for SUM, since x+0=x, but I presume that you
are interested in the programming technique to apply to something other
than SUM, since VBA will run much more slowly than the built-in function
as J.E. McGimpsey pointed out to you in an earlier thread. To see what
I am talking about, notice that COUNT(x,,y) = 1+COUNT(x,y). I consider
this to be a bug in COUNT, AVERAGE, etc., but one that has minimal
impact, and thus is unlikely to ever be fixed.

Since the point of OOP is to sublimate much of the nitpicking details of
programming, I was disappointed to find that the If IsArray(arg) branch
was needed (I think that For Each should work as an extractor for
scalars as well as arrays) but several of the gurus for this group
considered my viewpoint to be unreasonable.

Jerry

[Tom Ogilvy]

I answered your original posting of this question - If you don't bother to
read the answers, why should people bother to answer.

Regards,
Tom Ogilvy

Vasu <srin...@hotmail.com> wrote in message
news:792101c0f5fe$684a6c20$b1e62ecf@tkmsftngxa04...