Using Ranges to input variables to UDF

  • Thread starter Thread starter Peter Bradley
  • Start date Start date
P

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
 
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
 
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
 
Back
Top