Averaging a range in VBA
 

Would someone be so kind as to get me started in the right direction.

Creating a UDF that initially needs to evaluate the average of a range,
however, Average is not a VBA function. I've tried the Evaluate function
with the worksheet function Average but my syntax must be wrong.

Is there an easy way or am I missing something. Here is the basis of
what I'm trying to do:

Function YearAvg(rng As Range)
YearAvg = Average(rng)
End Function


Thanks!
Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Averaging a range in VBA
 

Hi Bill,

Try:

YearAvg = WorksheetFunction.Average(rng)

---
Regards,
Norman



"WilMar" wrote in message
...
Would someone be so kind as to get me started in the right direction.

Creating a UDF that initially needs to evaluate the average of a range,
however, Average is not a VBA function. I've tried the Evaluate function
with the worksheet function Average but my syntax must be wrong.

Is there an easy way or am I missing something. Here is the basis of
what I'm trying to do:

Function YearAvg(rng As Range)
YearAvg = Average(rng)
End Function


Thanks!
Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Averaging a range in VBA
 

Am I missing something?
Why create a UDF to do what an excel function already does?

--
Don Guillett
SalesAid Software

"WilMar" wrote in message
...
Would someone be so kind as to get me started in the right direction.

Creating a UDF that initially needs to evaluate the average of a range,
however, Average is not a VBA function. I've tried the Evaluate function
with the worksheet function Average but my syntax must be wrong.

Is there an easy way or am I missing something. Here is the basis of
what I'm trying to do:

Function YearAvg(rng As Range)
YearAvg = Average(rng)
End Function


Thanks!
Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Averaging a range in VBA
 

There is an AVERAGE function. In order to use it within VBA, you need to
prefix it with WorkSheetFunction.

Sub xx()
a = WorksheetFunction.Average(Range("A1:A5"))
MsgBox a
'or Range("B1").Value = WorksheetFunction.Average(Range("A1:A5"))
End Sub


"WilMar" wrote:

Would someone be so kind as to get me started in the right direction.

Creating a UDF that initially needs to evaluate the average of a range,
however, Average is not a VBA function. I've tried the Evaluate function
with the worksheet function Average but my syntax must be wrong.

Is there an easy way or am I missing something. Here is the basis of
what I'm trying to do:

Function YearAvg(rng As Range)
YearAvg = Average(rng)
End Function


Thanks!
Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Averaging a range in VBA
 

Norman - thanks! Knew I was missing the obvious.

Thanks!
Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!