Averaging a range in VBA

[WilMar]

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!

 

[Norman Jones]

Hi Bill,

Try:

YearAvg = WorksheetFunction.Average(rng)

 

[Don Guillett]

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

 

[AA2e72E]

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]

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!