Passing an unkown range to a function
 

I am sure this question has been answered before, but I have bee
looking for almost 3 hours in this forum without any luck.

What do I do when I don't know in advance the address of a column o
numbers for which I want to, say,
compute the mean.

The code below doesn't work.

Function Mean(ParamArray Pipun() As Variant) As Double
Dim pipun2 As Variant
pipun2 = Pipun()
Mean = Application.WorksheetFunction.Sum(pipun2)
End Function

(Please don't answer, use average() in Excel. )


Thanks in advance,

Newbi

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Passing an unkown range to a function
 

The following code will compute the mean of any range of
values, including non-contiguous, and multi-area

Function myMean(rng As Range) As Double
Dim sum As Double
Dim num As Long
Dim a As Range
Dim c As Range

sum = 0# : num = 0
For Each a in rng.Areas
For Each c In a
If IsNumber(c) Then
sum = sum + c.Value
num = num + 1
End If
Next c
Next a
If num 0 Then myMean = sum / n Else myMean = 0
End Function


-----Original Message-----

I am sure this question has been answered before, but I
have been
looking for almost 3 hours in this forum without any
luck.

What do I do when I don't know in advance the address of
a column of
numbers for which I want to, say,
compute the mean.

The code below doesn't work.

Function Mean(ParamArray Pipun() As Variant) As Double
Dim pipun2 As Variant
pipun2 = Pipun()
Mean = Application.WorksheetFunction.Sum(pipun2)
End Function

(Please don't answer, use average() in Excel. )


Thanks in advance,

Newbie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Passing an unkown range to a function
 

or

Function Mean(Pipun As Range) As Double
Mean = Application.WorksheetFunction.Sum(pipun)
End Function

would give the sum for the result of mean as the original function shows.
Obviously replacing Sum with Average would give the Arithmetic Mean.

So the question to the OP, is why you chose to use a parameter array and
what is the real technical problem you are trying to solve. A parameter
array is used when you will have multiple arguments, but you speak about a
column of numbers (a single range - single argument).

--
Regards,
Tom Ogilvy


"Kevin Beckham" wrote in message
...
The following code will compute the mean of any range of
values, including non-contiguous, and multi-area

Function myMean(rng As Range) As Double
Dim sum As Double
Dim num As Long
Dim a As Range
Dim c As Range

sum = 0# : num = 0
For Each a in rng.Areas
For Each c In a
If IsNumber(c) Then
sum = sum + c.Value
num = num + 1
End If
Next c
Next a
If num 0 Then myMean = sum / n Else myMean = 0
End Function


-----Original Message-----

I am sure this question has been answered before, but I
have been
looking for almost 3 hours in this forum without any
luck.

What do I do when I don't know in advance the address of
a column of
numbers for which I want to, say,
compute the mean.

The code below doesn't work.

Function Mean(ParamArray Pipun() As Variant) As Double
Dim pipun2 As Variant
pipun2 = Pipun()
Mean = Application.WorksheetFunction.Sum(pipun2)
End Function

(Please don't answer, use average() in Excel. )


Thanks in advance,

Newbie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Passing an unkown range to a function
 

Kevin, Tom:
Thanks a lot. Why was I playing with ParamArray, etc. Sheer
ignorance!!

Newbie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements