How to write a range function?
 

Hi,

How can I write a function (such as sum) which could be called on the
result of range operation such as A1:A10/A1:A10.

In other words, I want to call function in the following way:

mysum(A1:A10/A1:A10) Ctrl-Shift-Enter

The following does not work:

Function mysum(r As Range) As Double
Dim i As Integer
pgSum = 0
For i = 1 To r.Rows.Count
pgSum = pgSum + r(i)
Next i
End Function

Many thanks in advance!

Aaron Fude

How to write a range function?
 

Hi Aaron,

Try:

'=============
Public Function mySum(r As Range) As Double
Dim i As Integer

For i = 1 To r.Rows.Count
If IsNumeric(r(i)) Then
mySum = mySum + r(i)
End If
Next i
End Function
'<<=============


---
Regards,
Norman



wrote in message
ups.com...
Hi,

How can I write a function (such as sum) which could be called on the
result of range operation such as A1:A10/A1:A10.

In other words, I want to call function in the following way:

mysum(A1:A10/A1:A10) Ctrl-Shift-Enter

The following does not work:

Function mysum(r As Range) As Double
Dim i As Integer
pgSum = 0
For i = 1 To r.Rows.Count
pgSum = pgSum + r(i)
Next i
End Function

Many thanks in advance!

Aaron Fude

How to write a range function?
 

Hi Aaron,

I would additionally suggest that you change:

Dim i As Integer

to

Dim i As Long


---
Regards,
Norman

How to write a range function?
 

Try:

Function mysum(ByVal r As Variant) As Variant
Dim tmp As Variant
Dim i As Long, j As Long
Dim pgsum As Variant
tmp = r
For i = LBound(tmp, 1) To UBound(tmp, 1)
For j = LBound(tmp, 2) To UBound(tmp, 2)
pgsum = pgsum + tmp(i, j)
Next
Next
mysum = pgsum
End Function

keizi

wrote in message
ups.com...
Hi,

How can I write a function (such as sum) which could be called on the
result of range operation such as A1:A10/A1:A10.

In other words, I want to call function in the following way:

mysum(A1:A10/A1:A10) Ctrl-Shift-Enter

The following does not work:

Function mysum(r As Range) As Double
Dim i As Integer
pgSum = 0
For i = 1 To r.Rows.Count
pgSum = pgSum + r(i)
Next i
End Function

Many thanks in advance!

Aaron Fude