Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Summing an unkown number of columns | Excel Worksheet Functions | |||
Passing a range to a user defined function | Excel Discussion (Misc queries) | |||
Passing range as argument | Excel Programming | |||
Passing range to subprocedure - maybe? | Excel Programming |