Using Ranges to input variables to UDF

[Peter Bradley]

Hi all,

I would like to use a range of cells in a UDF to input variables. A
trivial example, without using ranges, would be:

______________________________________

Function SimpleSum(s0,s1,s2,s3)

SimpleSum = (s0 + s2 + s3 + s4)

End Function
______________________________________


The input would be, "SimpleSum(A1,A2,A3,A4)", No problem!

However, what if I wanted to use a range. Here is an example just to
illiterate the problem. Why does the following function not work.

_______________________________________

Function SimpleAve(iSpike As Range)

Dim n As Integer
n = iSpike.Count

ReDim s(n)

i = 0
For Each cell In iSpike
s(i) = cell.Value
i = i + 1
Next cell

SimpleAve = (s0 + s1 + s2 + s3)/n

End Function
_________________________________________


I would like the input to be a range, i.e. SimpleAve(A1:A4)

Thanks in advance!
Peter

 

[Tom Ogilvy]

s0 <> s(0)


Function SimpleAve(iSpike As Range)

Dim n As Integer
n = iSpike.Count

ReDim s(n)

i = 0
For Each cell In iSpike
s(i) = cell.Value
i = i + 1
Next cell

SimpleAve = (s(0) + s(1) + s(2) + s(3))/n

End Function

 

[Bob Phillips]

Function SimpleAve(rng As Range)
Dim i As Long, j As Long
Dim n As Long
Dim row As Range
Dim cell As Range
Dim nTotal As Double

If rng.Cells.Count <> 0 Then
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
nTotal = nTotal + cell.Value
If cell.Value <> "" Then n = n + 1
Next cell
Next row
End If

SimpleAve = nTotal / n
End Function

 

[Jerry W. Lewis]

Previous responders have already covered passing a single range.
To pass an arbitrary number of ranges (as in the worksheet SUM function) see
http://groups.google.com/[email protected]

Jerry