Custom Function
 

I am creating a custom function and want one of my arguments to be a range.
For example, the sum function accepts a range argument (=Sum(range)). How
does the Sum function define that range argument? Fill in the blank for me
and it should help me with my problem.

Function Sum(???)

Sum = ???

End Function

Custom Function
 

Enter as :

Function zum(r As Range) As Variant
Dim rr As Range
zum = 0#
For Each rr In r
zum = zum + rr.Value
Next
End Function


use as:

=zum(B1:B6)
--
Gary''s Student


"SPeterson" wrote:

I am creating a custom function and want one of my arguments to be a range.
For example, the sum function accepts a range argument (=Sum(range)). How
does the Sum function define that range argument? Fill in the blank for me
and it should help me with my problem.

Function Sum(???)

Sum = ???

End Function

Custom Function
 

Function mySum(myRange As Range)
mySum = 0

For Each c In myRange
mySum = mySum + c.Value
Next

End Function


"SPeterson" wrote:

I am creating a custom function and want one of my arguments to be a range.
For example, the sum function accepts a range argument (=Sum(range)). How
does the Sum function define that range argument? Fill in the blank for me
and it should help me with my problem.

Function Sum(???)

Sum = ???

End Function

Custom Function
 

And if you're passing a bunch of stuff (strings, arrays, ranges), you have to
code that yourself.

For example:

Option Explicit
Function mySum(ParamArray myParms()) As Double

Dim myCell As Range
Dim myElement As Variant
Dim tempSum As Double
Dim iCtr As Long

tempSum = 0

For Each myElement In myParms
If TypeOf myElement Is Range Then
For Each myCell In myElement.Cells
If Application.IsNumber(myCell.Value) Then
tempSum = tempSum + myCell.Value
Else
'skip it
End If
Next myCell
ElseIf IsArray(myElement) Then
For iCtr = LBound(myElement) To UBound(myElement)
If Application.IsNumber(myElement(iCtr)) Then
tempSum = tempSum + myElement(iCtr)
End If
Next iCtr
ElseIf Application.IsNumber(myElement) Then
tempSum = tempSum + myElement
End If
Next myElement

mySum = tempSum

End Function

Tested with:

Sub testme()
Dim myArr As Variant
myArr = Array("a", "b", 9999)
MsgBox mySum(ActiveSheet.Range("a1:a10"), "a", "b", 999, myArr)
End Sub

===
Kind of like if you did:
=sum(a1:a10,"a","b",999)



SPeterson wrote:

I am creating a custom function and want one of my arguments to be a range.
For example, the sum function accepts a range argument (=Sum(range)). How
does the Sum function define that range argument? Fill in the blank for me
and it should help me with my problem.

Function Sum(???)

Sum = ???

End Function

--

Dave Peterson